Baffling Problem With Temp Table 1

[Moebius01]

Ok, this one is going to be hard to describe properly, and as it's some client confidential stuff in the query I can't just post it direct. So here's the summary.

I have a query that is used by an ASP page to provide a report for a client. Due to some filtering they're asking for, I needed to modify the query to use a temp table structure so I could check some items before returning. However, when doing so, it causes the report page to crash on a missing name or ordinal. I've quintuple checked to make sure field names and datasets are identical between the two methods. So for example, is there a functional difference between:

SELECT s.Item [FieldA], s.Name [Name], s.Score [Score]
FROM Scores

and

CREATE TABLE #Results ([Item] INT, [Name] VARCHAR(50), [Score] INT)
INSERT INTO #Results
SELECT Item, Name, Score FROM Scores

This is a quick and dirty example. Obviously the real query is more complex, but I've even tried modifying it to return both result sets to look at in QA, and they're identical as far as I can see. Yet the second approach crashes the report.

 

[gmmastros]

Do you have SET NOCOUNT ON within the stored procedure?

-George

"the screen with the little boxes in the window." - Moron

 

[SQLDenis]

where is the last step?

SELECT * FROM #Results

you are not returning anything

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Moebius01]

Yes, it's set at the beginning of the sproc.

 

[gmmastros]

[tt][blue]SELECT s.Item [!][FieldA][/!], s.Name [Name], s.Score [Score]
FROM Scores

CREATE TABLE #Results ([!][Item][/!] INT, [Name] VARCHAR(50), [Score] INT)
INSERT INTO #Results
SELECT Item, Name, Score FROM Scores
[/blue][/tt]

Maybe you should use FieldA in your #Result table.

-George

"the screen with the little boxes in the window." - Moron

 

[Moebius01]

Sorry, that was a typo on my example there. The field is returned as [Item] in both result sets.

 

[Moebius01]

Ok, let me put the corrections in here as I was typing that example on the fly and got distracted by a call.

Example 1

SELECT s.Item [Item], s.Name [Name], s.Score [Score]
FROM Scores

Example 2

CREATE TABLE #Results ([Item] INT, [Name] VARCHAR(50), [Score] INT)
INSERT INTO #Results
SELECT Item, Name, Score FROM Scores
SELECT * FROM #Results

Again, looking at the 2 in QA the results appear identical in every aspect. But something appears to be different as far as ASP is concerned, and I'm at a loss to say what.

 

[TysonLPrice]

What about just skipping the table create and do:

SELECT s.Item [FieldA], s.Name [Name], s.Score [Score]
into #Results
FROM Scores

 

[Moebius01]

Ok, in a short test, useing SELECT INTO does seem to solve the problem (I'd sure love to know why though).

However, that leads me to another problem. In the real query, it's using a statment built into a @SQL variable then executed (neccessary as some other variables are populated into it).

Is there a way to use a variable like that with a SELECT INTO? SELECT (EXEC(@SQL)) INTO #Results won't work that I know of.

 

[SQLDenis]

>>I'd sure love to know why though

the temp table that you created probably has a different datatype than the original one, when you do select into it will use the same datatype

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Would one of your field names be a reserved word that needs to be in []?

Questions about posting. See faq183-874

 

[Moebius01]

I tried putting them all in brackets actually.

I also did some tests with the field types as a possible cause that Denis suggested, but could not confirm it, however I think there's a solid chance that it's that, or something similar.

 

[ESquared]

remember in Query Analyzer you can view column types, and you can always script a table out, and also useful is sp_help:

tempdb..sp_help '#Results'

So you can compare them directly. Try a select into a real table from scores with just the columns you want, and then script that table out and use it as the definition of your temp table.

Another idea I had is to put a GO in between your table creation and the table insert. Just a random thing to try.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[Lamprey13]

However, that leads me to another problem. In the real query, it's using a statment built into a @SQL variable then executed (neccessary as some other variables are populated into it).

That sounds like the issue to me. Any chance you can post your dynamic sql? Possible scope issue..?

 

[Moebius01]

As it tured out, tests concluded that it was a field definition conflict as this particular ASP piece was expecting varchar rather than int for numeric (go figure).

Using a SELECT INTO did the trick and is working correctly. Thanks all, and * to Tyson for suggesting that approach.