Application Note for QuNect ODBC for QuickBase
Synching all QuickBase tables in SQL Server
The following stored procedure will synch all tables the linked server has access to from QuickBase to SQL Server. After running this stored procedure the tables in SQL Server will be up to date with the tables in QuickBase. This stored procedure takes two optional parameters. @keepAllVersionsOfModifiedRecords is set to false by default. If you set it to true then you are keeping a history of the state of all your records at each point that this stored procedure was run. The optional parameter @keepAllDeletedRecords is also set to false by default. If you set it to true then deleted records are not removed from the SQL Server version of the QuickBase table. This is useful if you want the capability to restore deleted records to QuickBase. However if your objective is to have an exact SQL Server image of your QuickBase data then you'll want to leave these parameters at their default value of false. This stored procedure will handle schema changes in QuickBase by adding new columns into the existing SQL Server table. If columns are renamed, deleted or changed to a different type in QuickBase then this stored procedure will make a copy of the corresponding existing SQL Server table with a timestamp in the name and start from scratch with a new SQL Server table. This stored procedure only modifies SQL Server tables, not the corresponding QuickBase tables. If you want to go in the other direction please read Synching a SQL Server table in QuickBase.
GO
/****** Object: StoredProcedure [dbo].[SyncQuickBaseTablesToSQLServer] Script Date: 05/02/2012 10:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SyncQuickBaseTablesToSQLServer]
@keepAllVersionsOfModifiedRecords bit = 0,
@keepAllDeletedRecords bit = 0
AS
BEGIN TRY
SET NOCOUNT ON;
if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_tables_to_sync]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'This procedure is terminating without performing any backups because'
PRINT 'an instance of this stored procedure is already running.'
PRINT 'This is based on detecting the presence of the table called'
PRINT '"qunect_tables_to_sync". If this table exists and another instance of this stored'
PRINT 'procedure is not running then manually delete the table called "qunect_tables_to_sync"'
PRINT 'and rerun this stored procedure. The last thing this stored procedure does is'
PRINT 'drop the table called "qunect_tables_to_sync". Do "qunect_tables_to_sync" should not exist if this'
PRINT 'stored procedure is not running.'
END
CREATE TABLE [dbo].[qunect_tables_to_sync] (
[TABLE_QUALIFIER] [nvarchar] (128) NULL ,
[TABLE_OWNER] [nvarchar] (128) NULL ,
[TABLE_NAME] [nvarchar] (128) NULL ,
[TABLE_TYPE] [nvarchar] (128) NULL ,
[REMARKS] [varchar] (254) NULL
) ON [PRIMARY]
INSERT qunect_tables_to_sync EXEC sp_tables_ex quickbase
DECLARE @dbid nvarchar(128)
DECLARE @numQuickBaseTables int
DECLARE @numQuickBaseTablesBackedUp int
DECLARE @dateModifiedFieldName nvarchar(128)
DECLARE @recordIDFieldName nvarchar(128)
DECLARE @tempsql nvarchar(max)
SELECT @numQuickBaseTables = count(*) FROM qunect_tables_to_sync
SET @numQuickBaseTablesBackedUp = 0
DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM qunect_tables_to_sync
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @dbid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT ''
PRINT '####################'
PRINT 'Backing up ' + @dbid
BEGIN
DECLARE @doesTempExist int
DECLARE @doesExist int
SELECT @doesExist = count(*) FROM sysobjects Where Name = @dbid AND xType= 'U'
IF @doesExist = 0
BEGIN TRY
SET @tempsql = 'SELECT * INTO "'+@dbid+'" FROM openquery(QuickBase, ''select * FROM "'+@dbid+'"'')'
PRINT @tempsql
EXEC(@tempsql)
SET @numQuickBaseTablesBackedUp = @numQuickBaseTablesBackedUp + 1
FETCH NEXT FROM table_cursor INTO @dbid
CONTINUE
END TRY
BEGIN CATCH
PRINT 'Non-Incremental approach failed, no records retrieved from ' + @dbid
PRINT ERROR_MESSAGE()
FETCH NEXT FROM table_cursor INTO @dbid
CONTINUE
END CATCH
SELECT @doesTempExist = count(*) FROM sysobjects Where Name = 'qunect_temp' AND xType= 'U'
IF @doesTempExist > 0
BEGIN TRY
PRINT 'Dropping qunect_temp'
--need to clean up in case things didn't get cleaned up from the last run
EXEC('DROP TABLE qunect_temp')
END TRY
BEGIN CATCH
PRINT 'Could not drop qunect_temp'
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRY
--Here we need to get the largest timestamp from the local table
--and use it to get the records we need from QuickBase into a temp table
PRINT 'Attempting to get incremental records from:"'+@dbid+'"'
IF exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_backup_columns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [dbo].[qunect_backup_columns]
END
CREATE TABLE [dbo].[qunect_backup_columns](
[TABLE_QUALIFIER] [nvarchar](128) NULL,
[TABLE_OWNER] [nvarchar](128) NULL,
[TABLE_NAME] [nvarchar](128) NULL,
[COLUMN_NAME] [nvarchar](128) NULL,
[DATA_TYPE] [smallint] NULL,
[TYPE_NAME] [nvarchar](128) NULL,
[PRECISION] [int] NULL,
[LENGTH] [int] NULL,
[SCALE] [smallint] NULL,
[RADIX] [smallint] NULL,
[NULLABLE] [smallint] NULL,
[REMARKS] [varchar](254) NULL,
[COLUMN_DEF] [nvarchar](4000) NULL,
[SQL_DATA_TYPE] [smallint] NULL,
[SQL_DATETIME_SUB] [smallint] NULL,
[CHAR_OCTET_LENGTH] [int] NULL,
[ORDINAL_POSITION] [int] NULL,
[IS_NULLABLE] [varchar](254) NULL,
[SS_DATA_TYPE] [tinyint] NULL
) ON [PRIMARY]
DECLARE @escapeddbid nvarchar(256)
SET @escapeddbid = REPLACE(@dbid, '_', '[_]')
INSERT qunect_backup_columns EXEC('sp_columns "' + @escapeddbid + '"')
SELECT TOP 1 @dateModifiedFieldName = "COLUMN_NAME" FROM qunect_backup_columns WHERE [ORDINAL_POSITION] = 2
SELECT TOP 1 @recordIDFieldName = "COLUMN_NAME" FROM qunect_backup_columns WHERE [ORDINAL_POSITION] = 3
BEGIN TRY
PRINT 'Dropping qunect_modified'
--need to clean up in case things didn't get cleaned up from the last run
EXEC('DROP TABLE "qunect_modified"')
END TRY
BEGIN CATCH
PRINT 'No qunect_modified to drop'
END CATCH
BEGIN TRY
SET @tempsql = 'SELECT TOP 1 "' + @dateModifiedFieldName + '" AS qdbDateModified INTO "qunect_modified" FROM "'+@dbid+'" ORDER BY "' + @dateModifiedFieldName + '" DESC'
PRINT @tempsql
EXEC(@tempsql)
DECLARE @maxDateModified datetime
DECLARE @maxDateModifiedText nvarchar(128)
SELECT TOP 1 @maxDateModified = qdbDateModified FROM "qunect_modified" ORDER BY qdbDateModified DESC
SET @maxDateModified = ISNULL(@maxDateModified, 0)
SET @maxDateModified = DATEADD(millisecond, 2, @maxDateModified)
SET @maxDateModifiedText = (SELECT CONVERT(VARCHAR(23), @maxDateModified, 121))
SET @maxDateModifiedText = '{ts '''''+ @maxDateModifiedText + '''''}'
PRINT 'Got last modified date from local records: ' + @maxDateModifiedText
SET @tempsql = 'SELECT * INTO qunect_temp FROM openquery(QuickBase, ''select * FROM "'+@dbid+'" WHERE ' + @dateModifiedFieldName + ' > ' + @maxDateModifiedText + ' '')'
PRINT @tempsql
EXEC(@tempsql)
END TRY
BEGIN CATCH
PRINT 'Incremental approach failed, could not create qunect_temp.'
PRINT ERROR_MESSAGE()
END CATCH
SELECT @doesTempExist = count(*) FROM sysobjects Where Name = 'qunect_temp' AND xType= 'U'
IF @doesTempExist = 0
BEGIN TRY
PRINT 'Incremental approach failed, retrieveing all records from '+@dbid+' into qunect_temp.'
PRINT ERROR_MESSAGE()
SET @tempsql = 'SELECT * INTO qunect_temp FROM openquery(QuickBase, ''select * FROM "'+@dbid+'"'')'
PRINT @tempsql
EXEC(@tempsql)
END TRY
BEGIN CATCH
PRINT 'Non-Incremental approach failed, no additional records retrieved from '+@dbid+' into qunect_temp.'
PRINT ERROR_MESSAGE()
END CATCH
END TRY
BEGIN CATCH
PRINT 'Both incremental and non-incremental approaches failed for ' + @dbid
END CATCH
SELECT @doesTempExist = count(*) FROM sysobjects Where Name = 'qunect_temp' AND xType= 'U'
DECLARE @tempTableRecordCount int
SET @tempTableRecordCount = 0
IF @doesTempExist > 0
BEGIN
SELECT @tempTableRecordCount = count(*) FROM qunect_temp
PRINT 'Retrieved ' + CONVERT(VARCHAR(23), @tempTableRecordCount) + ' records modified since last synch.'
END
IF @tempTableRecordCount > 0
BEGIN
SELECT @doesExist = count(*) FROM sysobjects Where Name = @dbid AND xType= 'U'
IF @doesTempExist > 0 and @doesExist > 0
BEGIN
--now we need to try to do a specific insert with named columns
if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_backup_columns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qunect_backup_columns]
CREATE TABLE [dbo].[qunect_backup_columns](
[TABLE_QUALIFIER] [nvarchar](128) NULL,
[TABLE_OWNER] [nvarchar](128) NULL,
[TABLE_NAME] [nvarchar](128) NULL,
[COLUMN_NAME] [nvarchar](128) NULL,
[DATA_TYPE] [smallint] NULL,
[TYPE_NAME] [nvarchar](128) NULL,
[PRECISION] [int] NULL,
[LENGTH] [int] NULL,
[SCALE] [smallint] NULL,
[RADIX] [smallint] NULL,
[NULLABLE] [smallint] NULL,
[REMARKS] [varchar](254) NULL,
[COLUMN_DEF] [nvarchar](4000) NULL,
[SQL_DATA_TYPE] [smallint] NULL,
[SQL_DATETIME_SUB] [smallint] NULL,
[CHAR_OCTET_LENGTH] [int] NULL,
[ORDINAL_POSITION] [int] NULL,
[IS_NULLABLE] [varchar](254) NULL,
[SS_DATA_TYPE] [tinyint] NULL
) ON [PRIMARY]
SET @escapeddbid = REPLACE(@dbid, '_', '[_]')
INSERT qunect_backup_columns EXEC('sp_columns "' + @escapeddbid + '"')
if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_temp_columns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qunect_temp_columns]
CREATE TABLE [dbo].[qunect_temp_columns](
[TABLE_QUALIFIER] [nvarchar](128) NULL,
[TABLE_OWNER] [nvarchar](128) NULL,
[TABLE_NAME] [nvarchar](128) NULL,
[COLUMN_NAME] [nvarchar](128) NULL,
[DATA_TYPE] [smallint] NULL,
[TYPE_NAME] [nvarchar](128) NULL,
[PRECISION] [int] NULL,
[LENGTH] [int] NULL,
[SCALE] [smallint] NULL,
[RADIX] [smallint] NULL,
[NULLABLE] [smallint] NULL,
[REMARKS] [varchar](254) NULL,
[COLUMN_DEF] [nvarchar](4000) NULL,
[SQL_DATA_TYPE] [smallint] NULL,
[SQL_DATETIME_SUB] [smallint] NULL,
[CHAR_OCTET_LENGTH] [int] NULL,
[ORDINAL_POSITION] [int] NULL,
[IS_NULLABLE] [varchar](254) NULL,
[SS_DATA_TYPE] [tinyint] NULL
) ON [PRIMARY]
INSERT qunect_temp_columns EXEC('sp_columns qunect_temp')
SELECT TOP 1 @dateModifiedFieldName = "COLUMN_NAME" FROM qunect_backup_columns WHERE [ORDINAL_POSITION] = 2
SELECT TOP 1 @recordIDFieldName = "COLUMN_NAME" FROM qunect_backup_columns WHERE [ORDINAL_POSITION] = 3
--now we want to do an inner join on these two tables
DECLARE @commonColumnCount int
SELECT @commonColumnCount = count(qunect_backup_columns.COLUMN_NAME)
FROM qunect_backup_columns INNER JOIN
qunect_temp_columns ON qunect_backup_columns.COLUMN_NAME = qunect_temp_columns.COLUMN_NAME AND
qunect_backup_columns.DATA_TYPE = qunect_temp_columns.DATA_TYPE AND
qunect_backup_columns.IS_NULLABLE = qunect_temp_columns.IS_NULLABLE AND
qunect_backup_columns.PRECISION = qunect_temp_columns.PRECISION AND
qunect_backup_columns.SS_DATA_TYPE = qunect_temp_columns.SS_DATA_TYPE
DECLARE @columnCount int
SELECT @columnCount = count(qunect_backup_columns.COLUMN_NAME)
FROM qunect_backup_columns
PRINT CONVERT(VARCHAR(23), @columnCount) + ' columns in backup and ' + CONVERT(VARCHAR(23), @commonColumnCount) + ' columns in common.'
DECLARE @mergeSuccessful bit
SET @mergeSuccessful = 0
IF @commonColumnCount = @columnCount
BEGIN TRY
--we should be able to merge the contents of the backup table into the temp table
DECLARE column_cursor CURSOR FOR SELECT COLUMN_NAME FROM qunect_backup_columns
DECLARE @columnName nvarchar(128)
DECLARE @columnList nvarchar(max)
SET @columnList = ''
DECLARE @comma nvarchar(1)
SET @comma = ''
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @columnName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @columnList = @columnList + @comma + '"' + @columnName + '"'
SET @comma = ','
END
FETCH NEXT FROM column_cursor INTO @columnName
END
DEALLOCATE column_cursor
--now we need to put a primary key on
BEGIN TRY
PRINT 'Adding primary key constraint on qunect_temp'
SET @tempsql = 'ALTER TABLE qunect_temp ADD CONSTRAINT pk_rid_date_mod PRIMARY KEY ("Record ID_", "Date Modified");'
SET @tempsql = REPLACE(@tempsql, 'Record ID_', @recordIDFieldName)
SET @tempsql = REPLACE(@tempsql, 'Date Modified', @dateModifiedFieldName)
EXEC(@tempsql);
END TRY
BEGIN CATCH
PRINT 'Could not add constraint ' + @dbid
PRINT @tempsql
PRINT ERROR_MESSAGE()
END CATCH
SET @tempsql = 'INSERT INTO qunect_temp (' + @columnList + ') SELECT DISTINCT ' + @columnList + ' FROM "' + @dbid + '"'
PRINT @tempsql
EXEC(@tempsql)
SET @mergeSuccessful = 1
END TRY
BEGIN CATCH
PRINT 'Could not merge backup into temp table.'
PRINT ERROR_MESSAGE()
SET @mergeSuccessful = 0
END CATCH
IF @mergeSuccessful = 0
BEGIN TRY
--so here we should rename the backup table with today's timestamp
--and fetch all the records into the backup table
PRINT 'Merge unsuccessful, copying the backup table to a table with today''s timestamp as suffix in name.'
DECLARE @nowText nvarchar(256)
SET @nowText = @dbid + CONVERT(VARCHAR(23), GETDATE(), 121)
SET @tempsql = 'SELECT * INTO "' + @nowText + '" FROM "' + @dbid + '"'
PRINT @tempsql
EXEC(@tempsql)
BEGIN TRY
PRINT 'Dropping "'+@dbid+'"'
EXEC('DROP TABLE "'+@dbid+'"')
SET @tempsql = 'SELECT * INTO "'+@dbid+'" FROM openquery(QuickBase, ''select * FROM "'+@dbid+'"'')'
PRINT @tempsql
EXEC(@tempsql)
FETCH NEXT FROM table_cursor INTO @dbid
CONTINUE
END TRY
BEGIN CATCH
PRINT 'Could not drop local ' + @dbid + ' and fetch new copy.'
PRINT ERROR_MESSAGE()
END CATCH
END TRY
BEGIN CATCH
PRINT 'Copying the backup table to one with today''s timestamp as suffix.'
PRINT ERROR_MESSAGE()
END CATCH
END
IF @keepAllVersionsOfModifiedRecords = 0 and @mergeSuccessful = 1
BEGIN TRY
PRINT 'Deduping qunect_temp of ' + @dbid
SET @tempsql = '
DECLARE @rid int;
DECLARE @prev_rid int;
SET @prev_rid = 0;
DECLARE rid_cursor CURSOR FORWARD_ONLY FOR
SELECT "Record ID_"
FROM qunect_temp
ORDER BY "Record ID_" DESC, "Date Modified" DESC FOR UPDATE;
OPEN rid_cursor;
FETCH NEXT FROM rid_cursor INTO @rid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @rid = @prev_rid
BEGIN
DELETE FROM qunect_temp
WHERE CURRENT OF rid_cursor;
END
SET @prev_rid = @rid
FETCH NEXT FROM rid_cursor INTO @rid
END
DEALLOCATE rid_cursor'
SET @tempsql = REPLACE(@tempsql, 'Record ID_', @recordIDFieldName)
SET @tempsql = REPLACE(@tempsql, 'Date Modified', @dateModifiedFieldName)
EXEC(@tempsql);
END TRY
BEGIN CATCH
PRINT 'Could not dedupe ' + @dbid
PRINT @tempsql
PRINT ERROR_MESSAGE()
END CATCH
IF @keepAllDeletedRecords = 0
BEGIN TRY
PRINT 'Removing deleted records of ' + @dbid
SET @tempsql = 'DELETE FROM qunect_temp WHERE "Record ID_" NOT IN(SELECT fid3 FROM openquery(QuickBase, ''select fid3 from "dbid"''))'
SET @tempsql = REPLACE(@tempsql, 'Record ID_', @recordIDFieldName)
SET @tempsql = REPLACE(@tempsql, 'dbid', @dbid)
EXEC(@tempsql);
END TRY
BEGIN CATCH
PRINT 'Could not delete records from ' + @dbid
PRINT @tempsql
PRINT ERROR_MESSAGE()
END CATCH
--so now we copy from the qunect_temp into a new backup copy
--but first we have to drop the constraint
BEGIN TRY
PRINT 'Dropping CONSTRAINT on qunect_temp'
EXEC('ALTER TABLE qunect_temp DROP CONSTRAINT pk_rid_date_mod')
END TRY
BEGIN CATCH
PRINT 'Could not drop CONSTRAINT on qunect_temp'
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRY
PRINT 'Dropping "'+@dbid+'"'
EXEC('DROP TABLE "'+@dbid+'"')
END TRY
BEGIN CATCH
PRINT 'Could not drop ' + @dbid
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRY
SET @tempsql = 'SELECT * INTO "' + @dbid + '" FROM qunect_temp'
PRINT @tempsql
EXEC(@tempsql)
PRINT 'Copying temp data into backup table succeeded.'
SET @numQuickBaseTablesBackedUp = @numQuickBaseTablesBackedUp + 1
END TRY
BEGIN CATCH
PRINT 'Copying temp data into backup table failed.'
END CATCH
END
ELSE
BEGIN
PRINT 'No records have been modified since last synch.'
SET @numQuickBaseTablesBackedUp = @numQuickBaseTablesBackedUp + 1
END
END
END
FETCH NEXT FROM table_cursor INTO @dbid
END
DEALLOCATE table_cursor
if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_tables_to_sync]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [dbo].[qunect_tables_to_sync]
END
return @numQuickBaseTables - @numQuickBaseTablesBackedUp
END TRY
BEGIN CATCH
BEGIN
drop table [dbo].[qunect_tables_to_sync]
END
END CATCH