OPENROWSET with parameter

[TysonLPrice]

I trying to set something up to capture the output of a stored procedure without creating the temp table first. OPENROWSET does it but it doesn't accept parameters. Trying to use dynamic SQL I get the error:

"Cannot process the object " SET FMTONLY OFF EXEC cs_GetClaim 111144550". 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."

This is the code:

declare @pkClaim varchar(20)
set @pkClaim = '111144550'
declare @sql varchar(1000)
set @sql = 'SELECT * INTO #TysonTemp FROM OPENROWSET(' + '''SQLNCLI''' + ',' + '''Server=devsql\tpa1;Trusted_Connection=yes;'''
           + ',' + ''' SET FMTONLY OFF EXEC cs_GetClaim ' + @pkClaim + ''') AS a;'

--print @SQL

exec (@sql)

I have permission because I can run it with a stored procedure without parameters. Does someone see what I am doing wrong?

 

[TheBugSlayer]

Tyson,

A SELECT @sql of your statement yields

SELECT * INTO #TysonTemp FROM 
OPENROWSET('SQLNCLI','Server=devsql\tpa1;Trusted_Connection=yes;',' SET FMTONLY OFF EXEC cs_GetClaim [COLOR=red]111144550[/color]') AS a;	7	0

@pkClaim is declare as VARCHAR(20) therefore it should be enclosed in single quotes when the value is replaced.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TysonLPrice]

Thanks. I fixed that but still get the same error. This is what I am running.

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)]/code]

This works with a stored procedure that does not require parameters.

 

[TysonLPrice]

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

 

[TheBugSlayer]

Ah! So right, the database name was missing! Glad you got it to work.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)