SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

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

Group: General Forum Members
Points: 3243 Visits: 2777
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
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: 10803 Visits: 11966
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 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.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

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

Group: General Forum Members
Points: 14168 Visits: 12197
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: 1846 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 (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)

Group: General Forum Members
Points: 650 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-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31814 Visits: 18550
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