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

• 
  I have exactly the same problem.
  
  I'm disapointed that the tool can't cope with non-US date formats.

  ross April 24, 2009 06:29

  - 0 +
• 
  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!

  Brian Donahue April 24, 2009 13:18

  - 0 +
• 
  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.

  iposner April 24, 2009 18:18

  - 0 +
• 
  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?

  Brian Donahue April 27, 2009 06:24

  - 0 +
• 
  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

  ross May 08, 2009 06:25

  - 0 +
• 
  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?

  Brian Donahue May 08, 2009 08:49

  - 0 +
• 

  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

  ross May 13, 2009 04:56

  - 0 +
• 
  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.

  Brian Donahue May 13, 2009 05:59

  - 0 +

Add comment

Please sign in to leave a comment.