Todd Carrico

If you read my blog, you know how much I love writing code that writes code.  CodeSmith is my favorite tool for this, and I just found a way for you to get it for free!!

http://weblogs.asp.net/scottdockendorf/archive/2005/07/26/420584.aspx

get em while you can!!

I just posted a template in the CodeSmith Template Share forum that uses XML with sp_xml_preparedocument, OPENXML, and sp_xml_removedocument to accurately script out data in a SQL Server table.

Some highlights of the template:

• The original table data is copied into a new table for backup.
• If a TargetDatabase is defined, the script will include a "Use" statement.
• If a TargetDatabase is defined, and the AutoExecutescript property is true, the ouput will be executed on the target database
• If the current table has an identity column, the seed is maintained.
• The entire operation is handled in a Serialized Transaction.

This template was built and tested with CodeSmith 3.0

Check it out, and give me some feedback...  how does it compare to the template that ships with CodeSmith?  Anyone got any ideas for enhancing this template to handle Binary data?

tc

My favorite Code Generator just got a huge upgrade... 

A New site and a new release.

Ever wanted more time to get a project done?  This tool has saved me so much time in the last year.  After you get your templates generating your code the way you like, CodeSmith 3.0 will practically make time for you.  CodeSmith 2.6 has paid for itself many times over, and I expect that CodeSmith 3.0 will continue this trend.

Some things I like about CodeSmith 3.0:

XML Properties

• Generate and XSD that defines your structure, and add the property attribute to your template.  The property grid will let you pick an xml file to populate the property.  You can now create your own metadata.  Create your own mapping scheme they way you want it.

Code Completion

• One of the hardest things to pick up with CodeSmith is how powerful the Schema Explorer is.  It was documented in 2.6, but with Code Completion, the learning curve is ¼ what it was in CodeSmith 2.6

Built In Merge Strategies

• If you are not using Active Generation¹, but want more automation than Passive Generation² can give you, then Merge Strategies are your ticket.  Specify regions in your code that are either replaced when the CodeSmith console is run, or are preserved. Either way, it allows the developer the freedom to blend Passive and Active code generation for the ultimate in "Your Code, Your Way"

CodeSmith 3.0 is well worth purchasing at twice the price.  Eric Smith doesn't know what he has.  He has really taken a good tool and made it great.

_{¹Active Generation = The developer will not edit the code generated with the intention of re-generating the code when changes are needed
²Passive Generation = The developer may edit the code generated with no intentions of re-generating if changes are needed. Changes will be made by hand}

This may be an alternative to writing your own logical locking architecture.  Microsoft has provided a couple of hooks into the built in lock manager.  Using the sp_getapplock and sp_releaseapplock, developers are able to implement a custom locking strategy outside of SQL Server internals.  Here is an example of how these procs can be used:

DECLARE
    @RC INT,
    @ResourceName NVARCHAR(255)

-- the Application resource that we are trying to "lock"
SET @ResourceName = 'TableName'

-- lock the resource so that other users must wait. We will wait for a second.
EXEC @RC = master.dbo.sp_getapplock
    @Resource = @ResourceName,
    @LockMode = 'Exclusive',
    @LockOwner = 'Session',
    @LockTimeout = 1000

-- Possible return codes 0, 1, -1, -2, -3, -999 (see BOL [sp_getapplock] for explanations)
IF @RC NOT IN (0,1)
BEGIN
   RAISERROR( 'Timeout getting the Resource Lock', 16, 1 )
   GOTO ErrorHandler
END

    /** Do some work here **/
    -- note, you should see a lock Type == App in this result set
    exec sp_lock

-- clean up as soon as another user can get access to the resource
EXEC @RC = sp_releaseapplock
    @Resource = @ResourceName,
    @LockOwner = 'Session'

ErrorHandler:
    PRINT 'Skipped work'

This is some code I use for removing duplicate rows from a table based on Primary Keys.  It could be easily modified for other scenarios.  The end result is a clean table.

CREATE TABLE #Dups(
    PkUniquevalue INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
    cnt INT NOT NULL,
    BannerID INT
)

INSERT INTO #Dups ( cnt, BannerID )
SELECT
    COUNT(*),
    BannerID
FROM dbo.Banner
GROUP BY BannerID
HAVING COUNT(*) > 1

IF @@ROWCOUNT > 0
BEGIN

    DECLARE @PkUniquevalue INT
    DECLARE @RowsToKill INT 
    DECLARE @BannerID int 

    SET @PkUniquevalue = 0 

    WHILE (1=1) 
    BEGIN 

        SELECT TOP 1 
            @RowsToKill = cnt - 1, 
            @BannerID = BannerID, 
            @PkUniquevalue = PkUniquevalue 
        FROM #Dups 

        IF @@ROWCOUNT > 0 
        BEGIN 
            SET ROWCOUNT @RowsToKill 
            DELETE FROM dbo.Banner
            WHERE BannerID = @BannerID 
            SET ROWCOUNT 0 
        END 
        ELSE 
        BEGIN 
            BREAK 
        END 
        DELETE FROM #Dups WHERE PkUniquevalue = @PkUniquevalue 

    END    
END

I recently had an issue automating the creation of SQL Agent Jobs.  I need to set the Log File properties of the job steps, and was setting the path to a local share on the SQL Server.  No problem you say:

'\\' + @@SERVERNAME + '\Logs' 

should do the trick.

The problem came when I moved the code to a named instance of SQL Server.  @@SERVERNAME returns "<ServerName>\<InstanceName>".  So I briefly thought about parsing @@SERVERNAME, and figured there had to be a better way.  As it turns out, there is; it is called SERVERPROPERTY.

'\\' + CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName')) + '\Logs'

SERVERPROPERTY returns a sql_variant, so you might have to cast it into something you can use without type conversion issues.

The information you can get with SERVERPROPERTY is interesting:

• Collation
• Edition
• Engine Edition
• InstanceName
• IsClustered
• IsFullTextInstalled
• IsIntegratedSecurityOnly
• IsSingleUser
• IsSyncWithBackup
• LicenseType
• MachineName
• NumLicenses
• ProcessID
• ProductVersion
• ProductLevel
• ServerName

Just thought I would share some exciting (for me at least) news about SQL Server Management Studio.  While it did lock up on me, it had also made an auto save copy of the script I was working with.  When I restarted SMS Studio, it asked me if I wanted to recover the script.

Query analyzer would lock up me as well, but never asked me to recover script files.  Very cool feature SQL Team!!!

 

Yay!!!  Edit and Continue in C#.

I realize this is a SQL blog, but this will benefit Database developers as well.  Read more at:
http://blogs.msdn.com/somasegar/archive/2004/10/15/242853.aspx

Now if we could just get the SQL Team to add Intelisense into the SQL Management Studio... 

If you are new to code generation (even if you are not!) you need to get a copy of CodeSmith 2.6.  This app is the cats meow when it comes to templated code generation.  Eric Smith has really out done himself (is it really just one guy??).  Here are the features he listed in his release email:

- Syntax highlighting of both template and target languages.
- Much improved Visual Studio .NET custom tool.
- Outlining support.
- Line modification markers.
- CodeSmith Studio is now a single instance application.
- Improved compiler and template execution performance.
- Better Unicode support.
- Tons of other minor improvements and bug fixes.

He left out many more, but my favorite has got to be the “Template Code Expansion”.  Imagine an ASP 3.0 page with code and HTML intermingled.  Press Ctrl+Shift+M and watch the code collapse into hidden regions just like ASP.Net.  Very cool, and CodeSmith supports code behind as well.  As matter of fact, CodeSmith allows you take advantage of the full .NET framework to help you generate _YOUR_ code. 

Think about what ASP.Net has done for generating HTML?  Sure you could write that HTML by hand, but it really gets powerful when you can generate an HTML table row for each row in a table.  CodeSmith brings this same paradigm to code generation.

Nothing is cooler than code that writes code, and CodeSmith is the king of code generation.  Watch out for tips on how I use CodeSmith to help me get my projects done…

