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.Any error?|||Any error?|||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?|||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/kb/articles/Q235/3/40.ASP

:-D

Records don't lock-one user receives error

I created a MSAccess application with the forms in one db (front end) and the tables in another db (back end). The network admin migrated the back end to SQL Server. With a few minor changes (changing -1 to 1 in queries, etc.) the migration seemed to go pretty smoothly and everything works fine except....

The MSAccess backend used to lock records when someone was editing them. The status of the record was indicated in the record selector on each form, a right pointing arrow to indicate the record was free, a pencil with three dots indicating the record was locked by the current user, and a circle with a diagonal line to indicate the record was locked by another user. When 2 users attempted to simultaneously edit a record, the first user got the lock on the record. The second user received a message as soon as they attempted to edit the locked record to the effect that the record was locked.

Now (since the migration to SQL Server) the record selectors no longer give any indication as to the locking status of the records. When 2 users attempt to simultaneously edit a record, both are able to enter information in their forms, but when the second user attempts to save the record they receive the following message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Please help me find the cause of this behavior. Is it something I need to change in the MSAccess front end or something that needs to be set in the SQL Server back end by the network admin?

I am moving this question to the SQL Server Database Engine forum.