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

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advance
WITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR
|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>
|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>
|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]

>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]
[vbcol=seagreen]
>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> > another process attempting to acquire an IX lock on that table. I saw
> that
> > the showcontig had an "S" lock on the table.
> >
> > Is there any way to influence SQL Server into taking an IS lock on the
> table
> > and S locks on the pages?
> >
> > Thanks in advance
> >
> >
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> > Not currently. Even using WITH FAST requires a Shared Table lock
(however,
> > except on the largest tables any blocking should be fairly transient as
> the
> > results are returned so quickly)
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> > news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > > I noticed that while running a DBCC SHOWCONTIG - this processed
blocked
> > > another process attempting to acquire an IX lock on that table. I saw
> > that
> > > the showcontig had an "S" lock on the table.
> > >
> > > Is there any way to influence SQL Server into taking an IS lock on the
> > table
> > > and S locks on the pages?
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>sql