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?

so how big does this query string get? if it's over 8000 chars, then yeah, you'll end up with a truncated query. — Marc B
– Marc B, Commented Jun 9, 2015 at 15:47
so... "I have 10 gallons of water and a 1 gallon cup. why is the cup overflowing when I pour in all the water?" — Marc B
– Marc B, Commented Jun 9, 2015 at 16:03

JamieD77 · Accepted Answer · 2015-06-09 16:13:07Z

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

answered Jun 9, 2015 at 16:13

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zilla Over a year ago

Good point. However, i would like to execute all the update statements as one transaction. All or nothing processing. Any recommendation as to how to do that?

JamieD77 Over a year ago

are you trying do this in a stored procedure? you can create a cursor to go thru the temp table and execute each row or just skip the temp table and create a cursor for the query to select the table names and columns

Collectives™ on Stack Overflow

SQL SERVER 2000 TRUNCATES SQL STRING GENERATED USING DYNAMIC SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related