Date Parameters appear in US Format - SSRS Reports

The Problem

In reporting services a new report is created. The report region/Language is set to English(UK). A date parameter is created.
When the report is run the date parameters are showing up in mm/dd/yyyy format

The Fix

A parameter will take a label and a value field.
Change your query to have a label formatted in the way you like and the value left raw:
Select Right('0' + Cast(Day(dateRange) as Varchar(2)),2) +'-'+ Right('0' + Cast(Month(dateRange)as Varchar(2)),2) + '-'+ cast(Year(dateRange) as char(4)) as label, dateRange as value
FROM dbo.vwDateRange
Order by dateRange desc

Where daterange is a smalldatetime

Set the report parameters to display label as the label and value as value

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.

IsNull and Dates

The following code was written expecting the results to be -1,-1

DECLARE @x as datetime;
set @x = NULL;
SELECT IsNull(@x,-1) AS Col_1, IsNull(NULL,-1) AS Col_2;

The actual result was 1899-12-31 00:00:00.000,-1

Why did it happen?

The reason it does this is because dates can be given INT values where 0 = 1900-01-01

All other dates are relative to that so:
1 = 1900-01-02
-1 = 1899-12-31

SELECT IsNull(@x,-1) AS Col_1;
Is the same as set @x = -1;
Which is the same as
set @x = '1899-12-31 00:00:00.000';

So it did assign the value -1 to the date which then translated to Dec 31st 1899

SSIS: Configuration

Nearly everything in the package can be configured.

The main items you will want to configure are the passwords in server connections along with the name of the server and possible the initial catalog (database).
To set up a configuration option right click the package main window and select package configurations.
The options available are to use an XML file, Registry Entry, Parent Package Variable or a SQL database.
In this example I have a database called dataimports which I am going to use. In dataimports I have created a schema for each batch of work I use. This means I can separate out all the SSIS logging and configuration and I can give users access through the schemas.

Once you select the SQL server option you will need to select a database connection to use and then a table. If you already have config tables the drop down box will list them – else you can create a new one. You will need to type in a configuration filter. This is a key word that will match up the configuration settings in the table with this package. The name should be easily identifiably like >> DataDownload.
When you click next you will see a list of configurable items. If this is a new package there will not be very many.

You should try to restrict the configured values to the ones really needed else you will make reading the configuration table difficult which can lead to errors.

A config file should be used to store secure data – like passwords. It should also be used to store values which will or could change. For example a package which emails a user upon completion could have the To field of the email task configured. This way if you need to change the recipient you can change the config table. If a server name is going to change as the package is deployed then store the server name in a config file. You shouldn’t store static or trivial data like the protection level of a database connection – this will probably never change; or the connection string to a database and then all the individual elements of the connection. If you are using Windows Authentication you may not need to store any connection data.

What it looks like in the table:

I have created a primary key on ConfigurationFeature and PackagePath – the table is created without any primary key.

Note for passwords the value stored in the config file will be *****. This is a place holder. You will need to change this value to the actual password and set security on the table to stop people reading it.

Jobs failing with the error – job can not start as it is suspended

If you look in the SQL Agent error logs it should record the problem - Subsystem %s could not be loaded.

When this happens you need to run this script:

use msdb;
delete from msdb.dbo.syssubsystems;
exec msdb.dbo.sp_verify_subsystems 1;
go

Now restart the SQL Agent

Reference

http://support.microsoft.com/?kbid=914171