February 1, 2012 at 4:51 am
Hi Guys,
I have a program that needs to copy data from various sources. Some of these sources are not always available but the proc needs to run every 30 mins (some of the servers may be physically switched off at the time)
My idea was this:
CREATE PROCEDURE FOO AS
BEGIN
-- for server 1..
BEGIN TRY
~Insert table
~Select openquery
END TRY
BEGIN CATCH
~print error message
END CATCH
-- for server 2..
BEGIN TRY
~Insert table
~Select openquery
END TRY
BEGIN CATCH
~print error message
END CATCH
...
-- for server n..
BEGIN TRY
~Insert table
~Select openquery
END TRY
BEGIN CATCH
~print error message
END CATCH
END
as one of the servers that is supposed to be copied from *when it is up* is currently NOT up, t-sql refuses to execute my script and wont even CREATE the stored proc.
I thought the whole idea of TRY~CATCH was so that when a piece of code fails to execute correctly you can handle the failure cleanly without the proc exiting before it has finished...
Am I doing something wrong?
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 1, 2012 at 4:59 am
If you rewrite your proc slightly to use dynamic SQL in the non-validating bits, you can get round the validation issues.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2012 at 5:05 am
Yeah I guess but I hate Dynamic SQL. there has got to be a better way of doing this - are there any hints you can add to stop it validating?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 1, 2012 at 5:16 am
SSIS an option?
February 1, 2012 at 5:21 am
this is what i used when i came across the problem
http://msdn.microsoft.com/en-us/library/ms179296.aspx
specifically,
You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation
by executing the error-generating code in a separate batch within the TRY block.
For example, you do this by placing the code in a stored procedure. This allows TRY…CATCH to catch
the
error at a higher level of execution than the error occurrence. For example, the following code shows a stored procedure that generates an object name resolution error. The batch that
contains the TRY…CATCH construct is executing at a higher level than the stored procedure; and the error, which occurs at a lower level, is caught.
USE AdventureWorks2008R2;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
DROP PROCEDURE usp_MyError;
GO
CREATE PROCEDURE usp_MyError
AS
-- This SELECT statement will generate
-- an object name resolution error.
SELECT * FROM NonExistentTable;
GO
BEGIN TRY
-- Run the stored procedure.
EXECUTE usp_MyError;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
February 1, 2012 at 6:39 am
thanks david, that looks like it's going to be the easiest way of sorting it out. except when I try to create the stored procedure to call... that wil fail too!
unless...... I temporarily change the linked server to a machine that DOES exist whilst I create the sproc then change it back again afterwards...... a cunning plan.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply