Programmer's Edge

Following up on my previous post a couple of people have asked me how they could accomplish this same thing because MERGE is not available in SQL Server 2005. So let’s take a look at how you can use the OUTPUT statement which is available to produce sort of the same affect.

So first we will set up our test data once again from the AdventureWorks database. This will act as the data that is coming into our target table

SELECT TOP 1000 [ContactID]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[EmailAddress]
  INTO [AdventureWorks].[Person].[Contact_Temp]
  FROM [AdventureWorks].[Person].[Contact]

Then we’ll put some data into the target table…

  SELECT TOP 10 *
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp] 

We change the email address to ensure that our T-SQL will in fact update what we are wanting to update

  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'

Now we’ll take a look at the data to make sure it’s what we are expecting…..

So now we are going to write out statement that will mimic the MERGE stuff we did in the previous post. To clarify, we basically want to update the target table and if a particular ContactID exists then we want to update the EmailAddress column. If no such ContactID exists then we would like to insert the row.

Now OUTPUT statement works a lot like an inline TRIGGER in that you have access to an Inserted table. So you have access to the data that is actually being updated/inserted/whatever by the particular statement. So it’s not only good for this instance but also for other scenarios like logging. So we will first go and create as simple table variable to hold the ContactID values that we are going to update with our initial statement. Then we will be using the OUTPUT statement to fill up the table in conjunction with our UPDATE and lastly running an INSERT statement. Kind of a long way around the track when you look at the MERGE statement in the previous post…but hey ya’ gotta work with the tools you got.

 SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;  

 DECLARE @tblContacts table (ContId int); 

 UPDATE [AdventureWorks].[Person].[Contact_Base]
 SET EmailAddress=b.EmailAddress
 OUTPUT inserted.ContactID INTO  @tblContacts
 FROM [AdventureWorks].[Person].[Contact_Base] a INNER JOIN
 [AdventureWorks].[Person].[Contact_Temp] b ON a.ContactID=b.ContactID
;

INSERT INTO [AdventureWorks].[Person].[Contact_Base]
([ContactID],[Title],[FirstName],[MiddleName]
  ,[LastName],[EmailAddress])
SELECT [ContactID],[Title],[FirstName]
      ,[MiddleName],[LastName],[EmailAddress]
      FROM [AdventureWorks].[Person].[Contact_Temp]
WHERE ContactID NOT IN (SELECT ContId from @tblContacts);

Now when we look at the table with a simple SELECT statement we see that everything is as it should be….

SUCCESS! Hopefully, this helps someone else that may be in a jam because of using an older version of SQL Server ….

Cheers,

AJ

One of the more intimating things in the database architecture for DBAs that I meet at events is replication. Especially, if the replication that you need to set up needs to have a little twist in it that is not handled necessarily by the GUI Wizards in SQL Server Management Studio. What most DBAs need to realize is that if they concentrate on learning the underpinnings of ‘how’ replication works within the SQL Server environment then manipulating that environment becomes fairly easy.

Using that line of thought, I thought it would be nice if I compiled some tips and tricks dealing with transactional replication. Hopefully, these hints will shed some light on the underpinning of the system and let you see just how easy replication can be.

Stored Procedures

A lot of what transactional replication does in most cases is done through the use of stored procedures. Now normally the GUI would take care of going out and populating the stored procedures for you but in some instances you may need to do this manually or you may just want to have a quick script of the objects in order to modify some things.

Luckily, the database has a very simple stored procedure in order to script out the stored procedure that will be used for the articles in a publication.

exec sp_scriptpublicationcustomprocs [Publication Name]

This stored procedure will create all of the stored procedures needed for your publication and are normally applied by the snapshot agent but now you have them.

 

Identity Fields

Identity fields are often one of the sticklers in setting up replication because in a lot of instances you are working with a database for which you cannot make changes, 3rd party or just strict policies. So in some instances you may need to keep the existing architecture with the identity columns.

The trick here is once you realize that all of the replication is handled through stored procedures on the subscriber then you can take the hint on stored procedures above and modify them. So in this instance you would simply be looking for the stored procedures dealing with the tables that have the identity columns..specifically the insert and update ones. One the insert stored procedure you have to simply add an IDENTITY INSERT statement. On the update statements you just simply modify the update statement to not update the identity column. Piece of cake!

 

Working with Very Large Databases(VLDBs)

Replication often works through the use of the snapshot agent applying an initial snapshot of the database onto the subscriber for an initial synch. However, if you are working with very large databases you may not have the ability to apply a snapshot . In this instance, you may need to do a backup and restore but how do you get the subscription to be ‘pre-initialized’ and skip the creation of that initial snapshot.

Very simply, you just manually create the subscription yourself without the GUI. There is one areas that control how the initialization is handled. Take a look at the code below:

