T-SQL: Get Next/Previous Business Day

In a previous role, we used a service from a company who provided data from the last working day of each month. The application to retrieve the data was an SSIS package which was scheduled via SQL Agent. Unfortunately, the SQL Agent scheduler doesn't (yet) have the abililty to provide custom schedules and there is no inbuilt function to "run on the last working day of the month".

Our solution to this was to implement a schedule table which held a RunDate and a TradeDate, include a task which checked to see if the current day was a valid day and then run the package daily. Nice and simple. The final step was to populate the schedule table with data for the next year and this is where I had to address the T-SQL challenge.

Fortunately, there are many solutions on the web for this sort of thing, but i'm going to post my objects here for reference.

NB: I like to keep all these type of generic functions in a Utils schema so that they can managed in a database project and deployed to a database as required.


CREATE FUNCTION Utils.IsWeekDay(@Dt DATE)
RETURNS BIT
AS
BEGIN
DECLARE @IsWeekDay BIT = 1

IF DATENAME(DW,@Dt) IN ('SATURDAY', 'SUNDAY')
SET @IsWeekDay = 0

RETURN @IsWeekDay
END
GO

CREATE FUNCTION Utils.GetNextWeekDay(@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,1,@NxtDt)
END

RETURN @NxtDt
END
GO

CREATE FUNCTION Utils.GetPreviousWeekDay(@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,-1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,-1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,-1,@NxtDt)
END

RETURN @NxtDt
END
GO


-- and this is how we use the functions to get the schedule table for 2011

DECLARE @Year CHAR(8) = '20110101';

WITH Months
AS
(SELECT 0 AS Mth, CONVERT(DATETIME, @Year) AS Dt
UNION ALL
SELECT 1 + Mth, DATEADD(M, 1+Mth, @Year) AS Dt
FROM Months
WHERE Mth < 11
)
SELECT CASE Utils.IsWeekDay(Dt) WHEN 1 THEN Dt ELSE Utils.GetNextWeekDay(Dt) END AS RunDate,
Utils.GetPreviousWeekDay(Dt) AS TradeDate
FROM Months
GO



SSIS: Precedence Constraint with XML

I was using an expression for a precedence constraint in a package recently and came across an issue where, although the expression was valid (and correct, honest!!) the package would not proceed.

Essentially, i was testing that a string variable which contained XML from an XML (XSLT) task was not blank/empty:

LEN(@[User::ErrorXML]) > 0
@[User::ErrorXML]) != ""

I can see the variable in the watch list as but neither of these expression options was working. I guess this is down to the fact that you can't execute these functions on XML but i think its a bit confusing. The variable is of type string, so no matter if the SSIS Task outputs XML, i would have thought its type would have been strongly bound to that whats set in the variable. This doesn't appear to be the case as I precedence constraints on other "standard" string variables evaluate successfully.

Just something to keep any eye out for.

SSIS: Orphaned Configuration Connections

Although this issue seems to have been around a while, there doesn't seem to be stack loads of information on the web.

If you use configurations in SSIS package, in SQL 2008 an error is thrown if a connection property exists in the configuration, but not in the package. In other words, your package is expecting to find every connection that is referenced in the configuration and if it can't find it, it will throw an error.

The connection "YOURCONNECTIONNAME" is not found. This error is thrown by Connections collection when the specific connection element is not found. (Microsoft.DataTransformationServices.VsIntegration)

So, a typical scenario may be that you have a single configuration file which holds all your connection strings. It stands to reason that not all your packages will use all connections but SQL2008 packages will still expect them to be defined in your package.

This is a breaking change from SQL2005 where such a scenario would throw a warning not an error. To me, the SQL2005 behaviour makes more sense and i'll be interested to see if they revert this.

The workaround to the problem is to increase your configurations, and perhaps have a single configuration for each connection. This is probably workable in many scenarios but I can see this being quite tiresome if you have many connections and many packages.

SQL Database Project in Juneau!!

I've just watched the following video and it seems that MS are finally going to add a database project type to the default development environment.

http://www.msteched.com/2010/Europe/DAT314

