ODBC for QuickBase
ODBC for QuickBaseApplication 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 2008 and beyond. For earlier versions of SQL Server please search and replace DATETIME2 with DATETIME and datetime2 with datetime. 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 and it requires version x.18.07.56 or higher of QuNect ODBC for QuickBase. If you want to go in the other direction please read Synching a SQL Server table in Quickbase.
/****** Requires version x.18.07.56 or higher of QuNect ODBC for QuickBase**************/
/****** Requires version SQL Server 2016 SP1 onward ***************************************/
-- Helper procedure for schema synchronization (create first - no dependencies)
CREATE OR ALTER PROCEDURE [dbo].[SyncTableSchema]
@tableName nvarchar(128)
AS
BEGIN TRY
DECLARE @tempsql nvarchar(max)
-- Get current table schema
CREATE TABLE #current_schema (
COLUMN_NAME nvarchar(128),
DATA_TYPE nvarchar(128),
CHARACTER_MAXIMUM_LENGTH int,
NUMERIC_PRECISION int,
NUMERIC_SCALE int
)
CREATE TABLE #temp_schema (
COLUMN_NAME nvarchar(128),
DATA_TYPE nvarchar(128),
CHARACTER_MAXIMUM_LENGTH int,
NUMERIC_PRECISION int,
NUMERIC_SCALE int
)
-- Get schema info using set-based approach
INSERT INTO #current_schema
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
INSERT INTO #temp_schema
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '%qunect_temp%' AND TABLE_SCHEMA = 'dbo'
-- Add missing columns using set-based operations
DECLARE @alterStatements TABLE (AlterSQL nvarchar(max))
INSERT INTO @alterStatements (AlterSQL)
SELECT 'ALTER TABLE "' + @tableName + '" ADD "' + temp.COLUMN_NAME + '" ' +
temp.DATA_TYPE +
CASE
WHEN temp.DATA_TYPE = 'varchar' THEN '(' + CAST(temp.CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')'
WHEN temp.DATA_TYPE = 'numeric' THEN '(' + CAST(temp.NUMERIC_PRECISION AS varchar(10)) + ',' + CAST(temp.NUMERIC_SCALE AS varchar(10)) + ')'
ELSE ''
END
FROM #temp_schema temp
LEFT JOIN #current_schema curr ON temp.COLUMN_NAME = curr.COLUMN_NAME
WHERE curr.COLUMN_NAME IS NULL
-- Execute all ALTER statements
DECLARE @sql nvarchar(max)
DECLARE alter_cursor CURSOR FOR SELECT AlterSQL FROM @alterStatements
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC(@sql)
END TRY
BEGIN CATCH
PRINT 'Failed to execute: ' + @sql + ' - ' + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM alter_cursor INTO @sql
END
CLOSE alter_cursor
DEALLOCATE alter_cursor
END TRY
BEGIN CATCH
PRINT 'Error in SyncTableSchema for ' + @tableName + ': ' + ERROR_MESSAGE()
END CATCH
GO
-- Helper procedure for merging data (depends on SyncTableSchema)
CREATE OR ALTER PROCEDURE [dbo].[MergeIncrementalData]
@tableName nvarchar(128),
@columnList nvarchar(max),
@recordIDFieldName nvarchar(128),
@dateModifiedFieldName nvarchar(128),
@keepAllVersionsOfModifiedRecords bit = 0
AS
BEGIN TRY
DECLARE @tempsql nvarchar(max)
-- Handle schema differences
EXEC dbo.SyncTableSchema @tableName = @tableName
IF OBJECT_ID('qunect_temp') IS NOT NULL
BEGIN
-- Merge the data
IF @keepAllVersionsOfModifiedRecords = 0
BEGIN
-- Delete existing records with same IDs, then insert new ones
SET @tempsql = 'DELETE target FROM "' + @tableName + '" target INNER JOIN qunect_temp temp ON target."' + @recordIDFieldName + '" = temp."' + @recordIDFieldName + '"'
EXEC(@tempsql)
SET @tempsql = 'INSERT INTO "' + @tableName + '" (' + @columnList + ') SELECT ' + @columnList + ' FROM qunect_temp'
EXEC(@tempsql)
END
ELSE
BEGIN
-- Just insert all records (keeping versions)
SET @tempsql = 'INSERT INTO "' + @tableName + '" (' + @columnList + ') SELECT ' + @columnList + ' FROM qunect_temp'
EXEC(@tempsql)
END
END
END TRY
BEGIN CATCH
PRINT 'Error in MergeIncrementalData for ' + @tableName + ': ' + ERROR_MESSAGE()
END CATCH
GO
-- Helper procedure for incremental sync (depends on MergeIncrementalData)
CREATE OR ALTER PROCEDURE [dbo].[PerformIncrementalSync]
@tableName nvarchar(128),
@columnList nvarchar(max),
@dateModifiedFieldName nvarchar(128),
@recordIDFieldName nvarchar(128),
@keepAllVersionsOfModifiedRecords bit = 0,
@keepAllDeletedRecords bit = 0
AS
BEGIN TRY
DECLARE @tempsql nvarchar(max)
DECLARE @maxDateModified datetime2
DECLARE @maxDateModifiedText nvarchar(128)
DECLARE @tempTableRecordCount int = 0
-- Get current records from QuickBase for comparison
SET @tempsql = 'SELECT "' + @recordIDFieldName + '" as rid, "' + @dateModifiedFieldName + '" as dateModified INTO qunect_modified FROM OPENQUERY(QUICKBASE, ''select "' + @recordIDFieldName + '", "' + @dateModifiedFieldName + '" FROM "' + @tableName + '"'')'
EXEC(@tempsql)
-- Remove deleted records if configured
IF @keepAllDeletedRecords = 0
BEGIN
SET @tempsql = 'DELETE FROM "' + @tableName + '" WHERE "' + @recordIDFieldName + '" NOT IN(SELECT rid FROM qunect_modified)'
EXEC(@tempsql)
END
-- Get the latest modification date from local table
CREATE TABLE #qunect_last_modified (dateModified datetime2)
SET @tempsql = '
INSERT INTO #qunect_last_modified (dateModified)
SELECT TOP 1 qb.dateModified
FROM qunect_modified qb
LEFT JOIN "' + @tableName + '" local ON qb.rid = local."' + @recordIDFieldName + '" AND qb.dateModified = local."' + @dateModifiedFieldName + '"
WHERE local."' + @dateModifiedFieldName + '" IS NULL OR qb.dateModified <> local."' + @dateModifiedFieldName + '"
ORDER BY qb.dateModified ASC'
EXEC(@tempsql)
SELECT @maxDateModified = dateModified FROM #qunect_last_modified
-- Check if any sync is needed
DECLARE @backupCount int
CREATE TABLE #backup_count (record_count int)
SET @tempsql = 'INSERT INTO #backup_count SELECT COUNT(*) FROM "' + @tableName + '"'
EXEC(@tempsql)
SELECT @backupCount = record_count FROM #backup_count
DROP TABLE #backup_count
IF @maxDateModified IS NULL AND @backupCount > 0
BEGIN
PRINT 'No records modified since last sync for table: ' + @tableName
-- Cleanup
IF OBJECT_ID('qunect_modified') IS NOT NULL DROP TABLE qunect_modified
RETURN
END
IF OBJECT_ID('qunect_temp') IS NOT NULL DROP TABLE qunect_temp
-- Get incremental records
SET @maxDateModified = DATEADD(second, -1, @maxDateModified)
SET @maxDateModifiedText = '{ts ''''' + CONVERT(VARCHAR(23), @maxDateModified, 121) + '''''}'
PRINT 'Get incremental records'
SET @tempsql = 'SELECT * INTO qunect_temp FROM OPENQUERY(QUICKBASE, ''select ' + @columnList + ' FROM "' + @tableName + '" WHERE ' + @dateModifiedFieldName + ' > ' + @maxDateModifiedText + ''')'
EXEC(@tempsql)
SET @tempTableRecordCount = 0
IF OBJECT_ID('qunect_temp') IS NOT NULL
BEGIN
PRINT 'Remove duplicates that already exist locally'
-- Remove duplicates that already exist locally
SET @tempsql = 'DELETE temp FROM qunect_temp temp INNER JOIN "' + @tableName + '" local ON temp."' + @recordIDFieldName + '" = local."' + @recordIDFieldName + '" AND temp."' + @dateModifiedFieldName + '" = local."' + @dateModifiedFieldName + '"'
EXEC(@tempsql)
SELECT @tempTableRecordCount = COUNT(*) FROM qunect_temp
END
PRINT 'Counting successfully processed records.'
IF @tempTableRecordCount > 0
BEGIN
EXEC dbo.MergeIncrementalData
@tableName = @tableName,
@columnList = @columnList,
@recordIDFieldName = @recordIDFieldName,
@dateModifiedFieldName = @dateModifiedFieldName,
@keepAllVersionsOfModifiedRecords = @keepAllVersionsOfModifiedRecords
PRINT 'Successfully processed ' + CAST(@tempTableRecordCount AS varchar(10)) + ' records for table: ' + @tableName
END
ELSE
BEGIN
PRINT 'No new records to process for table: ' + @tableName
END
-- Cleanup temp tables
IF OBJECT_ID('qunect_modified') IS NOT NULL DROP TABLE qunect_modified
IF OBJECT_ID('qunect_temp') IS NOT NULL DROP TABLE qunect_temp
END TRY
BEGIN CATCH
PRINT 'Error in PerformIncrementalSync for ' + @tableName + ': ' + ERROR_MESSAGE()
PRINT @tempsql
-- Cleanup on error
IF OBJECT_ID('qunect_modified') IS NOT NULL DROP TABLE qunect_modified
IF OBJECT_ID('qunect_temp') IS NOT NULL DROP TABLE qunect_temp
END CATCH
GO
-- Helper procedure to process individual tables (depends on PerformIncrementalSync)
CREATE OR ALTER PROCEDURE [dbo].[ProcessSingleQuickBaseTable]
@tableName nvarchar(128),
@keepAllVersionsOfModifiedRecords bit = 0,
@keepAllDeletedRecords bit = 0,
@ignoreVirtualFields bit = 1
AS
BEGIN TRY
DECLARE @tempsql nvarchar(max)
DECLARE @columnList nvarchar(max)
DECLARE @dateModifiedFieldName nvarchar(128)
DECLARE @recordIDFieldName nvarchar(128)
DECLARE @doesExist int
-- Clean up any existing temp tables
IF OBJECT_ID('qunect_field_columns') IS NOT NULL DROP TABLE qunect_field_columns
IF OBJECT_ID('qunect_temp') IS NOT NULL DROP TABLE qunect_temp
IF OBJECT_ID('qunect_modified') IS NOT NULL DROP TABLE qunect_modified
-- Get field information
SET @tempsql = 'SELECT * INTO qunect_field_columns FROM OPENQUERY(QUICKBASE, ''select * FROM "' + @tableName + '~fields"'
IF @ignoreVirtualFields = 1
SET @tempsql = @tempsql + ' WHERE mode = '''''''' OR mode IS NULL OR field_type = ''''recordid'''' '')'
ELSE
SET @tempsql = @tempsql + ' WHERE (mode = '''''''' OR mode IS NULL OR field_type = ''''recordid'''') OR (mode = ''''virtual'''' AND field_type NOT IN (''''dblink'''',''''url'''') ) '')'
EXEC(@tempsql)
-- Build column list using STRING_AGG (SQL Server 2017+) or XML PATH for older versions
IF @@VERSION LIKE '%Microsoft SQL Server 2017%' OR @@VERSION LIKE '%Microsoft SQL Server 201[89]%' OR @@VERSION LIKE '%Microsoft SQL Server 202%'
BEGIN
-- Use STRING_AGG for newer versions
SELECT @columnList = STRING_AGG('"' + REPLACE(COLUMN_NAME, '''', '''''') + '"', ',')
FROM qunect_field_columns
END
ELSE
BEGIN
-- Use XML PATH for older versions
SELECT @columnList = STUFF((
SELECT ',' + '"' + REPLACE(COLUMN_NAME, '''', '''''') + '"'
FROM qunect_field_columns
FOR XML PATH('')
), 1, 1, '')
END
-- Get key field names
SELECT @dateModifiedFieldName = COLUMN_NAME FROM qunect_field_columns WHERE fid = 2
SELECT @recordIDFieldName = COLUMN_NAME FROM qunect_field_columns WHERE fid = 3
SELECT @doesExist = COUNT(*) FROM sysobjects WHERE Name = @tableName AND xType = 'U'
-- If table doesn't exist, create it with all data
IF @doesExist = 0
BEGIN
SET @tempsql = 'SELECT * INTO "' + @tableName + '" FROM OPENQUERY(QUICKBASE, ''select ' + @columnList + ' FROM "' + @tableName + '"'')'
EXEC(@tempsql)
-- Cleanup
IF OBJECT_ID('qunect_field_columns') IS NOT NULL DROP TABLE qunect_field_columns
RETURN
END
-- Table exists, do incremental sync
EXEC dbo.PerformIncrementalSync
@tableName = @tableName,
@columnList = @columnList,
@dateModifiedFieldName = @dateModifiedFieldName,
@recordIDFieldName = @recordIDFieldName,
@keepAllVersionsOfModifiedRecords = @keepAllVersionsOfModifiedRecords,
@keepAllDeletedRecords = @keepAllDeletedRecords
-- Cleanup
IF OBJECT_ID('qunect_field_columns') IS NOT NULL DROP TABLE qunect_field_columns
END TRY
BEGIN CATCH
PRINT 'Error in ProcessSingleQuickBaseTable for ' + @tableName + ': ' + ERROR_MESSAGE()
-- Cleanup on error
IF OBJECT_ID('qunect_field_columns') IS NOT NULL DROP TABLE qunect_field_columns
IF OBJECT_ID('qunect_temp') IS NOT NULL DROP TABLE qunect_temp
IF OBJECT_ID('qunect_modified') IS NOT NULL DROP TABLE qunect_modified
END CATCH
GO
-- Main procedure (depends on ProcessSingleQuickBaseTable)
CREATE OR ALTER PROCEDURE [dbo].[SyncQuickBaseTablesToSQLServer]
/****** Requires version x.18.07.56 or higher of QuNect ODBC for QuickBase ***************************************/
@keepAllVersionsOfModifiedRecords bit = 0,
@keepAllDeletedRecords bit = 0,
@ignoreVirtualFields bit = 1
AS
BEGIN TRY
SET NOCOUNT ON;
-- Check for concurrent execution
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.'
RETURN -1
END
-- Create sync control table
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,
[ProcessingOrder] int IDENTITY(1,1),
[IsProcessed] bit DEFAULT 0,
[HasError] bit DEFAULT 0,
[ErrorMessage] nvarchar(max) NULL
) ON [PRIMARY]
INSERT qunect_tables_to_sync (TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, REMARKS)
EXEC sp_tables_ex quickbase
DECLARE @numQuickBaseTables int, @numQuickBaseTablesBackedUp int = 0
SELECT @numQuickBaseTables = COUNT(*) FROM qunect_tables_to_sync WHERE [TABLE_TYPE] = 'TABLE'
-- Main processing loop using WHILE with set-based operations
WHILE EXISTS (SELECT 1 FROM qunect_tables_to_sync WHERE TABLE_TYPE = 'TABLE' AND IsProcessed = 0 AND HasError = 0)
BEGIN
DECLARE @currentTable nvarchar(128)
-- Get next table to process
SELECT TOP 1 @currentTable = TABLE_NAME
FROM qunect_tables_to_sync
WHERE TABLE_TYPE = 'TABLE' AND IsProcessed = 0 AND HasError = 0
ORDER BY ProcessingOrder
PRINT ''
PRINT '####################'
PRINT 'Processing table: ' + @currentTable
BEGIN TRY
EXEC dbo.ProcessSingleQuickBaseTable
@tableName = @currentTable,
@keepAllVersionsOfModifiedRecords = @keepAllVersionsOfModifiedRecords,
@keepAllDeletedRecords = @keepAllDeletedRecords,
@ignoreVirtualFields = @ignoreVirtualFields
-- Mark as successfully processed
UPDATE qunect_tables_to_sync
SET IsProcessed = 1
WHERE TABLE_NAME = @currentTable
SET @numQuickBaseTablesBackedUp = @numQuickBaseTablesBackedUp + 1
END TRY
BEGIN CATCH
-- Mark as error
UPDATE qunect_tables_to_sync
SET HasError = 1,
ErrorMessage = ERROR_MESSAGE()
WHERE TABLE_NAME = @currentTable
PRINT 'Error processing table ' + @currentTable + ': ' + ERROR_MESSAGE()
END CATCH
END
-- Cleanup
DROP TABLE [dbo].[qunect_tables_to_sync]
RETURN @numQuickBaseTables - @numQuickBaseTablesBackedUp
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[qunect_tables_to_sync]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[qunect_tables_to_sync]
RETURN -1
END CATCH
GO