Действительно ли `sp_executesql` принимает аргумент `nvarchar (max)`?

#sql #sql-server #sql-server-2014 #nvarchar #limits

#sql #sql-сервер #sql-server-2014 #nvarchar #ограничения

Вопрос:

Резюме: EXEC sp_executesql @code сбой для содержимого длиной более 4000 в @code , но @code не усекается до 4000 символов Юникода.

Я наблюдаю проблему на SQL Server 2014 Developer Edition.

Подробнее: мой сценарий установки SQL определяет некоторый код динамически, потому что он должен изменять код так, чтобы он отражал среду (только один раз, во время установки). Пусть следующая @datasource переменная фиксирует результаты для конкретной среды:

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

@code Переменная объявляется nvarchar(max) как тип, и REPLACE функция используется для изменения строки по мере необходимости (то есть для замены заполнителя @datasource содержимым) — см. Фрагмент ниже.

При выполнении sp_executesql с @code помощью в Management Studio отображается следующая ошибка:

Сообщение 156, уровень 15, состояние 1, процедура my_sp, строка 86
Неправильный синтаксис рядом с ключевым словом ‘AS’.
Сообщение 102, уровень 15, состояние 1, процедура my_sp, строка 88
Неправильный синтаксис рядом с ‘WHERE’.

Приведенный ниже фрагмент является точной копией кода, который не работает указанным выше способом (для воспроизведения). Функциональность, вероятно, не важна — вероятно, важна только длина кода. @code Содержимое, по-видимому, усекается sp_executesql ; однако этого не должно быть (см. Ниже):

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

Обратите внимание на две PRINT команды. Первый выводит первые 4000 символов, второй — остальные. Он вырезается в середине строки, но используется только для того, чтобы показать, что @code действительно содержит полную строку.

В документации для sp_executesql (Transact-SQL) говорится:

[ @stmt = ] оператор

[…] Размер строки ограничен только доступной памятью сервера базы данных. На 64-разрядных серверах размер строки ограничен 2 ГБ, что является максимальным размером nvarchar (max).

Я нашел в другом месте подсказку для использования EXEC(@code) , которая не имеет ограничений sp_executesql . Однако это противоречит приведенной выше части документации. Более того, EXEC(@code) также не работает.

Когда тот же контент после замены копируется / вставляется в консоль SQL, он работает (то есть создается процедура).

Как решить проблему?

1. Вы просите обходной путь для решения проблемы или вы спрашиваете (как указано в вашем названии) о внутренней работе sp_ExecuteSQL и действительно ли она используется nvarchar (max) ? Ваш вопрос, похоже, задает последнее, но ваш заключительный вопрос How to solve the case? подразумевает первое. О чем вы спрашиваете?

2. Во-первых, мне нужно найти какое-либо решение. Во-вторых, мне нужно разъяснение. В документе говорится, что он должен работать, но он не работает. Я обновил конец фрагмента — альтернативные решения также не работают. Пожалуйста, попробуйте скопировать / вставить, чтобы увидеть.

Ответ №1:

sp_executesql принимает NVARCHAR(MAX) . Проблема в том, что в шаблоне запроса есть ошибка в следующем утверждении:

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

Это должно быть: следующим образом:

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

Полный запрос должен выглядеть следующим образом:

 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);

1. @pepr Я не думаю, что эта проблема имеет какое-либо отношение к длине вашей строки, после устранения проблем с синтаксисом она выполняется успешно. Однако я не могу не чувствовать, что это своего рода плохое использование динамического sql, потому что его было бы так сложно поддерживать. Если у вас есть проект базы данных в Visual Studio или, я думаю, без него, вы могли бы проверить использование sqlcmd вместо установки переменных

2. @EdmondQuinton: Вы правы. Я проверю это завтра.

3. Вы правы. С моей стороны это была двойная ошибка. Первоначально я использовал nvarchar(4000) это, что вызвало очень похожую ошибку. Затем я добавил проблемную часть. Затем я изменил тип переменной на nvarchar(MAX) , и появилась аналогичная ошибка — и это заставило меня задуматься о nvarchar(MAX) том, что не работает. Большое спасибо за вашу пару глаз 😉

Ответ №2:

Похоже, что ваш запрос превышает максимальный предел nvarchar 4000, в таких случаях вам нужно разделить ваш динамический запрос на две части.

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

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

EXEC (@QueryA @QueryB)

Примечание: если все та же ошибка, попробуйте разделить на более части

1.Я думаю, вы упускаете суть вопроса, но, честно говоря, трудно сказать… Кажется, что весь пост спрашивает, почему это происходит или действительно используется nvarchar (max) ?, но в последнем предложении запрашивается обходной путь… Это отвечает на обходной путь, но я не думаю, что это был предполагаемый вопрос OPs. Честно говоря, трудно сказать, пока они не прояснят.

2. Спасибо, Сандип. (Нет, я не понизил голос 🙂 Я добавил последние закомментированные строки во фрагмент. Пожалуйста, попробуйте скопировать / вставить фрагмент и раскомментировать последние строки, чтобы попробовать. Это также не работает.

3. Sandip — здесь следует добавить следующее: «да, он принимает NVARCHAR (MAX), но если он содержит более 4000 символов, он не будет работать правильно без обходного пути»

4. Я отрицательный ответ, прочитайте мой ответ, чтобы понять, почему. Я протестировал оба EXEC amp; sp_executesql с 100 000 символов NVARCHAR без КАКИХ-ЛИБО проблем с 2008 года

Ответ №3:

Я понятия не имею, почему ошибка:

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

было интерпретировано как, возможно, слишком длинная длина вашей строки. Это явно синтаксическая ошибка. у вас было 2 ошибки, как указал Эдмон.

В любом случае, я публикую этот ответ, чтобы развеять миф, который создается другим ответом, и ваше предложение в вашем вопросе о том, что длина является проблемой, потому что ваше утверждение превышает 4,000 символы. вот сценарий для создания 100,000 NVARCHAR SQL-оператора символьной длины и выполнения его как EXEC (@SQL) и sp_executeSQ L. Ни у кого не было проблем с выполнением на SQL 2008 SP4-OD 10.0.6547.0 (x64), а также никаких проблем с 2014 SP2.

Таким образом, может показаться, что проблемы нет, НИКАКИХ обходных действий при аренде не требуется с момента выпуска 2008 года.

 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

Ответ №4:

Эта же ситуация немного беспокоила меня. И для меня решением было не объявлять более одной переменной NVARCHAR (MAX).

При построении динамического SQL вы можете использовать NVARCHAR (MAX) для подстрок, которые объединяются в конечную переменную SQL-запроса, которая передается в sp_executesql.

NVARCHAR (MAX) имеет МАКСИМАЛЬНОЕ выделение памяти 2 ГБ. Возможно, ваш сервер разграничивает полные 2 ГБ НА объявленный NVARCHAR (MAX). Если у вас, скажем, объявлено три переменных NVARCHAR (MAX), ваш сервер может выделять 6 ГБ для выполнения вашего скрипта. Этого может быть достаточно, чтобы перегрузить вашу оперативную память, в зависимости от того, что еще выполняется во время выполнения.

Если вы знаете, что все подстроки будут удобно содержать менее 8000 символов, используйте VARCHAR(8000) вместо NVARCHAR (MAX) для подстрок. Просто используйте NVARCHAR (MAX) для конечной строковой переменной (где объединяются все переменные подстроки), которая передается в sp_executesql.

Это то, что решило эту проблему для меня.