SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server, show tables in SQL Server Management Studio


Linked Server, show tables in SQL Server Management Studio

Author
Message
Andrew.Buis
Andrew.Buis
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 141
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..
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2856 Visits: 18718
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
Andrew.Buis
Andrew.Buis
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 141
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.
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2856 Visits: 18718
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
Ed Zann
Ed Zann
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 1391
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.



jtalbert
jtalbert
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search