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'?

Do you need to be a programmer to use SSIS?

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

If i understand your question correctly, the Script Component may be overkill. You could just drag over the OLE DB Dest and Source. Between the two items in the data flow, you could use a Derrived Column transform or Data Conversion based on what the nuts and bolts of your data conversion. It looks like you just want to do a conversion by your example and you could do that in the Data Conversion transform.

-- Brian

|||

Worf wrote:

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

Absolutely not. You have already done the required transformation in your SQL statement above. If you can do your required transforms in the source then invariably that is what you should do - there's no point in having unnecassary transformations in your data-flow. If you wanted to carry out those same transformations in the data-flow then you would require a Data Conversion component (or possibly the Derived Column component) and there is no "programming" (in the common sense of the word) involved in using those.

-Jamie

|||Good call Jaimie.