I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
COM+, and VB6SP5. We have been having some problems with transactions
not being rolled back when an error was raised, and we have traced it
to the following situation. I'm looking for strategies of error
handling for this situation.
Sometimes (most times?) when there is a deadlock, Err.Number (HRESULT)
is 80004005 (-2147467259), but SOMETIMES IT IS 0 (that is, no error).
So because my code was not told about the error, it kept on
processing, but the problem is, THE DTC TRANSACTION HAS BEEN ABORTED,
so all further updates are not in a transaction. Usually DTC wakes up
sooner or later, but by then it might be too late - I may have made
updates thinking I was in a transaction when I was not. These updates
cannot be rolled back, and my database is left in an inconsistent
state. (FYI, the error I get from DTC are 8509 in the profiler,
reflected as 3704 in VB, if it is detected during processing. If it
is not detected until the root object is exiting, I see 8004e002.)
In particular, so far we have seen only ONE section of code where it
sometimes returns 0. All other areas of the code so far have returned
80004005. Also, in the place that returns 0, it only SOMETIMES
returns 0. Other times, it returns 80004005. There does not seem to
be any rhyme or reason to when it returns 0 and when it returns
80004005 (not that I've discovered yet, though). In all cases, the
error information in the profiler trace either:
Error: 1205, Severity: 13, State: 8
or
Error: 1205, Severity: 13, State: 50
There doesn't seem to be any correlation between the state=8 or the
state=50 errors.
All the error handling samples in MSDN use the "on error goto" trick,
but obviously that doesn't work if Err.Number = 0. Is it safe to just
check cn.Errors? Would there ever be any "informational" or "warning"
errors in there that I really should just leave alone and not report
an error? Is Deadlock the only one that could return 0 that really
should be aborted?
Thanks for any strategies you might know about!
Christine Wolak -- Senior Programmer
www.axiom-corp.comkaligrrl@.yahoo.com (Christine) wrote in message news:<d6363ccb.0309120837.1fc9d3e2@.posting.google.com>...
> I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
> COM+, and VB6SP5. We have been having some problems with transactions
> not being rolled back when an error was raised
Okay, we have the answer. SET NOCOUNT ON in your stored procedure.
Always. In any stored procedure that you will be calling from a
client. Here is a link to a KB article:
http://support.microsoft.com/?kbid=240882 . How it can be "by design"
that an error gets raised and is not reported back to the caller, I
don't know, but apparently Microsoft does not plan to address this
issue any time soon. This article also says "a SQL Server trace may
reveal excessive attentions and rollbacks". We saw NONE of that.
NOTHING excessive - only EXPECTED stuff.
In our particular case, we had a deadlock earlier in the process,
during a stored procedure call. But since we were not told about it
(via Err.Number), our code kept processing. We did not have really
"unexpected" problems, because the stored procedure was a "fastpath",
and if for any reason the fastpath cannot be taken, then the code
takes the long path. And the stored procedure return code of 0 just
happens to indicate that the long path was necessary. So the code
just merrily went along, continuing to process.
Which for some reason DTC/COM+/MTS/whatever, let it keep doing. But
it's no longer in a transaction, and any updates at this point are
COMMITTED. At some point later, DTC would wake up and say "hey,
you're not supposed to still be doing stuff!" At this point, we might
see 3704, or 8004d00a (-2147168246), or, if it got all the way to the
very end and is exiting the root object, 8004e002 (-2147164158).
We had several stored procedures that work similarly, and only one had
the problem, and even then not every single time. The difference
would be the amount of informational messages passed back to the
client (DONE_IN_PROC messages).
So just take it from me - always use SET NOCOUNT ON in all stored
procedures called from VB/C++ except for the specific parts of data
that you want returned to the caller, and realize that, if you don't,
you could at any point stop receiving errors from SQL Server.
Christine Wolak -- www.axiom-corp.com
Showing posts with label sp1. Show all posts
Showing posts with label sp1. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
Deadlock isn't logging SQL statements
Hi,
I'm running SQL 2005 SP1 and we're getting some deadlocks. There is nothing
written to the event log. I've turned on 1204 and the log is showing the
deadlock however again, no SQL statements. I see this in the SQL Server log:
Log Viewer could not read information for this log entry. Cause: Data is
Null. This method or property cannot be called on Null values.. Content:.
I've also tried profiling deadlock and deadlock chain events and I can't get
the SQL still. Anyone tell me what I'm doing wrong?
ThanksHi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:03FDEDA3-935F-458F-81AA-9489BAB1C2B5@.microsoft.com...
> Hi,
> I'm running SQL 2005 SP1 and we're getting some deadlocks. There is
> nothing
> written to the event log. I've turned on 1204 and the log is showing the
> deadlock however again, no SQL statements. I see this in the SQL Server
> log:
> Log Viewer could not read information for this log entry. Cause: Data is
> Null. This method or property cannot be called on Null values.. Content:.
> I've also tried profiling deadlock and deadlock chain events and I can't
> get
> the SQL still. Anyone tell me what I'm doing wrong?
> Thanks
I'm running SQL 2005 SP1 and we're getting some deadlocks. There is nothing
written to the event log. I've turned on 1204 and the log is showing the
deadlock however again, no SQL statements. I see this in the SQL Server log:
Log Viewer could not read information for this log entry. Cause: Data is
Null. This method or property cannot be called on Null values.. Content:.
I've also tried profiling deadlock and deadlock chain events and I can't get
the SQL still. Anyone tell me what I'm doing wrong?
ThanksHi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:03FDEDA3-935F-458F-81AA-9489BAB1C2B5@.microsoft.com...
> Hi,
> I'm running SQL 2005 SP1 and we're getting some deadlocks. There is
> nothing
> written to the event log. I've turned on 1204 and the log is showing the
> deadlock however again, no SQL statements. I see this in the SQL Server
> log:
> Log Viewer could not read information for this log entry. Cause: Data is
> Null. This method or property cannot be called on Null values.. Content:.
> I've also tried profiling deadlock and deadlock chain events and I can't
> get
> the SQL still. Anyone tell me what I'm doing wrong?
> Thanks
Sunday, February 19, 2012
Dbo access does not work.
I apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallenHi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin right
s
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen|||I apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Hello Kalen,
db_owner role is the group the domain account has been assigned access to.
Sorry for the confusion there, in the sql circles I've been in over the
last 10 years, 'dbo rights' have always been understood as the db_owner grou
p.
No windows groups other than BUILTIN\Administrators have been given any expl
icit
rights, and the admins have sa rights.
The value of user_name is DOM\User1 when the user connects.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
[vbcol=seagreen]
> Hi Raymond
> There really isn't anything called 'dbo rights'. DBO is a user name in
> a database. You can put another user in the db_owner role, but this
> doesn't give them the user name dbo. Can you elaborate on exactly what
> you granted to DOM\User1?
> Is it possible the Windows user belongs to a Windows group that was
> given different access to the server and the database?
> What is the value of user_name() when DOM\User1 connects to DatabaseA?
> "Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
> news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallenHi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin right
s
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen|||I apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Hello Kalen,
db_owner role is the group the domain account has been assigned access to.
Sorry for the confusion there, in the sql circles I've been in over the
last 10 years, 'dbo rights' have always been understood as the db_owner grou
p.
No windows groups other than BUILTIN\Administrators have been given any expl
icit
rights, and the admins have sa rights.
The value of user_name is DOM\User1 when the user connects.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
[vbcol=seagreen]
> Hi Raymond
> There really isn't anything called 'dbo rights'. DBO is a user name in
> a database. You can put another user in the db_owner role, but this
> doesn't give them the user name dbo. Can you elaborate on exactly what
> you granted to DOM\User1?
> Is it possible the Windows user belongs to a Windows group that was
> given different access to the server and the database?
> What is the value of user_name() when DOM\User1 connects to DatabaseA?
> "Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
> news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>
Friday, February 17, 2012
Dbnetlib connectionopen(preloginhandshake()) error
SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
ThanksDuring that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server runni
ng
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get a
n
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
ThanksDuring that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server runni
ng
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get a
n
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
Dbnetlib connectionopen(preloginhandshake()) error
SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
Thanks
During that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get an
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
Thanks
During that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get an
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
dbms_lob conversion
hi,
am interested in porting some of my oracle quries to sqlserver. Here is a sample oracle procedure:
create procedure sp1(ac_clob clob)
as
n number;
position number;
begin
position := 4;
n := dbms_lob.instr(ac_clob,'test', position); --returns the location of the substring
n: = dbms_lob.getlength(ac_clob); --returns the length of the clob object
end;
As the clob object comes as a parameter let me know if it can be converted in sql server. Also I some other functions to dbms_lob packages like: dbms_lob.copy, dbms_lob.write, dbms_lob.trim,Hi,
am interested in porting some of my oracle quries to sqlserver. Here is a sample oracle procedure:
create procedure sp1(ac_clob clob)
as
n number;
position number;
begin
position := 4;
n := dbms_lob.instr(ac_clob,'test', position); --returns the location of the substring
n: = dbms_lob.getlength(ac_clob); --returns the length of the clob object
end;
As the clob object comes as a parameter let me know if it can be converted in sql server. Also I some other functions to dbms_lob packages like: dbms_lob.copy, dbms_lob.write, dbms_lob.trim,Hi,
SQL Server does not have the exact same functionality as the DBMS_LOB package. For example, there is no direct I/O between files and blob variables, and blob variables are not allowed as local variables. You need to use BULK INSERT and/or chunk-mode reads and writes from TSQL. ADO as a Stream class that makes this much easier from a client app.
In SQL2K we do support some functions on text columns (like CLOBs in Oracle) from the TSQL level, including DATALENGTH(), PATINDEX(), and SUBSTRING().
See:
Managing ntext, text, and image Data
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp
In SQL 2005, we've introduced a new feature called "large-value types", which allow you to treat CLOBS just like normal string values. You can declare local variables, use them in expressions, pass them as parameters, return them from functions, cast to/from XML, and so on. They have become a first-class data type in the TSQL language. Their declarations look like
varchar(max), nvarchar(max), varbinary(max)
It's really a great SQL 2005 feature, and it makes life *much* easier when dealing with CLOBS and BLOBs from within TSQL.
Regards,
Clifford Dibble
Program Manager, SQL Server|||Thanks for your suggestions|||In addition to what Clifford says, you can take a look at the CHARINDEX, PATINDEX and SUBSTRING functions which give you the functionality you have in your sample code. These should work in both SQL 2000 and SQL 2005.
- Christian
Subscribe to:
Posts (Atom)