exec sp_addsubscription @publication = N’AJPub’, @subscriber = N’NXDEVDSSQL03′, @destination_db = N’AJDB_Dest’, @subscription_type = N’Push’, @sync_type = N’none’, @article = N’all’, @update_mode = N’read only’, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @offloadagent = 0, @dts_package_location = N’Distributor’
GO

The @sync_type parameter here is what we use to tell the replication process that we don’t need to do an initial synch. No initial synch…no big snapshot file created.

 

Not Propagating DELETEs

Yes, I know ..not exactly best practices stuff here…but in some instances you may have tables that you are purging on one end of the replication setup and not on the other. So this goes back to realizing that the actual process of pushing the updates to the subscriber is handled by stored procedures. The replication distributor is just in the business of running a bunch of stored procedure calls and if the stored procedure executes successfully then that is good enough for the distributor. There is really no mystical process by which it double checks to make sure that the change was actually made like it was supposed to.

So in this instance all you need to do is to locate the delete stored procedure for the particular object you are wanting to not delete from on the subscriber. Once you find that just comment out the guts of the stored procedure(in case you need to reverse the decision some day) and replace it simply with ‘SELECT 1’ …the statement will always execute successfully….the distributor is happy ….and everybody else is happy.

 

When Replication Goes Wrong

Normally, replication goes on day after day with no hitches. Yet every once in a while you get that dreaded failure. Now SQL Server has a pretty tight way of handling your transactions in that it attempts to batch execute them over on the subscriber…if that fails then what it does is falls back and executes each entry separately for that particular batch. This is a pretty cool concept when you think about it because it is just verifying for you not only that it wasn’t just something screwy with the batch but also it ‘finds’ the offending transaction. Every transaction is sequenced so the next transaction in the bin when the failure occurs during round #2 is the bad one.

All transaction information is generally stored in these two tables…

Msrepl_commands

Msrepl_transactions

Now you can write your own routines to find the next one in the sequence and all but SQL Server provides some mechanisms for you to use.

sp_helpsubscriptionerrors

This stored procedure lets you actually view the errors involved in the transactional replication set and more importantly gives you the LSN of  the offending line.

sp_replcmds @maxtrans=1

This stored procedure will show you which transaction is currently up in the queue and a little information about it such as the command type.

sp_replshowcmds @maxtrans=1

This stored procedure will actually return the command text of the next stored procedure in the queue so that you can see what is trying to execute on the distributor. Then you can pair this up with the information from the sp_helpsubscriptionerrors statement …fix the offending statement,execute it on the subscriber, and then use the next hint to actually skip the offending line.

 

Skipping a Transaction

sp_setsubscriptionxactseqno

This stored procedure is used to manually set the LSN that the replication is currently on. This should be the last successful transaction. So if you were to want to do something like skipping a bad replication command you would pair this with the sp_helpsubscriptionerrors command to get the offending LSN..plug it in to this command and then SQL Server will officially skip the offending statement.

 

Replicating Across Domains

Replicating across a domain is often viewed as a pretty complicated process when in actuality it is pretty simple. Just set up a point to point IP tunnel between the distributor and the subscriber. Once you verify that you can connect to the subscriber SQL Server instance the rest is fairly easy.

The trick to remember here is the replication doesn’t necessarily like IP addresses for the destination server. So you have two options….

1. Bug your system administrator to create an entry in DNS for the IP
2. Create an alias in SQL Server configuration manager to point to the IP

I always like to use #2 because it eliminates the need to go back to the SAs in order to change things. Additionally, you can use this step regardless of whether you are replicating across domains or not. If you use an alias on the SQL Server then it is your hedge if you ever have to move the subscriber database to another server. If your replication is set up with the alias name then you just change the alias in config manager and replication is happy still.

 

Using SQL Server Authentication

Out of the box the replication agent in a push scenario is going to use windows authentication in order to make the connection to the subscriber database. In some situations those this is not optimal as you may want to use SQL Server authentication(see replicating across domains above). This change is quite simple, once you realize that the replication agent for pushing the transactions is actually just a SQL Server job. Locate the job on the distributor, it will be named with a combination of the pub and subscriber dbs. Now open it up and look at the syntax…

-Subscriber [DestinationServer] -SubscriberDB [AJDB_Dest] -Publisher [AJDB-SOURCE] -Distributor [AJDB-DIST] -DistributorSecurityMode 1 -Publication [AJProd] -PublisherDB [AJDB_Pub]    -Continuous

The syntax above is the usual default that is created and is windows authentication just by looking and seeing no SubscriberSecurityMode. By default it is 1 which mean windows. So it is going to use whatever windows account the replication agent is running under to execute this. In order to change it we just simply change the mode to 0 (SQL Server authentication) and provide a username and password.

