Mark Mintoff My superpower is common sense


How long will my database be IN RECOVERY? (SQL 2008)

I write this as I stare at a screen waiting for a critical database to return out of being In Recovery. Have you ever been there? Have you ever had an unexpected shutdown or error on the production server, while you were running a large transaction? If your answer is "Yes", then inevitably you are wondering how long it is going to take.

To this end, I have devised a simple query which will loop through all databases on the current server and give a report on the database recovery process for any that are in recovery. This will give you an estimate of how long you have left twiddling your thumbs and hoping you did not lose any data.

Enjoy and I hope you did not lose any data!

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

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:


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

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)

SSIS Deployment

Failed to decrypt protected XML node "DTS:Password" with error...This issue occurs mainly due to security related issues. Follow the steps below in order to rectify the issue:

  • In Visual Studio, go to the Control Flow Tab
  • Right-click on an empty area inside the window, select properties.
  • Set Protection Level to EncryptSensitiveWithPassword
  • Set PackagePassword to "myPassword"
  • From the Connection Managers:
    • Double click connection
    • Click "All"
    • Under Security Area:
    • Set Password to your connection's password
    • Set Persist Security Info to True
  • Save the package
  • Connect to SQL Integration Services in SQL Manager
  • Under Stored Packages:
    • Right-Click MSDB
    • Select Import Package
    • Set Package Location to File System
    • Set Package Path to the location of your dtsx file
    • Leave the rest as defaults
    • Click OK
    • Enter "myPassword" into the Package Password dialog
    • You have now successfully imported the package.
  • In order to create an SQL Job:
    • In the job step:
    • Set the Type as SQL Server Integration Services Package
    • Set the Package Source as SSIS Package Store
    • Set the Server as the database server where you stored your package in the steps above
    • Click the button for the package and choose your package.
    • Click OK
    • Enter "myPassword" into the Package Password dialog
Success. You may now schedule and execute the job.
VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

Monitor Running Processes using T-SQL

At times, monitoring processes which are running on your database is necessary; particularly if you suspect that a particular script is taking too long, or clogging everything up. You might have to kill one or two processes in order to ensure that everything keeps running smoothly. In order to monitor my processes and ensure that everything is well, I put together the following script:

This produces an interesting little 'dashboard', allowing you to identify problematic scripts. Hope this serves you well.

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

Drop Procedures using T-SQL

Quite a specific thing to do, but here's how you can drop all your stored procedures in one go using T-SQL. Simply put, all you need to do is run a cursor on a select from the information schema and create an EXEC script.

For this example, I left everything as a PRINT statement in order to minimize the "Oops I destroyed the database" en-masse factor. Leave the PRINT if you want to see what it puts together. Uncomment the EXEC to run it. If you went ahead and ran the script above with EXEC and feel you ruined your life, you might as well drop all your tables. Here, I'll save you some time:

Happy coding and I hope you don't ruin a database.

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

Getting Column Information using T-SQL

Thought I would share a simple T-SQL query in order to get column information about a table using T-SQL. I recently needed a "dump" of specific tables in order to copy paste the column name and data type into a small application I built to auto-generate my stored procedures. This simple script will query the information schema giving you the details:

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

Connection Class -and- Data Access Base

Seeing as I have blogged here and here about Data Access, I thought I ought to resume my little trend and push forward towards a good Connection Class and Data Access Base design. My connection class design is meant to integrate with my DataReader class, and goes further to simplify everything in connections. The Data Access Base inherits from the Connection Class, further simplifying everything through object inheritance.

For the purposes of this blog, I have simplified the Connection Class ever so slightly. Basically, since I work from more than one computer, I keep a value in the web.config, indicating which connection string I want to use. This blog post will assume there's only one connection string to utilize.

What is being done here is fully automated management of transactions, utilization of my reader class and simplified connection handling. This goes well in conjunction with the Data Access Base as follows:

These two interfaces provide a basic cascading inheritance model for the DataAccessBase class, which allow for better generic handling (collections, where clauses, etc) when utilizing DataAccessBase classes.

DataAccessBase will inherit from IDataAccessBase<T> specifying that T must be an IEntityBase. Now let's suppose we wanted a collection of different DataAccessBase<T>. This cannot be achieved since we cannot specify T for each one in the collection. Hence, we can create a collection of IDataAccessBase, which is allowed.

As you can see, there is a try-catch-finally block, along with OpenConnection and CloseConnection in each public method surrounding the protected abstract methods. This means that when a Data Access Component inherits from the DataAccessBase, the methods will be infinitely simpler as there will be no need for connection handling or try-catch-finally blocks. This simplifies a lot for the basic methods of communicating with the database; Get, GetAll, Set and Delete.

Now let's take a look at an example of a Data Access Component which inherits from DataAccessBase;

As can be seen, this simplifies the data access significantly, reducing development time and implementation cost greatly.

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

Simplified DB Design

I have an unorthodox attitude when it comes to databases and database design. Many people prefer to create complex stored procedures, consisting of joins, not ins, wheres and nested select statements. Unsurprisingly, I have a very simplistic approach. Here is my attitude, abbreviated in point form:
  • A database is simply a storage space for information
  • A database must be given data to store
  • A database must retrieve stored data
Anything beyond that, in my opinion, should be achieved through the application connecting to the database. I hold this attitude because I cache the information in the form of objects in static Dictionaries. Anything complex (such as a join or a where statement) is achieved with LINQ (and even Dynamic LINQ) through C# programming. Hence, my take on stored procedures is also very simplified. My preference is to use SQL-Server, and to this end I have made use of Schemas. Schemas are a very nice way of applying what looks like a .Net Namespace to your stored procedures, in addition to allowing security options. I make use of three schemas:
  • Get - Concerned with retrieval of information
  • Set - Concerned with insertions / updates of information
  • Del - Concerned with deletion of information (or rather setting a boolean named 'Deleted' to 'true')
The Get schema typically contains 2 stored procedures per table; GetOne, GetAll. So if I have a table named "MyFile", the stored procedures would be named:
  • Get.MyFile
  • Get.MyFiles
And as you can imagine, they are very simple! and The Set schema typically contains 1 stored procedure per table; SetOne. So for my imaginary table "MyFile", the stored procedure would be named:
  • Set.MyFile
This stored procedure however is not overtly simple; it combines the logic of Update and Insert into one. For this, I am using "IF EXISTS". On MySQL, this can be done with "MERGE". Did you notice the '0' at the end of that Insert statement? That's a default 'false' for the Deleted column. And with that, we get to the Del schema. This too, typically contains 1 stored procedure per table; DelOne. And for my imaginary table "MyFile", the stored procedure would be named:
  • Del.MyFile
Overly simple, it looks like this: The rest is simply a matter of connecting to the database, acquiring the information, caching it and running LINQ on it if necessary. Enjoy!
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

SqlDataReader Wrapper

Applications (and web applications) typically do some kind of work which involves database access. I have developed my own framework for data access (which is awesome) and today I will be talking to you about one of the founding pillars which support this data access framework; reading. When it comes to database access, I do not like to make use of SqlDataAdapters or LINQ over SQL. Rather I have a strong preference towards using the SqlDataReader. The problem with the SqlDataReader is that it's a little on the annoying side when it comes to reading information (which let's face it, is it's function). You either have to keep in mind the column name (and heaven forbid should you decide to change the column's name) or have to keep updating an index in order to access the column. And of course there's the typecasting. In short; it's a bit hairy. To this end, I developed a wrapper to hide away the annoying, hairy bits. The functionality is rather elementary, centering on: The way it works is that you would create a while loop on the Read(), which sets the Index to 0, whereas the GetValue<T> method increments the Index for you. The GetValue<T> method is private as you would essentially create several methods which would call the GetValue method, passing on the type. Now, clearly you don't have to do this. You can go about your business making the GetValue<T> method public, but I wouldn't recommend it because: reader.GetString(); reader.GetBoolean(); reader.GetInt32(); looks a lot neater and more readable than: reader.GetValue<string>(); reader.GetValue<bool>(); reader.GetValue<int>(); Plus; it's shorter to write, so trust me on this one. So let's take a look at usage before diving into the full-on code. I'm going to simplify my examples (don't judge me!) so that I don't reveal all of my Data Access Layer too soon. I might blog about it (encourage me!) at some later date though. And it's really that simple! Would you look at that? No tracking of indexes or column names whatsoever! In my not so humble opinion, I feel this clears up a lot of annoyances. Nothing is gained in terms of functionality true; however I can attest to a huge improvement towards development time, cutting down the time to develop a Data Access Layer drastically (I have made use of this system in a work environment as well). Note that the Reader is initialized by passing on an SqlDataReader into the constructor. So now for the full code: And as usual; Happy Coding!
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)