Replication Programming
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[] = ":\\Program Files\\Microsoft SQL Server\\mssql\\repldata\\unc\\samppub\\";
DISTCOMMANDDESC aCommand[60];
INT NumCommands = 0;
DistByteArray XactId;
DistByteArray XactSeqno;
CHAR szWrkDir[_MAX_PATH];
// Get the current working directory.
sprintf(szWrkDir, "%c", (char)(_getdrive() + 'A' - 1) );
strcat(szWrkDir, szDir);
// Set working directory.
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[] = ":\\Program Files\\Microsoft SQL Server\\mssql\\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 directory.
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. This switch applies only when using DISTCMDTYPE_NATIVE_BCP type bcp files. |
| -m | Denotes that the file is a character bcp file with a Unicode marker at the beginning of the file. |
Include escape characters (\) in file paths. Begin switch arguments with quotation marks (") and end the arguments with a backslash and quotation marks (\").
const char szDir[] = ":\\Program Files\\Microsoft SQL Server\\mssql\\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 directory.
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++;