Application Note for QuNect ODBC for QuickBase

Mirroring a QuickBase table in SQL Server

The following stored procedure will copy a table from QuickBase to SQL Server. It takes only one parameter dbid. You can find the DBID of your QuickBase table by reading the How to Find the DBID of a QuickBase 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 QuickBase to SQL Server
-- =============================================
-- This stored procedure depends on the existence 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=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