July 4, 2006 at 9:18 am
Hi, I'm creating a SP with a transaction involving tables in 2 different databases. When I run the SP from Query Analyzer, it works fine, but when it is called from a .NET program being developed by someone else, I get the error that a transaction is still open. I'm using the exact same parameters in both cases.
Any ideas?
July 4, 2006 at 2:55 pm
Could be of a connection setting implicit transaction on/off. Sure that any begin transaction has a matching rollback or commit transaction statement?
Have you checked your stored procedure with microsoft best practices analyzer for sql server (http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en)?
July 5, 2006 at 7:37 am
Its not your SQL.
You should have them add this to the .NET app. Since there are several methods in ADO.NET to open a database connection (sqlCommand, DataReader, DataAdapter), the connection state may need to be cleaned up after or before. Usually SqlCommand and DataAdapter connections are opened and closed by the ADO.NET, but the DataReader needs to be opened and closed as well as the database connection.
'add this bloc
If conn.ConnectionState = Connection.Open Then
conn.close()
End IF
'end of bloc, and should immediately precede this statement
conn.open()
reader.open()
while reader.read()
'read one line at a time
End while
reader.close()
conn.close()
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply