Replication: Distributor not set up correctly

Following some 3rd party scripts to set up replication I got this error:

The Distributor has not been installed correctly. Could not enable database for publishing

To fix this I ran these three scripts.

sp_adddistributor 'NameofDistributorServer';
sp_adddistributiondb N'NameofDatabaseBeingReplicated';
sp_adddistpublisher @publisher = 'NameofDistributorServer', @distribution_db = N'NameofDatabaseBeingReplicated';

This then allowed me to run:

use master;
exec sp_replicationdboption N'NameofDatabaseBeingReplicated ', N'publish', N'true'

70-442

I passed the 70-442 exam: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 today, with 910/1000. This now makes me a MCITP: Database Developer - so I must update my CV :-)

I liked this exam although I felt there was a little too much XML and cursors and not enough TSQL. The 70-441 exam felt a bit false and strained - I am going to word this question so that you have to pick one of the new technologies rather than solving a problem.

Well that's that for now, I'll do the 2008 upgrade next year. I will probably go for the 2008 DBA upgrade this year. This year I am planning to take the 70-445 exam: TS: Microsoft SQL Server 2005 Business Intelligence - Development and Maintenance. Then I need the two windows admin exams to finally complete the SLQ 2000 DBA exams. I have been putting those off as I do so little with Windows outside of SQL server and a bit with Active Directory.

Pivot or Sum

A developer was working on a report. The data was originally in a SQL 2000 Server database. The data being used was then transferred into a SQL 2005 database. The original report used SUM and Group BY. Now it was in 2005 Pivot could be used. He wrote a new script and compared results. His question give the same results, is it wrong to assume that SQL Pivot newness means betterness?

Here are the two queries:

Select [Reviewed] + [Logged] as [Queue], [Open] As Development, [Testing], [Pending Imp],
[Open] + [Testing] + [Pending Imp] as [Total In Progress],
[Reviewed] + [Logged] + [Open] + [Testing] + [Pending Imp] as [Grand Total]
from (Select [state] FROM dbo.tblCombinedSnapshto) Datatable
Pivot
(
Count([state])
For [state]
In ([Reviewed], [Logged], [Open], [Testing], [Pending Imp])
) Pivottable;

Select Sum(case when [state] = 'Reviewed' or [state] = 'Logged' then 1 else 0 end) as [Queue],
Sum(case when [state] = 'Open' then 1 else 0 end) as Development,
Sum(case when [state] = 'Testing' then 1 else 0 end) as Testing,
Sum(case when [state] = 'Pending Imp' then 1 else 0 end) as [Pending Imp],
Sum(case when [state] = 'Open' or[state] = 'Testing'
or [state] = 'Pending Imp' then 1 else 0 end) as [Total In Progress],
Count(*) as [Grand Total]
from dbo.tblCombinedSnapshto;

The results were:

PIVOT timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 38 ms.
IO Table 'tblCombinedSnapshto'. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2000 timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 50 ms.
IO Table 'tblCombinedSnapshto'. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So for disk usage they were equal. For speed Pivot wins out.

Only a small scale test but worth doing now and then.