Linked Server Browse Locally - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server Browse Locally

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2007-07-05 : 07:10:46

Hi all, I have a linked server from SQL2005 to DB2 using Micorosoft OLE DB Provider For ODBC Drivers.
If I run a select statement Using the four-part name ie:
Select * from linked_server_name.catalog.schema.object_name
Then my records get returned.
However if I open the query designer on this code the table is blank
I also cannot browse to linked servertables just the local tables.
My Question is..
If I change how I have my Linked server setup and use a different provider say IBMDA400 OLE Provider. will this enable me to use the designer on the linked server?
Is there a provider I use so that these tables are seen as local?
Also I think the way I currently have the connection can be made quicker if I use OLE instaed.
Any help Please?
Ray..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-05 : 11:45:06

No, you can't browse to linked server in query designer.

rwaldron
Posting Yak Master

131 Posts

Posted - 2007-07-05 : 20:26:44

Apparently if you read this from microsoft the it should work ?
http://msdn2.microsoft.com/en-us/library/aa225987(SQL.80).aspx#dvmscworkingwithtablesfromdifferentdatasources

I have a linked server in SQL 2007 form SQL to DB2.(IBM)

If I write a distributed query from SQL to DB2 I get mt data returned.
ie:
select *
from openquery(Movex_Extranet,'Select * From mvxadta.ooline')

If I write a Query from SQL to DB2 using the 4-part naming convention linkserver.catalog.schema.object
then I also get my data returned.
ie:
select *
from MOVEX_EXTRANET.RCHASE5C.MVXADTA.OOLINE

My problem is with the Query Designer in SQL2007.

When I right click the first lot of code and select "Design query in editor"
The Designer GUI opens up and displays my table in graphical form with all columns selected - Great.

But When I repeat this for the code that uses the 4 part naming
The Designer opens up and displays the table but with no columns viewable or available for me to select ??

Anyone know what might be causing this to happen.
Is it a bug in SQL 2007 ??

I have tried linking the server using 2 types of providers ( One from Microsoft & one from IBM) but each time
I still cannot see or choose columns in the designer using the 4part naming convention.
The 2 providers I tried where.
Microsoft OLE DB for ODBC Drivers.
IBM DB2 UDB for Iseries IBMDA400 OLE DB Provider

Any help greatly appretiated as I would really love to use the designer using 4part naming convention.

Ray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources