none
Building code dynamically - the length is always 13187 RRS feed

  • Question

  • Hi everybody,

    I'm trying to generate a script dynamically. I declared my variable as nvarchar(max) and I'm adding to it. I found that when I try to copy the script by using select @sql it doesn't contain the whole code and the length is always 13167 in SSMS. I'm using SQL Server 2014 SP1 (although database is in the compatibility level 2008). I'm showing the whole code I've written so far - do you see where is my mistake and how can I ensure that the @SQL is generated fully and can be copied?

    -- Description:	Dedupes Guests
    -- Example Call: execute dbo.siriussp_DedupeGuest @nOriginalGuest = 90000001, @nNewGuest = 91000001, @salespoint = 'RENTAL', @operator = 'ADMIN';   
    */
    --=========================================================   
    
    ALTER PROCEDURE dbo.siriussp_DedupeGuest
    (@nOriginalGuest decimal(17,0),
    @nNewGuest decimal(17,0),
    @salespoint CHAR(6),
    @operator CHAR(6),
    @debug bit = 1)
    as
    begin
    
    set nocount on;
    
    declare @TablesToProcess table (table_Name sysname, id int);
    declare @loop int, @tablesCount int, @sql nvarchar(max), @table_name sysname, @updateCmd nvarchar(max);
    
    ;WITH siriusTables
    AS (
       SELECT cast(objname AS sysname) AS table_name
       FROM::fn_listextendedproperty('SIRIUS_DefaultTable', 'user', 'dbo', 'table', NULL, NULL, NULL)
       )
    insert into @TablesToProcess (table_Name, id)
    SELECT DISTINCT c.TABLE_NAME, row_number() over (order by c.table_name) as id
    
    FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN siriusTables s
    ON c.TABLE_NAME = s.table_name COLLATE DATABASE_DEFAULT 
       AND COLUMN_NAME IN (
          'guest_no'
          ,'parent_no'
          ) AND c.TABLE_NAME not IN ('GUESTS','ACCESS','ADDRESS','ADDLINK','DAILY_SALE_HDR','DAILY_TRANSACT')
    ORDER BY c.table_name;
    
    set @tablesCount = @@ROWCOUNT;
    
    set @loop = 1;
    
    set @sql = N'';
    
    while @loop <=@tablesCount
    begin
         
      select @table_name = table_name from @TablesToProcess where id = @loop;
      
    	 set @sql = @sql + 'RAISERROR (''Processing table ' + quotename(@table_name) + ' iteration #' + cast(@loop as varchar(10)) + ' out of ' + 
    	 cast(@tablesCount as varchar(10)) + '..''  , 0, 1) with nowait;
    	 '
    
        IF not exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
    
    AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
    
    AND TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    
    WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'  AND TC.TABLE_NAME = @table_Name
    AND CU.COLUMN_NAME = 'guest_no') -- exclude cases where guest_no is a PK for the table as this is more complex case
    
         begin
    
    	 if exists (select 1 from INFORMATION_SCHEMA.COLUMNS c WHERE c.Table_Name = @table_name and c.COLUMN_NAME = 'guest_no')	 
    	  SET @sql = @sql + N' UPDATE dbo.' + quotename(@table_name) + ' set guest_no = @newGuestNo ' + 
    	  case upper(@table_name) WHEN 'GST_PASS' then ', mod_sp = @salespoint, mod_op = @operator' 
    	                          WHEN 'GST_ACTV' then ', gfwdstatus = 1'
     	  else '' end + 
    	  
    	  '
    	  where guest_no = @originalGuestNo;
    	  '
    	 if exists (select 1 from INFORMATION_SCHEMA.COLUMNS c WHERE c.Table_Name = @table_name and c.COLUMN_NAME = 'parent_no')	 
    	     SET @sql = @sql + ' UPDATE dbo.' + quotename(@table_name) + ' set parent_no = @newGuestNo where parent_no = @originalGuestNo;
    		  '
    
          if upper(@table_name) IN ('TR_INFO', 'TRS_INFO')
    
          set @sql = @sql + ' UPDATE dbo.' + quotename(@table_name) +  ' set info_num = CAST(@newGuestNo as CHAR(17)) 
    	  where info_num = CAST(@originalGuestNo as CHAR(17)) AND info_type = 10
    	  ;'
    
    
    	 end
    	 else -- primary key, more complex case
    	 begin
    	   IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS c 
    	   WHERE c.TABLE_NAME = @table_name AND c.COLUMN_NAME = 'date_time')
    
    	   begin
    	     select @updateCmd = 'WITH cte AS (select * FROM dbo.' + QUOTENAME(@table_name) + ' 
    		 WHERE guest_no = @originalGuestNo)
    	   UPDATE t  	   
    	   SET ' + stuff((select ', ' + quotename(c.COLUMN_NAME) + ' = cte.' + quotename(c.COLUMN_NAME)
    	   FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = @table_name 
    	   AND c.COLUMN_NAME NOT IN ('guest_no', 'last_mod') FOR XML PATH('')), 1, 2, '')  + '
    	   FROM dbo.' + QUOTENAME(@table_name) + ' t, cte
    	   WHERE t.guest_no = @newGuestNo;
    	   '	  
    
    	   set @sql = @sql + '
    	   IF EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo)
    	   
    	       BEGIN
    		      IF NOT EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @newGuestNo) -- simple case, only duplicate guest exists
    			     UPDATE dbo.' + quotename(@table_name) + ' SET guest_no = @newGuestNo 
    				 WHERE guest_no = @originalGuestNo;
                  ELSE -- both guests exist in the table 
    			    IF (SELECT ISNULL(date_time, ''19000101'') FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @newGuestNo) >=
    				   (SELECT ISNULL(date_time, ''19000101'') FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo)
    				      -- New guest is newer - just delete the duplicate
    					  DELETE FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo;
                    ELSE -- duplicate is newer
    				     ' + @updateCmd + 
    					 '
    					  DELETE FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo;
    		   END
    		   '
    		   end      
    		   ELSE -- no date_time column
    		      SET @sql = @sql + 'IF EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + '
    			   WHERE guest_no = @originalGuestNo)
    	   
    	       BEGIN
    		      IF NOT EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @newGuestNo) -- simple case, only duplicate guest exists
    			     UPDATE dbo.' + quotename(@table_name) + ' SET guest_no = @newGuestNo 
    				 WHERE guest_no = @originalGuestNo;
                  ELSE -- both guests exist in the table 
    			    -- just delete the duplicate
    					  DELETE FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo;
                END
    			'   
    	 end
    	
    	 
    	 
    
    	 set @loop = @loop + 1;
    end
    
    
    IF EXISTS (select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'addlink') -- this table is going to be deprecated
    begin
    set @sql = @sql + 'RAISERROR (''Removing rows for original guest number from the addlink table..''  , 0, 1) with nowait;
    	 
    DELETE FROM dbo.addlink where guest_no = @originalGuestNo; -- just remove old records
    '
    end
    
    set @sql = @sql + 'RAISERROR (''Merging guest data in ww_sales table..''  , 0, 1) with nowait;
    	 
     UPDATE dbo.ww_sales SET sale_text = replace(cast(sale_text as varchar(max)), ''<guest_no>'' + CAST(@originalGuestNo as varchar(max)) + ''</guest_no>'', ''<guest_no>'' + CAST(@newGuestNo as varchar(max)) + ''</guest_no>'')
                WHERE (fwd_status = 0 OR rec_type IN (2,3)) AND cast(sale_text as varchar(max)) like ''%<guest_no>'' + CAST(@originalGuestNo as varchar(max)) + ''</guest_no>%'';
           
    
    -- Process Contacts tables
    
    declare @ContactId numeric(17,0), @ContactId2 numeric(17,0), @oldContactId numeric(17,0), @oldContactId2 numeric(17,0),
    @PhotoId numeric(17,0), @oldPhotoId numeric(17,0);
    
    select @ContactId = contactId, @ContactId2 = contactId2 from dbo.guests where guest_no = @newGuestNo;
    select @oldContactId = contactId, @oldContactId2 = contactId2 from dbo.guests where guest_no = @OriginalGuestNo;
    
    if @oldContactId IS NOT NULL AND @oldContactId not IN (@ContactId, COALESCE(@ContactId2, @ContactId))
      begin
        -- Test other tables that may use that contact
    	if not exists (select 1 from dbo.invoices i where i.ContactId = @oldContactId)
    	   and not exists(select 1 from dbo.operator o where o.ContactId = @oldContactId)
    	   and not exists (select 1 from dbo.accounts a where a.ContactId = @oldContactId)
    	   -- This contact is not used anywhere and can be deleted once the appropriate rows from other tables are either deleted or re-pointed
    	   begin
    	   
    	   ;with cte as (select al.*, ad.ATypeId from dbo.ContactsAddressesLink al
    	   inner join dbo.Addresses ad on al.AddressId = ad.AddressId
    	    where al.ContactId = @oldContactId)
    
    	   merge cte trg 
    	   using (select al.*, ad.ATypeId from dbo.ContactsAddressesLink al 
    	   inner join dbo.Addresses ad on al.AddressId = ad.AddressId
    	   where al.ContactId = @ContactId) src on trg.ATypeId = src.ATypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId;
    
    	   ;with cte as (select al.*, em.EMTypeId from dbo.ContactsEmailAddressesLink al
    	   inner join dbo.EmailAddresses em on al.EmId = em.EmId
    	    where al.ContactId = @oldContactId)
    
    	   merge cte trg 
    	   using (select al.*, em.EMTypeId from dbo.ContactsEmailAddressesLink al
    	   inner join dbo.EmailAddresses em on al.EmId = em.EmId
    	    where al.ContactId = @ContactId) src on trg.EMTypeId = src.EMTypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId;
    
    	    ;with cte as (select al.*, pn.PNTypeId from dbo.ContactsPhoneNumbersLink al
    	   inner join dbo.PhoneNumbers pn on al.PhId = pn.PhId
    	    where al.ContactId = @oldContactId)
    
    	   merge cte trg 
    	   using (select al.*, pn.PNTypeId from dbo.ContactsPhoneNumbersLink al
    	   inner join dbo.PhoneNumbers pn on al.PhId = pn.PhId
    	    where al.ContactId = @ContactId) src on trg.PNTypeId = src.PNTypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId;
    
    	    -- Remove photo for old contact if there is a new photo
    	   if (select PhotoId from dbo.Contacts where ContactId = @ContactId) IS NOT NULL
    	      delete P from dbo.ContactPhotos P where exists (select 1 from Contacts C 
    	   where C.PhotoId = P.PhotoId and C.ContactId = @oldContactId);
    	   ELSE
    	      UPDATE dbo.Contacts SET PhotoId = (select PhotoId from dbo.Contacts where ContactId = @oldContactId)
    		  WHERE ContactId = @ContactId;
    
    
    	   -- Now remove old contact row
    
    	   delete from dbo.Contacts where ContactId = @oldContactId;
    
    
    	   end
    
      end 
    
    -- Now repeat the process for second contact
    
    if @oldContactId2 IS NOT NULL AND @oldContactId2 not IN (@ContactId, COALESCE(@ContactId2, @ContactId))
    
      set @ContactId2 = COALESCE(@ContactId2, @ContactId);
    
      begin
        -- Test other tables that may use that contact
    	if not exists (select 1 from dbo.invoices i where i.ContactId = @oldContactId2)
    	   and not exists(select 1 from dbo.operator o where o.ContactId = @oldContactId2)
    	   and not exists (select 1 from dbo.accounts a where a.ContactId = @oldContactId2)
    	   -- This contact is not used anywhere and can be deleted once the appropriate rows from other tables are either deleted or re-pointed
    	   begin
    	   
    	   ;with cte as (select al.*, ad.ATypeId from dbo.ContactsAddressesLink al
    	   inner join dbo.Addresses ad on al.AddressId = ad.AddressId
    	    where al.ContactId = @oldContactId2)
    
    	   merge cte trg 
    	   using (select al.*, ad.ATypeId from dbo.ContactsAddressesLink al 
    	   inner join dbo.Addresses ad on al.AddressId = ad.AddressId
    	   where al.ContactId = @ContactId2) src on trg.ATypeId = src.ATypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId2;
    
    	   ;with cte as (select al.*, em.EMTypeId from dbo.ContactsEmailAddressesLink al
    	   inner join dbo.EmailAddresses em on al.EmId = em.EmId
    	    where al.ContactId = @oldContactId2)
    
    	   merge cte trg 
    	   using (select al.*, em.EMTypeId from dbo.ContactsEmailAddressesLink al
    	   inner join dbo.EmailAddresses em on al.EmId = em.EmId
    	    where al.ContactId = @ContactId2) src on trg.EMTypeId = src.EMTypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId2;
    
    	    ;with cte as (select al.*, pn.PNTypeId from dbo.ContactsPhoneNumbersLink al
    	   inner join dbo.PhoneNumbers pn on al.PhId = pn.PhId
    	    where al.ContactId = @oldContactId2)
    
    	   merge cte trg 
    	   using (select al.*, pn.PNTypeId from dbo.ContactsPhoneNumbersLink al
    	   inner join dbo.PhoneNumbers pn on al.PhId = pn.PhId
    	    where al.ContactId = @ContactId2) src on trg.PNTypeId = src.PNTypeId
    	   WHEN MATCHED THEN DELETE
    	   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ContactId = @ContactId2;
    
    	   -- Remove photo for old contact if there is a new photo
    	   if (select PhotoId from dbo.Contacts where ContactId = @ContactId2) IS NOT NULL
    	      delete P from dbo.ContactPhotos P where exists (select 1 from Contacts C 
    	   where C.PhotoId = P.PhotoId and C.ContactId = @oldContactId2);
    	   ELSE
    	      UPDATE dbo.Contacts SET PhotoId = (select PhotoId from dbo.Contacts where ContactId = @oldContactId2)
    		  WHERE ContactId = @ContactId2;
    
    
    	   -- Now remove old contact row
    
    	   delete from dbo.Contacts where ContactId = @oldContactId2;
    
    	   end
    
      end 
    
    '
    
    
    
    set @sql = 'BEGIN TRANSACTION 
    ' + @sql + '
    COMMIT TRANSACTION'
    
    if @debug = 1
       select @SQL as SQLStatement, LEN(@SQL) as SQLLength;
    else
      execute sp_executeSQL @SQL, N'@newGuestNo decimal(17,0), @originalGuestNo decimal(17,0), @salespoint CHAR(6), @operator CHAR(6)', 
      @newGuestNo = @nNewGuest, @originalGuestNo = @nOriginalGuest, @salespoint = @salespoint, @operator = @operator;
    
    end
    
    go
    
    execute dbo.siriussp_DedupeGuest @nOriginalGuest = 90000001, @nNewGuest = 91000001, @salespoint = 'RENTAL', @operator = 'ADMIN';

    UPDATE. I made some changes in the script and the length is now 13187 however when I try to copy the script from SSMS it is still truncated. Is it the max length of a single column output (I'm using grid output) or length of the clipboard (I'm using Ctrl+C/Ctrl+V)?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    Tuesday, January 5, 2016 8:37 PM
    Moderator

