I am having difficulties loading the DateTime data type with TPT from SQL server into teradata using the UTF-16 char set. I keep getting the 2673 error code '2673 SOURCE PARCEL LENGTH DOES NOT MATCH DATA THAT WAS DEFINED'. When I leave out the DateTimes my script loads fine, it is only when I add a DateTime field that things go sour. Any ideas anyone on how to correctly load DATETIME data type into TIMESTAMP(3)? Exporting the the data to a file with bcp utility and doing a fastload works fine.
this is my script:
Update for other people battling with the same issue.
I got this to work using the following script:
this happens even when I try to format my datetime as convert( datetime, Record_Source_Timestamp, 121) in SelectStmt.
Granted I do not have SQLServer on my PC, but when I do an export from Teradata and then a load into Teradata, with TIMESTAMP(3) in the schema, and the value of the timestamp column in the source table is something like '2014-03-06 12:13:14.123', then it loads just fine. And I tried with and without USING CHARACTER SET UTF16.
What is the value in SQLServer that you are trying to export and load?
I will continue to look into this.
I just installed SQL Server onto my PC and created a table with a DataTime column and inserted a single column/row with value:
If I do a SELECT * FROM <tablename>; then I get the proper value.
If I do a SELECT convert(char(23), COL1, 121) FROM <tablename>; then I get the proper value.
(These are queries in SQL Server Management Studio.)
Thus, it is possible that ODBC is not returning the value properly to TPT and I will have to look into that.
I would think that converting to nvarchar would not have worked because you are providing a VARCHAR to TPT and TPT is expecting CHAR.
Can you do me a favor?
Can you re-run the original test (that failed), but in the script for the ODBC operator, set TRACELEVEL='all' and send me the entire job log (the .out file), I would appreciate it.
Send to: firstname.lastname@example.org
Being a binary file (you may have to zip it or rename it for it to get through to my inbox).
in the script that worked I coded the datetime as varchar(48) not as char(48) in the SCHEMA definition, so I don't understand what you mean with your statement "I would think that converting to nvarchar would not have worked because you are providing a VARCHAR to TPT and TPT is expecting CHAR."
In SQL server management studio I also get the proper values with and without converting to char(23).
I am using SQL server ODBC drivers, not the datadirect drivers.
Please see your mail for the requested log file.
I have an additional problem. When i have nvarchar field in SQL Server which contains digits with leading zeros this results in a fatal error for the ODBC Operator: ODBC_Operator: TPT17107: Retrieval error for row ...
I''l try to find a way to code around it
The original script had a schema with CHAR(46) and a 'convert' in the SELECT to CHAR(23).
That should have worked.
Timestamps are fixed length CHAR fields, not VARCHAR.
The fact that the original job did not run concerns me because it should have run fine.
The conversion of DateTime using code 121 converts to a 23-byte string.
With a client session character set of UTF16, the resultant string should have been 46 bytes.
That is what I see in my SQL Server test (not run with the ODBC operator yet).
Thus, I am wondering if the ODBC operator has a bug somewhere.
The fact that converting to VARCHAR did not issue an error is probably due to the operator binding the column as CHAR (since the schema indicated CHAR) and the ODBC driver converting from VARCHAR to CHAR.