Sql Server

In one of our Enterprise web application, we rely on Sql Server's Service Broker backed Query Notification feature a lot. Basically the database of that application is exposed to many other client-specific apps, and our app needs to respond to changes being made in the database by the other apps, a perfect use-case for Query Notifications.

However I lately started seeing lots of these errors in our application's Event Log for one specific client after an update was pushed to them:

I am in the process of revamping server setups for my office as a part of which I am establishing different Windows servers for Sql Server Developer 2008, Sql Server Express and Svn Server. Considering that I often work late nights from my home as well as when I am travelling, I wanted to be able to connect securely to any of these servers as and when required, even when I am outside my office's local network.

This week 2-3 days back, I got a really strange Sql Server error when browsing to one of my ASP.NET applications in the brower on my development server. As soon as I opened the app, the following error greeted me: M8ZWJBN5J8RV

The log for database 'CRC1' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Performing database side paging has always been a headache in Sql Server (as opposed to MySql which provides a built-in LIMIT clause for easy paging). However, I recently found an easy, fast and intuitive way of paging the data on the database side in Sql Server 2005 and later.

You would find some useful links on web, if you search with the title of this blog post. Basically you cannot pass parameters to a Trigger because Triggers get fired automatically by the database engine, and you do not interact with them directly as they execute.

The title sounds like related to a logging operation, isn't it? Well yes, it is.

In an existing logging operation, I needed to ensure that the number of log entries remained below a specified threshhold. And I came up with the following sql quickly:

I was thinking what title was more appropriate for this blog post, the one actually used above, or would "Sql Server as lock manager" would have been more appropriate.

Today only, I had a situation where I needed to ensure that multiple instances of one of our ASP.NET applications deployed on a web farm co-ordinated with each other in executing certain actions and did not get into a race situation with each instance trying to do the same thing as others.

I recently had a situation where I needed to trim a custom character from the right of a string in Sql Server. Although Sql Server provides a RTRIM function, it can only trim spaces from a string.

A quick search over web threw up some interesting results, but all of them were buggy in one way or another. The major problem was that most of them assumed that spaces would not occur in the string itself.

So, I came up with the following solution as a UDF:

I found this the hard way out. Local variables in a T-Sql loop in Sql Server are not really local in the strict terms of a block-oriented programming language like C/C++.

e.g. In C++, if you have the following:

int i;
for (i = 0; i<=10; i++)
{
       int *p = NULL;
       if (p == NULL)
          p = new int(1);
       else
          *p = *p + 1;
       printf("%d", *p);

}

One problem I have always faced with Sql Server (more so, after I used MySql, and saw that MySql allows this) is to find a way to limit the number of rows being fetched in a SELECT query.