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
SQL Server Cast Error
Moderators: chulett, rschirm, roy
SQL Server Cast Error
Sure I need help....But who dosent?
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 DScrouse 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
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
Sure I need help....But who dosent?
I changed the data type to small datetime and I removed the : " 00:00:00" from the end of the dirivation. Still same errorJDionne wrote: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 DScrouse 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
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
Sure I need help....But who dosent?
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
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 R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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........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
now im totaly confused
Jim
Sure I need help....But who dosent?
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
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 R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
i dont think its a security issue...i would think id get an access denyied error of some sort.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
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
Sure I need help....But who dosent?
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 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 R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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 uproy 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.
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
Sure I need help....But who dosent?
ok this is confusing to me. This is the code I am using to manipulate the date column.JDionne wrote: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 uproy 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.
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
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?
Sure I need help....But who dosent?
Square brackets... or more precisely, a lack thereof.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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
chulett wrote:Square brackets... or more precisely, a lack thereof.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?
AHHH
thanx
Sure I need help....But who dosent?