|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic SQLI am trying to use "select into" query in a dynamic sql.But the temp table is
not getting created when i execute the dynamic sql.What am I missing? Thanks in advance. HP wrote:
> I am trying to use "select into" query in a dynamic sql.But the temp Probably the fact that the dynamic sql is executed in a separate process:> table is not getting created when i execute the dynamic sql.What am I > missing? > one of the many reasons to avoid dynamic sql. See http://www.sommarskog.se/dynamic_sql.html for more information. There are several other informative articles on that site that are well worth reading. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Any more suggestions please?
Show quote "Bob Barrows [MVP]" wrote: > HP wrote: > > I am trying to use "select into" query in a dynamic sql.But the temp > > table is not getting created when i execute the dynamic sql.What am I > > missing? > > > Probably the fact that the dynamic sql is executed in a separate process: > one of the many reasons to avoid dynamic sql. > > See http://www.sommarskog.se/dynamic_sql.html for more information. There > are several other informative articles on that site that are well worth > reading. > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > > Hi HP
What didn't you like about Bob's suggestion? Did you follow the link he provided? I bet you'd get more suggestions if you would provide us with more information. For the tiny bit of info you gave us, Bob's suggestions was spot on, and I doubt anyone could do any better. What version are you running? How are you creating and running the dynamic SQL? What does it contain? How do you know the temp table isn't created? Is it just that it isn't available when the dynamic SQL is done? That doesn't mean it wasn't creating while the dynamic SQL was executing. Show quote "HP" <H*@discussions.microsoft.com> wrote in message news:11A865F8-F55E-46C0-80B9-FD194F5E79E7@microsoft.com... > Any more suggestions please? > > "Bob Barrows [MVP]" wrote: > >> HP wrote: >> > I am trying to use "select into" query in a dynamic sql.But the temp >> > table is not getting created when i execute the dynamic sql.What am I >> > missing? >> > >> Probably the fact that the dynamic sql is executed in a separate process: >> one of the many reasons to avoid dynamic sql. >> >> See http://www.sommarskog.se/dynamic_sql.html for more information. There >> are several other informative articles on that site that are well worth >> reading. >> >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get a >> quicker response by posting to the newsgroup. >> >> >> > Kalen beat me to it.
I can't offer any suggestions without knowing what you are trying to accomplish .. HP wrote: Show quote > Any more suggestions please? > > "Bob Barrows [MVP]" wrote: > >> HP wrote: >>> I am trying to use "select into" query in a dynamic sql.But the temp >>> table is not getting created when i execute the dynamic sql.What am >>> I missing? >>> >> Probably the fact that the dynamic sql is executed in a separate >> process: one of the many reasons to avoid dynamic sql. >> >> See http://www.sommarskog.se/dynamic_sql.html for more information. >> There are several other informative articles on that site that are >> well worth reading. >> -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Sorry for not giving you a detailed explanation of what i am trying to achieve.
i have a dynamic sql which uses "select into" to create a temp table. It has to be dynamic because the fieldnames are soft coded.I need to join this temp table (that i created using the dynamic sql) with another table.Since the temp table goes out of scope after the exec statement i am not able to use it in my second query. Show quote "Bob Barrows [MVP]" wrote: > Kalen beat me to it. > I can't offer any suggestions without knowing what you are trying to > accomplish > .. > HP wrote: > > Any more suggestions please? > > > > "Bob Barrows [MVP]" wrote: > > > >> HP wrote: > >>> I am trying to use "select into" query in a dynamic sql.But the temp > >>> table is not getting created when i execute the dynamic sql.What am > >>> I missing? > >>> > >> Probably the fact that the dynamic sql is executed in a separate > >> process: one of the many reasons to avoid dynamic sql. > >> > >> See http://www.sommarskog.se/dynamic_sql.html for more information. > >> There are several other informative articles on that site that are > >> well worth reading. > >> > > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > > HP wrote:
> Sorry for not giving you a detailed explanation of what i am trying Yes, I knew that. What I don't know is "why are you doing this"? What are> to achieve. I have a dynamic sql which uses "select into" to create a > temp table. you trying to achieve? > It has to be dynamic because the fieldnames are soft Do they have to be? Why do they have to be dynamic? If we knew what your> coded. objective was, we might be able to suggest an alternative. Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. I guess the problem is like following
exec('select 20 as tmp_id into #tmp') select * from #tmp -- This simply can't see the #tmp table James Show quote "HP" wrote: > Any more suggestions please? > > "Bob Barrows [MVP]" wrote: > > > HP wrote: > > > I am trying to use "select into" query in a dynamic sql.But the temp > > > table is not getting created when i execute the dynamic sql.What am I > > > missing? > > > > > Probably the fact that the dynamic sql is executed in a separate process: > > one of the many reasons to avoid dynamic sql. > > > > See http://www.sommarskog.se/dynamic_sql.html for more information. There > > are several other informative articles on that site that are well worth > > reading. > > > > -- > > Microsoft MVP -- ASP/ASP.NET > > Please reply to the newsgroup. The email account listed in my From > > header is my spam trap, so I don't check it very often. You will get a > > quicker response by posting to the newsgroup. > > > > > > Good guess. You may be correct, but: that does not tell us _why_ he is
trying to do this. Knowing what his real purpose is will help us suggest alternatives. James Ma wrote: Show quote > I guess the problem is like following > > exec('select 20 as tmp_id into #tmp') > select * from #tmp -- This simply can't see the #tmp table > > James > > "HP" wrote: > >> Any more suggestions please? >> >> "Bob Barrows [MVP]" wrote: >> >>> HP wrote: >>>> I am trying to use "select into" query in a dynamic sql.But the >>>> temp table is not getting created when i execute the dynamic >>>> sql.What am I missing? >>>> >>> Probably the fact that the dynamic sql is executed in a separate >>> process: one of the many reasons to avoid dynamic sql. >>> >>> See http://www.sommarskog.se/dynamic_sql.html for more information. >>> There are several other informative articles on that site that are >>> well worth reading. >>> >>> -- >>> Microsoft MVP -- ASP/ASP.NET >>> Please reply to the newsgroup. The email account listed in my From >>> header is my spam trap, so I don't check it very often. You will >>> get a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Can you pls refer to my previous reply.I've explained my problem.
Thanks!Your help would be greatly appreciated. Show quote "Bob Barrows [MVP]" wrote: > Good guess. You may be correct, but: that does not tell us _why_ he is > trying to do this. Knowing what his real purpose is will help us suggest > alternatives. > > James Ma wrote: > > I guess the problem is like following > > > > exec('select 20 as tmp_id into #tmp') > > select * from #tmp -- This simply can't see the #tmp table > > > > James > > > > "HP" wrote: > > > >> Any more suggestions please? > >> > >> "Bob Barrows [MVP]" wrote: > >> > >>> HP wrote: > >>>> I am trying to use "select into" query in a dynamic sql.But the > >>>> temp table is not getting created when i execute the dynamic > >>>> sql.What am I missing? > >>>> > >>> Probably the fact that the dynamic sql is executed in a separate > >>> process: one of the many reasons to avoid dynamic sql. > >>> > >>> See http://www.sommarskog.se/dynamic_sql.html for more information. > >>> There are several other informative articles on that site that are > >>> well worth reading. > >>> > >>> -- > >>> Microsoft MVP -- ASP/ASP.NET > >>> Please reply to the newsgroup. The email account listed in my From > >>> header is my spam trap, so I don't check it very often. You will > >>> get a quicker response by posting to the newsgroup. > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > > Hi
Without any code it is very difficult to recommend anything. You may have to watch that the temporary table does not go out of scope. Try creating the table outside the dynamic SQL and then use INSERT. John Show quote "HP" wrote: > I am trying to use "select into" query in a dynamic sql.But the temp table is > not getting created when i execute the dynamic sql.What am I missing? > > Thanks in advance. Thanks for your reply.I figured scope was the problem. I can't create the
table outside because the field names are soft coded.Can you pls suggest something? Thanks again! Show quote "John Bell" wrote: > Hi > > Without any code it is very difficult to recommend anything. > > You may have to watch that the temporary table does not go out of scope. Try > creating the table outside the dynamic SQL and then use INSERT. > > John > > "HP" wrote: > > > I am trying to use "select into" query in a dynamic sql.But the temp table is > > not getting created when i execute the dynamic sql.What am I missing? > > > > Thanks in advance. Try to use fake temp table in a specific user database (set it to use simple
recovery mode) instead of real temp table in the tempdb. In that way you can avoid the scope problem. James Show quote "HP" wrote: > Thanks for your reply.I figured scope was the problem. I can't create the > table outside because the field names are soft coded.Can you pls suggest > something? > Thanks again! > "John Bell" wrote: > > > Hi > > > > Without any code it is very difficult to recommend anything. > > > > You may have to watch that the temporary table does not go out of scope. Try > > creating the table outside the dynamic SQL and then use INSERT. > > > > John > > > > "HP" wrote: > > > > > I am trying to use "select into" query in a dynamic sql.But the temp table is > > > not getting created when i execute the dynamic sql.What am I missing? > > > > > > Thanks in advance. Hi
You are going to have to put most code into the one execute statement, as this is probably going to get messy you should probably re-think what you are trying to achieve and why it is such a pain to implement. Without seeing actual code it is hard to suggest anything else. John Show quote "HP" wrote: > Thanks for your reply.I figured scope was the problem. I can't create the > table outside because the field names are soft coded.Can you pls suggest > something? > Thanks again! > "John Bell" wrote: > > > Hi > > > > Without any code it is very difficult to recommend anything. > > > > You may have to watch that the temporary table does not go out of scope. Try > > creating the table outside the dynamic SQL and then use INSERT. > > > > John > > > > "HP" wrote: > > > > > I am trying to use "select into" query in a dynamic sql.But the temp table is > > > not getting created when i execute the dynamic sql.What am I missing? > > > > > > Thanks in advance. HP (H*@discussions.microsoft.com) writes:
> Thanks for your reply.I figured scope was the problem. I can't create the You will have to run everything within that dynamic SQL.> table outside because the field names are soft coded.Can you pls suggest > something? Now, what everyone really are asking you for, is to tell us what your real business problem is. That is, why do you need those soft-coded column names? As a completely wild guess, you are into dynamic crosstabs. In such case, stroll over to http://www.rac4sql.net for a third-party product that may be useful to you. (Never used it myself, but I've seen people expressed their joy over RAC.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp If you aren't bothered about multi connections using this then use a global
temporary table... exec( 'select * into ##tmp from sysobjects' ) select * from ##tmp Another approach is to use a global temp table suffixed by your spid... declare @sql set @sql = 'select * into ##tmp' + cast( @@spid as varchar(10) ) + ' from sysobjects' exec( @sql ) set @sql = 'select * from ##tmp' + cast( @@spid as varchar(10) ) exec( @sql ) Or, consider using a permanent table instead rather than a # or ## table. Tony Show quote "HP" <H*@discussions.microsoft.com> wrote in message news:83CDC32F-A76E-462B-AA74-2C2D0807861F@microsoft.com... >I am trying to use "select into" query in a dynamic sql.But the temp table >is > not getting created when i execute the dynamic sql.What am I missing? > > Thanks in advance. This is bad for many reasons but you could use a global temp table.
Declare @strSQL varchar(8000) set @strSQL = 'Select * into ##temptable from northwind..orders' exec (@strSQL) Select * from ##temptable I would encourage you to look for alternatives, but this will do what you are asking. Show quote "HP" <H*@discussions.microsoft.com> wrote in message news:83CDC32F-A76E-462B-AA74-2C2D0807861F@microsoft.com... >I am trying to use "select into" query in a dynamic sql.But the temp table >is > not getting created when i execute the dynamic sql.What am I missing? > > Thanks in advance.  |
|||||||||||||||||||||||
Other interesting topics