locked
SQL Server 2008 R2 RRS feed

  • Question

  • Have an SQL database that includes fields stored as currency.  In the regional settings for the server changed the currency symbol from a "$" to a "G" (Hatian Gourdes)  The data displays on the screen with a  "G", but if I change anything on the screen when it tries t save it back to the SQL table I get an Error 13 file mismatch.  Running Microsoft Server 2008 R2 Standard and SQL Express 2008.  Tried it on the full version of SQL and it does the same thing.
    Wednesday, August 1, 2012 12:00 AM

Answers

  • hi,

    my very first thought would be just to change the datatype of the column(s) in a decimal(18,4) one(s), and foreget the currency symbol... but that requires to inspect all stored procedures code, or what's worse, inspect and change the application "data layer"... it's not a nice scenario :( :)

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    • Marked as answer by Datatekinc Thursday, August 2, 2012 1:44 AM
    Wednesday, August 1, 2012 11:02 PM

All replies

  • hi,

    what do you mean by "...  but if I change anything on the screen when it tries t save it back to the SQL table I get an Error 13 file mismatch. ..." ??

    the "screen" is a client application? how do you submit the changes to the database? you usually should just use, say, a SqlClient.SqlCommand with SqlClient.SqlParameter defined as "Parameter.SqlDbType = SqlDbType.Money", and feeding that parameter with the arithmetical value of your change, say "param.Value = 123.456" omitting the currency symbol... and by the way, the currency symbol will always be the same reflecting the regional settings you already are aware of... personally I'd switch to a decimal(p, s) datatype, and obviously storing the currency symbol as a referencing value in a "Money" datatable (if required) including convervion rates and so on, so that values are "convertible" as necessary...

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    Wednesday, August 1, 2012 12:37 AM
  • In the table there is a field that stores the maximum dollar amount that a customer is allowed to charge at the store.  Let's say for example that it had a value of $1000.00.  If I change the regional settings for the currency symbol to be a "G"  the amount shows up as G1000.00  Perfect!  If I go into the phone number field for example and change it, when I save the record I get the file mismatch error 13 every time.  If I change thee regional setting back to a "$" I have no problems when saving the record. 

    Jim Brown

    Wednesday, August 1, 2012 1:11 PM
  • hi,

    >say for example that it had a value of $1000.00.  If I change the regional settings for the currency symbol to be a "G"  the amount shows up as G1000.00  Perfect! 

    actually, NO :)

    >If I go into the phone number field for example and change it, when I save the record I get the file mismatch error 13 every time

    this is a client application of yours, isn't it? how is the change submitted to the db? as long as you provide a currency symbol as indicated in http://msdn.microsoft.com/en-us/library/ms179882.aspx it should be correctly parsed, but other ones will reflect in an incorrect syntax exception or the like..

    and again, please read the BOL statement that states "... Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value. ..." :)

    regards



    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    Wednesday, August 1, 2012 9:49 PM
  • Actually, its my client, but not my application.  They purchased a product that was developed and sold in the US, but they are located in Haiti.  They do not use the "$" there and it is confusing their customers because 1 US dollar = 42 of their dollars (Gourdes).  I do not have access to the source code to the application that they purchased and was trying to find a solution for them with the tools that I had available.  The company that they purchased the application from indicated that they can't help them.  So, since the "G" is not an accepted currency symbol in the currency table at Microsoft currently,  They really don't have any options right now.   Thanks for your help though!

    Jim Brown

    • Marked as answer by Datatekinc Wednesday, August 1, 2012 10:39 PM
    • Unmarked as answer by Datatekinc Wednesday, August 1, 2012 10:40 PM
    Wednesday, August 1, 2012 10:39 PM
  • hi,

    my very first thought would be just to change the datatype of the column(s) in a decimal(18,4) one(s), and foreget the currency symbol... but that requires to inspect all stored procedures code, or what's worse, inspect and change the application "data layer"... it's not a nice scenario :( :)

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    • Marked as answer by Datatekinc Thursday, August 2, 2012 1:44 AM
    Wednesday, August 1, 2012 11:02 PM
  • That's the only possibility that I thought of as well, but it's gonna be ugly!!  Thanks

    Jim Brown

    Thursday, August 2, 2012 1:43 AM