September 26, 2008 at 7:50 pm
How do you stop a script from continuing execution? I tried using RAISERROR(), but the script does not stop executing.
The script is attached below:
CREATE TABLE #linked_tables
(
table_cat varchar(100),
table_schem varchar(100),
table_name varchar(100),
table_type varchar(100),
remarks varchar(100)
)
INSERT INTO #linked_tables
EXECUTE sp_tables_ex SALESFORCE
DECLARE @linked_table_name VARCHAR(100)
DECLARE table_cursor CURSOR FAST_FORWARD FOR SELECT table_name FROM #linked_tables;
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @linked_table_name
WHILE @@fetch_status = 0
BEGIN
BEGIN TRY
EXEC(N'SELECT * INTO [' + @linked_table_name + N'] FROM [SALESFORCE]...[' + @linked_table_name + N'];')
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() <> '7306') AND (ERROR_MESSAGE() NOT LIKE 'Cannot open the table')
BEGIN
RAISERROR(@error_message, @error_severity, @error_state) WITH LOG, NOWAIT;
END
END CATCH
FETCH NEXT FROM table_cursor INTO @linked_table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
DROP TABLE #linked_tables
September 26, 2008 at 8:31 pm
Your script looks a little strange. The Variables you're using in the CATCH aren't declared anywhere. If you want to raise the error you got, then you need to use the FUNCTIONS error_number(), error_desc(), etc...
Also - keep in mind that if the error level isn't high enough - it's considered a warning, and doesn't interrupt anything.
As an aside - aren't you telling it NOT to stop with the table's missing?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2008 at 9:03 pm
Matt Miller (9/26/2008)
Your script looks a little strange. The Variables you're using in the CATCH aren't declared anywhere. If you want to raise the error you got, then you need to use the FUNCTIONS error_number(), error_desc(), etc...
I apologize...I was trying to omit "extra" code and inadvertently removed the declarations:
IF (ERROR_NUMBER() <> '7306') AND (ERROR_MESSAGE() NOT LIKE 'Cannot open the table')
BEGIN
DECLARE @error_message VARCHAR(500);
DECLARE @error_severity INT;
DECLARE @error_state INT;
SET @error_message = ERROR_MESSAGE();
SET @error_severity = ERROR_SEVERITY();
SET @error_state = ERROR_STATE();
RAISERROR(@error_message, @error_severity, @error_state) WITH LOG, NOWAIT;
END
As an aside - aren't you telling it NOT to stop with the table's missing?
What do you mean?
To give a little background on my situation, I'm copying data from a linked server. However, some of the tables aren't available to be copied. I don't want to halt the script execution due to the 7306 errors. However, any other errors (like the one listed below) should stop script execution.
Error Number: 7330
Error Severity: 16
Error State: 2
Error Procedure: Null
Error Line: 1
Error Message: Cannot fetch a row from OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
September 26, 2008 at 9:11 pm
I just remembered something. There are some special issue with trying to use try/catch over a linked server.
Per BOL:
Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. If a remote stored procedure calls RAISERROR with severity less than 20 and the remote stored procedure is scoped within a TRY block on the local server, RAISERROR does not cause control to pass to the CATCH block of the TRY…CATCH construct. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.
So - a 16 on the remote isn't going to get caught locally, unless you do a try catch within the EXEC call, and raise an error with sev > 20. Which means you'd have to be using the linked server as SA on the remote (not the greatest security wise)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2008 at 9:35 pm
jlp3630 (9/26/2008)
As an aside - aren't you telling it NOT to stop with the table's missing?
What do you mean?
To give a little background on my situation, I'm copying data from a linked server. However, some of the tables aren't available to be copied. I don't want to halt the script execution due to the 7306 errors. However, any other errors (like the one listed below) should stop script execution.
Error Number: 7330
Error Severity: 16
Error State: 2
Error Procedure: Null
Error Line: 1
Error Message: Cannot fetch a row from OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
Makes sense - wasn't sure what other message you were getting....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply