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!