May 7, 2008 at 2:45 pm
I'm having trouble with dynamic SQL in an OpenRowset command, as per below code snippet. I'm attempting to call a stored procedure and put the data returned into a temp table. The sproc takes a parameter and I build the stored procedure call dyamically.
declare @Exec_Sproc varchar(500)
set @Exec_Sproc = 'SET FMTONLY OFF exec '
select @Exec_Sproc = @Exec_Sproc + db_name()
set @Exec_Sproc = @Exec_Sproc + '.dbo.S_RS_ListTableStructure ''Employee'''
select
a.*
into
#space
FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes',
@Exec_Sproc) AS a
The error message returned is:
Line 14: Incorrect syntax near '@Exec_Sproc'.
I can execute the sproc using execute(@Exec_Sproc), so it's not the dynamic SQL itself. I can also execute the OpenRowset by putting my command in a string like this: 'SET FMTONLY OFF exec Petsmart.dbo.S_RS_ListTableStructure ''Employee'''. So the issue is with the combination of dyamic SQL in the OpenRowset command. I'm stuck!
Linda
May 8, 2008 at 9:10 am
Well...........if I had looked at BOL first instead of last, I would have discovered that you CANNOT use variables with OpenQuery and OpenRowset. Anyway, nobody else responded so I guess I'll post the solution.
The solution is to make the entire:
'select a.* into ##table from OpenRowset(...'
into a string and execute it as dynamic SQL.
The one caveat to this approach is that you have to use a global temp table if you're planning on creating a temp table on the fly and inserting your data into it. You won't be able to access a local temp table generated from dynamic SQL.
May 8, 2008 at 8:08 pm
boumerlin (5/8/2008)
The one caveat to this approach is that you have to use a global temp table if you're planning on creating a temp table on the fly and inserting your data into it. You won't be able to access a local temp table generated from dynamic SQL.
You may create #table before running dynamic SQL and use INSERT INTO #table insead of SELECT ... INTO #table.
Take into account that when you run SELECT ... INTO you modify system catalog inside of trasaction.
Because you open call is dynamic you cannot really control how long will it take. Probably it will even hang because of some fault on the server it's trying to access.
And for the time it's waiting for response all other processes trying to deal with tempdb objects will be blocked.
_____________
Code for TallyGenerator
May 9, 2008 at 8:15 am
Yes, but in this case I was trying to get away from creating the temp table since the stored procedure I'm calling is subject to change by client and plan year. I needed a way of creating my temp table on the fly, to catch the results of the stored procedure. This approach, while more server intensive, seemed to be less of a maintenance nightmare in the long haul.
March 7, 2012 at 3:09 am
Currently any solution available in sql 2005 or 2008 ?. If you have share with me venkeyanbu@gmail.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply