none
Dynamic SQL query cannot store more than 4000 characters even with NVARCHAR(MAX) RRS feed

  • Question

  • Hello, everyone.

    I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql.

    One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable!

    I use SQL Server 2008. All the documentation online I checked states that the solution is just using NVARCHAR(MAX) and the problem should not happen. How can this keep happening?

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Thursday, April 1, 2010 4:22 PM

Answers

  • When dealing with an NVARCHAR(MAX) variable, you have to explicitly cast any literals or expressions that you are concatenating to be NVARCHAR(MAX).

    For example, this will end up populating the variable with only 4000 chars:

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = REPLICATE(N'A',3000) + REPLICATE(N'B',3000)
    SELECT LEN(@SQL)  --Returns 4000

    You have to do it this way to get the full 6000 chars:

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = CAST(REPLICATE(N'A',3000) AS NVARCHAR(MAX)) + CAST(REPLICATE(N'B',3000) AS NVARCHAR(MAX))
    SELECT LEN(@SQL)  --Returns 6000

     


    --Brad (My Blog)
    Thursday, April 1, 2010 5:52 PM
    Moderator
  • Ivo,

    The following is DEMO ONLY T-SQL script, no business meaning. It is over 4000 bytes dynamic SQL and works!

    As Brad, Adam & Naomi pointed out there are some traps when assembling/testing  nvarchar(max) dynamic SQL script, so it requires extra verification to make sure that the assembly valid and correct.

    Let us know if helpful.

    -- DEMO ONLY! NO BUSINESS MEANING!
    -- T-SQL over 4000 bytes dynamic SQL demo
    -- String concatenation requires verfication
    -- Using CONVERT(NVARCHAR(max)... for each component 
    DECLARE @SQL nvarchar(max)=N'SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt'
    SET @SQL=@SQL+N'
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt'
    SELECT LEN(@SQL), DATALENGTH(@SQL)
    -- 6705	13410
    EXEC sp_executeSQL @SQL
    
    

    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016







    Thursday, April 8, 2010 6:17 PM
    Moderator
  • Hi,

    I faced a similar issue and fixed it in the below way:

    DECLARE @sqlText1 NVARCHAR(4000), @sqlText2 NVARCHAR(4000)

    DECLARE @sqlText3 NVARCHAR(MAX)

    SET @sqlText1 = N'...Divide the query for 4000 characters....'

    SET @sqlText2 = N'...Divide the query for 4000 characters....'

    ....

    EXEC (@sqlText1 + @sqlText2)

     

    ----sp_executesql execution

    SET @sqlText3 = CAST @sqlText1 AS nvarchar(MAX)) + CAST (@sqlText2 AS nvarchar(MAX)) + ....

    EXEC sp_executesql @sqlText3 

     

    Let me know if this helped you.....

     

    Thanks,

    Sharmin

    Tuesday, December 6, 2011 11:16 PM

