Does SP1 change the way strings or data are processed?

We've just patched our Dev server, and, of our 3 servers (Dev, Test, Prod), we see major changes in the output of a raw data import process that runs nightly. Each night we import tables from a Remedy helpdesk system running on Oracle and place each ticket into a row on a table, tracking the changes and history of the ticket, etc. This includes tracking when supervisor groups are changed during the course of a ticket (ie Helpdesk to Data Comms to Billing etc). Now, after SP1, the results on Dev are skewed with partial strings showing in the From and To fields, broken in odd places (like the middle of words).

Has anyone noticed any changes in which post-SP1 SQL Server 2005 processes strings? Does it automatically trim spaces or convert NULLs etc?

The data import should be identical between Dev and the other servers.

Hi,

compare the ANSI NULL settings of your two instances. Right click on the instance > Properties > Connections > Default Connection Options.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Hi,

compare the ANSI NULL settings of your two instances. Right click on the instance > Properties > Connections > Default Connection Options.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

No, the options appear to be the same.

More on the particular symptom:

We get the audit trail string from the import and break it down on the pattern ' to ' (note the spaces) only now, under SP1, it seems to be trimming the space after 'to' so it is really trying to split on ' to', but the procedure isn't searching for ' to' and thus it is being broken as described originally.|||More information...

There seems to be a change in the way data types are handled/defined. Can anyone confirm?

Un-Patched
-
declare @.stringmax varchar(max)
declare @.string varchar(10)
set @.stringmax = 'Boo '
set @.string = 'Boo '

select len(@.string) as [string_len]
select len(@.stringmax) as [stringmax_len]
select datalength(@.string) as [string_datalength]
select datalength(@.stringmax) as [stringmax_datalength]

Returns:

string_len
3

stringmax_len
4

string_datalength
4

stringmax_datalength
4

Patched returns:
--

string_len
3

stringmax_len
3

string_datalength
4

stringmax_datalength
4
...

According to the Books Online, LEN was always supposed to ignore trailing spaces, but it looks like it wasn't ignoring them in VARCHAR(MAX) under the first release.

I couldn't find this 'fix' on any of the associated change documents for SP1. Does anyone know if there are any similar 'gotchas'?