Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to use opendatasource for exec sp with output parameter Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
hello all.
I wanna execute sp with output parameter by use opendatasource.i use this script for it:
exec('exec OPENDATASOURCE(''sqloledb'',''Data Source=ip; User ID=sa;Password=123'').documentdatabase.dbo.GLB_ConcatUploadedFilePart''' +@TBL_UserID+''','+@TBL_IdBody+'output')
and type of @TBL_IdBody is varbinary(max).when execute

declare @TBL_IdBody varbinary(max),@TBL_UserID int
set @TBL_UserID=1
set @TBL_IdBody=cast('' as varbinary(max))
exec('exec OPENDATASOURCE(''sqloledb'',''Data Source=192.168.1.102; User ID=sa;Password=123abc%'').documentdatabase.dbo.GLB_ConcatUploadedFilePart''' +@TBL_UserID+''','+@TBL_IdBody +'output')
select @TBL_IdBody

get error:Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

what do i do for solve this problem?how to use opendatasource for exec sp with output parameter?
Post #1478500
Posted Monday, July 29, 2013 5:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
So the simplest would be to defined a linked server, then you would not need to use dynamic SQL, but you could make a plain procedure call.

But to continue on the track where you are right now, you could use sp_executesql instead. See my article on dynamic SQL for details:
http://www.sommarskog.se/dynamic_sql.html


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1478530
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse