MS SQL 2005 TRY ... CATCH In Stored Procedures

Hello World!

 I was trying to figure out how to throw a custom error message through a stored procedure in MS SQL 2005 that can be "caught" using the structured exception handling provided in the database. All this while, my major interaction had been with SQL 2000 style stored procedures that use the @@ERROR system flag to check for an error after each statement that may cause an error to be thrown. One needs to define GOTO labels to handle the errors once detected.
So here is what I was trying to do:
1. Initiate a transaction
2. Start executing a batch of SQL statements that may run into potential errors OR may need to throw an error if some conditions are met/not met
3. Catch the error using a CATCH block, rollback the transaction is required
4. Return the error encountered using the inbuilt functions ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE() and ERROR_MESSAGE()

I managed to create a stored procedure as per my requirements stated above, but the issue was, the error I was trying to “throw” was not being “caught” in the CATCH block. The reason: I did not indicate that my error was severe enough!! A very nice article by Pinal Dave explains what the severity levels indicate. To sum up, a severity less than 16 is treated as informational by SQL server and hence the CATCH block does not get hit.

Here is the basic stored procedure body with the logic that tries to do 1 through 4 I listed earlier:

CREATE PROCEDURE MyStoredProc
@Param1 NVARCHAR(20),
@Param2 NVARCHAR(20)

AS

BEGIN
    -- We don’t need extra results to affect our rowcount
    SET NOCOUNT ON

    -- The following block wraps our SQL statements which may throw error   
    BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @MyUniqID INT

    -- Check if there is a record for @Param1 and @Param2
SELECT @MyUniqID = MyTableKey WHERE Col1 = @Param1 AND
Col2 = @Param2
  
    -- Did we find something?
    IF @MyUniqID IS NOT NULL
        BEGIN
            -- Go on do what ya’ like!
        END
    ELSE
    -- Nope! We did not find a row!
        BEGIN
            RAISERROR (N'Invalid Record. There is not record with the specified %s and %s',
                16, -- Severity,        -- Less than 16 would not be enough!
                1, -- State,
                N'Param1’s column name', -- First argument.
                N'Param2’s column name'); -- Second argument.
        END

        -- If we reach here, everything went to plan
        COMMIT TRANSACTION
        RETURN 0
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT ERROR_NUMBER() AS ERR_NUMBER, ERROR_PROCEDURE() AS ERROR_PROCEDURE ,ERROR_LINE() AS ERROR_LINE, ERROR_MESSAGE()AS ERR_MESSAGE      
    END CATCH

    SET NOCOUNT OFF
END


If you are executing this SP though .NET code using ExecuteDataset(), you may examine the columns of the returned data table to check and take appropriate action in the code as required.

Bon Voyage!!

Configure SASL with svn (subversion)

Hello World,


So here I was trying to grope my way through setting up an svn (SUBVERSION) instance for my projects. Following is what I had set out to achieve:


1. Install an SVN Server
2. Install an svn client to access the server
3. Enable access through a secured protocol like SASL
4. Create a repository structure


This post details how I went about achieving the above.


1. Install an SVN server

This one was the simplest of all. After googling a bit, I gathered that CollabNet offers a free, open source server that comes with an MSI installer – just what I was looking for. I downloaded the same from the CollabNet website. The site has documentation to aid the install and configuration that pretty much works for everyone with the ability to understand English :) 


2. Install an SVN client

Although there are a number of choices here, the most widely used and simple client interface is offered by Tortoise SVN. Again, this comes with an installer (MSI) that is self explanatory.

3. Enable SASL to access the SVN server

This turned out to be the difficult part because the documentation that accompanied Tortoise SVN did not have enough detail (or I could not find it!). What did I do next? No prizes for guessing: Google for how to set up SASL with SVN. I will list out what worked for me (and hopefully would work for anyone)


To enable SASL:


1. Edit svnserve.conf located in C:\SVN_REPOSITORY\conf directory (assuming your repository is located there.)
1.1 Uncomment the line use-sasl=true
1.2 ensure there are no spaces before "use-sasl"

2. Uncomment the line realm=My first repository
2.1 Give a unique name here e.g. realm=SVNREPOROOT
2.2 Ensure there are no spaces before "realm"

3. Create file subversion.conf in the directory pointed to by the registry path
HKEY_LOCAL_MACHINE\SOFTWARE\Carnegie Mellon\Project Cyrus\SASL Library and key called SearchPath under that

4. Enter the following in the subversion.conf file created in 3 above:
pwcheck_method: auxprop
auxprop_plugin: sasldb
mech_list: DIGEST-MD5

for an explanation of what the keys mean, refer to SASL documentation online

5. Now to create users, run the following from command line:

saslpasswd2 -c -f C:\sasldb -u SVNREPOROOT username

where-
C:\sasldb is the location where you'd want to create the sasl database file
SVNREPOROOT is the value specified for realm parameter in your svnserve.conf file
username is the login that you would like to use

5.1 You would be asked for password and then to confirm the password. Once done, the prompt would return to a new line (no message will be shown after you confirm password). This indicates that you have successfully created a user and set a password!!


IMPORTANT NOTE: All parameters in the svnserve.conf file (and any other conf files) should begin on the first column on the line; that is, there should not be any space before the parameter name. If this is not the case, you would get some strange failure messages that may cause your head to spin like a top. You have been warned!