ASP won''t recognize dynamically built SQL crosstab columns

Question

Post reply

ASP won''t recognize dynamically built SQL crosstab columns

Tolly

SSC Journeyman

Points: 92
More actions
March 22, 2005 at 12:40 pm

#66074

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 EXEC (@sql) command.
For example, 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).

Why won't ASP recognize the other columns???? I've been searching for a solution online for a week and haven't found anything.

I am using this code to return the recordset in Query Analyzer:
set @tempsql = 'SELECT * INTO #test1 FROM (' + @sql + ') a SELECT * FROM #test1'
EXEC (@tempsql)
Is there another way to pump the results of the EXEC into a temp table?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sthornton Ten Centuries Points: 1333 More actions · Answer 1

are you able to give the other three columns names?

col1, col2, col3 so you can refer to them in code?

Wayne West SSC-Insane Points: 22586 More actions · Answer 2

This is pure conjecture, but I think it might hold up.

In Access, if you create a report on a crosstab and the number of fields output changes, your report might not work. I'm guessing that DTS psuedo-compiles the package and by having a variable number of columns things are boggling it. I imagine it's optimizing for the number of columns from the first run, and when that changes, chaos and confusion abound.

If you can anticipate the maximum number of columns that will be produced by the crosstab, perhaps you can build a temp table at some step, insert the data there, then let DTS continue processing at that point.

For both our sakes I hope there is someone who knows what I'm getting at and can concisely and correctly restate it. 🙂

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

steve dassin SSCrazy Points: 2396 More actions · Answer 3

Check out the RAC faq for an example of using the result of a dynamic crosstab in ASP.

http://www.rac4sql.net

P.S. you'll get all the horsepower you need with RAC

Tolly SSC Journeyman Points: 92 More actions · Answer 4

I am currently putting the crosstab results into a temp table. SQL displays the results correctly with the correct dynamic column names. The ASP page just can't see the dynamic columns. As a test, I copied the dynamically built sql statement directly into the stored procedure and ran it. ASP will recognize the dynamic columns this way. However, it will not when the same exact sql statement is executed by "EXEC (@sql)". What is the difference????

Tolly SSC Journeyman Points: 92 More actions · Answer 5

Thanks for all of your help everyone! 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!!

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

Huh! How did he not have permission to tempdb? Was he explicitly removed or denied?

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]