Text Field Comes Back Blank When Using ADO

Originally, I hit this problem with PHP. But then I was able to reproduce using VBScript.

I have a table with a couple of text fields, and one Real field in a table. One row of data in the table. Both text fields set, but the real field doesn't even need to have a value set.

Connecting to SQL 2005 using a DSN - ODBC.

I'm using a stored procedure I named ExecIt that runs a SQL statement that is sent in and returns records.

If I use cursor location adUseClient, it returns values for the text fields.

If I use asUseServer, it returns blank values.

If I use a SQL statement directly, it works. Only fails when using SQL statement by way of ExecIt.

In each case, cursor type is adOpenStatic or adOpenDynamic.

Using this combination fo cursor type and cursor location, it mimics what I see in SQL profiler when PHP is using ODBC.

Anyone have a solution?

Table:

CREATE TABLE [dbo].[test3](

[ID] [int] IDENTITY(1,1) NOT NULL,

[text1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[text2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[real1] [real] NULL,

CONSTRAINT [PK_ReworkLog2] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Stored Procedure - ExecIt:


create PROCEDURE [dbo].[ExecIt]

-- Add the parameters for the stored procedure here

@Statement nvarchar(max)

AS

BEGIN

exec (@Statement)

END

DSN has been set up through ODBC manager. Can be using 2005 native driver, or older sql server driver.

Code:

dim txtOut

set conn1=CreateObject("ADODB.Connection")
conn1.Open "my-DSN","my-userid","my-password"
sql="exec execit 'select * from test3 where ID=1'"
set rs = CreateObject("ADODB.recordset")
rs.CursorLocation = 2 'adUseServer - 2, adUseClient - 3
rs.CursorType = 3 'adOpenStatic
rs.Open sql,conn1
if not rs.EOF then
txtOut = "Results" & vbcrlf
txtOut = txtOut &"text1:" & rs("text1") & vbcrlf
txtOut = txtOut &"text2:" & rs("text2") & vbcrlf
txtOut = txtOut & vbcrlf
end if
msgbox txtOut
rs.Close
conn1.Close