If you read
the documentation[
^], the reason would become clear:
'query' Is the query string executed in the linked server. The maximum length of the string is 8 KB.
You'll need to find a different way to do what you're trying to achieve. For example, you could use a
linked server[
^].
Example using a staging table on the remote server:
DECLARE @ID uniqueidentifier = NEWID();
INSERT INTO vinsdt073.innova.dbo.YourStagingTable (ID, Data)
VALUES (@ID, '<OutlookContact>...</OutlookContact>');
DECLARE @Query varchar(max);
SET @Query = 'SET NOCOUNT ON;
DECLARE @doc2 nvarchar(max);
SELECT
@doc2 = Data
FROM
innova.dbo.YourStagingTable
WHERE
ID = ''' + CAST(@ID As nvarchar(36)) + '''
;
EXEC innova.dbo..testsp2 @login = ''Ragul'', @doc2 = @doc2 WITH RESULT SETS
(
(
id varchar(255),
FirstName varchar(max),
LastName varchar(max),
EmailAddress1 nvarchar(max),
login_name varchar(255)
)
);';
SET @Query = Replace(@Query, N'''', N'''''');
SET @Query = N'SELECT * into #tbl_user_details FROM OPENQUERY (vinsdt073, ''' + @Query + N''')';
EXEC(@Query);
DELETE FROM vinsdt073.innova.dbo.YourStagingTable WHERE ID = @ID;
It's complicated by the fact that you can't pass a variable to the
OPENQUERY
function.