April 30, 2007 at 10:48 am
Does anyone know what is wrong with this code? It keeps coming up with the error
Msg 170, Level 15, State 1, Procedure SP_test, Line 71
Line 71: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Procedure SP_test, Line 93
Line 93: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Procedure SP_test, Line 106
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Procedure SP_test, Line 111
Incorrect syntax near the keyword 'END'.
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
PROCEDURE SP_test
@ServerName VARCHAR(200),
@Country VARCHAR(20) = Null
@Jobnumber VARCHAR(100)
AS
SET
XACT_ABORT ON
DECLARE
@object int
DECLARE
@hr int
DECLARE
@return varchar(200)
DECLARE
@tbname varchar(30)
DECLARE
@exec_str varchar(200)
DECLARE
@Log varchar(200)
DECLARE
@sMessage varchar(200)
SET
NOCOUNT ON
-- Set the server to the local server
IF
@ServerName is NULL
SELECT @ServerName = @@servername
-- Create an object that points to the SQL Server
EXEC
@hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF
@hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
BEGIN
EXEC @hr = sp_OAMethod @object,'Connect',NULL,@ServerName
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
END
-- Verify the connection
EXEC
@hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF
@hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
SET
@exec_str = "DECLARE script_cursor CURSOR FOR SELECT name FROM tmpTable"
EXEC
(@exec_str)
OPEN
script_cursor
FETCH
NEXT FROM script_cursor INTO @tbname
WHILE
(@@fetch_status <> -1)
BEGIN
--Ensure table exists on database
IF exists (select name from sysobjects where name = @tbname and type = 'U')
BEGIN TRY
BEGIN TRANSACTION
TRUNCATE table "'+ @tbname +'"
SET @exec_str = 'INSERT INTO "'+ @tbname +'" '
+ 'SELECT * FROM '
+ 'OPENQUERY(SCION, ''SELECT * FROM "'+ @tbname +'"'') '
+ 'WHERE CountyID = @Country'
EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
-- Insert details into log
SET @sMessage = 'Imported table ' + @tbname
SET @Log = 'INSERT INTO PROGRESS_LOG'
+ 'VALUES ("' + @Jobnumber + '", "' + @sMessage + '", " '' )'
EXECUTE sp_EXECUTESQL @Log
COMMIT
END TRY
BEGIN CATCH
DECLARE @err int
SET @err = @@error --trap the error number
ROLLBACK TRAN
SET @sMessage = 'Table' + @tbname + 'Does Not exist'
SET @Log = 'INSERT INTO PROGRESS_LOG '
+ 'VALUES ("' + @Jobnumber + '", "' + @sMessage + '", " "' + @err + '" )'
EXECUTE sp_EXECUTESQL @Log
END CATCH
FETCH
NEXT FROM script_cursor INTO @tbname
END
CLOSE
script_cursor
DEALLOCATE
script_cursor
-- Destroy the object
EXEC
@hr = sp_OADestroy @object
IF
@hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
April 30, 2007 at 11:41 am
I don't believe you can do IF... BEGIN TRY. I think you need to do something like this:
IF (something)
BEGIN
BEGIN TRY
...
April 30, 2007 at 1:15 pm
Aaron Ingold is right... Use this as a template...
DECLARE @t tinyint
SET @t = 1
IF (@t = 1)
begin
BEGIN try
SELECT @t
END try
BEGIN catch
PRINT error_message()
END catch
end
May 1, 2007 at 4:44 am
Guys,
I have copied the code by Charles into my database and tried to parse it. I still get the following error message.
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'try'.
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'try'.
Msg 195, Level 15, State 10, Line 9
'error_message' is not a recognized function name.
Do you think it is something to do with settings on the serve or database?
May 1, 2007 at 8:57 am
Are you running this against a 2000 server or against a 2005 server in 8.0 compatibility mode? Either of those will be the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply