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