1

I am calling a stored procedure in OpenQuery and passing parameters to the stored procedure.

declare @Src nvarchar(max),@Tgt nvarchar(max)
declare @sql_str nvarchar (4000)
set @Src = '$\VMT\Versions\0900\AMS\'
set @Tgt = '$\VMT\Versions\0900\Base\'
set @sql_str = 'exec [Tfs_VMT Collection].dbo.MergeCandidate ''' + @Src+ ''' , ''' +@Tgt+''' ' 
set @sql_str = 'select * into #test from openquery(ODSSERVER,''' +Replace(@sql_str,'''', '''''') +''')'

print @sql_str

EXEC (@sql_str)

but this fails with following error

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec [Tfs_VMT Collection].dbo.MergeCandidate '$\VMT\Versions\0900\AMS\' , '$\VMT\Versions\0900\Base\' ". The OLE DB provider "SQLNCLI10" for linked server "ODSSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

I have checked this has nothing to do with permissions.

Can you please help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anirudh
  • 21
  • 1
  • 3

1 Answers1

0

Try removing the exec from your openquery query. You should be able to just pass in the procedure name/parameters.

set @sql_str = '[Tfs_VMT Collection].dbo.MergeCandidate ''' + @Src+ ''' , ''' +@Tgt+''' '
Derek
  • 21,828
  • 7
  • 53
  • 61