All replies

  • Are you using any old code that may be limiting the stored procedure capability to 4000 characers?

    This might not be the issue, but try using EXEC() instead of the stored procedure sp_executesql and see if that will work.

     


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, April 1, 2010 4:45 PM
  • Hello, Abdshall.

     

    In theory, no, my code is recent, uses sp_executesql, and I preceed my dynamic query variable with N'' to make it Unicode.

    All I do is something like this: (just a reference query, does not exceed the 4000 character limit of course :) )

     

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = N'UPDATE ' + @DBName + '.' + @TableName + 'field SET myfield=1'

    EXEC sp_executesql @SQL

     

    I have not yet tried to use EXEC() because this is considered "deprecated" and would make me change a whole lot of lines in the code...

     

    This kind of query does not need any parameters to be passed to sp_executesql. So, the problem seems even stranger...

    I run SQL Server 2008 x64, my client computer is Windows Vista, updated, with SQL Server 2008 Management Studio...

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Thursday, April 1, 2010 4:57 PM
  • The following worked just fine for me.

    CREATE TABLE Test(ID INT,Value VARCHAR(20))
    
    DECLARE @DBName VARCHAR(20),@TableName VARCHAR(20)
    SET @DBName = 'databasename'
    SET @TableName = 'Test'
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'UPDATE ' + @DBName + '..' + @TableName + ' SET ID=1'
    PRINT(@sql)
    EXEC sp_executesql @SQL
    
    DROP TABLE Test
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, April 1, 2010 5:27 PM
  • When dealing with an NVARCHAR(MAX) variable, you have to explicitly cast any literals or expressions that you are concatenating to be NVARCHAR(MAX).

    For example, this will end up populating the variable with only 4000 chars:

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = REPLICATE(N'A',3000) + REPLICATE(N'B',3000)
    SELECT LEN(@SQL)  --Returns 4000

    You have to do it this way to get the full 6000 chars:

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = CAST(REPLICATE(N'A',3000) AS NVARCHAR(MAX)) + CAST(REPLICATE(N'B',3000) AS NVARCHAR(MAX))
    SELECT LEN(@SQL)  --Returns 6000

     


    --Brad (My Blog)
    Thursday, April 1, 2010 5:52 PM
    Moderator
  • Yes, this worked

    DECLARE @DBName VARCHAR(20),@TableName VARCHAR(20)
    SET @DBName = 'AllTests'
    SET @TableName = 'Cases'
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = cast(N'UPDATE ' + @DBName + '..' + @TableName as nvarchar(max)) + 
    cast(space(4000) as nvarchar(max)) + cast(' SET Ban=Ban' as nvarchar(max))
    PRINT(@sql)
    EXECUTE sp_executeSQL @SQL
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 1, 2010 6:06 PM
    Moderator
  • Hello, everyone.

    I still do not have a solution for this problem. I cannot understand, from reading Books Online or these threads, if NVARCHAR(MAX) can or cannot store those 8000 characters..

    I would like to avoid concatenating parts of my query toghether, since these dynamic queries span across several tables and not all of them have this problem...

     

    I already installed an x64 SQL Server 2005 Developer Edition instance just to test this, and one of my dynamic queries (one that has more than 6000 characters...) works fine.

    However, another query in the same file with LESS characters (more than 4000) does not work!!! They both use the same variable, @SQL, declared in the same way, (NVARCHAR(MAX).

     

    How can this be possible? Am I missing something here?

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal

     


    ComputerDoc
    Tuesday, April 6, 2010 11:37 AM
  • Hello, SQLUSA.

     

    Yes, I can post both queries here. Of course they are so big maybe reading them can be difficult..

    Note to readers: these queries may seem badly-formed, having all column fields expressed in them, but this has to be done this way. I run these queries across several different databases, all with different development status (by this I mean not all tables have the exact same columns, column order, etc).

    So this is the first query that works fine (approx. 6000+ chars):

    SET @SQL = N'UPDATE ' + @DestServer + '.' + @DestDB + '.dbo.cl SET [acc]=clx.[acc],[acmfact] = clx.[acmfact],[addd] = clx.[addd],[agno] = clx.[agno],[alimite] = clx.[alimite],[area] = clx.[area],[bidata] = clx.[bidata],[bilocal] = clx.[bilocal],[bino] = clx.[bino],[bizzaddress] = clx.[bizzaddress],[bizzproto] = clx.[bizzproto],[blck] = clx.[blck],[c1fax] = clx.[c1fax],[c1func] = clx.[c1func],[c1tele] = clx.[c1tele],[c2fax] = clx.[c2fax],[c2func] = clx.[c2func],[c2tacto] = clx.[c2tacto],[c2tele] = clx.[c2tele],[c3fax] = clx.[c3fax],[c3func] = clx.[c3func],[c3tacto] = clx.[c3tacto],[c3tele] = clx.[c3tele],[carr] = clx.[carr],[cass] = clx.[cass],[ccusto] = clx.[ccusto],[classe] = clx.[classe],[clifactor] = clx.[clifactor],[clinica] = clx.[clinica],[clivd] = clx.[clivd],[clstamp] = clx.[clstamp],[cobfax] = clx.[cobfax],[cobfunc] = clx.[cobfunc],[cobnao] = clx.[cobnao],[cobrador] = clx.[cobrador],[cobranca] = clx.[cobranca],[cobtacto] = clx.[cobtacto],[cobtele] = clx.[cobtele],[codfornecedor] = clx.[codfornecedor],[codpost] = clx.[codpost],[conta] = clx.[conta],[contaacer] = clx.[contaacer],[contaainc] = clx.[contaainc],[contacto] = clx.[contacto],[contado] = clx.[contado],[contafac] = clx.[contafac],[contalet] = clx.[contalet],[contaletdes] = clx.[contaletdes],[contaletsac] = clx.[contaletsac],[contatit] = clx.[contatit],[cw] = clx.[cw],[descarga] = clx.[descarga],[desccmb] = clx.[desccmb],[descloj] = clx.[descloj],[desconto] = clx.[desconto],[descpp] = clx.[descpp],[dformacao] = clx.[dformacao],[dfront] = clx.[dfront],[did] = clx.[did],[dqtt] = clx.[dqtt],[dqttval] = clx.[dqttval],[dsuporte] = clx.[dsuporte],[dteam] = clx.[dteam],[eacmfact] = clx.[eacmfact],[eag] = clx.[eag],[eancl] = clx.[eancl],[ecoisento] = clx.[ecoisento],[ediexp] = clx.[ediexp],[eem] = clx.[eem],[efl] = clx.[efl],[eid] = clx.[eid],[email] = clx.[email],[emno] = clx.[emno],[encm] = clx.[encm],[encmdesc] = clx.[encmdesc],[encrpin] = clx.[encrpin],[eplafond] = clx.[eplafond],[erentval] = clx.[erentval],[esaldlet] = clx.[esaldlet],[esaldo] = clx.[esaldo],[estab] = clx.[estab],[excm] = clx.[excm],[excmdesc] = clx.[excmdesc],[exporpos] = clx.[exporpos],[fax] = clx.[fax],[filtrast] = clx.[filtrast],[flestab] = clx.[flestab],[flno] = clx.[flno],[fref] = clx.[fref],[ftdatasmr] = clx.[ftdatasmr],[ftdiasmr] = clx.[ftdiasmr],[ftidbi] = clx.[ftidbi],[ftidcob] = clx.[ftidcob],[ftidcont] = clx.[ftidcont],[ftidcontacto] = clx.[ftidcontacto],[ftidnac] = clx.[ftidnac],[ftidnome] = clx.[ftidnome],[ftidutente] = clx.[ftidutente],[ftmrtot] = clx.[ftmrtot],[ftndias] = clx.[ftndias],[ftnid] = clx.[ftnid],[ftumamr] = clx.[ftumamr],[fuels] = clx.[fuels],[gaecstamp] = clx.[gaecstamp],[gaenome] = clx.[gaenome],[glncl] = clx.[glncl],[id] = clx.[id],[idno] = clx.[idno],[iectisento] = clx.[iectisento],[imagem] = clx.[imagem],[inactivo] = clx.[inactivo],[lang] = clx.[lang],[lmlt] = clx.[lmlt],[local] = clx.[local],[localentrega] = clx.[localentrega],[ltyp] = clx.[ltyp],[marcada] = clx.[marcada],[matric] = clx.[matric],[moeda] = clx.[moeda],[morada] = clx.[morada],[naoencomenda] = clx.[naoencomenda],[naomail] = clx.[naomail],[naood] = clx.[naood],[nascimento] = clx.[nascimento],[naturalid] = clx.[naturalid],[ncont] = clx.[ncont],[ncusto] = clx.[ncusto],[nib] = clx.[nib],[niec] = clx.[niec],[no] = clx.[no],[nocredit] = clx.[nocredit],[nome] = clx.[nome],[nome2] = clx.[nome2],[ntcm] = clx.[ntcm],[obs] = clx.[obs],[obsdoc] = clx.[obsdoc],[odatraso] = clx.[odatraso],[odo] = clx.[odo],[ollocal] = clx.[ollocal],[ousrdata] = clx.[ousrdata],[ousrhora] = clx.[ousrhora],[ousrinis] = clx.[ousrinis],[pagamento] = clx.[pagamento],[pais] = clx.[pais],[paramr] = clx.[paramr],[particular] = clx.[particular],[passaporte] = clx.[passaporte],[pin] = clx.[pin],[plafond] = clx.[plafond],[pncont] = clx.[pncont],[preco] = clx.[preco],[pscm] = clx.[pscm],[pscmdesc] = clx.[pscmdesc],[ptcm] = clx.[ptcm],[ptcmdesc] = clx.[ptcmdesc],[rbal] = clx.[rbal],[recdocdig] = clx.[recdocdig],[refcli] = clx.[refcli],[rentval] = clx.[rentval],[repl] = clx.[repl],[rota] = clx.[rota],[saldlet] = clx.[saldlet],[saldo] = clx.[saldo],[segmento] = clx.[segmento],[shop] = clx.[shop],[site] = clx.[site],[statuspda] = clx.[statuspda],[tabiva] = clx.[tabiva],[tbprcod] = clx.[tbprcod],[telefone] = clx.[telefone],[temcred] = clx.[temcred],[temftglob] = clx.[temftglob],[tipo] = clx.[tipo],[tipodesc] = clx.[tipodesc],[tlmvl] = clx.[tlmvl],[tpdesc] = clx.[tpdesc],[tpstamp] = clx.[tpstamp],[track] = clx.[track],[tracknr] = clx.[tracknr],[txftdata] = clx.[txftdata],[txftdias] = clx.[txftdias],[txftidbi] = clx.[txftidbi],[txftidcob] = clx.[txftidcob],[txftidcont] = clx.[txftidcont],[txftidcontacto] = clx.[txftidcontacto],[txftidnac] = clx.[txftidnac],[txftidnome] = clx.[txftidnome],[txftidutente] = clx.[txftidutente],[txftmrtot] = clx.[txftmrtot],[txftndias] = clx.[txftndias],[txftnid] = clx.[txftnid],[u_codpla] = clx.[u_codpla],[u_codpla2] = clx.[u_codpla2],[u_despla] = clx.[u_despla],[u_despla2] = clx.[u_despla2],[u_entpla] = clx.[u_entpla],[u_entpla2] = clx.[u_entpla2],[u_nrcartao] = clx.[u_nrcartao],[u_orgstamp] = clx.[u_orgstamp],[u_pato1] = clx.[u_pato1],[u_pato2] = clx.[u_pato2],[u_pato3] = clx.[u_pato3],[u_perfilu1] = clx.[u_perfilu1],[u_perfilu2] = clx.[u_perfilu2],[u_perfilu3] = clx.[u_perfilu3],[u_perfilu4] = clx.[u_perfilu4],[u_protopla] = clx.[u_protopla],[u_stpato1] = clx.[u_stpato1],[u_stpato2] = clx.[u_stpato2],[u_stpato3] = clx.[u_stpato3],[u_ultvenda] = clx.[u_ultvenda],[u_utenten2] = clx.[u_utenten2],[u_utenteno] = clx.[u_utenteno],[u_valipla] = clx.[u_valipla],[u_valipla2] = clx.[u_valipla2],[url] = clx.[url],[usaintra] = clx.[usaintra],[usrdata] = clx.[usrdata],[usrhora] = clx.[usrhora],[usrinis] = clx.[usrinis],[vencimento] = clx.[vencimento],[vendedor] = clx.[vendedor],[vendnm] = clx.[vendnm],[zncm] = clx.[zncm],[znregiao] = clx.[znregiao],[zona] = clx.[zona] FROM ' + @DestServer + '.' + @DestDB + '.dbo.cl cly WITH (NOLOCK) INNER JOIN ' + @SourceServer + '.' + @SourceDB + '.dbo.[cl] clx WITH (NOLOCK) ON cly.[no]=clx.[no] AND cly.estab=clx.estab WHERE clx.usrdata != cly.usrdata OR clx.usrhora != cly.usrhora'

    I could remove all the [], of course, and spaces, but... the point is to have this working WITH this stuff!! Besides I generate these lines automatically with another script I have.. (will post it later for you guys to check it out).

    This is the query that DOES NOT work:

    SET @SQL = 'UPDATE ' + @DestServer + '.' + @DestDB + '.dbo.st SET baixr = stx.baixr,bloqueado = stx.bloqueado,cancpos = stx.cancpos,clinica = stx.clinica,codcmb = stx.codcmb,codfiscal = stx.codfiscal,codigo = stx.codigo,compnovo = stx.compnovo,consumo = stx.consumo,contacev = stx.contacev,contacoe = stx.contacoe,contaieo = stx.contaieo,containv = stx.containv,contareo = stx.contareo,conversao = stx.conversao,convunsup = stx.convunsup,cpoc = stx.cpoc,custo = stx.custo,custof = stx.custof,custog = stx.custog,datanovpv = stx.datanovpv,datar = stx.datar,desc1 = stx.desc1,desc2 = stx.desc2,desc3 = stx.desc3,desc4 = stx.desc4,desc5 = stx.desc5,desc6 = stx.desc6,desci = stx.desci,descii = stx.descii,descrmo = stx.descrmo,design = stx.design,despimp = stx.despimp,diaseoq = stx.diaseoq,diaspto = stx.diaspto,ecoacumulador = stx.ecoacumulador,ecoel = stx.ecoel,ecoeval = stx.ecoeval,ecomissao = stx.ecomissao,ecool = stx.ecool,ecooval = stx.ecooval,ecopilha = stx.ecopilha,ecopl = stx.ecopl,ecopval = stx.ecopval,ecorl = stx.ecorl,ecorval = stx.ecorval,ecoval = stx.ecoval,ecovalor = stx.ecovalor,ecusto = stx.ecusto,ecustof = stx.ecustof,ecustog = stx.ecustog,eecoeval = stx.eecoeval,eecooval = stx.eecooval,eecopval = stx.eecopval,eecorval = stx.eecorval,eecoval = stx.eecoval,eiectin = stx.eiectin,eoq = stx.eoq,epcdisp = stx.epcdisp,epclab = stx.epclab,epcmo = stx.epcmo,epcpond = stx.epcpond,epcult = stx.epcult,epcusto = stx.epcusto,epmvenda = stx.epmvenda,epv1 = stx.epv1,epv2 = stx.epv2,epv3 = stx.epv3,epv4 = stx.epv4,epv5 = stx.epv5,epvcon = stx.epvcon,epvultimo = stx.epvultimo,familia = stx.familia,faminome = stx.faminome,fobloq = stx.fobloq,fornec = stx.fornec,fornecedor = stx.fornecedor,fornestab = stx.fornestab,forref = stx.forref,garantia = stx.garantia,horanovpv = stx.horanovpv,idudesign = stx.idudesign,iecagrad = stx.iecagrad,iecaisref = stx.iecaisref,iecamultgrad = stx.iecamultgrad,iecaref = stx.iecaref,iecarefnome = stx.iecarefnome,iecasug = stx.iecasug,iecautt = stx.iecautt,iectin = stx.iectin,iectinii = stx.iectinii,iectsug = stx.iectsug,imagem = stx.imagem,imgqlook = stx.imgqlook,inactivo = stx.inactivo,iva1incl = stx.iva1incl,iva2incl = stx.iva2incl,iva3incl = stx.iva3incl,iva4incl = stx.iva4incl,iva5incl = stx.iva5incl,ivaincl = stx.ivaincl,ivapcincl = stx.ivapcincl,lang1 = stx.lang1,lang2 = stx.lang2,lang3 = stx.lang3,lang4 = stx.lang4,lang5 = stx.lang5,langdes1 = stx.langdes1,langdes2 = stx.langdes2,langdes3 = stx.langdes3,langdes4 = stx.langdes4,langdes5 = stx.langdes5,local = stx.local,marcada = stx.marcada,marg1 = stx.marg1,marg2 = stx.marg2,marg3 = stx.marg3,marg4 = stx.marg4,marg5 = stx.marg5,massaliq = stx.massaliq,mesescon = stx.mesescon,mfornec = stx.mfornec,mfornec2 = stx.mfornec2,mod = stx.mod,nccod = stx.nccod,nexist = stx.nexist,noserie = stx.noserie,notimpcp = stx.notimpcp,nsujpp = stx.nsujpp,obs = stx.obs,ofcstamp = stx.ofcstamp,opendata = stx.opendata,orcamento = stx.orcamento,ousrdata = stx.ousrdata,ousrhora = stx.ousrhora,ousrinis = stx.ousrinis,pbruto = stx.pbruto,pcdisp = stx.pcdisp,pcimp = stx.pcimp,pclab = stx.pclab,pcmo = stx.pcmo,pcmoe = stx.pcmoe,pcpond = stx.pcpond,pcult = stx.pcult,pcusto = stx.pcusto,pentrega = stx.pentrega,peso = stx.peso,pmvenda = stx.pmvenda,ptoenc = stx.ptoenc,pv1 = stx.pv1,pv2 = stx.pv2,pv3 = stx.pv3,pv4 = stx.pv4,pv5 = stx.pv5,pvcon = stx.pvcon,pvultimo = stx.pvultimo,qlook = stx.qlook,qttacin = stx.qttacin,qttacout = stx.qttacout,qttcat = stx.qttcat,qttcli = stx.qttcli,qttesp = stx.qttesp,qttfor = stx.qttfor,qttrec = stx.qttrec,qtttouch = stx.qtttouch,qttvend = stx.qttvend,ref = stx.ref,refmo = stx.refmo,restctprep = stx.restctprep,semserprv = stx.semserprv,site = stx.site,statuspda = stx.statuspda,-    stid = stx.stid,stmax = stx.stmax,stmin = stx.stmin,stns = stx.stns,stock = stx.stock,stocktch = stx.stocktch,ststamp = stx.ststamp,tabiva = stx.tabiva,texteis = stx.texteis,tipodesc = stx.tipodesc,tkhclass = stx.tkhclass,txieca = stx.txieca,txiecanome = stx.txiecanome,txtqlook = stx.txtqlook,u_codint = stx.u_codint,u_esgoflnm = stx.u_esgoflnm,u_esgoflno = stx.u_esgoflno,u_esgotado = stx.u_esgotado,u_impetiq = stx.u_impetiq,u_lab = stx.u_lab,u_local = stx.u_local,u_local2 = stx.u_local2,u_nota1 = stx.u_nota1,u_tipoetiq = stx.u_tipoetiq,udata = stx.udata,uintr = stx.uintr,uni2 = stx.uni2,unidade = stx.unidade,unidadef = stx.unidadef,url = stx.url,usaid = stx.usaid,usalote = stx.usalote,usr1 = stx.usr1,usr2 = stx.usr2,usr3 = stx.usr3,usr4 = stx.usr4,usr5 = stx.usr5,usr6 = stx.usr6,usrdata = stx.usrdata,usrhora = stx.usrhora,usrinis = stx.usrinis,usrqtt = stx.usrqtt,vaiwww = stx.vaiwww,validade = stx.validade,valin = stx.valin,valout = stx.valout,vasilhame = stx.vasilhame,volume = stx.volume FROM ' + @DestServer + '.' + @DestDB + '.dbo.st sty WITH (NOLOCK) INNER JOIN ' + @SourceServer + '.' + @SourceDB + '.dbo.st stx WITH (NOLOCK) ON sty.ref=stx.ref AND sty.ststamp = stx.ststamp WHERE stx.usrdata != sty.usrdata OR stx.usrhora != sty.usrhora'

    I removed, for testing purposes, the [] I mencioned before, but with no luck. In the file's start I have the usual DECLARE @SQL NVARCHAR(MAX).

    I am running this query on SQL Server 2005 x64 as I posted before...

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal

     

     


    ComputerDoc
    Tuesday, April 6, 2010 1:17 PM
  • Try casting each of the terms to nvarchar(max), e.g.

    SET @SQL = cast('UPDATE ' as nvarchar(max)) + cast(@DestServer as nvarchar(max)) + cast('.' as nvarchar(max) + cast(@DestDB as nvarchar(max)) + cast('.dbo.st SET baixr = stx.baixr,bloqueado = stx.bloqueado,cancpos = stx.cancpos,clinica = stx.clinica,codcmb = stx.codcmb,codfiscal = stx.codfiscal,codigo = stx.codigo,compnovo = stx.compnovo,consumo = stx.consumo,contacev = stx.contacev,contacoe = stx.contacoe,contaieo = stx.contaieo,containv = stx.containv,contareo = stx.contareo,conversao = stx.conversao,convunsup = stx.convunsup,cpoc = stx.cpoc,custo = stx.custo,custof = stx.custof,custog = stx.custog,datanovpv = stx.datanovpv,datar = stx.datar,desc1 = stx.desc1,desc2 = stx.desc2,desc3 = stx.desc3,desc4 = stx.desc4,desc5 = stx.desc5,desc6 = stx.desc6,desci = stx.desci,descii = stx.descii,descrmo = stx.descrmo,design = stx.design,despimp = stx.despimp,diaseoq = stx.diaseoq,diaspto = stx.diaspto,ecoacumulador = stx.ecoacumulador,ecoel = stx.ecoel,ecoeval = stx.ecoeval,ecomissao = stx.ecomissao,ecool = stx.ecool,ecooval = stx.ecooval,ecopilha = stx.ecopilha,ecopl = stx.ecopl,ecopval = stx.ecopval,ecorl = stx.ecorl,ecorval = stx.ecorval,ecoval = stx.ecoval,ecovalor = stx.ecovalor,ecusto = stx.ecusto,ecustof = stx.ecustof,ecustog = stx.ecustog,eecoeval = stx.eecoeval,eecooval = stx.eecooval,eecopval = stx.eecopval,eecorval = stx.eecorval,eecoval = stx.eecoval,eiectin = stx.eiectin,eoq = stx.eoq,epcdisp = stx.epcdisp,epclab = stx.epclab,epcmo = stx.epcmo,epcpond = stx.epcpond,epcult = stx.epcult,epcusto = stx.epcusto,epmvenda = stx.epmvenda,epv1 = stx.epv1,epv2 = stx.epv2,epv3 = stx.epv3,epv4 = stx.epv4,epv5 = stx.epv5,epvcon = stx.epvcon,epvultimo = stx.epvultimo,familia = stx.familia,faminome = stx.faminome,fobloq = stx.fobloq,fornec = stx.fornec,fornecedor = stx.fornecedor,fornestab = stx.fornestab,forref = stx.forref,garantia = stx.garantia,horanovpv = stx.horanovpv,idudesign = stx.idudesign,iecagrad = stx.iecagrad,iecaisref = stx.iecaisref,iecamultgrad = stx.iecamultgrad,iecaref = stx.iecaref,iecarefnome = stx.iecarefnome,iecasug = stx.iecasug,iecautt = stx.iecautt,iectin = stx.iectin,iectinii = stx.iectinii,iectsug = stx.iectsug,imagem = stx.imagem,imgqlook = stx.imgqlook,inactivo = stx.inactivo,iva1incl = stx.iva1incl,iva2incl = stx.iva2incl,iva3incl = stx.iva3incl,iva4incl = stx.iva4incl,iva5incl = stx.iva5incl,ivaincl = stx.ivaincl,ivapcincl = stx.ivapcincl,lang1 = stx.lang1,lang2 = stx.lang2,lang3 = stx.lang3,lang4 = stx.lang4,lang5 = stx.lang5,langdes1 = stx.langdes1,langdes2 = stx.langdes2,langdes3 = stx.langdes3,langdes4 = stx.langdes4,langdes5 = stx.langdes5,local = stx.local,marcada = stx.marcada,marg1 = stx.marg1,marg2 = stx.marg2,marg3 = stx.marg3,marg4 = stx.marg4,marg5 = stx.marg5,massaliq = stx.massaliq,mesescon = stx.mesescon,mfornec = stx.mfornec,mfornec2 = stx.mfornec2,mod = stx.mod,nccod = stx.nccod,nexist = stx.nexist,noserie = stx.noserie,notimpcp = stx.notimpcp,nsujpp = stx.nsujpp,obs = stx.obs,ofcstamp = stx.ofcstamp,opendata = stx.opendata,orcamento = stx.orcamento,ousrdata = stx.ousrdata,ousrhora = stx.ousrhora,ousrinis = stx.ousrinis,pbruto = stx.pbruto,pcdisp = stx.pcdisp,pcimp = stx.pcimp,pclab = stx.pclab,pcmo = stx.pcmo,pcmoe = stx.pcmoe,pcpond = stx.pcpond,pcult = stx.pcult,pcusto = stx.pcusto,pentrega = stx.pentrega,peso = stx.peso,pmvenda = stx.pmvenda,ptoenc = stx.ptoenc,pv1 = stx.pv1,pv2 = stx.pv2,pv3 = stx.pv3,pv4 = stx.pv4,pv5 = stx.pv5,pvcon = stx.pvcon,pvultimo = stx.pvultimo,qlook = stx.qlook,qttacin = stx.qttacin,qttacout = stx.qttacout,qttcat = stx.qttcat,qttcli = stx.qttcli,qttesp = stx.qttesp,qttfor = stx.qttfor,qttrec = stx.qttrec,qtttouch = stx.qtttouch,qttvend = stx.qttvend,ref = stx.ref,refmo = stx.refmo,restctprep = stx.restctprep,semserprv = stx.semserprv,site = stx.site,statuspda = stx.statuspda,-    stid = stx.stid,stmax = stx.stmax,stmin = stx.stmin,stns = stx.stns,stock = stx.stock,stocktch = stx.stocktch,ststamp = stx.ststamp,tabiva = stx.tabiva,texteis = stx.texteis,tipodesc = stx.tipodesc,tkhclass = stx.tkhclass,txieca = stx.txieca,txiecanome = stx.txiecanome,txtqlook = stx.txtqlook,u_codint = stx.u_codint,u_esgoflnm = stx.u_esgoflnm,u_esgoflno = stx.u_esgoflno,u_esgotado = stx.u_esgotado,u_impetiq = stx.u_impetiq,u_lab = stx.u_lab,u_local = stx.u_local,u_local2 = stx.u_local2,u_nota1 = stx.u_nota1,u_tipoetiq = stx.u_tipoetiq,udata = stx.udata,uintr = stx.uintr,uni2 = stx.uni2,unidade = stx.unidade,unidadef = stx.unidadef,url = stx.url,usaid = stx.usaid,usalote = stx.usalote,usr1 = stx.usr1,usr2 = stx.usr2,usr3 = stx.usr3,usr4 = stx.usr4,usr5 = stx.usr5,usr6 = stx.usr6,usrdata = stx.usrdata,usrhora = stx.usrhora,usrinis = stx.usrinis,usrqtt = stx.usrqtt,vaiwww = stx.vaiwww,validade = stx.validade,valin = stx.valin,valout = stx.valout,vasilhame = stx.vasilhame,volume = stx.volume FROM ' + @DestServer + '.' + @DestDB + '.dbo.st sty WITH (NOLOCK) INNER JOIN ' + @SourceServer + '.' + @SourceDB + '.dbo.st stx WITH (NOLOCK) ON sty.ref=stx.ref AND sty.ststamp = stx.ststamp WHERE stx.usrdata != sty.usrdata OR stx.usrhora != sty.usrhora' as nvarchar(max))

    Would it help?

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 6, 2010 1:25 PM
    Moderator
  • Hello, Naom.

    Your suggestion did not work, either.  Thanks for the idea, anyhow... Anyway I could not implement it, my whole file for the Stored Procedure has 15 thousand lines and manually altering all this would be... very difficult :S

    This problem does not seem to have a specific cause. And know, as I said before, I have a query that WORKS with more than 4000 characters and another that does not..

    This is very strange, and is causing me a lot of headaches. I need to get this SP in production quickly...

     

    Any other ideas, anyone?

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Thursday, April 8, 2010 1:37 PM
  • Hello, everyone.

    Just one last thought: I declare my Server/Database variables as SYSNAME datatype, according to what I've read as best practices for SQL server internal naming variables.

    Is this correct? Anyone have any ideas on the impact of this?

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Thursday, April 8, 2010 4:34 PM
  • Ivo,

    BOL 2008: " The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. "

    Related dynamic SQL topic: QUOTENAME usage: http://www.sqlusa.com/bestpractices/dynamicsql/

    QUOTENAME ensures properly formatted, valid delimited identifier for table names, column names, and so on.


    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016







    Thursday, April 8, 2010 4:46 PM
    Moderator
  • NVARCHAR(MAX) stored up to 2 gB of data, which is half the number of characters, as each character is 2 bytes.  There is no exception to this it holds that much data.  If the variable is populated and showing the correct amount of data, that much data exists in the variable.  I think the problem the OP is facing is he is trying to PRINT the variable data, which is NOT possible.  Printing the whole string is not possible because print can only return 4000 chars for nvarchar and 8000 for varchar, everything else gets truncated, http://msdn.microsoft.com/en-us/library/ms176047.aspx (read remarks)

    You could develop a solution that use substring to print the data needed, but the best way is to convert it to xml.  Sure you get some invalid characters, but you can debug the code much easier.

    DECLARE @sql NVARCHAR(MAX)
    SET @sql = REPLICATE(N'A',3000) +  REPLICATE(N'B',3000)
    SELECT CAST(@sql AS XML)
    

    http://jahaines.blogspot.com/
    Thursday, April 8, 2010 5:21 PM
    Moderator
  • Adam,

    Reposting Brad's example, isn't that a bug?

    DECLARE @SQL NVARCHAR(MAX)
    
    SET @SQL = REPLICATE(N'A',3000) + REPLICATE(N'B',3000)
    
    SELECT LEN(@SQL)  --Returns 4000
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    No, this is implict conversion problem.  Essentially the replicate function is returning a nvarchar(4000) data type and when you concatenate 2 navarchar(4000) you get a single nvarchar(4000), this is why B is not shown at all in the text (convert to xml to verify).  You can run this to verify that nvarchar(max) has nothing to do with the problem.

    SELECT LEN(REPLICATE(N'A',4500)) /*4000*/

     


    http://jahaines.blogspot.com/
    Thursday, April 8, 2010 5:46 PM
    Moderator
  • Ivo,

    The following is DEMO ONLY T-SQL script, no business meaning. It is over 4000 bytes dynamic SQL and works!

    As Brad, Adam & Naomi pointed out there are some traps when assembling/testing  nvarchar(max) dynamic SQL script, so it requires extra verification to make sure that the assembly valid and correct.

    Let us know if helpful.

    -- DEMO ONLY! NO BUSINESS MEANING!
    -- T-SQL over 4000 bytes dynamic SQL demo
    -- String concatenation requires verfication
    -- Using CONVERT(NVARCHAR(max)... for each component 
    DECLARE @SQL nvarchar(max)=N'SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt'
    SET @SQL=@SQL+N'
    UNION ALL
    SELECT 
        pvt.[SalesPersonID]
        ,pvt.[FullName]
        ,pvt.[JobTitle]
        ,pvt.[SalesTerritory]
        ,pvt.[2002]
        ,pvt.[2003]
        ,pvt.[2004] 
    FROM (SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + '' '' + COALESCE(p.[MiddleName], '''') + '' '' + 
            p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal]
            ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
        FROM [AdventureWorks2008].[Sales].[SalesPerson] sp 
            INNER JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [AdventureWorks2008].[Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [AdventureWorks2008].[Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    	 ) AS soh 
    PIVOT 
    (
        SUM([SubTotal]) 
        FOR [FiscalYear] 
        IN ([2002], [2003], [2004])
    ) AS pvt'
    SELECT LEN(@SQL), DATALENGTH(@SQL)
    -- 6705	13410
    EXEC sp_executeSQL @SQL
    
    

    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016







    Thursday, April 8, 2010 6:17 PM
    Moderator
  • I noticed that there are no N prefix in second query may it lead to some varchar to nvarchar conversion issue?

    first query: SET @SQL = N'UPDATE ' + @DestServer ...

    second: SET @SQL = 'UPDATE ' + @DestServer +

     

    Friday, April 9, 2010 12:21 PM
  • Hello, Naom.

     

    Declaring my stored procedure input variables as NVARCHAR(MAX) seems to help, indeed. This makes perfect sense, since we cannot gather different data types (SYSNAME, the one I used before) and expect SQL Server to just glue them together into NVARCHAR(MAX).

    So, my bad here, newb error. :)

     

    Anyway, now I get a different kind of error. The query now started reporting a syntax error. For some time I thought that SQL still was not forming the query to the end (ommiting more than 4000 chars) but then I tried selecting the last 400 characters of the query, and its lenght, and something surprised me:

    SELECT LEN(@SQL) > 4239 (so the SQL is really in NVARCHAR(MAX), now)

     

    But when I do SELECT RIGHT(@SQL,400) I get the last part of my malformed query:

    pv3],[pv4] = stx.[pv4],[pv5] = stx.[pv5],[pvcon] = stx.[pvcon],[pvultimo] = stx.[pv[DestServer].[DestDB] .dbo.st sty WITH (NOLOCK)

    INNER JOIN [SourceServer].[SourceDB].dbo.st stx WITH (NOLOCK) ON sty.ref=stx.ref AND sty.ststamp = stx.ststamp       WHERE stx.usrdata != sty.usrdata OR stx.usrhora != sty.usrhora

    See the error in bold? SQL is apparently "eating up" parts of my query...

     

    Any ideas....?

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal

     

     


    ComputerDoc
    Monday, April 12, 2010 11:51 AM
  • Hello, Dmitry.

     

    Yes, that indeed was different, I already made both variable settings into Unicode using N'' as you specify.

    The problem I was having not being able to build a query with more than 4000 chars was due to the different datatypes I was trying to "collate" together into NVARCHAR(MAX), incorrectly.

     

    Please see my post reply to Naom above for this. In the meantime I have another problem.. now the query is malformed :S (another post with this above, too...)

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Monday, April 12, 2010 11:53 AM
  • Hello, SQLUSA.

    I could not use QUOTENAME inside my dynamic SQL because it returns NVARCHAR(258), as defined in BOL, and collating these quoted strings would make NVARCHAR(MAX) invalid in my entire SQL string. It is, however, a good idea to do this in smaller queries, perhaps. Thanks for the idea :)

    I have split my query in two parts, and apparently it has done away with the query's malforming (skipping characters in the middle of the query does not happen anymore). Now SQL complains about invalid tables, etc, wich I suspect has nothing to do with the malforming problem.

    This is the query that is "working":

    SET @SQL = 'UPDATE ' + @DestServer + '.' + @DestDb + '.dbo.st SET (....) = stx.pvcon,'
    SET @SQL = @SQL + 'pvultimo = stx.pvultimo,qlook = stx.qlook,qttacin =(...) WITH (NOLOCK) ON sty.ref=stx.ref AND sty.ststamp = stx.ststamp WHERE stx.usrdata != sty.usrdata OR stx.usrhora != sty.usrhora'

    Please note that I removed most of the text inside the (...) because there are so many lines in it... I did not change, however, any table code inside it.

    I will verify the query and post the results later on.

     

    Cheers,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Wednesday, April 21, 2010 2:17 PM
  • Hey IVO, i don't know if you are yet interested in some alternatives, but i'd like to share a link with an explanation of what is happening.

    Summarizing, this would work just fine (as someone also wrote above):

     

    DECLARE@sql nvarchar(MAX)
    SET@sql = N''
    SET @sql = @sql + N'String with more that 4000 characters'
    
    EXEC sp_executesql @sql
    

     

    The explanation is here:

    http://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql

    Hope this clarifies the issue a little.

    Regards


    • Edited by DarkBlues Wednesday, September 8, 2010 8:33 AM formatting
    • Proposed as answer by AlexFromSweden Friday, May 6, 2011 1:38 PM
    Wednesday, September 8, 2010 8:28 AM
  • I know this post is rather old, but came across it in my troubleshooting.  My problem ended up being a error in my code.  I had every thing converted to NVARCHAR(MAX) and was still getting what appeared to be a truncating error.  Try breaking up the code into seperate variables so that you can get a print statement out of it and see what you are actually parsing.   This helped me pinpoint the exact error. 

    Here's to hoping this helps someone else on their SQL journey.

     

    JG

    Monday, October 10, 2011 9:44 PM
  • Hi,

    I faced a similar issue and fixed it in the below way:

    DECLARE @sqlText1 NVARCHAR(4000), @sqlText2 NVARCHAR(4000)

    DECLARE @sqlText3 NVARCHAR(MAX)

    SET @sqlText1 = N'...Divide the query for 4000 characters....'

    SET @sqlText2 = N'...Divide the query for 4000 characters....'

    ....

    EXEC (@sqlText1 + @sqlText2)

     

    ----sp_executesql execution

    SET @sqlText3 = CAST @sqlText1 AS nvarchar(MAX)) + CAST (@sqlText2 AS nvarchar(MAX)) + ....

    EXEC sp_executesql @sqlText3 

     

    Let me know if this helped you.....

     

    Thanks,

    Sharmin

    Tuesday, December 6, 2011 11:16 PM
  • Hi,

    Create a view with your query and after that take query from your view with your where clause.

    Tuesday, December 11, 2012 4:46 AM