Application Note for QuNect ODBC for QuickBase

Mirroring all Quickbase tables in SQL Server

The following stored procedure will copy all your tables from Quickbase to SQL Server. If a table already exists it will drop (delete) the table and recreate it.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Claude von Roesgen
-- Create date: 1/7/2008
-- Description:	Mirror all Quickbase tables and convert text fields to nvarchar(max)
-- =============================================
-- 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].[mirrorAllQuickBaseTables] 
AS
BEGIN
	SET NOCOUNT ON;
	if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[qunect_tables]

	CREATE TABLE [dbo].[qunect_tables] (
		[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 EXEC sp_tables_ex quickbase
	DECLARE @dbid nvarchar(128)
	DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM qunect_tables
	OPEN table_cursor
	FETCH NEXT FROM table_cursor INTO @dbid
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN		
		IF (@@FETCH_STATUS <> -2)
		BEGIN
			PRINT 'Mirroring ' + @dbid
			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
		END
		FETCH NEXT FROM table_cursor INTO @dbid
	END
	DEALLOCATE table_cursor   
END