InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:27:20 GMT20http://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxI'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.Fri, 08 May 2009 16:41:28 GMTjtalberthttp://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxNot 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.Tue, 19 Aug 2008 07:08:22 GMTEd Zannhttp://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxIt 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.ChadMon, 18 Aug 2008 21:56:17 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxSo 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.Mon, 18 Aug 2008 05:48:15 GMTAndrew.Buishttp://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxI 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.ChadFri, 15 Aug 2008 08:43:04 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic553460-149-1.aspxI 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..Fri, 15 Aug 2008 07:31:03 GMTAndrew.Buis