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
sqlsql

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

Codepage 932 is not supported by the Java environment.

I have a JDBC type 4 driver, connecting to a Japanese SQL Server 2000. The connection gives this SQL exception:

SQLException: Codepage 932 is not supported by the Java environment.

SQL State: null

SQL Code: 0

This same software has been in use and working just fine for over 2 years with other servers. Does anyone know the solution to this problem?

Thanks!

pds2

What JDBC driver are you using?

The exception "Codepage 932 is not supported by the Java environment." doesn't sound like it's coming from SQL Server so the driver is likely raising it based on server configuration.

Try checking the database collation on SQL Server. That may have changed.

If this new SQL Server is running on a different machine is it possible that the Windows OS is running with a different locale?

Hope this helps,

Vaughn

|||

Hi,

The Microsoft SQL Server 2005 JDBC Driver relies on nio Charset support in the JVM to map from CHAR/VARCHAR/TEXT values with SQL collations to Java String instances. To do that, it needs to have charsets.jar, provided by your JVM vendor, installed and accessible via your application's classpath. Some JVM vendors choose not install charsets.jar, depending on what languages are supported by the host operating system at the time when the JVM is installed. So you may need to install charsets.jar separately.

For further information, please refer to:

http://java.sun.com/j2se/1.4.2/docs/guide/intl/encoding.doc.html

If you have charsets.jar installed and are still seeing this issue, please reply to this thread and I'll attempt to diagnose the issue further.

Thank you,

--David

|||

This fixed the problem! Many thanks!

pds2

|||

Hi David,

I have same error message from running agent on Domino server 7 tried to connect to SQL 2005 as:

java.lang.NoClassDefFoundError:java/nio/charset/Charset

at com.microsoft.sqlserver.jdbc.SQLCollation.readCollation

at com.microsoft.sqlserver.jdbc.SQLCollation.<init>

at com.microsoft.sqlserver.jdbc.IOBuffer.processEnvChange

at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets

at com.microsoft.sqlserver.jdbc.SQLServerConnection.processLogon

...

at java.sql.DriverManager.getConnection(DriverManager.java:539)

at java.sql.DriverManager.getConnection(DriverManager.java:211)

...

And I had installed j2sdk1.4.2_13 from Sun, and checked the charsets.jar is in place in c:\j2sdk1.4.2_13\jre\lib,

All the code with old three jar files were running well with SQL 2000 server before, after the harware failure on that server, we are now with SQL 2005 server, all the agents connected to it changed to new JDBC driver and new jar - sqljdbc.jar are not working at all becuase of this problem...

I had sqljdbc.jar in classpath too, here we are in US (enu). I don't know what else I 've under looked?

Thanks for your help.

Evelyn

|||

Instead of using MS JDBC driver1.1, downloaded jTDS jdbc driver solved the problem!