Error converting data type varchar to datetime when running raw SQL but not when called by LINQ to SQL

Question

A client is getting an error when running my code. They sent me the SQL from profilder.

When I paste it into SQL Server Management Studio it fails with: Error converting data type varchar to datetime

However it doesn't fail when I run it on my local dev box or another clients production sever.

To test I created a simple app with a L2S datacontext containing one entity that looks something like:

public class UserAccount 
{
    public int Id { get; set; }
    public string Username { get; set; }
    public DateTime? LastActivity { get; set; }
}

Insert a record and then update it:

var account = db.UserAccounts.Single(a => a.Username == "Mojo");
account.LastActivity = DateTime.Now;
db.SubmitChanges();

The records gets updated in the database. But when I grab the T-SQL from Profiler:

exec sp_executesql N'UPDATE [UserAccount] SET [LastActivity] = @p2 WHERE ([Id] = @p0) AND ([Username] = @p1) AND ([LastActivity] IS NULL)',N'@p0 int,@p1 nvarchar(4),@p2 datetime',@p0=1,@p1=N'Mojo',@p2='2009-11-10 14:04:41.7470000' 

and execute it in SQL Server Management Studio I get: Error converting data type varchar to datetime

What am I missing?

Answer 1

Are you using identical connections in Management Studio as in your test app? It sounds like this isn't the case the connections have different regional settings, causing the date to be parsed differently.