Passing Optional Parameters To Stored Procedure

Posted by beakersoft | Posted in SQL Server | Posted on 03-07-2007


Found quite a cool thing recently, that means you can have optional parameters in a SQL stored procedure.
This means you can pass the options into the procedure if you have some, or if none are passed get the SP to use some defaults instead. It took a while to get the syntax correct, but its fairly straight forward, in this example we will use the year and month as optional parameters:

@year int = NULL, @month char(20) = NULL

if ISNULL (@year, 0) = 0
SELECT @month = DATENAME(month, GETDATE())

First of all we declare the 2 variables, then we check if the first one is NULL (nothing passed). If it is then we set both the month and year to be the current month and year. Simple but effective!

I have another article published at, this one is about creating a simple RSS feed using, written in c#. Read it HERE

Integration Heartache

Posted by beakersoft | Posted in SQL Server | Posted on 22-04-2007


Not really been learning to much new over the last couple of weeks. We have been moving the Isle of Mans system ‘mainland’ and integrating them into ours.
Found a useful feature of the SQL 2005 Enterprise manager though. We were doing a lot of dumping and loading databases, and wanted to write a SQL script that would do it, but couldn’t find the correct syntax.
My boss then noticed that you can get Enterprise manager to ‘script’ certain tasks. Restoring a database was on of them. When you do the restore there is an option at the top left, it will save to a script what is currently set, or output it to a new window. Very Useful.

On another project I found out how to send optional parameters to a SQL stored procedure. When you declare the variables, make them = Null and you don’t have to pass anything!

Memory Managment in SQL Server

Posted by beakersoft | Posted in SQL Server | Posted on 30-03-2007


For quite a while, we have had a problem with our main instance of SQL server not using all the memory available to it on the server. We have finally found out what the problem was thanks to this article -

The scenario is we have a clustered instance of SQL, that can run on one of 3 identically spec’d server running Windows2003 Enterprise, SQL 2000 Enterprise and with 16gig of ram.

The sqlsvr process had only been running at about 3gig of memory, and was causing us some performance issues on busy days. We had found some articles about changing boot.ini that told windows to use the extra memory, but it didn’t seam to help. Here are the steps:

  1. Open up boot.ini (its normally hidden but lives in the root of c:\)
  2. At the end of the line for the OS you boot to, add /3GB /PAE. This basicly tells Windows 2003 that it and SQL server have access to more than 4gig of Ram, and they can use it
  3. Next you have to set the ‘awe enable’ option of SQL server to 1. This enables the API in SQL server and give it access to the extra memory
  4. Open up SQL query analyser, and type:
    sp_configure ‘awe enabled’, 1
    Reconfigure with overide
  5. When you have done this, restart the server. When you bring it back up, SQL server should have access to all the memory available in the system. You can now tell SQL server how much memory to use in the servers properties, but you now have to specify a a specific amount and not a range.

Also something else I found while doing this, the port that a clustered instance of SQL runs on is 1267, the port it runs on when not clustered is 1433.

Monitoring SQL Server using SQL Server Health and History Tool (SQLH2)

Posted by beakersoft | Posted in SQL Server | Posted on 17-12-2006


A couple of weeks ago my boss came across this tool called SQLH2. Its purpose is to monitor Microsoft SQL server, by collecting data about how the machine is performing and optionally what the SQL server services are doing.

The main part is one executable file that you schedule to run every day or so. This collects data about the server, how it is performing and what the state of the sql servers are running on that machine. It logs all this info into a sql server database.

The second part is a service that runs every few mins, and collects SQL server performance counter data. These get put into a local file and when the afore mentioned exe runs this data gets put into the database as well.

Lastly there is a suite of SQL reporting services reports ready made to look at the database, and provide you with all the information it has collected.

I did run into a couple of problems when installing it, the documentation is quite good but the interface to configure it is a bit clumsy you might be better of editing the xml config files by hand.
First thing is if you want to collect perf data from more than one machine at a central point, make sure you share the SQLH2\data directory’s on the remote servers out as SQLH2PerfCollector$, and make sure the user running the exe has access to this folder.
Also I found it helped to run the perf collector service as a domain user.

Then, on the server that is collecting the data, make sure you add a new target section the the H2PerfConfigFile.xml file, with the relevant counters. There doesn’t seam to be anyway to do this using the GUI config application.

Once you are up and running it seams to create some very useful stats, that might help in your next push for a server upgrade!

The only thing I couldn’t get working (and I’m not sure if you can do it) was monitoring a SQL server instance on a cluster. When running the app, even on the server that was currently running the database it didn’t find the instance. If any one has got this working can you let drop me and email on how to get it working.

Download the SQLH2 here

Update 28/12 - I emailed Microsoft about using it on a cluster, and the responded saying that it was’nt cluster aware. They did say though that you should be able to use the performance counters section by adding it to the xml config file on another machine. I haven’t managed to get this working yet though. If anyone does please let me know