Consistent Deadlock, need to identify the rogue PAGE lock

I'm having trouble tracking down a deadlock in a large multi-threaded application. I have been able to gather some information, but not quite enough to identify the two queries at fault.

I collected the following by turning on the -T1204 and -T3605 parameters:

Deadlock encountered .... Printing deadlock information
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Wait-for graph
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Node:1
2006-04-13 09:13:48.70 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.70 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5b80 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x1B4E54F0) Value:0x1a0d5ae0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:2
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14898 CleanCnt:2 Mode: U Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 0::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6bc0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:58 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: IU SPID:61 ECID:0 Ec:(0x1C2B54F0) Value:0x1a0d5440 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:3
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14899 CleanCnt:2 Mode: IX Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6e80 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:55 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:4
2006-04-13 09:13:48.71 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.71 spid3 Wait List:
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5ae0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:53 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec:(0x1BFB94F0) Value:0x1a0c6fc0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3 Victim Resource Owner:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)

I then ran the following queries: -

> select name from sysobjects where id=1993058136;
result: ACCOUNTARRANGEMENTSCHEDULES

> select name from sysindexes where indid=1 and id=1993058136;
result: PK_ACCOUNTARRANGEMENTSCHEDULES

> sp_help PK_ACCOUNTARRANGEMENTSCHEDULES
result: PK_ACCOUNTARRANGEMENTSCHEDULES , dbo , primary key cns , 2005-11-01 13:51:01.650

It looks like there is sql server selected a row lock for the ACCOUNTARRANGEMENTSCHEDULES table (sounds good), but for the UPDATE being done by nodes 2 and 3 a page lock is being used. Is there any way I can get more info on what the statement is or what table it is updating?
I found some more useful info, using the following command: -

dbcc traceon (3604)
dbcc page(10, 4, 14898,2)
dbcc traceoff (3604)

I received the following (amongst other things): -
m_objId = 1993058136

Which would indicate the page lock is on the same table (ACCOUNTARRANGEMENTSCHEDULES).|||

The page that the deadlock occurs on has little to do with what led to the deadlock.

This is the deadlock scenario:

Spid 61 -> owns KEY: 10:1993058136:1 (aa0069f30857)
-> requesting 10:4:14898
Spid 58 -> owns 10:4:14898
-> Requesting 10:4:14899
Spid 55 -> Owns 10:4:14899
-> requesting 10:1993058136:1 (aa0069f30857)

So spid 61 used an index to find a row it was interested in and the needed additional data off page 10:4:14898.

Spid 55 appears to have found a row it intends to update on page 10:4:14899, and now needs a key lock to perform the necessary index maintenance.

Spid 58 appears to be scanning as it is requesting the page with the next ID, which likely just happens to be the next page in the list.

All 3 of these spids are exectuting some kind of prepared statement that does an update, as evidenced by the "sp_execute" statement in the input buffer.

To get to the bottom of this you're going to need to look at the exectution plan of these statements. Use profiler and gather the showplan all, sp:stmtstarting, sp:stmtcompleted, rpc starting and rpc completed events. If the plan shows (as I suspect it will) something like CLUSTERED INDEX SCAN, then you need to think about proper indexes to support these statements.

|||Unfortunately, whenever I use the profiler (even if I only turn on Deadlock and Deadlock Chain events) it seems to change the timings enough that the deadlock is not produced.

I don't know how I can change the indexes. The table has a primary key, which is a composite of two columns. Since a primary key exists, why is a page lock being used? The only thing that I can think that could be causing it, is the fact that an updateable jdbc ResultSet is being used by one of the queries. So it's not strictly an UPDATE, it's a SELECT (that selects only 1 row) then an updateRow.

This deadlock only appeared when introducing sp4. With sp3, no deadlocks occur. Has the lock selection algorithm changed?
|||The lock selection algorithm has not changed. It's possible that you have a different plan now. Even if you don't hit the deadlock the plans themselves can be instructive. If you see Scan's in the plan you want to understand why. Of course a Scan is going to cause each page in an index to be read and locked. The fewer pages that are locked the fewer chances there are of encountering a blocking, or deadlocking scenario.

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RI
D
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Proble
ms
http://support.microsoft.com/defaul...kb;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
RonYou'd do best to call CSS to get the exact answer.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/defaul...kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/defaul...kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RID
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
RonYou'd do best to call CSS to get the exact answer.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RID
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
http://support.microsoft.com/default...b;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
Ron
You'd do best to call CSS to get the exact answer.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default...b;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron
|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default...b;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron
sqlsql

