Example activation script 1

This SQL script demonstrates a simple but insecure way to activate bulk import on an iBase database.

You can copy and paste this script into SQL Server Management Studio. Some of the values that are used in this script must be modified for your iBase database.

USE Your_DB; 

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace all occurrences of the string 'Your_DB' with the 
SQL Server name of the iBase database for which Bulk Import 
will be activated.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Add or update an iBase configuration setting. The 
'SQLServer:BulkImportDataFileLocation' configuration 
setting specifies a UNC path for a folder that Bulk Import 
can use for creating the temporary files.
iBase users will need to have a Windows account that has 
read/write permissions on this shared network location.
The account under which SQL Server runs requires read 
permission on this shared network location.
*/

IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportDataFileLocation') 
   UPDATE _Configuration_Text SET Data = '\\computername\sharedfolder\' 
   WHERE Item = 'SQLServer:BulkImportDataFileLocation' ;
ELSE 
	BEGIN
		INSERT INTO _Configuration_Def (Item, Encrypted) VALUES 
		  ('SQLServer:BulkImportDataFileLocation', 0);
	    INSERT INTO _Configuration_Text (Item, Data) VALUES 
		  ('SQLServer:BulkImportDataFileLocation', 
		  '\\computername\sharedfolder\');
	END
PRINT 'Bulk Import Data File configuration setting updated.';

/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace the string '\\computername\sharedfolder\' 
with the name of your shared network location.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Add or update iBase configuration settings: 
'SQLServer:BulkImportColumnDelimiter' and 
'SQLServer:BulkImportRowDelimiter'.
These configuration settings specify terminators to be used 
in the temporary data file that Bulk Import creates as part 
of the Bulk Import process. They must be specified using 
(escape) characters that are understood by the T-SQL BULK 
INSERT statement.
Under normal circumstances you will not need to change these 
configuration settings.
*/

DECLARE @fieldTerminator VARCHAR(20);
DECLARE @rowTerminator VARCHAR(20);
SET @fieldTerminator = '\0F\0'
-- !!! If you require a different field terminator, specify it here.!!
SET @rowTerminator = '\0R\0' 
-- !!! If you require a different row terminator, specify it here.!!
IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportColumnDelimiter') 
   UPDATE _Configuration_Text SET Data = @fieldTerminator 
   WHERE Item = 'SQLServer:BulkImportColumnDelimiter' ;
ELSE 
	BEGIN
	   INSERT INTO _Configuration_Def (Item, Encrypted) 
	   VALUES ('SQLServer:BulkImportColumnDelimiter', 0);
	   INSERT INTO _Configuration_Text (Item, Data) 
	   VALUES ('SQLServer:BulkImportColumnDelimiter', @fieldTerminator);
   END
IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportRowDelimiter') 
   UPDATE _Configuration_Text SET Data = @rowTerminator 
   WHERE Item = 'SQLServer:BulkImportRowDelimiter' ;
ELSE 
	BEGIN
	   INSERT INTO _Configuration_Def (Item, Encrypted) 
	   VALUES ('SQLServer:BulkImportRowDelimiter', 0);
	   INSERT INTO _Configuration_Text (Item, Data) 
	   VALUES ('SQLServer:BulkImportRowDelimiter', @rowTerminator);
	END
PRINT 'Row and column delimiter configuration settings updated.';

/*
Add or update the iBase configuration setting: 
'SQLServer:BulkImportIdentifierSelectivityThreshold'.
This configuration setting specifies the threshold ratio 
of source to target records, after which Bulk Import 
fails because there are too many matches; i.e. the import 
identifiers are not sufficiently selective.
Under normal circumstances you will not need to change this 
configuration setting.
*/

IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportIdentifierSelectivityThreshold') 
   UPDATE _Configuration_Text SET Data = @rowTerminator 
   WHERE Item = 'SQLServer:BulkImportIdentifierSelectivityThreshold' ;
ELSE 
	BEGIN
	   INSERT INTO _Configuration_Def (Item, Encrypted) 
	   VALUES ('SQLServer:BulkImportIdentifierSelectivityThreshold', 0);
	   INSERT INTO _Configuration_Text (Item, Data) 
	   VALUES ('SQLServer:BulkImportIdentifierSelectivityThreshold', 
				  '10');
	END
PRINT 'Identifier Selectivity Threshold 
              configuration settings updated.';

/*
Delete _BulkInsert stored procedure.
*/

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = '_BulkInsert' )
BEGIN
   DROP PROCEDURE dbo._BulkInsert
      PRINT '_BulkInsert stored procedure deleted.'
END
ELSE
BEGIN
    PRINT '_BulkInsert stored procedure not found.'
END
USE Your_DB;
go
IF EXISTS (SELECT * FROM sys.database_principals
   WHERE name = N'i2iBaseBulkInsertUser_Your_DB') 
BEGIN
   DROP USER i2iBaseBulkInsertUser_Your_DB;
   PRINT 'Bulk Insert user deleted.'
END
ELSE
BEGIN
   PRINT 'Bulk Insert user not found.'
END
go

/*
Delete bulk insert login
*/

USE master; 
go
IF EXISTS (SELECT * FROM sys.server_principals 
   WHERE name = N'i2iBaseBulkInsertLogin_Your_DB')
BEGIN 
   DROP LOGIN i2iBaseBulkInsertLogin_Your_DB 
   PRINT 'Bulk insert login deleted.' 
END
ELSE
BEGIN
    PRINT 'Bulk Insert login not found.'
END
USE master;
CREATE LOGIN i2iBaseBulkInsertLogin_Your_DB
   WITH PASSWORD = '#BulkInsertL0ginPa$$word#' ;
REVOKE CONNECT SQL FROM i2iBaseBulkInsertLogin_Your_DB; 
GRANT ADMINISTER BULK OPERATIONS TO 
              i2iBaseBulkInsertLogin_Your_DB ;
PRINT 'Bulk Insert Login created.';

/*
A user for the i2iBaseBulkInsertLogin_Your_DB login.
Used as the security context for performing a BULK INSERT.
*/
USE Your_DB; 
CREATE USER i2iBaseBulkInsertUser_Your_DB 
FOR LOGIN i2iBaseBulkInsertLogin_Your_DB ;
PRINT 'Bulk Insert User created.';
USE Your_DB;
go
/*
Stored procedure used by Bulk Import for executing a 
BULK INSERT command.
Uses impersonation to execute as the system administrator.
*/
ALTER DATABASE Your_DB SET TRUSTWORTHY ON;
go
CREATE PROCEDURE dbo._BulkInsert 
   @stagingTable VARCHAR(500), 
   @dataFile VARCHAR(500), 
   @formatFile VARCHAR(500), 
   @kiloBytesPerBatch VARCHAR(500), 
   @maxErrors INT 
WITH EXECUTE AS 'i2iBaseBulkInsertUser_Your_DB'
AS 
BEGIN 
   EXEC( 
      ' BULK INSERT ' + @stagingTable + 
      ' FROM ''' + @dataFile + '''' + 
      ' WITH (FORMATFILE =''' + @formatFile + 
      ''', MAXERRORS=' + @maxErrors + 
      ', KILOBYTES_PER_BATCH=' + @kiloBytesPerBatch + ')' 
   ) 
END 
PRINT '_BulkInsert stored procedure (re) created.';