August 23, 2010 at 6:57 pm
Hi every one,
I have a table which will transfer the rows into oracle linked server table.till now we did that manually but now we want to make it automatic,Oracle guys created a table for us to indicate when the transfer started and completed .
Oracle Table columns:
ID int,
transfer_start_time datetime,
transfer_completion_time datetime,
status varchar(10)
All i need to do is insert a row with status 'transfer started' before i start another procedure which will load data from sql server to oracle this takes 3 hours time because of the network connections,and after the procedure completes i need to update the inserted row status saying that the 'transfer completed'.....i have done this part.
But my boss wants me to change the column status to 'Transfer Failed' when we have network problems ,for that i have tried simple TRY/CATCH exception handling and for testing i have started executing the procedure and disconnected the network to see it works ,it didn't work for me and i have a question how will sql server exception handling work when we loose the network connections and have network problems?
Insert into ORACLETABLE (ID,STATUS,TRANSFER_START_TIME)
values(@ID,'Transfer Started',getdate())
--Handling the Exceptions using Try/Catch
Begin Try
-- transfer data to oracle
exec poracletransfer @id = @ID
-- runtime approximately 3.0 hours
End Try
Begin Catch
Update ORACLE TABLE
set
STATUS='Transfer Failed',
TRANSFER_COMPLETION_TIME=getdate()
where ID=@ID
End catch
can anyone please help me?
August 24, 2010 at 8:53 am
When you loose network connectivity, you won't be able to get to the Oracle DB to update it. You won't be able to get anywhere on the network. Maybe you could flash a message on the screen and then have support go over and manually update Oracle :-D. Just kiddin about the manual update. Maybe when the connectivity comes back on and the connection is made again, it could check to see if the last transmission was successful. If not, then it could update it to failed before continuing with the current transmission, but this could cause a lot of downstream issues if the data is realiant on the previous transmission.
Another way is to kick off a process on Oracle that monitors the transmission. If it's gone for x amount of time and the table hasn't been updated, then the process on Oracle could update the table
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2010 at 9:05 am
Can you keep your status table local? Or additionally keep a local status table? How can you write to your remote status table if you are having network problems? TRY/CATCH may not help you if there are network problems.
How about?
-Write 'transfer started ' to a local table
-Write 'transfer started' to your remote table
-begin transfer
-Write 'transfer ended' remotely
-Write 'transfer ended' to your locally
Then schedule something after a period of time when you think the job is complete.
-Check the remote table for 'transfer ended'
-If it doesn't exist check the local table for your 'transfer ended'
-If it doesn't exist locally then send an alert
August 30, 2010 at 8:12 am
Hi,
I am fine with the oracle table and don't need to update Status Column to "Transfer Failed" when we loose the network because oracle process is going to fetch the data only when it sees the status as "Transfer Completed" ..which is not going to happen unless it sees the same number of rows.
The exception worked when we lost the connection to Oracle linked server and updated the status to "Transfer Failed",which is good .and the whole process is scheduled as a job and when we loose the connection to oracle linked server it is throwing an exception but the job says 'it has completed the job successfully '.
Is there any way i can capture the exception when the process fails in a job ..rather than telling me 'the job has completed successfully'.
August 31, 2010 at 1:05 pm
I got it by calling a stored procedure to send email when the process fails.
Hope this link works for those who don't know how to do it..
http://www.dotnetspider.com/resources/19638-Sending-email-through-sql-server-stored-procedure.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply