Application Note for QuNect ODBC for Quick Base

Mirroring a Quick Base table in SQL Server

The following stored procedure will copy a table from Quick Base to SQL Server. It takes only one parameter dbid. You can find the DBID of your Quick Base table by reading the How to Find the DBID of a Quick Base Table application note.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Claude von Roesgen
-- Create date: 4/17/2007
-- Description: Mirror table from Quick Base to SQL Server
-- =============================================
-- This stored procedure depends on the existence of a linked server called
-- Quick Base. To create this linked server run the following SQL:
--
-- EXEC sp_addlinkedserver 
-- @server = 'Quick Base', 
-- @provider = 'MSDASQL',
-- @srvproduct = 'QuNect',
-- @provstr = 'DRIVER={QuNect ODBC for Quick Base};UID=YourQuickBaseEmailOrScreenName;PWD=YourPassword' 
-- GO
-- After you have your linked server in place you're ready to run the stored 
-- procedure below. 

CREATE PROCEDURE [dbo].[mirrorQuickBaseTable] 
@dbid Varchar(255)
 
AS

BEGIN
	DECLARE @doesExist int
	SELECT @doesExist = count(*) FROM sysobjects Where Name = @dbid AND xType= 'U'
	IF @doesExist > 0 
	BEGIN
		EXEC('DROP TABLE "'+@dbid+'"')
	END
	BEGIN TRY
		EXEC('SELECT * INTO "'+@dbid+'" FROM OPENQUERY(QUICKBASE, ''select * from "'+@dbid+'"'')')
	END TRY
	BEGIN CATCH
		PRINT 'Could not mirror table ' + @dbid + ' ' + ERROR_MESSAGE()
	END CATCH
END