April 14, 2015 at 7:57 am
Hi,
I'm a bit rusty on error handling, so I need some help with below script.
SET NOCOUNT ON
DECLARE @total int, @id int, @sql nvarchar(MAX);
DECLARE @dbname varchar(200);
DECLARE @t table(ID int not null identity(1,1), name varchar(255));
INSERT INTO @t(name)
SELECT name
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
SET @total = @@ROWCOUNT;
SET @id=1;
WHILE @id <= @total
BEGIN
SELECT @dbname = name FROM @t WHERE ID = @id
SET @sql =
'
SET NOCOUNT ON
USE [' + @dbname + '];
-- Checking if MyTableOLD does not exist. If exists, the script should not create the new table.
IF OBJECT_ID(''dbo.MyTableOLD'', ''U'') IS NULL
BEGIN
IF OBJECT_ID(''dbo.MyTableNew'', ''U'') IS NULL
BEGIN
CREATE TABLE [dbo].[MyTableNEW]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[RecipeID] [int] NOT NULL,
[InventoryItemID] [int] NOT NULL,
[DOB] [date] NOT NULL,
[Ratio] [real] NOT NULL
) ON [PRIMARY]
--Copying data to new table if data exists.
IF EXISTS(
SELECT 1 FROM dbo.MyTable
)
BEGIN
BEGIN TRAN
INSERT INTO dbo.MyTableNEW (RecipeID, InventoryItemID, DOB, Ratio)
SELECT RecipeID, InventoryItemID, DOB, Ratio
FROM dbo.MyTable;
COMMIT
END
--Dropping existing FKs after copying data and before swapping tables.
IF OBJECT_ID(''FK_MyTable_InventoryItem'', ''F'') IS NOT NULL
BEGIN
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyTable_InventoryItem];
END
IF OBJECT_ID(''FK_MyTable_Recipe'', ''F'') IS NOT NULL
BEGIN
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyTable_Recipe];
END
--Swapping tables
EXEC sp_rename ''PK_MyTable'', ''PK_MyTableOLD''; -- Renaming old PK. No need to rename new one beacuse does not exist yet.
EXEC sp_rename ''MyTable'', ''MyTableOLD''; -- changing name on existing table
EXEC sp_rename ''MyTableNEW'', ''MyTable''; -- changing name on the new table, assigning real name.
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [NCI_MyTable_DOB] ON [dbo].[MyTable]
(
[DOB] ASC
)
INCLUDE ( [RecipeID],
[InventoryItemID],
[Ratio]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [NCI_MyTable_RID_DOB_IIID] ON [dbo].[MyTable]
(
[RecipeID] ASC,
[DOB] ASC,
[InventoryItemID] ASC
)
INCLUDE ( [Ratio]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [UC_RecipeID_InventoryItemID_DOB] UNIQUE NONCLUSTERED
(
[RecipeID] ASC,
[InventoryItemID] ASC,
[DOB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
/****** Recreating FKs ******/
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [FK_MyTable_InventoryItem] FOREIGN KEY([InventoryItemID])
REFERENCES [dbo].[InventoryItem] ([InventoryItemID])
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_InventoryItem]
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [FK_MyTable_Recipe] FOREIGN KEY([RecipeID])
REFERENCES [dbo].[Recipe] ([RecipeID])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_Recipe]
PRINT ''Changes succesfully applied to ['+@dbname+']''
END
END
'
--PRINT @sql
EXECUTE sp_executesql @sql
SET @id = @id + 1
END
Basically, the script loops across hundreds of databases (exact schema) and moves data from old table to a new table, which has improved Indexes and slighlty different schema; it ran already for empty sets so it also checks for the existence of a table called MyTableOLD.
The script works "as is" but I want to be able to accomplish these two conditions as well:
1) If there is an error while copying the data during the execution on database A, be able to handle that gracefully and move on to next one in the loop without halting the whole thing. I know I'm using BEGIN TRAN/COMMIT but that does not handle errors gracefully.
2) Be able to stop its execution at will but allowing the script to finish working on current database so later I can resume from there.
#2 is key for me because I don't know how much it will take to finish; I have a limited time window to run this. If I ended running out of time, I want to be able to stop it but in an elegant way.
Thanks in advance
April 14, 2015 at 8:07 am
Hmmm .... thinking loud and to myself.
I still would like to hear any advice but I think I can't rollback DDL statements in TSQL. In other words, and after re-reading the my code, I think that my best bet is use the PRINT statement to revise the last successful swap and resume from there.
April 14, 2015 at 9:22 am
sql-lover (4/14/2015)
Hmmm .... thinking loud and to myself.I still would like to hear any advice but I think I can't rollback DDL statements in TSQL. In other words, and after re-reading the my code, I think that my best bet is use the PRINT statement to revise the last successful swap and resume from there.
You can rollback DDL statements in TSQL the same way you can rollback DML statements.
You can't rollback DDL statements in PL/SQL, because it will autocommit the current transaction.
Point for SQL Server. 😀
April 14, 2015 at 10:04 am
Luis Cazares (4/14/2015)
sql-lover (4/14/2015)
Hmmm .... thinking loud and to myself.I still would like to hear any advice but I think I can't rollback DDL statements in TSQL. In other words, and after re-reading the my code, I think that my best bet is use the PRINT statement to revise the last successful swap and resume from there.
You can rollback DDL statements in TSQL the same way you can rollback DML statements.
You can't rollback DDL statements in PL/SQL, because it will autocommit the current transaction.
Point for SQL Server. 😀
Take a look on my code...
If I suddenly stop my script after the data has been transferred to the new table, I may end with partially renamed objects inside the last database where the statements were running, those are my DDL statements.
My question is, can I rollback those as well, so I won't end with a partially renamed objects? If that's true, what would be the TSQL code or changes I will have to made.
I can rely on the last PRINT statement, if I halted the script abruptly and assume it did not complete on that last database then manually rename, etc. But I was looking for a more elegant and automatic way.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply