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 fragmentation changes after backup/restore operation?

Hello,

If i backup a database and then restore it, would physical structure remain the same? specially fragmentation.

I'm concerned about output of DBCC SHOWCONTIG.

Senario: I want to check if client database needs defragmentation, so he's sending db backup file. But is it possible that when i restore it fragmentation info has got lost?

Thank you.

Table/index fragmentation should be unaffected by BACKUP/RESTORE.

|||

And i hope it's same for tranferring .mdf file to be attached later?

?

|||Yes, it is the same for detach/attach.

Does a synchronous transformation process all rows in a buffer before outputting to next transfo

Hi,

If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?

Thanks in advance,
Lawrie.

Parts:
Component A (CA), Component B (CB), Row 1 (R1), Row 2 (R2), Row 3 (R3)

Example Synchronous:
CA and CB are Synchronous transforms (as defined by something like: output.SynchronousInputID = Input.ID in the ProvideComponentProperties()) (http://msdn2.microsoft.com/en-us/library/ms136027.aspx). The package has the row buffer size set to 1. The data source has 3 rows. The package starts and the following happens.

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA's ProcessInput finishes and R1 is passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB's ProcessInput finishes and R1 is passed downstream

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA's ProcessInput finishes and R2 is passed downstream

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB's ProcessInput finishes and R2 is passed downstream

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA's ProcessInput finishes and R3 is passed downstream

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB's ProcessInput finishes and R3 is passed downstream

Example Asynchronous:
CA and CB are Asynchronous transforms (as defined by something like: output.SynchronousInputID = 0 in the ProvideComponentProperties()) (http://msdn2.microsoft.com/en-us/library/ms135931.aspx). The package has the row buffer size set to 1. The data source has 3 rows. The package starts and the following happens.

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA stores R1
CA's ProcessInput finishes

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA stores R2
CA's ProcessInput finishes

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA stores R3
CA loops through stored rows and calls AddRow() on the output buffer and passes the data from the stored row to the new row
CA's ProcessInput finishes

R1-3 are passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB stores R1
CB's ProcessInput finishes

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB stores R2
CB's ProcessInput finishes

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB stores R3
CB loops through stored rows and calls AddRow() on the output buffer and passes the data from the stored row to the new row
CB's ProcessInput finishes

R1-3 are passed downstream

OR

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA calls AddRow() on the output buffer and passes the data from R1 to the new row
CA's ProcessInput finishes and R1 is passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB calls AddRow() on the output buffer and passes the data from R1 to the new row
CB's ProcessInput finishes and R1 is passed downstream

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA calls AddRow() on the output buffer and passes the data from R2 to the new row
CA's ProcessInput finishes and R2 is passed downstream

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB calls AddRow() on the output buffer and passes the data from R2 to the new row
CB's ProcessInput finishes and R2 is passed downstream

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA calls AddRow() on the output buffer and passes the data from R3 to the new row
CA's ProcessInput finishes and R3 is passed downstream

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB calls AddRow() on the output buffer and passes the data from R3 to the new row
CB's ProcessInput finishes and R3 is passed downstream

The point is with Asynchronous transforms is that the component must call AddRow() on the output buffer and passe the data from input buffer row to the new row for it to be passed downstream. As soon as ProcessInput finishes, any rows added to the output buffer are passed downstream. You may need to store all rows or just some and the base classes allow you to pass on records whenever you wish.

|||Hi James,

Many thanks for taking the time to provide such a detailed response. The only problem is that my question was really what happens with synchronous transforms when the package has the row buffer size set greater than 1!

If you could provide an example for this I'd be really grateful...

Thanks,
Lawrie
|||

lawrieg wrote:

Hi,

If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?

Thanks in advance,
Lawrie.

The SSIS pipeline works on buffers at a time, not individual rows (unless buffer size is one).

So, the first component will pass rows to its output when its finished processing that row. But the second compoennt won't start processing until the LAST row in the buffer is passed - because then the buffer will be passed to the next component.

Does that make sense?

-Jamie

|||To expand the explanation for synchronous; change R1, R2, R3 to B1, B2, B3 where B = Buffer.

Document map on Mac

I have seen other posts which indicate issues with the document map
and issues with reporting output in general on the Mac. However, I
haven't seen that anyone is having the specific problem I am having.
I have a report which uses a document map. It works perfectly in IE
6.0 on a PC when rendered as HTML or PDF. The report displays as HTML
(but not PDF) on the Mac in both IE and Safari, however the document
map does not display. This functionality is crucial for me. My main
concern for this particular client is making this document map work
with IE on the Mac whether that be in HTML or PDF. I should note
that, long term, I expect to see reports work in both browsers on the
Mac.
So my question is specifically:
Has anyone succeeded in displaying the document map in a report
rendered as HTML or PDF in IE on a Macintosh running OS 9 or OS 10,
latest release of IE.I have more information which I am sharing with anyone else who is
having this problem. The HTML output seems to use an IFrame. The
IFrame is supported in IE 5.2.3 for the Mac and it is also supported
in Safari. So, I still don't know why the document map doesn't show
up in HTML.
However, the PDF problem was with the Preview application in the Mac.
By default, PDF files are opened by Preview, even when they are opened
from a web page. I have opened other PDF 1.3 files from the web with
Preview without any problems. However, for some reason, Preview could
not open my reports generated from SQLRS. I was able to use the
Finder, Get Info feature on the Mac to tell the OS to open PDF files
with Adobe Reader 6.0 instead of Preview. So, I can now provide a
solution for reports, including a document map(Bookmarks in a PDF), on
the Mac.
I would still prefer to use HTML, which is faster, and does not
require a 2nd window to open.
eppingerr@.software-answers.com (Randy Eppinger) wrote in message news:<abcba2fa.0407160715.65406a3d@.posting.google.com>...
> I have seen other posts which indicate issues with the document map
> and issues with reporting output in general on the Mac. However, I
> haven't seen that anyone is having the specific problem I am having.
> I have a report which uses a document map. It works perfectly in IE
> 6.0 on a PC when rendered as HTML or PDF. The report displays as HTML
> (but not PDF) on the Mac in both IE and Safari, however the document
> map does not display. This functionality is crucial for me. My main
> concern for this particular client is making this document map work
> with IE on the Mac whether that be in HTML or PDF. I should note
> that, long term, I expect to see reports work in both browsers on the
> Mac.
> So my question is specifically:
> Has anyone succeeded in displaying the document map in a report
> rendered as HTML or PDF in IE on a Macintosh running OS 9 or OS 10,
> latest release of IE.|||The document map is only supported on IE 5.5 and higher on Windows.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Randy Eppinger" <eppingerr@.software-answers.com> wrote in message
news:abcba2fa.0407200502.524abb87@.posting.google.com...
> I have more information which I am sharing with anyone else who is
> having this problem. The HTML output seems to use an IFrame. The
> IFrame is supported in IE 5.2.3 for the Mac and it is also supported
> in Safari. So, I still don't know why the document map doesn't show
> up in HTML.
> However, the PDF problem was with the Preview application in the Mac.
> By default, PDF files are opened by Preview, even when they are opened
> from a web page. I have opened other PDF 1.3 files from the web with
> Preview without any problems. However, for some reason, Preview could
> not open my reports generated from SQLRS. I was able to use the
> Finder, Get Info feature on the Mac to tell the OS to open PDF files
> with Adobe Reader 6.0 instead of Preview. So, I can now provide a
> solution for reports, including a document map(Bookmarks in a PDF), on
> the Mac.
> I would still prefer to use HTML, which is faster, and does not
> require a 2nd window to open.
>
> eppingerr@.software-answers.com (Randy Eppinger) wrote in message
news:<abcba2fa.0407160715.65406a3d@.posting.google.com>...
> > I have seen other posts which indicate issues with the document map
> > and issues with reporting output in general on the Mac. However, I
> > haven't seen that anyone is having the specific problem I am having.
> >
> > I have a report which uses a document map. It works perfectly in IE
> > 6.0 on a PC when rendered as HTML or PDF. The report displays as HTML
> > (but not PDF) on the Mac in both IE and Safari, however the document
> > map does not display. This functionality is crucial for me. My main
> > concern for this particular client is making this document map work
> > with IE on the Mac whether that be in HTML or PDF. I should note
> > that, long term, I expect to see reports work in both browsers on the
> > Mac.
> >
> > So my question is specifically:
> > Has anyone succeeded in displaying the document map in a report
> > rendered as HTML or PDF in IE on a Macintosh running OS 9 or OS 10,
> > latest release of IE.