Re: Implicit conversion from data type text to nvarchar is not allowed.

Hi Matt,

Thanks for your response.

The data conversion known issue you mentioned doesn't seem to apply in this case because I am converting String data less than 4000 characters to varchar column with setAsciiStream().

By setting sendStringParametersAsUnicode property to false, I no longer get any SQLException. But in a complex server environment, I am not sure whether I can set that property without impacting other modules.

Personally I've no experience with indexing issues arising from setString() method, probably I'll do some testing to see what happens.

Btw, how do you view JDBC calls from the server? I don't know of any option in Enterprise Manager that allows me to do that.

Thanks,
Bo Zhu


Matt Weber [MSFT] wrote:
Hello Bo,

This is a known issue when using the SQL Server 2005 JDBC Driver to access SQL Server 2000. The following section from the release.txt file applies:

========================================================================
3) SQL SERVER 2000 DATA CONVERSION LIMITATIONS

When using SQL Server 2000 with the JDBC driver, the following data
conversion limitations apply:

- String data cannot be converted to an underlying money or
smallmoney column.
- String data longer than 4000 characters cannot be converted
to char or varchar underlying columns.
- String data more than 4000 characters cannot be converted to nchar or
nvarchar underlying columns if the sendStringParametersAsUnicode
connection string property is set to false, or the setAsciiStream
method is called.
- String data cannot be converted to text columns if the
sendStringParametersAsUnicode connection string property is set
to true. If you need to support string to text columns conversions,
set the sendStringParametersAsUnicode property to false.
========================================================================

The following test on my system is resolved by setting sendStringParametersAsUnicode to false:

Connection conn = null;
conn = DriverManager.getConnection(connStr);
if (null == conn) {
System.out.println("Unable to establish connection.");
return;
}
PreparedStatement ps = conn.prepareStatement("INSERT INTO StringInput VALUES (?)");
String myString = "The quick brown fox jumps over the lazy dog.";

ps.setAsciiStream(1, new ByteArrayInputStream(myString.getBytes()), myString.length());
int updateCount = ps.executeUpdate();
ps.close();
conn.close();

Incidentally, I am not familiar with any indexing issues that arise from the use of setString() that would be alleviated by using setAsciiStream(). Both methods actually appear the same when viewed from the server:

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P0 varchar(8000)', N'INSERT INTO StringInput VALUES (@P0) ', 'The quick brown fox jumps over the lazy dog.'
select @P1

Best regards,
Matt
--
Matt Weber [MSFT]
Microsoft Developer Support - SQL Developer

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup purposes only.

.

Relevant Pages

  • RE: Implicit conversion from data type text to nvarchar is not allowed.
    ... This is a known issue when using the SQL Server 2005 JDBC Driver to access ... SQL SERVER 2000 DATA CONVERSION LIMITATIONS ... String data longer than 4000 characters cannot be converted ... nvarchar underlying columns if the sendStringParametersAsUnicode ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Implicit conversion from data type text to nvarchar is not allowed.
    ... I agree that the data conversion issue should not apply to your scenario, ... this case because I am converting String data less than 4000 characters to ... This is a known issue when using the SQL Server 2005 JDBC Driver to ... connection string property is set to false, ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Problem converting non-ASCII string to an array of bytes.
    ... I have a string data that has degree symbol in it. ... this string data to an array of bytes in order to send it the socket server ... Encoding.ASCII.GetBytes for conversion but the the degree symbol got ... method is to replace non-ascii character with degree symbol. ...
    (microsoft.public.dotnet.general)
  • Re: converting strings to Integers in SSIS
    ... Is it possible to convert string data to integer in SSIS? ... using the cast -- and the Data Conversion data flow item. ... Please let me know if you know how to perform this conversion. ... suitable method. ...
    (microsoft.public.sqlserver.dts)
  • Re: Upsizing creates huge MSSQL
    ... I used the upsizing wizard for a client several years ago, and many of columns that were created in sql were char instead of varchar, and they were sometimes larger than they needed to be.... ... We discovered what had happened during the conversion, make column data type and length changes, and ensured the tables were indexed correctly, then they were good... ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ...
    (microsoft.public.sqlserver.server)
Loading