Click here to Skip to main content
15,943,245 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Below sample sp's are based on my working sp's, i have made this easier for you to understand in a simpler way to find the solution.
1) First SP
SQL
CREATE PROCEDURE testsp
@doc varchar(max) AS

Declare @idoc int 

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *  FROM OPENXML (@idoc,'/OutLookContact/Contact',1)
          WITH (
                    FirstName varchar(50),
                    LastName varchar(50),
                    EmailAddress varchar(50)
                )
GO

2) Second SP ( calling first sp in this sp)
SQL
CREATE PROCEDURE testsp2
@login varchar(255),
@doc2 varchar(max)
as
create table #tbl_user_details
(
    id        varchar(1000) default (newid()),
    firstName varchar(255),
    lastName  varchar(255),
    EmailAddress varchar(255)
)

insert into #tbl_user_details (firstName,lastName,EmailAddress)
exec testsp @doc = @doc2

select *,@login as [login_name] from #tbl_user_details

Drop table #tbl_user_details
GO


Now i need the second sp to insert in a temp table with input clause as xml have more than 8000 character. But I cant do this using openrowset or openquery.
I have worked and a tried below sample.
SQL
SELECT * into #tbl_user_details FROM OPENQUERY 
( vinsdt073, 
'SET NOCOUNT ON; exec innova..testsp2 @login = ''Ragul'',
 @doc2 = ''<OutLookContact>
            <Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="asifghafoor@my.web.pk" />
            <Contact FirstName="Rameez" LastName="Ali" EmailAddress1="rameezali@my.web.pk" />
            ---
            ---
            --- (More than 8000 char)
          </OutLookContact>''
WITH RESULT SETS
(
    (
        id        varchar(255),
        FirstName varchar(max),
        LastName  varchar(max),
        EmailAddress1  nvarchar(max),
        login_name varchar(255)
    )
);')
Posted
Comments
Herman<T>.Instance 7-Oct-15 10:36am    
And the question is?
Ragul M 10-Oct-15 9:54am    
Now i need the second sp to insert in a temp table with input clause as xml have more than 8000 character.
(Without Modifying both sp's)

1 solution

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:
SQL
-- Insert the data into a staging table:
DECLARE @ID uniqueidentifier = NEWID();
INSERT INTO vinsdt073.innova.dbo.YourStagingTable (ID, Data)
VALUES (@ID, '<OutlookContact>...</OutlookContact>');

-- Build the query to execute on the remote server:
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)
    )
);';

-- Build the query to execute on the local server:
SET @Query = Replace(@Query, N'''', N'''''');
SET @Query = N'SELECT * into #tbl_user_details FROM OPENQUERY (vinsdt073, ''' + @Query + N''')';

-- Execute the query:
EXEC(@Query);

-- Clean up the staging data:
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.
 
Share this answer
 
v2
Comments
Ragul M 7-Oct-15 11:09am    
No it's not possible, " WITH RESULT SETS " cant be inserted into new table.

http://raresql.com/2013/01/31/sql-server-stored-procedure-insert-into-exec-with-result-set/
Richard Deeming 7-Oct-15 11:15am    
Well it's not possible to do it with OPENQUERY or OPENROWSET either, if the query exceeds 8Kb.

Either remove the WITH RESULT SETS and execute the query directly, or insert the large XML into a staging table on the remote server and read it back within the query.
Ragul M 7-Oct-15 11:25am    
Could you please give me a sample to do this ?
Richard Deeming 7-Oct-15 11:38am    
INSERT INTO #tbl_user_details
EXEC vinsdt073.innova.dbo.testsp2 @login = 'Ragul', @doc2 = '...'


Or:
-- Insert the data into a staging table:
DECLARE @ID uniqueidentifier = NEWID();
INSERT INTO vinsdt073.innova.dbo.YourStagingTable (ID, Data)
VALUES (@ID, '<OutlookContact>...</OutlookContact>');

-- Build the query to execute on the remote server:
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)
)
);';

-- Build the query to execute on the local server:
SET @Query = Replace(@Query, N'''', N'''''');
SET @Query = N'SELECT * into #tbl_user_details FROM OPENQUERY (vinsdt073, ''' + @Query + N''')';

-- Execute the query:
EXEC(@Query);

-- Clean up the staging data:
DELETE FROM vinsdt073.innova.dbo.YourStagingTable WHERE ID = @ID;


The second option is more complicated, because you can't pass variables to OPENQUERY.
Ragul M 7-Oct-15 11:52am    
Did you tried your sample. it's not working in the first line itself

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900