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.

Connecting to database for bulk insertion

HI friends,

I am retrieving oracle data(linked server) to my local sql server 2005. Initially I want to have duplicate copy of the oracle data to my local server. I have created linked server and inserting to my local table(exact replica). This will run for every 2 minutes(as per my client requirement) to get newly added records.

But, I want to retrieve specific columns from the replicated table and insert into other local tables. i have written a SP to do this. some columns are storing in other tables to maintain normalization. this should also run for every two minutes.

I have written a class (c#) to retrieve replica table and passing parameters to SP and inserting to my local tables in normalized form.

But this is taking 10 minutes to complete my process to insert 1500 records. but my client insist to reduce the speed to run for every 2 mins.

is it correct way wat i am doing? or any other solution is there?

pls suggest me.

thanks in advance

You have supplied very little technical info on exactly how you are accomplishing this task. Please elaborate.

I will tell you, though, if you are pulling a bunch of queried records out of the database into a c# collection and inserting them back into the database one at a time, you ARE going about it the wrong way. :)

You should be writing this code in T-SQL or SSIS, not C#.

You should definitely make use of insert statements that insert multiple records at a time. An example is: insert into some_table (col1,col2,col3) select col1a, col2a, col3a from another_table.

Connecting to AS400

Using linked servers in SQL 2000 for some reason changes the commit mode
when doing an INSERT or UPDATE. That means that journaling needs to be
active on the tables that I am access on the 400. The 400 people have
said no. If I do a straight ODBC connection, not through linked
servers, everything works flawlessly.
From SQL server, what is the best means of utilizing ODBC to issue an
INSERT to our 400 WITHOUT using Linked Servers?
DTS works, but doesnt seem to be the answer. Is it possible to open an
ODBC connection in a stored procedure?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!See "Openrowset" in Books Online.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Will that type of connection support distributed transactions (commit, rollb
ack)?

Connecting to a linked server with ODBC

I'm currently trying to access data from a Cache DB using MSSQL. I have
linked the Cache server through an ODBC connection. I can see in the Linked
Server
expansion all the tables in Cache for the File(?) I want to access.

Here is my problem: Normally to access a linked sever I would do the
following:

select *
from ServerName.DatabaseName.dbo.TableName

I have tried

select *
from ServerName.DSN.dbo.TableName

select *
from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)

None have worked (error no such object...)

What is the syntax to select data on a linked sever via an ODBC connection?

In the Linked server set up I have also check the RPC and RPC Out options...
My ultimate goal is join tables in Cache and MSSQL into MSSQL.

Any help would be greatly appreciated!

Thanks,

-p"Pippen" <123@.hotmail.com> wrote in message news:<b8iWb.2520$_44.6454@.attbi_s52>...
> I'm currently trying to access data from a Cache DB using MSSQL. I have
> linked the Cache server through an ODBC connection. I can see in the Linked
> Server
> expansion all the tables in Cache for the File(?) I want to access.
> Here is my problem: Normally to access a linked sever I would do the
> following:
> select *
> from ServerName.DatabaseName.dbo.TableName
> I have tried
> select *
> from ServerName.DSN.dbo.TableName
> select *
> from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> None have worked (error no such object...)
> What is the syntax to select data on a linked sever via an ODBC connection?
> In the Linked server set up I have also check the RPC and RPC Out options...
> My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> Any help would be greatly appreciated!
> Thanks,
> -p

I don't know anything about Cache DB, but not all linked servers
support or require all 4 parts of the name. Have you tried these two
alternatives?

select * from LinkedServerName..SQLUser.TableName
select * from LinkedServerName...TableName

Simon|||Your first example works. Thanks for the help.

-p

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402110305.707515bb@.posting.google.c om...
> "Pippen" <123@.hotmail.com> wrote in message
news:<b8iWb.2520$_44.6454@.attbi_s52>...
> > I'm currently trying to access data from a Cache DB using MSSQL. I have
> > linked the Cache server through an ODBC connection. I can see in the
Linked
> > Server
> > expansion all the tables in Cache for the File(?) I want to access.
> > Here is my problem: Normally to access a linked sever I would do the
> > following:
> > select *
> > from ServerName.DatabaseName.dbo.TableName
> > I have tried
> > select *
> > from ServerName.DSN.dbo.TableName
> > select *
> > from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> > None have worked (error no such object...)
> > What is the syntax to select data on a linked sever via an ODBC
connection?
> > In the Linked server set up I have also check the RPC and RPC Out
options...
> > My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> > Any help would be greatly appreciated!
> > Thanks,
> > -p
> I don't know anything about Cache DB, but not all linked servers
> support or require all 4 parts of the name. Have you tried these two
> alternatives?
> select * from LinkedServerName..SQLUser.TableName
> select * from LinkedServerName...TableName
> Simon

connecting to a linked server using VS2005

hi,

i've currently created a linked server using sql server management studio express (it connects to an oracle database to draw data), i can query it all fine using the query analyzer with sql server etc.

