August 18, 2008 at 7:50 am
I'm getting
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
in this script on the marked line.
Also on second marked line, I'm getting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'.
Any ideas? Seems I may be having problems with IFs, BEGINs and ENDs, is that so?
BEGIN TRAN
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#error_status'))
BEGIN
DROP TABLE #error_status
END
GO
CREATE TABLE #error_status (has_error int, has_null_records varchar(100))
GO
IF @@TRANCOUNT>0
COMMIT
USE [LSO]
GO
BEGIN TRAN
GO
--**************************************
-- ptLabResults
--**************************************
-- Altering ptLabResults if it exists
-- ALSO, Save, then remove any records with NULL MRNs from ptLabResults
Print 'Altering ptLabResults if it exists'
IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0
BEGIN
INSERT INTO #error_status (has_null_records) VALUES ('ptLabResults')
SELECT * INTO ptLabResults_Null_MRN FROM ptLabResults WHERE MRN IS NULL OR RTRIM(MRN) = ''
DELETE FROM ptLabResults WHERE MRN IS NULL OR RTRIM(MRN) = ''
END
GO
IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ptLabResults]') AND name = N'IX_ptLabResults_MRN')
DROP INDEX [IX_ptLabResults_MRN] ON [dbo].[ptLabResults] WITH ( ONLINE = OFF ) -- First Error happening here
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK INSERT INTO #error_status (has_error) VALUES (1) SET NOEXEC ON END
GO
IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0
ALTER TABLE ptLabResults ALTER COLUMN MRN varchar(25) NULL
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK INSERT INTO #error_status (has_error) VALUES (1) SET NOEXEC ON END
GO
IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0
UPDATE ptLabResults SET MRN = RTRIM(MRN)
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK INSERT INTO #error_status (has_error) VALUES (1) SET NOEXEC ON END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ptLabResults]') AND name = N'IX_ptLabResults_MRN')
CREATE NONCLUSTERED INDEX [IX_ptLabResults_MRN] ON [dbo].[ptLabResults]
([MRN] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK INSERT INTO #error_status (has_error) VALUES (1) SET NOEXEC ON END
GO
END
IF @@TRANCOUNT>0 -- Second error happening here:
COMMIT
SET NOEXEC OFF
USE [master]
GO
IF EXISTS(SELECT * FROM #error_status)
BEGIN
-- Restoring database LSO from LSO_MRN_MODS.bak
Print 'Restoring database LSO from LSO_MRN_MODS.bak'
RESTORE DATABASE [LSO]
FROM DISK = 'LSO_MRN_MODS.bak'
WITH REPLACE
END
ELSE
BEGIN
-- Backing up database LSO to LSO_MRN_MODS_Done.bak
Print 'Backing up database LSO to LSO_MRN_MODS_Done.bak'
BACKUP DATABASE [LSO]
TO DISK = 'LSO_MRN_MODS_Done.bak'
WITH INIT
END
GO
DROP TABLE #error_status
GO
August 18, 2008 at 9:09 am
Try taking the GO statements out.
They indicate the end of a batch, which is breaking your BEGIN...END blocks
August 18, 2008 at 9:56 am
Something to remember...thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply