Converting Pervasive.SQL Data to SQL Server Data using Dexterity

This article has been archived. It is offered "as is" and will no longer be updated.
TechKnowledge Content

How to convert Pervasive.SQL data to SQL Server data using Dexterity?


The following scripts convert data from Pervasive.SQL to SQL Server.

The first script is a script on a push button of a form that is used to select the data path to the Pervasive.SQL tables. This application would be run in the client's new install that uses MS SQL Server. However, Pervasive.SQL would still need to be installed and running on the machine where the conversion is taking place.

{This is a push button change script on a form where you can select the Btrieve table path}
if empty('(L) Path') then
warning "You must select the path to the Custom Financial tables";
abort script;
end if;

if upper('User ID' of globals) <> "SA" then
warning "You must be the sa user to convert the tables.";
abort script;
end if;

open form GPDC_Create_amAutoGrant; {Form that creates the amAutoGrant stored procedure, used for granting access to sql tables}
close form GPDC_Create_amAutoGrant; {A sample of this form can be located in the 7.0 Integration & SQL Table Creation sample Application}

call Convert_Tables,
'(L) Path';
warning "Tables created & transferred";

in string table_name; {Technical name of your table, this script will be called once for each table}
in string lpath; {Path to the Btrieve data. If you have data in more than one series, then this path will be different for each series. Btrieve must still be loaded on this machine and at the data location so the data can be read.}

local anonymous table source_table;
local boolean result;

result = Table_DisableErrorChecks(true);
result = Table_SetCreateMode(true);

open table source_table with name table_name as DB_TYPE_BTRIEVE, lpath; {opening Btrieve table}
call Convert_Tables2,
table source_table,

close table source_table;
result = Table_FlushCache();
result = Table_DisableErrorChecks(false);

inout anonymous table source_table;
in string table_name;

local boolean result;
local anonymous table destination_table;
local string l_filepath,l_file;
local integer i,p;
local long status;

result = Table_SetCreateMode(true);

open table destination_table with name table_name; {opening and creating new sql table - must be logged in as sa}
l_filepath = Table_GetOSName(table destination_table);
{strip out the pathname, so just have the file os name}
i = pos(l_filepath, ".", 1);
p = pos(l_filepath, ".", i + 1);
l_file = substring(l_filepath, p + 1, length(l_filepath)-p);
call amAutoGrant,status,l_file; {granting access to sql table}

get first table source_table;
while err() <> EOF do
copy from table source_table to table destination_table; {now copy data from btrieve table to sql table}
save table destination_table;
get next table source_table;
end while;

result = Table_SetCreateMode(false);

This article was TechKnowledge Document ID: 29804

Article ID: 859079 - Last Review: 01/17/2015 18:18:17 - Revision: 4.0

  • kbnosurvey kbarchive kbmbsmigrate KB859079