Category Archives: SQL

Transact or SQL server related


Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases

SQL Server Monitor Changes to SCHEMA

If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It’s clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here

Some Key fundamentals we are going to be using
First off the

, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes

which contains all the details we want about what is changing and who is trying to change it
We will be using “

” to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the


commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won’t get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here’s the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master

SSMS SQL Server Management Studio Refresh Schema

Refreshing the intelli-sense (Intellisense) for the current connection to a given database can be a bug bare

There is nothing more frustrating than making additions, or changes in design to tables/views and having the management studio intellisense not pick-up the changes once you have made them. This is because the SSMS schema refresh doesn’t occur real-time not does it update local schema changes to those successfully committed to the db while you work, it occurs on close and open of the IDE.

The easiest way to refresh the schema on any connection (also note each new window is a separate connection and as such you need to refresh each window)

You can either

1) Go to Edit -> IntelliSense -> Refresh Local Cache and
2) Hit Ctrl+Shift+R

Hope this helps those with the same issue

SQL 2008 SSRS Web Service access from .NET

When you try to access a report server web service to execute code you get an error similar to, where the scheme or header varies a tiny bit

The HTTP request is unauthorized with client authentication scheme ‘Basic’. The authentication header received from the server was ‘Negotiate,NTLM

Basically my situation is that we have a MS 2008 Server running SSRS outside of our domain in the DMZ. However we need to execute code on a domain machine that will connect and run over 100 reports on the SSRS Server, then dump them on a share in our domain in excel format.

To get around the negotiation problem you need to make sure the SSRS server is allowing connections configured using basic authentication

Find the file


This is usually buried in the install folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Then change the authentication to support your desired connection authentication type

More info at MSDN
Once you have done that you should be good to connect.
Here is some sample code to get you started with connecting to your web service and pulling back a list of items

How to pull together ASP.NET, jQuery, ashx, SQL

I want to cover something that is very detailed and not for the faint hearted.
You will need a moderate to advanced understanding of ASP.NET forms based web applications, javascript and the jQuery library, .NET handlers, transact and stored procedures in SQL Server.
If you stick around and read this article to the end, you will gain a solid grasp of:

1. Understand how to use the power of MS SQL Server full text searching, leveraged with jQuery via .NET
2. learn how to use the jQuery tmpl library to its full potential
3. learn how to use .NET handlers making use of datacontract and datamember applied to classes, serialising and deserialising to and from JSON.
4. Understand concepts employed when designing for high traffic scaleable web apps
5. pickup some nice tips, tricks, concepts and design methodologies that will stay with you and take you forward

Ok in a nutt shell i will be explaining how to build the search engine i have just single handedly built in 2 months, thanks to the power of .NET, jQuery and SQL server without which this would not have been possible.
You can see how it works by typing something in the search box here
If you just had a look, im sure you’ll agree its pretty cool. If your an ultra geek and decided to snoop into the code, good luck its hugely compressed and minified, if you wanted help with anything, just ask me.

So lets begin.. oh wait, i dont have time to continue right now…. watch this space for updates

A google style FTS Full Text Search using MS Sql server 2008

People are used to searching the internet and thanks to google, yahoo and bing everyone can find what they want within a few keystrokes and mouse clicks.
This means that people have been self trained from countless hours spent searching the web using these search engines. We developers can make use of this by providing the same style search in our applications.
Enter the Ivory .Net Language Implementation Kit. What is Ivory ? well Ivory is a developer kit for implementing languages on a .NET platform. You can write your own grammar class to be loaded into the ivory dll in the form of EBNF.
The grammar class basically contains the logic behind how we will provide a Google style syntax for our search engine. Using either c# or writing in the Extended Backus Naur Form (EBNF). Now i don’t profess to be an expert in EBNF, far from it in fact. I simply want to use the already built class found in this kit “FullTextSearchQueryConverter” found in Irony.Samples.FullTextSearch called SearchGrammer and insert it in between my search form and the database call.

The first thing your going to need is the kit, you must download it from here Irony Kit Once you have downloaded it and had a little test of your own you are likely ready to implement it.

Before you continue its in your interest to already have a search facility built that uses the full text search engine in sql server

Let’s beginPersonally I code in vb so I converted the SearchGrammer Class SearchGrammer
Add the aforementioned class to your project.
To integrate this into our search tool you need to locate the area just prior to where you called your Full text Search crunch against the DB.
I integrated mine in the following way.

1. Instantiate our Irony dll and load the grammer class
2. Run the search text through the engine to generate the fts clause
3. Verify there we no errors
4. Pass FTS to db call

Personally I instantiate the Irony class and load the search grammer when my main search class is instantiated.

From a topographical perspective this is the code needed to execute this type of search from anywhere in my application

Where qry is the search object containing amongst other things the search text from the input, and oDS is the returned results from the query
Line 1 instantiates the FT search class, this instantiates the irony dll and loads the searchgrammer like so.

To use this to generate the full text search query I can now perform the following from any part of the class

Where FriendlySearchString is the input string from your user, and ftstring contains the TFS search query we can pass over to which ever means you are using to build the query.
The last thing you will need is the CheckParseErrors() method

I posted a discussion within the irony forum that says a similar thing to this post found here

I hope this helped those who needed a how to on implementing irony as your google style FTS generator in your .NET apps.

SQL Handling Null Values

Having Null Values in your database is a good thing despite those that might tell you otherwsie. Not only does it save space but it is a definitive “im empty” signature. There are of course proper and improper ways to handle SQL Nulls. Just remember never ever try to compare to a null using “=”.

1. A NULL value represents an UNKNOWN value

2. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

Any time a NULL value is present in a column that an aggregation is performed on, such as a COUNT, AVG, or SUM function, those values will be ignored and therefore not included in the functions result.

The COALESCE function returns the first non-NULL value in the field list that it accepts. The COALESCE function is very useful in a join operation or in queries where you are comparing a single value from a list of possible fields to a single value.

Execute denied for sp_send_dbmail

My backup email error handler noted a few errors in the windows event viewer, the specific error was

The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.

If you have receieved this, then you need to add the user to the database by going to properties of the user and editing the “mapping” ensuring that said user is ticked next to the db “msdb”
Then run the follwing trasact against he msdb daabase replacing the word user here with the user you need to apply this to.

SQL Server error full-text filter daemon

We recently migrated from SQL server 2005 to SQL Server 2008 Web edition on our hosted servers, amongst several issues that were faced was the full text search error

SQL Server encountered error 0x80070218 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

So when you execute the recomended SP

you receive this error

Msg 30046, Level 16, State 1, Procedure sp_fulltext_service, Line 163
SQL Server encountered error 0x80070218 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

what you need to do is add the log on user the full text search service is running under, to the full text search security group on the box in question, it will look something ike this


For me all i needed to do was add the “local service” account to the group – restart the sql service and re run the execute command

Problem solved

Using EXISTS with variable tables

I ran into a need to use EXISTS with a variable table yesterday and thought it might be nice to make it available to others.

As you can see all you need to do is enclose the variable table in [ ].