Post reply

sp_columns_ex With Link Server to AS400

  • September 6, 2013 at 12:49 pm

    #276952

    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.

    Many thanks,

    Bradley

    BOL - http://technet.microsoft.com/en-us/library/ms188330(v=sql.100).aspx

  • September 6, 2013 at 4:17 pm

    #1648269

    Not simple to investigate from the SQL Server side of things. Doing "sp_helptext sp_columns_ex", I find this:

    from

    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?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply