I am trying to do something very simple...
I want to add new column/s to some tables.I know I can use ALTER table but the way I am trying to do this is.
The steps are as follows:
1)Create back up tables with the new columns
2)Add data by copying data from the OLD tables and adding placeholders for the extra column in for the back up table. e.g
if backup table has col1, col2 and col3 is new
then
insert into bk_up_table(col1, col2, col3)select col1, col2, hard_coded_value from old_table.
3)Now drop old table
4)Create new table with the same structure as back up tables(with the new column/s).
5)Copy back data from backup to New table.
Now I want to do that in one SQL script..simple..right?
But for some reason SQL Server throws an error when I try to run the entire script, but if I run each one of the steps 1,2,3,4, sperately each one runs fine.
WHY??????
I do not have any GOs so I am trying to run everything in one batch with RETURN statements for errors. All this is in a transaction which Rolls back after any RETURN
Server:SQL server 7.0 running on Win nt 4.0 sp2
Client: Named Pipes/SQL Enterprise Manager /Win 2K
Below is the script in question:
I want to add new column/s to some tables.I know I can use ALTER table but the way I am trying to do this is.
The steps are as follows:
1)Create back up tables with the new columns
2)Add data by copying data from the OLD tables and adding placeholders for the extra column in for the back up table. e.g
if backup table has col1, col2 and col3 is new
then
insert into bk_up_table(col1, col2, col3)select col1, col2, hard_coded_value from old_table.
3)Now drop old table
4)Create new table with the same structure as back up tables(with the new column/s).
5)Copy back data from backup to New table.
Now I want to do that in one SQL script..simple..right?
But for some reason SQL Server throws an error when I try to run the entire script, but if I run each one of the steps 1,2,3,4, sperately each one runs fine.
WHY??????
I do not have any GOs so I am trying to run everything in one batch with RETURN statements for errors. All this is in a transaction which Rolls back after any RETURN
Server:SQL server 7.0 running on Win nt 4.0 sp2
Client: Named Pipes/SQL Enterprise Manager /Win 2K
Below is the script in question:
Code:
if exists (select * from sysobjects where id = object_id(N'[dbo].[Upgrade35Script]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Upgrade35Script]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
PRINT "Creating procedure for Upgrade Scripts..."
GO
CREATE PROCEDURE [Upgrade35Script] AS
-- BEGIN -- BACKUP THE CURRENT DATA SO IT ISN'T LOST WHEN WE MODIFY THE EXISTING DATABASE
-- Before dropping and recreating the modified tables, we need to back up the data so that we can
-- restore it into the new tables. Let's create exact replicas of the new tables and convert the
-- old data to fit into the new tables.
BEGIN
BEGIN TRANSACTION
PRINT "**************************************************"
if not exists (select * from sysobjects where id = object_id(N'[dbo].[SLEmlLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT "Table SLEmlLog does not exist !!"
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
CREATE TABLE [dbo].[BACKUP_SimpleBuyStatus] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[Action] [varchar] (8) NULL ,
[Status] [varchar] (8) NULL ,
[Result] [varchar] (8) NULL ,
[EBOID] [int] NULL ,
[BORGID] [int] NULL ,
[SysUserID] [int] NULL ,
[DateTime] [varchar] (32) NULL
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating table BACKUP_SimpleBuyStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "BACKUP_SimpleBuyStatus was created without error."
END
ALTER TABLE [dbo].[BACKUP_SimpleBuyStatus] WITH NOCHECK ADD
CONSTRAINT [PK_BACKUP_SimpleBuyStatus] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating the primary key on table BACKUP_SimpleBuyStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Primary key for BACKUP_SimpleBuyStatus was created without error."
END
PRINT "**************************************************"
CREATE TABLE [dbo].[BACKUP_SLCommLg] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[MessageNum] [int] NULL ,
[SenderBORG] [int] NULL ,
[ReceiverBORG] [int] NULL ,
[LinkedReqID] [int] NULL ,
[Status] [varchar] (8) NULL ,
[RsndCnt] [int] NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating table BACKUP_SLCommLg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "BACKUP_SLCommLg was created without error."
END
ALTER TABLE [dbo].[BACKUP_SLCommLg] WITH NOCHECK ADD
CONSTRAINT [PK_BACKUP_SLCommLg] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating the primary key on table BACKUP_SLCommLg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Primary key for BACKUP_SLCommLg was created without error."
END
PRINT "**************************************************"
CREATE TABLE [dbo].[BACKUP_SLERPMsg] (
[DocID] [varchar] (16) NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[UniqueNumber] [varchar] (16) NOT NULL ,
[LineNumber] [varchar] (16) NULL ,
[ErrorCode] [varchar] (20) NULL ,
[ShortMessage] [varchar] (255) NULL ,
[LongMessage] [varchar] (255) NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating table BACKUP_SLERPMsg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "BACKUP_SLERPMsg was created without error."
END
ALTER TABLE [dbo].[BACKUP_SLERPMsg] WITH NOCHECK ADD
CONSTRAINT [PK_BACKUP_SLERPMsg] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType],
[UniqueNumber]
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating the primary key on table BACKUP_SLERPMsg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Primary key for BACKUP_SLERPMsg was created without error."
END
PRINT "**************************************************"
CREATE TABLE [dbo].[BACKUP_SLStatus] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[UniqueNumber] [int] NOT NULL ,
[Action] [varchar] (8) NULL ,
[Status] [varchar] (8) NULL ,
[Result] [varchar] (8) NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating table BACKUP_SLStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "BACKUP_SLStatus was created without error."
END
ALTER TABLE [dbo].[BACKUP_SLStatus] WITH NOCHECK ADD
CONSTRAINT [PK_BACKUP_SLStatus] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType],
[UniqueNumber]
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating the primary key on table BACKUP_SLStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Primary key for BACKUP_SLStatus was created without error."
END
PRINT "**************************************************"
CREATE TABLE [dbo].[BACKUP_SimpleLinkAccount] (
[BORGID] [int] NOT NULL ,
[CostCenterID] [int] NOT NULL ,
[ERPCostCenter] [varchar] (255) NULL ,
[LastUpdateID] [int] NULL DEFAULT 0,
[Amount] [varchar] (32) NULL
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating table BACKUP_SimpleLinkAccount."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "BACKUP_SimpleLinkAccount was created without error."
END
ALTER TABLE [dbo].[BACKUP_SimpleLinkAccount] WITH NOCHECK ADD
CONSTRAINT [PK_BACKUP_SimpleLinkAccount] PRIMARY KEY NONCLUSTERED
(
[BORGID],
[CostCenterID]
) ON [PRIMARY]
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while creating the primary key on table BACKUP_SimpleLinkAccount."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Primary key for BACKUP_SimpleLinkAccount was created without error."
END
PRINT "**************************************************"
-- Now that we have created the backup tables, lets copy the data into them, converting it as needed.
INSERT INTO BACKUP_SimpleBuyStatus (DocID,
DocType,
Action,
Status,
Result,
EBOID,
BORGID,
SysUserID,
DateTime)
SELECT DocID, DocType, Action, Status, Result, EBOID, BORGID, SysUserID, convert(varchar(32),getdate()) FROM SimpleBuyStatus
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving data from SimpleBuyStatus into BACKUP_SimpleBuyStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Data was successfully moved from SimpleBuyStatus into BACKUP_SimpleBuyStatus."
END
PRINT "**************************************************"
INSERT INTO BACKUP_SLCommLg ( DocID,
DocType,
MessageNum,
SenderBORG,
ReceiverBORG,
LinkedReqID,
Status,
RsndCnt)
SELECT DocID, DocType, MessageNum, SenderBORG, ReceiverBORG, LinkedReqID, "", 0 FROM SLEmlLog
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving data from SLCommLg into BACKUP_SLCommLg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Data was successfully moved from SLCommLg into BACKUP_SLCommLg."
END
PRINT "**************************************************"
INSERT INTO BACKUP_SLERPMsg ( DocID,
DocType,
UniqueNumber,
LineNumber,
ErrorCode,
ShortMessage,
LongMessage)
SELECT DocID, DocType, UniqueNumber, LineNumber, ErrorCode, ShortMessage, LongMessage FROM SLERPMsg
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving data from SLERPMsg into BACKUP_SLERPMsg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Data was successfully moved from SLERPMsg into BACKUP_SLERPMsg."
END
PRINT "**************************************************"
INSERT INTO BACKUP_SLStatus ( DocID,
DocType,
UniqueNumber,
Action,
Status,
Result)
SELECT DocID, DocType, UniqueNumber, Action, Status, Result FROM SLStatus
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving data from SLStatus into BACKUP_SLStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Data was successfully moved from SLStatus into BACKUP_SLStatus."
END
PRINT "**************************************************"
INSERT INTO BACKUP_SimpleLinkAccount ( BORGID,
CostCenterID,
ERPCostCenter,
Amount)
SELECT BORGID, CostCenterID, ERPCostCenter, Amount FROM SimpleLinkAccount
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving data from SimpleLinkAccount into BACKUP_SimpleLinkAccount."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "Data was successfully moved from SimpleLinkAccount into BACKUP_SimpleLinkAccount."
END
PRINT "**************************************************"
-- END -- BACKUP THE CURRENT DATA SO IT ISN'T LOST WHEN WE MODIFY THE EXISTING DATABASE
-- BEGIN -- MAKE THE MODIFICATIONS TO THE EXISTING DATABASE
-- Added the DateTime column to each of the following tables (up until the new table definitions).
if exists (select * from sysobjects where id = object_id(N'[dbo].[SimpleBuyStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SimpleBuyStatus]
CREATE TABLE [dbo].[SimpleBuyStatus] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[Action] [varchar] (8) NULL ,
[Status] [varchar] (8) NULL ,
[Result] [varchar] (8) NULL ,
[EBOID] [int] NULL ,
[BORGID] [int] NULL ,
[SysUserID] [int] NULL ,
[DateTime] [varchar] (32) NULL
) ON [PRIMARY]
-- We are changing the name of the SLEmlLog table to SLCommLg. Therefore we are dropping both tables.
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLEmlLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLEmlLog]
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLCommLg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLCommLg]
CREATE TABLE [dbo].[SLCommLg] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[MessageNum] [int] NULL ,
[SenderBORG] [int] NULL ,
[ReceiverBORG] [int] NULL ,
[LinkedReqID] [int] NULL ,
[Status] [varchar] (8) NULL ,
[RsndCnt] [int] NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLERPMsg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLERPMsg]
CREATE TABLE [dbo].[SLERPMsg] (
[DocID] [varchar] (16) NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[UniqueNumber] [varchar] (16) NOT NULL ,
[LineNumber] [varchar] (16) NULL ,
[ErrorCode] [varchar] (20) NULL ,
[ShortMessage] [varchar] (255) NULL ,
[LongMessage] [varchar] (255) NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLStatus]
CREATE TABLE [dbo].[SLStatus] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[UniqueNumber] [int] NOT NULL ,
[Action] [varchar] (8) NULL ,
[Status] [varchar] (8) NULL ,
[Result] [varchar] (8) NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
-- These are new tables that support error processing.
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLDBErr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLDBErr]
CREATE TABLE [dbo].[SLDBErr] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLEmlErr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLEmlErr]
CREATE TABLE [dbo].[SLEmlErr] (
[DocID] [int] NOT NULL ,
[DocType] [varchar] (8) NOT NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
-- This is a new table to support budget update.
if exists (select * from sysobjects where id = object_id(N'[dbo].[SLBudgetUpdate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SLBudgetUpdate]
CREATE TABLE [dbo].[SLBudgetUpdate] (
[BORGID] [varchar] (32) NOT NULL ,
[CostCenter] [varchar] (255) NOT NULL ,
[DocID] [varchar] (32) NOT NULL ,
[Amount] [varchar] (32) NULL ,
[DateTime] [varchar] (32) NULL DEFAULT convert(varchar(32),getdate())
) ON [PRIMARY]
-- Added the LastUpdateID column.
if exists (select * from sysobjects where id = object_id(N'[dbo].[SimpleLinkAccount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SimpleLinkAccount]
CREATE TABLE [dbo].[SimpleLinkAccount] (
[BORGID] [int] NOT NULL ,
[CostCenterID] [int] NOT NULL ,
[ERPCostCenter] [varchar] (255) NULL ,
[LastUpdateID] [int] NULL DEFAULT 0,
[Amount] [varchar] (32) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SimpleBuyStatus] WITH NOCHECK ADD
CONSTRAINT [PK_SimpleBuyStatus] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLCommLg] WITH NOCHECK ADD
CONSTRAINT [PK_SLCommLg] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLERPMsg] WITH NOCHECK ADD
CONSTRAINT [PK_SLERPMsg] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType],
[UniqueNumber]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLStatus] WITH NOCHECK ADD
CONSTRAINT [PK_SLStatus] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType],
[UniqueNumber]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLDBErr] WITH NOCHECK ADD
CONSTRAINT [PK_SLDBErr] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLEmlErr] WITH NOCHECK ADD
CONSTRAINT [PK_SLEmlErr] PRIMARY KEY NONCLUSTERED
(
[DocID],
[DocType]
) ON [PRIMARY]
ALTER TABLE [dbo].[SLBudgetUpdate] WITH NOCHECK ADD
CONSTRAINT [PK_SLBudgetUpdate] PRIMARY KEY NONCLUSTERED
(
[BORGID],
[CostCenter]
) ON [PRIMARY]
ALTER TABLE [dbo].[SimpleLinkAccount] WITH NOCHECK ADD
CONSTRAINT [PK_SimpleLinkAccount] PRIMARY KEY NONCLUSTERED
(
[BORGID],
[CostCenterID]
) ON [PRIMARY]
--Reqstatus starts here
if not exists (select * from ReqStatus where ReqStatusID = 108)
begin
Insert into ReqStatus values(108,"Pre-Encum Pending","Req is pending at PreEncumbrance","PreEncumbrance","PreEncumbrance")
end
if not exists (select * from ReqStatus where ReqStatusID = 109)
begin
Insert into ReqStatus values(109,"Pre-Encum Backout Success","Pre-Encumbrance Rejected","PreEncumbrance","PreEncumbrance")
end
if not exists (select * from ReqStatus where ReqStatusID = 110)
begin
Insert into ReqStatus values(110,"Encum Pending","Encumbrance","Encumbrance","Encumbrance")
end
if not exists (select * from ReqStatus where ReqStatusID = 111)
begin
Insert into ReqStatus values(111,"Pre-Encum Backout","PreEncum","PreEncum","PreEncum")
end
if not exists (select * from ReqStatus where ReqStatusID = 112)
begin
Insert into ReqStatus values(112,"Pre-Encum Backout Fail","PreEncum","PreEncum","PreEncum")
end
if not exists (select * from ReqStatus where ReqStatusID = 113)
begin
Insert into ReqStatus values(113,"Pre-Encum Rejected","PreEncum","PreEncum","PreEncum")
end
--Reqstatus ends here
--PoStatus starts here
if not exists (select * from POStatus where POStatusID = -5)
begin
Insert into POStatus values(-5,"Pre-Encumbrance Backing","Pre-Encum backing","PreBack","Pre-Encumbrance Backing",0,0,1,0,1,1,1,1,0,0)
end
if not exists (select * from POStatus where POStatusID = -4)
begin
Insert into POStatus values(-4,"Encum Rejected","Encumbrance Rejected","EncRejected","EncumbranceRejected",0,0,1,0,1,1,1,1,0,0)
end
if not exists (select * from POStatus where POStatusID = -3)
begin
Insert into POStatus values(-3,"Encum Backout","Encumbrance Backing out","EncBackOut","Encumbrance Backing",0,0,1,0,1,1,1,1,0,0)
end
if not exists (select * from POStatus where POStatusID = -2)
begin
Insert into POStatus values(-2,"Encum Pending","Encumbrance Pending","Encumbrance","Encumbrance Pending",0,0,1,0,1,1,1,1,0,0)
end
if not exists (select * from POStatus where POStatusID = -6)
begin
Insert into POStatus values(-6,"Encum Backout Success","Backout Success","BackOutSuccess","Backout Success",0,0,0,0,1,1,1,1,0,0)
end
if not exists (select * from POStatus where POStatusID = -7)
begin
Insert into POStatus values(-7,"Encum Backout Fail","Backout failed","BackoutFailed","Backout Failed",0,0,1,0,1,1,1,1,0,0)
end
--POstatus ends here
-- END -- MAKE THE MODIFICATIONS TO THE EXISTING DATABASE
-- BEGIN -- COPY THE DATA BACK TO THE MAIN TABLES FROM THE BACKUP TABLES
INSERT INTO SimpleBuyStatus ( DocID,
DocType,
Action,
Status,
Result,
EBOID,
BORGID,
SysUserID,
DateTime)
SELECT DocID, DocType, Action, Status, Result, EBOID, BORGID, SysUserID, DateTime FROM BACKUP_SimpleBuyStatus
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving the backed up data from BACKUP_SimpleBuyStatus into SimpleBuyStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "The backed up data was successfully moved from BACKUP_SimpleBuyStatus into SimpleBuyStatus."
END
PRINT "**************************************************"
INSERT INTO SLCommLg ( DocID,
DocType,
MessageNum,
SenderBORG,
ReceiverBORG,
LinkedReqID,
Status,
RsndCnt)
SELECT DocID, DocType, MessageNum, SenderBORG, ReceiverBORG, LinkedReqID, Status, RsndCnt FROM BACKUP_SLCommLg
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving the backed up data from BACKUP_SLCommLg into SLCommLg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "The backed up data was successfully moved from BACKUP_SLCommLg into SLCommLg."
END
PRINT "**************************************************"
INSERT INTO SLERPMsg ( DocID,
DocType,
UniqueNumber,
LineNumber,
ErrorCode,
ShortMessage,
LongMessage)
SELECT DocID, DocType, UniqueNumber, LineNumber, ErrorCode, ShortMessage, LongMessage FROM BACKUP_SLERPMsg
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving the backed up data from BACKUP_SLERPMsg into SLERPMsg."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "The backed up data was successfully moved from BACKUP_SLERPMsg into SLERPMsg."
END
PRINT "**************************************************"
INSERT INTO SLStatus ( DocID,
DocType,
UniqueNumber,
Action,
Status,
Result)
SELECT DocID, DocType, UniqueNumber, Action, Status, Result FROM BACKUP_SLStatus
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving the backed up data from BACKUP_SLStatus into SLStatus."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "The backed up data was successfully moved from BACKUP_SLStatus into SLStatus."
END
PRINT "**************************************************"
INSERT INTO SimpleLinkAccount ( BORGID,
CostCenterID,
ERPCostCenter,
Amount)
SELECT BORGID, CostCenterID, ERPCostCenter, Amount FROM BACKUP_SimpleLinkAccount
IF (@@ERROR <> 0)
BEGIN
PRINT "Error while moving the backed up data from BACKUP_SimpleLinkAccount into SimpleLinkAccount."
PRINT "Procedure for Upgrade Scripts FAILED!"
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
PRINT "The backed up data was successfully moved from BACKUP_SimpleLinkAccount into SimpleLinkAccount."
END
COMMIT TRANSACTION
PRINT "**************************************************"
PRINT "Procedure for Upgrade Scripts completed succesfully!"
PRINT "**************************************************"
RETURN
END
-- END -- COPY THE DATA BACK TO THE MAIN TABLES FROM THE BACKUP TABLES
PRINT "Created procedure for Upgrade Scripts"
GO
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
PRINT "**************************************************"
PRINT "Running procedure for Upgrade Scripts..."
GO
PRINT "**************************************************"
GO
exec Upgrade35Script
PRINT "**************************************************"
GO
PRINT "Dropping procedure for Upgrade Scripts..."
GO
PRINT "**************************************************"
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[Upgrade35Script]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Upgrade35Script]
GO
PRINT "Procedure for Upgrade Scripts dropped succesfully!"
GO
PRINT "**************************************************"
GO
