views:

119

answers:

2

Hi all,

I have two servers, the first one with SQL Server 2005 and the second one with SQL Server 2000. I want to insert data from the 2005 to the 2000 and I want to do it unsync (without distributed transactions because "save transaction" are used).

Once the information is inserted in the tables of the 2000 server, some instead-of triggers are fired to process this information.

In that scenario I decided to use Service Broker. So I have a Stored Procedure to insert information from one server to the other and it works perfectly.

But when I call this procedure from the target queue process message procedure it fails, and I don't know why!!

Also, I know it works because when I use the same structure (queues & stored procedures) to copy form one database to another on the same SQL 2005 server.

So it fails only between machines, anyone knows why or how to get more information about the cause of the failure? Or how to insert data unsync (I can't to use the SQL Agent because I want insert the information more frequently than 1 minute).

A: 

Consider using a linked server instead of service broker. With a linked server, you can:

insert into LinkedServer.The2000Db.dbo.The2000Table
(col1, col2, col3)
select col1, col2, col3
from The2005Table
Andomar
Yes, of course, this Stored Procedure used to insert information from one server to the other has insert into like that, using Linked Servers.
Alex
@Alex: How is the security configured on the linked server? If it's "using the login's current security context", try replacing it with a fixed context
Andomar
+1  A: 

The usual issue is that the SSB procedure uses WAITFOR and WAITFOR is incompatible with distributed transactions. The only solution is to get rid of the WAITFOR(RECEIVE) and use a ordinary RECEIVE instead.

Remus Rusanu
@Remus: I don't use a explicit transaction (distributed or not), not even to do the WAITFOR(RECEIVE). I just use a insert into a linked server, but maybe there is a implicit distributed transaction on it. I will try it!!
Alex
The INSERT into a linked server is a distributed transaction. Because WAITFOR creates a save point internally, is incompatible with distributed transacitons, hence with inserts over a linked server.
Remus Rusanu