| Author |
Topic |
|
toby1
Starting Member
8 Posts |
 Posted - 2003-01-17 : 06:02:49
|
| Hi all!Would be really happy if somebody could help me.Im transferring a textfile to a table with an DTS package.The problem is that the field amount set to datatype money will not display the correct value. In the semicolon separated file the value looks like this: 27000,00 and when i look in the table after the transformation is don it will look like this: 2700000. How can I solve this.By the way I'm living in sweden so we use , instead of . |
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-17 : 06:28:21
|
quote:
Hi all!Would be really happy if somebody could help me.Im transferring a textfile to a table with an DTS package.The problem is that the field amount set to datatype money will not display the correct value. In the semicolon separated file the value looks like this: 27000,00 and when i look in the table after the transformation is don it will look like this: 2700000. How can I solve this.By the way I'm living in sweden so we use , instead of .
You can not use a comma separator when you express monetary data on SQL Server. In your case you will have to convert the string using CONVERT or CAST.Regards. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-17 : 06:45:15
|
| update importtable set moneycolumn = moneycolumn/100Jay White{0} |
|
|
|
toby1
Starting Member
8 Posts |
Posted - 2003-01-17 : 06:45:17
|
| Ok. Thankz |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-17 : 08:38:59
|
quote:
update importtable set moneycolumn = moneycolumn/100Jay White{0}
Is he actually asking for the simple update as above or is he wanting the values to be displaed with a , but not a . ? |
|
|
|
toby1
Starting Member
8 Posts |
Posted - 2003-01-17 : 09:18:18
|
| Answer to your question from the forum: It's not a good idea to use cast or convert or divide by 100 to solve a problem like this. It works on sql 7 why wont it work on sql 2000. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-17 : 09:43:56
|
What doesn't work in 2K?create table wallet (bill money not null)insert wallet (bill) values ($10000)select bill from walletupdate wallet set bill = bill/100select bill from walletdrop table wallet Runs as expected...Jay White{0} |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-17 : 09:46:56
|
quote:
What doesn't work in 2K?create table wallet (bill money not null)insert wallet (bill) values ($10000)select bill from walletupdate wallet set bill = bill/100select bill from walletdrop table wallet Runs as expected...Jay White{0}
That is NOT what his problem is. I think he wants to have the ',' in the value. So instead of 100.00 he wants 100,00 but like I said previously.. SQL Server 7 does not allow the ',' in moenatry data, does 2000 allow for it?I don't think so eaither.. it is more of a cosmetic thing to be honest.Regards. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-17 : 09:54:33
|
I think we all know that the display layer and the data tier should and are seperate. toby1's front-end should handle the display of the data, not his sql query.The problem, as I read it, is that toby1 imported data from a file ...quote:
Im transferring a textfile to a table with an DTS package.
... and because the file contained comma's instead of decimals, all of his data is incorrect ...quote:
27000,00 and when i look in the table after the transformation is don it will look like this: 2700000
... I am suggesting that rather than try to fix the import process (by maybe adding an ActiveX transformation for that particular column), just clean up the data in the table and be done with it.Now, toby1, seems to be saying he can't divide by 100 in SQL 2k, but he can in SQL 7 ...quote:
It's not a good idea to use ... divide by 100 to solve a problem like this. It works on sql 7 why wont it work on sql 2000.
... I don't believe that it doesn't work.Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-17 : 10:23:18
|
| I think the problem is not the divide but that the v7 import recognises the comma as a decimal separator but the v2000 one doesn't.I guess v7 is taking the localle settings.As dts is a client utility this is probably from the client that it is running on.It would be worth running the v7 import into the 2000 database but I'm pretty sure that would be OK.Other than that make sure that the v2000 dts install didn't also change the settings on the m/c and ensure that the user that is running dts has it's profile set as Swedish.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
toby1
Starting Member
8 Posts |
Posted - 2003-01-17 : 10:30:05
|
| Ok. Guys! I'm glad that you all are trying to help me. I'm gonna explain the problem a little bit moore.First of all I have a sql server 7 for testing purpose. In this machine I use an DTS package to just do and ordinary text file to table transformation. This works verry well. the file looks like this(textvalue;blah;blah1;blah2;27000,00;Blah3when the field with 27000,00 gets to the table (column with money datatype) I can read 27000 in the table. This is correct.I do the same thing on the same file in sql 2000 and when i look in the table I'll find the value 2700000 instead. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-17 : 10:35:04
|
| Sounds like you are running dts on the servers so check the profiles of the users that you are running under.Also try using dts on the v7 m/c to send data to the v2000 m/c.Log into the server on the v2000 m/c and select @@language to see which language setting are being used there.It should be the user that is running the dts client that decides the separator - but it's worth checking.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
problems with money datatype