Does `sp_executesql` really accepts the `nvarchar(max)` argument?

Asked 29/9, 2016 at 13:16 Answered 30/6, 2020 at 22:59

Solved sql sql-server sql-server-2014 nvarchar limits

Summary: The EXEC sp_executesql @code fails for the content longer than 4000 in the @code, but the @code is not truncated to 4000 unicode characters.

I am observing the problem on SQL Server 2014 Developer Edition.

More details: my SQL installation script defines some code dynamically because it should modify the code so that it reflects the environment (only once, during the installation). Let the following @datasource variable captures results for the specific environment:

DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'

The @code variable is declared to be of nvarchar(max) type, and the REPLACE function is used to modify the string as needed (that is to replace the placeholder by the @datasource content) -- see the snippet below.

When executing sp_executesql with the @code in the Management Studio, the following error is displayed:

Msg 156, Level 15, State 1, Procedure my_sp, Line 86
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure my_sp, Line 88
Incorrect syntax near 'WHERE'.

The snippet below is the exact copy of the code that fails the above way (to be reproduced). The functionality is probably not important -- probably only the length of the code is. The @code content is apparently truncated by the sp_executesql; however, it should not be (see below):

-- ... repeated from above
DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'

DECLARE @code nvarchar(MAX) = REPLACE(N'
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
CREATE PROCEDURE dbo.my_sp
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @result int = -555   -- Comment comment comment comment comment.

    -- Comment comment comment comment comment comment comment comment comment.
    -- Comment comment comment comment comment comment comment comment comment.
    DECLARE @info_table TABLE (
        action nvarchar(10),    -- Comment comment comment comment comment
        firmaID int,            -- Comment comment comment comment comment
        kod numeric(8, 0),      -- Comment comment comment comment comment
        oz1 nvarchar(40),       -- Comment comment comment comment comment
        oz2 nvarchar(40),       -- Comment comment comment comment comment
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )

-- Comment comment comment comment comment comment comment comment comment.
    BEGIN TRANSACTION tran_firmy
    BEGIN TRY
        MERGE dbo.firmy AS target
        USING (SELECT kod, ico, dic, nazev,
               oz1, oz2, oz3, oz4,
               jeaktivni,
               ulice, mesto, psc
               FROM @datasource) AS source
        ON target.kod = source.kod
        WHEN MATCHED AND (COALESCE(target.ico, '''') != COALESCE(source.ico, '''')
                          OR COALESCE(target.dic, '''') != COALESCE(source.dic, '''')
                          OR COALESCE(target.nazev, '''') != COALESCE(source.nazev, '''')
                          OR COALESCE(target.nepouzivat_oz1, '''') != COALESCE(source.oz1, '''')
                          OR COALESCE(target.nepouzivat_oz2, '''') != COALESCE(source.oz2, '''')
                          OR COALESCE(target.nepouzivat_oz3, '''') != COALESCE(source.oz3, '''')
                          OR COALESCE(target.nepouzivat_oz4, '''') != COALESCE(source.oz4, '''')
                          OR COALESCE(target.jeaktivni, 0) != COALESCE(source.jeaktivni, 0)
                          OR COALESCE(target.ulice, '''') != COALESCE(source.ulice, '''')
                          OR COALESCE(target.mesto, '''') != COALESCE(source.mesto, '''')
                          OR COALESCE(target.psc, '''') != COALESCE(source.psc, '''')
                          ) THEN
            UPDATE
            SET target.ico = source.ico,
                target.dic = source.dic,
                target.nazev = source.nazev,
                target.nepouzivat_oz1 = source.oz1,
                target.nepouzivat_oz2 = source.oz2,
                target.nepouzivat_oz3 = source.oz3,
                target.nepouzivat_oz4 = source.oz4,
                target.jeaktivni = source.jeaktivni,
                target.ulice = source.ulice,
                target.mesto = source.mesto,
                target.psc = source.psc,
                target.changed = GETDATE(),
                target.changedby = ''dialog''
        WHEN NOT MATCHED THEN
            INSERT (kod, ico, dic, nazev,
                    nepouzivat_oz1, nepouzivat_oz2, nepouzivat_oz3, nepouzivat_oz4,
                    jeaktivni,
                    ulice, mesto, psc,
                    created, createdby)
            VALUES (source.kod, source.ico, source.dic, source.nazev,
                    source.oz1, source.oz2, source.oz3, source.oz4,
                    source.jeaktivni,
                    source.ulice, source.mesto, source.psc,
                    GETDATE(), ''dialog'')
        OUTPUT
            $action AS action,  -- INSERT or UPDATE
            inserted.ID AS firmaID,
            inserted.kod AS kod,
            inserted.nepouzivat_oz1 AS oz1,
            inserted.nepouzivat_oz2 AS oz2,
            inserted.nepouzivat_oz3 AS oz3,
            inserted.nepouzivat_oz4 AS oz4
        INTO @info_table;

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        SET @result = @@ROWCOUNT

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        DELETE FROM obchodni_zastupci AS ozt
        WHERE ozt.kod IN (
            SELECT kod FROM @info_table AS it WHER it.action = ''UPDATE''
            )

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        UPDATE dodaci_adresy
            SET custID = f.ID
        FROM firmy AS f,  dodaci_adresy AS da
        WHERE da.custID IS NULL AND f.kod = da.kod_firmy

        COMMIT TRANSACTION tran_firmy
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tran_firmy
        SET @result = -1  -- Comment comment comment comment comment comment comment comment comment.

    END CATCH
    RETURN @result          -- Comment comment comment comment comment comment comment comment comment.
END', N'@datasource', N'testdb.dbo.source_table')


-- The following prints only show that the full-length string is there
PRINT SUBSTRING(@code, 0, 4000)
PRINT '-----------------------------------------------------------'
PRINT SUBSTRING(@code, 4000, 10000)


EXEC sp_executesql @code

-- The following command also does not work (uncomment it).
-- EXEC(@code)

-- Even splitting to two variables and passing the concatenation 
-- does not work. 
-- DECLARE @code1 nvarchar(MAX) = SUBSTRING(@code, 0, 4000)
-- DECLARE @code2 nvarchar(MAX) = SUBSTRING(@code, 4000, 10000)
-- EXEC(@code1 + @code2)

Notice the two PRINT commands. The first one prints the first 4000 characters, the second one the rest. It is cut in the middle of the line, but it is used only to show that the @code really contains the full string.

The documentation for the sp_executesql (Transact-SQL) says:

[ @stmt= ] statement

[...] The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

I have found elsewhere the hint to use EXEC(@code) that does not have the limitation of the sp_executesql. However, it contradicts with the above cited part of the documentation. Moreover, the EXEC(@code) also does not work.

When the same content after the replacement is copy/pasted to the SQL console, it works (that is the procedure is created).

How to solve the case?

Pumpkin answered 29/9, 2016 at 13:16 Comment(2)

Are you asking for a workaround to solve the issue, or are you asking (as hinted in your title) about the inner workings of sp_ExecuteSQL and if it truly uses nvarchar (max)? Your question appears to ask the latter, but your closing question How to solve the case? implies the former. Which are you asking? – Watchmaker 29/9, 2016 at 13:24

Firstly, I need to find any solution. Secondly, I need the clarification. The doc says it should work but it does not work. I have updated the end of the snippet -- the alternative solutions also do not work. Please, try to copy/paste to see. – Pumpkin 29/9, 2016 at 13:35

The sp_executesql does accept NVARCHAR(MAX). The problem is that there is an error is in the query template at the following statement:

    DELETE FROM obchodni_zastupci AS ozt
    WHERE ozt.kod IN (
        SELECT kod FROM @info_table AS it WHER it.action = ''UPDATE''
        )

It should be: as follow:

    DELETE FROM obchodni_zastupci
    WHERE obchodni_zastupci.kod IN (
        SELECT kod FROM @info_table AS it WHERE it.action = ''UPDATE''
        )

The full query should look as follow:

DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'
DECLARE @template NVARCHAR(MAX) = N'
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
CREATE PROCEDURE dbo.my_sp
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @result int = -555   -- Comment comment comment comment comment.

    -- Comment comment comment comment comment comment comment comment comment.
    -- Comment comment comment comment comment comment comment comment comment.
    DECLARE @info_table TABLE (
        action nvarchar(10),    -- Comment comment comment comment comment
        firmaID int,            -- Comment comment comment comment comment
        kod numeric(8, 0),      -- Comment comment comment comment comment
        oz1 nvarchar(40),       -- Comment comment comment comment comment
        oz2 nvarchar(40),       -- Comment comment comment comment comment
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )

-- Comment comment comment comment comment comment comment comment comment.
    BEGIN TRANSACTION tran_firmy
    BEGIN TRY
        MERGE dbo.firmy AS target
        USING (SELECT kod, ico, dic, nazev,
               oz1, oz2, oz3, oz4,
               jeaktivni,
               ulice, mesto, psc
               FROM @datasource) AS source
        ON target.kod = source.kod
        WHEN MATCHED AND (COALESCE(target.ico, '''') != COALESCE(source.ico, '''')
                          OR COALESCE(target.dic, '''') != COALESCE(source.dic, '''')
                          OR COALESCE(target.nazev, '''') != COALESCE(source.nazev, '''')
                          OR COALESCE(target.nepouzivat_oz1, '''') != COALESCE(source.oz1, '''')
                          OR COALESCE(target.nepouzivat_oz2, '''') != COALESCE(source.oz2, '''')
                          OR COALESCE(target.nepouzivat_oz3, '''') != COALESCE(source.oz3, '''')
                          OR COALESCE(target.nepouzivat_oz4, '''') != COALESCE(source.oz4, '''')
                          OR COALESCE(target.jeaktivni, 0) != COALESCE(source.jeaktivni, 0)
                          OR COALESCE(target.ulice, '''') != COALESCE(source.ulice, '''')
                          OR COALESCE(target.mesto, '''') != COALESCE(source.mesto, '''')
                          OR COALESCE(target.psc, '''') != COALESCE(source.psc, '''')
                          ) THEN
            UPDATE
            SET target.ico = source.ico,
                target.dic = source.dic,
                target.nazev = source.nazev,
                target.nepouzivat_oz1 = source.oz1,
                target.nepouzivat_oz2 = source.oz2,
                target.nepouzivat_oz3 = source.oz3,
                target.nepouzivat_oz4 = source.oz4,
                target.jeaktivni = source.jeaktivni,
                target.ulice = source.ulice,
                target.mesto = source.mesto,
                target.psc = source.psc,
                target.changed = GETDATE(),
                target.changedby = ''dialog''
        WHEN NOT MATCHED THEN
            INSERT (kod, ico, dic, nazev,
                    nepouzivat_oz1, nepouzivat_oz2, nepouzivat_oz3, nepouzivat_oz4,
                    jeaktivni,
                    ulice, mesto, psc,
                    created, createdby)
            VALUES (source.kod, source.ico, source.dic, source.nazev,
                    source.oz1, source.oz2, source.oz3, source.oz4,
                    source.jeaktivni,
                    source.ulice, source.mesto, source.psc,
                    GETDATE(), ''dialog'')
        OUTPUT
            $action AS action,  -- INSERT or UPDATE
            inserted.ID AS firmaID,
            inserted.kod AS kod,
            inserted.nepouzivat_oz1 AS oz1,
            inserted.nepouzivat_oz2 AS oz2,
            inserted.nepouzivat_oz3 AS oz3,
            inserted.nepouzivat_oz4 AS oz4
        INTO @info_table;

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        SET @result = @@ROWCOUNT

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        DELETE FROM obchodni_zastupci
        WHERE obchodni_zastupci.kod IN (
            SELECT kod FROM @info_table AS it WHERE it.action = ''UPDATE''
            )

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        UPDATE dodaci_adresy
            SET custID = f.ID
        FROM firmy AS f,  dodaci_adresy AS da
        WHERE da.custID IS NULL AND f.kod = da.kod_firmy

        COMMIT TRANSACTION tran_firmy
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tran_firmy
        SET @result = -1  -- Comment comment comment comment comment comment comment comment comment.

    END CATCH
    RETURN @result          -- Comment comment comment comment comment comment comment comment comment.
END'


DECLARE @code nvarchar(MAX) = REPLACE(@template, N'@datasource', N'testdb.dbo.source_table');

exec (@code);

Simulation answered 29/9, 2016 at 14:3 Comment(3)

@Pumpkin I don't think this issue has anything to do with length of your string after fixing syntax issues it runs successfully. However, I can't help but feel this is kind of a bad use of dynamic sql because it would just be so hard to maintain. If you have a databse project in visual studio or I guess without it you could check out use sqlcmd instead to set variables – Besom 29/9, 2016 at 15:7

@EdmondQuinton: You are right. I will check it tomorrow. – Pumpkin 29/9, 2016 at 20:13

You are right. It was a double-fault on my side. Originally, I did use nvarchar(4000) that caused very similar error. Then I added the problematic part. Then I changed the variable type to nvarchar(MAX), and the similar error appeared -- and that made me thinking about nvarchar(MAX) does not work. Thanks a lot for your pair of eyes ;) – Pumpkin 30/9, 2016 at 7:5

Your query look like it exceed max limit of nvarchar 4000, in such cases you have to split your dynamic query in two part.

Declare @QueryA NVARCHAR(MAX),@QueryB NVARCHAR(MAX)

SET @QueryA='SELECT * FROM'
SET @QueryB=' Employee'

EXEC (@QueryA+@QueryB)

Note: If still same error try to split in more part

Deli answered 29/9, 2016 at 13:19 Comment(4)

I think you're missing the point of the question, but it's honestly hard to tell... The entire post seems to be asking why is this happening or does it really use nvarchar (max)?, but the last sentence asks for a workaround... This answers the workaround, but I don't think that was the OPs intended question. It's honestly hard to tell until they clarify. – Watchmaker 29/9, 2016 at 13:30

Thanks, Sandip. (No, I did not downvote :) I have added the last, commented-out lines to the snippet. Please, try to copy/paste the snippet and uncomment the last lines to try. It also does not work. – Pumpkin 29/9, 2016 at 13:33

Sandip -- what you should add here is that, "yes, it accepts NVARCHAR(MAX), but if it's more than 4000 characters it won't work quite right without a workaround" – Kobarid 29/9, 2016 at 15:17

I am a downvote read my answer to see why. I have tested both EXEC & sp_executesql with 100,000 NVARCHAR characters with NO issue from 2008 + – Besom 29/9, 2016 at 15:49

I have no idea why the error:

Msg 156, Level 15, State 1, Procedure my_sp, Line 86
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure my_sp, Line 88
Incorrect syntax near 'WHERE'.

was interpreted as possibly the length of your string being too long. It is clearly a syntax error. you had 2 mistakes as Edmon pointed out.

Anyway, I am posting this answer to dispel a myth that is being created by another answer and your suggestion in your question that length is being an issue because your statement is over 4,000 characters. here is a script to make a 100,000 character length NVARCHAR SQL statement and execute it as EXEC (@SQL) and sp_executeSQL. Neither had a problem executing on SQL 2008 SP4-OD 10.0.6547.0 (x64) and also no issue on 2014 SP2.

So it would appear that there is NO issue, NO work around is needed at lease since that 2008 edition.

DECLARE @CharacterLength INT = 100000
DECLARE @SQL NVARCHAR(MAX) = 'SELECT ' + CHAR(39)

DECLARE @i INT = 1

WHILE (LEN(@SQL) <= @CharacterLength - 2)
BEGIN

    SET @SQL = @SQL + 'A'

END

SET @SQL = @SQL + CHAR(39)

PRINT 'Total Length: ' + CAST(LEN(@SQL) AS VARCHAR(100))

EXECUTE sp_executesql @sql

PRINT 'No Problem with sp_executesql'

BEGIN TRY
    PRINT 'Total Length: ' + CAST(LEN(@SQL) AS VARCHAR(100))
    EXEC (@SQL)
    PRINT 'No Problem with EXEC (@SQL)'
END TRY
BEGIN CATCH
    PRINT 'Yep never got here because there was no problem with over this character limit'
END CATCH

Besom answered 29/9, 2016 at 15:44 Comment(0)

-2

This same situation plagued me for a bit. And what the solution was for me was not declaring more than one NVARCHAR(MAX) variable.

In building out dynamic SQL, you may be using NVARCHAR(MAX) for the substrings that get combined into the final SQL Query variable that is passed to sp_executesql.

NVARCHAR(MAX) has a MAX memory allocation of 2GB. Your server may be demarcating the full 2GB PER NVARCHAR(MAX) declared. If you have, say, three NVARCHAR(MAX) vars declared, your server may be allocating 6GBs to execute your script. That may be enough to overload your RAM, depending on what else is executing at runtime.

If you know that the substrings are all going to be comfortably less than 8,000 characters, use VARCHAR(8000) instead of NVARCHAR(MAX) for the substrings. Just use NVARCHAR(MAX) for the final string variable (where all substring vars are combined) that is passed into sp_executesql.

This is what solved this issue for me.

Lyell answered 30/6, 2020 at 22:59 Comment(1)

No, the server doesn't allocate 2GB of RAM per max variable. Whatever your issue was you have clearly misinterpreted what was going on – Cupp 30/6, 2020 at 23:9

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags