none
DateTime Error (Possibly Related to Windows Workgroup Login) RRS feed

  • Question

  • I have some client code that works when run on the SQL Server but fails, with a date conversion error, when run from a remote client with Windows authentication.

    There is no domain controller so Windows Workgroup is used with the same username and password on both computers. The remote client connects successfully using Windows authentication and executes queries except for any involving date/time strings.

    The error is "Conversion failed when converting date and/or time from character string..OleDb Info:"

    I am wondering whether this could be a strange user/default language issue. There is login in the database for the SQL Server Windows user (Computer1\UserX) but not for the remote client Windows user (Computer2\UserX). Perhaps SQL can deal with this as far as establishing a remote connection is concerned but not for language. The one thing that makes me wonder about that is that the day at the moment is less than 13 so, while this could cause incorrect dates, it shouldn't result in an error.

    The format of the strings is like '2014-11-05 13:05:45'. It has been suggested that a more language neutral format would be to omit the dashes, for example '20141105 13:05:45'. I will try this tomorrow and if it fixes the problem I will be happy but any other suggestions would be most welcome.


    R Campbell





    Wednesday, November 5, 2014 1:19 PM

Answers

  • The language neutral date/time format made no difference and I wasn't able to create a SQL login for UserX on the remote computer (Computer2). I can't help thinking that this is a unique problem associated with not having domain accounts.

    The application that I am using has its own client/sever architecture so I ended up using a remote procedure call from the remote computer to run the code on the SQL Server. It actually has a slight speed advantage because the SQL connect is faster

    Thanks again for the suggestions.

    .


    R Campbell

    • Marked as answer by Dick Campbell Thursday, November 6, 2014 8:53 AM
    Thursday, November 6, 2014 8:53 AM

All replies

  • Dick

    You are on the right way....

    http://blog.sqlauthority.com/2012/12/03/sql-server-fix-error-microsoft-ole-db-provider-for-sql-server-error-80040e07-or-microsoft-sql-native-client-error-80040e07/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 5, 2014 1:33 PM
    Answerer
  • Hello Dick,

    It's not Windows Workgroup/Domain related, the expected date formats depends on the Default language Setting of the SQL Server Login.

    See also MSDN: Writing International Transact-SQL Statements for how to avoid such issues.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 5, 2014 1:46 PM
  • To correct an error in the original post I should have said server login, not database login.

    Is the problem possibly due to the fact that, because there is no Computer2\UserX login, when the code is called from the remote computer there is no language context at all.


    R Campbell

    Wednesday, November 5, 2014 1:58 PM
  • Is that possible to capture a statement you/application send to SQL Server?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 5, 2014 1:59 PM
    Answerer
  • Of course Server Login is mean and the default language of the SQL Server Login is used for converting date values:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 5, 2014 2:17 PM
  • You dont need to worry on this if you can always pass date values in unambiguos ISO format ie YYYYMMDD

    see

    http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 5, 2014 4:30 PM
  • My error trap captures the SQL text as per below.

    ERROR -SQLInfo: select dbo.ToBigInt(N'2014-11-05 07:11:29') as TimeStamp

    dbo.ToBigInt() is a scalar function that comes with the database application I am using.

    Today I am going to try two solutions:-

    1. A language neutral format, N'20141105 07:11:29', with no dashes.

    If that doesn't work...

    2. Create a login Computer2\UserX, if possible, and explicitly set the language to be the same as Computer1\UserX.

    Another solution could be to use a SQL login but that would mean coding the user name and password in plain text.

    Thanks for the replies. I will report progress.

     

    R Campbell

    Wednesday, November 5, 2014 7:34 PM
  • The language neutral date/time format made no difference and I wasn't able to create a SQL login for UserX on the remote computer (Computer2). I can't help thinking that this is a unique problem associated with not having domain accounts.

    The application that I am using has its own client/sever architecture so I ended up using a remote procedure call from the remote computer to run the code on the SQL Server. It actually has a slight speed advantage because the SQL connect is faster

    Thanks again for the suggestions.

    .


    R Campbell

    • Marked as answer by Dick Campbell Thursday, November 6, 2014 8:53 AM
    Thursday, November 6, 2014 8:53 AM