CommandType Text Formats

The CommandType member of the DISTCOMMANDDESC structure requires specific text formatting when specifying these items

Transact-SQL data types
Working directories
Schema files
BCP files

Transact-SQL data types

When using DISTCMDTYPE_SQL, data types in Transact-SQL statements have these formats.

Data type	Format	Comment
Datetime datetime smalldatetime	{ts 'yyyy'mm'dd hh'mm'ss[.mmm]'}	Milliseconds are optional.
Binary binary varbinary	{b 'data'}	Where data is one or more characters within the range: [0-9a-f]. It should not contain a leading 0x.
Long Binary image	{lb 'data'}	Where data is one or more characters within the range: [0-9a-f]. It should not include a leading 0x.
Character char varchar	'data'	Where data is any sequence of characters. Single quotation marks within the data portion must be expanded to two adjacent single quotation marks.
Long Character text	{lc 'data'}	Where data is any sequence of characters. Single quotation marks within the data portion must be expanded to two adjacent single quotation marks.

Working directories

When using DISTCMDTYPE_WORKINGDIR, include escape characters (\) in the file paths.

const char szDir[] = ":\\mssql7\\repldata\\unc\\samppub\\";

DISTCOMMANDDESC aCommand[60];

INT NumCommands = 0;

DistByteArray XactId;

DistByteArray XactSeqno;

CHAR szWrkDir[_MAX_PATH];

// Get the current working dir

sprintf(szWrkDir, "%c", (char)(_getdrive() + 'A' - 1) );

strcat(szWrkDir, szDir);

// Set working dir

NumCommands++;

aCommand[i].PublicationId = 1;

aCommand[i].ArticleId = 1;

aCommand[i].CommandId = NumCommands;

aCommand[i].CommandType = DISTCMDTYPE_SQL_WORKINGDIR;

aCommand[i].fPartialCommand = FALSE;

aCommand[i].pXactId = (BYTE *)&XactId;

aCommand[i].pXactSeqno = (BYTE *)&XactSeqno;

aCommand[i].szOriginator = NULL;

aCommand[i].szOriginatorDB = NULL;

aCommand[i].szCommand = (LPSTR)szWrkDir;

i++;

Schema files

When using DISTCMDTYPE_SCRIPT, include escape characters (\) in file paths.

const char szDir[] = ":\\mssql7\\repldata\\unc\\samppub\\";

const char szScriptCmd[] = "%ssamptab.sch";

DISTCOMMANDDESC aCommand[60];

char pszCmdBuf[60][255];

INT NumCommands = 0;

DistByteArray XactId;

DistByteArray XactSeqno;

// Get the current working dir

sprintf(szWrkDir, "%c", (char)(_getdrive() + 'A' - 1) );

strcat(szWrkDir, szDir);

// Execute Script - table schema

NumCommands++;

aCommand[i].PublicationId = 1;

aCommand[i].ArticleId = 1;

aCommand[i].CommandId = NumCommands;

aCommand[i].CommandType = DISTCMDTYPE_SCRIPT;

aCommand[i].fPartialCommand = FALSE;

aCommand[i].pXactId = (BYTE *)&XactId;

aCommand[i].pXactSeqno = (BYTE *)&XactSeqno;

aCommand[i].szOriginator = NULL;

aCommand[i].szOriginatorDB = NULL;

sprintf(pszCmdBuf[i], szScriptCmd, szWrkDir);

aCommand[i].szCommand = pszCmdBuf[i];

i++;

Use this format in .sch files:

SET QUOTED IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [Samptbl1] (C1 INT, C2 VARCHAR(20))

GO

BCP files

When using DISTCMDTYPE_CHAR_BCP or DISTCMDTYPE_NATIVE_BCP, you can use these switches with the sync command.

Switch	Description
-t	Destination table
-o	Destination owner
-d	Data file
-f	Field delimiter. Default field delimiter: \n<x$3>\n
-r	Row delimiter. Default row delimiter: \n<,@g>\n
-u	Unicode

Include escape characters (\) in file paths. Begin switch arguments with a quotation mark (“) and end the arguments with a backslash and a quotation mark (\”).

const char szDir[] = ":\\mssql7\\repldata\\unc\\samppub\\";

const char szBCPCmd[] = "sync -t\"SampTable1\" -d\"%ssamptab.bcp\" -u";

DISTCOMMANDDESC aCommand[60];

char pszCmdBuf[60][255];

INT NumCommands = 0;

DistByteArray XactId;

DistByteArray XactSeqno;

// Get the current working dir

sprintf(szWrkDir, "%c", (char)(_getdrive() + 'A' - 1) );

strcat(szWrkDir, szDir);

// Import Data - char BCP

NumCommands++;

aCommand[i].PublicationId = 1;

aCommand[i].ArticleId = 1;

aCommand[i].CommandId = NumCommands;

aCommand[i].CommandType = DISTCMDTYPE_CHAR_BCP;

aCommand[i].fPartialCommand = FALSE;

aCommand[i].pXactId = (BYTE *)&XactId;

aCommand[i].pXactSeqno = (BYTE *)&XactSeqno;

aCommand[i].szOriginator = NULL;

aCommand[i].szOriginatorDB = NULL;

sprintf(pszCmdBuf[i], szBCPCmd, szWrkDir);

aCommand[i].szCommand = pszCmdBuf[i];

i++;