August 15, 2008 at 7:31 am
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..
August 15, 2008 at 8:43 am
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
August 18, 2008 at 5:48 am
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.
August 18, 2008 at 9:56 pm
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
August 19, 2008 at 7:08 am
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.
May 8, 2009 at 4:41 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply