June 16, 2011 at 5:19 pm
Please advise on some error handling techniques that I can research and that would be appropriate here.
The below script working well for the task it is meant for: to rename a column currently called Big with IsBig, and to change the data type to bit. There is a condition that checks upfront for the possibility that there may or may not be a default constraint that needs to be dropped before the renaming. But it all works....
UNLESS....anything I haven't conceived of happens.
One of those things was finding out that one of the tables on my list actually had both a Big and and IsBig column, in which case this script didn't fail, just skirted around the table, kept processing other tables, and gave no error or exception.
Where and with which constructs should I begin?
---retrieves tables to change
USE DatabaseName
GO
DECLARE @TableName varchar(max)
DECLARE getTableName CURSOR FOR
select ss.name + '.' + o.name as table_name
from sys.objects o
join sys.schemas ss on ss.schema_id = o.schema_id
where type = 'u' and
ss.name + '.' + o.name in
('mgo.tablename1'
,'sbr.tablename2'
,'dkl.tablename3'
);
OPEN getTableName
FETCH NEXT
FROM getTableName INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare @default varchar(max)
declare @table_name varchar(max)
set @table_name = @TableName
set @default = null
---- identify if default constraint on Big column exists
SELECT @default = d.name
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = SUBSTRING(@Table_Name,5,50)
and c.name = 'Big'
if @default is not null
BEGIN
---- renames Big column to IsBig
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename '''+ @Table_Name + '.Big'', ''IsBig'', ''COLUMN'''
EXEC(@sql0)
---- drops constraint on Big column if one exists
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
EXEC (@sql1)
---- converts datatype from smallint to bit
DECLARE @sql2 nvarchar(4000)
SET @sql2 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsBig bit not null'
EXEC(@sql2)
---- readds default constraint, this time on renamed column
DECLARE @sql3 nvarchar(4000)
SET @sql3 = 'ALTER TABLE '+ @Table_Name + ' ADD DEFAULT ((1)) FOR IsBig'
exec (@sql3)
print @Table_Name + 'has been updated.'
END
ELSE
BEGIN
print @Table_name + ' has no constraint'
---- renames Big column to IsBig
DECLARE @sql11 nvarchar(4000)
SET @sql11 = 'sp_rename '''+ @Table_Name + '.Big'', ''IsBig'', ''COLUMN'''
EXEC(@sql11)
print @Table_name + ' remains without constraint'
---- converts datatype from smallint to bit
DECLARE @sql12 nvarchar(4000)
SET @sql12 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsBig bit not null'
EXEC(@sql12)
print @Table_Name + 'has been updated.'
END
FETCH NEXT
FROM getTableName INTO @TableName
END
CLOSE getTableName
DEALLOCATE getTableName
June 16, 2011 at 6:22 pm
Ever used TRY/CATCH ? It's awesome 😎
BEGIN TRY
EXEC('raiserror(''Bad stuff happened...'',11,1);') ;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [error_message]
END CATCH
<my_two_cents>If you insist on full automation go for it...it can be done with full error handling. What I find works better however (for my sanity anyway) is to have scripts like this output SQL to the SSMS text pane where I can inspect what would have been run had it been fully automated...then I copy that output to the next query window and run it by hand.</my_two_cents>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 6:24 pm
PS I didn't notice any transaction handling in your code. If you're operating on a table or set of tables more than once inside a "unit of work" you may want to consider adding some...especially since you've already experienced the previously unexpected once.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 10:32 pm
OK, I will be googling transaction erroring and begin/try and cracking the whip on myself tonight, to get a handle on this. Thank you for taking my question and helping me.
June 17, 2011 at 6:11 am
I have read BOL and can see how to place begin/try around regular select statements, but not around dynamic sql statements. Would you mind giving me a suggestion about placement, specifically around a stored procedure, within dynamic sql? I have distinct DECLARE, SET, EXEC blocks whose results I'd like to catch. I would specifically like to catch any errors arising around the sp_rename block portion of my script, because that is where I should get an error when a table column is being renamed to one that already exists.
DECLARE @sql11 nvarchar(4000)
SET @sql11 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql11)
June 17, 2011 at 6:32 am
hxkresl (6/17/2011)
I have read BOL and can see how to place begin/try around regular select statements, but not around dynamic sql statements. Would you mind giving me a suggestion about place of the begin and try clauses? I have distinct DECLARE, SET, EXEC blocks whose results I'd like to catch. I would specifically like to catch any errors arising around the sp_rename block.DECLARE @sql11 nvarchar(4000)
SET @sql11 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql11)
Sure, here is the same example from above but with a transaction mixed in:
BEGIN TRY
-- start a transaction
BEGIN TRAN ;
-- do some dynamic sql
EXEC('select ''Hi!'' as did_some_stuff;') ;
-- do some more dynamic sql that must be done together with the previous stateent
EXEC('raiserror(''Bad stuff happened...'',11,1);') ;
-- commit the transaction
COMMIT TRAN ;
END TRY
BEGIN CATCH
-- we're in here which means an error occurred!
-- if all transactions have not already been rolled back or are not otherwise dead roll it back
IF ( XACT_STATE() != 0 )
ROLLBACK TRAN ;
-- display the error message
SELECT ERROR_MESSAGE() AS [error_message] ;
END CATCH
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 10:21 am
opc.three.
I haven't found the proper place to insert the BEGIN TRY...BEGIN TRAN/ COMMITT TRAN...END TRY clauses. I have taken my first chunk of consequetive dynamic sql code betweeen them, as follows. Do you mind showing me error, or alternative?:
---- renames Active column to IsActive
BEGIN TRY
BEGIN TRAN;
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql0)
EXEC ('select 'error at rename column step for ' + '@tablename'');
---- drops constraint on Active column if one exists
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
EXEC (@sql1)
EXEC ('select 'error at default constraint drop step for ' + '@tablename'');
---- converts datatype from smallint to bit
DECLARE @sql2 nvarchar(4000)
SET @sql2 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql2)
EXEC ('SELECT 'error at alter column datatype step for ' + '@tablename'');
---- readds default constraint, this time on renamed column
DECLARE @sql3 nvarchar(4000)
SET @sql3 = 'ALTER TABLE '+ @Table_Name + ' ADD DEFAULT ((1)) FOR IsActive'
exec (@sql3)
print @Table_Name + 'has been updated.'
COMMIT TRAN;
END TRY
BEGIN CATCH
IF(XACT_STATE()!=0)
ROLLBACK TRAN;
SELECT ERROR_MESSAGE()AS[rename failed]
END
June 17, 2011 at 10:31 am
You're close. See if this makes more sense. I updated your script so it runs independently and just prints what would be run...just uncomment the EXEC command if you want it to do work. Try running it to see what it does.
---- renames Active column to IsActive
BEGIN TRY
BEGIN TRAN ;
DECLARE @Table_Schema_Name SYSNAME = N'dbo',
@Table_Name SYSNAME = N'some_table',
@default SYSNAME = N'some_default' ;
DECLARE @sql NVARCHAR(MAX) ;
SET @Table_Schema_Name = QUOTENAME(@Table_Schema_Name) ;
SET @Table_Name = QUOTENAME(@Table_Name) ;
SET @default = QUOTENAME(@default) ;
SET @sql = N'EXEC sys.sp_rename ''' + @Table_Schema_Name + '.' + @Table_Name + N'.Active'', ''IsActive'', ''COLUMN'';
' ;
---- drops constraint on Active column if one exists
SET @sql = @sql + N'ALTER TABLE ' + @Table_Schema_Name + '.' + @Table_Name + N' DROP ' + @default + N';
' ;
---- converts datatype from smallint to bit
SET @sql = @sql + N'ALTER TABLE ' + @Table_Schema_Name + '.' + @Table_Name + N' ALTER COLUMN IsActive bit not null;
' ;
---- readds default constraint, this time on renamed column
SET @sql = @sql + N'ALTER TABLE ' + @Table_Schema_Name + '.' + @Table_Name + N' ADD DEFAULT ((1)) FOR IsActive;
' ;
PRINT @sql
-- EXEC (@sql);
PRINT @Table_Name + 'has been updated.' ;
COMMIT TRAN ;
END TRY
BEGIN CATCH
IF (XACT_STATE() != 0)
ROLLBACK TRAN ;
SELECT ERROR_MESSAGE() AS [rename failed]
END CATCH
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2011 at 8:21 am
opc.three.
Yes, I see what that does, so I inserted a print @sqlXX in before every exec @sqlXX. The print command prints what gets executed for each iteration of the cursor's fetch. I appreciate that output for every input. Still.....
Nothing gets printed to the screen for the table that is failing to get updated.
Recap:
I give it 8 tables to process, it processes 7, no errors, not even print output. If I run the 8th one by itself, in a different query window, using only the following syntax I do get an error:
sp_rename 'mgo.TableName.Active', 'IsActive', 'COLUMN'
error:
Msg 15335, Level 11, State 1, Procedure sp_rename, Line 408
Error: The new name 'IsActive' is already in use as a COLUMN name and would cause a duplicate that is not permitted.
Sorry to be so dense. I think I still don't have the placement of the BEGIN TRY/CATCH COMMIT TRAN/ROLLBACK TRAN clauses right. Can you tell me exactly where in the script (first one in this thread) to place them?
I tried this:
---retrieves tables to change
USE MGONextGen
GO
DECLARE @TableName varchar(max)
DECLARE getTableName CURSOR FOR
select ss.name + '.' + o.name as table_name
from sys.objects o
join sys.schemas ss on ss.schema_id = o.schema_id
where type = 'u' and
ss.name + '.' + o.name in
('mgo.table_name_1'
,'mgo.table_name_2'
,'mgo.table_name_3'
,'mgo.table_name_4'
,'mgo.table_name_5'
,'mgo.table_name_6'
,'mgo.table_name_7'
,'mgo.table_name_8'
);
OPEN getTableName
FETCH NEXT
FROM getTableName INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN TRY
BEGIN TRANSACTION
BEGIN
declare @default varchar(max)
declare @table_name varchar(max)
set @table_name = @TableName
set @default = null
---- identify if default constraint on Active column exists
SELECT @default = d.name
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = SUBSTRING(@Table_Name,5,50)
and c.name = 'Active'
if @default is not null
BEGIN
---- renames Active column to IsActive
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql0)
---- drops constraint on Active column if one exists
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
EXEC (@sql1)
---- converts datatype from smallint to bit
DECLARE @sql2 nvarchar(4000)
SET @sql2 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql2)
---- readds default constraint, this time on renamed column
DECLARE @sql3 nvarchar(4000)
SET @sql3 = 'ALTER TABLE '+ @Table_Name + ' ADD DEFAULT ((1)) FOR IsActive'
exec (@sql3)
print @Table_Name + 'has been updated.'
END
COMMIT TRANS
END TRY
BEGIN CATCH
IF(XACT_STATE()!=0)
ROLLBACK TRAN;
SELECT ERROR_MESSAGE()AS[rename failed]
END CATCH
END
ELSE
BEGIN TRY
BEGIN TRANS
BEGIN
print @Table_name + ' has no constraint'
---- renames Active column to IsActive
DECLARE @sql11 nvarchar(4000)
SET @sql11 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql11)
print @Table_name + ' remains without constraint'
---- converts datatype from smallint to bit
DECLARE @sql12 nvarchar(4000)
SET @sql12 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql12)
print @Table_Name + 'has been updated.'
END
FETCH NEXT
FROM getTableName INTO @TableName
END
COMMIT TRANS
END TRY
BEGIN CATCH
IF(XACT_STATE()!=0)
ROLLBACK TRAN;
SELECT ERROR_MESSAGE()AS[rename failed]
END CATCH
END
CLOSE getTableName
DEALLOCATE getTableName
June 19, 2011 at 10:34 am
You're very close. Keep in mind that the beginning and ending tags of all T-SQL language features must always match up in the order in which they're declared; and that BEGIN and END tags, in addition to being able to surround an arbitrary block of code, can be required like when surrounding the blocks of code in WHILE loops, i.e. BEGIN must be the first open tag after a WHILE loop is declared and all code to that closing END tag will be executed in the loop.
This code works:
WHILE 1 = 1
BEGIN -- open BEGIN/END
BEGIN TRY -- open TRY/CATCH
BEGIN TRAN -- open TRANSACTION
COMMIT TRAN -- close TRANSACTION by committing
END TRY
BEGIN CATCH
IF ( XACT_STATE() != 0 )
ROLLBACK TRAN ; -- close TRANSACTION by rollingback
END CATCH -- close TRY/CATCH
END -- close BEGIN/END
This does not because the TRY/CATCH was opened immediately after the WHILE was declared:
-- this is your code minus some of the logic so you can see what needed to be cleaned up
WHILE @@FETCH_STATUS = 0
BEGIN TRY
BEGIN TRANSACTION
BEGIN
-- ... more code
COMMIT TRANS
END TRY
BEGIN CATCH
IF(XACT_STATE()!=0)
ROLLBACK TRAN;
SELECT ERROR_MESSAGE()AS[rename failed]
END CATCH
END
CLOSE getTableName
DEALLOCATE getTableName
I find it much easier to spot problems when code is properly indented and keywords are capitalized uniformly. Have you checked out RedGate SQL Prompt? It's worth it's weight in Gold. The Professional Edition will do reformatting for you.
You may be properly indenting your code on your own, however I cannot tell because of the way you're posting code to the forum. For future forum posts you'll likely get more and higher quality help by surrounding your sql code in these tags to maintain the indentation as well as show syntax highlighting:
[code="sql"]
code goes here
[/code]
Back to the issue at hand...see if this code does any better for you:
---retrieves tables to change
USE test
GO
DECLARE @TableName VARCHAR(MAX) ;
DECLARE @default VARCHAR(MAX) ;
DECLARE @table_name VARCHAR(MAX) ;
DECLARE getTableName CURSOR FOR
SELECT ss.name + '.' + o.name AS table_name
FROM sys.objects o
JOIN sys.schemas ss ON ss.schema_id = o.schema_id
WHERE type = 'u'
AND ss.name + '.' + o.name IN ( 'mgo.table_name_1', 'mgo.table_name_2', 'mgo.table_name_3', 'mgo.table_name_4', 'mgo.table_name_5', 'mgo.table_name_6', 'mgo.table_name_7', 'mgo.table_name_8' ) ;
OPEN getTableName
FETCH NEXT FROM getTableName INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET @table_name = @TableName
SET @default = NULL
---- identify if default constraint on Active column exists
SELECT @default = d.name
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
AND c.column_id = d.parent_column_id
WHERE t.name = SUBSTRING(@Table_Name, 5, 50)
AND c.name = 'Active' ;
IF @default IS NOT NULL
BEGIN
---- renames Active column to IsActive
DECLARE @sql0 NVARCHAR(4000)
SET @sql0 = 'sp_rename ''' + @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql0)
---- drops constraint on Active column if one exists
DECLARE @sql1 NVARCHAR(4000)
SET @sql1 = 'ALTER TABLE ' + @Table_Name + ' DROP ' + @default
EXEC (@sql1)
---- converts datatype from smallint to bit
DECLARE @sql2 NVARCHAR(4000)
SET @sql2 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql2)
---- readds default constraint, this time on renamed column
DECLARE @sql3 NVARCHAR(4000)
SET @sql3 = 'ALTER TABLE ' + @Table_Name + ' ADD DEFAULT ((1)) FOR IsActive'
EXEC (@sql3)
PRINT @Table_Name + 'has been updated.'
END
ELSE
BEGIN
PRINT @Table_name + ' has no constraint'
---- renames Active column to IsActive
DECLARE @sql11 NVARCHAR(4000)
SET @sql11 = 'sp_rename ''' + @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
EXEC(@sql11)
PRINT @Table_name + ' remains without constraint'
---- converts datatype from smallint to bit
DECLARE @sql12 NVARCHAR(4000)
SET @sql12 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql12)
PRINT @Table_Name + 'has been updated.'
END
COMMIT TRAN
END TRY
BEGIN CATCH
IF ( XACT_STATE() != 0 )
ROLLBACK TRAN ;
SELECT ERROR_MESSAGE() AS [rename failed] ;
END CATCH
FETCH NEXT FROM getTableName INTO @TableName ;
END
CLOSE getTableName
DEALLOCATE getTableName
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 6:47 pm
opc.three.
First, I want to thank you for understanding my specific problem and helping me specifically. This helps me exceedingly and I will apply it in more situations. I feel tremendously competent now at Try/Catch, commit tran and rollback tran. I have a template I will use until it is etched into my brain. There is more for me to learn around error handling, but for my purposes we/I nailed it! Thank you.
Redgate SQL Profiler answers the question about an editing application for SQL. I have wondered about this, I DO need it but I will have to wait until I can afford $195.
Wheeee! got it working and happy moving forward in my learning.
hxkresl
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply