October 12, 2010 at 3:31 am
I'm trying to use OPENROWSET to copy the results of a stored procedure to a temporary table so I don't need to predefine it. The first example works but the second gets the error:
Works:
declare @pkClaim int
set @pkClaim = '111144550'
declare @sql varchar(1000)
set @sql = 'SELECT * INTO ##TysonTemp FROM OPENROWSET(' + '''SQLNCLI''' + ','
+ '''Server=devsql\mco1;Trusted_Connection=yes;'''
+ ',' + ''' SET FMTONLY OFF EXEC SP_WHO '') AS a;'
exec(@sql)
Does not work:
declare @pkClaim int
set @pkClaim = 788588
declare @sql varchar(1000)
set @sql = 'SELECT * INTO ##TysonTemp FROM OPENROWSET(' + '''SQLNCLI''' + ','
+ '''Server=devsql\mco1;Trusted_Connection=yes;'''
+ ',' + ''' SET FMTONLY OFF EXEC cs_GetClaim '
+ convert(varchar,@pkClaim) + ''') AS a;'
exec(@sql)
Server: Msg 7357, Level 16, State 2, Line 1
Cannot process the object " SET FMTONLY OFF EXEC cs_GetClaim 788588". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
1. EXEC cs_GetClaim 788588 returnss data
2. I do have permissions.
October 13, 2010 at 3:23 am
Our friendly DBA here at work got it working for me. Looks like my biggest mistake was not specifing the database in the connection string. Here are several examples. Just point to the right database 😀
-- Run on DEVSQL\COMMON1 ----------
declare @iParm int
set @iParm = 788588
declare @sql varchar(1000)
-- DEVSQL\MCO1 cs_GetClaim -------
set @sql = 'SELECT a.* into ##tmpAEC FROM OPENROWSET(''SQLNCLI'',''Server=devsql\mco1;Trusted_Connection=yes;Database=Manhattan'',''exec cs_GetClaim ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select * from ##tmpAEC
drop table ##tmpAEC
-- DEVSQL\MCO1 cs_GetClaimErrors -------
set @sql = 'SELECT a.* into ##tmpAEC FROM OPENROWSET(''SQLNCLI'',''Server=devsql\mco1;Trusted_Connection=yes;Database=Manhattan'',''exec cs_GetClaimErrors ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select * from ##tmpAEC
drop table ##tmpAEC
-- DEVSQL\IMAGE1 GetObject 4 times -------
select @iParm = 13598293
set @sql = 'SELECT a.* into ##tmpAEC FROM OPENROWSET(''SQLNCLI'',''Server=devsql\image1;Trusted_Connection=yes;Database=eDocs'',''exec GetObject ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select @iParm = 13598292
set @sql = 'insert ##tmpAEC select * FROM OPENROWSET(''SQLNCLI'',''Server=devsql\image1;Trusted_Connection=yes;Database=eDocs'',''exec GetObject ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select @iParm = 13598291
set @sql = 'insert ##tmpAEC select * FROM OPENROWSET(''SQLNCLI'',''Server=devsql\image1;Trusted_Connection=yes;Database=eDocs'',''exec GetObject ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select @iParm = 13598290
set @sql = 'insert ##tmpAEC select * FROM OPENROWSET(''SQLNCLI'',''Server=devsql\image1;Trusted_Connection=yes;Database=eDocs'',''exec GetObject ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select * from ##tmpAEC
drop table ##tmpAEC
October 13, 2010 at 3:26 am
Sorry about the formatting. I must have used the code tags wrong. 🙂
October 13, 2010 at 8:49 am
tyson.price (10/13/2010)
Sorry about the formatting. I must have used the code tags wrong. 🙂
The "/" only goes in the closing tag. You have it in both the opening and closing tags.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply