sql

  • 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.


  • Out of Memory Exception While Attempting to Do SQL CLR

    Update: We figured out how to make it work with the help of our DBA and Jonathan Kehayias (see comments). We increased SQL Server’s MEM_TO_LEAVE property, by adjusting the –g command line switch for the service, to 448MB. This increase of the shared memory pool gave SQL Server enough breathing room for its worker threads, and now we are able to return 1536 records in 1 minute 21 seconds, including the Bitmap conversion.

    Furthermore, we found a workaround using our reporting engine, which we will implement when we have some breathing room ourselves in our rollout timeline. This will “get it working” for now, which is the requirement handed down from above. Soon though, we will have this working in our application tier, where it belongs.

    SQL Server’s CLR abilities are really cool. I have done some benchmarking, performing the same computations in both T-SQL and CLR and I have found CLR to outperform T-SQL by factors of greater than 10 to 1. It’s fantastic for this use.

    Unfortunately, it has won a battle against me today. I’ll provide a bit of background first. I am currently working on the Point of Sale component of my company’s pharmacy system, and in particular, I am writing all of the code associated with interacting with the signature pad. I’ve abstracted everything nicely, such that we can support multiple pad’s, and I just have to write a .Net class that implements the ISignaturePad interface.

    Obviously, other than the navigational aspects of buttons and listboxes and sale line items being displayed on the pad, capturing signatures themselves is paramount. Because each signature pad can spew out the signature data in a different way, and we want to store the “perfect” vector information, I’ve abstracted the signature data into two classes. The Signature and SignaturePoint classes’ definitions look like this:

        [Serializable]
        public partial class Signature
        {
            public virtual SignaturePoint[] Points { get; protected set; }
            public virtual SignaturePoint TopLeft { get; set; }
            public virtual SignaturePoint BottomRight { get; set; }
            public int Width
            {
                get
                {
                    return BottomRight.X - TopLeft.X;
                }
            }
            public int Height
            {
                get
                {
                    return BottomRight.Y - TopLeft.Y;
                }
            }
            public int XDpi { get; set; }
            public int YDpi { get; set; }
    
            private Signature()
            {
            }
    
            public static Signature CreateFromTT8500String(string signaturePoints)
            {
                var sig = new Signature();
                sig.Points = sig.CreateFromTT8500PointsData(signaturePoints);
                return sig;
            }
    
            public virtual byte[] Serialize()
            {
                using (var ms = new MemoryStream())
                {
                    var bf = new BinaryFormatter();
                    bf.Serialize(ms, this);
                    return ms.ToArray();
                }
            }
    
            public static Signature Deserialize(byte[] serializedSignatureBytes)
            {
                using (var ms = new MemoryStream(serializedSignatureBytes))
                {
                    var bf = new BinaryFormatter();
                    return (Signature)bf.Deserialize(ms);
                }
            }
    
            protected virtual SignaturePoint[] CreateFromTT8500PointsData(string signaturePoints)
            {
                List list = new List();
    
                // Do a lot of work here to change the strange format that we get
                // as a string into bytes and transform them into an array of my custom class
    
                return list.ToArray();
            }
    
            protected void CropPoints(List list)
            {
                foreach (var point in list)
                {
                    point.X -= TopLeft.X;
                    point.Y -= TopLeft.Y;
                }
    
                BottomRight.X -= TopLeft.X;
                BottomRight.Y -= TopLeft.Y;
                TopLeft.X = 0;
                TopLeft.Y = 0;
            }
        }
    
        [Serializable]
        public class SignaturePoint
        {
            public int X { get; set; }
            public int Y { get; set; }
            public bool PenUp { get; set; }
    
            public SignaturePoint()
            {
            }
    
            public SignaturePoint(int x, int y)
                : this(x, y, false)
            {
            }
    
            public SignaturePoint(int x, int y, bool penUp)
                : this()
            {
                X = x;
                Y = y;
                PenUp = penUp;
            }
    
            public Point ToPoint()
            {
                return new Point(X, Y);
            }
    
            public override string ToString()
            {
                return ToPoint().ToString();
            }
        }

    As you might have noticed, Signature is marked as Serializable, and that’s exactly what we’re doing to store the “perfect” information in the database. We call the Serialize() method on my Signature class, and store the resulting byte array in the database as VARBINARY(MAX). It works fine when we pull that back with ADO.NET and re-hydrate a Signature object with my static Deserialize() method.

    To actually draw a signature on a picture box on a Windows form for example, we call my ToBitmap() method, that is in another file (partial class), and it generates a bitmap of the requested width, height, and pen width, suitable for display on a receipt, screen, report, etc.

    However, as a limitation of our ridiculous reporting engine (and we are currently trying to work around it’s oddities), for an upcoming beta, we are trying to get SQL server to create the bitmaps for passing back up to our reporting engine. Yes, I do know that is application tier logic and shouldn’t be performed at the database level. We are still trying to work around it using custom controls with the reporting engine.

    So, I create a SQL CLR scalar function in my Signature class (SqlBytes CreateBitmap(SqlBytes serializedSignatureBytes, SqlInt32 width, SqlInt32 height)), moved Signature and SignaturePoint to a CompanyName.SignaturePad.Common assembly, added a reference to System.Drawing. I added the assembly to SQL Serverand fought it a bit (setting TRUSTWORTHY to ON for the database). I had to manually add System.Drawing as well, because the version on my computer didn’t match exactly on the server, yet another pain and indication I shouldn’t be doing this. And another indication was SQL server warning me that System.Drawing hadn’t been tested and that the universe will indeed explode if they change something in it. I accept the risks, at the moment.

    Everything worked great… for 3 signatures. As soon as the 4th signature is added, I get this:

    Msg 6532, Level 16, State 49, Line 1
    .NET Framework execution was aborted by escalation policy because of out of memory.
    System.Threading.ThreadAbortException: Thread was being aborted.
    System.Threading.ThreadAbortException:
       at System.Drawing.Graphics.CheckErrorStatus(Int32 status)
       at System.Drawing.Graphics.DrawImage(Image image, Int32 x, Int32 y, Int32 width, Int32 height)
       at CompanyName.SignaturePad.Common.Signature.ToBitmap(Int32 width, Int32 height, Int32 penWidth, Color foregroundColor, Color backgroundColor)
       at CompanyName.SignaturePad.Common.Signature.ToBitmap(Int32 width, Int32 height)
       at CompanyName.SignaturePad.Common.Signature.CreateBitmap(SqlBytes serializedSignatureBytes, SqlInt32 width, SqlInt32 height)

    Looking at that code, I’m doing everything that I know to do. I’m making sure to Dispose() all Bitmaps, Graphics, Streams, etc. I even tried explicitly setting this to null. SQL Server just runs out of memory after about 3 signatures. I’m not sure if this is a side effect of its execution plan (perhaps forking to multiple workers?).

    So… now I’m stuck, and I’m not really sure what to do, until we get a workaround in the reporting engine, which someone else is currently working on. I’ll update this post if we get a working solution.


  • 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:
    dbo.AspNet_SqlCacheRegisterTableStoredProcedure

    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) 
             AS
             BEGIN
             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 
             ELSE 
                 SET @canonTableName = '[' + @tableName + ']' 
             /* First make sure the table exists */ 
             IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL 
             BEGIN 
                 RAISERROR ('00000001', 16, 1) 
                 RETURN 
             END 
             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 + '
                           END
                           ')
             COMMIT TRAN
             END
    

    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)
    ELSE
        SET @schemaName = 'dbo'
    /* Create the trigger name */
    IF @schemaName <> 'dbo'
        SET @triggerName = SUBSTRING(@tableName,
            CHARINDEX('.', @tableName) + 1, LEN(@tableName) - CHARINDEX('.', @tableName))
    ELSE
        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.