how can i start using the linked server data with visual studio 2005? i can't seem to find the linked server anywhere inside the server explorer, am i suppose to import it / connect to it any specific way so i can start using the data retreived from the linked server?

thanks in advance!

create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,

Hope this helps

|||

Ramzi.Aynati:

create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,

Hope this helps

thanks, but how do i create a view?

|||

if you would like to hit linked server in you code just add database name to each object your would like to access. You can run query on server to which you are connected and server will forward your query to linked server. for example if your SQLServer2 is linked server in SQlServer1 you can run query like this om SQLServer1 ( if user you used to link servers have rights to objects in your query)

select * from [SQLserver2].[database].dbo.[tablename]

you can connect results from multiple servers

select * from [SQLserver2].[database].dbo.[tablename]
left join [SQLserver1].[database1].dbo.[tablename1]
ON tablename.aa=tablename1.bb

If you only use linked server in your query you can use OpenQuery which will process data on the linked server and returns only results to you.

SELECT * from OPENQUERY([SQLServer2,' select * from [database].dbo.[tablename]')

Thanks

|||

In your SQL, go to ur query analyzer

and type

Create View View_name as

SELECT *
FROM TEST1.Northwind.dbo.Orders

//where test is ur linked server

This is it, u got urself a view accessible from ur server explorer

|||

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

|||

jpazgier:

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

thanks, i might try both options.. would you happen to know of any tutorials that would put me to the right direction in creating a stored procedure to do this? thanks

|||

Dear Frank

As long as the table ur querying is less than 10000 records, u wont feel a pinch, the difference will be in milli seconds,

The topic jpazgier is raising is for really advanced SQL programming and for huge amount of records over 1 million

|||

thanks, have given it a go and i get this error message, any idea what i'm doing wrong?

OLE DB error trace [Non-interface error].

Msg 7312, Level 16, State 1, Procedure OracleComDir, Line 3

Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

|||nevermind, worked it out, thanks :)|||

Dear Frank

Please dont forget to mark the post that helped u the most as answered for sake of future readers

Glad to be of help my friend

|||

jpazgier:

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

turns out there's about 500,000 records in the database, so results are generating really slow.

i've been attempting to create a stored procedure, but it's still taking 20 odd seconds to bring up the results, here's a basic summary of my stored procedure:

ALTER PROCEDURE GetOracleData
AS

SELECT
oracle_table1.field1,
oracle_table1.field2,
oracle_table2.field1,
oracle_table2.field2

from
OracleCD..oracle_database1.table1,
OracleCD..oracle_database2.table2

WHERE
(oracle_table1.field1 = oracle_table2.field1) AND
(oracle_table1.field2 = oracle_table2.field2)

OracleCD = name of the linked server i created, i'm thinking the fact that i'm calling the data from the linked server is the reason why it's taking soo long for the data to load? what's the best way to call upon data from different databases in a stored procedure?

thanks in advance!

Connecting through proxy

Hi,
We are having problem with Linked Server connection through proxy in our
network. The port 1433 is open. Everything was fine before using proxy. Is
there any other configuration required?
Any help would be greatly appreciated,
Leila
Maybe: http://support.microsoft.com/kb/216415
Also, if you are using a dynamic port for SQL-Server or if you have more
than one instance of sql-server, the port 1434 and some of the following
ports must also be opened.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Leila" <Leilas@.hotpop.com> wrote in message
news:u0$T%231YlHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are having problem with Linked Server connection through proxy in our
> network. The port 1433 is open. Everything was fine before using proxy. Is
> there any other configuration required?
> Any help would be greatly appreciated,
> Leila
>

Connecting through proxy

Hi,
We are having problem with Linked Server connection through proxy in our
network. The port 1433 is open. Everything was fine before using proxy. Is
there any other configuration required?
Any help would be greatly appreciated,
LeilaSee:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932
--
Cheers,
Stefan Delmarco
http://www.fotia.co.uk|||Maybe: http://support.microsoft.com/kb/216415
Also, if you are using a dynamic port for SQL-Server or if you have more
than one instance of sql-server, the port 1434 and some of the following
ports must also be opened.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Leila" <Leilas@.hotpop.com> wrote in message
news:u0$T%231YlHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are having problem with Linked Server connection through proxy in our
> network. The port 1433 is open. Everything was fine before using proxy. Is
> there any other configuration required?
> Any help would be greatly appreciated,
> Leila
>

Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?

Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/400 system? Can you do it thru setting up a Linked Server?
Thanks,
Warren
Thanks Sue I will try this and let you know!
Thanks Again,
Warren

Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?

Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/4
00 system? Can you do it thru setting up a Linked Server?
Thanks,
WarrenYes and yes.
Install Client Access on the SQL Server box and the
configure the linked server. For data source, use the IP
address of the AS400. For provider string, you need to
include the library you are using, connect timeout setting
and code page. There is some documentation for the settings
in the Client Access help files. You'd set the provider
string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Tue, 6 Apr 2004 07:51:06 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/
400 system? Can you do it thru setting up a Linked Server?
>Thanks,
>Warren|||Thanks Sue I will try this and let you know!
Thanks Again,
Warren