A buddy of mine pointed me to a great article on writing cool CodeSmith templates:
http://msdn.microsoft.com/library/en-us/dnhcvs04/html/vs04e5.asp

The author does a great job of covering the basics, and then gets into more complicated issues such as sub templates and custom property editors (like for picking a file using an OpenFile dialog box instead of requiring the user to type in the path to a file).

Very cool read.

Ok, I promised to blog about my replacement for the snapshot replication.  Well, it is not really a replacement, but more of a work around for the built in snapshot (Sorry do dissapoint. I promiss it is worth the read <grin>)

In our environment, we have some large transactional tables that we replicate to a reporting environment.  We all know that the reporting environment can sometimes get less than stellar hardware.  As a result, our reporting environment was pretty fragile.  Add to that an entire company of users that were banging on it, with some pretty ugly SQL I might add, and you end up with an environment that lends itself to needing an article to be re-pushed when you least expect it.

The built in snapshot will lock your table while it does the push (Concurrent Snapshot was added after we had figured this out.  We tried the shiny new concurrent snapshot, but it didn't work until SP2 I believe.  We have not tried it since...)  What we needed was a way to snapshot the data using an uncommitted isolation level to keep the locks to a minimum.  The CIO gets a little peeved when you have to bring your site down to push replication to your reporting environment...  However, uncommitted isolation has it's issues too, namely duplicate records.  It is easy to fix, just clean out the duplicates before you create your unique indexes.

So we created a DTS for the table we are going to push and use the nolock query hint to get the data.  When moving around large amounts of data, I like to truncate and drop the table (Dropping a table is a logged operation, Truncating is not ;) ).  Then move the data and then recreate your indexes.  At the end of this process, you now have a DTS package that will push your data, without locking your base table.  Do not run this yet.  You need to create the subscription before pushing the data.  This allows for no gaps in the data.  If you were to push the data, then create the subscription, you would have a whole in your data if records were new or updated between the push and the creation of the subscription.  Since the push uses a dirty read, we affectionately call this a "Dirty Push".

The next step is to create your publication.  I like to create one publication per article.  It makes troubleshooting and management very easy.  If you have to re-initialize an article, you have to re-initialize the publication.  Re-initializing the publication will re-push all the articles in that publication. That might get ugly in situations where all or some of the articles are large.  After you have created your publication it is time to create your subscription(s).

You will create one subscription per subscriber for your publication.  When you get to the “Initialize Schema” section of the wizard, select the radio button for "No, the Subscriber already has the schema and data".  This tells SQL Server to forego the snapshot and just start sending data.  We want to stop the distribution agent for that subscriber so that new rows are not being inserted at the subscriber just yet.  At this point records will start collecting in the Distribution.dbo.ms_ReplCommands table for your subscriber. 

Now, we can push the data using our shiny new DTS package.  Now for those of you paying attention, we have just created another problem.  We are queuing data to go to the subscriber in ms_replCommands.  We are also going to push this same data to the subscriber in our DTS.  If you have worked with replication at all, you will recognize this real quick.  The stored procedures that SQL Server creates at the subscriber will not handle this very well.  They will try to insert data that already exists, or update records that don't exist etc.  and raise an error, effectively breaking replication.  In the next entry, I will go into detail what we do in these stored procedures to ensure that replication continues, and still maintain data integrity.

 

I was playing around with an idea for using extended properties to hold metadata regarding the Source Control version of an object.  We manage our DB objects in Vault, and it is always difficult to verify that the DB and Vault are in sync.

My idea was to add some code to the bottom of each object creation script that took the SC expanded keyword $Revision: $ and used to it build an extended property.  Then I decided to make it work in SQL 2005 for grins :)

Here is the SQL 2000 version:

