ASP won't recognize dynamically built SQL crosstab columns

[harleychick]

Background info:
I have a main SQL stored procedure that calls the crosstab stored procedure found on the website below:

http://www.sqlteam.com/item.asp?ItemID=2955

I return the SQL statement (@sql) from the crosstab stored procedure into the main stored procedure and then run an [blue]EXEC (@sql)[/blue] command.

SQL Query Analyzer runs the stored proc without any errors and returns a recordset with 5 columns. The first two column names are static and the last three column names are dynamically built by the crosstab.

Problem:
When I access the recordset from an ASP page, the ASP page only thinks there are 2 columns (the columns with static names). I returned a field count and field names to the webpage using code below:

        Dim fld
	Response.Write "Field Count:" & rs.Fields.Count 	For each fld in rs.Fields
		Response.Write fld.Name & "<BR>"
	Next

[red]Why won't ASP recognize the other columns????[/red] I've been searching for a solution online for two days and haven't found anything.

 

[Sheco]

What if instead of just running the EXEC, you pump it into a temp table and then SELECT * FROM your temp table?

Also, a long shot, but might want to check to make sure ADO didn't stick your other fields into a child recordset.

 

[harleychick]

I have tried the code below:

set @tempsql = 'SELECT * INTO #test1 FROM (' +  @sql + ') a SELECT * FROM #test1'
EXEC (@tempsql)

I get the same result. Is there another way to pump the results of the EXEC into a temp table? How would I check for a child recordset?

 

[Sheco]

What I was trying to say is just make sure it isn't somehow being stuffed into a second recordset.

Use something like this to test for a second recordset:
Dim rs2
Set rs2 = rs.NextRecordset
For each fld in rs2.Fields
Response.Write fld.Name & "<BR>"
Next


I don't expect there will be one but it is pretty quick to test and rule that out.

 

[harleychick]

I get an "Object Required" error on this line of code

For each fld in rs2.Fields

 

[Sheco]

OK then rs2 is Nothing and we can rule out the split recordset theory.

 

[harleychick]

Is there a way to pump the results of the EXEC into a temp table directly? Something like SELECT * INTO #temp FROM EXEC (@sql)???

 

[Sheco]

Do you need a CREATE TABLE first? I guess this would be a problem if you don't know the data types of the final 3 fields.

 

[harleychick]

I don't know how many fields I will have. The recordset is a dynamic crosstab. The 3 fields I talked about were just an example.

 

[harleychick]

Thanks for all of your help Sheco! I discovered the problem. The web user I was using to execute the stored proc didn't have permissions to tempdb - that's why you couldn't see the dynamically built columns. I can't believe it was something that simple!!

 

[Sheco]

Hey thanks for the followup message to let us know how it worked out.

I never would have thought of THAT !