Although there appear to be plenty of extra features which are impressive, just having the ability to store your database schema as a project is a massive step forward. Previously, this option was only available in Visual Studio Database Edition (aka datadude) and came at a fairly significant cost. I always thought that MS was selling DBAs/Database Developers a little short by not providing this out of the box in SSMS or BIDS but now it appears that they rectifying the situation.

It'll be interesting to see what this means for SSMS. Perhaps this will be streamlined to be more akin to the old Enterprise Manager and used as an Administration tool rather than the dev/admin hybrid that it currently exists as. Personally, although I have VS2008DE, i tend to do my development using SSMS and then really just use VS2008DE to manage the project from a source control perspective. Its certainly not ideal but SSMS is where i feel most comfortable and I think this just highlights the need for a more "joined up" approach to the SQL tool suite. Fingers crossed this is the solution.

SQLCLR: SQLChars or SqlString

Recently, i came across a post on the forums which focused on a strange error coming from a CLR function. Essentially, the OP was attempting to execute a dynamic sql string in a function (which isn't possible in a regular UDF) so he went down the CLR route. All sounds simple enough, but when calling his CLR UDF, he kept receiving the following error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "DynamicSQL":
System.Data.SqlClient.SqlException: Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at UserDefinedFunctions.DynamicSQL(SqlChars Query, SqlInt32 OperationId, SqlSingle ConstantValue)

Here is a sample of the source code of the CLR function:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static string DynamicSQL(SqlChars Query)
{
string value;
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Sandbox;Integrated Security=True;");
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(Query.ToString(), conn);
value = cmd.ExecuteScalar().ToString();
conn.Close();
}

return value;
}

SELECT dbo.DynamicSql('SELECT 1 AS t', 1, 1)

Nothing in this looked particularly terrible to start but the error wasn't that cryptic - it was parsing the query with 4 part naming. Hardcoding the Query in the CLR resolved the issue indicating there was obviously something amiss with the Query input parameter.

A bit of quick debugging allowed me to establish what the problem was. When casting Query.ToString, the value that was being returned was System.Data.SqlTypes.SqlChars. And this value happens to be in the same format a 4 part object name in SQL Server. Voila!! There is the issue. A bit of digging online, and i found that casting SqlChars.ToString returns a String that represents the current Object. (Inherited from Object.).

A method of resolving this would either be to change the input parameter to be of type SqlString or to change the casting to:

string value = Query.ToSqlString().ToString();



T-SQL: Working with FTS - Stopwords & Filestream

I last worked with Full Text Search back on SQL2000 in a company which used the feature extensively. However since then, i've not had cause to use it in anger and I thought it high time that I refreshed my knowledge. Many things have changed since back then, notably the new FILESTREAM storage and also the reworking of noise words - now called Stopwords - and its these 2 features that i'm going to look at here.

