Do I need to change hardware when migrating from SQL 2000 to SQL 2005?

Hi Everybody!!!!

Do I need to consider investing in new and more powerful hardware when migrating from SQL 2000 (or any other previous version) to SQL 2005?

I have a friend / client who migrated from SQL 2000 to SQL 2005 sp 1. The performance of the server got really bad, and now he's trying to fix the problem by any means necesary.

I think it would be very understandable if investing in hardware would be recommended, as we all have seen that there are so many new platforms to use and integrate to (.NET framework, XML, soap, ADO.NET, etc, etc,) and keeping the old platforms as compatible as always (ADODB, ODBC, etc.)

Any feedback is greatelly appreciated!!!!

Tarh Ik

I would recommend having a fresh, new server with Windows Server 2003 R2 SP2 and then installing SQL Server 2005 SP2 on it. Then, you can either detach/attach or restore from a backup to migrate from SQL Server 2000. This is much less risky than upgrading in place from 2000 to 2005 and will perform better than installing a named instance on the same server where SQL 2000 is running.

As far as hardware goes, obviously, new, faster hardware is better if you have the budget for it. Hardware prices continue to get lower all the time, and nobody has ever complained about a database server being too fast.

DMX Shape query error

Hi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:

Code Snippet

===================================

Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()

And, here's my query:

Code Snippet

SELECTFLATTENED

(SELECT *

FROMPredictAssociation([PredictTable],

10,

INCLUDE_NODE_ID,

INCLUDE_STATISTICS

)

WHERE$NODEID <> ''

)

FROM

[Mining Model]

NATURALPREDICTIONJOIN

SHAPE {

OPENQUERY( [datasrc],

'SELECT ''1234'' AS [Customer_D_SID]'

)

} APPEND ({

SHAPE {

OPENQUERY( [datasrc],

'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]

FROM [Base_Sales_F]

WHERE [Customer_D_SID] = ''1234'' '

)

} APPEND ({

OPENQUERY( [datasrc],

'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]

FROM [dbo].[Calendar_D]

WHERE [CALENDAR_YR_NBR] >= ''2007'' '

)

} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B

} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]

AS T

I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!

The internal error is being raised because your SHAPE statement is generating 2 levels of nesting which doesn't match your model defnition (SQL Server DM only supports single-level nesting i.e. nested tables cannot have table columns).

You need to remove the second nested join and instead, use a view on the transaction table that includes the column (CALENDAR_YR_NBR) you want to filter on.

|||Thank you! Making a view solved my problem.sql

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division Calc in Column Returns 0's or 1's

I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95

Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!

Distribution Cleanup Acting Weird

Hi.
In a previous post I asked if there was anything that we could do to
help the cleanup job run better. We then realized that we had over
12,000,000 records in MSreplcommands.
We have been "reducing" the max retention parameter. It was sitting at
72 hours. We started reducing this parameter by 1 hour, then 2 hours,
then 4 hours and the job would complete in about 4-5 minutes. It would
delete approximately 5000 rows/sec.
Suddenly the same job takes several hours while deleting around 80
rows/sec. This is horrible. Deleting 4 hours of old command takes 4
hours. I'll never be able to catch up.
Is there anything that we can do to bring back the 5000 rows/sec
performance of the earlier calls? What would cause the performance of
this job to decrease so drastically?
We're at a point where we are contemplating rebuilding the distributor
to start with a clean slate and set the distribution cleanup to a low
max retention figure (like 1 or 2 hours). We're hoping that with such a
low figure the job will not take so long to run and not block
replication for more than a few seconds.
One more thing. We're going to run without the distribution cleanup job
for the weekend where we have most of our activity. Will there be any
issues with replication if the distribution tables get super huge.
Regards,
Canadian Gambler.
*** Sent via Developersdex http://www.codecomments.com ***
You may be experiencing blocking betweek the cleanup agent, the logreader
agent which writes to the MSreplcommands table and the distribution agent
which reads from the same table. I'd recommend suspending the log reader and
distribution agents when you give the cleanup agent a decent time to clear
out the table. Your transaction logs may get large during this time, and
you'll need to be careful not to stray anywhere close to the distribution
retention period. If this succeeds, run the log reader and distribution
agents and suspend the cleanup agent while they clear the backlog from the
transaction log.
The above applies if the issue is indeed blocking. When dealing with several
million transactions per day I also got into the situation where the
MSreplcommands table balooned and transactions weren't removed even after
being applied to the single subscriber. Only when the retention period was
reached would they be removed. If this applies to your case, consider
synchronizing then really reduce the retention period.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the help.
this morning we pointed all the ODBCs to the main database, stop
replication and ran the CleanUp job. It took as long to complete.
We will have to synchronize next week, blow up the distribution database
and start fresh. Any issues setting the max retention as low as 1 or 2
hours.
Regards,
Canadian Gambler.
*** Sent via Developersdex http://www.codecomments.com ***
|||A low value for the distribution retention period is a bit risky. You're
assuming that all the transactions will get delivered tothe subscriber
during this period, and if there is a network problem, you'll have to
remember to immediately stop the cleanup agent. I've only ever set a low
value after synchronization to remove the redundant records from
MSreplcommands and then reset afterwards.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution Agent Hangs

Message
Another distribution agent for the subscription(s) is running or the server
is working on a previous request by the same agent.I have two Transactional
publications that were setup as push subscriptions, occasionally when the
distribution agent failed the spid did not complete and when the distribution
agent ran again we got this error
Killing the old SPID is a work around for the problem.
However, we have recently installed SQL 2005 and this is now the
distribution server. We had problems with the DTS packages so the
subscriptions have been changed to pull subscriptions, however now we are
getting this problem most nights and the spid hangs around everytime the
agent fails. Again killing the old spid is a work around but now its
happening too often for this to be acceptable.
Subscriber and Publisher are both SQL 2000 and are not likely to be
upgrading in the near future.
This only happens on two publications, and there are three publications set
up the same way on the same servers, and we have never had a problem with the
third one.
Any suggestions would be most welcome
Thanks
The first part of my message appears to be missing from the post.
We originally had a problem with this when the distributor was SQL 2000, and
the subscriptions were pull subscriptions.
We get this error when the distribution Agent fails (doesn't seem to matter
why it failed) the Agent Job errors and has stopped, but for some reason the
SPID does not disconnect hence when the distribution agent starts again we
get the error previously mention.
"Janet" wrote:

> Message
> Another distribution agent for the subscription(s) is running or the server
> is working on a previous request by the same agent.I have two Transactional
> publications that were setup as push subscriptions, occasionally when the
> distribution agent failed the spid did not complete and when the distribution
> agent ran again we got this error
> Killing the old SPID is a work around for the problem.
> However, we have recently installed SQL 2005 and this is now the
> distribution server. We had problems with the DTS packages so the
> subscriptions have been changed to pull subscriptions, however now we are
> getting this problem most nights and the spid hangs around everytime the
> agent fails. Again killing the old spid is a work around but now its
> happening too often for this to be acceptable.
> Subscriber and Publisher are both SQL 2000 and are not likely to be
> upgrading in the near future.
> This only happens on two publications, and there are three publications set
> up the same way on the same servers, and we have never had a problem with the
> third one.
> Any suggestions would be most welcome
> Thanks
>