DSXchange

I have a sequencial file that I need to load into SQL server. I have already defined the format of the file and the table layout. I have numeric data that I need to be stored as a varchar (comID, Location ID's etc etc) I get the following error when i try to import the data.

[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

the thing is I created the SQL server table based on the file layout in DS ie i let DS create the table so in therie all the data types should be the same. IF the data types are the same why is SQL server tying to do a cast? I can see that it doesnt like the numbers in a varchar field, but it should not mater because DS is trying to enter them in "" meaning that its to be treated as a text string. I dont know whats up with this and was hopeing that other may have seen this. Ill keep looking and will also post the full error.

JOCRAWExports..Transformer_1.DSLink4: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO "Temp_JOC_Raw_Tbl"("Comcode", "Commodity", "Xrecnum", "Fill1", "Name", "City", "St", "Vessel", U_M, "Sline", "Yrmtdy", "Uscode", "Ctrycode", "Fcode", LBS, "Usport", "Country", "Fport", "Pdate", "Xqty", "Ultport", "Ultcode", "Conflag", "Xconqty", "Consize", "Xconvol", "Fill58", "Xteu", "Fill3", "Xhscode", "Bol_Number", "Street", "Street2", "Zipcode", "Comp_nbr", "Manifest_Nbr", "Vessel_Code", "Registry", "Voyage", "Reefer_Flag", "Hazmat_Flag", "Roro_Flag", "Nvocc_Flag", "Financial", "Payable", "Org_des_city", "Org_des_st", "Xvalue", "Fill2") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Comcode = "1301000"
Commodity = "CEREAL"
Xrecnum = "63358968"
Fill1 = " "
Name = "QUAKER OATS"
City = "BOCA RATON"
St = "FL"
Vessel = "SEABOARD ENDEAVOU"
U_M = "PKG"
Sline = "SBMR"
Yrmtdy = "11/01/03 00:00:00"
Uscode = "5201"
Ctrycode = "247"
Fcode = "24741"
LBS = 0000045905
Usport = "MIAMI"
Country = "DOM REP"
Fport = "HAINA"
Pdate = "11/21/03 00:00:00"
Xqty = 00002992
Ultport = "HAINA"
Ultcode = "24741"
Conflag = "C"
Xconqty = 002
Consize = "40"
Xconvol = "0000004321"
Fill58 = " "
Xteu = 00400
Fill3 = " "
Xhscode = "190410"
Bol_Number = "RHA048A49211"
Street = "7000 WEST CAMINO REAL"
Street2 = ""
Zipcode = ""
Comp_nbr = "00008763000001"
Manifest_Nbr = "358"
Vessel_Code = ""
Registry = "LW WW I"
Voyage = "200"
Reefer_Flag = ""
Hazmat_Flag = ""
Roro_Flag = ""
Nvocc_Flag = ""
Financial = ""
Payable = "P"
Org_des_city = ""
Org_des_st = ""
Xvalue = 000042812
Fill2 = " "


Jim

It seems that the "" around the items may be the problem....for one the fields inthe table arent long enough to load the data....but im not sure exaxtly how SQL sees the "" in the sql statment...Ugg
Jim

Casting issues "usually" deal with dates.

What does the DDL look like (i.e. is Yrmtdy a date or timestamp or varchar?)

I think ODBC is expecting a YYYY-MM-DD HH:MM:SS.TTT format.

-Craig Rouse

crouse wrote:Casting issues "usually" deal with dates.

What does the DDL look like (i.e. is Yrmtdy a date or timestamp or varchar?)

I think ODBC is expecting a YYYY-MM-DD HH:MM:SS.TTT format.

-Craig Rouse

I think u are close to it. the two date columns are set to be datetime is SQL and this is how i Dirive them in DS
Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D2/") : " 00:00:00"

im gona change the data types to small datetime and see what i get.
Jim

JDionne wrote:

crouse wrote:Casting issues "usually" deal with dates.

What does the DDL look like (i.e. is Yrmtdy a date or timestamp or varchar?)

I think ODBC is expecting a YYYY-MM-DD HH:MM:SS.TTT format.

-Craig Rouse

I think u are close to it. the two date columns are set to be datetime is SQL and this is how i Dirive them in DS
Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D2/") : " 00:00:00"

im gona change the data types to small datetime and see what i get.
Jim

I changed the data type to small datetime and I removed the : " 00:00:00" from the end of the dirivation. Still same error

Hi,
2 things I can contribute:
1. select a date value from any table that has the same format ans has values, this should get you on the right string format.
2. if you don't use/need the time format simply use Iconv() to get the date to the DS internal number and use Date as your type for this column.

IHTH

roy wrote:Hi,
2 things I can contribute:
1. select a date value from any table that has the same format ans has values, this should get you on the right string format.
2. if you don't use/need the time format simply use Iconv() to get the date to the DS internal number and use Date as your type for this column.

IHTH

here is a twist. I make DS output to a flat file instead of load to the table then i manualy load that file...and it loads with out errors........
now im totaly confused
Jim

Hi,
from flat file you use ds to load with the exact same table definition?
sometimes the table definition that won't go in has a scale format that needs a ".000" added to the string, did you check for that?

hmm just notice you said you let DS build the table?
could it be that the created table gets built with user x from DS, while your insert tries to use the dbo user table with the same name??? or the other way around? (just thinking out lowd)

IHTH

roy wrote:Hi,
from flat file you use ds to load with the exact same table definition?
sometimes the table definition that won't go in has a scale format that needs a ".000" added to the string, did you check for that?

hmm just notice you said you let DS build the table?
could it be that the created table gets built with user x from DS, while your insert tries to use the dbo user table with the same name??? or the other way around? (just thinking out lowd)

IHTH

i dont think its a security issue...i would think id get an access denyied error of some sort.

sometimes the table definition that won't go in has a scale format that needs a ".000" added to the string, did you check for that?


Im not sure what that means. can u explane a bit more?
Jim

yea it means that you need yyy-mm-dd hh:mm:ss.fff format to the datetime ehre fff is the fraction.
I never ment security, simply ment that sql server can have table x for several users i.e. user.x and dbo.x
since the default would be to go to the user's table then dbo.
if you didn't mention dbo.x in your create statement it will be under the user you run the statement.
and if you use fully qualified names to insert the data you might access the dbo.x table.

roy wrote:yea it means that you need yyy-mm-dd hh:mm:ss.fff format to the datetime ehre fff is the fraction.
I never ment security, simply ment that sql server can have table x for several users i.e. user.x and dbo.x
since the default would be to go to the user's table then dbo.
if you didn't mention dbo.x in your create statement it will be under the user you run the statement.
and if you use fully qualified names to insert the data you might access the dbo.x table.

I dont use fully qualified names...and i dont see the same table in two schemas so i dont think im running into that problem. I have also set up
DS to dorp and recreate the table at every run so im almost certain that its going to the created table. as for the format i have chaged the data type to smalldatetime wich will only require mm-dd-yyyy hh:mm:ss and im going back into DS now to see if i can get that format the way i need it. Will revert back when I have succeded or failed
Jim

JDionne wrote:

roy wrote:yea it means that you need yyy-mm-dd hh:mm:ss.fff format to the datetime ehre fff is the fraction.
I never ment security, simply ment that sql server can have table x for several users i.e. user.x and dbo.x
since the default would be to go to the user's table then dbo.
if you didn't mention dbo.x in your create statement it will be under the user you run the statement.
and if you use fully qualified names to insert the data you might access the dbo.x table.

I dont use fully qualified names...and i dont see the same table in two schemas so i dont think im running into that problem. I have also set up
DS to dorp and recreate the table at every run so im almost certain that its going to the created table. as for the format i have chaged the data type to smalldatetime wich will only require mm-dd-yyyy hh:mm:ss and im going back into DS now to see if i can get that format the way i need it. Will revert back when I have succeded or failed
Jim

ok this is confusing to me. This is the code I am using to manipulate the date column.
Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D-YMD(4,2,2)")
I have no errors in the DS transformer stage, but the out put is in Internal DS notation IE i get DS's numeric value for that date, not the data in a date format. Why is that?

JDionne wrote:This is the code I am using to manipulate the date column.
Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D-YMD(4,2,2)")
I have no errors in the DS transformer stage, but the out put is in Internal DS notation IE i get DS's numeric value for that date, not the data in a date format. Why is that?

Square brackets... or more precisely, a lack thereof.

chulett wrote:

JDionne wrote:This is the code I am using to manipulate the date column.
Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D-YMD(4,2,2)")
I have no errors in the DS transformer stage, but the out put is in Internal DS notation IE i get DS's numeric value for that date, not the data in a date format. Why is that?

Square brackets... or more precisely, a lack thereof.

AHHH
thanx

WOOOHOOOOO
it was a date data type
and the code Oconv(Iconv(DSLink3.Yrmtdy, "DYMD[2,2,2]"), "D-YMD[4,2,2]")
Fixed it. Thanx guys.
Jim