First up, i configured a sandbox environment (I already have a basic C# windows console app that can add the files to the database which i may make available in another post).

CREATE DATABASE Sandbox
GO
/* set up the database/table to store the BLOBs */
ALTER DATABASE Sandbox ADD FILEGROUP Sandbox_FS CONTAINS FILESTREAM
GO
ALTER DATABASE Sandbox ADD FILE (NAME= 'Sandbox_FS', FILENAME = 'C:\SQLData\Sandbox_FS')
TO FILEGROUP Sandbox_FS
GO
CREATE TABLE dbo.SearchTable
(ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, SearchBlob VARBINARY(MAX) FILESTREAM NULL, FileExtension VARCHAR(10))
GO
CREATE FULLTEXT CATALOG Sandbox_FS_FTCat;
GO
CREATE FULLTEXT INDEX ON dbo.SearchTable (SearchBlob TYPE COLUMN FileExtension Language 1033)
KEY INDEX UQ_SearchTable_ID ON Sandbox_FS_FTCat
WITH CHANGE_TRACKING AUTO;
GO

-- add some BLOB data to my table using C# app

SELECT * FROM dbo.SearchTable WHERE CONTAINS (SearchBlob,'"munson"')

So the first issue I came across was with regard to my sandbox SQL installation. I hadn't changed any of the default startup accounts for the SQL Full-text Filter Daemon Launcher so it was running under the LOCAL SERVICE account. When i ran a full text query, i got the following error:

Msg 30053, Level 16, State 102, Line 2
Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.

A quick google search showed that this is a fairly common error with a very simple fix as highlighted in this forum post. Essentially, the LOCAL SERVICE account needs to be part of the SQLServerFDHostUser$$MSSQLSERVER group so I added that, restarted the services and hey presto, we were in business. So rerunning my query:

SELECT * FROM dbo.SearchTable WHERE CONTAINS (SearchBlob,'"munson"')
-- 1 row returned

Next up, its time to configure the stoplist:

-- create the stop list
CREATE FULLTEXT STOPLIST BadWords;
GO
-- add the "bad word" to the stoplist
ALTER FULLTEXT STOPLIST BadWords ADD 'munson' LANGUAGE 'English';
GO
-- associate the stoplist with the fulltext index
ALTER FULLTEXT INDEX ON dbo.SearchTable SET STOPLIST BadWords

SELECT * FROM dbo.SearchTable WHERE CONTAINS (SearchBlob,'"munson"')
-- no rows returned

I've got to say, that i'm impressed with the revamp of the fulltext feature. In the "good ol'" days managing noise words/stop words was quite finicky, editing text files and restarting services whereas now it can all be managed using intuitive T-SQL commands. Good work MS.

T-SQL: Can't Convert Dates in Indexed Views

Just thought i'd drop here an example of a view and how using CONVERT on a date in a view can invalidate it as a candidate to be indexed. Some CONVERT styles are classified as non-deterministic and it just so happens that the default style is one of these so if you use the CONVERT function in your views, you'll need to ensure you specify a valid style.

This is run on SQL2008.

CREATE DATABASE TEST
USE Test
GO
-- set up the sample DDL
CREATE TABLE dbo.a (Id INT NOT NULL PRIMARY KEY, dt DATETIME)
CREATE TABLE dbo.b (Id INT NOT NULL, sdt VARCHAR(100))
GO
-- and add some sample DML
INSERT INTO dbo.a SELECT 1, GETDATE()
INSERT INTO dbo.a SELECT 2, GETDATE()
INSERT INTO dbo.a SELECT 3, GETDATE()
INSERT INTO dbo.b SELECT Id, CAST(dt AS VARCHAR) FROM dbo.a
GO
-- create a basic view which is able to be indexed
CREATE VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, b.sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- this works
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO
-- change the view to add a convert
ALTER VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, CONVERT(DATE, b.sdt) AS sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- now this doesn't work as the CONVERT is non deterministic!!
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO
-- change the view to use a deterministic CONVERT style
ALTER VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, CONVERT(DATE, b.sdt,103) AS sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- now this works as we're using a deterministic CONVERT style!!
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO


BOL has an article which explains what the requirements are for using Indexed Views here.

And you can view more on the CONVERT function and which styles are deterministic here.

Microsoft Community Contributor Award 2011!

On firing up my inbox today, i was intrigued to see an email from MS informing me, "Your online community contributions have been recognized by Microsoft". I assume that this must be in recognition of my activity on the MSDN forums and its nice to receive acknowledgment for it.

However, my involvment in the forums stems from an enjoyment of knowledge sharing and helping others with problems even if you don't have any form of relationship with them. In my jobs, i love working in teams and helping people develop their skills and its a great feeling when you all pull together, share information, bounce ideas and solve a problem as a team. Its just something that floats my boat, pure and simple. Jonathan Kehayis has written a great article on the subject of mentoring and its something i can certainly relate to.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/09/03/the-importance-of-a-mentor.aspx

Of course, there is a second side to the coin when working the forums and that is strengthening my own knowledge. Seeing some of the great/creative ways of solving problems that many of my peers frequently demonstrate is both inspiring and educational. It also helps to revisit some of the concepts that I know but haven't put in practice in a while (SQL Server moves so fast its tough even to tread water!!).

I'd recommend anybody to spend time in the SQL Server forums. They are a fantastic source of information and I think a superb way of getting to know the product and how it is used in real life scenarios. You just can't get that information from a book.

SSRS: SQL2008 RDLC

I thought i'd experiment with writing a WPF app which can expose some SQL2008 reports that i've developed. Notwithstanding the workaround required to use ReportViewer in WPF (see here) it appears that due to the timeline of when VS2008 and SQL2008 were developed/released, you can't use ReportViewer 2008 with SQL2008 reports!! See the quote below from this forum thread:

"Visual Studio 2008 was released much earlier than SQL Server 2008, so ReportViewer 2008 is based on the 2005 version of RDL. A SQL Server Reporting Services 2008 server report is based on the 2008 version of RDL, so, it can't be degrade to 2005 RDLC".

Bit of a pain, but it sounds like this should all be fixed up in Visual Studio 2010.

MCTS 70-448: Too bloated?

Over the past few years i've really started to embrace the power of SSIS and to a lesser extent SSRS. I think with a lot of these things, its only really possible to get engaged in a feature of SQL Server if you have a practical/real world use for the technology rather than just working through the tutorials and examples on the web. However, one area where I don't even have a basic knowledge of is SSAS mainly due to the fact that i've not had cause to dive in and get my hands dirty with it.

At the moment, i'm looking to complete the various SQL MS exams and have been successful in achieving certification in my "comfort zones" of Development and Administration. The next obvious step to complete the set is to take on the Business Intelligence exam MCTS 70-448 and its here we I have a slight issue. In my mind, this exam seems to be a "catch all" for the features of SQL Server that can be described as BI. Of course, i'm not saying that they aren't BI or indeed their principal features are BI but it seems to me that, particularly with the case of SSIS, the range of features/uses is so large that it warrants an exam all of its own.

I'm keen to demonstrate my knowledge of SSIS/SSRS and the MS certification program is a way of proving to others that you do have some level of expertise in these areas. However, SSAS seems to be such a large topic (akin to the Database Engine) that I think its unfair to expect users of SSIS/SSRS to have to know it to a working level in order to earn the 70-448 certification.

I am working through the self paced training kit for 70-448 but am finding it difficult to really get engaged in the SSAS section. Of course, reading through the material and the completing the exercises do help and I feel I am much further ahead than at the start of the week but I'm not sure i'd feel comfortable implementing an SSAS solution from scratch.

But i'll perserve and hopefully achieve certification soon but I hope in the future Microsoft consider realigning their exam content soon.

SQLCLR: Publish SSRS reports from CLR stored procedure

Over the past few years, an task i've come across on a frequent basis is how to produce reports from within an application. The scenario is typically something like: SSIS Package imports data, transforms it and then produces output into a table. The user then wants the data to be rendered into excel and made available to them.

I'd really like to see a system T-SQL stored procedure to be able to do this- something along the lines of sys.sp_renderreport @reportname, @format, @location etc

However, for now this doesn't exist so its down to you to make it happen with the tools at your disposal. SSRS provides the reportexecution2005 webservice which you can use to render reports and its fairly straightforward to call this from a CLR stored procedure.

Here is the source code for the CLR stored procedure:

using System;
using System.Data;
using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Collections.Generic;
using System.Xml.Serialization;
using RenderReport.ReportExecution2005;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RenderReport(string reportPath, string filePath, string format, SqlXml reportParams)
{
ReportExecutionService res = new ReportExecutionService();
res.Url = "http://DEVSERVER/reportserver_DEV/reportexecution2005.asmx?wsdl";
res.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string extension;
string encoding;
string mimeType;
Warning[] warnings;
string[] streamIDs;

int i = 0;

XmlDocument paramXML = new XmlDocument();
paramXML.LoadXml(reportParams.Value);
var paramNodes = paramXML.SelectNodes("//reportparams/param");

ParameterValue[] prms = new ParameterValue[paramNodes.Count];

foreach (XmlNode param in paramNodes)
{
prms[i] = new ParameterValue();
prms[i].Label = param.Attributes["label"].Value;
prms[i].Name = param.Attributes["name"].Value;
prms[i].Value = param.Attributes["value"].Value;
i++;
}

res.LoadReport(reportPath, historyID);

// set params if we have any
if (i > 0)
{
res.SetExecutionParameters(prms, "en-us");
}

byte[] results = res.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

// Open a file stream and write out the report
FileStream stream = File.OpenWrite(filePath);
stream.Write(results, 0, results.Length);
stream.Close();
}
};


