How do you capture the value of a local variable from SQL Server?

[acraft]

How do you capture this variable from SQL Server?
Example of T-SQL code:

declare @strVariable varchar(20)
set @strVariable = 'This was a success...'
select @strVariable as NEW_FIELD

How do I capture the returned value "This was a success..." without creating
a temporary table on the SQL Server? I am using MS Access and trying to use
the ADO.Recordset.


Should I use a different method? Any suggestions or sample code?

 

[Tom van Stiphout]

You need to put that T-SQL code in a stored procedure, and return the
string via an OUTPUT parameter.

-Tom.

 

[Brendan Reynolds]

How do you capture this variable from SQL Server?
Example of T-SQL code:

declare @strVariable varchar(20)
set @strVariable = 'This was a success...'
select @strVariable as NEW_FIELD

How do I capture the returned value "This was a success..." without
creating
a temporary table on the SQL Server? I am using MS Access and trying to
use
the ADO.Recordset.


Should I use a different method? Any suggestions or sample code?

Something like this should work. Substitute your own connection string and
stored procedure name of course.

Public Sub TestSub()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLNCLI.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=test;" & _
"Data Source=K9\SQLEXPRESS"
cnn.Open
Set rst = New ADODB.Recordset
rst.Open "test", cnn, , , adCmdStoredProc
Debug.Print rst.Fields(0).Value
rst.Close
cnn.Close


End Sub

 

[Tom van Stiphout]

On Tue, 25 Mar 2008 07:13:04 -0700, Tom van Stiphout

Oops, Brendan is right. You can return it from a Select statement as
well.
-Tom.

 

[acraft via AccessMonster.com]

Thanks for you help, Tom.....But, I found out the answer. I have to add the
following T-SQL statement at the beginning of the code: SET NOCOUNT ON;

Once I added this statement, the result of my query came back to my recordset.

Oops, Brendan is right. You can return it from a Select statement as
well.
-Tom.

You need to put that T-SQL code in a stored procedure, and return the
string via an OUTPUT parameter.

[quoted text clipped - 13 lines]