Transaction With Exec and Fetch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction With Exec and Fetch

Author

Topic

andydev
Starting Member

5 Posts

Posted - 2002-05-26 : 05:28:18

Hello all,
I'm building some script and I'm having problems making it inside a Transaction, I'll write here the first problem I have :) maybe I'll write the others when I fix this one ;)

I got a cursor, that from it I build a string which I exec it afterwards, This is the code I use to execute every string I build, and I want to build it all in a transaction, so if one fails it rollbacks everything.
I didnt write the way I build the cursor, because I know that part works ok for now (When it prints the string I see they are Ok)


open collate_curs
declare @TableName varchar (40), @ColName varchar (40), @Length int, @isnullable varchar (10), @xTypeName varchar(50), @idCol int
fetch next from collate_curs into @TableName, @ColName, @Length, @isnullable, @xTypeName, @idCol
while (@@fetch_status = 0)
BEGIN
	set @sSQLtbl = 'ALTER TABLE '
	set @sSQLcol = ' ALTER COLUMN '
	set @collate = ' COLLATE database_default
    set @sSQLtbl = 'ALTER TABLE ' +' ['+ @TableName+']' + @sSQLcol +@ColName+ ' '+ @xTypeName+ '('+ cast( @Length as varchar (5))+') ' + @collate + @isnullable
    print @sSQLtbl
    exec ( @sSQLtbl)
    fetch next from collate_curs into @TableName, @ColName, @Length, @isnullable, @xTypeName, @idCol
END
close collate_curs
deallocate collate_curs

Edited by - andydev on 05/26/2002 07:26:32

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-26 : 13:12:41

Try to get out of the habit of creating cursors.
In the past sql server has had problems with the server hanging if you create a cursor inside a transaction.

In this case you are trying to include schema change statements within a transaction.
Rather than do this I would take a backup of the database and if the procedure fails restore the backup.
In any case I would take a backup and restart the server after the change.
You can get some odd effects if you make changes like this and leave things in memory.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

andydev
Starting Member

5 Posts

Posted - 2002-05-27 : 02:54:14

Could you write some example of how to do it?
how cna I check if it failed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources