Menu
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?
319
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