ConnectionString nightmare

I'm having a hard time making a connection work in my ASP.Net page and would be most appreciative of any help. Here two different code snipets and the errors they generate:

ATTEMPT 1:

Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; database=WebDataUtility; uid=AVILA-4400\ASPNET; pwd=;")
cnWebDataUtility.Open()

ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'. Reason: Not associated with a trusted SQL Server connection.

ATTEMPT 2:

Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; Database=WebDataUtility; Integrated Security=SSPI")
cnWebDataUtility.Open()

ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'.

BACKGROUND INFO:

I recently installed SQL Server Developer Edition. During that installation it did not allow me to select anything but "Windows Login" (I am not sure of the exact wording) for authentication, I believe it was. I would expect this to mean, in terms of the connectionstring, "Integrated Security=SSPI." I have tried making this connection in Visual Studio .NET and it works, but when I copy the contents of the ConnectString property in the Properties window in VS to my ConnectionString in the code above, I get errors that it does not recognize a "provider" property, and so on.

How frustrating. If anyone can help me understand this mess, I'd be very grateful.

Thank you,

Paul.I've never seen a connection string with double-quotes around the 'server=' portion of the connection string (probably just a typo?). Nor have I ever seen the server name prepended to the 'uid'. Try:

server=AVILA-4400; database=WebDataUtility; uid=ASPNET; pwd=yourPassword;

I assume that "ASPNET" is the Database Login and you've omitted the password on purpose?

This should work.sqlsql

Connections Closed but still getting errors on page

I have a page that I have 3 connections. I've made sure that each of these are closed when they are not being used and opened just right before being used. I keep getting the error "There is already an open DataReader associated with this Command which must be closed first." This error might show up as being produced by a dataadapter or sqldatareader...I have many. I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections. I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned. I just don't know where to go from here...

Is it possible that the problem lies in multiple instances of this page being opened? Also, the data refreshes every 15 seconds. I really need this to work, but I have no clue on how to fix this problem. The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!

could you provide some example code how you execute your process when your get error?

|||

This section has showed up as producing an error...

 SqlCommand SqlCommand_ACK =new SqlCommand("SQL_STATEMENT", Connection_dis01_8);if (Connection_dis01_8.State == ConnectionState.Closed) Connection_dis01_8.Open(); SqlCommand_ACK.ExecuteNonQuery(); //<<<<<<<<ERROR HERE<<<<<<<<if (Connection_dis01_8.State == ConnectionState.Open) Connection_dis01_8.Close();

This section has also showed up...

 SqlDataReader SqlReader_OOME; SqlCommand SqlCommand_OOME =new SqlCommand("SQL_STATEMENT", Connection_dis01_6);if (Connection_dis01_6.State == ConnectionState.Closed) Connection_dis01_6.Open(); SqlReader_OOME = SqlCommand_OOME.ExecuteReader(); //<<<<<<<<ERROR HERE<<<<<<<<while (SqlReader_OOME.Read()) {//Manipulate Data... } SqlReader_OOME.Close();if (Connection_dis01_6.State == ConnectionState.Open) Connection_dis01_6.Close();

I have the message show up in this section...

if (Connection_dis01_5.State == ConnectionState.Closed) Connection_dis01_5.Open(); dis01DataAdapter.Fill(TempSet2); //<<<<<<<<ERROR HERE<<<<<<<<if (Connection_dis01_5.State == ConnectionState.Open) Connection_dis01_5.Close();

As you can see, I have multiple connecions and one connection is not used twice. I can easily recreate the message by just opening up multiple instances of this page. Thanks! I hope this code helps...the sections are really random and could show up anywhere a connection is used. Could it be a SQL Server setting I need to change?|||Here are my connections...

//Setting up Connections to dis01public static SqlConnection Connection_dis01_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_3 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_4 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_5 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_6 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_7 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_8 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_9 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_10 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_11 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_12 =new SqlConnection("CONNECTION_STRING_HERE");//Setting up Connections to ICCPpublic static SqlConnection Connection_ICCP_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_3 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_4 =new SqlConnection("CONNECTION_STRING_HERE");//Setting up Connections to SCADApublic static SqlConnection Connection_SCADA_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_SCADA_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_SCADA_3 =new SqlConnection("CONNECTION_STRING_HERE");

|||

it is not good idea to create connections and keeping them all the time, modify your code to create connectiuon every time you would like to use it. .Net will take care about pooling so you connections if connection string is the same will be available very fast.

