January 18, 2009 at 1:20 am
Hello,
I have SQL Server 2005 Express (Server1) at work and SQL Server 2005 Express (Server2) on my laptop. From laptop, I can connect to Server 1 with connectionstring ip,port\instanceName (through connect object explorer).
I have the same databases on both servers and because I have limited internet connection, I work mainly on my local server (Server2). But when I connect to internet, I'd like to synchronize both databases (with sql statements).
So - how can I connect to remote server from transact sql?
Thanks
January 18, 2009 at 9:12 am
T-SQL doesn't have a way to do a conneciton. You can use T-SQL to set up a Linked server, which then you can query through, but you can't open directly a connection with T-SQL.
actually, perhaps this would work with OPENQUERY, but I wouldn't recommend it.
Are you trying to synchronize two databases or something else?
January 18, 2009 at 1:20 pm
Yes, I am trying to synchronize two databases (just a couple of tables). It would be great if I could do this just with running transact sql on client.
So I can create linked server within transact sql and then connect to remote server? Could you please be more specific how to do this?
January 18, 2009 at 1:40 pm
Create your second server as a linked server on the first. (See http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx for more information)
Then, you can reference the other server and the current one in the same query.
For example:
UPDATE Table1
SET Value1 = Table2.Something,
Value2 = Table2.Something
FROM Table1
INNER JOIN OtherDatabaseServer.DatabaseName.dbo.Table2 Table2
WHERE SomeCriteria = True
January 20, 2009 at 1:22 pm
Thanks, it worked with linked server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply