problem displaying text on page

[miranda]

Scenario

SQL Server stored procedure joins multiple tables. Some of the fields being selected are data type text, most are varchar, datetime or bit
Stored procedure is called from asp page
Variables are assigned values from the fields called in the sproc.
Variables are displayed on page unless the field was text data type those are returned as empty.



When we run the procedure in Query Analyzer the data is displayed, yet it returns empty when we try to assign the values on the page.

Now I have noticed that it defaults to a forward only cursor so we have to assign the variables in the same order as they are called in the select statement inside the procedure. Yes, I know we can assign a different cursor to prevent this. We will still generally assign our variables in the same order that they are listed in the procedure. However, even this does not help with getting the values assigned to the variables if the corresponding field has a data type of text.

Any ideas what I need to look at? I realize that the text data types are just pointers in the tables.

[miranda]

the answer was to Add EXECUTE to the SQL String used to call the SPROC
So instead of

sSQL = "usp_SomeProcedure " & UserID

I needed to use
sSQL = "EXECUTE usp_SomeProcedure " & UserID

:thumbsup: