Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server, show tables in SQL Server Management Studio Expand / Collapse
Author
Message
Posted Friday, August 15, 2008 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, August 15, 2008 8:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 2,607, Visits: 17,910
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
Posted Monday, August 18, 2008 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, August 18, 2008 9:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 2,607, Visits: 17,910
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
Posted Tuesday, August 19, 2008 7:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 486, Visits: 1,210
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
Posted Friday, May 8, 2009 4:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse