Partial multi-column reports in Reporting Services

Scenario. You need to create a multicolumn report in reporting services, but unfortunately, you just need the multicolumn behaviour in the details part of the report, not also on the master.

Suppose you have an invoice (master: invoice number, customer, total sum, etc, details: every item being invoiced), or any other type of report that has a master/details structure and you just need the multi column behaviour in the details part of it.

Problem. As stated in Writing Multi-Column Reports:

A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report.

It seems you really have a problem... Microsoft says that it is impossible to do what you need to. Fortunately there is a...

Workaround. The idea behind this workaround is letting RS believe that the report is just a tabular report as usual. In fact, all you need to do is pure T-SQL behind the scenes, the final report just have one column, but will seem to have 2 columns (or any) where you need to. Let's see it with an example. Suppose you have your data:

Invoice Customer Item Description  Qty  Price    Sum
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00
 394483 JOHN DOE    2 BISCUITS      1    4.00  17.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00
 394483 JOHN DOE    4 MILK          2    2.00  17.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00

And you want to make your invoice with the details (Item, Description, Qty and Price) displayed in two columns. We need to transform the data into something like:

Invoice Customer Item Description  Qty  Price    Sum Item2 Description2 Qty2 Price
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00     2 BISCUITS      1    4.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00     4 MILK          2    2.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00  NULL NULL         NULL  NULL

Do yo see the point? You need to create new fields (ended with 2 suffix) that will store the next row of the needed data, and return half the rows (plus one if the count is not pair). Having transformed your underlying data in such a way, you can create your report as usual, placing a table where you need to and doubling (in case you want 2 columns) the number of columns:

Item Description  Qty  Price         Item2 Description2  Qty2  Price2

How can we do such a transormation using T-SQL?

Let's suppose you have prepared a stored procedure for retrieving the underlying data for RS to use. If you have not, you will need to because we will be doing a couple of transformations, not just a simple select query, so you cannot embed the query into the report itself. You will need to use a stored procedure for it, let's call it RS_MyCustomReport.

You might have RS_MyCustomReport defined as something like:

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT {your list of fields}
  FROM {your tables or views}
  WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
        ({FieldY} = @Filter2 OR @Filter2 IS NULL)

Where FieldX and FieldY are the fields to use to filter using the optional parameters @Filter1 and @Filter2. All you need to do is the following (the changes are highlighted):

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT IDENTITY(int,1,1) AS ID, AUX1.*
  INTO #TEMP
  FROM (
    SELECT {your list of fields}
    FROM {your tables or views}
    WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
          ({FieldY} = @Filter2 OR @Filter2 IS NULL)
  ) AUX1

  SELECT * FROM
   ( SELECT T1.*, T2.{FieldA} AS FieldA2, T2.{FieldB} AS FieldB2 {...}
     FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID OR T2.ID IS NULL)
   ) AUX
  WHERE AUX.ID % 2 = 1
  ORDER BY AUX.ID

You can replace the number of joins and the % 2 operator if you need to create multi column reports of 3 or more columns. A whole running script that you can test follows:

SELECT IDENTITY(int,1,1) AS ID, AUX1.*
INTO #TEMP
FROM (SELECT 13 AS Col, 'one' AS Descr UNION
      SELECT 18, 'two' UNION
      SELECT 35, 'three' UNION
      SELECT 51, 'four' UNION
      SELECT 67, 'five') AS AUX1

SELECT * FROM #TEMP

SELECT * FROM (
  SELECT T1.*, T2.Col AS Col2, T2.Descr AS Descr2
  FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID or T2.ID IS NULL)
) AUX WHERE AUX.ID % 2 = 1
ORDER BY AUX.ID

DROP TABLE #TEMP

How to convert several rows into a CSV string

Scenario

I recently posted just about the opposite (see INNER JOIN with a comma separated values CSV field ) and now I want to show how to create a CSV list when you have several rows to merge, grouping by some criteria. Let's suppose you have a set of data such as:

F1  F2  Item
-----------------
1  4    A
1  4    B
3  2    A
3  3    C
5  1    B
5  4    A
5  4    E
5  4    F
6  1    E
6  1    W

And you need to generate something like:

F1  F2  ItemsList
-----------------
1  4    A, B
3  2    A
3  3    C
5  1    B
5  4    A, E, F
6  1    E, W

Solution

This procedure is based on the information I found on Converting Multiple Rows into a CSV String (Set Based Method) but tuned to be able to group by a set of fields, not only one:

IF NOT OBJECT_ID('tempdb..#Source') IS NULL
  DROP TABLE #Source
IF NOT OBJECT_ID('tempdb..#t') IS NULL
  DROP TABLE #t

SELECT 1 F1, 4 F2, 'A' Item INTO #Source
UNION ALL SELECT 1, 4, 'B'
UNION ALL SELECT 3, 2, 'A'
UNION ALL SELECT 3, 3, 'C'
UNION ALL SELECT 5, 1, 'B'
UNION ALL SELECT 5, 4, 'A'
UNION ALL SELECT 5, 4, 'E'
UNION ALL SELECT 5, 4, 'F'
UNION ALL SELECT 6, 1, 'E'
UNION ALL SELECT 6, 1, 'W'