SqlConnection Connection_dis01_1 =new SqlConnection("CONNECTION_STRING_HERE");SqlCommand SqlCommand_ACK =new SqlCommand("SQL_STATEMENT", Connection_dis01_1);

//     try

      Connection_dis01_8.Open();

       SqlCommand_ACK.ExecuteNonQuery(); //<<<<<<<<ERROR HERE<<<<<<<<

// finally

          Connection_dis01_1.Close();

//end try

put your code in try catch finally and close connecion in finally part (I use VB so you have to writy your trty catsh yourself)

THis way your connection will be always closed and you save some server memory and time also.

Connection to SQL Server 2000 From an ASP Page

Dear All,
I'm really confusing while reading the connections Issue in the support
knowledge.
Our Company website guest users can register themselves in my website. The
data goes to the SQL Server database 2000 which also used by internal users
of my company as Inhouse Database. So I want to restrict the Website Guest
users. Whats the safest way to connect to?.
Actually MY IIS SQL Server running on different machine(as stated in
support base)
1. I have created windows user a/c (say WEBUSER) on both machines with the
same password.
2. I have created a login a/c SQL Server for WEBUSER too.
But unfortunately I am getting "Internal Server Error" - Page Can not be
Displayed.
My connection String is as follows:
dc0.Open "Provider=sqloledb;" & _
"Network Library=DBNETLIB;" & _
"Integrated Security=SSPI." & _
"Data Source=ServerName;" & _
"Initial Catalog=DatabaseName;" & _
"User ID=UserName;" & _
"Password=password"
Please advice me is this safe?. Why I'm getting the error?
Thanks
Kavi
As a first step, try the following:
Log onto SQL Query Analyser and log on with the DataSource/username/password
that is used withing your connection string.
If this is OK , simulate a SQL statement that is similar that will run by a
web user.
At this point, you'll know that the account is OK.
can you connect , for example, via telnet to the sql server?
Are other pages running on the web server.
Also, see if you can trap the error and print it on the page.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> Dear All,
> I'm really confusing while reading the connections Issue in the support
> knowledge.
> Our Company website guest users can register themselves in my website. The
> data goes to the SQL Server database 2000 which also used by internal
users
> of my company as Inhouse Database. So I want to restrict the Website Guest
> users. Whats the safest way to connect to?.
> Actually MY IIS SQL Server running on different machine(as stated in
> support base)
> 1. I have created windows user a/c (say WEBUSER) on both machines with
the
> same password.
> 2. I have created a login a/c SQL Server for WEBUSER too.
> But unfortunately I am getting "Internal Server Error" - Page Can not be
> Displayed.
> My connection String is as follows:
> dc0.Open "Provider=sqloledb;" & _
> "Network Library=DBNETLIB;" & _
> "Integrated Security=SSPI." & _
> "Data Source=ServerName;" & _
> "Initial Catalog=DatabaseName;" & _
> "User ID=UserName;" & _
> "Password=password"
> Please advice me is this safe?. Why I'm getting the error?
> Thanks
> Kavi
>
|||Thanks Jack. I'll try.
"Jack Vamvas" wrote:

> As a first step, try the following:
> Log onto SQL Query Analyser and log on with the DataSource/username/password
> that is used withing your connection string.
> If this is OK , simulate a SQL statement that is similar that will run by a
> web user.
> At this point, you'll know that the account is OK.
> can you connect , for example, via telnet to the sql server?
> Are other pages running on the web server.
> Also, see if you can trap the error and print it on the page.
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> users
> the
>
>
|||In the Advanced tab of Internet Options of IE, uncheck "Show friendly
HTTP error messages". Then you usually can see the whole error
message.
Shane

Connection to SQL Server 2000 From an ASP Page