A couple of things to note,
1) ReportParams need to be set in this format:
<reportparams><param name="MyName"label="My Name"value="Richard"</reportparams>

2) You will need to follow the steps in this KB article.
3) You will find that you need to deploy this using the EXTERNAL ACCESS permission set.
4) EXTERNAL_ACCESS CLR routines will be executed as the SQL Service Account so it will need appropriate permissions if you want to save reports to a remote location.

T-SQL: Check for invalid views - remember to refresh!

Recently on the MSDN forums, I came across a post which wanted to find orphaned views - those which have had their base tables changed/removed so that they are no longer valid.

I suggested running sp_refreshview 'Schema.View' for each view which would highlight any which aren't able to be resolved. However, the poster came back with a solution which made use of the INFORMATION_SCHEMA view VIEW_TABLE_USAGE. This was something that had slipped under my radar in the past so i thought i'd take a look into it to and see how useful it could be.

On the face of it, the query posted on the forums seemed to be tickety boo! However, on closer inspection, i noticed that it didn't have quite the behaviour i expected. See the following example for an explanation.


-- create some test objects
CREATE DATABASE [Test]
GO
USE [Test]
GO
CREATE SCHEMA schemaa
GO
CREATE SCHEMA schemab
GO
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
CREATE TABLE schemaa.tblb
(
int1 INT,
int2 INT
)
GO
CREATE VIEW schemaa.vwa
AS
SELECT a.*
FROM schemaa.tbla a
INNER JOIN schemaa.tblb b
ON a.int1 = b.int1
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- now remove one of the tables
DROP TABLE schemaa.tbla
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! only 1 row appears in SchemaA.vwA as expected
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- recreate the dropped table
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
-- sp_refreshview
EXEC Sp_refreshview 'SchemaA.vwA'
GO
-- check the results again in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- this time change schemas of objects
ALTER SCHEMA schemab transfer schemaa.tbla
ALTER SCHEMA schemab transfer schemaa.tblb
ALTER SCHEMA schemab transfer schemaa.vwa
GO
-- check the results in INFORMATION_SCHEMA.
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- again 2 rows BUT the VIEW_DEFINTION still shows references to the old schemas.
-- so you could be fooled to thinking that this view is still valid when it isn't
GO
-- try and run refreshview
EXEC Sp_refreshview 'SchemaB.vwA'
-- ha ha, an error!
GO

