Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the result of the following query


What is the result of the following query

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Got it right for the wrong reason, learned something. Thanks.
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3054 Visits: 2716
Oleg Netchaev (12/9/2010)
... In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.

if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;

create table #tmp (id int);

declare @sql varchar(2000);
set @sql = 'insert into #tmp(id) values (1);';
exec (@sql);

select * from #tmp;


results in

id
-----------
1


Oleg

Another way to get it to work is to put it all on the inside of the dynamic SQL like this:
Declare @strSql varchar(2000);
Set @strSql = '';
Set @strSql = @strSql + 'Create table #tmp (id int);';
Set @strSql = @strSql + 'Insert into #tmp(id) values (1);';
Set @strSql = @strSql + 'Select * From #tmp;';
Exec (@strSql);


Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11578
cengland0 (12/10/2010)
I thought I found the trick and it was that a space was missing. The lack of the space actually had nothing to do with the error but I thought it did so I selected the error message I thought would have been generated and got it right but for the wrong reason.

I'll admit that I expected the missing space to be the issue too, when I saw the question in the daily mail. But when I went to the sit, saw the answer options, and missed any option that I could relate to this missing space issue, I realized that apparently either the missing space is not a problem, or the author accidentally forgot the space and didn't realize. So I answered the question, then copied and executed the code to check that indeed, no space is required here.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
Hugo Kornelis (12/10/2010)
da-zero (12/10/2010)
(but it surprises me that apparently 78% -for the moment- know the exact error message!)

Well, I don't know about others, but for me this is one of the more common errors I see when developing. Or when copying and pasting code snippets from internet support forums.


Hugo makes a good point. I am sure more than 78% of us have submitted or executed code from this very web site that has returned this error.

<shhhhhh>
But I still always check my work by running the code.
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
Since the error is due to the #tmp table no longer being in scope once the EXEC() is done, one could fix this to work by making the table global and adding an explicit DROP after its last use. This leaves the table available to the SELECT.
Declare @strSql varchar(2000)
Set @strSql = ''
Set @strSql = @strSql + 'Create table ##tmp (id int)' -- Now "##tmp', not "#tmp'
Set @strSql = @strSql + 'Insert into ##tmp(id) values (1)'
Exec (@strSql)
Select * from ##tmp
drop table ##tmp



Of course, in practice, one must be careful in naming global temp tables to avoid possible collisions with unrelated processes.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the question, I have run into this before so I knew it right away.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12013
Hugo Kornelis (12/10/2010)
(plus, that one is extremely unlikely, as the database for temporary object is called tempdb, not TempDB - but I guess only people who are in the habit of developing on a case sensitive instance would know that).

No, the case sensitivity makes no difference to the message displayed so even people like me who never use a case-sensitive instance are used to seeing "tempdb" in messages, and never "TempDB".

Tom

Abi Chapagai
Abi Chapagai
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1127
Good question. Sometimes it is easy to miss small things and this type of questions will help us to keep up with the tips and tricks.
dfine
dfine
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 597
good basic question.

Declare @strSql varchar(2000)
Set @strSql = ''
Set @strSql = @strSql + 'Create table #tmp (id int)'
Set @strSql = @strSql + 'Insert into #tmp(id) values (1) Select * from #tmp'
Exec (@strSql)

Raj
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search