ADODB.command is not executing stored procedure

  • Is anything wrong with the below code. Stored procedure is returning -1 rows when executed from command. Please help...

    Function Main()

    dim myConn

    dim Rs1

    dim Cmd1

    ' instantiate the ADO objects

    set myConn = CreateObject("ADODB.Connection")

    set Cmd1 = CreateObject("ADODB.Command")

    set Rs1 = CreateObject("ADODB.Recordset")

    myConn.Open = "Provider=SQLOLEDB.1;Data Source= Server; Initial Catalog= NorthWind; user id = 'uid'; password='pwd'"

    Cmd1.ActiveConnection = myConn

    Cmd1.CommandType = 4

    Cmd1.CommandText = "test_sp"

    Set Rs1 = Cmd1.Execute

    msgbox Rs1.RecordCount

    Main = DTSTaskExecResult_Success

    End Function

  • Does uid.test_sp exist in the database NorthWind? And does uid has execute permissions on uid.test_sp?

  • Also - perhaps try setting NOCOUNT on in your stored proc. Messages being returned might be getting misinterpreted too.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks for the inputs...

    The sp is in the db and it has access to execute the package. I am using SET NOCOUNT ON while creating the package.

  • The stored procedure calls another package?

    Have you added some debugging routines in the package to see if it actually executes?

  • Yes, it does call another package

  • sat.offers (5/30/2008)


    Is anything wrong with the below code. Stored procedure is returning -1 rows when executed from command. Please help...

    Function Main()

    dim myConn

    dim Rs1

    dim Cmd1

    ' instantiate the ADO objects

    set myConn = CreateObject("ADODB.Connection")

    set Cmd1 = CreateObject("ADODB.Command")

    set Rs1 = CreateObject("ADODB.Recordset")

    myConn.Open = "Provider=SQLOLEDB.1;Data Source= Server; Initial Catalog= NorthWind; user id = 'uid'; password='pwd'"

    Cmd1.ActiveConnection = myConn

    Cmd1.CommandType = 4

    Cmd1.CommandText = "test_sp"

    Set Rs1 = Cmd1.Execute

    msgbox Rs1.RecordCount

    Main = DTSTaskExecResult_Success

    End Function

    How many rows does the test_sp return when you run it from the stored procedure? Maybe you need to tell us more about the stored proc. Your function here looks ok to me.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply