Mark Mintoff My superpower is common sense

2May/120

Adding Regular Expression Support to SQL Server

First and foremost, it is worth noting that SQL Server supports .Net assemblies, allowing us to create methods which can be used from SQL.

.Net assemblies (dll files) can be loaded into the below folder.

With that out of the way, we can get started on creating a .Net assembly. SQL Server does not support regular expressions natively, so I thought it would be useful to add this functionality as my first assembly.

To start, we can create a Class Library Solution (I called mine 'TextFunctions') and add a single class (I called mine 'StringUtility'). The following is the code I wrote for my StringUtility class:

Very simple bit of code, which allows me to check for Regex matches. No big deal. Now we must install this assembly into the database. First we must check to ensure that the database is CLR enabled, so we run:

Next we will create the assembly. Please note that the dll file must be on the same machine as the database is. You might also want to specify the database you will be adding this to. I added a simple "use master;" to my script.

Now we can install the RGX function I created.

Note that the syntax for EXTERNAL NAME is SolutionName.[Namespace.ClassName].MethodName

That is all! You now have a brand new function which executes C# code and returns a boolean value back! Let us try some simple examples:

This function can also be used in WHERE clause conditions as follows:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.3/5 (3 votes cast)
27Apr/120

Useless SSIS Error Message

Had to investigate an SSIS error code to figure out what was causing it. The error code was -1073450910. I pasted that code into Windows Calculator and converted to hex.

You have to trim the Fs from it, getting me C0047062. I then navigated to C:Program FilesMicrosoft SQL Server100SDKInclude and opened dtsmsg.h to search for the error. What followed was an error which explained absolutely nothing.

Insert sad face here

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
27Apr/122

Simple Way To Check For Rows

I would like to share a very simple technique for checking for rows in your table. Before developing this, I used to make the mistake of doing a SELECT COUNT(1) script.

On tables with a large number of rows (millions), this proved to be inefficient (even with indexing). So instead, I developed this efficient way to check for rows, which makes a difference:

It's very simple. All it does is select the top one row from your table, based on your condition in a subquery (X). Then select count on that subquery and convert the result to a bit. The result will always be 1 or 0, which translates to True and False respectively!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
27Mar/120

A string literal in the expression exceeds the maximum length of 4000 characters.

In SSIS, the error "A string literal in the expression exceeds the maximum length of 4000 characters." has proved to be a stumbling block for me in the past because the error message is misleading. In SSIS, when a string variable is set to Evaluate As Expression, then the string being put together by that expression is not actually a string, but rather the SSIS specific DT_WSTR.

DT_WSTR has a limit of 4000 characters whereas string does not, in spite of what the error message appears to suggest.

I personally ran into this error on attempting to build a large query through Expression, but there is an alternative solution to using EvaluateAsExpression.

If instead, we create a Script Task, providing it with all the necessary variables; we could construct a script which exceeds 4000 characters and output it to the string variable. Contrary to expectations, the string is accepted in it's entirety and it can be used normally.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
27Mar/124

C# Equivalent for SQL IN

One of the things that irks the most when reading code is cumbersome and long-winded IF statements to the tune of:

Naturally, this could be easily typed out as:

But I am not entirely comfortable with this. Certainly it is infinitely better than trying to cram all those possibilities for a into one line, but there must be a more readable syntax. SQL has the answer with the IN statement. For example:

I wanted to emulate this syntax in C#, so I wrote the following extension method which effectively mimics the SQL IN syntax:

or alternatively as Andreas suggested in the comments below:

What this does conceptually, is incredibly simple. You pass to it a generic List or an array and it simply loops on that array searching for a match returning true or false, depending on whether a match has been found. Conceptually, this is akin to the SQL IN syntax.

What this means, is that we can now type the above cumbersome IF statement as:

Which I feel, is a noticeable improvement in the readability of a long-winded collection of OR conditions in a single IF statement.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tagged as: , , 4 Comments
13Mar/120

T-SQL Batch Delete

There are times when you need to delete an arbitrarily large subset of data from a database table. For the sake of example, let us suppose we will be deleting 100,000,000 rows. A simple delete statement:

will take a very long time (hours) to process as 100,000,000 rows will be deleted within a single transaction.

For this purpose, when I need to delete a large subset of data, I prefer to work with a while loop delete and multiple transactions:

This results in a much faster deletion of data as each transaction will only need to delete 1000 rows at a time. An additional benefit is presented if you need to stop the deletion at any point; for example you have locked the table and somebody needs to select some data. There are no long rollbacks and you do not lose progress on what you have deleted.

Hence, to make things easier:

Alternatively:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)
1Mar/120

SSRS Report Parameters via Query String URL

In order to pass report parameters via querystring URL in SSRS, use the following syntax:

http://[servername]/Reportserver/Pages/ReportViewer.aspx?[folder]/[reportname]&rs:Command=Render&[parameter]=[value]

Please note, this is different from the usual syntax when viewing a report:

http://[servername]/Reports/Pages/Report.aspx?ItemPath=%2f[folder]%2f[reportname]

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
16Feb/122

C# Timed Execution

A nice and comfortable way to time how long a particular code block has taken to execute.

Usage is easy. Here's a simple example:

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
23Jan/121

ASP.Net File Serving

The best way to go about creating downloadable files in ASP.Net is to control the way in which those files are served to the users. Some of the most apparently obvious advantages are:

  • Controlling the response header, allowing for a forced download
  • Hiding the directory structure of your website
  • Easy way to control file access through rights
  • File download tracking

To do this, we need to create a Generic Handler (.ashx) in our project.I added one and named it "File.ashx".

Working with generic handlers is super easy. All it takes is programming; no design. Most systems utilize a database to track files and I will be working on this assumption.

In the ProcessRequest method, we will first do some querystring handling to get the ID of the file:

Here, we are simply acquiring the file of the ID and ensuring that the application will not crash through querystring manipulation.

I did not want to get into the details of database implementation, so I wrote the following code block as a simulation of fetching from the database:

I am making the assumption here that the MIME type is already stored in the database along with other information about the file. The best time to extract the MIME type from the file would be during the upload to the server. In order to do this, I developed my own (long...224 lines) method to extract the MIME type from an HttpPostedFile object.

Moving on however, after we have the file name and mime type information, it is time to actually serve the file. We can do this by writing the file into the Response's Output Stream, but first we need to read the file into a byte array.

The byte array returned from this method will then be written into the OutputStream, along with some other information. One of the things we should do is control whether or not the file is a download; forcing the browser to download the file onto the computer as opposed to handling the file how it sees fit. For example, many browsers handle images by opening the image in the browser itself which may not be a desired outcome. Alternatively there may be some files that the browser does not know how to handle (docx for example) and will instead opt to do nothing rather than download the file. Hence:

Here, we are writing the file into the Output Stream, specifying the content type (MIME Type) and setting the content-disposition, determining whether or not the file is a download. It is necessary to wrap the file name in quotes because Firefox does not handle file names with spaces otherwise.

Finally, the way to access the file (from the downloadable example) is creating a link as follows:

And here is the obligatory, downloadable example:

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
23Jan/120

Actual Line Count

Often times, we find ourselves designing throwaway code which only fulfills a one-time requirement. The following piece of coding is one such piece of code.

What it is designed to do is generate an actual count of lines of code within an SQL file (it can be modified accordingly to work with other language files).

In order to do this, the code iterates through the lines of text and skips counting on empty lines, as well as comment blocks.

As it turns out, the 177,251 lines of code file I scanned has 154,109 lines of actual code. Handy!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)