how to use opendatasource for exec sp with output parameter

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  July 29, 2013 at 3:34 am

  #280722

  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?

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 29, 2013 at 5:07 am

  #1636590

  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

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]