Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What is the result of the following query Expand / Collapse
Author
Message
Posted Friday, December 10, 2010 6:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Got it right for the wrong reason, learned something. Thanks.
Post #1032986
Posted Friday, December 10, 2010 7:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:55 AM
Points: 1,887, Visits: 1,862
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);

Post #1033001
Posted Friday, December 10, 2010 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,794, Visits: 8,010
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
Post #1033048
Posted Friday, December 10, 2010 10:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1033126
Posted Friday, December 10, 2010 10:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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.
Post #1033134
Posted Friday, December 10, 2010 6:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Thanks for the question, I have run into this before so I knew it right away.
Post #1033267
Posted Sunday, December 12, 2010 10:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 8,288, Visits: 8,739
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
Post #1033487
Posted Sunday, December 12, 2010 6:40 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:42 PM
Points: 1,786, Visits: 1,100
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.
Post #1033537
Posted Sunday, December 12, 2010 11:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:42 AM
Points: 337, Visits: 441
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)


Joe
Post #1033595
Posted Tuesday, December 14, 2010 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 20,466, Visits: 14,096
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1034578
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse