Error Handling Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error Handling Question

Author

Topic

otto
Starting Member

21 Posts

Posted - 2003-01-13 : 17:47:47

I'm working on a trigger that will update a Linked Server. My current problem is that if the remote server isn't available, my insert, update, or delete on the local server will fail. So, I figured I could trap the error and move on. Doesn't look like that's so easy.

So, to simplify things, I wrote a test stored procedure to see if the SP could select some data from my remote server. I figured if it could select data, the server is up and I can run my other stored procedures. The problem I'm having is I can't continue with my coding if the server is down. It seems like something else is doing error handling outside of the stuff I've written.

Is there any function within SQL to resume after error trapping? Attached is a sample SP I wrote to test the remote server.

------------------------------------------------------
CREATE PROCEDURE sp_MyTestSP
AS

SET XACT_ABORT OFF

SELECT TOP 1 FieldName FROM Server.Database.dbo.MyTable
IF @@ERROR <> 0 -- Error connecting to the server...
BEGIN
print 'Code here'
END
ELSE IF @@ERROR = 0 -- No Error Found
BEGIN
print 'Code here'
END

SET XACT_ABORT ON
GO

otto
Starting Member

21 Posts

Posted - 2003-01-14 : 19:13:29

I'm not sure if anyone is interested, but after a days worth of pulling out my hair, I found a work-around solution.

It doesn't seem like I can trap this error. Every time my Linked Server is off, I get error messages that I cannot avoid. It would seem like a good idea if we had some sort of simple feature that said, "Hey SQL, are you ready for a remote connection?" and it just said yes or no.

So, I ended up creating a stored procedure that calls Master.dbo.xp_cmdshell that then uses ISQL to test the connection to the remote server. How messed up is that?

Did I miss something? Is there something like ping for SQL?

Thanks,

Otto

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-14 : 19:27:36

you shouldn't be building a solution like this... there is something wrong with your architecture or maybe just your strategy for the application if a required resource is not available... why wouldn't the box be available? what are you doing in the trigger? are you trying to replicate data? there is automation built into the database server that could handle that without using triggers ... you are making the user wait xx number of seconds longer than they should have to ... triggers happen synchronously ... even though that makes me want asynchronous triggers (that would be nifty) ...

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-14 : 19:43:48

Yeah, I think yer going about this the wrong way. I think you need to look into replication, or possibly log shipping.

Can you explain exactly what yer trying to accomplish? It sounds like you want to insert a row in a different database on a different server when a record is inserted in the "local" server.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

otto
Starting Member

21 Posts

Posted - 2003-01-14 : 23:02:13

I have a couple reasons why replication won't work.

1- I can in no way shape or form change the current configuration of the SQL Servers -- it is currently run as Local System (ie, replication won't work, it needs a NT user account)

2- I have 3 remote servers that need to update 2 centeralized servers -- the remote servers are firewalled and we cannot make any more changes to their configuration

3- I highly doubt we'd come anywhere near being able to get file sharing to work with the current configuration of the servers (which, from my understanding is a requirement for replication to work)

I never really explained what I was trying to do, because I felt I was asking a pretty specific question. Judging by the responses, the only way for me to get a specific answer is to elaborate more.

I have 3 servers that get updated 24/7 with account information. Specific data needs to be feed to 2 servers (under my control) with the newly inserted, updated, or deleted data. My 2 servers will act as mirrors of one another, accepting the data from the 3 other servers. If ServerA fails, my ServerB will take place and vice versa.

So, I can't use replication. I *must* allow a transaction to fail on one or both of my servers.

How is my approach wrong?

Understand, I know this isn't the best solution, but I don't have a mallet big enough to make this client understand everything they've done up to this point is terrible and they are continuing to do so.

Otto

Ps. The other post is related to this one.

Pss. Will anyone ever answer my question about the trigger handling multiple rows?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-14 : 23:51:53

quote:
Pss. Will anyone ever answer my question about the trigger handling multiple rows?

Otto-

I said in the other thread that there was an example in Books Online that uses the inserted table to update another table based on the affected rows (under the entry for "CREATE TRIGGER"). It is far more beneficial for you to read the entire entry so you can see the context in which it is used.

I will also make the same suggestion I made earlier about putting the affected rows into a local audit table, and then creating a scheduled job that runs periodically to synchronize the other tables from that audit table. It is NOT a good idea to attempt to have the trigger update the linked servers directly, ESPECIALLY since you can't guarantee that the linked server is available all the time. Trying to do so is the fastest way to ENSURE that the data is out of sync and cannot be sync'd later on.

And regardless of what your client is doing, you've had three different people recommend replication to you. It's very frustrating to offer the best advice we can give and have someone come back and say "I can't do it that way, I have to use the other, messy way" without offering an explanation until the last minute. And your client is not an expert on database design, otherwise they wouldn't have hired you. If you want to drive yourself crazy, use the trigger to synchronize data. You'll never be able to completely guarantee that all of the data is perfectly in sync. Use this as an argument to your client that if they want this feature, replication is the way to go, period: no triggers, no errors, no data loss, no effort, no problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources