Hello, This problem has haunted me for sometime and I am exhausted in trying to figure this out. I am running SQL Server 2008 and have a linked server (AS400). When I am developing SSIS packages, I have no problems with the linked server. I get full table names along with the column names within the Data flow tasks query builder. But when I am using Microsoft SQL Server Management Studio and I want to build a view, I have to physically type the SQL Statement. Using the four part naming convention, The table will pop up in the Diagram pane, but the only listing is '*(All Columns)'. I cant choose any of the individual fields because they are not even listed. I can continue writing the sql statement in the sql pane and the view works. But it gets really difficult when I have to add a second, third or even forth table. I admit I have been spoiled with the Query builder UI. It is too easy when all you have to do is just check the box next to the column you want or drag a field from one table to another creating a link. I certainly hope someone can help!

After writing this I got to think about why one worked (SSIS) and the other didn't (Management Studio):

SSIS Connection Manager: Properties of as400 Connection(Works)

Connection ManagerType: ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=Neutral, PublicKeyToken=b77a5c561934e089
ConnectionString: uid=<userID>; dsn=<systemname>
DelayValidation: False
Name: <SystemName>.<Username>
Password: <Password>
Qualifier: System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
RetainSameConnection: False
SupportDTCTransactions: False
Username:<Username>

Linked Server Connections: (Does not show Column names in Diagram pane View)

Linked Server: <as400 Systemname>
Other Data Source: (Selected)
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: <System Name>
Data Source: <SystemName>

After looking at these two, I can see that it is the provider. The only Providers I see for a linked server that I have is:

Microsoft OLE DB Provider for SQL Server
Microsoft OLE DB Provider for Analysis Services 10.0
Microsoft OLE DB Provider for Data Mining Services
IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
OLE DB Provider for Microsoft Services
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
SQL Server Native Client 10.0
Microsoft OLE DB Provider for ODBC Drivers
Microsoft Jet 4.0 OLE DB Provider
IBM DB2 UDB for iSeries IBMDARLA OLE DB Provider
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for Indexing Service

I know I had in the past tried these other providers and had problems. I will try again to see if anything has changed. Maybe somebody has been through this or understands more than I do and can help.
* I have tried all of the above that would allow a connection. Same Results. Connection can be made but still no column names.
Thanks!

Garrett

This post has been edited by slushpuppie: Apr 4 2012, 06:45 AM

I was hoping maybe someone does use an AS400 as a linked server to the SQL Server. If sonmeone does, can you tell me what provider you are using?
Thanks!