SQL Problems

Varchar error
Using JDBC to populate varchar fields over 4000 chars long.

Get an error along the lines of

java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer] Disallowed implicit conversion from data type ntext to data type varchar, table 'myDb.dbo.table1', column 'largeVarchar'. Use the CONVERT function to run this query

This is caused by the driver making strings ( using statement.setString( myString ) ) of sizes over 4000 a nText instead of a VARCHAR.

To avoid this problem the prepared statement should use the CONVERT command

String insert = "INSERT INTO table1 VALUES ( CONVERT( VARCHAR( 10000 ), ? ) )"; PreparedStatement statement = connection.prepareStatement( insert ); statement.setString( 1, myString ) ; statement.executeUpdate;

Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Example query
SELECT DiscTitle FROM [serverName\\instance].DVDdb.dbo.user WHERE Username = ?

Solution
check the settings for MSDTC on both servers

On Windows 2003
 * go to Component Services -> Computers -> My Computer -> Properties
 * On the MSDTC tab Network DTC Access should be checked
 * Allow Remote Clients should be checked
 * Allow Inbound should be checked
 * No authentication should be set.

On Windows 200
 * as above, except the properties box is different
 * Default Properties tab
 * Default Authentication Level should be none.

Reference

 * Microsoft KB article

jTDS
jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000 and 2005) and Sybase (10, 11, 12, 15).

I have been moving over to using this driver as to performs much faster than the Microsoft drivers.

External Link
jTDS