December 1, 2011 at 10:56 am
I have the the following code (last few lines).
I'm able to come up with a correct SQL Statement but it does not execute.:unsure:
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = @SQLString
EXEC (@sqlCommand)
SELECT @sqlCommand AS sqlCommand
CLOSE DynamicCursor;
DEALLOCATE DynamicCursor;
GO
The SELECT @sqlCommand AS sqlCommand return the following. I cut & paste and it executes and creates the temp table.
SELECT * INTO #Temp FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00 WHERE YP_EFFECTIVE_DATE >=1111001 AND YP_Account_No IN (7003303,7003050,7003055,7003013,7002928,8062604,7003306,7002945,7003019,7003006,7003006)') AS DB2
Any information would be greatly appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2011 at 11:11 am
If the temp table is created in the dynamic SQL command, it will only exist in that scope. That means it goes away as soon as that command finishes executing. Is that what you're running into?
The way to avoid that is to define the table in the script/proc that runs the dynamic command. That may not be feasable in what you're trying to do here.
What are you trying to do with the data in the temp table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2011 at 11:39 am
GSquared,
Thanks, you are correct, the Temp Table was out of scope.
I was attempting to reference the Temp Table outside a cursor. I had not created a Stored Procedure yet and I forgot that local Temp Tables are outside the Scope in this situation.
I'm going to switch to a Global Temp Table.
I need a Temp Table because I'm using dynamic sql to get the Account Number from a SQL Table and concatenate a OPEN QUERY SQL Statement.
I had to do it that way because I could never get the query to complete when performing a Join.
Now I need to compare the results of the Temp Table so that I can automate a manual reconciliation process.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2011 at 11:53 am
Not sure that I understand exactly what you're doing, but don't forget you can pass variables both into and out of dynamic sql (the latter with output params.) You can even do that with table valued parameters in 2k8 if you want to save the overhead of the temp table creation and still have a table to work with.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply