Connect To DIFFERENT Server 1

[fhlee]

Hi,
Hope someone can help me with this... been struggling with this urgent (duh!) request...

Microsoft SQL
----------------

I have 2 SQL server (Server A & Server B). In the 2 servers, there is 1 database (MyData). Inside the MyData, there is 1 table (Pictures). The Pictures table contain a field called (LastChangedDateTime).

What happen is that the users will update the pictures from ServerA. But then the webserver will display the pictures fomr ServerB.

I'm trying to write a STORED PROCEDURE to synchronise the 2 tables. Normally, it's a push from ServerA to ServerB. However, I need to push only changed (LastChangeDateTime different) records.

What I'm pulling my hair over is the very basic... How do I connect to 2 different sql host in T-SQL? Once I have established connection, I am sure I can do it. But it seems so strange that I cannot connect to 2 different hosts.

I'm sure this can be done. Can someone help me please? Really pulling my hair... found something like CONNECT TO, but it doesn't seems to work on the SQL Query Analyzer... It says something like:
CONNECT TO {[server_name.]database_name} [AS connection_name] USER [login[.password] | $integrated]

But I can't seems to get this to run on SQL Query Analyzer? Please help! Thanks.

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[onpnt]

Add a linked server entry for the minimum required communications between the 2 servers you need. Meaning if you need to only talk to ServerB from ServerA then ServerA only needs a linked server entry to ServerB.

Then you refer to the linked server entry as any other db.table but by means of the instance name in the call.

e.g.
[instance].[database].[owner].

Note: There are choices to use when creating a linked server entry for what credentials are passed to gain access to the linked server entry. NEVER use the system administrator account for this need. To the point, don't use sa. Create a account for this purpose or use windows credentials when possible. You can impersonate an account and or specify directly in which context you want to use

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[fhlee]

Hi onPnt,
Thanks so much for replying. Through the keyword 'Linked Server' you provided, I found the solution I needed: OPENROWSET.

This is just to document this post & for future programmers who is asking the same question:

Title: SQL query to a different server / host.

SELECT ServerA.*
FROM OPENROWSET('SQLOLEDB','<server name>[port]';'<userName>';'<password>',
'SELECT imgID, imgLastChangedDateTime FROM MyPictureData')
AS ServerA


Thanks again. cheers.

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[gmmastros]

No.

OpenRowset is not the same thing as linked server. In my opinion, OpenRowset is fine for '1 time things', but if you are going to continously use the other server then you should set up a linked server.

Here are a couple articles that you should read.

http://www.databasejournal.com/features/mssql/article.php/3331881

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom