TPT READ Operator to SQL Server (HY092 error)

Database
Enthusiast

TPT READ Operator to SQL Server (HY092 error)

I'm running 32-bit TPT 15.10 on a Windows Server 2012 box. The SQL Server is 2008 (10.50). I am using SQL Server ODBC Driver version 11. 

 

The TPT runs great and all records are loaded, but in the log I receive a "Warning" when the connectio via the READ_OPERATOR is established to the SQL Server box. 

 

My Read Operator

DEFINE OPERATOR load_ItemMgr_TblCombos_READ_OPERATOR
  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER ODBC READER'
  TYPE ODBC
  SCHEMA load_ItemMgr_TblCombos_ODBC_SCHEMA
  ATTRIBUTES
  (
    VARCHAR DSNName = 'somedatabase',
    VARCHAR UserName = 'someusername',
    VARCHAR UserPassword = @SRCPassword,
    VARCHAR PrivateLogName = 'myprivatelogname',
    VARCHAR SelectStmt = 'mysqlstatement'
  );

Relevant bits from the log:

load_ItemMgr_TblCombos_READ_OPERATOR: connecting sessions
load_ItemMgr_TblCombos_OPERATOR: connecting sessions
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17176: Info: Message received from ODBC driver:
STATE=01000{delim} CODE=5701{delim}
MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'somedatabase'.' 
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17176: Info: Message received from ODBC driver:
STATE=01000{delim} CODE=5703{delim}
MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.' 
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17175: Warning: Message received from ODBC driver:
STATE=HY092{delim} CODE=0{delim}
MSG='[Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier' 

Those last 3 lines are the issue. Normally I wouldn't care because this is raised as a mere warning, but I catch Warnings because often times it means that not all records are loaded or some other event that I need to inspect. These events are raised up through our cronacle team and an incident is created in our bug tracking software. So this will cause some heartburn if I allow it into production as-is. 

 

I'm hoping there is some TPT variable I can toggle or some other simple fix that doesn't require me monkeying with the script that the cron team uses to call our jobs to keep this from propogating.

 

Tags (2)
9 REPLIES
Teradata Employee

Re: TPT READ Operator to SQL Server (HY092 error)

Hi.

 

>>" I'm running 32-bit TPT 15.10 "

>>" I am using SQL Server ODBC Driver version 11. "

 

AFAIK, the only certified drivers for the TPT in TTU's 15.10 are the Teradata provided ones (Progress DataDirect & Simba).

 

For 16.10 (?) you can use the MS SQL Server ODBC driver version 11.

 

Cheers.

 

Carlos.

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

I'll upgrade to the latest drivers on that server and see if the issue disappears. Thanks for the suggestion!

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

No luck. Upgraded to the latest 32-bit TTU (TPT BASE 16.20.00.04) and I'm seeing the same issue:

Teradata Parallel Transporter Load Operator Version 16.20.00.04
load_ItemMgr_TblCombos_OPERATOR: private log specified: load_ItemMgr_TblCombos_LOAD
Teradata Parallel Transporter ODBC Operator Version 16.20.00.04
load_ItemMgr_TblCombos_READ_OPERATOR: private log specified: load_ItemMgr_TblCombos_ODBCREAD
load_ItemMgr_TblCombos_READ_OPERATOR: connecting sessions
load_ItemMgr_TblCombos_OPERATOR: connecting sessions
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17176: Info: Message received from ODBC driver:
STATE=01000, CODE=5701,
MSG='[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Changed database context to 'somedatabase'.' 
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17176: Info: Message received from ODBC driver:
STATE=01000, CODE=5703,
MSG='[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Changed language setting to us_english.' 
load_ItemMgr_TblCombos_READ_OPERATOR: TPT17175: Warning: Message received from ODBC driver:
STATE=HY092, CODE=0,
MSG='[Microsoft][ODBC Driver 11 for SQL Server]Invalid attribute/option identifier' 

 

Teradata Employee

Re: TPT READ Operator to SQL Server (HY092 error)

There's no way to tell TPT to ignore this warning, or to reset the exit code to indicate anything other than the highest severity encountered. 

 

I suspect the "Invalid attribute/option identifier" is due to some configuration / compatibility issue within the Microsoft "stack", rather than between the TPT ODBC operator and the MS driver. Perhaps using a stand-alone ODBC client too (such as ODBC Test) and/or tracing from the driver side would help you isolate and resolve the underlying problem.

 

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

I suspect you are right. That it is a funk between the driver and the ancient SQL Server it is hitting up. I will try to trace and see if I can get it to stop squaking. 

Thank you for the direction.

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

I ran a trace running both a TPT and over a SQL Client. Only the TPT trace causes the error in the ODBC connection:

executor        b48-de8	ENTER SQLSetConnectAttrW 
		SQLHDBC             0x01597508
		SQLINTEGER                1061 <unknown>
		SQLPOINTER          [Unknown attribute 1061]
		SQLINTEGER                  -6 

executor        b48-de8	EXIT  SQLSetConnectAttrW  with return code -1 (SQL_ERROR)
		SQLHDBC             0x01597508
		SQLINTEGER                1061 <unknown>
		SQLPOINTER          [Unknown attribute 1061]
		SQLINTEGER                  -6 

		DIAG [HY092] [Microsoft][ODBC Driver 11 for SQL Server]Invalid attribute/option identifier (0) 

executor        b48-de8	ENTER SQLErrorW 
		HENV                0x01597488
		HDBC                0x01597508
		HSTMT               0x00000000
		WCHAR *             0x00BAE9E4
		SDWORD *            0x00BAF1E8
		WCHAR *             0x00BAE5E4 
		SWORD                      512 
		SWORD *             0x00BAF1E4

executor        b48-de8	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                0x01597488
		HDBC                0x01597508
		HSTMT               0x00000000
		WCHAR *             0x00BAE9E4 [       5] "HY092"
		SDWORD *            0x00BAF1E8 (0)
		WCHAR *             0x00BAE5E4 [      77] "[Microsoft][ODBC Driver 11 for SQL Server]Invalid attribute/option identifier"
		SWORD                      512 
		SWORD *             0x00BAF1E4 (77)

The little bit available online about this points to a mismatch in character sets between the application and the driver (and/or possibly the OS). Where the driver and whatnot is anticipating UTF16LE and the application is not causing a bit or two to be offset so whatever setting is being set here is miscommunicated and given the smack down by the server.

 

This message comes after it successfully sets the database and sets the language to us_english. Not certain what TPT is trying to set under the hood. When I make the same connection and trace (running the same SQL statement in that connection) I see no other attributes attempting to be set after the database and language are set, so I suspect this is specific to something TPT is trying under the hood. 

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

Got the bright idea to set USING CHARACTER SET UTF16 and the error went away. Now I have brand new errors to work through. 

Highlighted
Teradata Employee

Re: TPT READ Operator to SQL Server (HY092 error)

Yes, 1061 does appear to be SQL_ATTR_APP_WCHAR_TYPE, which seems to be DataDirect specific; I don't think the SQL Server native driver supports that attribute.

You might want to open an incident with Teradata Support and report this as a bug.

 

Interesting that using a Unicode character set for the TPT job changes the behavior. You could also try UTF-8 for the job if UTF-16 causes too many issues.

 

Enthusiast

Re: TPT READ Operator to SQL Server (HY092 error)

Oh! That makes perfect sense then. From what I can tell of the native SQL Server ODBC Drivers is that they anticipate UTF16LE by default since that is what Windows uses. Oddly TPT then trips up on the DataParcels returned which are definitely returned as UTF16LE with the extra byte (Error code 2673). I've doubled the varchar on the schema, but it's still tossing the 2673. There's probably some other voodoo in there causing problems.

 

I'm going to switch over to the Data Direct SQL Drivers since TPT is built to talk to those. I should have thought of that in the beginning. I've only ever done this on our Suse box for these jobs. 

 

I greatly appreciate your direction while I bang my head against a wall :)