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

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

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

Side effects: If the double-fired trigger makes any update, this update is first executed at the publisher. When the update is run at the subscriber, symptoms described in KB241511 appear: Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9 Updatable Subscribers: Rows do not match between Publisher and Subscriber. Refresh rows at Subscriber. Server: Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8 Updatable Subscribers: Rolling back transaction. This is because the change tried at the subcriber was already done by the publisher, changing msrepl_tran_version field, thus not matching the version that the subscriber has, and failing the transaction. According KB241511 - PRB: REPL: Msgs. 20515, 21054, or 20512 with Immediate-Updating Subscribers:

These messages appear if a change has been made at the publisher and that change has not appeared on the subscriber yet. These messages prevent you from making a change that would violate transactional consistency.

More information: This behaviour is new to SQL Server 2005. Database solutions including transactional replication that worked perfectly with SQL Server 2000 version, might have problems when migrating to SQL Server 2005 and they will need to make modifications in order to succeed. Acording to SQL Server 2005 Books Online: How to: Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming):

To prevent triggers from executing during synchronization:

• When creating a new trigger, specify the NOT FOR REPLICATION option of CREATE TRIGGER (Transact-SQL).
• For an existing trigger, specify the NOT FOR REPLICATION option of ALTER TRIGGER (Transact-SQL).

And also in Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION:

For example, if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table.

As you can read, this comment is done from the publisher's point of view: what should happen on the subscriber when the insertion is done at the publisher? Since the trigger is marked as NFR, the trigger should not be fired there because it would not be fired by a user, but rather a replication agent. However, what happens when the insertion is done at the subscriber? Well... if you are using transactional replication with immediate updates, the behaviour is not symetric, I mean, the trigger will be fired twice: at the subscriber and also at the publisher (If there are more than one subscriber participating in the replication, when the change is replicated to the rest of them it will not be fired again, since the change is done by replication agent). This asymmetry in behaviour regarding NOT FOR REPLICATION triggers has been the reason for my headaches since we decided to migrate to SQL Server 2005.

Steps to reproduce the problem: Lets go and imagine a simple pair of master/detail tables: TQuotations and TQuotationsDetails. We will create triggers to update the master quotation record with the new sum of items' prices every time an insert/update/delete ocurrs on the details table. The tables's schema will be:

