• redgate Releases SQL Search for Free

    redgate has released their SQL Search 1.0 for free, and my coworker Stephen sent our team an email letting us know about it. It is a fantastic product that integrates with SSMS and now it’s free. It keeps an index of all the text in every sproc, all the columns in every table, etc, and you can search them all instantly, limiting by type and many other options.

    These are the features they list on their page:

    • Find fragments of SQL text within stored procedures, functions, views and more
    • Quickly navigate to objects wherever they happen to be on your servers
    • Find all references to an object
    • Integrates with SSMS

    And their “Why use SQL Search?”:

    • Impact Analysis
      You want to rename one of your table columns but aren’t sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.
    • Work faster
      Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.
    • Make your life easier
      You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text ‘TODO’.
    • Increase efficiency, reduce errors
      You are a DBA, and developers keep using ‘SELECT *’ in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for ‘SELECT *’ in the text of stored procedures and views.

    If you are a user of SQL Server Management Studio, I highly recommend you check out out. You sure can’t beat the price. Check out the screenshots below as well.

  • MCTS and Some Other Stuff

    I am writing this post because of Jen. She pointed out that I haven’t blogged in forever. I thought it was worth posting though, if for nothing else than to show up Chris’ blogging abilities.

    I passed the Microsoft 70-536 and 70-528 tests. That means I’m MCTS certified on Web, Windows, and SQL Server 2005. Distributed is next.

    Also, LSUS (my college) is having an awards ceremony at 12PM on April 13th, 2006 and they’re awarding me two things. I’ve been selected as the “Outstanding CIS Student for the Academic Year”, and also I was on the Chancellor’s list for both semesters last year (3.8 or above or something). They’ve offered me the honor, so I’m going to honor their offer.

    Chris (same Chris as the non-blogging one) and I went to Circle K today for our daily ICEEs (can I pluralize it like that?). While the lady was scanning my card, I saw two little girls (roughly 12-14 years old) talking to Chris and I thought Chris was hitting on 12 year olds. After she scanned my card and Chris was waiting on his to be scanned (dial-up credit card processing), I walked over to find out why Chris was doing that. I was like “What’s funny?” since the girls were laughing, and they said it was that Chris has a Batman credit card and was wearing a Superman shirt and they thought his shoes were Batman shoes. Quickly they lost interest in that and started hitting on me saying they knew me and stuff and that she had a bunch of sisters and I used to go over one one of their houses a couple of years ago or something. I have no idea what she was talking about, but Chris and I left. Weird little girls. (Chris and I are 25 and 24 respectively at the time of this writing).

    Aside from those happenings, you should watch this video. It’s hilarious. I AM A SCIENTIST! For my nerd friends, Mike posted a video on PXE booting. You will probably enjoy that.

    I am going to take a vacation soon I think, since I have to use 2 weeks this year and I know I’ll use about 1 week between Thanksgiving and Christmas. I’m pretty sure I’m going to go to DFW and hang out with Mike and Courtney. Courtney got a Wii, and Wii would like to play.

    You’re welcome Jen. 🙂

  • Enabling SQL Cache Dependency With Schema Solution

    My solution has now been included in a published book about ASP.NET for SQL Server

    This post is about programming and ASP.NET / Microsoft SQL Server 2005, so any of my friends who aren’t programmers won’t find this interesting. With that said…

    If you’re reading this post, it’s more than likely that you’ve encountered the “Cannot create trigger ‘dbo.” error when trying to enable a SQL cache dependency on a table. I will explain how to fix this. If you’re lazy, skip to the bottom. If you want to know the source of the problem, continue reading.

    This is the full error text:

    C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -et -E -d DatabaseName -t SchemaName.TableName -S SERVER_NAME
    Enabling the table for SQL cache dependency.
    .An error has happened.  Details of the exception:
    Cannot create trigger 'dbo.SchemaName.TableName_AspNet_SqlCacheNotification_Trigger' as its schema is different from the schema of the target table or view.
    Failed during cache dependency registration.
    Please make sure the database name and the table name are valid. Table names must conform to the format of regular identifiers in SQL.
    The failing SQL command is:

    The failing stored procedure is provided right there, and I didn’t even see that the first time, so I found what it was running in the SQL Profiler. That was stupid of me, but I still found it. The offending code in that stored procedure if you go and view it (you must enable the database for caching using “-ed” before you can see the stored procedure) is the following:

    ALTER PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] 
                 @tableName NVARCHAR(450) 
             DECLARE @triggerName AS NVARCHAR(3000) 
             DECLARE @fullTriggerName AS NVARCHAR(3000)
             DECLARE @canonTableName NVARCHAR(3000) 
             DECLARE @quotedTableName NVARCHAR(3000) 
             /* Create the trigger name */ 
             SET @triggerName = REPLACE(@tableName, '[', '__o__') 
             SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
             SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
             SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 
             /* Create the cannonicalized table name for trigger creation */ 
             /* Do not touch it if the name contains other delimiters */ 
             IF (CHARINDEX('.', @tableName) <> 0 OR 
                 CHARINDEX('[', @tableName) <> 0 OR 
                 CHARINDEX(']', @tableName) <> 0) 
                 SET @canonTableName = @tableName 
                 SET @canonTableName = '[' + @tableName + ']' 
             /* First make sure the table exists */ 
             IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL 
                 RAISERROR ('00000001', 16, 1) 
             BEGIN TRAN
             /* Insert the value into the notification table */ 
             IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName) 
                 IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName) 
                     INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
                     VALUES (@tableName, GETDATE(), 0)
             /* Create the trigger */ 
             SET @quotedTableName = QUOTENAME(@tableName, '''') 
             IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR') 
                 IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR') 
                     EXEC('CREATE TRIGGER ' + @fullTriggerName + ' ON ' + @canonTableName +'
                           FOR INSERT, UPDATE, DELETE AS BEGIN
                           SET NOCOUNT ON
                           EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' + @quotedTableName + '
             COMMIT TRAN

    The offending code is:

    SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 

    What’s happening is that the stored procedures that are created by the aspnet_regsql tool do not handle schemas, which are new to 2005. It automatically assumes you’re using the default schema “dbo”. I’ve modified Microsoft’s stored procedure to be able to handle schemas gracefully.

    All you need to do is change the DECLARE block and the “Create the trigger name” blocks to:

    DECLARE @triggerName AS NVARCHAR(3000) 
    DECLARE @fullTriggerName AS NVARCHAR(3000)
    DECLARE @canonTableName NVARCHAR(3000) 
    DECLARE @quotedTableName NVARCHAR(3000) 
    DECLARE @schemaName NVARCHAR(3000)
    /* Detect the schema name */
    IF CHARINDEX('.', @tableName) <> 0 AND CHARINDEX('[', @tableName) = 0 OR CHARINDEX('[', @tableName) > 1
        SET @schemaName = SUBSTRING(@tableName, 1, CHARINDEX('.', @tableName) - 1)
        SET @schemaName = 'dbo'
    /* Create the trigger name */
    IF @schemaName <> 'dbo'
        SET @triggerName = SUBSTRING(@tableName,
            CHARINDEX('.', @tableName) + 1, LEN(@tableName) - CHARINDEX('.', @tableName))
        SET @triggerName = @tableName
    SET @triggerName = REPLACE(@triggerName, '[', '__o__') 
    SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
    SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
    SET @fullTriggerName = @schemaName + '.[' + @triggerName + ']'

    If you’re lazy or you just want to go ahead and fix it, here are links to the full original Microsoft version and my modified version that works. Just run the code in my modified version after you enable the database and then you should be able to enable any table, including those that have a schema.

    If anyone finds problems with these, please let me know. I tried to test them, but it’s possible there might be scenarios for which I’ve not accommodated.