Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

sp_columns_ex With Link Server to AS400 Expand / Collapse
Posted Friday, September 6, 2013 12:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 14, 2016 7:00 AM
Points: 64, Visits: 197
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
• 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.

Many thanks,

Post #1492377
Posted Friday, September 6, 2013 4:17 PM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 889, Visits: 863
Not simple to investigate from the SQL Server side of things. Doing "sp_helptext sp_columns_ex", I find this:

sys.fn_remote_columns (@table_server,@table_catalog,NULL,NULL,NULL) c, -- (1)
sys.fn_remote_provider_types (@table_server, NULL, NULL) p -- LUXOR/KAGERA don't support restricting DATA_TYPE

sys.fn_remote_columns is not accessible directly it seems, but obviously it invokes the provider to get the metadata.

What you could so is to run the corresponding to Profiler on AS400 to see what queries your call to sp_columns_ex generates. Of course, that requires that you have some knowledge about AS400 - I for sure does not!

By the way, you mentioned ODBC. But you are not using ODBC for the access to AS400, I hope, but an OLE DB provider?

Erland Sommarskog, SQL Server MVP,
Post #1492452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse