Error: The conversion of char data type to smalldatetime...

iposner

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

SQL 2005, British English connection using SQL Data Compare 7.1.0.245.

Changing my default language to English makes the problem go away.

Looks like it's that old chestnut of coding with US date format strings rather than ISO format strings (yyyymmdd) which would make the problem go away...

ross@segware.co.uk

I have exactly the same problem.

I'm disapointed that the tool can't cope with non-US date formats.

Brian Donahue

Thanks for your post. As far as I know, though, date comparisons are culture specific according to the location set on your operating system.

The problem may be an incompatibility between the date format returned by SQL Server being misinterpreted when converted to a .NET DateTime type.

I'll see if I can get a clear answer on this from our development team.

We definitely don't force you to use a US datetime format, or we couldn't use our own software as we are a UK company!

iposner

The culture specific comparisons in .NET have nothing to do with the SQL Server default language. In the reported bug scenario, the user sesson on the workstation was UK english, but the default language for the connection was British English. When the SQL Server default language for the connection was changed to English (US), everything worked.

Most people don't realise it - regardless of the local workstation settings, they connect to SQL Server using the default English (US) language, so don't experience the problem.

Brian Donahue

If you run SQL Profiler during a Data Compare, you can see that Data Compare is explicitly setting the following settings on the connection:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Can you run Profiler and see if you get something different? I'd assume that explicitly setting us_english and dateformat mdy would return datetime strings in a consistent format.

Also, is this happening during comparison or synchronization of data?

ross@segware.co.uk

If Data Compare is explicitly seeting the language during the compare, then this is the cause of the problem.

I am generating the scripts from data compare, but then pasting these scripts into our own rollout tool (which runs sqlcmd).

So the scripts are being created in us_english, but then when they are run against the target database they are being run using the language of british (which our db is set to use).

As a workaround I am now setting the language to us_english at the begining of the script, and then back again at the end.

I think it would make scense if there was an option to "Use the database language" or "Specify language". If a language is specified then it should also be included in the script output to ensure it runs correctly.

Thanks

Brian Donahue

For my clarification, is the workaround to put the text

SET LANGUAGE us_English

at the top of the SQL Script that Data Compare produces, or to actually change the language on the server?

ross@segware.co.uk

Brian Donahue wrote:

For my clarification, is the workaround to put the text

SET LANGUAGE us_English

at the top of the SQL Script that Data Compare produces, or to actually change the language on the server?

At the top of the sql script that data compare produces.
And then set it back to british at the bottom of the script

Brian Donahue

Thanks--I have added a suggestion that we put SET LANGUAGE us_english in the synch script.

One of our managers will evaluate the request and put it in the product if it solves these problems and there isn't a better way of doing it.