I am trying to extract data from an AS400 using SQL Server 2008 linked server. The following are the steps taken to create the linked server:
• Defined ODBC connection called “HTE" to the AS400
• Create linked server called “HTE" in SQL Server
I believe the link server is defined correctly because the follow are functioning:
• OpenQuery returns data from the tables on the AS400
(example: SELECT * FROM OPENQUERY(HTE, 'SELECT * FROM QGPL.ABT'))
• EXEC sp_tables_ex 'HTE' returns the following:
- Table_Cat -- SCH
- Table_Schem -- QGPL
- Table_Name -- ABT
- Table_Type -- TABLE
- Remarks -- Some remark about table
The problem I am having is that when I try to use the procedure sp_columns_ex no results are being returned. BOL1
indicates sp_columns_ex receives the following parameters: Table_server, Table_Name, Table_schema, Table_catalog, column, and ODBCVer. Only Table_server is required. The following are examples of the variations used without success:
• exec sp_columns_ex 'HTE' , 'ABT' , 'QGPL','SCH'
• exec sp_columns_ex 'HTE' , 'ABT'
• exec sp_columns_ex 'HTE' , ' QGPL.ABT'
• exec sp_columns_ex 'HTE'
I am sure I am missing something simple, but… Any pointers would be appreciated.
BOL - http://technet.microsoft.com/en-us/library/ms188330(v=sql.100).aspx