Linked Server, show tables in SQL Server Management Studio

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Linked Server, show tables in SQL Server...

Linked Server, show tables in SQL Server Management Studio

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew.Buis

Posted Friday, August 15, 2008 7:31 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 12:50 PM
Points: 11, Visits: 133

I have a Linked Server created from a DB2 ODBC using StarSQL driver. I was hoping to view all the databases/tables/columns like I do with other normal SQL servers... tree view with database->tables->columns etc. I can EXEC sp_tables_ex DB2LinkedServer, and can select data without issues, but trying to write queries "blind" is really annoying. Is there any way that I can get this to work? The connection works fine opening in Excel for example, and shows the tables, but not in the SQL Server Management Studio..

Post #553460

Chad Crawford

Posted Friday, August 15, 2008 8:43 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:31 PM
Points: 2,558, Visits: 17,421

I don't know anything about the StarSQL driver, but I have seen similar issues using DTS against ODBC (specifically Firebird). Sometimes it seemed to work and sometimes it didn't, but mostly it didn't and I wasn't ever able to figure out how I got it to work the 1 or 2 times it did come through. I resigned myself to either having a printed copy of the model nearby or having one tool up on one monitor and the other in a 2nd.

Chad

Post #553548

Andrew.Buis

Posted Monday, August 18, 2008 5:48 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 12:50 PM
Points: 11, Visits: 133

So is this standard for linked servers? Or just non-microsoft sql servers?

[Edit] I am a programmer, not a DBA, just happens that I'm the only one here that has a decent amount of experience with SQL. I am writing a web app to replace an Access front-end. The DB2 data is dumped to our local SQL server, but changes are in the works there, so I want to query the DB2 data and update the local db I am creating.

Post #554230

Chad Crawford

Posted Monday, August 18, 2008 9:56 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:31 PM
Points: 2,558, Visits: 17,421

It isn't standard for linked servers because you can get the metadata from some. I really don't know what makes the difference - there may be some way to structure the connection string in such a way that it either enables or knows how to get the metadata. I can share my experience though - with the Firebird driver I was using I spent a lot of time playing with it and wasn't able to get it to work whether it was using the driver direction or hopping through a system or user DSN. I finally decided that I would end up spending more time trying to tweak connection strings than it would take to look everything up and moved on.

I know this isn't helpful, but at least you know that someone shared your pain.

Chad

Post #554729

Ed Zann

Posted Tuesday, August 19, 2008 7:08 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, June 14, 2013 12:20 PM
Points: 473, Visits: 1,080

Not sure what your DB2 lives on - but in my case, we have DB2 on an iSeries(AS400). I use the iSeries Access ODBC driver. In SMS 2005 I can drill down to the DB2 table names, but I can not get to the field name level.

Post #554979

jtalbert

Posted Friday, May 08, 2009 4:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 13, 2010 10:49 AM
Points: 8, Visits: 12

I'm banging my head trying to establish a linked server to query. I'm using SQL Server Management Studio Express and have an ODBC connection:
OBDC System DSN setup using: iSeries Access for Windows ODBC data source
DSN name = AS400
SQL default library = M800DAT200 (using SQL naming convention)

Would appreciate some insight.

Thanks.

Post #713417

« Prev Topic | Next Topic »

Permissions