Link Server

Question

Link Server

Paul Sp

SSCrazy

Points: 2314
More actions
May 11, 2005 at 5:45 am

#164430

Hi
I am having trouble linking to another SQL Server on the same network in Enterprise Manager. The name of the server hosting SQL Server 2000 is MARION, and the SQL Server itself is just recognised in properties as Local. The database needed is called DOCUMENTS.
Provider Name: I have as Microsoft OLE DB Provider for SQL Server.
What will I need for:
Product Name
Data Source
Provider String
Thanks
Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Perry Citrowske Ten Centuries Points: 1243 More actions · Answer 1

I'm not sure how being logged on as "(local)" affects the configuration, but for our SQL server links, when I check the radio button for SQL server, the option for the ODBC paramters you are speaking of, disappear. Then in the next tab (security), I insert a username/password under the radio button "Be made under this security context". The username/password is the same on both SQL servers. This works great for us. When we link our SQL servers together, it's usually for read-only purposes. There are some more secure and complicated configurations available. You may have to grab your latest SQL book to view other types of configurations.

Paul Sp SSCrazy Points: 2314 More actions · Answer 2

Thanks, Perry

I'm getting data fatigue. I am normally linking to an Oracle Server not SQL Server.

Thanks again

Paul

Judith Webber Ten Centuries Points: 1027 More actions · Answer 3

Paul,

Just an FYI....I use the Provider for my linked servers and give the link a generic name. I do this so when I do cross server joins I do not have to change server names as I migrate stored procedures through my environments (Dev, UAT, Prod). It was becoming a nightmare to migrate procs since the server names would have to change for each environment so I was, in effect, moving untested code to production and if we missed changing a server name we had the potiential of a stored procedure in one environment impacting another environments data (there are users that have permissions in all environments....not ideal but it wasn't my call). If you choose to go down this path the key field would be the Datasource which is the name of the server itself.

Judie

Paul Sp SSCrazy Points: 2314 More actions · Answer 4

Thanks, Judie

This is an interesting comment - which we will have to think through. I can recognise the implications.

Paul