-Subscriber [DestinationServer] -SubscriberDB [AJDB_Dest] -Publisher [AJDB-SOURCE] -Distributor [AJDB-DIST] -DistributorSecurityMode 1 -Publication [AJProd] -PublisherDB [AJDB_Pub]   -SubscriberSecurityMode 0 -SubscriberLogin [AJDBUser] -SubscriberPassword [VerySecretPassword]

Now save the job updates and you are ready to roll.

I hope that you find these tips helpful in both possibly implementing your own brand of replication and understanding just how the process works. If you have your own unique requirements concerning replication then shoot me an email and I will see if I have run across it before. It may even end up being appended to this list.

Cheers,
AJ

A commonly asked question that I field is when working with CRM or another 3rd party database what is the most efficient way in order to find out column information on a specific table using only T-SQL. This practice is often important if you are writing your own stored procedures and functions to import data into the database. This is often done by writing a series of views that will be used from the import source that map the table structure that the data is going into. So you would naturally want to write some T-SQL that would just automatically create the INSERT statements from view source A into destination table B.

*Disclaimer: Although you could do the same type of thing using SSIS it may not be an option in your environment or you may not consider that option as flexible.

This is readily accomplished by using the INFORMATION_SCHEMA. In the example below I have a custom entity in my CRM instance called Employee. Now in CRM you always have a Base table and then an ExtensionBase table. The ExtensionBase table for an entity is normally the place where you are looking at because your looking for attributes. So the query below should return me decent information about the custom attributes that have been created for me in the table…

 

SELECT
COLUMN_NAME,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='new_EmployeeExtensionBase'
ORDER BY
COLUMN_NAME;

Cheers,

AJ

 

I recently ran into a bug issue here recently when I had to update a server name on a database box running SQL Server 2008. I updated the server name and then set SQL Server to run under the new name as well and restarted the system.

The strange thing was that I went to immediately run one of the maintenance plans to get backups of the database system….it failed. Hhhhmmmm,interesting. Why would it fail now? The error log said that the problem stemmed from the Local Server Connection being unrecognized.

Normally, when you create a maintenance task within the instance there is a connection that is set up automatically set to the local instance like shown below.

This is supposed to make it easier when setting up the jobs and moving it between servers. Since it is set to localhost then it wouldn’t have to be updated or anything. So why is this one different?

After some thinking, I went and looked at the maintenance plan local server connection value. There is where I found the answer. The local server connection showed something similar to below.

The localhost connection was actually a named connection. The thing is that if I create another job on the SQL Server 2008 instance it does not replace localhost with the server name. So this isn’t a bug with SQL Server 2008 maintenance plans. However, this machine was upgraded from SQL Server 2005 a couple of months back. …Doing some quick investigating using a SQL Server  2005 machine I had handy I confirmed this to be the case. Unfortunately, after messing around with it for a little while I also found out that it appears the only way around it is to delete the job and recreate it. Bummer.

So if you are upgrading from SQL Server 2005 to SQL Server 2008 this may be one of the things you have to watch out for. It won’t necessarily stop you from upgrading right away as the it appears that the process will update the name of the connection when upgrading. However, if the name is changed from the original server name…..that is where it will bite you.

Hopefully, this saves you a little bit of time if you run into this issue.

Cheers,

AJ

Okay,  you may run into the situation where you restore a database from Server A onto Server B and the database contains CLR assemblies. When you try to execute a given CLR assembly it blows an error similar to the one below.

Msg 10314, Level 16, State 11, Line 2 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException: at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString)

 

This occurs even though you have the database Trustworthy property set to ON and the assembly is set to either external_access or unsafe for the permission level. Additionally, it will not matter if you are the sysadmin or not. So what gives since the assembly worked just fine on the first machine?

Well, it seems that the packages are pretty attached to their owners. So if the owner whom originally created the package on Server A does not have an account on Server B then it goes haywire. Microsoft has kind of a hokey way to get around this in copying over the account and then matching up the SID and giving the account the proper permissions on the database instance.

This is outlined in the following kb article

http://support.microsoft.com/kb/918040

However, a much easier workaround is just to simply script out the dropping of the procedures and functions that rely on the assembly as well as the assembly itself. Then script out a set of create statements to recreate them. Put it all together and run it under a new account on the new server and you are good to go.

In practice a better method of handling this is to ensure that your assemblies are deployed with an AD service account with permissions on the SQL Server. The AD accounts are easier to handle in this scenario because you just have to make sure that the service account is on the new database instance with the proper permissions. No synching of SIDS is required.

Hopefully, this post saves someone a little bit of time down the road…..

Cheers,
AJ