Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Exclamation Unanswered: Recordset does not open with Table Variables

    Hello!

    I have made an stored procedure that receives 2 parameters and returns a resultset. The resultset is populated from a select made from a table variable declared on that procedure:

    select * from @MyTable

    Now, the stored procedure works as expected when invoked on the Query Analizer; but when using a Visual Basic application that uses ADO 2.7, the Recordset object does not open.

    What is wrong?

    Thanks a lot in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Any error?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Any error?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2003
    Posts
    144

    Arrow

    Thanks for your reply.

    The recordset object does not open; however, a runtime error does not occur.

    The visual basic code is very straightforward and has been used with other kind of stored procedures:

    Public Sub LoadData()
    on error goto E:
    Dim objConnection As ADODB.Connection
    Dim objCommand As ADODB.Command
    Dim objRecordset As ADODB.Recordset
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.ConnectionString = m_strConnectionString
    objConnection.Open
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "myStoredProcedure"
    objCommand.Parameters("@myParameter1").Value = m_varValue1
    objCommand.Parameters("@myParameter2").Value = m_varValue2

    Set objRecordset = objCommand.Execute
    If objCommand.Parameters(0).Value = 0 Then
    do while not objRecordset.EOF
    debug.print objRecordset!myField1
    objRecordset.MoveNext
    loop
    End If
    Exit sub
    E:
    MsgBox Err.Description

    End Sub

    I do not understand why the recordset is not opened. The only difference with other kind of stored procedures that I have used is that the SELECT statement is made from a Table variable:

    select * from @MyTable

    The Query Analyzer returns values.

    What could be wrong?

  5. #5
    Join Date
    Mar 2003
    Posts
    144

    Talking I solved the problem!

    When using Table variable or Temp tables, it is necesary to write "SET NOCOUNT ON" on the top of the stored procedure.

    I read that in "PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB", a Microsoft Knowledge Base Article (235340).

    http://support.microsoft.com/support.../Q235/3/40.ASP

    :-D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •