Application Note for QuNect ODBC for QuickBase

Synching a QuickBase table in SQL Server

The following stored procedure will synch a table from QuickBase to SQL Server. After running this stored procedure the table in SQL Server will be up to date with the table in QuickBase. This stored procedure only modifies the SQL Server table, not the corresponding QuickBase table. If you want to go in the other direction please read Synching a SQL Server table in QuickBase It has only one required parameter: dbid. You can find the DBID of a QuickBase table by reading the How to Find the DBID of a QuickBase Table application note.

GO
/****** Object:  StoredProcedure [dbo].[SyncQuickBaseTable]    Script Date: 10/21/2008 12:24:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Claude von Roesgen Copyright 2007
-- Create date: 4/17/2007
-- Updated:     10/21/2007
-- Description:	Synchronize a table from QuickBase to SQL Server.
--				This stored procedure depends on the existance of a linked server called
--				QuickBase. To create this linked server run the following SQL:
--
--					EXEC sp_addlinkedserver 
--					@server = 'QuickBase', 
--					@provider = 'MSDASQL',
--					@srvproduct = 'QuNect',
--					@provstr = 'DRIVER={QuNect ODBC for QuickBase};UID=claude;PWD=YourPassword' 
--					GO
--
--              After you have your linked server in place you're ready to run the stored 
--				procedure below. If the SQL Server copy of the table doesn't exist it will be created.
--				If it does exist then only the records that have been modified since the last
--				sync will be imported. Records that have been deleted on QuickBase will only
--				be deleted here on SQL Server if the @deleteLocalOrphanRecords is equal to 1.
--				If you don't want all the columns from QuickBase you can select a subset of them
--				by specifying @commaDelimitedFieldList with a comma delimited list
--				of field names. You must double quote enclose field names that have spaces in them.
--				For example you must refer to Date Created as "Date Created". You'll have to include
--				the fields Record ID# and Date Modified in the list. These fields could have been renamed to
--				other names. You must use the names as they currently are in QuickBase. 
--				So this would look like this at a minimum:
--
--					@ridFieldName = 'Record_ID_',
--					@dateModifiedFieldName = 'Date_Modified',
--					@commaDelimitedFieldList = N'"Record_ID_", "Date_Modified"'
--
--				Note that the list does not start or end with a comma. Also note that the variables
--				@ridFieldName and @dateModifiedFieldName do not require enclosing double quotes.
--				Since field names are easily changed in QuickBase you may want to refer to the fields 
--				by their field identifiers instead of their field labels. The field identifiers never
--				change, even when the field name is changed.
--				How to Find the FID of a QuickBase Field.
--				So you can set the @commaDelimitedFieldList parameter to:
--
--					@ridFieldName = 'fid3',
--					@dateModifiedFieldName = 'fid2',
--					@commaDelimitedFieldList = N'fid3, fid2, fid6 as "Product ID", fid7 as Name, fid8 as Desc'
--
--				and you'll be insulated from any field name changes made in QuickBase. Note that you cannot
--				create aliases for the Record ID# field and the Date Modified field. You can however use the 
--				field identifier nomenclature. The names you provide for the @ridFieldName and @dateModifiedFieldName
--				variables must be present and unaliased in the @commaDelimitedFieldList variable value unless of
--				course you use its default value of *.
-- =============================================
CREATE PROCEDURE [dbo].[SyncQuickBaseTable] 
@dbid VarChar(13),
@ridFieldName VarChar(255) = 'Record ID_',
@dateModifiedFieldName VarChar(255) = 'Date Modified',
@deleteLocalOrphanRecords bit = 1,
@commaDelimitedFieldList VarChar(8000) = '*'
AS
BEGIN

    DECLARE @tempsql nvarchar(max)
    DECLARE @localCount int
    DECLARE @execLocalCount int
    SET @localCount = 0
    SET @tempSQL = 'SELECT @execLocalCount = count(*) FROM ' + @dbid
    DECLARE @parameter nvarchar(1000)
    SET @parameter = N'@execLocalCount int OUTPUT'
    BEGIN TRY
            EXECUTE sp_executesql @tempSQL, @parameter, @execLocalCount = @localCount OUTPUT
    END TRY
    BEGIN CATCH
            PRINT 'Table already gone'
    END CATCH



	DECLARE @doesExist int
	-- See if there is a local copy of the table that already exists.
	SELECT @doesExist = count(*)  FROM sysobjects WHERE Name = @dbid AND xType= 'U'
	IF @doesExist = 0 or @localCount = 0
		BEGIN
			SET @tempsql = 'DROP TABLE ' + @dbid
			BEGIN TRY
				EXEC( @tempsql)
			END TRY
			BEGIN CATCH
				PRINT 'TABLE ALREADY GONE'
			END CATCH
		-- No local copy exists so we'll create one and then we're done!
		
			EXEC('SELECT * INTO "'+@dbid+'" FROM  OPENQUERY(QuickBase, ''SELECT '+@commaDelimitedFieldList+' FROM "'+@dbid+'"'')')
		END
	ELSE
		-- There already was a local copy.
		BEGIN
			-- Find the latest date modified of the SQL Server records
			-- and then do an inner join with QuickBase records to see which SQL Server records have
			-- changed on the QuickBase server since the last synch. Delete those records here
			-- in SQL Server and then refetch them from QuickBase and put them back into SQL Server.
			EXEC('
			DECLARE @maxDateModified datetime
			SELECT TOP 1 @maxDateModified = "'+@dateModifiedFieldName+'" FROM "'+@dbid+'" ORDER BY "'+@dateModifiedFieldName+'" DESC
			DELETE qdbSQLServer FROM 
				"'+@dbid+'" AS qdbSQLServer
					INNER JOIN 
				(SELECT "'+@ridFieldName+'", "'+@dateModifiedFieldName+'" 
					FROM 
						OPENQUERY(QuickBase, ''SELECT "'+@ridFieldName+'", "'+@dateModifiedFieldName+'" FROM "'+@dbid+'"'')
					WHERE "'+@dateModifiedFieldName+'" > @maxDateModified)
				AS qdbInternet 
				ON qdbSQLServer."'+@ridFieldName+'" = qdbInternet."'+@ridFieldName+'"')
			EXEC('
			DECLARE @maxDateModified datetime
			SELECT TOP 1 @maxDateModified = "'+@dateModifiedFieldName+'" FROM "'+@dbid+'" ORDER BY "'+@dateModifiedFieldName+'" DESC
			INSERT INTO "'+@dbid+'" SELECT * FROM  openquery(QuickBase, ''select '+@commaDelimitedFieldList+' FROM "'+@dbid+'"'') 
			WHERE "'+@dateModifiedFieldName+'" > @maxDateModified
			')
			if @deleteLocalOrphanRecords = 1
				-- Now look for all orphaned records using an outer join
				-- and delete the orphans.
				BEGIN
				EXEC('
					DELETE qdbSQLServer FROM 
					"'+@dbid+'" AS qdbSQLServer
						LEFT OUTER JOIN 
					(SELECT "'+@ridFieldName+'" 
						FROM 
							OPENQUERY(QuickBase, ''SELECT "'+@ridFieldName+'" FROM "'+@dbid+'"''))
					AS qdbInternet 
					ON qdbSQLServer."'+@ridFieldName+'" = qdbInternet."'+@ridFieldName+'"
					WHERE qdbInternet."'+@ridFieldName+'" IS NULL
				')
				END
		END	
END