Insert sp result into temp table with dynamic sql including xml max length

Question

0.00/5 (No votes)

See more:

, +

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 7-Oct-15 4:01am

Ragul M

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2015-10-07T04:51:00

Solution 1

If you read the documentation[^], the reason would become clear:

OPENQUERY (Transact-SQL)[^]:

'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.

Posted 7-Oct-15 4:51am

Richard Deeming

Updated 7-Oct-15 5:42am

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

Richard Deeming 7-Oct-15 11:56am

By "the first line", do you mean:
INSERT INTO vinsdt073.innova.dbo.YourStagingTable (ID, Data)

You'll need to create the staging table on the remote server before you can run that.

If not, then specify which line you're referring to.

Ragul M 8-Oct-15 1:41am

Thanks for replying me by taking your time, But i need to do this without modifying sp's.
If it's the case i understand that i cant do this without altering the sp's.