473,230 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

ALTER TABLE from sqlcmd script

Hi,

I'm trying to add a column to a table, then update that column with a
query. This is all within a single batch. Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added. We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")

However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.

If need be I can just do a SELECT INTO and add the column there, but
it's a pain in the butt and I'm moving a LOT of data just to do what I
want. And no, I can't go back to where the table is created and add
the column. Does anyone have any suggestions? TIA!

- Jeff

Aug 10 '06 #1
2 12808
Hi,

put a GO after the ALTER TABLEa nd you should be done.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Aug 10 '06 #2
Jeff_in_MD (jf*****@dsoftware.biz) writes:
I'm trying to add a column to a table, then update that column with a
query. This is all within a single batch. Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added. We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")

However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.
The full story is that SQL Server never accepts a missing column. Still
you sometimes you get away with it. Why? Because of deferred name
resolution (one of the biggest misfeatures added in SQL 7). Deferred
name resolution means that if SQL Server finds a query in a batch, where
one or more tables are missing, it defers compilation until later, and
you will not get an error, unless execution reaches that query and the
table is still missing. Quite an aggravated cost for plain spelling
errors!

But if all tables in a query exists, SQL Server also requires that all
columns exist. Thankfully, there is no deferred name resolution on
columns!

The actual effect of these rules is a bit different in SQL 2000 and
SQL 2005, since in SQL 2000, the entire batch is always recompiled,
while SQL 2005 has statment recompile.

Anyway, the proper procedure in a case like yours is to put all
statements that refer to the new column in EXEC, so that they are
compiled after the new column was added. There is not really any
need to put the ALTER statement in EXEC though.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 10 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Jon Combe | last post by:
I have created the following test SQL code to illustrate a real problem I have with some SQL code. CREATE TABLE JCTable ( CustomerName varchar(50) ) ALTER TABLE JCTable ADD CustomerNo int...
1
by: Matik | last post by:
Hello, I need to change collation in my database (more databases acctualy). Therefore, I wanted to make a script, which will do it at one more time. I already have a cursor, updating...
8
by: David Housman | last post by:
Hi, I'm trying to write a function check if a column exists on a table, and creates it if it doesn't. The line that the query analyzer is citing is noted. It seems unhappy taking variables in...
2
by: Kevin Haugen | last post by:
I need to create a script to disable all triggers and constraints in my database. It appears as though I cannot use a local variable for the table name in the ALTER TABLE statement (e.g. ALTER...
1
by: Euler Almeida via SQLMonster.com | last post by:
Hi people, I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K. Example: table : item_nota_fiscal_forn_setor_publico field : qtd_mercadoria integer NOT NULL...
3
by: Jeff Kish | last post by:
Hi. I'm getting errors like this when I try to run an upgrade script I'm trying to write/test: altering labels to length 60 Server: Msg 5074, Level 16, State 4, Line 5 The object...
5
by: Giacomo | last post by:
Hi, I’ve the following error message: --------------- ALTER TABLE . ALTER COLUMN varchar(10) Go Server: messaggio 4929, livello 16, stato 1, riga 1
2
by: rcamarda | last post by:
Hello, I need to alter fields in all my tables of a given database, and I would to do this via a t-sql script. Example, I want to change all fields called SESSION_ID to char(6). The field is...
7
by: quincy451 | last post by:
drop table . CREATE TABLE . ( NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , NULL , NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , (16) COLLATE
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.