CREATE TABLE [dbo].[TQuotations](
  [Id] [int] NOT NULL,
  [Description] [varchar](50) NOT NULL,
  [Price] [money] NOT NULL CONSTRAINT [DF_TQuotations_Price]  DEFAULT ((0)),
  CONSTRAINT [PK_TQuotations] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TQuotationsDetails](
  [QuotationId] [int] NOT NULL,
  [Id] [int] NOT NULL,
  [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
  [Quantity] [int] NOT NULL CONSTRAINT [DF_TQuotationsDetails_Quantity]  DEFAULT ((1)),
  [Price] [money] NOT NULL CONSTRAINT [DF_TQuotationsDetails_Price]  DEFAULT ((0)),
  CONSTRAINT [PK_TQuotationsDetails] PRIMARY KEY CLUSTERED ([QuotationId] ASC, [Id] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the triggers, defined at TQuotationsDetails will be:

CREATE TRIGGER [dbo].[trg_Check_ins_upd_TQuotationsDetails] ON [dbo].[TQuotationsDetails]
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS BEGIN
 SET NOCOUNT ON

 IF UPDATE(Quantity) OR UPDATE(Price) BEGIN
  UPDATE dbo.TQuotations
  SET Price = ROUND(AUX.Total, 2)
  FROM dbo.TQuotations INNER JOIN (
   SELECT dbo.TQuotations.Id AS QuotationId,
    SUM(ROUND(dbo.TQuotationsDetails.Quantity * dbo.TQuotationsDetails.Price, 2)) AS Total
   FROM dbo.TQuotations INNER JOIN
    dbo.TQuotationsDetails ON dbo.TQuotations.Id = dbo.TQuotationsDetails.QuotationId INNER JOIN
    (SELECT DISTINCT QuotationId FROM inserted) AS i ON dbo.TQuotations.Id = i.QuotationId
   GROUP BY dbo.TQuotations.Id) AUX ON AUX.QuotationId = dbo.TQuotations.Id
  WHERE dbo.TQuotations.Price <> ROUND(AUX.Total, 2)
 END
END

CREATE TRIGGER [dbo].[trg_Check_del_TQuotationsDetails] ON [dbo].[TQuotationsDetails]
FOR DELETE
NOT FOR REPLICATION
AS BEGIN
 SET NOCOUNT ON

 UPDATE dbo.TQuotations
 SET Price = ROUND(AUX.Total, 2)
 FROM dbo.TQuotations INNER JOIN (
  SELECT dbo.TQuotations.Id AS QuotationId,
   ISNULL(SUM(ROUND(dbo.TQuotationsDetails.Quantity * dbo.TQuotationsDetails.Price, 2)), 0) 
    AS Total
  FROM dbo.TQuotations INNER JOIN
   (SELECT DISTINCT QuotationId FROM deleted) AS d ON dbo.TQuotations.Id = d.QuotationId
   LEFT OUTER JOIN dbo.TQuotationsDetails
    ON dbo.TQuotations.Id = dbo.TQuotationsDetails.QuotationId
  GROUP BY dbo.TQuotations.Id) AUX ON AUX.QuotationId = dbo.TQuotations.Id
 WHERE dbo.TQuotations.Price <> ROUND(AUX.Total, 2)
END

You can now set up a publication including these two tables. Remember to set the option to deploy triggers to subscribers also (this is disabled by default). Configure a subscriber for your new publication and wait until the publication is ready at the subscriber. We will try to do some inserts first at the publisher in order to test that the triggers behave as expected:

INSERT INTO TQuotations (Id, Description) VALUES (1, 'the test1')
SELECT * FROM TQuotations
GO
--Id Description Price msrepl_tran_version
--1 the test1 0,00 75ACD2B8-7A0F-4F15-9F8F-0FA1D8640378
--
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (1, 1, 'Item 1', 1, 20)
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (1, 2, 'Item 2', 10, 5)
SELECT * FROM TQuotations
SELECT * FROM TQuotationsDetails
GO
--Id Description Price msrepl_tran_version
--1 the test1 70,00 80D3E3CB-30D5-42BF-A02A-1F395A94836D
--
--QuotationId Id Description Quantity Price msrepl_tran_version
--1 1 Item 1 1 20,00 CC20554D-E9DE-434D-866C-1A65C6F582A3
--1 2 Item 2 10 5,00 AB690758-ACA9-4D2C-A037-BFCE633A1BEA

As you can see the triggers run fine (the price at the master quotation Id 1 is updated) and the commands are replicated to subscriber without any problem. You can try to run updates and deteles on TQuotationsDetails and check the triggers are doing their job nicely. Now the problems: Let's connect to the subscriber and try to insert a new quotation (id=2) with some details:

INSERT INTO TQuotations (Id, Description) VALUES (2, 'the test2')
SELECT * FROM TQuotations
GO
--Id Description Price msrepl_tran_version
--1 the test1 70,00 80D3E3CB-30D5-42BF-A02A-1F395A94836D
--2 the test2 0,00 7AACDD7F-4A7A-4D0C-93BA-C6C37093FDE6
--
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (2, 1, 'Item 1', 1, 20)
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (2, 2, 'Item 2', 10, 5)
SELECT * FROM TQuotations
SELECT * FROM TQuotationsDetails
GO
--Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9
--Updatable Subscribers: Rows do not match between Publisher and Subscriber.
--Refresh rows at Subscriber.
--Server: Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
--Updatable Subscribers: Rolling back transaction.

My feedback to Microsoft on this issue: NOT FOR REPLICATION clause in triggers that are replicated to subscribers are fired twice in both publisher and subscriber This issue was resolved as 'By Design'. Please have a look at it. You can read Microsoft's point of view on this problem. This is an 'open' issue for me. I have not found any solution to this problem yet. As a workaround (suggested by Microsoft) I am forced to use transactional replication with queued updates which has its own drawbacks also (I have enough on this for another article).

Keywords: SQL Server 2005, transactional replication with immediate updates, Microsoft, NOT FOR REPLICATION, NFR, trigger, subscriber, fired twice, side-effects, KB241511, Rows do not match between Publisher and Subscriber, Updatable Subscribers: Rolling back transaction, bug, error, workaround, resolution

2006-10-02 Update: See the workaround in SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers. Part II

Microsoft Live Messenger released

Press note: Launch of Windows Live Messenger Marks Significant Progress for Microsoft’s Windows Live Era Features:

• Video call.
• Windows Live Call with Verizon Web Calling service.
• Cordless phones designed exclusively for Windows Live Messenger.
• Microsoft LifeCams optimized for Windows Live Messenger.
• Sharing Folders. Now sharing is as easy as dragging and dropping a file. With a new, intuitive user interface, customers can more easily share their files and personal photos with family, friends and colleagues on their Windows Live Messenger Contact List.
• Windows Live Contacts.
• Integration with Windows Live services.

Even localised versions are available for downloading (at least, Spanish version). After installing it we can see a new service is installed (Start Configuration Control Panel Administrative Tasks Services) called: Messenger Sharing USN Journal Reader service. This service is in charge of monitoring USN Journal changes to file system for the directories shared with your msn contacts. These directories are under %USERPROFILE%\Local settings\Program data\Microsoft\Messenger\<your-msn-email> Under that directory you will find a folder called Sharing Folders and a hidden one called SharingMetadata. If you browse through the hidden one you will find another one called DFSR containing ConflictDelete, Deleted, Installing and Staging. For those of you not in the depths of Microsoft Server systems, I will tell you that this directory structure is used also on Windows Server 2003 R2, the newest release of Microsoft servers. One of the main advantages of this new R2 release is the new replication service, called DFS (Distributed File System). Extracted from Distributed File System Technology Center page from Microsoft:

The Distributed File System (DFS) technologies in Windows Server 2003 R2 offer wide area network (WAN)-friendly replication as well as simplified, fault-tolerant access to geographically dispersed files. The two technologies in DFS are as follows:

• DFS Replication. New state-based, multimaster replication engine that is optimized for WAN environments. DFS Replication supports replication scheduling, bandwidth throttling, and a new byte-level compression algorithm known as remote differential compression (RDC).
• DFS Namespaces. Technology that helps administrators group shared folders located on different servers and present them to users as a virtual tree of folders known as a namespace. DFS Namespaces was formerly known as Distributed File System in Windows 2000 Server and Windows Server 2003.

If you are using Windows Server 2003 R2 and want to keep folders synchronized, we recommend using DFS Replication instead of FRS. DFS Replication system in Windows Server 2003 R2 has many benefits over File Replication Service (FRS), including improved management tools, higher performance, and delegated management.

As you can see, it seems that Microsoft is also using their brand-new-fresh-cutting-edge technology for file replication on their new Windows Live Messenger. One of the new features of this file replicating technology is the previously called RDC (Remote Differential Compression), which in summary, only transfers compressed portions of the file that has been modified. If only some chunks of a huge Powerpoint presentation are modified, there is no need to transfer the whole thing again, only the modified portions of the file are compressed and transferred, thus saving time and bandwidth. Since home users do not have the same hight bandwidth as corporate environments have, I'm sure they will be glad to read this. I must congratulate Microsoft for including this new server technology in such an everyday-user product as Microsoft Live Messenger. Note for Live Messenger users: If a shared file is overwritten by both sides at the same time, one of them is selected as looser of the conflict. I suppose that Live Messenger will warn you about this if it ever happens (I haven't tested it wisely). Just for your information, when this happens on a Windows Server 2003 R2, the file that is selected as looser is stored on ConflictAndDeleted. The equivalent for Live Messenger is ConflictDelete.

Problems with identity fields in MS Access when using SQL Server's transactional replication with queued updates

Scenario: Transactional replication among SQL Server 2005 publisher and 2000 or 2005 subscribers with queued updates. Articles/Tables with a primary key defined by an identity field throw errors in MS Access when inserted at the subscriber (SQL Server 2005 Standard SP1 & MS Access 2003 SP2).

Problem description: @@identity is changed by replication triggers deployed to subscribers.

Side effects: Since MS Access 2003 uses @@identity (see BOL for more information) to retrieve the last inserted record for tables indexed by identity fields, every time you try to insert a new record on a subscriber of transactional replication with queued updates on a table with an identity primary key, the following error message appears: "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source."

Good solution (but out of our hands): MS Access 2003 should use SCOPE_IDENTITY() (see BOL) instead of @@identity to retrieve the record that it has just inserted. Since @@identity is not scope-aware, if there are triggers in the offending table that do other inserts on other tables also managed with identities, @@identity would return the wrong value to client application (MS Access). SCOPE_IDENTITY() solves this problem by returning the last inserted identity within the same scope. Since this behavior is hard-coded inside MS Access there is very little we can do with user defined code (VB) to change this. Until a MS Office Service Pack solves this issue, the only things that programmers can do are workarounds.

Workarounds:

a. Do not use identity as PK on tables. Sometimes identities are avoidable, some other times not. If you have read up to this point, you probably need them and not using them is not an option for you.

b. Create triggers on tables experiencing this problem to discard inserts done by MS Access and do the real inserts through VB code inside a form. First of all, you need a way for the trigger to know when the insertion is done by MS Access directly and when the insertion comes through your user defined code. For this purpose you can use SET CONTEXT_INFO as in the following VB code (self documented):

Private ResyncCommandORI As String
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim NewIdentity As Long

    If (Me.NewRecord) Then ' We are inserting
        ' Set this context variable so that the trigger knows the insertion
        ' is done via VB. If this context variable is not set, the trigger
        ' will delete the inserted record transparently.
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x1"

        strSQL = "INSERT INTO TestTable (TestField) " & _
                 "VALUES ('" & TestField.Value & "'); SELECT SCOPE_IDENTITY() AS Id"
        Set rst = CurrentProject.Connection.Execute(strSQL)

        ' Once the insertion is done, we delete the context info
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x0"

        ' And now we retrieve the fresh new identity
        ' Skip first recordset, closed and without data (insert statement)
        Set rst = rst.NextRecordset
        If (rst.State <> adStateClosed) Then
            If Not rst.EOF Then
                NewIdentity = rst.Fields(0)
            End If
        End If

        ' In order to MS Access copes with the insertion that he thinks
        ' he will be able to do without problems, we need to change
        ' ResyncCommand so that it does not use ? (@@identity) and
        ' use the NewIdentity we have just read.
        ResyncCommandORI = Me.ResyncCommand
        Me.ResyncCommand = Replace(Me.ResyncCommand, "?", Trim(Str(NewIdentity)))
        ' ResyncCommand is ready to be used by MS Access so that it
        ' can read the new record.
    End If
End Sub
Private Sub Form_AfterInsert()
    ' We just leave ResyncCommand as it was originally (with the ?)
    ' so that updates will work also.
    Me.ResyncCommand = ResyncCommandORI
End Sub

Besides this VB user code in every form dealing with tables with identities, we need to create the following trigger on those tables:

CREATE TRIGGER [dbo].[insert_TestTable_trigger] on [dbo].[TestTable]
   AFTER INSERT
   NOT FOR REPLICATION
AS
BEGIN
    DECLARE @v varbinary(1024) -- 128bytes

    -- added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON
    SELECT @v = context_info
      FROM master.dbo.sysprocesses
      WHERE spid = @@SPID;

    IF @v <> 0x1 BEGIN
      -- Do not complete the insertion. If we do a ROLLBACK MS Access
      -- will know and will raise an error. We just do a DELETE of the
      -- record just inserted and nothing wrong happens.
      DELETE dbo.TableTest
        FROM dbo.TableTest
        INNER JOIN inserted ON dbo.TableTest.Id = inserted.Id
    END
END

Using this code in every form and in every table that uses identities is a hard work and a nasty workaround. You will agree that this is not an elegant nor scalable solution and it is not a good idea to mix this complex logic between client (MS Access) and server (SQL Server). If you are not satisfied with this workaround, please keep on reading...

c. Modify SQL Server 2005 core system stored procedures to leave @@identity unchanged on sync triggers deployed to subscribers. SQL Server has two kind of transactional replication modes: immediate and queued. When in immediate mode, every subscriber connects to publisher in every insert/update/delete using a two-phase protocol (2PC) so that the statement is only 'approved' for the subscriber when it has been committed previously on the publisher. When transactional replication drops into queued mode (because of a network connection loss or intentionally because you want it that way), the changes are queued and they are tried to be applied on a scheduled basis, hence a little (but selectable) latency occurs. No matter which mode you use, the identities are generated (by default) at the side doing the insertion and that is the reason for them to need a reserved space for assigned identities (identity ranges) so that there are no collisions (conflicts). This is another changed behavior since SQL Server 2000. When using SQL Server 2000 and transactional replication with immediate updates, identities were generated always at the publisher even though the insertions were done on a subscriber. In 2005, identity ranges are used always (by default), even when using immediate updates. In order to implement replication SQL Server deploys a series of stored procedures and triggers to subscribers. The names for those triggers and stored procedures are like:

trg_MSsync_del_TestTable
trg_MSsync_ins_TestTable
trg_MSsync_upd_TestTable
sp_MSdel_dboTestTable
sp_MSins_dboTestTable
sp_MSupd_dboTestTable

It seems that, internally, the *ins* trigger or stored procedure involved during an insertion do another insert into a table with identities, thus, changing the value of @@identity and making it unusable for MS Access afterwards. As I said before, it's MS Access fault but, as long as we cannot change MS Access behavior, we will modify SQL Server core system stored procedures that create the scripts that create the former triggers so that @@identity is preserved when they are executed. Our work is based on a trick widely used by programmers to circumvent MS Access bad behavior (thanks Sylvain Lafontaine for your information on my post at news microsoft.public.access.adp.sqlserver). This trick is commented in this post and basically the idea is saving @@identity on a local variable right at the beginning of the trigger and then restore it before leaving out such as:

CREATE TRIGGER [dbo].[trg_MSsync_ins_TestTable] on [dbo].[TestTable]
AFTER INSERT
NOT FOR REPLICATION
AS BEGIN
 DECLARE @identity int, @strsql varchar(128)
 SET @identity=@@identity
   -- Main block of the trigger here
 SET @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ', 1) as id into #tmp'
 EXECUTE(@strsql)
END

This is the trick that people have been using on their own triggers. Making this goes against the definition of @@identity, that must always return the last inserted identity in any table for the current connection, but as I said, this is a trick and it does the job (MS Access does the inserts without any error despite its own bug). The main challenge for us now is that the offending triggers are not user-generated, but system-generated. Where do I need to 'touch' to modify SQL Server's behavior and include those 4 lines of code into their own generated triggers? That's the question. And here is the answer: The Resource Database (or RDB). I first read about it here and here are some official words from Microsoft:

[...]The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.[...]

First of all, I must say that the following procedure is completely unsupported by Microsoft. As stated in the former URL:

[...]The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The only supported user action is to move the Resource database to the same location as the master database. For more information, see Moving System Databases.[...]

The (unsupported) steps to make things work:

1. Do a backup. As you have probably guessed, the first thing you should do before making any unsupported change is a backup. Do a backup of your databases and (more important) do a backup of your original files mssqlsystemresource.mdf and mssqlsystemresource.ldf. They must be in the same directory as your master database files, usually %PROGRAMFILES%\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Since that database is read only, there should be no problem if you try to do the backup with mssqlserver service running, but just for security, stop the service and do the backup afterwards (from a cmd line):

net stop mssqlserver
copy mssqlsystemresource.mdf mssqlsystemresource.mdf.original
copy mssqlsystemresource.ldf mssqlsystemresource.ldf.original

The backup could be as simple as that. You can try this whole procedure on a virtualised / testing environment for improved security.

2. Start SQL Server in single-user mode. In order to modify the resource database, we need to start SQL Server in single-user mode (/m parameter). We need to stop SQL Server Agent and any other service that is SQL Server related (i.e. reporting services, analysis services, IIS if apply) so that the agent (or other) could use the unique allowed connection.

net stop sqlserveragent
net stop (any other service related)
net start mssqlserver /m

3. Use sqlcmd to make the changes. This is the core of all this article. You need now to connect to the server and execute the ALTER PROCEDURE to modify/patch the system stored procedures sys.sp_MSscript_sync_del_trig, sys.sp_MSscript_sync_ins_trig and sys.sp_MSscript_sync_upd_trig. You can download the full source code for patching SQL Server 2005 SP1 here: sp_MSscript_sync_xxx_trig_for_SQL_Server_2005_SP1_9.00.2047.00.zip. Unzip the files and run the following commands:

sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_del_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_ins_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_upd_trig.sql

If everything has gone nicely, you should be again on the command prompt. Now you just need to stop the service and start it again as usual (not in single-user mode).

net stop mssqlserver
net start mssqlserver
net start (anything previously stopped)

Congratulations! Now the system stored procedures sys.sp_MSscript_sync_xxx_trig contain our custom pieces of code. You won't need to follow this procedure for every server involved in replication as long as they are all SQL Server 2005 with the same service pack applied. You just need to copy the new (updated) mssqlsystemresource.mdf and mssqlsystemresource.ldf files to every server/instance (stop service first). Furthermore, the files are the same for 32bit versions and x64 versions of SQL Server 2005 (you can check it using any crc32, md5 file checker as I did). It is important that every subscriber have this patched version of resource database files since the patched stored procedure is called at the subscribers. When a publication is marked for reinitailization at the publisher and the initial snapshot is run, the scripts sent to the subscribers do not include the offending triggers, they are generated and applied directly at the subscribers. The initial snapshot publication files include:

• bulk copy files with the contents of the tables (.bcp)
• DDL commands to recreate the tables, sp_MSxxx_ stored procedures and schema (.sch)
• Constraints dealing with identity ranges repl_identity_range_tran_xxx (.dri)
• Indexes and primary key constraints (.idx)
• Pre-script to drop foreign keys pointing to the table we are publishing and need to drop and recreate (.pre)
• User defined triggers (.trg)

As you can see, there is no trace about the triggers that appear finally on subscribers. That is because they are generated and applied locally on the subscribers and that is the reason because every subscriber must have the patched files.

4. The final step. Reinit every publication that you had already running so that the new patched triggers are generated and applied on every subscriber. After that, MS Access should work nicely without any problem dealing with identities.

Some final words (disclaimer): As I already stated, this patching procedure is completely unsupported by Microsoft. I shall not be liable for any damage (including, without limitation, damage for loss of business or loss of profits) arising from the use of, or inability to use, this document or any material contained in it, or from any action or decision taken as a result of using this document or any such material. I tested this procedure in my environment, solved the problem and worked as expected. I encourage you to do your own test in virtualised/testing environments first. Before applying any new SQL Server Service Pack that might arise in the future, I suggest to stop the server, restore the original files we copied at step 1 of the procedure, and then follow the instructions of the SP. After the SP is applied (also on subscribers), you can reinit the publications and see if the MS Access error appears again. If so, I suppose you could re-patch the new resource database that the SP might have set in place. In my scenario SQL Server 2005 was Spanish localized and a SELECT SERVERPROPERTY('ResourceVersion'), SERVERPROPERTY('ResourceLastUpdateDatetime') returned: Original RDB: 9.00.2047 2006-04-14 04:17:29.497 Patched RDB: 9.00.2047 2006-06-15 19:08:00.210

Note for SQL Server 2000 subscribers: Solving this issue for SQL Server 2000 subscribers is much more easier. We need to modify the same dbo.sp_MSscript_sync_xxx_trig system stored procedures, located at the master database. No need to deal with Resource Databases, needing to stop and start the server in single-user mode, etc... just a simple ALTER PROCEDURE and a reinitialization of the publications. Well, there are some additional steps to be taken in order to still have those stored procedures marked as system objects. If you just do an ALTER PROCEDURE, they will no longer be marked as system objects. The complete scripts for patching SQL Server 2000 SP4 are available here.

Links: You receive an error message when you add a record to a subform in an Access 2002 Project Moving System Databases (SQL Server 2005) http://msdn2.microsoft.com/en-us/library/ms187342.aspx BOL chapter about SCOPE_IDENTITY() function

My feedback to Microsoft on this issue: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125475

Keywords: Identity, @@IDENTITY, transactional replication with queued updates, SQL Server 2005, Microsoft, MS Access, SCOPE_IDENTITY(), Reserved Resource Database, sp_MSscript_sync_ins_trig, bug, error, workaround, resolution

NOTE: Just as sp_MSscript_sync_ins_trig purpose is scripting the trg_MSsync_ins_TableName trigger on subscribers, trg_MSsync_upd_TableName is scripted by sp_MSscript_sync_upd_trig, trg_MSsync_upd_TableName is created by sp_MSscript_sync_del_trig and the all three need to be patched the same way. All of them make inserts into MSreplication_queue table, which has an identity column called orderkey. You can read about MSreplication_queue table on SQL Server 2005 Books Online. Any command that should be delivered to publisher is inserted into this table, thus changing the value of @@identity variable. Now I have the three stored procedures (sp_MSscript_sync_ins_trig, sp_MSscript_sync_upd_trig and sp_MSscript_sync_del_trig) patched and everything is running smoothly.

This is my first entry

I created this blog to show IT community, mainly Microsoft community, the problems, workarounds and solutions I find in my day-to-day work. Sometimes there will be solutions, some others there will be just thoughts. I don't know how frecuently I will post new blogs here but, just by now, I have a couple of them and they are the main reason for me creating this blog: share the solutions. Thanks for your reading.