none
ODBC and SQL Job execution RRS feed

  • Question

  • I am trying to execute a MSSQL Job through ODBC.

     

    I have this code:

     

              CmdODBC.CommandText = "{CALL sp_start_job (?,?)}"
    
              Dim jobReturnValue As OdbcParameter = CmdODBC.Parameters.Add("@RETURN_VALUE", OdbcType.Int)
              CmdODBC.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
    
              Dim jobParameter As OdbcParameter = CmdODBC.Parameters.Add("@job_name", OdbcType.VarChar)
              CmdODBC.Parameters("@job_name").Direction = ParameterDirection.Input
              CmdODBC.Parameters("@job_name").Value = "test"
              CmdODBC.Parameters("@job_name").Size = 4
            CmdODBC.ExecuteNonQuery()
            Dim jobResult As Integer = DirectCast(CmdODBC.Parameters("@RETURN_VALUE").Value, Integer)


    But the execution gives me an error:

     

    ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The formal parameter "@job_name" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

    It seems like executing through ODBC is a bit different. Any insight on the error?

    <script type="text/javascript"></script> <script type="text/javascript"></script>
    Monday, January 31, 2011 7:15 PM

Answers

All replies

  • It doesn't look like the ODBC call escape sequence is correct. The output parameter should be at the front of the statement:

    http://msdn.microsoft.com/en-us/library/aa198007(v=sql.80).aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, January 31, 2011 8:18 PM
  • Hmm.. I made this change:

     

              CmdODBC.CommandText = "{CALL sp_start_job (?,)}"
    
    
              Dim jobParameter As OdbcParameter = CmdODBC.Parameters.Add("@job_name", OdbcType.VarChar)
              CmdODBC.Parameters("@job_name").Direction = ParameterDirection.Input
              CmdODBC.Parameters("@job_name").Value = "test"
              CmdODBC.Parameters("@job_name").Size = 4
    
              Dim jobReturnValue As OdbcParameter = CmdODBC.Parameters.Add("@RETURN_VALUE", OdbcType.Int)
              CmdODBC.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
    			CmdODBC.ExecuteNonQuery()
        		Dim jobResult As Integer = DirectCast(CmdODBC.Parameters("@RETURN_VALUE").Value, Integer)
    
    
    
    Now, it does not complain with an error but RETURN_VALUE is db_null and ExecuteNonQuery always return -1 even though it is a success. What am I doing wrong?

    Tuesday, February 1, 2011 10:13 AM
  • According to the documentation, the correct sequence is:

    {? = CALL sp_start_job (?)}
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, February 1, 2011 1:02 PM
  • I found an example, which looks like it should work with both a return value and an output parameter. Perhaps the problem is how the the stored procedure output parameter is defined in the sp.

    How To Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual Basic .NET

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, February 1, 2011 1:08 PM
  • Excellent, moved around the code and it finally worked when using it like this. Thanks!

     

              CmdODBC.CommandText = "{? =CALL sp_start_job (?)}"
    
              Dim jobReturnValue As OdbcParameter = CmdODBC.Parameters.Add("@RETURN_VALUE", OdbcType.Int)
              CmdODBC.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
    
              Dim jobParameter As OdbcParameter = CmdODBC.Parameters.Add("@job_name", OdbcType.VarChar)
              CmdODBC.Parameters("@job_name").Direction = ParameterDirection.Input
              CmdODBC.Parameters("@job_name").Value = "Test"<br/>
     CmdODBC.Parameters("@job_name").Size = 4
    

    Tuesday, February 1, 2011 1:34 PM