Last post Jun 28, 2016 02:59 PM by limno
Jun 27, 2016 03:17 PM|remojr76|LINK
I have a linked server and I am able to run this query using OPENQUERY:
DECLARE @OPENQUERY nvarchar(max), @TSQL nvarchar(max), @LinkedServer nvarchar(max)
SET @LinkedServer = '[SQLP01]'
SET @OPENQUERY = 'SELECT COUNT(*) FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT lgh_number FROM [Live].[dbo].[legheader] where lgh_number = ' + @casted + ''')'
I need to run an additional open query that executes a stored procedures and needs 2 parameters and I can't get it to run.
any help would be appreciated.
set @OPENQUERY = 'select COUNT(*) from openquery(' + @LinkedServer + ', '''
set @TSQL = 'exec([Live].[dbo].[SSRS_Load_Confirm] ' + @casted + ', ''' + @userId + '''))'''
print @OPenquery + @TSQL
exec(@openquery + @TSQL)
Thanks in advance!
Jun 27, 2016 07:59 PM|skarnath|LINK
This seemed to work for me:
DECLARE @TSQL nVARCHAR(max), @Param nVARCHAR(1)
SET @Param = '1'
SET @TSQL = 'EXEC [svr].[dbo].sp_StoredProcedure ' + @Param
Jun 27, 2016 08:56 PM|remojr76|LINK
I have already tried this. You have to have rpc enabled for this to work, an in my case I do not, and its not a possibility.
Jun 28, 2016 02:59 PM|limno|LINK
You can try to use four parts syntax without using openquery:
SELECT COUNT(*) FROM [SQLP01].[Live].[dbo].[legheader] where lgh_number =@casted