SQL Problems

From Null-pointer

Jump to: navigation, search

Contents

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();

Cross server query error

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

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

Retrieved from "http://www.null-pointer.co.uk/wiki/index.php/SQL_Problems"
Personal tools