Connecting to Linked Servers

I know this is an easy one for most of you but this is new territory for me.

I am trying to link a production application database to an existing SMS database.

Both of these databases are SQL 2000. I have sa access to both of them.

I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.

For simplicity I will define them both.

Application Database we will call “A-Database”

SMS Database we will call “B-Database”

I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab.

In the Security Tab I selected the “Be made using the security context” and used “sa” for the Remote login and the CORRECT sa password for the With password option.

The server options tab remain the same with ”RPC, RPC Out, & Use Remote Collation” selected.

It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?

Thanks,

Gene Godsey

In the properties of the Linked Servers I am looking at trying to change my connection by selecting the general tab and selecting the option for Other Data Sources. I have selected the Microsoft OLE DB Provider for SQL Server and then I have to provide data for 3 fields.

In the Product Name field I just put the database name I want to use from the server. In the Data Source name I insert serverName\DataSource name.

The Privider string has me stumped.

The location is grayed out but I believe that this will open up when I have a provider name. I do not know what goes there either.

It says that the catelog name is optional. I am not worried about that field.

Any suggestions.

Thanks,

|||Hi Geneway,

Im not sure why you are putting the DB name in the Product Name? I dont understand that bit. Where is it saying Product Name?

Your datasource would be the server name.

Your catalog would be the database.

Keep us updated.

Barry Andrew (Pace)|||

In the enterprise manager of SQL 2000 go to the security node. Under this you would go to the linked servers. If there is a linked server I would right click and select the properties link.

When the properties dialog box opens there are 3 tabs. General/Security/Server Options.

I select the General tab and the\n I will select the “Other Data Source” option.

From here I would select the “Microsoft OLE DB Provider for SQL Server” Dropdown. At this point there are 3 text boxes that are available to insert data.

1: Product name:

2: Data Source:

3: Provider String:

I must have this information incorrect because I cannot see tables or view from this.

I have tried to select the SQL Server option but I cannot control what database that is selected in that option.

Any suggestions,

Thanks,

|||Hi,

Do as you have been doing and use the following;

1) SQL Server - this will do fine
2) NetworkNameHere!!! - IE the server name the sql database is running on
3) a) Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
b)Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

A and B are trusted and standard authentication methods respectively

You may also find the website; www.connectionstrings.com useful.

hth

Pace|||

I figured it out!

YEA!!!!!

I created an datareader account in the SMS database. I selected the connect to SQL Server option button in the general tab location.

Under security I selected the sa account but the remopte user was the datareader account that I created in the SMS Database. It was really easy but this is not very well documented.