Dear All,
I'm really confusing while reading the connections Issue in the support
knowledge.
Our Company website guest users can register themselves in my website. The
data goes to the SQL Server database 2000 which also used by internal users
of my company as Inhouse Database. So I want to restrict the Website Guest
users. Whats the safest way to connect to?.
Actually MY IIS SQL Server running on different machine(as stated in
support base)
1. I have created windows user a/c (say WEBUSER) on both machines with the
same password.
2. I have created a login a/c SQL Server for WEBUSER too.
But unfortunately I am getting "Internal Server Error" - Page Can not be
Displayed.
My connection String is as follows:
dc0.Open "Provider=sqloledb;" & _
"Network Library=DBNETLIB;" & _
"Integrated Security=SSPI." & _
"Data Source=ServerName;" & _
"Initial Catalog=DatabaseName;" & _
"User ID=UserName;" & _
"Password=password"
Please advice me is this safe?. Why I'm getting the error?
Thanks
KaviAs a first step, try the following:
Log onto SQL Query Analyser and log on with the DataSource/username/password
that is used withing your connection string.
If this is OK , simulate a SQL statement that is similar that will run by a
web user.
At this point, you'll know that the account is OK.
can you connect , for example, via telnet to the sql server?
Are other pages running on the web server.
Also, see if you can trap the error and print it on the page.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> Dear All,
> I'm really confusing while reading the connections Issue in the support
> knowledge.
> Our Company website guest users can register themselves in my website. The
> data goes to the SQL Server database 2000 which also used by internal
users
> of my company as Inhouse Database. So I want to restrict the Website Guest
> users. Whats the safest way to connect to?.
> Actually MY IIS SQL Server running on different machine(as stated in
> support base)
> 1. I have created windows user a/c (say WEBUSER) on both machines with
the
> same password.
> 2. I have created a login a/c SQL Server for WEBUSER too.
> But unfortunately I am getting "Internal Server Error" - Page Can not be
> Displayed.
> My connection String is as follows:
> dc0.Open "Provider=sqloledb;" & _
> "Network Library=DBNETLIB;" & _
> "Integrated Security=SSPI." & _
> "Data Source=ServerName;" & _
> "Initial Catalog=DatabaseName;" & _
> "User ID=UserName;" & _
> "Password=password"
> Please advice me is this safe?. Why I'm getting the error?
> Thanks
> Kavi
>|||Thanks Jack. I'll try.
"Jack Vamvas" wrote:

> As a first step, try the following:
> Log onto SQL Query Analyser and log on with the DataSource/username/passwo
rd
> that is used withing your connection string.
> If this is OK , simulate a SQL statement that is similar that will run by
a
> web user.
> At this point, you'll know that the account is OK.
> can you connect , for example, via telnet to the sql server?
> Are other pages running on the web server.
> Also, see if you can trap the error and print it on the page.
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> users
> the
>
>|||In the Advanced tab of Internet Options of IE, uncheck "Show friendly
HTTP error messages". Then you usually can see the whole error
message.
Shane

connection to SQL from asp.net page

I am using an asp.net page with vb.net. The following connection in my web.config file will connect the database to my page no problem:

"Data Source=MIAPPS1;Initial Catalog=MASTER_DB;Integrated Security=SSPI;"

However, when I add data to the page and try to preview it in the browser I get the following error:

"System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I'm kind of perplexed as to why I can attach a database to my page, test the connection view data -but not be able to connect when previewing through the browser.

Any Ideas?

BillHave you added the ASP.NET account to the server server authorized logins?|||I have added an account ASPNET. Is it ASPNET or ASP.NET. The error message also showed, null for the login, does this still point to the ASPNET login issue?|||Yes. The errors means that you either trying to connected to SQL with SQL Authication while only Windows Authentication is enabled, or that the windows account you're using is not one enlisted into the sql logins account.

If you're using IIS5 the account is ASP.NET, if using IIS6 is the Network Service account

Connection timeout when generating recordset from a stored procedure in an ASP page

