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

Does RS process sleep?

I tried both SQL2000 & SQL2005.
But both servers RS processes sleep if no users access to them over an hour.
Once it sleeps, the user has to wait 20 seconds to start responding the
request.
Is this normal? Is there anyway to prevent this waiting time?
Please help!!What I do is have a very simple report that auto-refreshes every 5 minutes.
That keeps it alive. There is also an IIS configuration that can be set but
I haven't used it and I don't remember it off the top of my head. The
auto-refresh is pretty easy hack though.
Instead of 5 minutes you could set it longer than than (30 minutes?).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>I tried both SQL2000 & SQL2005.
> But both servers RS processes sleep if no users access to them over an
> hour.
> Once it sleeps, the user has to wait 20 seconds to start responding the
> request.
> Is this normal? Is there anyway to prevent this waiting time?
> Please help!!
>
>|||Auto-refreshes means scheduling report?
Actually I tried an every 3 minutes schedule report.
But it didn't work for my case.
Anyway, I will try the IIS configuration.
Thanks.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Layout, report tab, autorefreshes checkbox. Once you have a report that
autorefreshes then open it and leave it up. This has worked for me both with
RS 2000 and RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
> Auto-refreshes means scheduling report?
> Actually I tried an every 3 minutes schedule report.
> But it didn't work for my case.
> Anyway, I will try the IIS configuration.
> Thanks.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>|||Yes, it works.
Thanks again.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:egpp%23KE$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Layout, report tab, autorefreshes checkbox. Once you have a report that
> autorefreshes then open it and leave it up. This has worked for me both
> with RS 2000 and RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
>> Auto-refreshes means scheduling report?
>> Actually I tried an every 3 minutes schedule report.
>> But it didn't work for my case.
>> Anyway, I will try the IIS configuration.
>> Thanks.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that
>> can be set but I haven't used it and I don't remember it off the top of
>> my head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>>
>|||That is called Application Pool in IIS. Go to IIS Manager, under Applocation
Pools node, right click "DefaultAppPool" in which the Reporting Server work
process is running, select properties. On "Performace" tag, you will see, by
default, the app pool will shut down if being idle for 20 min. You can
extend this time to 8x60min 480min, so that the app pool will not shut down
for a regular working day. However, the first report reader of the day, will
hit the delay. You may schedule a dummy report at beginning of a work day
for this.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Wow, This is what I was looking for.
Thank you.
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:uSWtdkQ$FHA.532@.TK2MSFTNGP15.phx.gbl...
> That is called Application Pool in IIS. Go to IIS Manager, under
> Applocation Pools node, right click "DefaultAppPool" in which the
> Reporting Server work process is running, select properties. On
> "Performace" tag, you will see, by default, the app pool will shut down if
> being idle for 20 min. You can extend this time to 8x60min 480min, so that
> the app pool will not shut down for a regular working day. However, the
> first report reader of the day, will hit the delay. You may schedule a
> dummy report at beginning of a work day for this.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>

Does replication work with SQL2k standard

Hi,
Does replication work between two SQL2k standard servers? Or do I need
advanced servers?
I'm in the process of migrating from another vendor, and a friend just told
me that I need advanced server to do replication!!!! Is he correct?
TIA,
Edgard L. Riba
Edgard,
yes there's no problem using Standard Edition. Replicationwise, the only
distinction I know of is Indexed views, which are only available in
Enterprise Edition and can be used as a tablelike article. Apart from that
as far as I know they are identical, both as a Publisher and Subscriber.
HTH,
Paul Ibison
|||Thanks Paul...
Edgard
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje
news:O6QF#MHWEHA.2444@.TK2MSFTNGP11.phx.gbl...
> Edgard,
> yes there's no problem using Standard Edition. Replicationwise, the only
> distinction I know of is Indexed views, which are only available in
> Enterprise Edition and can be used as a tablelike article. Apart from that
> as far as I know they are identical, both as a Publisher and Subscriber.
> HTH,
> Paul Ibison
>
|||You can replicate indexed views with the standard and developer editions of SQL Server. However, the query optimizer will not use them in the query plan, unless you give it an optimizer hint.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:

> Edgard,
> yes there's no problem using Standard Edition. Replicationwise, the only
> distinction I know of is Indexed views, which are only available in
> Enterprise Edition and can be used as a tablelike article. Apart from that
> as far as I know they are identical, both as a Publisher and Subscriber.
> HTH,
> Paul Ibison
>
>
|||Thanks - I wasn't aware of this distinction.
Actually I got this from BOL
(http://msdn.microsoft.com/library/de...-us/architec/8
_ar_ts_1cdv.asp) which (incorrectly) claims they are not available in
Standard Edition, but you're spot on according to SQLServerMagazine
(http://www.winnetmag.com/SQLServer/A...657/40657.html).
Cheers,
Paul Ibison
sql

Does MS SQL 2000 support multiple processors?

We are in the process of moving our SQL servers to a SAN
environment and decided to upgrade our servers to 4
processors. Therefore, does anyone know if MS SQL 2000
supports multiple processors?Depends on the edition. See BOL: Maximum Capacity Specifications for the
complete OS\Edition matrix. For four processors, you can use any of the
Windows Server OS editions and Standard or Enterprise Edition SQL Server
2000.
From the performance side, SQL does very well with multiple processors.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Cory" <crubicco@.yahoo.com> wrote in message
news:04ae01c3883d$882e63b0$a301280a@.phx.gbl...
> We are in the process of moving our SQL servers to a SAN
> environment and decided to upgrade our servers to 4
> processors. Therefore, does anyone know if MS SQL 2000
> supports multiple processors?

Does creating KPI require cube reprocess?

The Analysis Service help says that after creating a KPI you need to process the cube in order to browse it. However, that's not the behavior I'm seeing - it looks like the KPI becomes available immediately. What behavior should I be expecting here?

The Books Online is being incorrect in this case. You dont need to re-process your cube after creating KPI's.

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

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.

DoCmd.RunSQL uses what library?

I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
Maury
RunSQL should be part of the Access object library.

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury

DoCmd.RunSQL uses what library?

I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
MauryRunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury