May 1, 2008 at 12:03 am
Hi,
what's the best alternative to using a 'GO' command right after the 'Alter table' command? It seems ALTER TABLE will not commit until I issue a go, but I need to have a BEGIN TRAN before that, so having this problem. Thanks
BEGIN TRAN
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'TableX')
BEGIN
ALTER TABLE TEMPDB.dbo.TableX
ADD MyFlag VARCHAR
END
-- should have a GO command here...
DECLARE @Error INT
SET @Error = @@ERROR
IF @Error <> 0 -- alter
PRINT 'ERROR: Failed to alter TableX'
ELSE
BEGIN
UPDATE TEMPDB..TableX
SET MyFlag='A'
FROM TEMPDB..TableXl a INNER JOIN
TEMPDB.dbo.Address b
ON a.AddressID = b.AddressID
SET @Error = @@ERROR -- get fresh value
END
--The update above will fail,because MyFlag column is still missing
IF @Error <> 0
BEGIN
ROLLBACK TRAN
PRINT 'ERROR'
END
ELSE
BEGIN
COMMIT TRAN
PRINT 'SUCCESSFUL'
END
GO
Thank you.
May 1, 2008 at 12:10 am
Transactions CAN wrap multiple SQL batches and GO commands.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2008 at 6:32 am
I had to clean up your code just a bit, but it works just fine:
--created the tables so I could play
IF NOT EXISTS ( SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'TableX' )
CREATE TABLE TableX ( mycol INT NULL )
IF NOT EXISTS ( SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'TableXl' )
CREATE TABLE TableXl
(
MyFlag VARCHAR(55) NULL
,AddressId INT null
)
IF NOT EXISTS ( SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'Address' )
CREATE TABLE Address ( AddressId int NULL )
BEGIN TRAN
IF EXISTS ( SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'TableX' )
BEGIN
ALTER TABLE TEMPDB.dbo.TableX
ADD MyFlag VARCHAR(55) NULL ;
--just want to see if it runs this command
PRINT 'made it'
END
DECLARE @Error INT
SET @Error = @@ERROR
IF @Error <> 0 -- alter
PRINT 'ERROR: Failed to alter TableX'
ELSE
BEGIN
UPDATE TEMPDB.dbo.TableX
SET MyFlag = 'A'
FROM TEMPDB.dbo.TableXl a
INNER JOIN TEMPDB.dbo.Address b
ON a.AddressID = b.AddressID
SET @Error = @@ERROR -- get fresh value
END
IF @Error <> 0
BEGIN
ROLLBACK TRAN
PRINT 'ERROR'
END
ELSE
BEGIN
COMMIT TRAN
PRINT 'SUCCESSFUL'
END
--get rid of the evidence so I can try it all again
DROP TABLE Address
DROP TABLE TableX
DROP TABLE TableXl
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 8:23 am
You need to end the alter table command with a semicolon ";", then you don't need a "go".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 8:31 am
You're telling me I fixed it by accident? I just put the semi-colon on because I've gotten in the habit of closing commands with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 10:34 am
Grant Fritchey (5/1/2008)
You're telling me I fixed it by accident? I just put the semi-colon on because I've gotten in the habit of closing commands with it.
Yeah, SQL Server isn't quite sure what to with:
ADD MyFlag VARCHAR END
After all, the column "END" doesn't have a data type or scale, and needs to be preceded by a comma. 🙂
(As an aside, don't use Varchar without a scale. Without one, it's Varchar(1), and you're better off with Char(1). Char(1) takes 1 byte, Varchar(1) takes 3. Of course, if you're using zero-length strings as a critical part of your code, ignore this.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 11:38 am
It was that lack of scale that forced me to go in and make edits to the code. It wouldn't compile as it was.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 2:34 pm
Thanks guys.
Grant, when I ran your code in the SQL Analyzer, you forgot to indicate where are you creating the table (not in tempdb...?) so made some modifications. it now gives me this error similar to what I'm getting previously (the reason i posted this question). cheers
Server: Msg 207, Level 16, State 1, Line 41
Invalid column name 'MyFlag'.
You see, the update won't work without having the table created first in that batch...
May 2, 2008 at 6:19 am
Oh, wait. I am an idiot. Here's the deal. The optimizer is smart enough to recognize that you're creating a table within the set when you execute the whole thing, but it's not so smart to parse out the ALTER statement when that runs by itself. So the query runs into the algebrizer which doesn't recognize that column. If you break it down like this, it will work (although I hate ad hoc queries).
--created the tables so I could play
IF NOT EXISTS ( SELECT *
FROM TEMPDB.dbo.SYSOBJECTS
WHERE NAME = 'TableX' )
CREATE TABLE dbo.TableX ( mycol INT NULL ) ;
IF NOT EXISTS ( SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = 'TableXl' )
CREATE TABLE dbo.TableXl
(
MyFlag VARCHAR(55) NULL
,AddressId INT null
) ;
IF NOT EXISTS ( SELECT *
FROM TEMPDB.dbo.SYSOBJECTS
WHERE NAME = 'Address' )
CREATE TABLE dbo.Address ( AddressId int NULL ) ;
GO
IF EXISTS ( SELECT *
FROM TEMPDB.dbo.SYSOBJECTS
WHERE NAME = 'TableX' )
BEGIN
BEGIN TRAN
ALTER TABLE TEMPDB.dbo.TableX
ADD MyFlag VARCHAR(55) NULL ;
--just want to see if it runs this command
PRINT 'made it'
COMMIT TRAN
END
DECLARE @myquery NVARCHAR(MAX)
SET @myquery = 'BEGIN TRAN
DECLARE @Error INT
SET @Error = @@ERROR
IF @Error <> 0 -- alter
PRINT ''ERROR: Failed to alter TableX''
ELSE
BEGIN
UPDATE TEMPDB.dbo.TableX
SET MyFlag = ''A''
FROM TEMPDB.dbo.TableXl a
INNER JOIN TEMPDB.dbo.Address b
ON a.AddressID = b.AddressID
SET @Error = @@ERROR -- get fresh value
END
IF @Error <> 0
BEGIN
ROLLBACK TRAN
PRINT ''ERROR''
END
ELSE
BEGIN
COMMIT TRAN
PRINT ''SUCCESSFUL''
END';
EXEC sp_executesql @myquery;
--get rid of the evidence so I can try it all again
DROP TABLE Address
DROP TABLE TableX
DROP TABLE TableXl
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply