October 16, 2007 at 2:48 am
We have branches (i think more or less 50) which have a table named holiday. I was asked to synchronized the holiday dates of all the branches if a new holiday will be announced or if the holiday date was moved. I thought of ways where i can do the job and these ideas came into my mind.
1. Remotely add/update dates in the holiday table - I think this will consumes time if i will open each of the branch serves then their databases up to the table to add/update the date inside (we have almost 50 branches)
2. DTS - yeah i can change our main server's holiday table and dts the table to all the holiday table in the branches (I think like no. 1 this is also time consuming)
3. Then here comes linked server - Setup a linked server to all the instances of the branches (including linkedserverlogins). Use CURSOR to add/update the holiday table of the branches using 1 query (not opening all the branch servers and not use DTS in each of the holiday table in the branches)
My questions are...
1. Am i right about choosing linked server? Any other faster way on doing this?
2. Other than SELECT statement, can i use UPDATE in linked servers?
3. Is this correct? what are the values needed?
USE master
GO
EXEC sp_addlinkedserver
@server = ‘',--I can create my own linkservername?
@srvproduct = '',--what is the value if I'm using sql 2000?
@provider = '',--what should i use here?
@datasrc = '' --what should i use here?
GO
"-=Still Learning=-"
Lester Policarpio
October 16, 2007 at 7:56 pm
Ok after reading some articles about adding linked servers this is what i did. We have a test server whose network is 10.1.1.1 inside this server is a database named lester which has a table named table1. I created a linked server by doing this procedure (in EM):
1. RIght click linked server>New linked server
2. Linked Server name = Lester
3. Provider name = Microsoft OLE DB Provider for SQL (in Drop Down Menu)
4. Product Name = SQL Server
5. Data Source = 10.1.1.1 --is this right???? its the IP of the server
6. Provider String = SQLOLEDB
In the Security Tab
1. Local Login = lester
2. Impersonate = checked the check box
3. In the radio button i chose Be made using the login's current security context.
NOTE:
The server 10.1.1.1 has server login named lester
The server 10.1.1.1 with database = lester has a database user lester
when i used query analyzer (in my server)
SELECT * FROM Linkedservername.databasename.dbo.tablename
which is = SELECT * FROM lester.lester.dbo.table1
it gave me an error message stating:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Did i missed something? am i wrong in creating a linked server?? my local server is MSSQL 2000 and the 10.1.1.1 server is also MSSQL 2000
"-=Still Learning=-"
Lester Policarpio
October 16, 2007 at 9:19 pm
Ok problem solved all i did is to change the server's IP with its computer name. Then on the security tab i choose Be made using this security content and add a remote login and password which also exists in the server to be linked
"-=Still Learning=-"
Lester Policarpio
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply