Replication, Restores, and Reseeding Identity Columns

I recently ran into an issue where, after restoring a replicated database, all the identity columns were seeded to start over at 1.  To make sense of my situation, I'll describe it a bit first.

Situation
I have database servers in multiple geographic locations: our production server is off-site while our development environment is on-site.  Being a smaller company, our resources to get high-speed lines into our office are limited and not real high priority right now.  I do, however, have a replication subscriber on-site that subscribes to our production database - works great.

Problem
In order to have a fresh testing environment, I wanted to restore our production database into our development environment each morning so developers had fresh live data to work against.  Problem is, to copy a production backup from our remote location on-site would take a half day whilst consuming most of our bandwidth.

Solution
I decided to run a backup of our on-site replicated database and restore directly from that backup.  Works great - takes 15 minutes or so.  However, as described earlier, the identity columns are not seeded properly - developers were getting primary key violation errors because of this.

Here is how you can resolve this issue in a single script, which for me I included as a step in my restore process.  Basically I'm reseeding all the user tables that have identity columns.

SET NOCOUNT ON

DECLARE @table_name VARCHAR(200),
    @hasidentity TINYINT

DECLARE table_cursor CURSOR
    FOR SELECT  TABLE_NAME
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
                AND TABLE_CATALOG = 'database-name'
        ORDER BY TABLES.TABLE_NAME

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @hasidentity = OBJECTPROPERTY(OBJECT_ID(@table_name),
                                              'TableHasIdentity')
        IF @hasidentity = 1
            BEGIN
                DBCC CHECKIDENT ( @table_name, RESEED ) 
            END                           
       
        FETCH NEXT FROM table_cursor INTO @table_name
    END
CLOSE table_cursor
DEALLOCATE table_cursor

SQL Server: Map Orphaned Users

I've put together the following stored procedure that will map ''orphaned" users in a database.  This often happens when you restore a database into a new environment.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_map_orphaned_db_users]    Script Date: 10/31/2009 19:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
 * PROCEDURE NAME:  sp_map_orphaned_db_users
 * DESCRIPTION:
 * Maps orphaned database users to server logins of the same name.
 *
 */
ALTER PROCEDURE [dbo].[sp_map_orphaned_db_users]
(
 @with_report BIT = NULL
,@database NVARCHAR(128) = NULL
,@report_only BIT = NULL /* Pass NULL or 0(zero) to execute and 1 to show actions that would be taken. */
)
AS
BEGIN

    SET NOCOUNT ON

    SELECT
        @report_only = ISNULL(@report_only, 0)
       ,@database = ISNULL(RTRIM(LTRIM(@database)), '')
       ,@with_report = ISNULL(@with_report, 0)

    DECLARE
        @UserName SYSNAME
       ,@dbname SYSNAME
       ,@sql NVARCHAR(4000)
       ,@orphanTableName NVARCHAR(128)

    SELECT
        @orphanTableName = '##orphans_' + REPLACE(CAST(NEWID() AS NVARCHAR(128)), '-', '')

    CREATE TABLE #orphans
    (
     UserName SYSNAME
    ,UserSID VARBINARY(85)
    ,dbname SYSNAME
    )

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END
        CREATE TABLE ' + @orphanTableName + '
        (
            UserName SYSNAME,
            UserSID VARBINARY(85),
            dbname SYSNAME
        )'
    EXEC (@sql)
   
    IF @database = ''
        BEGIN

            SELECT
                @sql = N'
            USE [?]
            CREATE TABLE #report
            (
                UserName SYSNAME,
                UserSID VARBINARY(85)
            )

            INSERT INTO #report
            EXEC sp_change_users_login @Action=''Report''

            ALTER TABLE #report
            ADD dbname SYSNAME NULL

            UPDATE #report
            SET dbname = ''?''

            INSERT INTO ' + @orphanTableName + '
            SELECT
                UserName,
                UserSID,
                dbname
            FROM #report'

            EXEC sp_msforeachdb @sql
        END
    ELSE
        BEGIN

            SELECT
                @sql = '
        USE [' + @database + ']
        CREATE TABLE #report
        (
            UserName SYSNAME,
            UserSID VARBINARY(85)
        )

        INSERT INTO #report
        EXEC sp_change_users_login @Action=''Report''

        ALTER TABLE #report
        ADD dbname SYSNAME NULL

        UPDATE #report
        SET dbname = ''' + @database + '''

        INSERT INTO ' + @orphanTableName + '
        SELECT
            UserName,
            UserSID,
            dbname
        FROM #report'

            EXEC (@sql)

        END

    SELECT
        @sql = N'
        INSERT INTO #orphans
        SELECT
            UserName,
            UserSID,
            dbname
        FROM ' + @orphanTableName

    EXEC (@sql)

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END'
    EXEC (@sql)

    DECLARE @userCount INT

    SELECT
        @userCount = COUNT(*)
    FROM
        #orphans o
        INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    DECLARE rc CURSOR LOCAL FAST_FORWARD
        FOR SELECT
                o.UserName
               ,o.dbname
            FROM
                #orphans o
                INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    OPEN rc

    FETCH NEXT FROM rc INTO @UserName, @dbname

    WHILE @@FETCH_STATUS = 0
        BEGIN

            SELECT
                @sql = 'USE ' + @dbname + ' EXEC sp_change_users_login @Action=''update_one'', @UserNamePattern=''' + @UserName + ''', @LoginName=''' + @UserName + ''''
            IF @report_only = 1
                BEGIN
                    PRINT 'WOULD HAVE ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END
            ELSE
                BEGIN
                    EXEC (@sql)
                END

            IF @with_report = 1
                AND @report_only = 0
                BEGIN
                    PRINT 'ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END


            FETCH NEXT FROM rc INTO @UserName, @dbname

        END

    IF (@userCount > 0)
        BEGIN
            PRINT CAST(@userCount AS NVARCHAR(50)) + N' USERS PROCESSED.'
        END
    ELSE
        BEGIN
            PRINT 'NO ORPHANED USERS TO PROCESS.'
        END


    CLOSE rc
    DEALLOCATE rc
END

How to move an object from one schema to another

There are various reasons you may wish to move an object to a different schema.  The reason may be for security purposes or simply for logical grouping.  No matter the reason, the syntax is rather simple:

ALTER SCHEMA newschema TRANSFER  oldschema.objectname

Took me a bit to find this piece of information, so I found it a worthy post!

Stored Procedure: Dynamic IN Clause Solution

I'm sure many database programmers have run across the issue of wanting to pass a delimited string of values as a parameter, then using that in your IN clause.  Example:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)

AS

SELECT *

FROM table

WHERE column IN (@StringOfValues)

To execute, you do the following:
EXEC MyProc '1,2,3'

If the column data type is an integer, you will likely get the error "Conversion failed when converting the varchar value '1,2,3' to data type int."  Now, you could certainly create a string for the SQL statement - which is a pain to say the least - then EXEC(thestring), but let's look at a nice alternative to that.

First, you will need a table valued function that allows you to pass in a delimited list of values:

CREATE FUNCTION [dbo].[ParamParser]
    (
     @delimString VARCHAR(255)
    ,@delim CHAR(1)
    )
RETURNS @paramtable TABLE ( id INT )
AS BEGIN
    DECLARE @len INT
       ,@index INT
       ,@nextindex INT

    SET @len = DATALENGTH(@delimString)
    SET @index = 0
    SET @nextindex = 0

    WHILE ( @len >= @index )
        BEGIN

            SET @nextindex = CHARINDEX(@delim, @delimString, @index)

            IF ( @nextindex = 0 )
                SET @nextindex = @len + 2

            INSERT  @paramtable
                    SELECT  SUBSTRING(@delimString, @index, @nextindex - @index)

            SET @index = @nextindex + 1

        END
    RETURN
   END

Now that you have that table valued function, you can rewrite your stored procedure as such:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)

AS

SELECT *

FROM table

INNER JOIN dbo.ParamParser(@StringOfValues,',') tvf ON table.column = tvf.id

When you then execute your stored procedure call, you get back the results as expected - filtered accordingly on your delimited string of values.

SQL 2008 Replication - Convert from TEXT to VARCHAR(MAX)

SQL Server Replication has evidently come a long way since I began using it. Back in the day it seemed like every action was equivalent to a visit to the dentist, but today after hours of searching and digging, I found the solution to my problem was actually quite simple.

The task at hand was that that I had three columns that I wanted to convert from TEXT data types to VARCHAR(MAX).

First I tried the obvious: simply changing the data type via the SSMS GUI, but received the following error "Unable to modify table. Cannot drop the table 'tablename' because it is being used for replication." Strike one.

Next, I tried to script out my changes, but SSMS gave me a pretty awful script that basically created a temporary table, migrated the data, dropped the original, and renamed the temporary table back. That just didn't sound like a sound plan in a replicated environment.

Then I started investigating the replication stored procedures to see if there was one that allowed me to change the properties of an article (table in this case), to no avail.

Continuing on, I considered removing the article from the publication, modifying the column, then adding it back into the publication. Now, while I figured this method would work, I wanted to try one last thing: I did recall reading that running the raw ALTER TABLE ALTER COLUMN command may work, and having not tried that, I gave it a shot.

I opened a new query window and ran the following:
ALTER TABLE tablename ALTER COLUMN columnname VARCHAR(MAX)

BINGO! I checked my subscribers and within seconds they too inherited the schema change (which BTW is an option you can select on the publication properties).

Too easy... but had to share. Hopefully it sames someone some time down the road.