none
ExecuteNonQuery hangs up RRS feed

  • Question

  • We have a .NET 2.0  windows application that accesses a SQL 2000 database, which I will call DB1. The app calls  stored proc pSP1 in DB1 using the ExecuteNonQuery method. 

     

    pSP1 looks something like this:

     

    ALTER Procedure [dbo].[pSP1] (@CompanyNumber VARCHAR(4), @RoleName VARCHAR(50), @AppShortName VARCHAR(50))
    AS
     
    -- calls a proc on a linked SQL 2005 server
    EXEC [LNK-SQLDEV03].DB2.dbo.pSP2 @CompanyNumber, @RolwName, @AppShortName
    

    Simply, pSP1 is calling another stored proc on a linked server.  The linked sever happens to be a SQL 2005 box.

     

     

    pSP1 succeeds when run from the SQL Management Studio environment,  but when the proc is called from the .NET app the code reaches the call to ExecuteNonQuery and hangs up. When DB2 resided on a SQL 2000 box the call from the .NET app also succeeded.  So the problem only presented itself when the database was migrated to SQL 2005.

     

    Here's the code in the app that executes the proc:

     

    Function modifyData(ByVal ProcName As String, _

    ByVal Params As IDataParameter()) As Integer

    mCommand = New SqlCommand

    Dim RowsAffected As Integer = -1

    Dim Param As SqlParameter

    CType(mCommand, SqlCommand).CommandType = CommandType.StoredProcedure

    CType(mCommand, SqlCommand).CommandTimeout = 30

    CType(mCommand, SqlCommand).Connection = CType(mConnection, SqlConnection)

    If mCommand.Connection.State = ConnectionState.Closed Then

    mCommand.Connection.Open()

    End If

    Dim MyTransaction As SqlTransaction = _

    CType(mConnection, SqlConnection).BeginTransaction(IsolationLevel.RepeatableRead, "Modification")

    CType(mCommand, SqlCommand).Transaction = MyTransaction

     

    CType(mCommand, SqlCommand).Parameters.Clear()

    For Each Param In Params

    CType(mCommand, SqlCommand).Parameters.Add(Param)

    Next

    Try

    CType(mCommand, SqlCommand).CommandText = ProcName

    RowsAffected = CType(mCommand, SqlCommand).ExecuteNonQuery

    MyTransaction.Commit()

    Catch e As Exception

    MyTransaction.Rollback("Modification")

    Throw e

    RowsAffected = 0

    If CType(mConnection, SqlConnection).State <> ConnectionState.Closed Then

    CType(mConnection, SqlConnection).Close()

    End If

    End Try

    Return RowsAffected

    End Function

     

    After initially thinking the problem was in with ExecuteNonQuery, I discovered that it actually is realted the the SqlTransaction.  By removing the transaction code the proc will execute successfully.  We're not all that eager to change the code, but if there is some limitation that prevents this from working, we'll probably have no choice.  But if someone has some explanation about what is going on and if there is something we can do without removing the transaction, I would appreciate the feedback. 

     

    Friday, March 7, 2008 3:02 PM

All replies

  • We've migrated code from 1.1 to 2.0 and are having the same problem on ExecuteNonQuery in 2.0 on a DB2 database.  Our problem only occurs (at this point) when we try to execute an insert statement that creates a constraint violation.  The database should throw a data access error and our application handles the error accordingly, at least it did in the 1.1 version.  In 2.0, the only error that occurs (after an exceptionally long period of time) is MemoryOutOfException due to it bringing the db down. 

     

    When an insert is done with data that doesn't produce a constraint violation, there is no issue in 1.1 or 2.0 on the ExecuteNonQuery.  Nothing has changed with the code and when I run the code against the same database using the 1.1 version I still get the desired result of a data access error. 

     

    Have researched but not found anything concrete.  Any thoughts definitely appreciated. 

    Wednesday, March 12, 2008 10:13 PM
  • mtock99,

     

    Unfortunately, the DB2 that the original poster was refering to in this post was a reference to the second database that they upgraded to SQL 2005.

     

    As for the original issue, have you checked to see that MSDTC is running on both of the servers?  Also in the SQL Server Properties, see if the check box Require distributed transactions for server to server connections is checked on either server?  It is under connections when you right click on the server root and goto properties.

     

    One other thing you can try is to see if you can install the SQL 2005 Tools onto the SQL 2000 Server.  No, they won't create a problem coexisting with the SQL 2000 Tools.  You will likely need to reboot the server after patching them, but maybe not.  Then try creating the linked server using the SQL Native Client.

     

    Friday, March 14, 2008 1:45 AM
  • Although we also do not have SQL Server or Access, I still need to add a post here. We're using DB2 as well and experiencing the exact same problem. On inserting values that generate a constraint violation, de OleDbCommand hangs up. Our DBA monitored the database and noticed that the insert in this case was being executed time after time again! As if it was entering an infinite loop!
    I will post this issue on IBM's support site and see if they have a solution because something smelly is definately going on ;-)

    So apologies for not responding to the original post. But this was my first real hit on searching for help.
    Thursday, July 17, 2008 2:11 PM
  • I would first run SQL Profiler on both SQL Servers to ensure that the linked server query is reaching the 2nd server and also to see what is different between the two runs.  Ensure you trace locks and transactions and errors so you don't miss anything in the trace.

     

    If you see an error in this SQL Profiler trace, post back with the error.

     

    Also, have you tried different isolation levels, do you need IsolationLevel.RepeatableRead?

     

     

     

    Friday, July 18, 2008 12:14 AM