2006/09/18

How to download more than 2 files simultaneously using Internet Explorer

As stated in 8.1.4 Practical Considerations from RFC2616:
Clients that use persistent connections SHOULD limit the number of simultaneous connections that they maintain to a given server. A single-user client SHOULD NOT maintain more than 2 connections with any server or proxy. A proxy SHOULD use up to 2*N connections to another server or proxy, where N is the number of simultaneously active users. These guidelines are intended to improve HTTP response times and avoid congestion.
IE follows that suggestion, and it will not open more than two connections to the same server, preventing you from downloading more than 2 files simultaneusly (again from the same server). There is, however, a way to override this behaviour (after all, this is just a suggestion). It's a classic, but it always takes me some time to find it whenever I need to do this registry hack. Here it is:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
"MaxConnectionsPer1_0Server"=Dword:00000008
"MaxConnectionsPerServer"=Dword:00000008
Taking into account that RFC2616 is dated June 1999, and client's bandwidth (and also server's) has grown a lot since then, I think that it should not be a problem increasing that limit from 2 to 6 or 8 files at a time.

2006/09/05

Identity columns in a view treated as an ordinary int column

I was experiencing the behavior shown in KB920974: FIX: SQL Server 2005 treats an identity column in a view as an ordinary int column when the compatibility level of the database is set to 80 and asked Microsoft support for the hotfix opening a new support incident. After some hours, technical staff sent me the links for the files and installed them on a virtualised environment (testing is always a good thing).

After having checked that the problem was solved if the database compatibility level was 80, I changed it back to 90 and the problem appeared again. I did not accept the ms incident as being resolved and still complained about it. They told me that the shown behaviour was BY DESIGN. In other words, if you are using a database with compatibility level 80, your identity fields will still behave as identity when viewed through a view (check the following example in how to reproduce the problem), but if you want to use compatibility level 90, the identity behavior is lost by design if the table is viewed through a view. I could not believe it! Why did they do that?

As stated in SQL Server 2005 BOL regarding Modifying Data Through a View:

You can modify the data of an underlying base table through a view, in the same manner as you modify data in a table by using UPDATE, INSERT and DELETE statements or by using the bcp utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:
  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  • The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
    • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
    • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.
  • The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
  • TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.
[...] The following additional guidelines apply:[...]
  • INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions.

Steps to reproduce the problem: Create a new [test] database in compatibility mode 90 (2005). Define the following two tables: TMaterials and TMeasureUnits with the script:

CREATE TABLE [dbo].[TMeasureUnits](
 [MeasureUnitId] [int] NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_TMeasureUnits] PRIMARY KEY CLUSTERED 
