What is the best way to have two inter-company SQL Servers connect?

[MikeBronner]

Hi All!

I have the following situation:

SQL Server 2000 server at company A wants to send data to a SQL Server 2000 server at company B. Company B wants to send an acknowledgment back to the SQL server at company A once the received data is processed.

What is the best way to do this? I was hoping to find a solution along the SOAP / XML lines, so that we could send XML data to a certain PORT on the SQL Server, where it would automatically consume it. (Of course along with validation provided be XSD.)

I believe that setting this up as Linked Servers is out of the question due to security restrictions.

What are your thoughts on the best method of achieving direct server-to-server communication?

Best Regards!
Mike

Take Care,
Mike

 

[mrdenny]

If you had SQL Server 2005 then service broker would be the way to go. On SQL 2000 there isn't much available out of the box. You'll need to write some sort of service to listen for the data and to send the responce, and they will need to do the same.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com