CREATE TABLE #t (
  F1 int not null,
  F2 int not null,
  Item varchar(7) not null,
  ItemsList varchar(8000)
)

INSERT INTO #t (F1, F2, Item)
  SELECT TOP (100) PERCENT F1, F2, Item
  FROM #Source
  ORDER BY F1, F2, Item

DECLARE
    @List varchar(8000),
    @F1_Last int,
    @F2_Last int

SELECT
    @List = '',
    @F1_Last = -1,
    @F2_Last = -1

UPDATE #t
  SET
    @List = ItemsList =
      CASE
        WHEN (@F1_Last <> F1) OR
             (@F2_Last <> F2)
          THEN Item
        ELSE @List + ', ' + Item
      END,
    @F1_Last = F1,
    @F2_Last = F2

SELECT TOP (100) PERCENT F1, F2,
  CASE
    WHEN LEN(MAX(ItemsList)) > 512
      THEN CONVERT(varchar(512), LEFT(MAX(ItemsList), 509) + '...')
    ELSE CONVERT(varchar(512), MAX(ItemsList))
    END AS ItemsList
FROM #t
GROUP BY F1, F2
ORDER BY F1, F2
GO

Regarding efficency I must say that using this approach in my production environment, where the original internal query (real table instead of #Source temporary table) would return near 5800 rows, 5 columns, needed grouping by 4 of them (integers) and creating a list combining the fifth column (varchar(512)), this procedure does the job in just 3 seconds, whereas our old iterative-cursor-based procedure took more than 23 seconds to complete.

SQL server 2005 publishing views to SQL 2000 subscribers

If you are in the process of migrating your SQL Server 2000 scenario to SQL Server 2005 and you have replication configured, here is a hint that you must have in mind before changing the publisher to be a SQL Server 2005: SQL Server 2005 has changed the syntax for SELECT clauses (when ORDER BY is used) in the following way:

SQL 2000: SELECT TOP 100 PERCENT Field1, Field2 FROM Table1 ORDER BY Field1
SQL 2005: SELECT TOP (100) PERCENT Field1, Field2 FROM Table1 ORDER BY Field1

Please note the parentesys. This newer syntax is not recognised by SQL Server 2000 engine, thus giving you errors when the initial snapshot is tried to be applied. If this is your case and you need to set up SQL Server 2005 as a publisher to SQL Server 2000 subscriber(s), you will need to change those views that need to be published to remove the ORDER BY so that no TOP [n] PERCENT is needed (and also be removed), and modify your application accordingly. Besides, according ORDER BY Clause (Transact-SQL)(in BOL):

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

In other words, it is useless to do a SELECT TOP 100 PERCENT... ORDER BY FieldX in a view (let's say View1), since all records will be returned, but not granted to be ordered as expected, unless we do a SELECT * FROM View1 ORDER BY FieldX from the view itself. See also: SQL Server 2005 Ordered View and Inline Function Problems from OakLeaf Systems' blog.

INNER JOIN with a comma separated values CSV field

Scenario: Sometimes we use varchar fields to store little pieces of data that, extriclty talking, should be normalized and stored on another table that references the former main table. Think for instance in a list of items referenced by a bigger entity, using a 1..n relationship. In certain particular scenarios that list might be as short as 4 or 5 items, and sure less than 10. In that case, and if you plan that you will never need to do a join with that data and another table you are tempted to store de data as a list of items (varchar), and simplify things not adding another table to your already huge schema, despite the normalization rules. And time goes by and needs change and then you need to do that JOIN that you planned you will never need to do, and you have just a list of integers such:

K1  List
-----------------
1  '1, 5, 22, 31'
2  '4, 9, 48'
3  '5, 13, 22'

And you need to generate something like:

K1  Item
-----------------
1  1
1  5
1  22
1  31
2  4
2  9
2  48
3  5
3  13
3  22

Solution: I must confess that I was not able to find a good solution by myself and asked for help on microsoft.public.es.sqlserver. Miguel Egea showed me a solution based on Itzik Ben Gan's work that was worth making this blog entry. Pure Transact-SQL, and efficient:

IF NOT OBJECT_ID('tempdb..#t') IS NULL
DROP TABLE #t
GO

SELECT 1 K1, '1, 5, 22, 31' List INTO #t
UNION ALL
SELECT 2, '4, 9, 48'
UNION ALL
SELECT 3, '5, 13, 22'
GO

/* CommonTableExpression to retrieve the first 1000 numbers */
WITH cte AS
( SELECT 1 id
  UNION ALL
  SELECT id+1 from cte WHERE id<1000
)
  --SELECT *,
  --  SUBSTRING(List, id+1, PATINDEX('%,%', SUBSTRING(List, id+1, LEN(List)))-1)
  SELECT K1,
    CONVERT(int, SUBSTRING(List, id+1, PATINDEX('%,%', SUBSTRING(List, id+1, LEN(List)))-1)) Item
  FROM cte INNER JOIN (SELECT K1, ',' + List + ',' List FROM #t) t
    ON SUBSTRING(t.List, id, 1) = ',' AND LEN(List)>id
  ORDER BY K1, Item -- OPTIONAL
  OPTION(MAXRECURSION 0)

SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers. Part II

Scenario: Transactional replication with immediate updates among SQL Server 2005 publisher and subscribers. Articles/Tables have user defined triggers marked as NOT FOR REPLICATION (NFR) and those triggers are deployed to subscribers for load balancing.

Problem description: Even though the triggers are marked as NOT FOR REPLICATION (NFR), when the origin of the triggering action is a subscriber, the trigger is fired twice, at both the subscriber (expected behaviour) and at the publisher (changed behaviour from SQL Server 2000 point of view).

The solution: In this page the solution to the problem exposed in SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers is explained: The main idea is using SET CONTEX_INFO logic so that triggers thrown at the server are capable of distinguishing when the call has been made directly by a user (either at the publisher or the subscriber) or has been made in order to replicate a command.

In the former URL we saw an example of a trigger that should not be executed at all when fired at the publisher by means of an insert/update done at the subscriber. The procedure explained here is to avoid the execution of those unwanted double-fired triggers.

You must ensure that your application executes:

SET CONTEXT_INFO 0x80

Everytime it makes a connection to the server. We will use 0x80 (that in binary is 10000000 00000000). In fact, we will only use the first bit from the first byte (8 bits) from CONTEXT_INFO, that is sized 128 bytes, just in case you might need the rest of the variable for other purposes.

If you already use CONTEXT_INFO in your scenario, select another bit position for this purpose and change the following code accordingly.

And now, the other side of the change: You have to include the following code at the very beginning in every NOT FOR REPLICATION trigger deployed to subscribers that might give you problems when fired more than once:

  -- In order to workaround the double triggering issue in both subscribers 
  -- and publisher when the action is executed on the subscriber, we need 
  -- to check here if the trigger has been fired by a user action.
  -- For this reason, client applications MUST execute SET CONTEXT_INFO 0x80
  -- in order to identify themselves. Only first bit of first byte of the 
  -- 128bytes of CONTEXT_INFO is used here. The rest of CONTEXT_INFO could 
  -- still be used for other purposes. We only check that first bit of first
  -- byte is set to 1.
  IF NOT EXISTS(SELECT * FROM master.dbo.sysprocesses
                WHERE spid = @@SPID AND 
                      CONVERT(tinyint, SUBSTRING(context_info, 1, 1)) & 0x80 = 0x80)
       RETURN -- If no context info exists, return
  -- If we reach to this point, the trigger has identified correctly, the user
  -- action and the execution will continue either on the publisher or the 
  -- subscriber, but not on both.

  -- The rest of the trigger follows...

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%'

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.

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

Little queries that save you time

Which tables in the database use an identity field?

SELECT [name]
FROM sys.objects
WHERE ObjectProperty(object_id,'TableHasIdentity')=1

Search for a text inside triggers and stored procedures. To be used, for instance, if you need to know where a particular table or stored procedure or function is used (for removal).

SELECT sysobjects.name, syscomments.text
FROM syscomments INNER JOIN sysobjects ON syscomments.id = sysobjects.id
WHERE (sysobjects.xtype = 'P' OR sysobjects.xtype = 'TR')
AND syscomments.text LIKE '%SEARCHEDTEXT%'
ORDER BY sysobjects.name

If you are using SQL Server 2005 you can also make use of sys.sql_modules:

SELECT 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 '%SEARCHEDTEXT%'

How to copy SQL Server diagrams from one server ORIGIN, to another server DESTINATION? Provided both databases have already the same tables and schemas, you can copy the definitions of the diagrams in ORIGIN creating a linked server to ORIGIN in DESTINATION and then running this simple query at DESTINATION (both databases have the same name mydatabase):

use mydatabase
set identity_insert dbo.sysdiagrams on
insert dbo.sysdiagrams (name, principal_id, diagram_id, version, definition)
  select name, principal_id, diagram_id, version, definition
  from ORIGIN.mydatabase.dbo.sysdiagrams
set identity_insert dbo.sysdiagrams off

How do I find all the tables referenced by Stored Procedures or Functions?

SELECT so.name AS FuncProcName, t.TABLE_NAME AS TableName, sc.text AS Definition
  FROM syscomments sc
    INNER JOIN sysobjects so ON sc.id = so.id
    INNER JOIN INFORMATION_SCHEMA.Tables t ON sc.text LIKE '%'+t.TABLE_NAME+'%'
  WHERE (so.xtype='FN' OR so.xtype='P') AND so.category=0

How do I find the names of all tables containing a field called like criteria?

SELECT sys.objects.name, sys.columns.name
FROM sys.columns INNER JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.name LIKE '%criteria%' AND sys.objects.type = 'U'
ORDER BY sys.objects.name