(
 [MeasureUnitId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TMaterials](
 [MaterialId] [int] IDENTITY(1,1) NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
 [MeasureUnitId] [int] NOT NULL,
 CONSTRAINT [PK_TMaterials] PRIMARY KEY CLUSTERED 
(
 [MaterialId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TMaterials] WITH CHECK
  ADD CONSTRAINT [FK_TMaterials_TMeasureUnits] FOREIGN KEY([MeasureUnitId])
  REFERENCES [dbo].[TMeasureUnits] ([MeasureUnitId])
GO
ALTER TABLE [dbo].[TMaterials] CHECK CONSTRAINT [FK_TMaterials_TMeasureUnits]
GO
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (1, 'Units')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (2, 'Boxes')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (3, 'm.')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (4, 'Kg.')

Now we will create the offending view:

CREATE VIEW [dbo].[VMaterials]
AS
SELECT dbo.TMaterials.MaterialId,
       dbo.TMaterials.Description,
       dbo.TMaterials.MeasureUnitId,
       dbo.TMeasureUnits.Description AS MeasureUnit
FROM dbo.TMaterials INNER JOIN
     dbo.TMeasureUnits ON dbo.TMaterials.MeasureUnitId = dbo.TMeasureUnits.MeasureUnitId
GO

Now execute:

SELECT name, is_identity
FROM sys.columns
WHERE object_id = object_id('VMaterials')

And you will see that NONE of the fields have is_identity = 1. This behavior is told to be BY DESIGN if database compatibility level 90. The fix is only intended to restore original behavior when database is in level 80 (2000).

The real problem: Despite this different behavior, the view is still insertable using T-SQL. Our real problem then, is due to MS Access.

We are using MS Access 2003 as front-end for our SQL Server 2005 corporate database. With such a new behavior, MS Access 2003 is not able to do INSERTs in such views. When MS Access asks SQL Server for view's metadata (to know field types, lengths, primary keys, nullable fields, and identities), SQL incorrectly returns that the identity field is not an identity field anymore:

  1. Create a new MS Access 2003 database project and connect to your server and database.
  2. Change to see Queries objects and open VMaterials (note that the expected first field should be marked as (Autonumeric) and it is not).
  3. Try to insert a new record:
    • If you try to leave MaterialId blank, and enter Description='Sand', MeasureId=2, you will get the error: Invalid input parameter values. Check the status values for details.Invalid input parameter values. Check the status values for details.
    • If you try to give any value to MaterialId and Description='Sand', MeasureId=2 as before, you get the following error message: Cannot insert explicit value for identity column in table 'TMaterials' when IDENTITY_INSERT is set to OFFCannot insert explicit value for identity column in table 'TMaterials' when IDENTITY_INSERT is set to OFF

Up to now, our incident is still opened and no solution nor workaround has been given yet (apart from the obvious fall back to compatibility level 80).

2006/09/04

Numbered stored procedures will be deprecated

I was just browsing the BOL regarding CREATE PROCEDURE and found some words about numbered stored procedures:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
I have some of them in my corporate application. The purpose is to make somewhat like (or behaves like) overloaded functions and methods in OOP (object oriented programming), i.e:
void foo(int x) {
   printf("x is %d\n", x);
}
void foo(char *x) {
   printf("x is '%s'\n", x);
}

In T-SQL we would have:

CREATE PROCEDURE [dbo].[foo];1 @x int AS
  PRINT 'x is ' + CONVERT(varchar(8), @x)
GO
CREATE PROCEDURE [dbo].[foo];2 @x char AS
  PRINT 'x is ' + @x
GO

The fact is that in order to call those stored procedures you need to know the 'suffix' to be applied, such as:

EXEC foo;1 33
-- x is 33
EXEC foo;2 'a'
-- x is a
EXEC foo;1 'a'
--Msg 8114, Level 16, Status 1, Procedure foo, Line 0
--Error converting data type varchar to int.

Thus makes this feature useless from the point of view of behaving like OOP overloaded functions. For that reason and, since Microsoft has planned to deprecate numbered stored procedures for next releases of SQL Server, I will remove them from my application (and so should you do too). The easiest way to achieve the same functionality with a minimum effort is to make:

CREATE PROCEDURE [dbo].[foo_1] @x int AS
  PRINT 'x is ' + CONVERT(varchar(8), @x)
GO
CREATE PROCEDURE [dbo].[foo_2] @x char AS
  PRINT 'x is ' + @x
GO

If you want to know if your database uses numbered stored procedures somewhere you can execute this query:

SELECT DISTINCT sys.objects.name, sys.sql_modules.definition
FROM sys.sql_modules
  INNER JOIN sys.objects on sys.sql_modules.object_id = sys.objects.object_id
WHERE sys.sql_modules.definition LIKE '%;1%'
   OR sys.sql_modules.definition LIKE '%;2%'
   OR sys.sql_modules.definition LIKE '%;3%'

2006/09/01

How to deploy foreign keys to subscribers that are using queued updates

Scenario: Transactional replication with queued updates. 'Subscriber wins' conflict resolution policy ( exec sp_addpublication ... @conflict_policy = N'sub wins' ...). Several publications are used to deploy database instead of a single big publication. Since publications are queued, subscribers must know about foreign key (FK) restrictions in order to prevent users from breaking the rules, thus referential integrity must be replicated to subscribers. The command to create articles matching the former requirements is:

exec sp_addarticle ... @schema_option = 0x0000000008035FDF ...

and a sparse explanation of @schema_option bitmask is:

0x0000000008035FDF
         0x8000000 Create any schemas not already present on the subscriber.
           0x10000 Replicates CHECK constraints as NOT FOR REPLICATION ...
           0x20000 Replicates FOREIGN KEY constraints as NOT FOR REPLICATION...
            0x4000 Replicates UNIQUE constraints. Any indexes related to the...
            0x1000 Replicates column-level collation.
             0x100 Replicates user triggers on a table article, if defined...
             0x200 Replicates foreign key constraints. If the referenced table ...
             0x400 Replicates check constraints. Not supported for Oracle...
             0x800 Replicates defaults. Not supported for Oracle Publishers.
             etc..

You can read more about sp_addarticle and @schema_option in BOL. If you use Management Studio rather than T-SQL you don't have to worry about this bitmask mess: the GUI does everything for you.

Problem: Even though SQL Server 2005 has the option to publish referential integrity among tables in a publication, this option only works for tables that are both inside the publication. If there is any referential integrity that goes TO a table outside the publication or comes FROM a table outside the publication, this foreign key (FK) is not replicated to subscribers.

Besides, if you are using SQL Server 2000, this option is not available at all. If you want to use queued updates, the subscribers must have all foreign constraints applied in order to avoid problems. The stored procedures and semi-automated way of publishing foreign keys described in this document can be used also if you are still using SQL Server 2000.

Subscribers are unaware of those restrictions and there is a chance that a subscriber receives a command that could violate a foreign key on the publisher and still be executed and queued without any warning/error to the user/client application. Afterwards, when the queued command is replicated to the publisher, it is rolled back because it goes against a foreign key that only publisher knows about.

In the following picture you can see a sample of the problem under discussion. There we have 2 publications, QUOTATIONS and ORDERS and there are foreign keys that are completely inside the publications, but there is another one FK_TOrders_TQuotations) that goes beyond the limits of a single publication and starts in ORDERS and finishes in QUOTATIONS.

Orders and Quotations

This kind of foreign keys are not replicated to subscribers even though the option to replicate FKs is enabled. If you are using 'publisher wins' policy, the offending command is rolled back and the original record is restored at the subscriber (seconds or minutes later). The user there is not warned about this fact; he might still think that his changes were commited (but they were not).

On the worst possible scenario, if you are using 'subscriber wins' policy, when the offending command is sent to publisher for replication, it cannot be commited because it breaks the referential integrity, but since 'subscriber wins' is set, the command is not sent back to subscriber to be undone. In this case, subscriber will have the new (but inconsistent) record, and publisher will hold the old (but consistent) record.

After some hours, or even days, if the publication is validated on a scheduled basis using sp_publication_validation, the publication will appear has having validation errors. If the publication is then reinitialised, the subscribers will receive the publishers version of the data (foreign key consistent).

In any case, the changes committed initially at the subscriber are undone without any error nor warning by the time the statements are being executed at the subscriber.

My feedback to Microsoft on this issue: Subscriptions get desynched easily when subscriber uses queued updates and there are foreign keys that point outside the articles in the publication

Solution: The main idea behind this solution is using post-snapshot scripts to create the foreign keys needed at the subcribers that are not replicated automatically by SQL Server.

We must create pre-snapshots scripts also to drop the FKs that might prevent the tables from being dropped and recreated by the initial snapshots at the subscribers.

In our example, in order to successfully publish QUOTATIONS publication, we need first to try dropping FK_TOrders_TQuotations because it points to TQuotations, and since that FK might be already in place, TQuotations could not be dropped in order to be recreated and populated by the initial snapshot (this only applies if the action for in use object is drop and recreate ( exec sp_addarticle ... @pre_creation_cmd = N'drop' ... ).

So, we will need pre-snapshot scripts to drop those FKs that might be already in place and post-snapshot scripts to re-create them.

Is there any way to automate the process of creating those scripts? If the database you are trying to replicate is somewhat big, sure you will have hundreds of tables with at least an average of 3 or 4 foreign keys for each one. Having to manually find and script the creation (and deletion) of those FKs that are beyond the limit of a publication is a headache (at least).

Besides, you will need to mantain those scripts if the schema changes or new tables are created. Having to manually manage pre-snapshot scripts and post-snapshot scripts is not a solution for real environments.

Prerequisites: As commented in the former feedback to Microsoft, there are some conditions that your database must met in order to be able to use the scripts.

  • The tables must be published using their original names.
  • The scripts will try to create/delete both (incoming and outgoing) foreign keys for every single article/table. This will ensure that a FK is created if, in first instance, one of the tables involved in a FK is not already published. It will be created during the post-snapshot script of the second publication initialization.
  • FKs will be created at the subscribers after publisher's name (the same name as publisher's) and, if a FK with the same name already exists, the scripts suppose it is already in place and it will not be created twice, nor with another name.
  • If a FK references tables that are already (both) at the subscriber, the scripts will try to create the FK. The schema will not be checked, this is the reason for the need to deploy the tables with their original names.
  • If horizontal or vertical partitions (row filters) are applied to any of the articles, you must be sure not to remove rows/columns that would violate any of the existing FK at the publisher when tried to be applied at the subscriber (the scripts will not check this).

Scripting: We will discuss here a procedure to automate the process of creating those scripts. We will use an auxiliary publication SyncForeignKeysForSubscribers that will contain 2 articles (tables) called TSyncForeignKeysForSubscribersCREATE and TSyncForeignKeysForSubscribersDROP. The scripts to create those 2 tables are:

CREATE TABLE [dbo].[TSyncForeignKeysForSubscribersCREATE](
  [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [PublicationName] [varchar](256) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [TableName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [FKName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [TheRest] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_TSyncForeignKeysForSubscribersCREATE] PRIMARY KEY CLUSTERED
  ( [Id] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[TSyncForeignKeysForSubscribersDROP](
  [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [PublicationName] [varchar](256) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [TableName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [FKName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_TSyncForeignKeysForSubscribersDROP] PRIMARY KEY CLUSTERED
  ( [Id] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

Those two tables will be filled at the publisher with the information about FKs and replicated to subscribers. When that information is replicated to subscribers, it will be ready to be used by pre and post-snapshot scripts to drop and recreate the FKs. UpdateForeignKeysInformationForReplicationOnAllPublications This stored procedure is to be called at the publisher. It is a wrapper for calling to UpdateForeignKeysInformationForReplicationOnPublication for every every publication on the current database.

CREATE PROCEDURE [dbo].[UpdateForeignKeysInformationForReplicationOnAllPublications]
AS
BEGIN
  DECLARE 
    @PublicationName varchar(256)

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- We retrieve the list of publications that are published 
  -- inside the database we are running into.
  DECLARE ListOfPublications CURSOR LOCAL FOR
    SELECT publication FROM distribution.dbo.MSpublications
    WHERE publisher_db = (SELECT db_name(dbid) FROM master..sysprocesses WHERE spid=@@SPID)
    ORDER BY publication

  -- Just iterate on them calling to the other SP that we will discuss later
  OPEN ListOfPublications
  FETCH NEXT FROM ListOfPublications INTO @PublicationName
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      PRINT 'Processing publication ' + @PublicationName
      EXEC dbo.UpdateForeignKeysInformationForReplicationOnPublication @PublicationName
    END
    FETCH NEXT FROM ListOfPublications INTO @PublicationName
  END
  CLOSE ListOfPublications
  DEALLOCATE ListOfPublications
END

UpdateForeignKeysInformationForReplicationOnPublication @PublicationName varchar(256) This stored procedure is to be called at the publisher too and does all the work of retrieving FK information in order to publish it to subscribers. It should be called everytime your schema changes or new FKs are created. It iterates on FKs defined in de database that @PublicationName belongs to, looking for those that go TO a table outside the articles of the publication or comes FROM a table that is not included in it. Once a matching FK is found, its information is saved in the replicated tables so that subscribers could use this info later.

CREATE PROCEDURE [dbo].[UpdateForeignKeysInformationForReplicationOnPublication] 
  @PublicationName varchar(256)
AS BEGIN
  DECLARE 
    @TableName varchar(128),
    @PKTABLE_NAME varchar(128),
    @PKCOLUMN_NAME varchar(128),
    @FKTABLE_NAME varchar(128),
    @FKCOLUMN_NAME varchar(128),
    @FK_NAME varchar(128),
    @sql varchar(2048)

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Create a temporary table to store middle calculations
  CREATE TABLE #tmpFKs (
    PKTABLE_QUALIFIER varchar(1024),
    PKTABLE_OWNER varchar(1024),
    PKTABLE_NAME varchar(1024),
    PKCOLUMN_NAME varchar(1024),
    PKTABLE_QUEALIFIER varchar(1024),
    FKTABLE_OWNER varchar(1024),
    FKTABLE_NAME varchar(1024),
    FKCOLUMN_NAME varchar(1024),
    KEY_SEQ int,
    UPDATE_RULE int,
    DELETE_RULE int,
    FK_NAME varchar(1024),
    PK_NAME varchar(1024),
    DEFERRABILITY int)

  -- 1st step: Retrieve all outgoing FK: from articles within the publication to other tables 
  -- outside this publication. We will use a cursor to iterate through all those articles
  -- inside the publication and retrieve any FK pointing out to any other table. Afterwards, we 
  -- will delete those FK that fall inside the publication articles.
  PRINT 'Step 1: Inside -> Outside FKs from ' + @PublicationName
  DECLARE ListOfArticles CURSOR LOCAL /*SCROLL READ_ONLY*/ FOR
    SELECT DISTINCT A.destination_object
    FROM distribution.dbo.MSpublications P 
      INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
    WHERE P.publication = @PublicationName
    ORDER BY A.destination_object

  OPEN ListOfArticles
  FETCH NEXT FROM ListOfArticles INTO @TableName
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      SELECT @sql = 'sp_fkeys @fktable_name = N''' + @TableName + ''''
      INSERT INTO #tmpFKs EXEC(@sql)
      -- From those just inserted, delete those FKs that are internal (among articles inside the
      -- publication). At the end, we are just interested in those FKs pointing out to tables 
      -- NOT in this publication.
      DELETE FROM #tmpFKs
      WHERE PKTABLE_NAME IN (  -- Subselect with publication's own articles.
        SELECT A.destination_object
        FROM distribution.dbo.MSpublications P
          INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
        WHERE P.publication = @PublicationName
      )
    END
    FETCH NEXT FROM ListOfArticles INTO @TableName
  END
  CLOSE ListOfArticles
  DEALLOCATE ListOfArticles

  -- 2nd step: We need to retrieve all incoming FKs: from tables NOT included in the publication
  -- to articles inside it. We will retrieve all incoming FK for the tables inside the publication
  -- and then remove those that fall again within tables inside it.
  PRINT 'Step 2: Inside <- Outside FKs from ' + @PublicationName
  DECLARE ListOfOutterArticles CURSOR LOCAL FOR
    SELECT [name] TableName FROM sys.objects T
    WHERE T.type='U' AND 
          T.is_ms_shipped = 0 AND
          T.[name] NOT LIKE 'conflict_%' /* AND
          T.is_published = 1 */ AND
          T.[name] NOT IN (SELECT A.destination_object
                           FROM distribution.dbo.MSpublications P 
                             INNER JOIN distribution.dbo.MSarticles A 
                               ON P.publication_id = A.publication_id
                           WHERE P.publication = @PublicationName)

  OPEN ListOfOutterArticles
  FETCH NEXT FROM ListOfOutterArticles INTO @TableName
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      SELECT @sql = 'sp_fkeys @fktable_name = N''' + @TableName + ''''
      INSERT INTO #tmpFKs EXEC(@sql)
      -- From those just inserted, delete FKs that are completely out the publication
      -- (from tables outside to tables outside)
      -- We are just interested in those pointing to articles inside this publication
      DELETE FROM #tmpFKs
      WHERE 
        FKTABLE_NAME = @TableName AND
        PKTABLE_NAME NOT IN (  -- Subselect with publication's own articles.
          SELECT A.destination_object
          FROM distribution.dbo.MSpublications P
            INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
          WHERE P.publication = @PublicationName
        )
    END
    FETCH NEXT FROM ListOfOutterArticles INTO @TableName
  END
  CLOSE ListOfOutterArticles
  DEALLOCATE ListOfOutterArticles

  -- 3rd step: Some format and cleaning of the auxiliary table.
  -- If there are FKs with more than one key column, KEY_SEQ indicates the 
  -- order we need follow when considering FKCOLUMN_NAME and PKCOLUMN_NAME
  -- We are going to combine those records where KEY_SEQ > 1, flattening 
  -- FKCOLUMN_NAME and FKCOLUMN_NAME into the single record where KEY_SEQ=1
  PRINT 'Step 3: Reorganize #tempFKs   from ' + @PublicationName

  -- First of all, we use QUOTENAME for not having problems afterwards
  UPDATE #tmpFKs 
    SET FKCOLUMN_NAME = QUOTENAME(FKCOLUMN_NAME),
        PKCOLUMN_NAME = QUOTENAME(PKCOLUMN_NAME)

  -- Cursor with records belonging to FKs defined with more than one key column
  -- except this very first column ( > 1 )
  DECLARE ListOfKeys CURSOR LOCAL FOR
    SELECT PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME
    FROM #tmpFKs
    WHERE KEY_SEQ > 1
    ORDER BY PKTABLE_NAME, FK_NAME, KEY_SEQ

  OPEN ListOfKeys
  FETCH NEXT FROM ListOfKeys INTO @PKTABLE_NAME, @PKCOLUMN_NAME, 
                                  @FKTABLE_NAME, @FKCOLUMN_NAME, @FK_NAME
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      -- Update the 'main' record of this FK (the one with KEY_SEQ=1)
      -- and matching the rest of variables...
      UPDATE #tmpFKs
        SET
          PKCOLUMN_NAME = PKCOLUMN_NAME + ', ' + @PKCOLUMN_NAME,
          FKCOLUMN_NAME = FKCOLUMN_NAME + ', ' + @FKCOLUMN_NAME
        WHERE PKTABLE_NAME = @PKTABLE_NAME AND 
              FKTABLE_NAME = @FKTABLE_NAME AND 
              FK_NAME      = @FK_NAME AND
              KEY_SEQ      = 1
    END
    FETCH NEXT FROM ListOfKeys INTO @PKTABLE_NAME, @PKCOLUMN_NAME,
                                    @FKTABLE_NAME, @FKCOLUMN_NAME, @FK_NAME
  END
  CLOSE ListOfKeys
  DEALLOCATE ListOfKeys

  -- 4th Step: Clean and final inserts
  PRINT 'Step 4: Clean & final inserts from ' + @PublicationName

  -- Remove records with KEY_SEQ>1 (we don't need them anymore)
  DELETE FROM #tmpFKs WHERE KEY_SEQ > 1

  -- Now we have all the information we need. We just need to do de inserts
  -- into TSyncForeignKeysForSubscribersDROP formatting according the information 
  -- in this auxiliary table. This table is to be read by the stored procedure
  -- UseForeignKeysInformationAndDoDropsOnPublication thrown by a pre-snapshot script
  -- at the subscriber.
  DELETE TSyncForeignKeysForSubscribersDROP WHERE PublicationName = @PublicationName
  INSERT INTO TSyncForeignKeysForSubscribersDROP (PublicationName, TableName, FKName)
    SELECT @PublicationName, FKTABLE_NAME, FK_NAME FROM #tmpFKs
  
  -- Now the commands to recreate those FKs after the initialization of the
  -- publication is done. This table is to be read by the stored procedure
  -- UseForeignKeysInformationAndDoCreatesOnPublication included in every 
  -- post-snapshot script.
  DELETE TSyncForeignKeysForSubscribersCREATE WHERE PublicationName = @PublicationName
  INSERT INTO
    TSyncForeignKeysForSubscribersCREATE (PublicationName, TableName, FKName, TheRest)
    SELECT 
      @PublicationName,
      FKTABLE_NAME,
      FK_NAME,
      'FOREIGN KEY(' + FKCOLUMN_NAME + ') REFERENCES [dbo].' + QUOTENAME(PKTABLE_NAME) +
        ' (' + PKCOLUMN_NAME + ') NOT FOR REPLICATION'
      FROM #tmpFKs

  -- Finally drop the temporary table.
  DROP TABLE #tmpFKs

  -- Once executed, the tables TSyncForeignKeysForSubscribersCREATE and 
  -- TSyncForeignKeysForSubscribersDROP contain the updated instructions that 
  -- subscribers need to apply to replicate the foreign keys.
  PRINT ''
END

UseForeignKeysInformationAndDoDropsOnPublication @PublicationName varchar(256) This stored procedure should be called only on subscribers, since it DROPS all FKs contained in the table TSyncForeignKeysForSubscribersDROP dealing with @PublicationName. The idea is to include this command into a pre-snapshot script for every publication, so that FKs that would create conflicts when tables should be dropped and recreated are not in place when the actual DROP TABLE command arrives (that is why it should be in a pre-snapshot script).

CREATE PROCEDURE [dbo].[UseForeignKeysInformationAndDoDropsOnPublication]
  @PublicationName varchar(256)
AS
BEGIN
  DECLARE
    @i int,
    @TableName varchar(1024),
    @FKName varchar(1024),
    @sql nvarchar(2048),
    @IsSQL2000 BINARY

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Are we running on a SQL 2000 version?
  -- There must be 2 spaces before 2000%, this is not a typo
  SELECT @IsSQL2000 = COUNT(*) FROM (SELECT @@version AS V) AUX
    WHERE AUX.V LIKE 'Microsoft SQL Server  2000%'

  DECLARE FKsToDROP CURSOR LOCAL FOR
    SELECT TableName, FKName FROM TSyncForeignKeysForSubscribersDROP
      WHERE PublicationName = @PublicationName

  OPEN FKsToDROP
  FETCH NEXT FROM FKsToDROP INTO @TableName, @FKName
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      SET @i = 0
      IF @IsSQL2000<>0
        SELECT @i = COUNT(*) FROM dbo.sysobjects
          WHERE xtype='F' AND type='F' AND Name = @FKName
      ELSE
        SELECT @i = COUNT(*) FROM sys.foreign_keys
          WHERE Name = @FKName

      IF (@i = 1) BEGIN -- The FK is in place, we need to remove it
        SELECT @i = COUNT(*) FROM sysobjects WHERE TYPE = 'U' AND Name = @TableName
        IF @i > 0 BEGIN -- If the table exists, we can go on
          SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@TableName) +
                        ' DROP CONSTRAINT ' + QUOTENAME(@FKName)
          EXEC sp_executesql @sql
          PRINT 'Successfully dropped: ' + QUOTENAME(@FKName)
         END ELSE BEGIN
           PRINT 'No action done:       ' + QUOTENAME(@TableName) + ' doesn''t exist.'
         END
      END ELSE BEGIN -- The FK is not there, do nothing
        PRINT 'No action done:       ' + QUOTENAME(@FKName)
      END
    END
    FETCH NEXT FROM FKsToDROP INTO @TableName, @FKName
  END
  CLOSE FKsToDROP
  DEALLOCATE FKsToDROP
END

UseForeignKeysInformationAndDoCreatesOnPublication @PublicationName varchar(256) This stored procedure is to be called on subscribers. It uses the information stored in TSyncForeignKeysForSubscribersCREATE to issue CREATE commands for FKs related to @PublicationName. It should be inserted in every post-snapshot script so that FKs are recreated after the initial snapshot is sent.

CREATE PROCEDURE [dbo].[UseForeignKeysInformationAndDoCreatesOnPublication]
  @PublicationName varchar(256)
AS
BEGIN
  DECLARE
    @i INT,
    @FKTableName varchar(1024),
    @PKTableName varchar(1024),
    @FKName varchar(1024),
    @TheRest varchar(1024),
    @sql nvarchar(2048),
    @IsSQL2000 BINARY

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Are we running on a SQL 2000 version?
  -- There must be 2 spaces before 2000%, this is not a typo
  SELECT @IsSQL2000 = COUNT(*) FROM (SELECT @@version AS V) AUX
    WHERE AUX.V LIKE 'Microsoft SQL Server  2000%'

  DECLARE FKsToCREATE CURSOR LOCAL FOR
    SELECT FKTableName, PKTableName, FKName, TheRest FROM TSyncForeignKeysForSubscribersCREATE
      WHERE PublicationName = @PublicationName

  OPEN FKsToCREATE
  FETCH NEXT FROM FKsToCREATE INTO @FKTableName, @PKTableName, @FKName, @TheRest
  WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN
      SET @i = 0
      IF @IsSQL2000<>0
        SELECT @i = COUNT(*) FROM dbo.sysobjects
          WHERE xtype='F' AND type='F' AND Name = @FKName
      ELSE
        SELECT @i = COUNT(*) FROM sys.foreign_keys
          WHERE Name = @FKName
      
      IF (@i = 0) BEGIN -- If the FK is not in place, we will try to create it

        SELECT @i = COUNT(*) FROM sysobjects
          WHERE TYPE = 'U' AND (Name = @FKTableName OR Name = @PKTableName)
        IF @i = 2 BEGIN -- If both tables exist, we can go on
          SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@FKTableName) + 
                        ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(@FKName) + ' ' + @TheRest
          EXEC sp_executesql @sql
          SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@FKTableName) +
                        ' CHECK CONSTRAINT ' + QUOTENAME(@FKName)
          EXEC sp_executesql @sql
          PRINT 'Successfully created and applied:  ' + QUOTENAME(@FKName)
        END ELSE BEGIN
          PRINT 'Nothing done, tables do not exist: ' + QUOTENAME(@FKTableName) +
                                               ' or ' + QUOTENAME(@PKTableName)
        END
      END ELSE BEGIN -- If it is not there, do nothing
        PRINT 'No action done, already in place:  ' + QUOTENAME(@FKName)
      END
    END
    FETCH NEXT FROM FKsToCREATE INTO @FKTableName, @PKTableName, @FKName, @TheRest
  END
  CLOSE FKsToCREATE
  DEALLOCATE FKsToCREATE
END

Download: You can also download the scripts described in this article.

Keywords: transactional replication, queued updates, foreign keys, SQL Server 2005, Microsoft, bug, error, workaround, resolution, transact-sql, stored procedure, replicate foreign keys to subscribers