So you can't rely on INFORMATION_SCHEMA to be a true indicator of whether your views are valid/orphaned or not. Instead, the safest way is to run sp_refreshview to find any that are unable to be resolved.

T-SQL: CTEs - the best thing since sliced bread?

In the absence of anything particularly informative to say, i thought it might be nice just to give a big thumbs up to the MS SQL Server guys for introducting CTEs.

I honestly believe they are the best thing to come out of SQL Server in the last 5 years. Ok, so they may not push the boundaries of what SQL can do but they are the thing I find myself using more than any other. Even if its just making a query more "tidy" they are incredibly powerful particularly when you harness them with the ranking functions.

So a big thanks to MS for giving us CTEs. More evidence that its often the little things that make the difference!

T-SQL: DATEADD Bug?

Just trying to troubleshoot an issue with one of my developers and came across this "feature" with DATEADD.

He had something akin to, SELECT DATEADD(Y,-1,DateColumn) and couldn't understand why it wasn't returning the correct data. It turns out that using the datapart Y in this context actually runs as if it were D.

I checked the documentation in BOL which states that DY or Y is a valid datepart which i don't really understand. DY means day of year which isn't really appropriate for a DATEADD statement. If it behaves like DAY then i'd expect that to be in the documentation.

Looking around connect, it appears that this is a problem with the vbscript implementation of dateadd too so my guess is that this error has been "ported".

I raised a connect case #596764 but it was closed as "by design". I guess its low priority and strictly speaking it shouldn't throw an error as Y is a valid datepart. Still, i don't believe the documentation is clear and i also don't believe that it should behave like this.

SSRS: Format Date Parameter Labels in RS

Recently creating a report with a dataset populated parameter list with INT as the value and DATETIME as the label.

