Linked server - Retrieve information on columns

[Lava]

I am trying to retrieve information on columns defined in the destination Unidata table. I am using linked server to connect to the table using Unidata ODBC driver.
When I issue below command:

select * from openquery(CADC1, 'select * from INFORMATION_SCHEMA.COLUMNS')

I get the following error:

OLE DB provider "MSDASQL" for linked server "CADC1" returned message "[Ardent][UniData ODBC Driver][IBM][SQL Client][UNIDATA]You have no privilege on file COLUMNS".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from INFORMATION_SCHEMA.COLUMNS" for execution against OLE DB provider "MSDASQL" for linked server "CADC1".

Does any of you know how to query the destination to get the columns metadata?

Thanks,
Lava

[rmiao]

Did you create system dsn cadc1 on sql server? Does regular sql linked server work?

[Lava]

Yes, I have and I can query the table.

Here is the senario, I am trying to insert the data to a table in SQL, when I try to insert:

insert into dbo.tb_PERSONNEL
select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN
FROM PERSONNEL_1_NF')

I get:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Below is how the table setup in SQL:

tb_PERSONNEL ID NULL varchar 10
tb_PERSONNEL PNUM NULL varchar 8
tb_PERSONNEL NAME NULL varchar 30
tb_PERSONNEL PN NULL varchar 1

The destination table is as following:

ColumnName SQLdataType Nullable
ID VARCHAR(10) Y
PNUM VARCHAR(8) Y
NAME VARCHAR(30) Y
PN VARCHAR(1) Y

[rmiao]

Microsoft Knowledge Base article 255765 may help.

[Lava]

I tried, but got the same error

[rmiao]

Where did you get that odbc driver? May check with vendor for latest version.

[Lava]

The driver is an IBM driver, it is called UNIDATA ODBC. I already posted a question with them.

Thanks.