SQL SERVER 2000 TRUNCATES SQL STRING GENERATED USING DYNAMIC SQL

Question

i'm trying to generate update statement for over 90 tables using the code below:

   Declare @cmd   VARCHAR(8000)

       Select @cmd =  COALESCE(@cmd,'') +
      '
         UPDATE ' + TABLE_NAME + ' SET ' + Column_Name + '  = ''000000000''' + '''
         WHERE ' + Column_Name + '  = ''000000001''' + '''
      '


        From INFORMATION_SCHEMA.COLUMNS
                  Where TABLE_NAME not in (SELECT TABLE_NAME
                        From INFORMATION_SCHEMA.VIEWS)
                   and 
                        (Column_Name like 'SSN%'       
                        OR Column_Name LIKE 'ssn%'          
                        OR Column_Name LIKE 'ssn%'          
                        OR Column_Name LIKE '%_ssn%'            
                        OR Column_Name LIKE '_ocsecno'          
                        OR Column_Name LIKE 'Ssn%');

          Select @cmd

The code works but SQL server 2000 is able to generate update statements for only 45 tables out of 91 tables. it truncates the SQL string at the 45th table. Does any knows how to solve this issue?

Answer 1

If you're just trying to generate the update statements you can insert the updates into a temp table.

SELECT
    '
        UPDATE ' + TABLE_NAME + ' SET ' + Column_Name + '  = ''000000000''' + '''
        WHERE ' + Column_Name + '  = ''000000001''' + '''
    ' AS [query]
INTO
    #tempCmd
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME NOT IN (SELECT
                    TABLE_NAME
                    FROM
                    INFORMATION_SCHEMA.VIEWS)
    AND (Column_Name LIKE 'SSN%'
        OR Column_Name LIKE 'ssn%'
        OR Column_Name LIKE 'ssn%'
        OR Column_Name LIKE '%_ssn%'
        OR Column_Name LIKE '_ocsecno'
        OR Column_Name LIKE 'Ssn%');

SELECT [query] FROM #tempCmd