I wanted to have a sensible title along the lines of: Report for date: DD/MMM/YYYY rather than the full nasty datetime. Easy i thought. Just use Parameter!MyDate.Label in a textbox and wrap it with a Format expression. To my chagrin however, i could not get this to work. Doing the following:

=Format(Parameters!MyDate.Label, "dd MMM yyyy")

resulted in a display of dd MMM yyyy. Not what i wanted at all.

Although the label is a DATETIME in the dataset, i wondered whether it was being converted to a string by RS so i tried to explicitly convert it to a date before running the format clause:

=Format(CDate(Parameters!MyDate.Label), "dd MMM yyyy")

Again, no dice but with #Error.

The closest I have been able to get is to just TRIM the label to the 1st 10 characters, which doesn't give me the exact format i'm after and I don't believe is the most robust solution.

=Left(Parameter!MyDate.label,10)

I've posted on the MSDN forums and hope for a response.

Admin: Analysis of Deprecated Features

I recently came across the perfmon counter introduced in SQL2008 of SQLServer:Deprecated Features. It basically tracks executions of deprecated features on a given database server which is invaluable if you are trying to find potentially breaking changes in a huge database - particularly if that database has grown organically across several versions of SQL.

There is also the list provided in Books Online, but i think a real live analysis of your database is much more powerful.

Using the DMV sys.dm_os_performance_counters, you can run a query similar to:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Deprecated Features'

And this will give you the values for any deprecated features you may need to weed out. Further explanations of the exact changes can be found in the earlier article.

Admin: Force Encryption Flag

I've been looking to brush up on some of the security capabilities in recent version of SQL Server and I found myself playing with the encryption features for connecting to SQL Server.

I was getting a touch frustrated and confused at being unable to force SQL Server to deny my connection when using the Force Protocol Encryption to be true on the client. I'd opened SQL Server Configuration Manager (SSCM), right clicked the SQL Native Client and set the flag to be Yes. I was hoping to be see a connection refused error when connection to my remote server which had been started with a self signed certificate. However, I was able to connect successfully through SQL Server Management Studio (SSMS) and checking sys.dm_exec_connections showed that my connection was being made without encryption. To further muddy the waters, when forcing the encryption through SSMS using the options in the connection dialog box, i got an error i was hoping for:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The problem (some people will be thinking, its obvious!!) was that I was testing using SQL Server Management Studio and as SSMS is written in .Net it doesn't use the SNAC to connect to SQL Server at all and uses the more snazzy .NetSqlClient. This explains why changing the properties on the SNAC in SSCM doesn't affect connections made through SSMS.

A method of checking that the flag does force an encrypted connection (it does!) is to use the Import/Export wizard (Start/Run/DTSWizard.exe) to connect which appears to use the SNAC and you'll an error message similar to the following:

SSL Provider: The certificate chain was issued by an authority that is not trusted. (Microsoft SQL Server Native Client 10.0)

T-SQL: Intellisense? Where's that new table?

Does this ring a bell?

You're developing in SSMS and you issue a CREATE TABLE command. You then (understandably) want to add some rows to your new table, so you pop open a new window, start typing your INSERT command and when intellisense pops up the list of tables, your new one isn't there.

No matter as you can easily refresh the cache by just hitting CTRL + SHIFT + R (or go Edit, Intellisense from the menu).

Now, the funny thing is that if you'd have just started typing your insert statements in the same window as the one where you created the table, the table would have been available in the intellisense cache. But new windows opened don't appear to reflect this - its as if each query connection keeps a list of new objects created and then adds these to the main local cache.

Lets get started!!

Recently, I've been spending a fair bit of time looking through http://sqlblog.com/ and have been both entertained and informed by the stuff i've read. So much so in fact that I got the urge to get "Blogging" myself and see if i could add anything useful to the SQL Server community that is obviously already thriving.

Now, i've attempted a blog before and found i couldn't fill it with enough information and my enthusiasm waned quite quickly but hopefully i can make a better attempt this time. I can't guarentee high frequency but i'll endeavour to make regular postings even if its just a snippet of cool code that may help someone out (or will just serve as a reminder to me in the future!).

Anyhow, lets see how it goes.