Brak has asked for the wisdom of the Perl Monks concerning the following question:

I just migrated my SQL server from MSSQL 2005 to 2008 and now DBI::ODBC is returning all the INTs as decimals! Even boolean columns that used to return just 1 or 0, now return 0.00. Just the data moved; the server running the scripts hasn't changed since the move, aside from me pointing my DBI connections to the new server, literally only updating the "Server=oldserver;" to "Server=newserver". Both of my servers are Windows Server 64 2008.

When I get back a result with an ID (primary key) it will respond with "15.00" instead of "15", for example. This makes using that number in hundreds of already written and previously working queries not work at all.

Perl is using: "dbi:ODBC:Driver={SQL Server};" as the connection string to talk to the server and PHP is using "Driver={SQL Server};". PHP seems to be doing it too.

Any DBI or ODBC expert super monks out there who could shed light on why this is happening now and never before? Maybe a tip or configuration change that may fix it?

Solved, See reply below.

Replies are listed 'Best First'.
Re: Need DBI/ODBC advice Quick!
by psini (Deacon) on Aug 10, 2010 at 23:25 UTC

    Did you check whether the problem is in the databse, in the ODBC driver or in DBI?

    Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."

[reply]
Re: Need DBI/ODBC advice.
by pileofrogs (Priest) on Aug 10, 2010 at 23:55 UTC

    If the only thing that changed is the SQL server, then that implies the SQL server has changed the way it's delivering the data. Your solution would then either include some sort of "compatibility mode" or other configuration on the server to get the old behavior back, or something on the other end to convert those floats to ints.

    I'd go search google for changes between SQL server 2005 vs. SQL server 2008.

[reply]
Re: Need DBI/ODBC advice.
by mje (Curate) on Aug 11, 2010 at 09:38 UTC

    I answered you on the dbi-users mailing list. The most likely cause for this is that the "faulty" DSN you are using has the "use regional settings when outputting currency, numbers, dates and times" setting enabled. Don't use it, it is flawed in so many ways you would not believe it and can horribly break applications expecting ODBC calls to return data as per the spec.

[reply]

      Yes mje, you certainly did. For the future benefit of others, here's my response that adds some additional details.

      That's exactly what the problem was, sorta. I wasn't using a DSN, but I had specified the Regional=Yes; setting in my connection string.

      dbi:ODBC:Driver={SQL Server}; Server=%server; Database=%db; Regional=Yes;

      vs

      dbi:ODBC:Driver={SQL Server}; Server=%server; Database=%db; Regional=No;

      Knowing that the DSN had that option (I don't get to interact with our server, our server guy does) made me rethink the purpose of the Regional setting. Both Perl and PHP were using the same connection string which explains why both languages were doing that.

      I turned it off and thousands of queries went from completely broken and failing to 100% working.

      Thank you so much!

[reply]
[d/l]
[select]

        Well... that's a little irritating. In the CB I asked you, and re-asked you, to try to do a brand new connection without cutting and pasting the connection info to see what happened. In the CB, and in this post, all you shared was "dbi:ODBC:Driver={SQL Server}." If you'd shown the whole string, I would have--and probably others too--said, "Try it without the Regional setting." :(

[reply]
[d/l]
Re: Need DBI/ODBC advice.
by aquarium (Curate) on Aug 11, 2010 at 03:07 UTC
    I don't know enough of the detail as I wasn't involved in the initial DB setup, however the database needs to be created with all the correct environment in Windblows, otherwise you'll get the typical (un)helpful automatic conversions and things happening without you even knowing. That means all the locale type stuff + any other silly system or SQL Server parameters need to match before you create the empty database ready for import.
    the hardest line to type correctly is: stty erase ^H
[reply]

Back to Seekers of Perl Wisdom