Cache Database

Details of BCP Export

Step 1: procBpcInitExport

CREATE PROCEDURE [dbo].[procBcpInitExport]
	@TargetPath varchar(200), 
	@Schema varchar(200),
	@ProtocolFileName varchar(200)
WITH EXECUTE AS 'cmdshell'
AS 
DECLARE @RC int;
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL nvarchar(500);
	DECLARE @BcpCmd varchar(500);
	DECLARE @ToFile varchar(200);
	DECLARE @CheckDatabase varchar(200);
	DECLARE @DatabaseName varchar(200);
	
	-- Retrieve database name
	SET @DatabaseName = (SELECT DB_NAME());
	SET @SQL = N'SELECT @CheckDatabase = [CATALOG_NAME] FROM [INFORMATION_SCHEMA].[SCHEMATA] WHERE [SCHEMA_NAME]=@Schema;';
	EXECUTE sp_executesql @SQL, N'@Schema varchar(200), @CheckDatabase varchar(200) OUTPUT', @Schema=@Schema, @CheckDatabase=@CheckDatabase OUTPUT;
	IF @DatabaseName <> @CheckDatabase
	BEGIN
		SET @RC = 2;
		RETURN @RC;
	END
	SET @RC = 1;

	-- Normalize target path
	SET @TargetPath = RTRIM(@TargetPath);
	IF ('\' <> RIGHT(@TargetPath, 1)) 
		SET @TargetPath = @TargetPath + '\';
	SET @DatabaseName = (SELECT [CATALOG_NAME] FROM [INFORMATION_SCHEMA].[SCHEMATA] WHERE [SCHEMA_NAME]=@Schema);
	SET @ToFile = ' >> ' + @TargetPath + @DatabaseName + '\' + @ProtocolFileName;

	-- Create subdirectories
	SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName;
	EXEC xp_cmdshell @BcpCmd

	SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName + '\' + @Schema;
	EXEC xp_cmdshell @BcpCmd

	SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName + '\dbo';
	EXEC xp_cmdshell @BcpCmd

	-- Create protocol file
	SET @BcpCmd = 'echo User: ' + (SELECT CONVERT(varchar, ORIGINAL_LOGIN())) + ', Start: ' +(SELECT CONVERT(varchar, SYSDATETIME())) + ' > '+ @TargetPath + @DatabaseName + '\' + @ProtocolFileName;
	EXEC xp_cmdshell @BcpCmd

	SET @BcpCmd = 'echo Proxy account: >> '+ @TargetPath + @DatabaseName + '\' + @ProtocolFileName;
	EXEC xp_cmdshell @BcpCmd

	SET @BcpCmd = 'whoami >> '+ @TargetPath + @DatabaseName + '\' + @ProtocolFileName;
	EXEC xp_cmdshell @BcpCmd

	SET @RC = 0;
END
RETURN @RC

Parameters of the procedure

@TargetPath: The directory where the output will be created, e.g. U:\postgres.
@Schema: The schema of the current project within the database, e.g. Project_BFLtestcoll.
@ProtocolFileName: The file containing the output, e.g. Outfile.txt.

Parameters set within the procedure

@DatabaseName: The name of the current database - corresponds to the subdirectory where the output will be created

Check database

The first step checks if the database corresponds to current schema and database to avoid sqlinjection:

SELECT [CATALOG_NAME] 
FROM [INFORMATION_SCHEMA].[SCHEMATA] 
WHERE [SCHEMA_NAME]='Project_BFLtestcoll';

If the check fails the procedure will return the error code 2

Fix path

The next step fixes a possible miswritten path of the target

SET @TargetPath = RTRIM(@TargetPath);
	IF ('\' <> RIGHT(@TargetPath, 1)) 
		SET @TargetPath = @TargetPath + '\';

Set target

This step sets the syntax to the target file

SET @ToFile = ' >> ' + @TargetPath + @DatabaseName + '\' + @ProtocolFileName;

Creating directories

Creating one main and 2 subdirectories

SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName;
EXEC xp_cmdshell @BcpCmd

SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName + '\' + @Schema;
EXEC xp_cmdshell @BcpCmd

SET @BcpCmd = 'md ' + @TargetPath + @DatabaseName + '\dbo';
EXEC xp_cmdshell @BcpCmd

Creating the protocol file

SET @BcpCmd = 'echo User: ' + (SELECT CONVERT(varchar, ORIGINAL_LOGIN())) + ', Start: ' +(SELECT CONVERT(varchar, SYSDATETIME())) + ' > '+ @TargetPath + @DatabaseName + '\' + @ProtocolFileName;
EXEC xp_cmdshell @BcpCmd

Writing the name of the service into the protocol file

SET @BcpCmd = 'whoami >> '+ @TargetPath + @DatabaseName + '\' + @ProtocolFileName;
EXEC xp_cmdshell @BcpCmd

EXEC xp_cmdshell 'whoami'

→ nt service\mssqlserver

Step 2: procBcpViewCreate

Creating a view that maps the content of the table that should be exported within the schema. The view adapts the output according to values needed for postgres, e.g. string values will the enclosed with ".