Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name = SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward