EXEC Statements in a sql script

Thanks for the response. Didn't help however can tell you what is
happening now. I do an alter to create a new column. Put it in a
transaction and commit it. Then the next transaction I do an update to
the newly created column and it complains it can't find the column. If
I run all this manually it's fine. Could it be an issue with the speed
that the script is running that SQL Server, even though I committed
between alter and update, still is not done creating the tables or
something?
Thanks.
JRJR (jriker1@.yahoo.com) writes:
> Thanks for the response. Didn't help however can tell you what is
> happening now. I do an alter to create a new column. Put it in a
> transaction and commit it. Then the next transaction I do an update to
> the newly created column and it complains it can't find the column. If
> I run all this manually it's fine. Could it be an issue with the speed
> that the script is running that SQL Server, even though I committed
> between alter and update, still is not done creating the tables or
> something?
No, speed has nothing to do with it.
If you do:
ALTER TABLE tbl ADD newcol int
UPDATE tbl
SET newcol = 91
this will fail, because when SQL Server compiles this batch, it sees
that you references a column that does not exist in tbl, and that is
an error. SQL Server has deferred name resolution, so that if a table
does not exist when the batch is compiled, SQL Server is silent in hope
that the table is created. There is, thankfully, not deferred name
resolution for column names. It is bad as it is.
There are a couple of ways to skin the cat. The best is probably
to wrap the UPDATE into EXEC(), so that it will not be compiled
until after the ALTER TABLE statement has been executed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 27 Mar 2006 22:42:08 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>There are a couple of ways to skin the cat. The best is probably
>to wrap the UPDATE into EXEC(), so that it will not be compiled
>until after the ALTER TABLE statement has been executed.
Hi Erland,
In a stored procedure: yes.
But in a SQL script, just adding a "go" between the ALTER TABLE and the
UPDATE is enough.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> (snip)
> Hi Erland,
> In a stored procedure: yes.
> But in a SQL script, just adding a "go" between the ALTER TABLE and the
> UPDATE is enough.
I didn't mention that possibility because the hour was late, and there are
some caveates with it. Say that you do:
BEGIN TRANSACTION
-- Do something
go
-- Do something more
go
-- Yet something more
COMMIT TRANSACTION
Now, if there is an error on the line of the kind that aborts the batch,
the transaction will be rolled back, but the remaining batches will be
executed. You will get an error when you reach COMMIT, but then the damage
may already been done.
Of course, in this particular case if ALTER TABLE fails, the UPDATE command
will also fail. However, there can be other commands in other batches that
still can be carried out when they shouldn't.
One way to handles this is to open every batch with IF @.@.trancount > 0,
but I think would be prefer to keep all in one batch, and interleave
problematic statments in dynamic SQL. Not the least on SQL 2005, as I
then can have single CATCH handler at the end. (But note that if you
have:
BEGIN TRY
UPDATE tbl SET missingcolumn = <somevalue>
END TRY
BEGIN CATCH
-- handle error
END CATCH
that the CATCH handler will not be reached, as the error is a compilation
error.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 28 Mar 2006 10:27:33 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
>I didn't mention that possibility because the hour was late, and there are
>some caveates with it. Say that you do:
(snip)
Hi Erland,
Good point. Thanks for adding this warning!
Hugo Kornelis, SQL Server MVPsql