Answers

  • I did not check Naomi's script to closely, but I think I know what is going on.

    First run this:

    DECLARE @a nvarchar(4000) = 'N''' + replicate('A', 3000) + ''' + ',
            @b nvarchar(4000) = '''' + replicate('N', 3000) + ''''
    SELECT  @a, @b

    Then copy and paste the result after the equal sign into this snippet and run it:

    DECLARE @c nvarchar(MAX) = SELECT len(@c), @c

    The string is now 4000 characters long. Remove the N and the result is now 6000 characters long!

    Mystery and magic? No, there is only mystery and magic in computers if you fail to observe what is going on.

    T-SQL is a statically typed language, meaning that each literal and expression has a type. The type of a string literal like this: 'abcd' is varchar(4). If you concatenate a varchar(n) and a varchar(m), the type of the expression is varchar(n + m), as demonstrated here:

       declare @c varchar(3) = 'AB', @d varchar(23) = 'AA'
       declare @v sql_variant = @c + @d
       SELECT sql_variant_property(@v, 'basetype'),           sql_variant_property(@v, 'maxlength')

    This returns 'varchar' and 26. However, this rule has an addition: if the total length exceeds 8000 characters, the type is still varchar(8000), and if the actual values in total are longer truncation occurs. You don't get a varchar(MAX), because that is a different type. And this applies even if you are assigning to an (n)varchar(MAX), because the expression has a type of its own.

    However, if the concatnation includes a term of the MAX type, the other operand is also converted to MAX, since the operation must be carried out on the same types.

    In Namoi's case, apparently two strings that both were < 8000 characters met, but there was no MAX expression involved, so no automatic conversion occurred. Note that if a single string literal is > 8000 characters, the type of that literal is varchar(MAX). Therefore it is possible, that Naomi's script would have worked, if she had put N before all leading quotes, as now the limit would have been 4000 characters instead!

    Wednesday, January 6, 2016 11:52 AM

All replies

  • I added CAST(N'long script here ..' as nvarchar(max)) in 2 places and it resolved the problem so far.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 5, 2016 9:03 PM
    Moderator
  • UPDATE. I made some changes in the script and the length is now 13187 however when I try to copy the script from SSMS it is still truncated. Is it the max length of a single column output (I'm using grid output) or length of the clipboard (I'm using Ctrl+C/Ctrl+V)?

    There is a setting in SSMS (Tools - Options... - Query Results - SQL Server - Results to Grid - Maximum Characters Retrieved - Non XMl data:) which can be set to a maximum of 65535 (also the default), however when copying and pasting out of grid results I only get a maximum of 43679 characters even if the setting is higher.

    Here's a connect here with a typical Microsoft response: https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode (same thing happens as of SSMS 2016 CTP3)

    Maybe not an issue for you as your aren't close to that length but it's something to be aware of.

    Tuesday, January 5, 2016 10:02 PM
  • Thanks! For now I was able to fix the problem by adding CAST(my long script as nvarchar(max)) in a few places. I do remember there was a similar thread a while back that's why I tried that workaround first. I think my total script is probably going to be around 20K characters, so I should be OK.

    BTW, the MS Connect site is experiencing some problems again. If I try to open this link in incognito window from Google Chrome, I'm able to see it, but when I try to open normally, I get 'Page not found'.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 5, 2016 10:25 PM
    Moderator
  • I've fixed long NVARCHAR(MAX) concats by splitting huge chunks of text using '+', effectively causing them to be concatenated to fix issues like this as well.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Tuesday, January 5, 2016 10:31 PM
  • I did not check Naomi's script to closely, but I think I know what is going on.

    First run this:

    DECLARE @a nvarchar(4000) = 'N''' + replicate('A', 3000) + ''' + ',
            @b nvarchar(4000) = '''' + replicate('N', 3000) + ''''
    SELECT  @a, @b

    Then copy and paste the result after the equal sign into this snippet and run it:

    DECLARE @c nvarchar(MAX) = SELECT len(@c), @c

    The string is now 4000 characters long. Remove the N and the result is now 6000 characters long!

    Mystery and magic? No, there is only mystery and magic in computers if you fail to observe what is going on.

    T-SQL is a statically typed language, meaning that each literal and expression has a type. The type of a string literal like this: 'abcd' is varchar(4). If you concatenate a varchar(n) and a varchar(m), the type of the expression is varchar(n + m), as demonstrated here:

       declare @c varchar(3) = 'AB', @d varchar(23) = 'AA'
       declare @v sql_variant = @c + @d
       SELECT sql_variant_property(@v, 'basetype'),           sql_variant_property(@v, 'maxlength')

    This returns 'varchar' and 26. However, this rule has an addition: if the total length exceeds 8000 characters, the type is still varchar(8000), and if the actual values in total are longer truncation occurs. You don't get a varchar(MAX), because that is a different type. And this applies even if you are assigning to an (n)varchar(MAX), because the expression has a type of its own.

    However, if the concatnation includes a term of the MAX type, the other operand is also converted to MAX, since the operation must be carried out on the same types.

    In Namoi's case, apparently two strings that both were < 8000 characters met, but there was no MAX expression involved, so no automatic conversion occurred. Note that if a single string literal is > 8000 characters, the type of that literal is varchar(MAX). Therefore it is possible, that Naomi's script would have worked, if she had put N before all leading quotes, as now the limit would have been 4000 characters instead!

    Wednesday, January 6, 2016 11:52 AM