I'm trying to create a binding to a stored procedure on a SQL server. I can
create the database connection and view the tables and stored procedures.
When I try to create a stored procedure binding I can choose the connection
and the procedure. But when I check "Returns DataSet" and click Test... I
get the following Dreamweaver error after a minute or so:
A server timeout has occured. Here are the possible reasons.
1. Please make sure that the webserver is up and running.
2. Please verify that the ODBC DSN exists on the testing server.
Executing the stored procedure in SQL Query Analyzer returns results in less
than 5 seconds.
My webserver is up and running and the web.config file is the same on both
the local machine and testing server. The SQL server and webserver are
different machines. My stored procedure is below.
I can create datasets (querys) on the database tables. It's only when I try
to generate a dataset from a stored procedure that run into problems.
If it matters I'm using ASP.NET VB, SQL Server 2000, IIS 5.0, .NET 1.1 and
Dreamweaver MX 2004.
Please help, I'm at a standstill.
I get the same problem when using VB .NET to to create the ASP page. If I
use Coldfusion instead on ASP it works flawlessly. Only problem is I that
have a developers license for Coldfusion so this is not an acceptable
workaround.
Thanks in advance!! My stored procedure is below:
CREATE PROCEDURE proc_combinedDB
(@.platform varchar(40), @.server varchar(40),@.keyword varchar(50))
WITH RECOMPILE
AS
SELECT *
FROM [prtracker_classworks].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
UNION ALL
SELECT *
FROM [prtracker_mac bugs].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS]
LIKE @.server AND Details LIKE @.keyword
UNION ALL
SELECT *
FROM [Prtracker_Arizona State Edition].[dbo].[Problem Re
ports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
... // And so on for 15 more databases
RETURN
GOHi,
I am getting the same error using DWMX 2004 / ASP / VB.
Did you found a workaround ?
If so, your help is greatly apreciated.
axiaxi2003@.hotmail.com
A
amucino
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message466748.html

Connection string problem

I'm converting a simple Access application to ASP.NET with MSDE to run on our Intranet. The app has just one page with 2 SqlDataSource controls. Initially I configured the data source to connect directly to the .mdb file. Once I got the code running, I used the "Upsize Wizard" in Access to convert the data to a SQL database using the MSDE instance running on my web server. I then re-configured the SqlDataSources to connect to the MSDE database.

I am able to step all the way through the configuration wizard, and when I test the query, the correct data is returned. However, when I run the app (from within Visual Web Developer Express) I get the following exception when I bind a drop-down list to one of the SqlDataSources:

An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.

The connection string generated by the Wizard in my web.config is this:

<

addname="ConnectionString"connectionString="Data Source=BCFWEB01;Initial Catalog=InOut;User ID=XXX;Password=XXXXXXXXX"providerName="System.Data.SqlClient" />

Any idea why its complaining that I don't have an OLE DB provider, and why the query works fine from the Configuration Wizard but not when I run the page?

Thanks,

what is your code to bind your datasource? This errors mostly occurs when you are using OleDb connections in your code, and you have an Sql connection in your web.config file.|||Problem went away when I deleted & recreated the data source. I no longer have the code that didn't work :) but I suspect that's what the issue was. Thanks for the help!

Connection property not initialized

Hi,

I'm trying to do a database operation in ASP.NET page using the following code:

string connString = "SERVER=localhost;DATABASE=chbr;UID=sa;PWD=password;Connection Timeout=120";
SqlConnection sqlConn = new SqlConnection(connString);
sqlConn.Open();

string commandStr = "...";

SqlCommand command = new SqlCommand(commandStr);

command.ExecuteNonQuery();
sqlConn.Close();

But I kept getting the exception saying "ExecuteNonQuery: Connection property has not been initialized."

What did I do wrong?

Thanks!

You should be doing as much of the "busy work" as possible before you open the connection. Also, you should use either the using statement or a try / finally block.You did not specify which SQL connection the SqlCommand is supposed to use, which is why you got that error.

SqlConnection sqlConn = null;

try
{
string connString = "SERVER=localhost;DATABASE=chbr;UID=sa;PWD=password;Connection Timeout=120";
sqlConn = new SqlConnection(connString);

string commandStr = "...";

SqlCommand command = new SqlCommand(commandStr, sqlConn);

sqlConn.Open();
command.ExecuteNonQuery();
}
finally
{
if (sqlConn != null)
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}

Connection problem

I have connection to MSSQL server.
When I read the data from the database, I close the connection (conn.Close() and conn.Dispose()).
After several page loads, it gets an error:
Database error. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I found the way to fix this by compiling the webapp in release, butagain, after several page loads problem still occurs. Then I have tocompile it in debug and over and over again.
This is somehow OK if Iam on local so I can do the compile, but if Iam on the net (my web page) I cant do this.
I realised that when I make a change to a connection string (connectionstring is in appSettings in web.config) I don't get the above error(just like I copiled the project).
Can I some how prevent this error to occur?
Can I use Pooling=false in connection string to solve the problem?
Hi there,
to me it seems that your application is not closing SQL connections properly... I will advie to go over your code again..
In some cases you may have a function which is returning values fromdatabase if you return a value even before you close the connection,your SQL connection will still remain active... this is just a smallpointer for you to get started.
Cheers!
|||Thanks for the reply.
I will take a look at the code, but Iam convinced that I have closed the connection every time I use it.
About Pooling=false in ConnectionString. I tried to load a page inlocalhost many time in a roud, but nothing happens, no error, butstill, I want to use the ConnectionString without Pooling=false.
As I said, I will take a look at the code again.
Thanks mate

|||I have put the Pooling=false in ConnectionString and error doesen't appear anymore until I see where the problem.