SQL Server Performance Forum – Threads Archive
Stored procedures won’t work properly…
Here is an issue…Im programming an application which accesses a SQL server 2000 sp4 and runs some stored procedures. The code is written in Visual Basic.NET (under visual studio.NET and .NET framework 1.1.*). The problem is that the stored procedures doesn’t work as expected. I know for sure that they are written well, and that is because I used the Visual Studio.NET built-in "Run procedure" command (under the server explorer>myDB>stored procedures(context menu>Run procedure) ).
And im pritty sure that the code is written good as well (or maybe not!!)
I also used the SQL server 2000 profiler which showed me some differences in the actions sequence. The main difference in my opinion is the "Application Name"… Which is "Microsoft Visual Studio" when I run it from there, and "MS SQLME" when I run the application and it accesses the SQL server from the system as a stand alone application. I’ve been adviced to check the LocalSystem acount for NT permission, because there is a chance that it is not permitted to write to the disk, unlike the account used by MSdevEnv. Can anyone explain to me what’s going on here??? And what are the steps that I should do to give access to my DB??? Note: The stored procedure return -1, and the profiler shows as if the procedure perfomed a "Rollback"
What error are you getting???? You didn’t menetion that anywhere except to say the procedure returns -1. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
No errors nor exceptions are raised
I only get that -1 as an indication that no rows are affected.
This is not a programming error…
This is a system performance of the SQL server error as I understood for now.
I found this article at Microsoft TechNet:
How to change the SQL Server or SQL Server Agent Service account without using SQL Enterprise Manager in SQL Server 2000
Correction: The link is broken try to copy paste the text and you’ll find it first thing in Google!!
Am I in the right direction??
Did you click on that link after you posted it? [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />It’s kind of hard to troubleshoot without seeing the query. Do you have access to the stored procedure and can you post the code for it? How is the application getting a return value of -1 back from the procedure? Also, if something is being rolled back, then something caused the rollback to actually occur. That would mean either an error occurred, you programmed something in the proc to cause the rollback, or your application started the rollback. So, either you have a database error or you have a programming error. There is no system performance problem.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Yes I guess you’re right it is a database error, apperently caused by some permission error. Not that I can see that anywhere, but still when the application running the sp is ".Net SqlClient Data Provider" (according to the profiler) the job isn’t done properly and a rollback occurs. However when the aplication is "Microsoft Visual Studio .NET", only then it works well. Also check this out…
Snapshot from Profiler
The snapshot shows three main differences marked with red rectangles:
- ApplicationName
- The NTUserName + LoginName
- And the TextData
Notice that in the above (The good one) it says: exec dbo."ProductsInsert" 101,N"’not a ket’",0,1…
And below it adds the sqlParameters: exec ProductsInsert @prodID = 101, @productKey = N"’not a ket’",…
Note: for the connection I used a fully trusted connectionString (with the SSPI)
And here is my VB.NET code: Public Overloads Shared Sub ExecuteNonQuery(ByVal connection As SqlConnection, ByVal transaction As SqlTransaction, ByVal commandText As String, ByVal ParamArray parameters() As SqlParameter)
Dim state As ConnectionState = connection.State
Dim command As SqlCommand = New SqlCommand
‘ Initialize the command
command.Connection = connection
command.CommandText = commandText
command.CommandType = CommandType.StoredProcedure
command.Transaction = transaction
‘ Append each parameter to the command
For Each parameter As SqlParameter In parameters
command.Parameters.Add(parameter)
Next
‘ Open the database connection if it isn’t already opened
If (state = ConnectionState.Closed) Then
connection.Open()
End If
Try
‘ Execute the query
Dim NofRows As Integer
NofRows = command.ExecuteNonQuery() <—- returns (-1)
Console.WriteLine("Number of rows affected : " + NofRows.ToString)
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
‘ If the database connection was closed before the method call, close it again
If (state = ConnectionState.Closed) Then
connection.Close()
End If
End Try
End Sub
Hi, Just a couple of things I’ve noticed. First of all, when you run the procedure using Visual Studio.NET built-in "Run procedure" command you’re not running the same thing that the application is running. Look at the profiler output you provided. They’re completely different values in the parameters suggesting that the procedure is performing the rollback itself perhaps? In the first instance, the value of @productKey is "Not a Key". Whereas in the second instance it’s got a proper value. If you’re going to run a test then run the test so that the variables are the same on both counts otherwise the test is not valid. Secondly, I’ve noticed you’re declaring command.Transaction but you’re not commiting it anywhere. Which means as a result that when you close the connection in the Finally block it’s going to get rolled back. Here’s what you should be doing: Cmd.Transaction = transaction …
… Catch transaction.rollback ‘doesn’t hurt to be explicit Finally ‘if there were no errors
transaction.commit connection.close
End Try
quote:Originally posted by Bilal Issa
Yes I guess you’re right it is a database error, apperently caused by some permission error. Not that I can see that anywhere, but still when the application running the sp is ".Net SqlClient Data Provider" (according to the profiler) the job isn’t done properly and a rollback occurs. However when the aplication is "Microsoft Visual Studio .NET", only then it works well.
Also check this out…
Snapshot from Profiler
The snapshot shows three main differences marked with red rectangles:
Notice that in the above (The good one) it says: exec dbo."ProductsInsert" 101,N"’not a ket’",0,1…
And below it adds the sqlParameters: exec ProductsInsert @prodID = 101, @productKey = N"’not a ket’",…
Note: for the connection I used a fully trusted connectionString (with the SSPI)
And here is my VB.NET code: Public Overloads Shared Sub ExecuteNonQuery(ByVal connection As SqlConnection, ByVal transaction As SqlTransaction, ByVal commandText As String, ByVal ParamArray parameters() As SqlParameter)
Dim state As ConnectionState = connection.State
Dim command As SqlCommand = New SqlCommand
‘ Initialize the command
command.Connection = connection
command.CommandText = commandText
command.CommandType = CommandType.StoredProcedure
command.Transaction = transaction
‘ Append each parameter to the command
For Each parameter As SqlParameter In parameters
command.Parameters.Add(parameter)
Next
‘ Open the database connection if it isn’t already opened
If (state = ConnectionState.Closed) Then
connection.Open()
End If
Try
‘ Execute the query
Dim NofRows As Integer
NofRows = command.ExecuteNonQuery() <—- returns (-1)
Console.WriteLine("Number of rows affected : " + NofRows.ToString)
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
‘ If the database connection was closed before the method call, close it again
If (state = ConnectionState.Closed) Then
connection.Close()
End If
End Try
End Sub
Snapshot from Profiler
The snapshot shows three main differences marked with red rectangles:
- ApplicationName
- The NTUserName + LoginName
- And the TextData
Notice that in the above (The good one) it says: exec dbo."ProductsInsert" 101,N"’not a ket’",0,1…
And below it adds the sqlParameters: exec ProductsInsert @prodID = 101, @productKey = N"’not a ket’",…
Note: for the connection I used a fully trusted connectionString (with the SSPI)
And here is my VB.NET code: Public Overloads Shared Sub ExecuteNonQuery(ByVal connection As SqlConnection, ByVal transaction As SqlTransaction, ByVal commandText As String, ByVal ParamArray parameters() As SqlParameter)
Dim state As ConnectionState = connection.State
Dim command As SqlCommand = New SqlCommand
‘ Initialize the command
command.Connection = connection
command.CommandText = commandText
command.CommandType = CommandType.StoredProcedure
command.Transaction = transaction
‘ Append each parameter to the command
For Each parameter As SqlParameter In parameters
command.Parameters.Add(parameter)
Next
‘ Open the database connection if it isn’t already opened
If (state = ConnectionState.Closed) Then
connection.Open()
End If
Try
‘ Execute the query
Dim NofRows As Integer
NofRows = command.ExecuteNonQuery() <—- returns (-1)
Console.WriteLine("Number of rows affected : " + NofRows.ToString)
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
‘ If the database connection was closed before the method call, close it again
If (state = ConnectionState.Closed) Then
connection.Close()
End If
End Try
End Sub
Karl Grambow www.sqldbcontrol.com
Hi, A big missunderstanding regarding the first note,
the @productKey value I used there was a simple innocent string "not a key",
and it had nothing to do with a PK of the products table.
The parameters are not the issue here,
ofcourse I’ve also tried similar values (just to be sure im not missing a thing!!) About the second Note, Im not such a wise guy you know,
I only followed the MSDN help about how to use these methods.
But yet, I used the commit sub, and guess what?!
Im still stuck with no effect in the DB. Something must have went wrong in the connection to the DB,
Is it the framework? Maybe…
Is it the connection that the IIS* uses to access the database? Maybe…
Is it an issue of permissions given to the envolved working processes? Maybe…
Or is it a bug? I don’t know!!
Service packs? Had them all!
Permissions? I gave the devil full access permissions on the SQL Server…
Any suggestions??
Looking at the output of profiler you can see that a rollback transaction is issued from the .NET provider, so therefore it is unlikely to be bug but the way in which the code has been written on that back-end. Unless you have a very specific need for controlling the transaction from the application layer I would suggest you remove all references to "transaction" in your code. Even if you do need it in the application layer, I would suggest removing it for testing purposes if nothing else. Hope that helps,
quote:Originally posted by Bilal Issa
Hi,
A big missunderstanding regarding the first note,
the @productKey value I used there was a simple innocent string "not a key",
and it had nothing to do with a PK of the products table.
The parameters are not the issue here,
ofcourse I’ve also tried similar values (just to be sure im not missing a thing!!) About the second Note, Im not such a wise guy you know,
I only followed the MSDN help about how to use these methods.
But yet, I used the commit sub, and guess what?!
Im still stuck with no effect in the DB. Something must have went wrong in the connection to the DB,
Is it the framework? Maybe…
Is it the connection that the IIS* uses to access the database? Maybe…
Is it an issue of permissions given to the envolved working processes? Maybe…
Or is it a bug? I don’t know!!
Service packs? Had them all!
Permissions? I gave the devil full access permissions on the SQL Server…
Any suggestions??
the @productKey value I used there was a simple innocent string "not a key",
and it had nothing to do with a PK of the products table.
The parameters are not the issue here,
ofcourse I’ve also tried similar values (just to be sure im not missing a thing!!) About the second Note, Im not such a wise guy you know,
I only followed the MSDN help about how to use these methods.
But yet, I used the commit sub, and guess what?!
Im still stuck with no effect in the DB. Something must have went wrong in the connection to the DB,
Is it the framework? Maybe…
Is it the connection that the IIS* uses to access the database? Maybe…
Is it an issue of permissions given to the envolved working processes? Maybe…
Or is it a bug? I don’t know!!
Service packs? Had them all!
Permissions? I gave the devil full access permissions on the SQL Server…
Any suggestions??
Karl Grambow www.sqldbcontrol.com
GOOD NEWS!!! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Finally I’ve found it!!!<br /><b>It was a huge programming error.</b><br /><br />Look at this line of code:<br /><pre id="code"><font face="courier" size="2" id="code"><font color="red">Dim command As SqlCommand = New SqlCommand</font id="red"></font id="code"></pre id="code">The newly created SqlCommand is not related to the SqlTransaction object,<br />even though it is set in the next lines of code…<br /><br />What happens is that the SqlTransaction tends to respond to its own created commands!!<br />i.e. even though you can set any SqlCommand to be your SqlTransaction’s command,<br />it will not respond to it as if it was its own! <br />(Someone should explain why does this happen)<br /><br />In software design such a thing could have been prevented if the designer thought of the "Factory design pattern", perventing any external creation of the command object,<br />and making the SqlTransaction the only provider of its own commands.<br /><br />The correction to the code is:<pre id="code"><font face="courier" size="2" id="code"><font color="green">Dim command As SqlCommand = connection.<b>CreateCommand()</b></font id="green"></font id="code"></pre id="code">For conclusion:<ul><li>Never rely on someone else’s "ready to use" code. </li><li>Never fully rely on microsoft’s software designers’ software designs. </li><li>And also read the help lines more carefully. </li></ul>Following are the remarks from the documentation page of the SqlCommand.Transaction Property:<br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Remarks<br />You cannot set the Transaction property if it is already set to a specific value, and the command is in the process of executing. If you set the transaction property to a SqlTransaction object that is not connected to the same SqlConnection as the SqlCommand object, an exception will be thrown the next time you attempt to execute a statement.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hope this helped/will help someone else.[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />
]]>