Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_columns_ex With Link Server to AS400


sp_columns_ex With Link Server to AS400

Author
Message
Bradley Hardin
Bradley Hardin
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
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
(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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)

Group: General Forum Members
Points: 975 Visits: 866
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?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
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