My Profile Photo

Gareth Fletcher


Cloud admirer, technology sherpa, visio fanatic, high-functioning madman. Based in Auckland, New Zealand.


Dynamics CRM Outlook Add-In not working, Generic SQL Errors

Some blog You’ll find this post in your _posts directory. Go ahead and edit it and re-build the site to see your changes. You can rebuild the site in many different ways, but the most common way is to run jekyll serve --watch, which launches a web server and auto-regenerates your site when a file is updated.

To add new posts, simply add a file in the _posts directory that follows the convention YYYY-MM-DD-name-of-post.ext and includes the necessary front matter. Take a look at the source for this post to get an idea about how it works.

You can also grab from github if you wanted to.

/*
	Clean up Outlook Sync tables

	Run against the organization's CRM database
	
	Bla bla bla you shouldn't run this, not supported
	by Microsoft. Directly modifying the database is
	a bad idea. There is nothing here but pain and
	suffering.
*/

DECLARE @SubscriptionID as UniqueIdentifier
DECLARE @SystemUserID as UniqueIdentifier
DECLARE @TableToCheck as varchar(50)
DECLARE @MachineName as nvarchar(200)
DECLARE @InternalEmailAddress as nvarchar(100)
DECLARE @SubscriptionCursor as CURSOR

/*
SELECT SubscriptionId, users.SystemuserID, FullName,
			InternalEmailAddress, DomainName, MachineName,
			SyncEntryTableName, Subscriptiontype,
			CompletedSyncStartedOn
	FROM [dbo].[Subscription] sub
	JOIN [dbo].[SystemUserbase] users
	ON sub.SystemuserID = users.SystemUserId
*/

/* Open Cursor across the subscrpitions */
SET @SubscriptionCursor = CURSOR FOR SELECT sub.SubscriptionId,
			sub.SystemuserID, sub.MachineName,
			users.InternalEmailAddress
	FROM [dbo].[Subscription] sub
	JOIN [dbo].[SystemUserbase] users
	ON sub.SystemuserID = users.SystemUserId

OPEN @SubscriptionCursor

FETCH NEXT FROM @SubscriptionCursor INTO @SubscriptionId,
			@SystemuserID, @MachineName, @InternalEmailAddress

WHILE @@FETCH_STATUS = 0
BEGIN
 /*PRINT 'Processing: ' + cast(@SubscriptionID AS VARCHAR (50)) */
 SET @TableToCheck = 'SyncEntry_' + LOWER(REPLACE(CAST(@SubscriptionID AS VARCHAR (50)),'-',''))
 PRINT 'Checking for: ' + @TableToCheck

	/* See if there's a SyncEntry_{ID} table */
 
	IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = @TableToCheck))
	BEGIN
	    PRINT CHAR(9) + 'Seems OK! - ' + CAST(@SubscriptionID AS VARCHAR (50))
	END

	ELSE
	BEGIN
	
		/* If not, clean out the subscription data */
		
		PRINT CHAR(9) + 'FOUND ISSUE! - ' + CAST(@SubscriptionID AS VARCHAR (50))
		PRINT CHAR(9) + 'Cleaning up sync data... (Machine:' + @MachineName + ', User: ' + @InternalEmailAddress + ')'
		/*
		DELETE FROM [dbo].[SubscriptionClients] WHERE SubscriptionID = @SubscriptionID
		DELETE FROM [dbo].[SubscriptionManuallyTrackedObject] WHERE SubscriptionID = @SubscriptionID
		DELETE FROM [dbo].[SubscriptionStatisticsOutlookBase] WHERE SubscriptionID = @SubscriptionID
		DELETE FROM [dbo].[SubscriptionSyncEntryOutlookBase] WHERE SubscriptionID = @SubscriptionID
		DELETE FROM [dbo].[SubscriptionSyncInfo] WHERE SubscriptionID = @SubscriptionID
		DELETE FROM [dbo].[Subscription] WHERE SubscriptionID = @SubscriptionID
		*/
	END
 FETCH NEXT FROM @SubscriptionCursor INTO @SubscriptionId,
		@SystemuserID, @MachineName, @InternalEmailAddress
END

CLOSE @SubscriptionCursor
DEALLOCATE @SubscriptionCursor

Jekyll also offers powerful support for code snippets:

def print_hi(name)
  puts "Hi, #{name}"
end
print_hi('Tom')
#=> prints 'Hi, Tom' to STDOUT.

Check out the Jekyll docs for more info on how to get the most out of Jekyll. File all bugs/feature requests at Jekyll’s GitHub repo. If you have questions, you can ask them on Jekyll’s dedicated Help repository.