I'm using a script to add a column to a table. In one database it works, in another it doesn't work

AddicksAddict 0
Viewed 30

I've got two logins, let's call them 1 and 2, and two databases, again 1 and 2, both databases and both logins made using the same sets of instructions. Login1 uses database1 and login2 uses database2.

I'm running this script from an application to do an amendment to a table:

sqlcmd -U {loginID} -P {password} -S SVR09\SQL2019 -h-1 -e -b -i {path}stl_u92_001_o_accref_new_column.sql -o {path}stl_u92_001_o_accref_new_column.log

The loginid, password and paths are all correct because in all cases I get the log file

The SQL script contains this:

   -- add new column to the table?
   IF NOT EXISTS (SELECT 'X'
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_SCHEMA = 'dbo'
                        AND TABLE_NAME  = 'O_ACCREF'
                        AND COLUMN_NAME = 'ALIAS_ID'
                    )

      ALTER TABLE O_ACCREF
        ADD ALIAS_ID varchar(70)
            DEFAULT ' ' NOT NULL;

 UPDATE O_ACCREF
    SET ALIAS_ID = ACC_ALIAS
 WHERE ALIAS_ID = ' ';

This is the table before the script is run:

CREATE TABLE dbo.O_ACCREF
   (
     OUR_BIC VARCHAR(11) DEFAULT ' ' NOT NULL
   , THEIR_BIC VARCHAR(11) DEFAULT ' ' NOT NULL
   , ACC_ALIAS VARCHAR(70) DEFAULT ' ' NOT NULL
   , ORIGIN VARCHAR(1) DEFAULT ' ' NOT NULL
   , ACC_CUST VARCHAR(11) DEFAULT ' ' NOT NULL
   , ACCOUNT VARCHAR(35) DEFAULT ' ' NOT NULL
   )
   ON [PRIMARY]

and it has 27 rows in my sample data.

With login1 pointing to database1, the script works and I get the new column on the table and the data from ACC_ALIAS in ALIAS_ID. The log file says 27 rows updated.

With login2 pointing to database2, the script fails to add the new column. However, the ALTER TABLE command doesn't give an error, the only error is

Msg 207, Level 16, State 1, ...
Invalid column name 'ALIAS_ID'

for the UPDATE command. If I run the sqlcmd from the command line on the server hosting the installation of SQL Server, I get the same results.

So I tried using login1 pointing to database2 - fails, and login2 pointing to database1 - works.

When I use Management Studio to get the database and login creation scripts, they look identical, apart from the names of course.

Any pointers as to where I'm going wrong would be much appreciated.

not-exists alter-table sqlcmd sql-server
1 Answers

As I mention in the comments, the problem is that the entire batch fails if the column doesn't exist. This is because the batch is parsed before it is executed, and at that point the column ALIAS_ID doesn't exist in the table O_ACCREF, and so the compile fails the batch. Columns are not one of the objects that allow for deferred compilation (such as tables, where you can both CREATE and INSERT into the table in the same batch).

As you as using sqlcmd, you can simply separate the batches with a batch separator (GO by default):

   -- add new column to the table?
   IF NOT EXISTS (SELECT 'X'
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_SCHEMA = 'dbo'
                        AND TABLE_NAME  = 'O_ACCREF'
                        AND COLUMN_NAME = 'ALIAS_ID'
                    )

      ALTER TABLE O_ACCREF
        ADD ALIAS_ID varchar(70)
            DEFAULT ' ' NOT NULL;
GO
 UPDATE O_ACCREF
    SET ALIAS_ID = ACC_ALIAS
 WHERE ALIAS_ID = ' ';
Larnu0
Related