getting recordset via ado/sproc

Trying to get output from sqlserver stored procedure into access recordset via ado.

I have stored procedure with select statement that returns rows when executed from query analyser:

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

When I execute following bit of code from Access, the set rst = cmd.execute runs ok, but the rst.movefirst gets error 3704, Operation is not allowed when the object is closed.

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mcnnSQLDB
cmd.CommandText = "ps_tblMackVendors_Select_test"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
rst.MoveFirst

I have tested the stored procedure & the connection using an output parameter, doing a cmd.execute (without the rst..) & the value is retrieved by access okprobably you need to rethink your app structure before you go too far with it. i bet your rst will successfully movefirst if you just do a straight select from your sp.|||My question would be why do you have

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

instead of,...

CREATE procedure ps_tblMackVendors_Select_test
AS
Select * from tblMackVendors
GO

???|||well, i understand he's just experimenting, getting ready for the real stuff, but i'm saying "don't go that route, there is a sign there - no outlet!"|||What happens if you open a recordset using a sql string instead of a stored procedure ? Also, you should debug your vb code and view the locals to see what is going on with the rst object.|||my sp uses string for qry because I want to build the sql from values passed to the sp in the final version.
I did just try it without the string for the sql & it worked - good idea. But it does work with the string when I call the sp from query analyzer. Why do you think there is a difference ?|||multiple recordsets is one option,...

using sp_executesql instead of exec might help.

personally I'd really avoid this method of doing things though... if you are going to use dynamic sql then use it in the application, not in a stored proc.

Getting Print Statements from SQL SERVER 2000

Using ADO and Visual Basic 6, I am executing SQL stored
procedures, and I would like to be able to output
SQL "print" statements. I have not yet found a way to do
so, please help if you can.
Thanks
Mike BUse the error collection.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike B" <anonymous@.discussions.microsoft.com> wrote in message
news:027701c3c0ed$07bf1010$a301280a@.phx.gbl...
> Using ADO and Visual Basic 6, I am executing SQL stored
> procedures, and I would like to be able to output
> SQL "print" statements. I have not yet found a way to do
> so, please help if you can.
> Thanks
> Mike B

Getting identity value

I am using ADO to connect to SQL 2k db. We have converted it to merge
replication and now I am not getting identity field value after I use the
Update method. For example:
rsPayInfo.AddNew
rsPayInfo("CheckAmt") = 100.00
rsPayInfo.Update
lngCheckID = rsPayInfo("CheckID")
The variable lngCheckID is returning a zero, which is not the actual value
on the new table entry. What do I need to do to retrieve the new CheckID?
Thanks.
Davidcheck the update command - it should be UPDATE ...; SELECT ...
to select the updated values
same for INSERT
David wrote:
> I am using ADO to connect to SQL 2k db. We have converted it to merge
> replication and now I am not getting identity field value after I use the
> Update method. For example:
> rsPayInfo.AddNew
> rsPayInfo("CheckAmt") = 100.00
> rsPayInfo.Update
> lngCheckID = rsPayInfo("CheckID")
> The variable lngCheckID is returning a zero, which is not the actual value
> on the new table entry. What do I need to do to retrieve the new CheckID?
> Thanks.
> David
>
>