IF EXISTS(SELECT * FROM dbo.sysproperties WHERE id = OBJECT_ID('Configuration') AND name = 'SCVersion')     EXEC dbo.sp_dropextendedproperty N'SCVersion', N'user', N'dbo', N'table', N'Configuration' DECLARE @v NVARCHAR(20); SET @v = CAST(REPLACE(REPLACE('$Revision: 1 $', 'Revision:', ''), '$', '') AS INT)     EXEC dbo.sp_addextendedproperty N'SCVersion', @v, N'user', N'dbo', N'table', N'Configuration' -- SELECT * FROM dbo.sysproperties WHERE id = OBJECT_ID('Configuration') AND name = 'SCVersion'

For SQL 2005:

IF EXISTS(SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID('Configuration') AND name = 'SCVersion')     EXEC dbo.sp_dropextendedproperty N'SCVersion', N'user', N'dbo', N'table', N'Configuration' DECLARE @v NVARCHAR(20); SET @v = REPLACE(REPLACE('$Revision: 1 $', 'Revision:', ''), '$', '')     EXEC dbo.sp_addextendedproperty N'SCVersion', @v, N'user', N'dbo', N'table', N'Configuration' -- SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID('Configuration') AND name = 'SCVersion'

My group thought this was pretty cool, so I thought I would share it with the world ;)

CREATE PROCEDURE _fooUpd (     @Param1 INT = NULL OUTPUT,     @Param2 INT = NULL OUTPUT,     @PK INT ) AS BEGIN     UPDATE dbo.foo SET         @Param1 = Col1,         @Param2 = Col2,         Retreived = Retreived + 1     WHERE PK = @PK END

The idea is to perform the parameter population, and the update with the same “hit” to the table.  The alternative is an UPDATE follwed by a SELECT...  Some purists might argue that this is hard to read... I do not agree with that, but I thought I would take the wind out of there sails ;)  Sorry, this trick does not work for SELECT statements.  You have to select all columns into variables, or return rowsets :(

A couple of friends of mine have been asking me to blog about some of the things I have done with custom replication in SQL 2000.  I figured a good place to start would be with some basic principles about replication, and then dive deeper into the guts of what I have done over a few blog entries.  Hopefully I won’t bore anyone.  This post will cover the basics of Transactional Replication.  It has been trimmed down for brevity, so please no complaints about “oh you left this out…” you can comment about it yourself and fill it in.

Microsoft SQL Server 2000 has three flavors of replication.  Snapshot, Transactional, and Merge.  For this series (if you can do that in a blog ;) I will focus on Snapshot and Transactional replication.

To start with, Microsoft uses a magazine metaphor for describing the parts of replication.  You start off with a publication and add article(s).  This publication is then subscribed to, and then distributed to the particular subscribers on some schedule (yes including continuous).
 
After you have defined a Subscription, you must synchronize the subscriber with the publisher.  This is where the snapshot comes in.  When you create the subscription, one of the questions you are asked is whether you want to start the snapshot agent.  Selecting yes will start the agent after the wizard is finished.  The snapshot agent will script out the table, copy the script over and BCP (ok I am not sure if it is BCP, or DTS.  It gets the data there) and put down three stored procedures used by the distribution agent.  If you did not select yes, do not worry.  The distribution agent will not start sending data until it thinks the data is synchronized.

Now, data gets synchronized by a couple of different methodologies.  The snapshot replication, backup and restore, or BCP.  Now I assume that Snapshot replication is the preferred method for synchronizing, but we found a few issues with it that I will go into later in the series.  Suffice to say, the distribution agent must “think” that the data is in sync.  When you create the subscription, you can tell SQL Server that the subscriber already has the data, and SQL Server will take your word for it.  Isn’t that special…

While the wizard was creating the snapshot agent job, it also created a couple of other agent jobs.  The wizard also created the distribution agent job and the logreader agent job.  The logreader job does exactly what it sounds like.  It reads the transaction log and creates records in a table called ms_ReplCommands in the Distribution database.  This table is the queue that the distribution agents use to serialize the commands that get the data into the subscriber.  This job is the conduit that data will flow to the subscriber.

Ok, at this point you have a rough idea of the objects involved.  At least the objects that we are going to fiddle with.  Stay tuned because I am going to blog about what I don’t like about the snapshot agent, and how I get around it.  On the way you learn some cool things about replication with SQL Server 2000.