SqlCommand.ExecuteNonQueryを使用して実行した戻り値はいつも-1になってしまう

Question

今困ってるところなんです。心当たりがある方、助けて
　　開発現場ではワーク環境と本番環境のように二つ分けられた（構成は両方ともWebServer + SqlServer2005）。同じソースは開発環境でちゃんと1か0か返ってきた一方、本番環境中では掲題どおり更新操作の成功いかんに関らず、すべて戻り値は-1として戻っていた。両方のソースはぜんぜん同じですが、もしかしてSqlServerの構成はどこか違いがあるのか？たとえば、DBサーバーのログイン権限とか、戻り値が取れるようにSqlServer2005側も必要な設定あるとか・・・わかんないんですよね

一応ソースを下のよう貼り付けとく。（注：更新操作のトランザクション制御はビジネスロジックレイヤーで行う）
        　''' <summary>
        ''' 更新系コマンドを初期化
        　''' </summary>
        ''' <param name="hashParam"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function InitUpdateCommand(ByVal hashParam As Hashtable) As DbCommand

            Dim dbCmd As DbCommand

            dbCmd = GetCommand()
            ' 承認済
            dbCmd.CommandText = "UPDATE E_AA_SinsJH " & _
                        "SET SinsJtai = @SinsJtai, " & _
                            "SynsID = @SynsID, " & _
                            "SynsNM = @SynsNM, " & _
                            "SynsDY = @SynsDY " & _
                        "WHERE SinsNO = @SinsNO AND SinsJtai = @SinsJtai_Old; " & _
                        "SET @RowAffected = @@ROWCOUNT "
           
            dbCmd.Parameters.Add(GetParameter("@SynsID", DbType.AnsiString, 15, "SynsID"))
            dbCmd.Parameters("@SynsID").Value = hashParam("SynsID")
            dbCmd.Parameters.Add(GetParameter("@SynsNM", DbType.String, 30, "SynsNM"))
            dbCmd.Parameters("@SynsNM").Value = hashParam("SynsNM")
            dbCmd.Parameters.Add(GetParameter("@SynsDY", DbType.DateTime, 4, "SynsDY"))
            dbCmd.Parameters("@SynsDY").Value = hashParam("SynsDY")
            dbCmd.Parameters.Add(GetParameter("@SinsNO", DbType.Int32, 4, "SinsNO"))
            dbCmd.Parameters("@SinsNO").Value = hashParam("SinsNO")
            dbCmd.Parameters.Add(GetParameter("@SinsJtai", DbType.Byte, 1, "SinsJtai"))
            dbCmd.Parameters("@SinsJtai").Value = hashParam("SinsJtai")
            dbCmd.Parameters.Add(GetParameter("@SinsJtai_Old", DbType.Byte, 1, "SinsJtai"))
            dbCmd.Parameters("@SinsJtai_Old").Value = hashParam("SinsJtai_Old")
           
            Return dbCmd

        End Function
       
        ''' <summary>
        ''' 条件による更新
       　 ''' </summary>
        ''' <param name="hashParam"></param>
        ''' <remarks></remarks>
        Public Sub UpdateE_AA_SinsJH(ByVal hashParam As Hashtable)

            Dim dbCmd As DbCommand
            Dim rowAffected As Integer = 0

            Try
                ' DBコレクション接続
                OpenConnection()

                dbCmd = InitUpdateCommand(hashParam)
                rowAffected = dbCmd.ExecuteNonQuery()

            Catch dbEx As DbException

                Throw HandleDbException(dbEx)

            Finally
                ' DBコレクション切断
                CloseConnection()

            End Try

            If rowAffected = 0 Then Throw New DbCrossUpdateAppException()

        End Sub

Answer 1

詳しく調べてないんですが、バッチクエリにする必要がないんじゃないでしょうか？　つまり、SET @RowAffected = @@ROWCOUNTを削ったらどうなりますでしょうか？

でも、テスト環境ではうまく取得できてたんですよね。

Answer 2

SET @RowAffected = @@ROWCOUNTの行は投稿時に、削り忘れちゃったため、残ってあります。このバージョンのソース中にないんです。
実は、戻り値を取得する作りこみが下のよう改修してみれば、二つの環境でよく動いてきた。

        ''' <summary>
        ''' 更新系コマンドを初期化
        ''' </summary>
        ''' <param name="hashParam"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function InitUpdateCommand(ByVal hashParam As Hashtable) As DbCommand

            Dim dbCmd As DbCommand

            dbCmd = GetCommand()
            ' 承認済
            dbCmd.CommandText = "UPDATE E_AA_SinsJH " & _
                        "SET SinsJtai = @SinsJtai, " & _
                            "SynsID = @SynsID, " & _
                            "SynsNM = @SynsNM, " & _
                            "SynsDY = @SynsDY " & _
                        "WHERE SinsNO = @SinsNO AND SinsJtai = @SinsJtai_Old; " & _
                        "SET @RowAffected = @@ROWCOUNT "
           
            dbCmd.Parameters.Add(GetParameter("@SynsID", DbType.AnsiString, 15, "SynsID"))
            dbCmd.Parameters("@SynsID").Value = hashParam("SynsID")
            dbCmd.Parameters.Add(GetParameter("@SynsNM", DbType.String, 30, "SynsNM"))
            dbCmd.Parameters("@SynsNM").Value = hashParam("SynsNM")
            dbCmd.Parameters.Add(GetParameter("@SynsDY", DbType.DateTime, 4, "SynsDY"))
            dbCmd.Parameters("@SynsDY").Value = hashParam("SynsDY")
            dbCmd.Parameters.Add(GetParameter("@SinsNO", DbType.Int32, 4, "SinsNO"))
            dbCmd.Parameters("@SinsNO").Value = hashParam("SinsNO")
            dbCmd.Parameters.Add(GetParameter("@SinsJtai", DbType.Byte, 1, "SinsJtai"))
            dbCmd.Parameters("@SinsJtai").Value = hashParam("SinsJtai")
            dbCmd.Parameters.Add(GetParameter("@SinsJtai_Old", DbType.Byte, 1, "SinsJtai"))
            dbCmd.Parameters("@SinsJtai_Old").Value = hashParam("SinsJtai_Old")
            dbCmd.Parameters.Add(GetParameter("@RowAffected", DbType.Int32, 4, "RowAffected"))
            dbCmd.Parameters("@RowAffected").Direction = ParameterDirection.Output

            Return dbCmd

        End Function
       
        ''' <summary>
        ''' 条件による更新
        ''' </summary>
        ''' <param name="hashParam"></param>
        ''' <remarks></remarks>
        Public Sub UpdateE_AA_SinsJH(ByVal hashParam As Hashtable)

            Dim dbCmd As DbCommand
            Dim rowAffected As Integer = 0

            Try
                ' DBコレクション接続
                OpenConnection()

                dbCmd = InitUpdateCommand(hashParam)
                dbCmd.ExecuteNonQuery()
                rowAffected = CInt(dbCmd.Parameters("@RowAffected").Value)

            Catch dbEx As DbException

                Throw HandleDbException(dbEx)

            Finally
                ' DBコレクション切断
                CloseConnection()

            End Try

            If rowAffected = 0 Then Throw New DbCrossUpdateAppException()

        End Sub