#EDIR DATA#
DOCTYPE='BOOK' PUBLICID='-//SYBOOKS//Default DTD//EN' SYSTEMID=''DOCTYPE='book' PUBLICID='' SYSTEMID=''SQL Server Utility Programs for UNIXSYBASE SQL Servername='trade' font='symbol' code='228'  charset='fontspecific'Utility Programs for UNIXREMAP="titleBO--Body"SYBASE SQL Server Release 10.0REMAP="titleBO--Body"Document ID: 30475-00-1000-04REMAP="titleBO--Body"Change Level: 2REMAP="titleBO--Body"Last Revised: June 17, 1994Principal authorship: Server Publications GroupDocument ID: 30475-00-1000REMAP="discBO--Body"This publication pertains to SYBASE SQL
Server Release 10.0 of the SYBASE database management software and to
any subsequent release until otherwise indicated in new editions or
technical notes. Information in this document is subject to change
without notice. The software described herein is furnished under a
license agreement, and it may be used or copied only in accordance
with the terms of the agreement.Document OrdersREMAP="discBO--Body"Customers may purchase additional copies of any document or the right to make photocopies of documentation for their in-house use.REMAP="discBO--Body"To order additional documents or photocopy rights, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.REMAP="discBO--Body"Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the fax number. All other international customers should contact their Sybase subsidiary or local distributor.REMAP="discBO--Body"Upgrades are provided only at regularly scheduled software release dates.REMAP="discBO--Body"© Copyright Sybase, Inc., 1989, 1994. All rights reserved.REMAP="discBO--Body"No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical or otherwise, without prior written permission of Sybase, Inc.Sybase TrademarksREMAP="discBO--Body"SYBASE, the SYBASE logo, SyBooks, APT-FORMS, Data Workbench, DBA Companion, Deft, GainMomentum, SA Companion, SQL Debug, SQL Solutions, SQR, Transact-SQL, and VQL are registered trademarks of Sybase, Inc. ADA Workbench, Adaptable Windowing Environment, Application Manager, Applications from Models, APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-Translator, APT-Library, Build Momentum, Camelot, Client/Server Architecture for the Online Enterprise, Client/Server for the Real World, Configurator, DataServer, Data Workbench, Database Analyzer, DB-Library, Deft Analyst, Deft Designer, Deft Educational, Deft Professional, Deft Trial, Developers Workbench, Easy SQR, Embedded SQL, Enterprise Builder, Enterprise Client/Server, Enterprise Meta Server, Enterprise Modeler, Enterprise Momentum, Gain, GainExposure, Insight, Mainframe Access Products (MAP), Momentum, Movedb, Navigation Server, Net-Gateway, Net Library, Object Momentum, OmniSQL Gateway, Omni SQL Server, OmniSQL Access Module, Open Client, Open Gateway, Open Server, Open Solutions, PC APT-Execute, PC DB-Net, PC Net Library, Report Workbench, Report Execute, Open Server, Partnerships that Work, Replication Server, Resource Manager, RW-Display Lib, RW-Library, SA Monitor, Secure SQL Server, Secure SQL Toolset, SQL Code Checker, SQL Edit/TPU, SQL Edit, SQL Monitor, SQL Server, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Station, SQR Toolset, SQR Developers Kit, SQR Workbench, SYBASE Client/Server Interfaces, SYBASE Gateways, SYBASE SQL Lifecycle, Sybase Synergy Program, SYBASE Virtual Server Architecture, SYBASE User Workbench System 10, Tabular Data Stream, and The Enterprise Client/Server Company are trademarks of Sybase, Inc.REMAP="discBO--Body"All other company and product names used herein may be the trademarks or registered trademarks of their respective companies.Restricted Rights LegendREMAP="discBO--Body"Use, duplication or disclosure by the Government is subject to restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.REMAP="discBO--Body"Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608PrefaceREMAP="BO--Body"SQL Server Utility Programs for UNIX is a guide to the SQL Server utility programs available on all UNIX platforms. Utility programs are commands that you invoke directly from the operating system.RENDERAS="SECT1"AudienceREMAP="BO--Body"This manual is for anyone using Transact-SQL® and System 10 SQL Servername='trade' font='symbol' code='228'  charset='fontspecific'. It is a reference manual, and assumes that you have basic knowledge of how to use the UNIX operating system and SQL Server.RENDERAS="SECT1"How to Use This BookREMAP="BO--Body"This manual includes the following chapters:MARK="BULL" REMAP="BU--Bullet"LINKEND="X54168"Chapter 1,  ``SQL Server Utility Programs for UNIX,'' consists of alphabetized reference pages for all of the available utility commands.LINKEND="X72116"Chapter 2,  ``Using bcp to Transfer Data to and from SQL Server,'' discusses MOREINFO="NONE"bcp in detail.LINKEND="X33269"Chapter 3,  ``Using isql: Additional Information,'' discusses using the interactive SQL utility.REMAP="BO--Body"The examples in this book are based on the pubs2 sample database. Ask your System Administrator how to get a clean copy of MOREINFO="NONE"pubs2. RENDERAS="SECT1"Related DocumentsREMAP="BO--Body"Other manuals that you may find useful are:MARK="BULL" REMAP="BU--Bullet"System Administration Guide for SQL Server, which contains in-depth information about SQL Server administration issues.System Administration Guide Supplement for your platform, which documents operating-system specific system administration tasks.Open Client DB-Library/C Reference Manual, a collection of manual pages and code samples for the SQL Server interface library, Open Client DB-Library.The SQL Server Installation Guide, which describes the installation procedures for SQL Server.SQL Server Reference Manua
l Vol. 1 and Vol. 2, which contains detailed information on all of the commands and system procedures discussed in this manual.RENDERAS="SECT1"Conventions Used in This ManualREMAP="BO--Body"The following paragraphs detail the typographic conventions used in this manual.ID="IN331"Conventions, syntax
ID="IN322"Syntax conventionsID="X97350" RENDERAS="SECT2"Font and Syntax ConventionsREMAP="BO--Body"The font and syntax conventions in this reference are as follows:COLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="193*"COLNAME="C2" COLWIDTH="471*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"ElementCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"ExampleVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Command names, command option 
names, utility names, utility flags, and 
other keywords are MOREINFO="NONE"bold.COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Database names, datatypes, filenames 
and pathnames are in italics.COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"master database COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Variables, or words that stand for values 
that you fill in, are in italics.COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select column_name 
MOREINFO="NONE"from table_name
 
MOREINFO="NONE"where search_conditions
 COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Parentheses are to be typed as part of the 
command. COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"compute row_aggregate 
 (column_name
) COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Curly braces indicate that you must 
choose at least one of the enclosed 
options. Do not type the  braces. COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"{cash, check, credit} COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Brackets mean choosing one or more of 
the enclosed options is optional. Do not 
type the  brackets. COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"[anchovies] COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"The vertical bar means you may select 
only one of the options shown. COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"{die_on_your_feet | live_on_your_knees | 
live_on_your_feet} COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"The comma means you may choose as 
many of the options shown as you like, 
separating your choices with commas to 
be typed as part of the command.COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"[extra_cheese, avocados, sour_cream] COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"An ellipsis (...) means that you can repeat 
the last unit as many times as you 
like.COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"buy thing = price [cash | check | credit]  MOREINFO="NONE"[, thing = price [cash | check | credit]]...
You must buy at least one thing and give its 
price. You may choose a method of payment: 
one of the items enclosed in square brackets. 
You may also choose to buy additional things: 
as many of them as you like. For each thing 
you buy, give its name, its price, and 
(optionally) a method of payment.MARK="BULL" REMAP="BU--Bullet"Syntax statements (displaying the syntax and all options for a command) appear as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"showserver MOREINFO="NONE"parameterREMAP="UN--Under"or, for a command with more options:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"buildmaster [-d MOREINFO="NONE"physicalname] [-c MOREINFO="NONE"cntrltype] [-sMOREINFO="NONE" size] [-r] [-m] REMAP="UN--Under"In syntax statements, commands are in normal font and options are in lowercase: normal font for flags, italics for user-supplied values.Examples showing utility commands appear in bold, as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp -vExamples of output from the computer appear as follows:ID="IN313"Conventions, syntax
ID="IN304"Syntax conventionsREMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"pub_id  pub_name                city            state 
------- -------------------     -----------     -----
0736    New Age Books           Boston          MA
0877    Binnet & Hardley        Washington      DC
1389    Algodata Infosystems    Berkeley        CA (3 rows affected)RENDERAS="SECT1"If You Need HelpREMAP="BO--Body"Help with your SYBASE software is available in the form of documentation and the Technical Support Center.ID="IN235"Help, Technical SupportREMAP="BO--Body"Each SYBASE installation has a designated person who may contact Technical Support. If you cannot resolve your problem using the manuals, ask the designated person at your site to contact Sybase Technical Support.NUMBER="1" ID="X54168"SQL Server Utility Programs for UNIXRENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter consists of reference pages for the utility programs listed below. REMAP="BO--Body"The UNIX system shell interprets the utility program commands. Place characters with special meaning to the shell, such as the backslash (\), asterisk (*), slash (/), and spaces, in ID="IN1091"Quotation marks (" ") for enclosing special characters;" " (quotation marks) for enclosing special characters
ID="IN892"Backslash (\)escaping special characters;\ (backslash):escaping special characters
quotes. You can precede some special characters with the backslash (\) to "escape" them. This prevents the shell from interpreting the special characters.TOCENTRY="1"LBL="Table 1-1: "SQL Server Utility Programs for UNIXCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="9*"COLNAME="C2" COLWIDTH="31*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"UtilityCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Description VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"backupserver COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Executable form of the Backup Server programCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"bcp COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Copies a database table to or from an operating system file 
in a user-specified formatCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"buildmaster COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Builds the master device and creates the master, model, and 
tempdb databases on the deviceCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"dataserver COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Executable form of the SQL Server programCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"defncopy COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Copies definitions for specified views, rules, defaults, 
triggers, procedures, or reports from a database to an 
operating system file or from an operating system file to a 
databaseCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"isql COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Interactive SQL parser to SQL Server COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"langinstall COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Installs one new language on the SQL ServerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"showserver COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Shows SQL Servers and Backup Servers that are currently 
running on the local machineCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"startserver COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Starts a SQL Server and/or a Backup ServerID="X97777"backupserverID="IN881"backupserver utility command
ID="IN272"Backup Server
The executable form of the Backup Server program. FORMAT="LINESPECIFIC"backupserver
[-Cserver_connections] [-Sb_servername] [-Iinterface_filename] [-eerror_log_file] [-Msybmultbuf_binary] [-Nnetwork_connections][-Ttrace_value] [-LSybase_language_name] [-JSybase_character_set_name]orbackupserver [-v]ParametersREMAP="RI--RefIndent"MOREINFO="NONE"-CMOREINFO="NONE"server_connections  - ID="IN1103"Backup Serverserver connections, number required;Number (quantity of):server connections to the Backup Server
ID="IN684"Server connections (backupserver)
specifies the number of server connections for the Backup Server. The Backup Server requires:MARK="DASH" REMAP="BS--BulletSub"Two connections for each dump sessionOne connection for each load sessionOne connection for volume change messagesREMAP="UN--Under"Allow a maximum of three times the number of expected concurrent dump and load sessions. The default value is 20 server connections.MOREINFO="NONE"-S MOREINFO="NONE"b_servername  - specifies the name of the Backup Server to start. The default is SYB_BACKUP. This entry must specify the name of a Backup Server in the interfaces file.MOREINFO="NONE"-I MOREINFO="NONE"interfaces_fileID="IN1025"Filesbackupserver and interfaces  - specifies the name and location of the interfaces file to search when connecting to Backup Server. If MOREINFO="NONE"-I is omitted, MOREINFO="NONE"backupserver looks for a file named interfaces in the directory pointed to by your SYBASE environment variable. MOREINFO="NONE"-e MOREINFO="NONE"error_log_fileID="IN526"Backup Servererror log file;Error log:Backup Server  - specifies the name and location of the Backup Server error log file used to report Open Server internal errors, sybmultbuf errors, errors that halt the Backup Server, and errors for disconnected sessions. All other errors are sent to the MOREINFO="NONE"notify destination specified in theMOREINFO="NONE" dump database, MOREINFO="NONE"dump transaction, MOREINFO="NONE"load database, and MOREINFO="NONE"load transaction commands.MOREINFO="NONE"-M MOREINFO="NONE"sybmultbuf_binaryID="IN557"sybmultbuf executable  - specifies the full path name of the sybmultbuf executable. Use this option only when starting Backup Server from a directory other than the /bin directory of the SYBASE installation tree, or when using a diagnostic version of sybmultbuf.MOREINFO="NONE"-N MOREINFO="NONE"network_connections ID="IN979"Backup Servernetwork connections;Number (quantity of):network connections from the Backup Server
ID="IN6910"Network connections (backupserver)
specifies the number of total network connections (DBPROCESSes) that the master Backup Server can originate. The default value is 25.MOREINFO="NONE"-T MOREINFO="NONE"trace_value - interprets trace_value as a bit mask (base-2 number). The 1 bits in trace_valueID="IN7511"Trace values;Flags, trace;Open Server trace flags;Backup Servertrace flags
 correspond to Open Server trace flags to turn on. If you specify more than one MOREINFO="NONE"-T option on the command line, the final MOREINFO="NONE"-T value overrides the values from earlier MOREINFO="NONE"-T options. The trace_value must be a positive integer. MOREINFO="NONE"-L MOREINFO="NONE"Sybase_language_name  - specifies the default language for Backup Server. If not specified, Backup Server uses the locale specified by the LC_ALL or LANG environment variables. If these variables are not set, Backup Server searches for the "default" entry in locales.dat.MOREINFO="NONE"-J MOREINFO="NONE"Sybase_character_set_name  - specifies the default character set for Backup Server. If not specified, Backup Server uses the locale specified by the LC_ALL or LANG environment variables. If these variables are not set, Backup Server searches for the "default" entry in locales.dat.MOREINFO="NONE"-v - prints the version number and copyright message of the MOREINFO="NONE"backupserver software, then exits.CommentsID="IN11415"backupserver utility commandstartserver andMARK="BULL" REMAP="BU--Bullet"Start
Backup Server with the MOREINFO="NONE"startserver command rather
than by directly executing the MOREINFO="NONE"backupserver program. If you need to change any of the default values, edit the RUN_servername file in your SYBASE installation directory. See the LINKEND="X28711"startserver manual page for details. ID="IN12716"Environment variableDSLISTEN;DSLISTEN environment variableThe default name of a Backup Server is SYB_BACKUP. The value of the environment variable DSLISTEN overrides the default value, and the MOREINFO="NONE"-S option overrides the default and the value in DSLISTEN.ID="IN10917"Backup Serverinterfaces file;backupserver utility command:interfaces file
ID="IN2918"Interfaces fileBackup ServerWhenever possible, the Backup Server and any SQL Servers that dump or load directly through the Backup Server should share the same interfaces file. The interfaces file that Backup Server uses must contain entries for:MARK="DASH" REMAP="BS--BulletSub"The Backup Server.Any other Backup Servers with which this Backup Server communicates.Trace flags cause the Backup Server to print information regarding its operation while it is running, for debugging problems in the Backup Server. See the Open Server Server-Library/C Reference Manual for more details on trace flags. The UNIX Backup Server does not support use of the Open Server-defined SRV__TR symbols for MOREINFO="NONE"-T.If Backup Server cannot find the locales and charsets directories specified by the MOREINFO="NONE"-L and MOREINFO="NONE"-J options, or if these options specify an incorrect language and character set combination, Backup Server issues an error message and uses the default language and character set.See Also COLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="129*"COLNAME="C2" COLWIDTH="357*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"UtilitiesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"LINKEND="X28711"startserverID="IN8619"backupserver utility command
ID="IN2520"Backup ServerREMAP="BO--Body"ID="X12769"bcpID="IN1002"Bulk copying. See bcp (bulk copy utility)
ID="IN833"Database objectscopying using bcp
ID="IN944"Operating system filesbcp copying to or from
ID="INA725"Copyingtables with bcp
Copies a database table to or from an operating system file in a user-specified format. FORMAT="LINESPECIFIC"bcp [[MOREINFO="NONE"database_name.]owner.]MOREINFO="NONE"table_name {in | out}MOREINFO="NONE"    datafile [-m MOREINFO="NONE"maxerrors] [-f MOREINFO="NONE"formatfile] [-e MOREINFO="NONE"errfile] [-F MOREINFO="NONE"firstrow] [-L MOREINFO="NONE"lastrow] [-b MOREINFO="NONE"batchsize][-T text_or_image_size] [-n] [-c] [-t MOREINFO="NONE"field_terminator] [-r MOREINFO="NONE"row_terminator] [-U MOREINFO="NONE"username] [-P MOREINFO="NONE"password] [-I MOREINFO="NONE"interfaces_file][-S MOREINFO="NONE"server] [-a MOREINFO="NONE"display_charset][-q MOREINFO="NONE"datafile_charset] [-J MOREINFO="NONE"client_charset][-z MOREINFO="NONE"language] [-v] [-A size] [-E] [-X]ParametersREMAP="RI--RefIndent"MOREINFO="NONE"database_name - is optional if the table being copied is in your default database or in master. Otherwise, you must specify a database name. MOREINFO="NONE"owner  - is optional if you or the Database Owner own the table being copied. If you do not specify an owner, MOREINFO="NONE"bcp looks first for a table of that name owned by you. Then it looks for one owned by the Database Owner. If another user owns the table, you must specify the owner's name or the command fails. MOREINFO="NONE"table_name  - is the name of the database table to copy. The table name cannot be a Transact-SQL reserved word.MOREINFO="NONE"in  |MOREINFO="NONE" out  - ID="IN896"in | out optionbcp
is the direction of the copy. MOREINFO="NONE"in indicates a copy from a file into the database table, while MOREINFO="NONE"out indicates a copy to a file from the database table. MOREINFO="NONE"datafile  - is the full path name of an operating system file. The path name can be from 1 to 255 characters in length. MOREINFO="NONE"-m  MOREINFO="NONE"max_errors  - ID="IN617"Errorsmaximum bcp copying
ID="IN738"bcp (bulk copy utility)errors allowed
is the maximum number of errors permitted before MOREINFO="NONE"bcp aborts the copy. MOREINFO="NONE"bcp throws out each row that it cannot build, counting it as one error. If you do not include this option, MOREINFO="NONE"bcp uses a default value of 10. MOREINFO="NONE"-f  MOREINFO="NONE"format_file - ID="IN719"bcp (bulk copy utility)format files
is the full path name of a file with stored responses from a previous use of MOREINFO="NONE"bcp on the same table. After you answer MOREINFO="NONE"bcp's format questions, it asks you if you want to save your answers in a format file; creation of the format file is optional. The default file name is ID="IN4710"bcp.fmt files
bcp.fmt. The MOREINFO="NONE"bcp program can refer to a format file when copying data, so that you do not have to duplicate your previous format responses interactively. Use this option only when you previously created a format file that you want to use now for a copy in or out. If this option is not used, MOREINFO="NONE"bcp queries you for format information interactively. MOREINFO="NONE"-e  MOREINFO="NONE"errfile  - is the full path name of an error file where MOREINFO="NONE"bcp stores any rows that it was unable to transfer from the file to the database. Error messages from the MOREINFO="NONE"bcp program appear on your terminal. MOREINFO="NONE"bcp creates an error file only when you specify this option. If you specify this option and MOREINFO="NONE"bcp does not encounter any error, it does not create the error file.MOREINFO="NONE"-F  MOREINFO="NONE"firstrow  - is the number of the first row to copy (default is the first row). MOREINFO="NONE"-L  MOREINFO="NONE"lastrow  - is the number of the last row to copy (default is the last row). MOREINFO="NONE"-b  MOREINFO="NONE"batchsize  - is the number of rows per batch of data copied (the default is to copy all the rows in one batch). Batching applies only when bulk copying in; it has no effect on bulk copying out. MOREINFO="NONE"-T  MOREINFO="NONE"text_or_image_size  - allows you to specify, in bytes, the maximum length of ID="IN10011"text datatype, copying with bcp
text or ID="IN10112"image datatype, copying with bcp
image data that SQL Server sends. The default is 32K. If a text or image field is larger than the value of MOREINFO="NONE"-T or the default, MOREINFO="NONE"bcp does not send the overflow.MOREINFO="NONE"-n - performs the copy operation using native (operating system) formats. This option does not prompt for each field. Files in native data format are not human-readable. ID="IN3613"Operating systemsnative format dataID="IN6214"Filesnative data format;Data:native (operating system) formatID="IN6815"Native data format files, bcp andMOREINFO="NONE"-c  - performs the copy operation with ID="IN9116"char datatype, and bcp
char datatype as the default. This option does not prompt for each field; it uses char as the default storage type, no prefixes, \t (tab) as the default field terminator, and \n (newline) as the default row terminator.MOREINFO="NONE"-t  MOREINFO="NONE"field_terminator  - specifies the default field terminator. MOREINFO="NONE"-r  MOREINFO="NONE"row_terminator  - specifies the default row terminator. ID="IN6317"Terminators (bcp)specifyingWhen specifying terminators from the command line with the MOREINFO="NONE"-t or MOREINFO="NONE"-r 
options, you must escape characters that have special significance to the UNIX operating system LINKEND="X39428"(see Example 1). Either place a backslash in front of the special character or enclose it in quotes. This is not necessary when MOREINFO="NONE"bcp prompts you (interactive mode).ID="IN4418"\ (backslash)escaping special charactersID="IN4419"Backslash (\)escaping special charactersID="IN7520"bcp (bulk copy utility)interactive modeID="IN7321"Interactive bcpspecial characters andREMAP="RI--RefIndent"MOREINFO="NONE"-U MOREINFO="NONE"username  - specifies a SQL Server login name. If you do not specify username, MOREINFO="NONE"bcp uses the current user's operating system login name. MOREINFO="NONE"-P MOREINFO="NONE"password  - specifies a SQL Server password. If you do not specify MOREINFO="NONE"-P MOREINFO="NONE"password, MOREINFO="NONE"bcpID="IN5722"Passwordsnull, in bcp
 prompts for a password. If your password is NULL, place the MOREINFO="NONE"-P flag at the end of the command line by itself. MOREINFO="NONE"-S MOREINFO="NONE"server  - specifies the name of the SQL Server to connect to. If you specify MOREINFO="NONE"-S with no argument, MOREINFO="NONE"bcpID="IN8723"Server user name and ID, DSQUERY environment variable
ID="IN6224"Environment variableDSQUERY
ID="IN8825"DSQUERY environment variable server name specification
 uses the server that your DSQUERY environment variable specifies. MOREINFO="NONE"-I MOREINFO="NONE"interfaces_file  - specifies the name and location of the interfaces file to search when connecting to SQL Server. If you do not specify MOREINFO="NONE"-I, MOREINFO="NONE"bcp looks for a file named interfaces in the directory that your SYBASE environment variable specifies. MOREINFO="NONE"-J MOREINFO="NONE"client_charset - 
specifies the character set to use on the client. MOREINFO="NONE"bcpID="IN6728"Filters, character set inputbcp
 uses a filter to convert input between MOREINFO="NONE"client_charset and the SQL Server character set. ID="IN7126"Character setsbcp andID="IN8727"bcp (bulk copy utility)character setsREMAP="UN--Under"MOREINFO="NONE"-J MOREINFO="NONE"client_charset requests that SQL Server convert to and from MOREINFO="NONE"client_charset, the character set used on the client. REMAP="UN--Under"MOREINFO="NONE"-J with no argument sets character set conversion to NULL. No conversion takes place. Use this if the client and server use the same character set.REMAP="UN--Under"OmittingMOREINFO="NONE" -J sets the character set to a default for the platform. LINKEND="X26005"Table 1-2
ID="IN9629"Character setsplatform default;Defaults:platform character sets;Platform character set defaults
 lists platform defaults. TOCENTRY="1"LBL="Table 1-2: " ID="X26005"Default Character Sets for Different PlatformsCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="11*"COLNAME="C2" COLWIDTH="6*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"PlatformCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Default Character SetVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Sun, DEC, Pyramid, NCR, Alpha, othersCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"iso_1COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"HPCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"roman8COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"RS6000/aix, OS/2COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"cp850COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MacintoshCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"macREMAP="UN--Under"The default may not necessarily be the character set that the client is using. (See the System Administration Guide for SYBASE SQL Server and the System Administration Guide Supplement for more information about character sets and the associated flags.)REMAP="RI--RefIndent"MOREINFO="NONE"-a MOREINFO="NONE"display_charset - allows you to run MOREINFO="NONE"bcp from a terminal where the character set differs from that of the machine on which MOREINFO="NONE"bcp is running. (See the System Administration Guide for more information about changing character sets.) MOREINFO="NONE"-a in conjunction with MOREINFO="NONE"-J specifies the character set translation file (.xlt file) required for the conversion. Use MOREINFO="NONE"-a without MOREINFO="NONE"-J only if the client character set is the same as the default character set.MOREINFO="NONE"-q MOREINFO="NONE"datafile_charset  - allows you to run MOREINFO="NONE"bcp to copy character data to or from a file system that uses a character set different from the client character set.MOREINFO="NONE" -q in conjunction with MOREINFO="NONE"-J specifies the character set translation file (.xlt file) required for the conversion.REMAP="UN--Under"InID="IN8930"Japanese character setsin bcp;Character sets:Japanese
 Japanese language environments, the MOREINFO="NONE"-q flag translates Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument "zenkaku" and with the MOREINFO="NONE"-J flag to indicate the client's Japanese character set (MOREINFO="NONE"sjis or MOREINFO="NONE"eucjis). The zenkaku.xlt file was designed to translate only from terminal display to SQL Server, not from SQL Server to the terminal. ID="IN10531"ascii_7 character setbcp and;Character sets:compatibility and ascii_7The ascii_7 character set is compatible with all character sets. If either the SQL Server's or client's character set is set to ascii_7, any 7-bit ASCII character is allowed to pass between client and server unaltered. Other characters produce conversion errors. Character set conversion issues are covered more thoroughly in the 
System Administration Guide.ID="IN8532"bcp (bulk copy utility)character setsID="IN6933"Character setsbcp andREMAP="RI--RefIndent"MOREINFO="NONE"-z MOREINFO="NONE"language - ID="IN6434"Languages, alternatebcp with
is the official name of an alternate language that the server uses to display MOREINFO="NONE"bcp prompts and messages. Without the MOREINFO="NONE"-z flag, MOREINFO="NONE"bcp uses the server's default language. Add languages to a SQL Server at installation, or afterwards with the utility MOREINFO="NONE"langinstall or the stored procedure MOREINFO="NONE"sp_addlanguage. MOREINFO="NONE"-v  - ID="IN5335"Version numberbcp
ID="IN5436"Numbersbcp versiondisplays the version number of MOREINFO="NONE"bcpID="IN5637"Copyright messagebcp
 and a copyright message and returns to the operating system.MOREINFO="NONE"-A MOREINFO="NONE"size - ID="IN9238"bcp (bulk copy utility)specifying network packet size in
ID="IN7539"Packet size, networkspecifying with bcp
ID="IN7240"Network packet sizespecifying in bcpspecifies the network packet size to use for this MOREINFO="NONE"bcp session. For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp -A 2048REMAP="UN--Under"sets the packet size to 2,048 bytes for this MOREINFO="NONE"bcp session. size must be between the values of the MOREINFO="NONE"default network packet size and MOREINFO="NONE"maximum network packet size configuration variablesMOREINFO="NONE", and it must be a multiple of 512. ID="IN7741"bcp (bulk copy utility)performance issuesREMAP="UN--Under"Use larger-than-default network packet sizes to improve the performance of large bulk copy operations.MOREINFO="NONE"-E  - ID="IN11342"bcp (bulk copy utility)IDENTITY columns and
ID="IN10443"IDENTITY columns, specifying in bcpexplicitly specifies the value of a table's IDENTITY column. REMAP="UN--Under"By default, when you bulk copy data into a table with an IDENTITY column, MOREINFO="NONE"bcp assigns each row a temporary IDENTITY column value of 0. As MOREINFO="NONE"bcp inserts each row into the table, the server assigns the row a unique, sequential IDENTITY column value, beginning with the value 1. If you specify the MOREINFO="NONE"-E flag when copying data into a table, MOREINFO="NONE"bcp prompts you to enter an explicit IDENTITY column value for each row. If the number of inserted rows exceeds the maximum possible IDENTITY column value, SQL Server returns an error.REMAP="UN--Under"By default, when you bulk copy data from a table with an IDENTITY column,MOREINFO="NONE" bcp excludes all information about the column from the output file. If you specify the MOREINFO="NONE"-E flag,MOREINFO="NONE" bcp copies the existing IDENTITY column values into the output file. MOREINFO="NONE"-X  - 
when connecting to the server, MOREINFO="NONE"bcp initiates the login with client-side password encryption. MOREINFO="NONE"bcp (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which MOREINFO="NONE"bcp uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.REMAP="UN--Under"If MOREINFO="NONE"bcp crashes, the system creates a core file which contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.ID="IN5944"Passwordsbcp encryptionID="X39428"ExamplesNUMERATION="ARABIC" REMAP="S1--StepOne" CONTINUATION="RESTARTS" INHERITNUM="IGNORE"In the following example, the MOREINFO="NONE"-c option copies data out of the publishers table in character format (using char for all fields). TheMOREINFO="NONE" -t field_terminator option ends each field with a comma, and theMOREINFO="NONE" -r row_terminator option ends each line with a Return. MOREINFO="NONE"bcp prompts only for a password. The first backslash before the final "r" escapes the second so that one backslash prints. REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_out -c -t , -r \\rIn the following example,MOREINFO="NONE" bcp copies data from the publishers table to a file named pub_out for later reloading into SQL Server. Pressing Return accepts the defaults that the prompts specify. The same prompts appear when copying data into the publishers table. REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_out Password: Enter the file storage type of field pub_id [char]: Enter prefix length of field pub_id [0]: Enter length of field pub_id [4]: Enter field terminator [none]: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Enter the file storage type of field pub_name [char]: Enter prefix length of field pub_name [1]: Enter length of field pub_name [40]: Enter field terminator [none]: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Enter the file storage type of field city [char]: Enter prefix length of field city [1]: Enter length of field city [20]: Enter field terminator [none]: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Enter the file storage type of field state [char]: Enter prefix length of field state [1]: Enter length of field state [2]: Enter field terminator [none]: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Do you want to save this format information in a file? [Y-n] yHost filename [bcp.fmt]: pub_form REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Starting copy... REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"3 rows copied. Clock Time (ms.): total = 300   Avg = 100  (10.00 rows per sec.) To copy this data back into SQL Server using the saved format file, pub_form, use the following command: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in pub_out -f pub_form To see examples of datatypes, enter "?" at the prompt: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Enter the file storage type of field 'pub_id' ['char']:?Invalid column type. Valid types are:<cr>: same type as SQL Server column.    c : char     T : text     i : int     s : smallint     t : tinyint     f : float     m : money     b : bit     d : datetime     x : binary     I : image     D : smalldatetime     r : real     M : smallmoney     n : numeric     e : decimal REMAP="UN--Under"Enter the single letter exactly as it appears above. The following example copies a data file created with a character set used on a VT200 terminal into the pubs2..publishers table. The MOREINFO="NONE"-q flag translates it. The MOREINFO="NONE"-z flag displays MOREINFO="NONE"bcp messages in French.REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in vt200_data -J iso_1 -q vt200 -z frenchThe following example specifies that you are using a Macintosh, running MOREINFO="NONE"bcp on a workstation that is using roman8, with the file system on another machine that uses iso_1:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in -a mac -J roman8 -q iso_1The following example specifies that SQL Server send 40K of text or image data using a packet size of 4096:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out -T 40960 -A 4096ID="X62082"CommentsSee LINKEND="X72116"Chapter 2,  "Using bcp to Transfer Data to and from SQL Server" for a more in-depth discussion of MOREINFO="NONE"bcp.MOREINFO="NONE"bcp provides a convenient and high-speed method for transferring data between a database table and an operating system file. It is capable of reading or writing files in a wide variety of formats. When copying in from a file, MOREINFO="NONE"bcp appends data to an existing database table; when copying out to a file, MOREINFO="NONE"bcp overwrites any previous contents of the file. Upon completion, MOREINFO="NONE"bcp informs you of the number of rows of data successfully copied, the number of rows (if any) that it could not copy, the total time the copy took, the average amount of time that it took to copy one row (in milliseconds), and the number of rows copied per second. ID="IN7045"Indexesbcp copying and tables with
ID="IN5946"Triggersbcp copying and
MOREINFO="NONE"Copying Tables with Indexes or TriggersID="IN4247"Copyingtables with no indexes or triggersMARK="BULL" REMAP="BU--Bullet"The MOREINFO="NONE"bcp program is optimized to load data into tables that do not have indexes or triggers associated with them. It loads data into tables without indexes or triggers at the fastest possible speed, with a minimum of logging. Page allocations are logged, but the insertion of rows is not.ID="IN5448"Slow version of bcpREMAP="UN--Under"When you copy data into a table that has one or more indexes or triggers, a slower version of MOREINFO="NONE"bcp is automatically used, which logs row inserts. This includes indexes implicitly created using the unique integrity constraint of a MOREINFO="NONE"create table statement. However, MOREINFO="NONE"bcp does not enforce the other integrity constraints defined for a table.ID="IN7149"bcp (bulk copy utility)fast version
ID="IN12550"bcp (bulk copy utility)select into/bulkcopy option and
ID="IN5451"Fast version of bcpREMAP="UN--Under"Because the fast version of MOREINFO="NONE"bcpID="IN11552"select into/bulkcopy database option, and bcp
 inserts data without logging it, the System Administrator or Database Owner must first set the MOREINFO="NONE"select into/bulkcopy option on with the system procedure ID="IN10753"sp_dboption system procedure, and bcp
MOREINFO="NONE"sp_dboption. If the option is not set on, and you try to copy data into a table that has no indexes or triggers, SQL Server generates an error message. You do not need to set this option in order to copy data out to a file, or in order to copy data in to a table that contains indexes or triggers. Because MOREINFO="NONE"bcp logs inserts into a table that has indexes or triggers, the log can grow very large. You can truncate the log withID="IN16654"dump transaction commanddump transaction command:select into/bulkcopyand select into/bulkcopy
MOREINFO="NONE" dump transaction ID="IN9955"dump database commandbcp and
after the bulk copy completes, after you have backed up your database with MOREINFO="NONE"dump database. ID="IN11556"select into/bulkcopy database option, and bcpID="INA12557"bcp (bulk copy utility)select into/bulkcopy option andMARK="BULL" REMAP="BU--Bullet"While the MOREINFO="NONE"select into/bulkcopy option is on, you cannot dump the transaction log. Issuing MOREINFO="NONE"dump transaction produces an error message instructing you to use MOREINFO="NONE"dump database instead. Be certain that you dump your database before you turn off the MOREINFO="NONE"select into/bulkcopy flag. If you insert unlogged data into your database, and then perform aMOREINFO="NONE" dump transaction before you perform a MOREINFO="NONE"dump database, you will not be able to recover your data. MARK="BULL" REMAP="BU--Bullet"UnloggedMOREINFO="NONE" bcp runs more slowly while a MOREINFO="NONE"dump database is taking place.LINKEND="X94464"Table 1-3 shows which version MOREINFO="NONE"bcp uses when copying in, the necessary settings for theMOREINFO="NONE" select into/bulkcopy option, and whether the transaction log is kept and dumpable. TOCENTRY="1"LBL="Table 1-3: " ID="X94464"Comparing Fast and Slow bcpCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="15*"COLNAME="C2" COLWIDTH="10*"COLNAME="C3" COLWIDTH="10*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select into/bulkcopy COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"on COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"off COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"fast MOREINFO="NONE"bcp(no indexes or triggers on 
target table)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction 
prohibitedCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"bcp prohibitedCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"slow MOREINFO="NONE"bcp(one or more indexes or 
triggers)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction 
prohibitedCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction 
OKMARK="BULL" REMAP="BU--Bullet"By default, the MOREINFO="NONE"select into/bulkcopy option is off in newly created databases. To change the default situation, turn this option on in the MOREINFO="NONE"model database. ID="IN7758"bcp (bulk copy utility)performance issues
ID="IN6859"Indexesdropping before using bcp
ID="IN6960"Triggersdropping before using bcpThe performance penalty for copying data into a table that has indexes or triggers in place can be severe. If you are copying in a very large number of rows, it may be faster to drop all the indexes and triggers first withID="IN9761"drop index command, and bcp
MOREINFO="NONE" drop index (orMOREINFO="NONE" alter tableID="IN9962"drop trigger command, and bcp
 for indexes created as a unique constraint) and MOREINFO="NONE"drop triggerID="IN9063"Space allocationindexes and triggers, copying with bcp
, set the database option, copy the data into the table, recreate the indexes and triggers, and then dump the database. Remember to allocate disk space for the construction of indexes and triggers-about 2.2 times the amount of space needed for the data for a clustered index. ID="INA8064"bcp (bulk copy utility)prompts and responses
ID="IN4665"Promptsbcp
Responding to bcp Prompts REMAP="BO--Body"When you copy data in or out using the MOREINFO="NONE"-n (native format) or MOREINFO="NONE"-c (character format) option, MOREINFO="NONE"bcp only prompts you for your password, unless you supplied it with the MOREINFO="NONE"-P option. If you don't supply either the MOREINFO="NONE"-n or MOREINFO="NONE"-c options, MOREINFO="NONE"bcp prompts you for information for each field in the table. MARK="BULL" REMAP="BU--Bullet"Each prompt displays a default value, in brackets, which you can accept by pressing Return. The prompts include: MARK="DASH" REMAP="BS--BulletSub"The file storage type, which can be character or any valid SQL Server datatype The prefix length, which is an integer indicating the length in bytes of the following data The storage length of the data in the file ID="IN7666"bcp (bulk copy utility)field terminatorsThe field terminator, which can be any character string REMAP="UN--Under"The row terminator is the field terminator of the last field in the table or file.ID="IN6967"Datatypesdefaults and bcp promptsMARK="BULL" REMAP="BU--Bullet"The bracketed defaults represent reasonable values for the datatypes of the field in question. For the most efficient use of space when copying out to a file: MARK="DASH" REMAP="BS--BulletSub"Use the default promptsCopy all data in their table datatypesUse prefixes as indicatedDo not use terminatorsAccept the default lengthsREMAP="UN--Under"The following table shows the defaults and possible alternate responses:PGWIDE=1 TOCENTRY="1"LBL="Table 1-4: "bcp Prompts, Their Defaults and ResponsesCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="9*"COLNAME="C2" COLWIDTH="23*"COLNAME="C3" COLWIDTH="20*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"PromptCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Default ProvidedCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Possible ResponsesVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"File Storage 
TypeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Use database storage type for most fields 
except:
   char for varchar
   binary for varbinaryCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"char to create or read a human-
readable file; any SQL Server 
datatype where implicit conversion is 
supported.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prefix LengthCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"0     for fields defined with char datatype (not storage type) and all fixed-length datatypes 1     for most other datatypes 2     for binary and varbinary saved as char 4     for text and image COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"0 if no prefix is desired; defaults are 
recommended in all other cases.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Storage LengthCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"For char and varchar, use defined length. 
For binary and varbinary saved as char, use 
double the defined length.
For all other datatypes, use maximum 
length needed to avoid truncation or data 
overflow.COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Default values, or greater, are 
recommended.COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Field or Row 
TerminatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"noneCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Up to 30 characters, or one of the 
following:
\t  tab
\n  newline
\r  carriage return
\0  null terminator
\   backslashMARK="BULL" REMAP="BU--Bullet"MOREINFO="NONE"bcpID="IN6468"Conversion of datatypes (bcp)
 can copy data out to a file either as its native (database) datatype, or as any datatype for which implicit conversion is supported for the datatype in question. MOREINFO="NONE"bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. For more information on datatype conversions, see MOREINFO="NONE"dbconvert in the Open Client DB-Library/C Reference Manual. Be careful copying data in native format from different versions of SQL Servers because they do not always have the same datatypes. ID="IN6469"Sizedata prefix-length (bcp)ID="IN13270"Prefix length, bcp field;bcp (bulk copy utility)prefix lengthMARK="BULL" REMAP="BU--Bullet"A prefix length is a 1-, 2-, or 4-byte integer that represents the length of each data value in bytes. It immediately precedes the data value in the host file. ID="IN6671"Datatypesstorage length in bcp
ID="IN6672"Datatypesstorage length in bcp
ID="IN9173"char datatype, and bcpIf fields are stored as char (except char, nchar, and binary fields) instead of their database datatypes, they take less file storage space with the default length and prefix or a terminator. MOREINFO="NONE"bcp can use either a terminator or a prefix to determine the most efficient use of storage space. MOREINFO="NONE"bcp suggests the maximum amount of storage space required for each field as the default. For char or varchar data, MOREINFO="NONE"bcp accepts any length. Fields defined in the database as char, nchar, and binaryID="IN6574"Padding, data, and bcp copying
 are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary(8). REMAP="UN--Under"If data in varchar and varbinary fields is longer than the length you specify for copy out, MOREINFO="NONE"bcpID="IN6875"Truncation, data, bcp copying and
ID="IN6276"Filesstorage length in bcp
 silently truncates the data in the file at the specified length.ID="IN5677"Field terminatorsbcp
ID="IN7678"bcp (bulk copy utility)field terminators
ID="IN6579"Tab data field terminator, bcp
ID="IN7480"\t (tab) data field terminator (bcp)
ID="IN6681"Newline terminator (\n), bcp
ID="IN7882"\n (newline) data field terminator (bcp)A field terminator string can be up to 30 characters long; the most common terminators are a tab (entered as "\ " and used for all columns except the last one), and a newline (entered as "\n" and used for the last field in a row). Other terminators are: ID="IN9983"Null field terminator (\0), bcp
ID="INA7584"\0 (null) data field terminator (bcp)
ID="IN7985"Backslash (\)data field terminator (bcp)
ID="IN8686"Carriage return (\r) data field terminator (bcp)
ID="IN9987"Return character. See Carriage return character (\r)
"\0" (the null terminator), "\" (backslash), and "\r" (Return). When choosing a terminator, be sure that its pattern does not appear in any of your character data. For example, if you use tab terminators with a string that contains a tab, MOREINFO="NONE"bcp would not know which tab represents the end of the string. Since MOREINFO="NONE"bcp always looks for the first possible terminator, in this case it would find the wrong one. REMAP="UN--Under"When a terminator or prefix is present, it affects the actual length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is followed immediately by the terminator, or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from SQL Server already padded to the full length). ID="IN5588"Dataflushing by bcpREMAP="UN--Under"When copying in from a file, data is transferred until either the number of bytes indicated in the "Length" prompt have been copied or the terminator is encountered. Once a number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed. The following tables show the interaction of prefix lengths, terminators, and field length on the information in the file. "P" indicates the prefix in the stored table. "T" indicates the terminator, and dashes (-) show appended spaces. An ellipsis (...) indicates that the pattern repeats for each field. The field length is 8 for each column, and "string" represents the 6-character field each time. TOCENTRY="1"LBL="Table 1-5: "SQL Server char DataCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="5*"COLNAME="C2" COLWIDTH="6*"COLNAME="C3" COLWIDTH="6*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prefix length=0COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prefix length 1, 2 or 4VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"No terminatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"string--string--...COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Pstring--Pstring--...COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"TerminatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"string--Tstring--T...COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Pstring--TPstring--T...TOCENTRY="1"LBL="Table 1-6: "Other Datatypes Converted to char StorageCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="5*"COLNAME="C2" COLWIDTH="6*"COLNAME="C3" COLWIDTH="6*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prefix length=0COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Prefix length 1, 2 or 4VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"No terminatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"string--string--...COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"PstringPstring...COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"TerminatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"stringTstringT...COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"PstringTPstringT...ID="IN6589"Sizefile storage length (bcp)MARK="BULL" REMAP="BU--Bullet"Note that the file storage type and length of a column do not have to be the same as the type and length of the column in the database table. (If types and formats copied in are incompatible with the structure of the database table, the copy fails.) File storage length is generally the maximum amount of data to be transferred for the column, plus terminators and/or prefixes. When copying data into a table, MOREINFO="NONE"bcp observes any defaults defined for columns and user-defined datatypes. However, MOREINFO="NONE"bcpID="IN8190"bcp (bulk copy utility)rules and copying data
 ignores rules in order to load data at the fastest possible speed. Because MOREINFO="NONE"bcpID="IN6491"Null columns, and bcp copying
 considers any data column that can contain null values to be variable length, use either a length prefix or terminator to denote the length of each row of data. ID="IN9592"datetime datatype, and bcpData written to a host file in its native format preserves all of its precision. datetimeID="IN9193"float datatypebcp and
 and floatID="IN10094"money datatype, and bcp copying
 values preserve all of their precision even when they are converted to character format. SQL Server stores moneyID="IN9795"Roundingmoney values in bcp
 values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places. Before copying data that is in character format from a file into a database table, check the datatype entry rules in the "Datatypes" section of the SQL Server Reference Manual. Character data that is being copied into the database with MOREINFO="NONE"bcp must conform to those rules. Note especially that dates in the undelimited (yy)yymmdd format may result in overflow errors if the year is not specified first.When you send host data files to sites that use terminals different from your own, inform them of the datafile_charset that you used to create the files.ID="IN5696"Pipes, named, and bcpYou cannot use named pipes to MOREINFO="NONE"bcp files in or out.MessagesREMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"Error in attempting to determine the size of a pair of translation tables.:'stat' utility failed.REMAP="UN--Under"The character translation file(s) named with the MOREINFO="NONE"-a or MOREINFO="NONE"-q parameter is missing, or you mistyped the name(s).ID="IN8197"bcp (bulk copy utility)
ID="IN7098"Copyingtables with bcp
ID="IN9299"Operating system filesbcp copying to or from
ID="IN81100"Database objectscopying using bcpREMAP="UN--Under"buildmaster
ID="IN792"Master device, buildmaster initialization of
Builds the master device and creates the ID="IN1083"master databasebuildmaster creation of
master, ID="IN1084"model database, buildmaster creation of
model, and ID="IN1095"tempdb database, buildmaster creation of
tempdb databases on the device.FORMAT="LINESPECIFIC"buildmaster [-d MOREINFO="NONE"physicalname] [-c MOREINFO="NONE"cntrltype] [-s MOREINFO="NONE"size] [-r] [-m] [-x]ParametersREMAP="RI--RefIndent"MOREINFO="NONE"-d MOREINFO="NONE"physicalname - is the physical name of the raw disk partition or operating system file where the master device resides.MOREINFO="NONE"-c MOREINFO="NONE"cntrltypeID="IN376"Numberscontroller, for master device - is the controller number for the master device. Together, MOREINFO="NONE"cntrltype and MOREINFO="NONE"physicalname specify the device. The default value for MOREINFO="NONE"cntrltype is 0. Do not change this value unless instructed to do so. MOREINFO="NONE"-s MOREINFO="NONE"size - ID="IN527"Sizemaster device
is the size of the master device in 2K blocks. For example, a size of "5120" creates a 10 megabyte master device. MOREINFO="NONE"buildmaster verifies that the value you specify for this parameter does not exceed the space available to the master device, unless you use the MOREINFO="NONE"-m or MOREINFO="NONE"-r option.MOREINFO="NONE"-r ID="IN828"Configuration variablesbuildmaster and default - rewrites the configuration block that contains the system startup parameters with the default values without disturbing anything else on the database device. This option changes the configuration variables to their default values without rebuilding the master database. Use this option when the value of a configuration variable is set so high that SQL Server cannot start.MOREINFO="NONE"-m - rewrites only the master database, without changing the configuration block or initializing the master device. Use this option when the master database is corrupted but the other databases on the master device are undamaged. If you use both MOREINFO="NONE"-m and MOREINFO="NONE"-r, then MOREINFO="NONE"buildmaster rewrites the configuration block that contains the system start parameters (as derived from the system tables ID="IN1049"sysconfigures tablebuildmaster and
sysconfiguresID="IN10510"syscurconfigs table, buildmaster and
 and syscurconfigs) and the master database without initializing the rest of the master device.MOREINFO="NONE"-x - rewrites only the model database, without changing the configuration block or initializing the master device. Use this option when the model database is corrupted and you cannot load it sucessfully from a backup. If you modified model, you must restore it from a backup after reinitializing it with this option.ExamplesNUMERATION="ARABIC" REMAP="E1--ExampleOne" CONTINUATION="RESTARTS" INHERITNUM="IGNORE"MOREINFO="NONE"buildmaster -d /dev/rsd1f -s8704REMAP="UN--Under"Initializes the raw device /dev/rsd1f as a 17 Mb master device, and creates the system databases master, model, and tempdb on the device.CommentsThe MOREINFO="NONE"buildmaster program initializes the specified device (database device) as a SQL Server master device, and builds the master and model databases on it. MOREINFO="NONE"buildmaster also provides options for recovery from configuration errors and other disasters. ID="IN11411"sybinit installation programbuildmaster andThe MOREINFO="NONE"sybinit installation program runs MOREINFO="NONE"buildmaster and builds an initial master database on the database device you specify in answer to the program's prompts. ID="IN5412"Promptsbuildmaster
ID="IN7013"buildmaster utility commandpromptsIf you run MOREINFO="NONE"buildmaster with no options, it prompts for the information listed below. You must enter a response for each prompt. REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"master disk name? master disk controller number? master disk size? configuration only?(y or n) (same as MOREINFO="NONE"-r) databases only? (y or n) (same as MOREINFO="NONE"-m) If you rebuild the modelID="IN11014"installmodel script;Scriptsinstallmodel
 database, you must run the MOREINFO="NONE"installmodel script, located in $SYBASE/scripts. MOREINFO="NONE" installmodel sets up the necessary permissions for the model database. To run the script enter the following series of commands:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"cd $SYBASE/scriptssetenv DSQUERY server_nameisql -Usa -P -Sserver_name < installmodelIf you rebuild the master database, and do not have a database dump, or cannot load the dump, you must:NUMERATION="ARABIC" REMAP="S1--StepOne" CONTINUATION="RESTARTS" INHERITNUM="IGNORE"Start the server using MOREINFO="NONE"startserver.If your sybsystemprocs database is undamaged, run MOREINFO="NONE"disk reinit and MOREINFO="NONE"disk refit to restore the system tables entries. Otherwise, create a sybsystemprocs database on any device or on an operating system file. This database should be at least 10 Mb. If you do not have a sybsystemprocs database, the next step tries to install the system procedures in the master database.ID="IN11215"installmaster script;ScriptsinstallmasterRun the MOREINFO="NONE"installmaster script. To run the script, use the following commands:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"cd $SYBASE/scriptssetenv DSQUERY server_nameisql -Usa -P -Sserver_name < installmasterREMAP="BSU--BSUnder"The MOREINFO="NONE"installmaster script installs the system stored procedure tables such as spt_valuesID="IN7116"System procedures, installmaster and
 in the master database and creates the system procedures in ID="IN5417"sybsystemprocs table
sybsystemprocs. If sybsystemprocs does not exist, installmaster creates the procedures in master.ID="IN6818"Passwordsbuildmaster -m and null
ID="IN6519"SA sccount, buildmaster -m andThe password to the default "sa" account reverts to NULL after you run MOREINFO="NONE"buildmaster -mID="IN7620"Lock state, SA account and buildmaster -m
, and the account is unlocked.ID="IN8521"buildmaster utility command
 Loading a backup of the master database restores the password and lock state from when the dump was taken.ID="X76998"dataserverID="INA721"dataserver utility command
ID="IN642"SQL Serverdataserver command
The executable form of the SQL Server program. FORMAT="LINESPECIFIC"dataserver -dMOREINFO="NONE"devicename [-eMOREINFO="NONE"errorlogfile] [-m][-rMOREINFO="NONE"mastermirror] [-Msharedmem_directory] [-psso_login_name]ParametersREMAP="RI--RefIndent"MOREINFO="NONE"-d MOREINFO="NONE"devicename - ID="IN993"master databasedataserver and
is the full path name of the master database device. The master database device must be writable by the user who starts SQL Server. The default master database device name is d_master. MOREINFO="NONE"-e MOREINFO="NONE"errorlogfile - ID="IN594"Error logdataserver and
is the full path name of the error log file for SQL Server system-level error messages. MOREINFO="NONE"-m  - ID="IN725"Single-user modedataserver -m option
starts SQL Server in single user mode. MOREINFO="NONE"-r MOREINFO="NONE"mastermirror - ID="IN656"Disk mirroring, dataserver and
ID="IN927"Mirroring master database with dataserver utility commandstarts the mirror of the master database device. Use this option to start SQL Server if the master database device has been damaged. MOREINFO="NONE"-M MOREINFO="NONE"sharedmem_directory - ID="IN658"Shared memory file, dataserver
ID="IN779"Filesshared memory and dataserver commandplaces shared memory files in the specified directory instead of in the default location, $SYBASE. If sharedmem_directory starts with "/", the directory name is assumed to be absolute. Otherwise, the directory name is interpreted relative to $SYBASE.MOREINFO="NONE"-p - specifies the login name of a System Security Officer when starting SQL Server, for the purposes of getting a new password for that account. SQL Server generates a random password, displays it, encrypts it, and saves it in master..syslogins as that account's new password. CommentsID="IN11210"startserver utility commanddataserver andMARK="BULL" REMAP="BU--Bullet"Start SQL Server with the MOREINFO="NONE"startserverID="IN7311"Configuration variablesdataserver and
 command rather than by directly executing the MOREINFO="NONE"dataserver program. ID="IN10312"sysconfigures tabledataserver andSQL Server derives its running environment from values in the sysconfigures system table. Run the system procedure MOREINFO="NONE"sp_configure to see configuration values; use MOREINFO="NONE"sp_configure and MOREINFO="NONE"reconfigure to change configuration. ID="IN1913"PasswordsforgottenBecause SQL Server passwords are encrypted, you cannot recover forgotten passwords. If all System Security Officers lose their passwords, the MOREINFO="NONE"-pID="IN3114"System Security Officer account
 option generates a new password for a System Security Officer account. Start SQL Server with MOREINFO="NONE"-p, immediately log into SQL Server with the new random password and execute MOREINFO="NONE"sp_password to reset your password to a more secure one. After you have finished running the MOREINFO="NONE"sybinit installation program, be sure to set the file permissions on the MOREINFO="NONE"dataserver executable to limit who can execute it. ID="X55824"defncopy
Copies definitions for specified views, rules, defaults, triggers, or procedures from a database to an operating system file or from an operating system file to a database. 
ID="IN552"Copyinginvoked from the operating system
ID="IN823"Copyingdefinitions with defncopy
ID="IN774"Views, copying with defncopy
ID="IN765"Rulescopying with defncopy
ID="IN796"Defaultscopying with defncopy
ID="IN797"Triggerscopying with defncopy
ID="INA898"Stored procedures, copying with defncopyFORMAT="LINESPECIFIC"defncopy [-U MOREINFO="NONE"username][-P MOREINFO="NONE"password] -S MOREINFO="NONE"server [-v]   [-I MOREINFO="NONE"interfaces_file] [-a MOREINFO="NONE"display_charset]   [-J MOREINFO="NONE"client_charset] [-z MOREINFO="NONE"language] [-X]  {in MOREINFO="NONE"filename dbname | out MOREINFO="NONE"filename dbname      [MOREINFO="NONE"owner.]MOREINFO="NONE"objectname [[MOREINFO="NONE"owner.]MOREINFO="NONE"objectname]...}ParametersThe MOREINFO="NONE"defncopy utility cannot copy table definitions or reports created with Report Workbench.REMAP="RI--RefIndent"MOREINFO="NONE"-U MOREINFO="NONE"username - allows you to specify a login name. Login names are case-sensitive. If you do not specify username, MOREINFO="NONE"defncopy uses the current user's operating system login name. MOREINFO="NONE"-P MOREINFO="NONE"password - allows you to specify your password. If you do not specify MOREINFO="NONE"-P, MOREINFO="NONE"defncopy prompts for your password. MOREINFO="NONE"-S MOREINFO="NONE"server - allows you to specify the name of the SQL Server to connect to. If you specify MOREINFO="NONE"-S with no argument, MOREINFO="NONE"defncopy looks for a server named SYBASE. If you do not specify MOREINFO="NONE"-S, MOREINFO="NONE"defncopy uses the server specified by your DSQUERY environment variable.MOREINFO="NONE"-v  - ID="IN619"Copyright messagedefncopyID="IN5910"Numbersdefncopy versionID="IN5811"Version numberdefncopydisplays the version and copyright message of MOREINFO="NONE"defncopy and returns to the operating system. MOREINFO="NONE"-I MOREINFO="NONE"interfaces_file - allows you to specify the name and location of the interfaces file to search when connecting to SQL Server. If you do not specify MOREINFO="NONE"-I, MOREINFO="NONE"defncopy looks for a file named interfaces in the directory that your SYBASE environment variable specifies.MOREINFO="NONE"-a  MOREINFO="NONE"display_charset - allows you to run MOREINFO="NONE"defncopy from a terminal whose character set differs from that of the machine on which MOREINFO="NONE"defncopy is running. MOREINFO="NONE"-a in conjunction with MOREINFO="NONE"-J specifies the character set translation file (MOREINFO="NONE".xlt file) required for the conversion. Use MOREINFO="NONE"-a without MOREINFO="NONE"-J only if the client character set is the same as the default character set.REMAP="UN--Under"In ID="IN9512"Japanese character setsdefncopy and;Character sets:Japanese
Japanese language environments, the MOREINFO="NONE"-q flag is required to translate Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument ``zenkaku'' and with the MOREINFO="NONE"-J flag to indicate the client's Japanese character set (MOREINFO="NONE"sjis or MOREINFO="NONE"eucjis). The zenkaku.xlt file was designed to translate only from terminal display to SQL Server, not from SQL Server to the terminal. ID="IN6913"ascii_7 character setdefncopy andThe ascii_7 character set is compatible with all character sets. If either the SQL Server's or client's character set is set to ascii_7, any 7-bit ASCII character is allowed to pass between client and server unaltered. Other characters produce conversion errors. (Character set conversion issues are covered more thoroughly in the System Administration Guide.)REMAP="RI--RefIndent"MOREINFO="NONE"-J MOREINFO="NONE"client_charset - ID="IN5814"Character setsdefncopy
specifies the character set to use on the client. A filter converts input between MOREINFO="NONE"client_charset and the SQL Server character set. REMAP="UN--Under"MOREINFO="NONE"-J MOREINFO="NONE"client_charset requests that SQL Server convert to and from MOREINFO="NONE"client_charset, the client's character set. REMAP="UN--Under"MOREINFO="NONE"-J with no argument sets character set conversion to NULL. No conversion takes place. Use this if the client and server are using the same character set. REMAP="UN--Under"OmittingMOREINFO="NONE" -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using. (See the System Administration Guide
 and the System Administration Guide Supplement for your platform for more information about character sets and the associated flags.)MOREINFO="NONE"-z MOREINFO="NONE"language - ID="IN6415"Languages, alternatedefncopy
is the official name of an alternate language that the server uses to display MOREINFO="NONE"defncopy prompts and messages. Without the MOREINFO="NONE"-z flag, MOREINFO="NONE"defncopy uses the server's default language. Add languages to a SQL Server at installation, or afterwards with the utility MOREINFO="NONE"langinstall or the stored procedure MOREINFO="NONE"sp_addlanguage.MOREINFO="NONE"-X  - ID="IN6416"Passwordsdefncopy encryption
specifies that in this connection to the server, initiate the login with client-side password encryption. MOREINFO="NONE"defncopy (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which MOREINFO="NONE"defncopy uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.REMAP="UN--Under"If MOREINFO="NONE"defncopy crashes, the system creates a core file which contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.MOREINFO="NONE"in |MOREINFO="NONE" out  - ID="IN9417"in | out optiondefncopy
specifies the direction of definition copy. MOREINFO="NONE"filename  - specifies the name of the operating system file destination or source for the definition copy. The copy out overwrites any existing file. MOREINFO="NONE"dbname  - specifies the name of the database to copy the definitions from or to.MOREINFO="NONE"objectname  - specifies name(s) of database object(s) for MOREINFO="NONE"defncopy to copy out. Do not use MOREINFO="NONE"objectname when copying definitions in. CommentsInvoke the MOREINFO="NONE"defncopy program directly from the operating system. MOREINFO="NONE"defncopy provides a non-interactive way of copying out definitions (MOREINFO="NONE"create statements) for views, rules, defaults, triggers, or procedures from a database to an operating system file. Alternatively, it copies in all the definitions from a specified file. ID="IN5518"PermissionsdefncopyREMAP="UN--Under"You must have MOREINFO="NONE"select permission on the sysobjects and syscomments tables to copy out definitions; you do not need permission on the object itself. ID="IN7419"Database objectscopying using defncopyYou must have the appropriate MOREINFO="NONE"create permission for the type of object you are copying in. Objects copied in belong to the copier. A System Administrator copying in definitions on behalf of a user must log in as that user to give the user proper access to the reconstructed database objects. ID="IN7020"Names, defncopy in file or out file
ID="IN6821"Filesnames of defncopy in or outThe MOREINFO="NONE"in filename or MOREINFO="NONE"out filename and the database name are required and must be unambiguously stated. For copying out, use file names that reflect both the object's name and its owner. MOREINFO="NONE"defncopy ends each definition that it copies out with the comment REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"/* ### DEFNCOPY: END OF DEFINITION  */REMAP="UN--Under"Definitions you create as text must end with this comment, or MOREINFO="NONE"defncopy cannot copy them in successfully. ID="IN7222"Text, defncopy copying definitions asEnclose values specified to MOREINFO="NONE"defncopy in quotation marks if they contain characters that could be significant to the shell. ID="IN10823"Commentsdefncopy and create statement
ID="IN11024"create statements, copying with defncopyLong comments (>100 characters) placed before a MOREINFO="NONE"create statement may cause MOREINFO="NONE"defncopy to fail. ID="IN8225"defncopy utility commandID="IN7526"Views, copying with defncopyID="IN8027"Copyingdefinitions with defncopyREMAP="BO--Body"ID="IN7728"Defaultscopying with defncopy
ID="IN7429"Rulescopying with defncopy
ID="IN7730"Triggerscopying with defncopy
ID="IN8731"Stored procedures, copying with defncopy
ID="IN5332"Copyinginvoked from the operating systemisqlID="IN803"isql utility command
ID="IN1032"SQL parser utility. See isql utility command
ID="IN784"SQL Serverisql SQL parser to
ID="IN685"Data parsing (isql)
Interactive SQL parser to SQL Server. FORMAT="LINESPECIFIC"isql [-e] [-F] [-g] [-p] [-n] [-v] [-X] [-Y][-a MOREINFO="NONE"display_charset] [-c MOREINFO="NONE"cmdend] [-E editor][-h MOREINFO="NONE"headers] [-H hostname] [-i inputfile] [-I MOREINFO="NONE"interfaces_file] [-J MOREINFO="NONE"client_charset] [-l login_timeout] [-m MOREINFO="NONE"errorlevel] [-o outputfile] [-P MOREINFO="NONE"password] [-s MOREINFO="NONE"colseparator] [-S MOREINFO="NONE"server] [-t MOREINFO="NONE"timeout] [-U MOREINFO="NONE"username] [-w MOREINFO="NONE"columnwidth] [-y sybase_directory] [-z MOREINFO="NONE"language] [-A size]FORMAT="LINESPECIFIC"ID="INA606"Command terminator (isql)To terminate a command:  goTo clear the query buffer:  resetID="IN1297"Terminator, command. See Command terminator (isql)ID="INB898"reset command, isqlTo call the editor:  viID="IN599"Calling an editor (isql)ID="IN5210"vi default editorTo execute an operating system command: !!MOREINFO="NONE" commandID="IN8011"Operating systemscommands prefix (!!) (isql)ID="IN9812"!! (exclamation points) operating system commands prefix (isql)ID="IN9813"Exclamation points (!!) operating system commands prefix (isql)To exit from MOREINFO="NONE"isql:  quit or exitID="IN8814"quit command, isqlID="IN8815"exit command, isql ParametersREMAP="RI--RefIndent"MOREINFO="NONE"-e  - echoes input. MOREINFO="NONE"-F  - ID="IN5316"FIPS flagger, isql
enables the FIPS flagger. With this option, the server flags any non-ANSI standard commands sent.MOREINFO="NONE"-p - ID="IN5017"Statisticsisql
prints out performance statistics. MOREINFO="NONE"-n - ID="IN6718"Numbersline, removing from isql
removes numbering and the prompt symbol (>) from input lines. MOREINFO="NONE"-v - ID="IN5519"Numbersisql version
ID="IN5420"Version numberisql
ID="IN5721"Copyright messageisqlprints the version and copyright of the MOREINFO="NONE"isql software that you are using, and exits. MOREINFO="NONE"-X - ID="IN8922"Encryption, password. See Password encryption
ID="IN6023"Passwordsisql encryption
initiates the login connection to the server with client-side password encryption. MOREINFO="NONE"isql (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which MOREINFO="NONE"isql uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.REMAP="UN--Under"If MOREINFO="NONE"isql crashes, the system creates a core file which contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.MOREINFO="NONE"-Y - ID="INA2024"Chained transactions
tells the SQL Server to use chained transactions.MOREINFO="NONE"-a MOREINFO="NONE"display_charset  - allows you to run MOREINFO="NONE"isql from a terminal whose character set differs from that of the machine on which MOREINFO="NONE"isql is running. MOREINFO="NONE"-a in conjunction with MOREINFO="NONE"-J specifies the character set translation file (.xlt file) required for the conversion. Use MOREINFO="NONE"-a without MOREINFO="NONE"-J only if the client character set is the same as the default character set.REMAP="UN--Under"In Japanese language environments, the MOREINFO="NONE"-qID="IN9425"Half-width characters. See Japanese character sets
ID="IN9426"Full-width characters. See Japanese character sets
 flag is required to translate Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument "zenkaku" and with the MOREINFO="NONE"-J flag to indicate the client's Japanese character set (MOREINFO="NONE"sjis or MOREINFO="NONE"eucjis). The zenkaku.xlt file was designed to translate only from terminal display to SQL Server, not from SQL Server to the terminal. ID="IN6527"ascii_7 character setisql and
ID="IN2728"Errorscharacter conversionThe ascii_7 character set is compatible with all character sets. If either the SQL Server's or client's character set is set to ascii_7, any 7-bit ASCII character is allowed to pass between client and server unaltered. Other characters produce conversion errors. See the System Administration Guide for more information on character set conversion.REMAP="RI--RefIndent"MOREINFO="NONE"-c MOREINFO="NONE"cmdend - ID="IN7029"Command terminator (isql)resetting
ID="IN8430"isql utility commandresetting command terminator
resets the command terminator. By default, terminate commands and send them to SQL Server by entering "go" on a line by itself. When you reset the command terminator, do not use SQL reserved words or control characters. MOREINFO="NONE"-E MOREINFO="NONE"editor - ID="IN7331"isql utility commandeditors, using in
specifies an editor other than your default editor.MOREINFO="NONE"-h MOREINFO="NONE"headers - ID="IN6432"isql utility commandheadings
specifies how many rows to print between column headings. The default prints headings only once for each set of query results. MOREINFO="NONE"-H MOREINFO="NONE"hostname  - sets the client hostname. MOREINFO="NONE"-i MOREINFO="NONE"inputfile  - specifies the name of an operating system file to use for input to MOREINFO="NONE"isql. The file must contain command terminators ("go" by default).REMAP="UN--Under"Specifying the parameter as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"-i inputfileREMAP="UN--Under"is equivalent to:ID="IN12733"\isql;Redirect in symbol (\isqlREMAP="UI--UserInput" FORMAT="LINESPECIFIC"< inputfileREMAP="UN--Under"If you use MOREINFO="NONE"-i and do not specify your password on the command line, MOREINFO="NONE"isql prompts you for it.REMAP="UN--Under"If you use MOREINFO="NONE"< inputfile and do not specify your password on the command line, you must specify your password as the first line of the input file.MOREINFO="NONE"-I MOREINFO="NONE"interfaces_file - ID="IN5534"Interfaces fileisql
specifies the name and location of the interfaces file to search when connecting to SQL Server. Without MOREINFO="NONE"-I, MOREINFO="NONE"isql looks for a file named interfaces in the directory specified by your SYBASE environment variable. MOREINFO="NONE"-J MOREINFO="NONE"client_charset  - specifies the character set to use on the client. MOREINFO="NONE"-J MOREINFO="NONE"client_charset requests that SQL Server convert to and from MOREINFO="NONE"client_charsetID="IN6835"Filters, character set inputisql
, the character set used on the client. A filter converts input between MOREINFO="NONE"client_charset and the SQL Server character set. REMAP="UN--Under"MOREINFO="NONE"-J with no argument sets character set conversion to NULL. No conversion takes place. Use this if the client and server use the same character set. REMAP="UN--Under"OmittingMOREINFO="NONE" -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using. (See the System Administration Guide and the System Administration Guide Supplement for your platform for more information about character sets and the associated flags.)MOREINFO="NONE"-l MOREINFO="NONE"login_timeout  - specifies the maximum timeout value allowed when connecting to SQL Server. MOREINFO="NONE"-m MOREINFO="NONE"errorlevel - ID="IN6736"Error messagesisql user-defined
customizes the error message display. For errors of the severity level specified or higher only the message number, state, and error level display; no error text appears. For error levels lower than the specified level, nothing appears. MOREINFO="NONE"-o MOREINFO="NONE"outputfile  - specifies the name of an operating system file to store the output from MOREINFO="NONE"isql. Specifying the parameter as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"-o outputfile REMAP="UN--Under"is similar to:ID="IN13137"\> (redirect out), in isql;Redirect out symbol (\>), isqlREMAP="UI--UserInput" FORMAT="LINESPECIFIC"> outputfileMOREINFO="NONE"-P MOREINFO="NONE"password - ID="IN5838"Passwordsnull, in isql
specifies your current SQL Server password. If you do not specify the MOREINFO="NONE"-P flag, MOREINFO="NONE"isql prompts for a password. If your password is NULL, use theMOREINFO="NONE" -P flag at the end of the command line without any password. ID="IN6739"Columnsseparator character (isql)MOREINFO="NONE"-s MOREINFO="NONE"colseparator  - resets the column separator character, which is blank by default. To use characters that have special meaning to the operating system (for example, "|", ";", "&", "<", ">"), enclose them in quotes or precede them with a backslash. MOREINFO="NONE"-S MOREINFO="NONE"server  - specifies the name of the SQL Server to connect to. MOREINFO="NONE"isql looks this name up in the interfaces file. If you specify MOREINFO="NONE"-S with no argument, MOREINFO="NONE"isql looks for a server named SYBASE. Without MOREINFO="NONE"-S, MOREINFO="NONE"isql looks for the server specified by your DSQUERY environment variable. MOREINFO="NONE"-t MOREINFO="NONE"timeout - ID="IN5540"Timeout option, isql
specifies the number of seconds before a SQL command times out. If you do not specify a timeout, a command runs indefinitely. This affects commands issued from within MOREINFO="NONE"isql, not the connection time. The default timeout for logging into MOREINFO="NONE"isql is 60 seconds. MOREINFO="NONE"-U MOREINFO="NONE"username  - specifies a login name. Logins are case-sensitive. MOREINFO="NONE"-w MOREINFO="NONE"columnwidth  - sets the screen width for output. The default is 80 characters. When an output line reaches its maximum screen width, it breaks into multiple lines. MOREINFO="NONE"-y MOREINFO="NONE"sybase_directory  - specifies a SYBASE directory other than the default $SYBASE directory. MOREINFO="NONE"-z ID="IN6041"languageMOREINFO="NONE"language - is the official name of an alternate language to display MOREINFO="NONE"isql prompts and messages. Without MOREINFO="NONE"-z, MOREINFO="NONE"isql uses the server's default language. Add languages to a SQL Server at installation, or afterwards with the utility MOREINFO="NONE"langinstall or the stored procedure MOREINFO="NONE"sp_addlanguage. MOREINFO="NONE"-A MOREINFO="NONE"size - ID="IN7442"Packet size, networkspecifying in isql
ID="IN7343"Network packet sizespecifying in isql
ID="IN7444"Packet size, networkspecifying in isql
specifies the network packet size to use for this MOREINFO="NONE"isql session For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -A 2048REMAP="UN--Under"sets the packet size to 2,048 bytes for this MOREINFO="NONE"isql session. sizeID="IN8545"default network packet size configuration variable
 must be between the values of the MOREINFO="NONE"default network packet size and ID="IN8546"maximum network packet size configuration variable
MOREINFO="NONE"maximum network packet size configuration variables, and must be a multiple of 512.ExamplesNUMERATION="ARABIC" REMAP="E1--ExampleOne" CONTINUATION="RESTARTS" INHERITNUM="IGNORE"MOREINFO="NONE"isql -Ujoe -PabracadabraREMAP="UI--UserInput" FORMAT="LINESPECIFIC"1>MOREINFO="NONE"select * MOREINFO="NONE"2>MOREINFO="NONE"from authors MOREINFO="NONE"3>MOREINFO="NONE"where city = "Oakland" MOREINFO="NONE"4>MOREINFO="NONE"viREMAP="UN--Under"Puts you in a text file where you can edit the query. When you write and save the file, you are returned to MOREINFO="NONE"isql. The query appears; type MOREINFO="NONE"go to execute it. MOREINFO="NONE"isql -U almaMOREINFO="NONE"Password: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"1>MOREINFO="NONE"select * MOREINFO="NONE"2>MOREINFO="NONE"from authors MOREINFO="NONE"3>MOREINFO="NONE"where city = "Oakland" MOREINFO="NONE"4>MOREINFO="NONE"reset MOREINFO="NONE"1>MOREINFO="NONE"quitREMAP="UN--Under"MOREINFO="NONE"reset clears the query buffer. MOREINFO="NONE"quit returns you to the operating system. isql -a mac -J roman8REMAP="UN--Under"Specifies that you are running MOREINFO="NONE"isql from a Macintosh against a server that is using the roman8 character set.CommentsID="IN7547"isql utility commandusing interactivelyTo use MOREINFO="NONE"isql interactively, give the command MOREINFO="NONE"isql (and any of the optional flags) at your operating system prompt. The MOREINFO="NONE"isql program accepts SQL commands and sends them to SQL Server. The results are formatted and printed on standard output. Exit MOREINFO="NONE"isql with MOREINFO="NONE"quit or MOREINFO="NONE"exit.ID="IN6048"Command terminator (isql)
ID="IN9849"go command terminator (isql)Terminate a command by typing a line beginning with the default command terminator MOREINFO="NONE"goID="IN7750"Number (quantity of)go command executions
. You may follow the command terminator with an integer to specify how many times to run the command. For example, to execute this command 100 times, type: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select x = 1go 100 REMAP="UN--Under"The results appear once at the end of execution. ID="IN7451"isql utility commandspecifying optionsIf you enter an option more than once on the command line, isql uses the last value. For example, if you enter the following command:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -c"." -csendREMAP="UN--Under""send", the second value for MOREINFO="NONE"-cID="IN5952"Aliases, overriding isql
, overrides ".", the first value. This enables you to override any aliases you set up.ID="IN5953"Calling an editor (isql)
ID="IN6154"Environment variableEDITORTo call an editor on the current query buffer, enter its name as the first word on a line. Define your preferred callable editor by specifying it with the EDITORID="IN6155"EDITOR environment variable
 environment variable. If EDITOR is undefined, the default isID="IN5256"vi default editor
 vi. ID="IN8057"Operating systemscommands prefix (!!) (isql)
ID="IN9858"Exclamation points (!!) operating system commands prefix (isql)
ID="IN9859"!! (exclamation points) operating system commands prefix (isql)REMAP="UN--Under"Execute operating system commands by starting a line with "!!" followed by the command. Call alternate editors this way, without defining EDITOR. ID="IN9560"Buffer, query;Clearing existing query buffer;Query buffer, resetting;Resetting the query bufferTo clear the existing query buffer, type MOREINFO="NONE"reset on a line by itself. MOREINFO="NONE"isql discards any pending input. You can also enter Ctrl-c anywhere on a line; this cancels the current query and returns the user to the MOREINFO="NONE"isql prompt. ID="IN7661"Filesoperating system, reading with isql
ID="IN8462"Queries, reading operating system files with isqlRead in an operating system file containing a query for execution by MOREINFO="NONE"isql as follows: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -U alma -P****** < input_fileID="IN6063"Output redirection (isql)REMAP="UN--Under"The file must include command terminator(s). The results appear on your terminal. Read in an operating system file containing a query and direct the results to another file as follows: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -U alma -P****** < input_file > output_fileCase is significant for the MOREINFO="NONE"isql flags.MOREINFO="NONE"isqlID="IN8064"Datafloat, isql
ID="IN9265"float datatypeisql and
 displays only six digits of floatID="IN9266"real datatype, and isql
ID="IN7967"Datareal, isql
 or realID="IN6768"Roundingdatatype values in isql
 data after the decimal point, rounding off the remainder.ID="IN7569"Operating system filesreading into isql
ID="IN9370"Command buffer, reading operating system files into (isql)
ID="IN8371"Read operations, isql and operating system filesWhen using MOREINFO="NONE"isql interactively, read an operating system file into the command buffer with the command:ID="IN8172"\;r (interactive isql)r (interactive isql)REMAP="UI--UserInput" FORMAT="LINESPECIFIC":r filename REMAP="UN--Under"Do not include a command terminator in the file; enter the terminator interactively once you have finished editing. ID="IN13273"Commentsisql statement;isql utility command:using comments inYou can include comments in a Transact-SQL statement submitted to SQL Server by MOREINFO="NONE"isqlID="IN5674"Nesting isql comments
. Open a comment with "/*". Close it with "*/". Comments can be nested. For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"select au_lname, au_fname /*retrieve authors' last and first names*/ from authors, titles, titleauthor where authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id /*this is a three-way join that links authors **to the books they have written.*/ See Also COLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="129*"COLNAME="C2" COLWIDTH="357*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"System proceduresCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"BOOK="ref" LINKEND="X44244"sp_addlanguage, BOOK="ref" LINKEND="X25636"sp_addlogin, BOOK="ref" LINKEND="X60334"sp_configure, 
BOOK="ref" LINKEND="X24535"sp_defaultlanguage, BOOK="ref" LINKEND="X82772"sp_droplanguage, 
BOOK="ref" LINKEND="X13013"sp_helplanguageREMAP="BO--Body"ID="IN7875"isql utility command
ID="IN7676"SQL Serverisql SQL parser to
ID="IN6677"Data parsing (isql)
MOREINFO="NONE"langinstall
ID="IN862"Installinglanguage using langinstall
ID="IN983"Languages, alternateinstalling using langinstall
Installs one new language on SQL Server. FORMAT="LINESPECIFIC"langinstall  [-S MOREINFO="NONE"server] [-I MOREINFO="NONE"interfaces_file][-P MOREINFO="NONE"password] [-R MOREINFO="NONE"release_number] [-v] MOREINFO="NONE"language character_setParametersREMAP="RI--RefIndent"MOREINFO="NONE"-S MOREINFO="NONE"server  - specifies the name of the SQL Server to connect to. If you do not specify MOREINFO="NONE"-S, MOREINFO="NONE"langinstall uses the server specified by your DSQUERY environment variable. If DSQUERY is not set, MOREINFO="NONE"langinstall attempts to connect to a server named SYBASE attempts to connect to a server named SYBASE. MOREINFO="NONE"-I MOREINFO="NONE"interfaces_file  - specifies the name and location of the interfaces file that MOREINFO="NONE"langinstall searches when connecting to SQL Server. If you do not specify MOREINFO="NONE"-I, MOREINFO="NONE"langinstall uses the interfaces file in the directory that your MOREINFO="NONE"SYBASE environment variable specifies. If SYBASE is not set, MOREINFO="NONE"langinstall looks for the default SYBASE home directory.MOREINFO="NONE"-P MOREINFO="NONE"password  - specifies the System Administrator's password. Only the System Administrator can run MOREINFO="NONE"langinstall. If you omit MOREINFO="NONE"-P, MOREINFO="NONE"langinstall prompts for the System Administrator's password. MOREINFO="NONE"language  - is the official name of the language to install. You must specify a language.MOREINFO="NONE"character_set  - ID="IN614"Character setslanginstallis the name of SQL Server's default character set. character_setID="IN375"Fileslocalization;Localization files
ID="IN626"common.loc localization file
 indicates the directory name of the localization files for the language. The common.locID="IN627"server.loc localization file
 and server.loc localization files for an official language reside in the character set directory $SYBASE/locales/language/character_set. You must specify a character set.MOREINFO="NONE"-R MOREINFO="NONE"release_number - ID="IN678"Release numbers, and langinstall
ID="IN679"Numbersrelease, langinstall and
specifies the release number, in the format n.n.n., to use to upgrade messages in master..sysmessages. Use MOREINFO="NONE"-R only in failure conditions, such as if MOREINFO="NONE"langinstall fails or in case of user error, when you think that messages in sysmessages are out of date. MOREINFO="NONE"-v  - prints the version number and copyright message for MOREINFO="NONE"langinstall, and exits. CommentsMARK="BULL" REMAP="BU--Bullet"ID="IN11412"sybinit installation programlanginstall and
MOREINFO="NONE"sybinit runs MOREINFO="NONE"langinstall automatically for a new installation as well as for customers who are upgrading from a previous release. MOREINFO="NONE"langinstall does the following: ID="IN10413"syslanguages table, and langinstallMARK="DASH" REMAP="BS--BulletSub"Adds the specified language-specific information to master..syslanguages using the MOREINFO="NONE"sp_addlanguage stored procedure. If the language already exists, MOREINFO="NONE"langinstall updates the appropriate row in syslanguages. ID="IN10314"sysmessages table, langinstall andAdds to, updates, and deletes error messages as necessary from master..sysmessages. Updates syslanguages.update, inserting the new release number. ID="IN3715"Fileslocalization;Localization files
MOREINFO="NONE"langinstall validates the entries found in the localization file sections that it uses. If anything is missing, MOREINFO="NONE"langinstall prints an error message and does not add the language to syslanguages. MOREINFO="NONE"langinstallID="IN8416"Version numberlocalization files and langinstall
 compares the version numbers of each of the localization files that it uses, common.loc and server.loc. If they are not the same it prints a warning message. syslanguages.upgrade is always set according to the version number in server.loc. The MOREINFO="NONE"-R option forces MOREINFO="NONE"langinstall to collect messages from a release previous to the most current one. ID="IN4117"Installingmessages from previous release
ID="IN7218"Error messageslanginstall updates of
ID="IN8519"Deleting obsolete error messages using langinstall
MOREINFO="NONE"langinstall compares the existing messages with the ones to be installed and replaces any that have changed.REMAP="UN--Under"For example, if the most current release is 4.9 but you think that sysmessages may not be correct, specify the messages added before the release in the syslanguages.upgrade column (4.9 in this case) with MOREINFO="NONE"-R MOREINFO="NONE"4.8MOREINFO="NONE". langinstallID="IN3720"Messages, installing previous release
 then installs all messages from Release 4.8 and earlier.PermissionsID="IN5821"PermissionslanginstallREMAP="BO--Body"Only System Administrators can run MOREINFO="NONE"langinstall.Tables UsedREMAP="BO--Body"MOREINFO="NONE"master.dbo.syslanguages, master.dbo.sysmessages See AlsoCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="129*"COLNAME="C2" COLWIDTH="357*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"System proceduresCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"BOOK="ref" LINKEND="X44244"sp_addlanguage, BOOK="ref" LINKEND="X25636"sp_addlogin, BOOK="ref" LINKEND="X60334"sp_configure, 
BOOK="ref" LINKEND="X24535"sp_defaultlanguage, BOOK="ref" LINKEND="X82772"sp_droplanguage, 
BOOK="ref" LINKEND="X13013"sp_helplanguage showserver
ID="IN702"Information (SQL Server)showserver
ID="IN283"Current SQL Servers, showing
Shows SQL Servers and Backup Servers currently running on the local machine. FORMAT="LINESPECIFIC"MOREINFO="NONE"showserver CommentsMARK="BULL" REMAP="BU--Bullet"MOREINFO="NONE"showserver prints process information about SQL Server or Backup Server. If no servers are running, only the header appears. ID="IN564"StatisticsshowserverID="IN716"SQL Servershowserver information onID="IN655"Serversshowserver informationID="IN747"Backup Servershowserver information onID="X28711"startserver
ID="IN721"Restarts, SQL Server, and startserver
ID="IN1452"SQL Serverrestarting with startserver;SQL Server:starting with startserver
ID="IN843"Serversstartserver utility command
Starts a SQL Server and/or a Backup Server. FORMAT="LINESPECIFIC"startserver [[-f MOREINFO="NONE"runserverfile] [-m]] ... ParametersREMAP="RI--RefIndent"MOREINFO="NONE"-f MOREINFO="NONE"runserverfile - ID="INA304"Filesrunserver;Runserver file
ID="IN755"Restarts, Backup Server, and startserverspecifies the name of a runserver file that is used as a reference each time you restart a SQL Server or Backup Server. By default, the runserver file is named RUN_servername. If you start a second SQL Server on the same machine, a new runserver file named RUN_servername is created. REMAP="UN--Under"The MOREINFO="NONE"startserver command creates SQL ServerID="IN606"Error logstartserver and
's error log file (named errorlog) in the directory where you start the server, and adds this information as part of the MOREINFO="NONE"-e option on the SQL Server executable line in the runserver file. If a second SQL Server is started on the same machine, a new error log named errorlog_servername is created; this information is added to that server's runserver file. The user must have execute permission on the specified runserver file. MOREINFO="NONE"-m  - ID="IN677"Single-user modestartserver and
ID="IN1238"allow updates configuration variable, startserver andstarts SQL Server in single user mode, allowing only one System Administrator to log in, and turns the MOREINFO="NONE"allow updatesID="IN1009"master databasestartserver and
 configuration variable on. Use this mode to restore the master database. The System Administrator can use theMOREINFO="NONE" dbo use only option of MOREINFO="NONE"sp_dboption for system administration activities that require more than one process, such as bulk copying or using the data dictionary. MOREINFO="NONE"startserver normally starts up only one server per node. REMAP="UN--Under"The MOREINFO="NONE"-m option creates a m_RUNSERVER file and overwrites any existing m_RUNSERVER file. ExamplesNUMERATION="ARABIC" REMAP="E1--ExampleOne" CONTINUATION="RESTARTS" INHERITNUM="IGNORE"MOREINFO="NONE"startserver REMAP="UN--Under"Starts a SQL Server named SYBASE.MOREINFO="NONE"startserver -f RUN_MYSERVER -f RUN_SYB_BACKUP REMAP="UN--Under"Starts a SQL Server named MYSERVER and a Backup Server named SYB_BACKUP.MOREINFO="NONE"startserver -f RUN_SYB_BACKUP REMAP="UN--Under"Starts only the Backup Server SYB_BACKUP.ID="X92237"CommentsThe master device must be writable by the user who starts SQL Server. You may specify more than one runserver file, as shown in Example 2. You may specify MOREINFO="NONE"-m after eachMOREINFO="NONE" -f MOREINFO="NONE"runserverfile.If the master database device has been damaged, use the dataserver -r option to start the mirror of the device. See LINKEND="X76998"dataserver for information.SQL Server derives its running environment from values in the sysconfigures system table. Run the system procedure MOREINFO="NONE"sp_configure and the Transact-SQL command MOREINFO="NONE"reconfigure to see or to change configuration. ID="IN6710"Permissionschanging startserverTo ensure the integrity of your SQL Server, it is important that you apply appropriate operating system protections to the startserver executable file.See AlsoCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="129*"COLNAME="C2" COLWIDTH="357*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"UtilitiesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"LINKEND="X97777"backupserver, LINKEND="X76998"dataserverID="IN8511"startserver utility commandREMAP="BO--Body"ID="IN8212"Serversstartserver utility commandNUMBER="2" ID="X72116"Using bcp to Transfer Data to and from SQL ServerID="IN761"Databases, copying with bcp
ID="IN832"bcp (bulk copy utility)
ID="IN1623"Copy inCopy in:!see also bcp (bulk copy utility)See also bcp (bulk copy utility)
ID="IN1644"Copy outCopy out:!see also bcp (bulk copy utility)See also bcp (bulk copy utility)
ID="IN1215"Copying, bulk. See bcp (bulk copy utility)
ID="IN1436"Data copying. See bcp (bulk copy utility)\; Copy in\; Copy out
ID="IN877"Data copying steps. See Copy in\; Copy out
ID="IN1898"Data files, transferring. See bcp (bulk copy utility)\; Format files (bcp)
ID="IN1939"Database files, transferring. See bcp (bulk copy utility)\; Format files (bcp)
ID="IN11710"Exchange files. See Format files (bcp)
ID="IN12911"Exchanging data files. See bcp (bulk copy utility)
ID="IN13412"Exporting data. See bcp (bulk copy utility)\; Copy out
ID="IN15513"File formats, bcp. See Format files (bcp)
ID="IN15914"FilesFiles:!see alsoSee also Format files (bcp)\; Tables\; Transaction logs
ID="IN14815"Format files (bcp)Format files (bcp):!see also FilesSee also Files
ID="IN14216"Human-readable exchange files. See Character format files (bcp)
ID="IN13317"Importing data. See bcp (bulk copy utility)\; Copy in
ID="IN18918"Interactive bcpInteractive bcp:!see alsoSee also bcp (bulk copy utility)
ID="IN13519"Output formats, data. See Copy out\; Format files (bcp)
ID="IN12220"Parsing, data. See Field terminators\; bcp
ID="IN12521"Transferring data. See bcp (bulk copy utility)RENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter explains how to use MOREINFO="NONE"bcp to move data between SQL Server and an operating system file. There are two ways to move data: MARK="BULL" REMAP="BU--Bullet"The bulk copy utility (MOREINFO="NONE"bcp), used as a standalone program from the operating systemOpen Client DB-Library routines from inside an application programREMAP="BO--Body"This chapter discusses MOREINFO="NONE"bcp in detail. See LINKEND="X12769""bcp"
 for the full command syntax and descriptions of the parameters.REMAP="BO--Body"For information about transferring data using Open Client DB-Library, see the Open Client and Open Server Common Libraries Reference Manual. ID="IN2622"DatacopyingRENDERAS="SECT1"Importing and Exporting DataREMAP="BO--Body"There are no Transact-SQL commands for the bulk transfer of data. Use the bulk copy utility (MOREINFO="NONE"bcp) from the operating system.REMAP="BO--Body"MOREINFO="NONE"bcpID="IN3423"Database management systems, other
 is most frequently used to import data previously associated with another program (such as another database management system). Use the dump facilities from the other program to put the data to be transferred into an operating system file. REMAP="BO--Body"You can also use the bulk copy utility for moving tables between SQL Servers, or between almost any data source that can produce an operating system file.ID="IN2024"Spreadsheet programsREMAP="BO--Body"SQL Server's bulk copy utility can transfer data for use with other programs as well-for example, with spreadsheet programs. MOREINFO="NONE"bcp moves the data from SQL Server into an operating system file; from there the other program can import the data. When you finish using your data with the other program, you can use MOREINFO="NONE"bcp to transfer it back into an operating system file, and then back onto SQL Server. ID="INA6725"Filesdata transfer format (bcp)
ID="IN9026"ASCII format, bcp and
ID="IN10927"bcp (bulk copy utility)ASCII format and
ID="IN1128"Binary data
ID="IN7629"bcp (bulk copy utility)binary format andREMAP="BO--Body"SQL Server can accept data in any character or binary format, as long as you can describe the terminators (the characters used to separate columns) or the length of the fields in the data file. The table structures need not be identical. When importing from a file, MOREINFO="NONE"bcp appends data to an existing database table; when exporting to a file, MOREINFO="NONE"bcp overwrites any previous contents of the file. RENDERAS="SECT1"Requirements for Using bcpREMAP="BO--Body"In general, you must supply the following information for transferring data to and from SQL Server: MARK="BULL" REMAP="BU--Bullet"Name of the database and tableName of the operating system fileDirection of the transfer (in or out)REMAP="BO--Body"In addition, you can optionally modify the datatype, file storage length, and terminator for each column. ID="IN3830"Information (SQL Server)data transferREMAP="BO--Body"When the transfer is complete, the program reports the number of rows successfully copied and some performance information. ID="IN7731"bcp (bulk copy utility)permissions needed
ID="IN5432"Permissionsbcp andRENDERAS="SECT2"Permissions Needed for Copying DataREMAP="BO--Body"In order to use MOREINFO="NONE"bcpID="IN11233"bcp (bulk copy utility)insert command andID="IN6134"insert commandpermissionsID="IN9235"insert commandbcp and, you must have a SQL Server account and the appropriate permissions on the database tables and operating system files that you will use. To copy data into a table, you must have MOREINFO="NONE"insert permission on the table.ID="IN6636"select command, permissions andREMAP="BO--Body"To copy a table out to an operating system file, the user must have MOREINFO="NONE"select permission on the following tables: MARK="BULL" REMAP="BU--Bullet"The table being copiedsysobjectsMOREINFO="NONE" syscolumnsMOREINFO="NONE" sysindexesMOREINFO="NONE" RENDERAS="SECT1"bcp Performance IssuesID="IN8537"Speed (SQL Server), bcp fast or slowREMAP="BO--Body"When using MOREINFO="NONE"bcpID="IN16338"bcp (bulk copy utility)performance issues;Performance:bcp issuesID="IN16339"Recoverybcp speed and;bcp (bulk copy utility):recoverability andto copy data into a target table, you must make some choices between performance and recoverability. These choices depend on the size of the table into which you are copying, the amount of data you are copying in or out, the number of indexes on the table, and the amount of spare database device space that you have for recreating indexes, if you decide to remove them. ID="IN8540"bcp (bulk copy utility)slow version
ID="IN8541"bcp (bulk copy utility)fast versionREMAP="BO--Body"These issues are discussed in detail in the following section. Here is a summary of the main considerations: MARK="BULL" REMAP="BU--Bullet"Fast MOREINFO="NONE"bcpID="IN7342"Triggersbcp copying and
: You can remove any indexes and triggers on the target table and use "fast" MOREINFO="NONE"bcp. MOREINFO="NONE"inserts are not logged and cannot be recovered from a log backup created using MOREINFO="NONE"dump transaction. Also, you need to allocate resources to recreate the indexes when you are finished copying in-for a clustered index, this is typically about 2.2 times the amount of disk space needed for the data. Slow MOREINFO="NONE"bcp: You can retain the indexes and triggers on the table and use "slow" MOREINFO="NONE"bcp. Every MOREINFO="NONE"insertID="IN2243"Transaction logs, size
 is logged, which can cause the transaction log to fill very
quickly. If you are copying a large number of rows, the performance penalty and log space requirements for using slow MOREINFO="NONE"bcpID="IN15944"Recoverybcp speed and;bcp (bulk copy utility):recoverability andID="IN15945"bcp (bulk copy utility)performance issues;Performance:bcp issuesID="IN8346"Speed (SQL Server), bcp fast or slow can be severe. ID="IN8447"bcp (bulk copy utility)indexes and
ID="IN8448"Indexesbcp copying and tables withRENDERAS="SECT2"Bulk Copying Data with Indexes and TriggersID="IN6649"Tables without indexes, bcp andREMAP="BO--Body"For copying data in, MOREINFO="NONE"bcp is fastest if your database table has no indexes or triggers, because "fast" MOREINFO="NONE"bcp does not log data inserts in the transaction log. REMAP="BO--Body"When you copy into a table that has indexes or triggers, MOREINFO="NONE"bcp automatically uses a slower version, which logs data inserts in the transaction log. This can cause the transaction log to become very large, but dumping the log to a backup device with MOREINFO="NONE"dump transactionID="IN9950"dump database commandbcp and
ID="IN11951"bcp (bulk copy utility)dump database command and
ID="IN12052"dump transaction commandwith truncate_only option
 assures that the database is fully recoverable in the event of a failure. Also, after backing up your database with MOREINFO="NONE"dump databaseID="IN12253"bcp (bulk copy utility)dump transaction command and
ID="IN10254"dump transaction commandbcp and
, you can truncate the transaction log with ID="IN12155"with truncate_only option, dump transaction command
MOREINFO="NONE"dump transaction with truncate_only. ID="IN8456"bcp (bulk copy utility)triggers and data copyingREMAP="BO--Body"MOREINFO="NONE"bcp does not fire the triggers on the target table, if any exist.ID="IN12557"bcp (bulk copy utility)select into/bulkcopy option andTo allow any user to copy in data using the fast version of MOREINFO="NONE"bcpID="IN10958"bcp (bulk copy utility)sp_dboption andID="IN10759"sp_dboption system procedure, and bcp, a System Administrator or the Database Owner must first use the MOREINFO="NONE"sp_dboptionID="IN11560"select into/bulkcopy database option, and bcp
 system procedure to set the MOREINFO="NONE"select MOREINFO="NONE"into/bulkcopy option to MOREINFO="NONE"on for the database containing the target table(s). If the option is not set to MOREINFO="NONE"on and a user tries to copy data into a table that does not have indexes, SQL Server generates an error message. REMAP="BO--Body"You don't need to set this database option to copy data out or to run MOREINFO="NONE"bcp on a table that has indexes or triggers. Tables with indexes or triggers are always copied with the slower version, and all inserts are logged. ID="IN2161"Unlogged transactions
ID="IN11262"dump transaction commanddump database and
ID="IN11263"dump database commanddump transaction andREMAP="BO--Body"If you have made unlogged data inserts with fast MOREINFO="NONE"bcp, you cannot dump the transaction log because changes are not in the log and, therefore, are not recoverable from such a dump. In this situation, issuing MOREINFO="NONE"dump transaction produces an error message instructing you to use MOREINFO="NONE"dump database instead. This restriction remains in force until a MOREINFO="NONE"dump database successfully completes.REMAP="BO--Body"The following table shows which version of MOREINFO="NONE"bcp is used when copying in, the necessary settings for the MOREINFO="NONE"select MOREINFO="NONE"into/bulkcopy option, and whether the transaction log is kept and can be dumped.TOCENTRY="1"LBL="Table 2-1: "Fast and Slow bcp with select into/bulkcopyCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="15*"COLNAME="C2" COLWIDTH="10*"COLNAME="C3" COLWIDTH="10*"VALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"select into/bulkcopy COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"on COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"off COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"fast MOREINFO="NONE"bcp(no indexes or triggers on 
target table)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction 
prohibitedCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"bcpprohibitedCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"slow MOREINFO="NONE"bcp(one or more indexes or 
triggers)COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction prohibitedCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OK
MOREINFO="NONE"dump transaction 
OKREMAP="BO--Body"By default, the MOREINFO="NONE"select into/bulkcopy option is MOREINFO="NONE"off in newly created databases. To change the default setting, turn this option on in the MOREINFO="NONE"model database. ID="IN10564"Dropping indexes before copying data;Indexesdropping before using bcpREMAP="BO--Body"If you are copying a very large number of rows, it may be faster to drop all the indexes and triggers beforehand with MOREINFO="NONE"drop index and MOREINFO="NONE"drop triggerID="IN6565"Space allocationbcp steps and
ID="IN6566"Space allocationbcp steps and
, set the database option, copy the data into the table, recreate the indexes and triggers, and then dump the database. Remember to allocate 2.2 times the amount of space needed for the data to reconstruct a clustered index. If you don't have enough space to sort the data and build the index(es), use "slow"ID="IN8367"bcp (bulk copy utility)fast versionID="IN8368"bcp (bulk copy utility)slow versionID="IN8269"Indexesbcp copying and tables withID="IN8270"bcp (bulk copy utility)indexes and
ID="IN7171"Triggersbcp copying and
MOREINFO="NONE"bcp.ID="IN2772"Copy insteps
ID="IN8873"bcp (bulk copy utility)copying data inRENDERAS="SECT2"Steps for Copying Data   REMAP="BO--Body"LINKEND="X99672"Table 2-2 summarizes the steps for copying data into SQL Server. PGWIDE=1 TOCENTRY="1"LBL="Table 2-2: " ID="X99672"Steps for Copying DataCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="12*"COLNAME="C2" COLWIDTH="7*"COLNAME="C3" COLWIDTH="7*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"StepsCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NotesCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Who Can Do ItVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Use MOREINFO="NONE"sp_dboption to set MOREINFO="NONE"select into/bulkcopy to 
true, and then run MOREINFO="NONE"checkpoint in the database 
that was changedCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"System AdministratorCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Drop the indexes on the tableCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Check to be sure that you 
have enough space to 
recreate them!COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Table ownerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Be sure that you have MOREINFO="NONE"insert permission on the 
tableCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Granted by the table 
ownerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Perform the copy with MOREINFO="NONE"bcpCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Any user with MOREINFO="NONE"insert 
permissionCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Recreate the indexesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Reset MOREINFO="NONE"sp_dboption, if desired, and run 
MOREINFO="NONE"checkpoint in the database that was changedCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"System Administrator 
or Database OwnerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Use MOREINFO="NONE"dump database to back up the newly 
inserted dataCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"System Administrator, 
Operator, or Database 
OwnerCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Run stored procedures or queries to check to 
see if any of the newly loaded data violates 
rulesCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Table owner or stored 
procedure ownerRENDERAS="SECT1"Using the bcp OptionsREMAP="BO--Body"See the LINKEND="X12769""bcp"
 reference page in Chapter 1 for MOREINFO="NONE"bcp's syntax and a full discussion of the available options. The following sections detail some of the more complex options.RENDERAS="SECT2"Using the Default Formats REMAP="BO--Body"ID="IN6774"Filesdata transfer format (bcp)
MOREINFO="NONE"bcp provides two command line options that create files with frequently used default formats. These options provide the easiest way to copy data in and out of SQL Server. TheMOREINFO="NONE" -nID="IN12075"Native file format, bcp;Filesnative format in bcp
ID="IN4576"Operating systemsfile format (native format)
 option uses "native" (or operating system) formats. The MOREINFO="NONE"-cID="IN6377"Character format files (bcp)
ID="IN9178"char datatype, and bcp
 option uses "character" (char datatype) for all columns, providing tabs between fields on a row, and a newline at the end of each row. REMAP="BO--Body"If you are using the native or character options, MOREINFO="NONE"bcpID="IN7479"bcp (bulk copy utility)non-interactive
 operates non-interactively and does not ask you for any information except your SQL Server password. ID="IN9380"bcp (bulk copy utility)native format optionRENDERAS="SECT3"Native Format REMAP="BO--Body"TheMOREINFO="NONE" -n option creates files using "native" (operating system-specific) formats. Native format usually creates a more compact operating system file. For example, the following commands copy the MOREINFO="NONE"publishers table to the file called MOREINFO="NONE"pub_out, using native data format: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_out -n REMAP="BO--Body"Here are the contents of MOREINFO="NONE"pub_out: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"0736^MNew Age Books^FBoston^BMA0877^PBinnet & Hardley^J
Washington^BDC1389^TAlgodata Infosystems^HBerkeley^BCAREMAP="BO--Body"MOREINFO="NONE"bcp prefixed each field (except the pub_id, which is a char(4)ID="IN9081"ASCII format, bcp andID="IN7282"bcp (bulk copy utility)field lengthsID="IN9183"char datatype, and bcpID="IN7584"bcp (bulk copy utility)character formatdatatype) with an ASCII character equivalent to the length of the data in the field. For example, "New Age Books" is 13 characters, and ^M (Ctrl-M) is ASCII 13. All of the data in this table is char or varcharID="IN9185"Numeric datatypesoperating system format for;Operating systems:numeric datatype formatting
 data, so it is human-readable. In a table with numeric data, MOREINFO="NONE"bcpID="IN9186"bcp (bulk copy utility)native format option
 writes the information to the file in the operating system's data representation format and it may not be human-readable. Be careful copying native format data from different versions of SQL Servers because they do not always have the same datatypes. ID="IN7587"bcp (bulk copy utility)character formatRENDERAS="SECT3"Character Format REMAP="BO--Body"Character format uses "character" (the charID="IN6688"Newline terminator (\n), bcp
 datatype) for all columns. It inserts tabs between fields on each row and a newline terminator at the end of each row. REMAP="BO--Body"For example, the following commands copy the MOREINFO="NONE"publishers file out in character format: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_out -c REMAP="BO--Body"This is the output: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"0736    New Age Books            Boston       MA 0877    Binnet & Hardley         Washington   DC 1389    Algodata Infosystems     Berkeley     CA ID="IN6189"Terminators (bcp)changingRENDERAS="SECT3"Changing Terminators for Character Format REMAP="BO--Body"The row terminator is the field terminator of the last field in the table or file.REMAP="BO--Body"Use the ID="IN11390"Field terminators, bcp;Row terminators, bcp
MOREINFO="NONE"-t ID="IN5791"Field terminators, bcp
ID="IN7692"bcp (bulk copy utility)field terminators
field_terminator and MOREINFO="NONE"-r ID="IN7493"bcp (bulk copy utility)row terminators
row_terminator command line options with the character format option to change the terminators. The following example uses the comma as the field terminator and Return (\r) as the row terminator (remember to "escape" the backslash if necessary for your operating system command shell): REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_out -c -t , -r \\rREMAP="BO--Body"This produces: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"0736, New Age Books, Boston, MA 0877, Binnet & Hardley, Washington, DC 1389, Algodata Infosystems, Berkeley, CA REMAP="BO--Body"You can also use theMOREINFO="NONE" -t and MOREINFO="NONE"-r options to change the default terminators without the characterID="IN7394"Terminators (bcp)changing
 option. RENDERAS="SECT1"Changing the Defaults: Interactive bcp   REMAP="BO--Body"If you do not use the native or character options, MOREINFO="NONE"bcpID="IN6099"Promptsbcp
ID="IN85100"Datatypesbcp file storage types for
 enters interactive mode and prompts you for the storage type, prefix length, and terminator for each column of data to copy. For fields that are to be stored as char or binary, MOREINFO="NONE"bcp also prompts for a field length. REMAP="BO--Body"The default values for these four prompts produce the same results as using the MOREINFO="NONE"-nID="IN90101"bcp (bulk copy utility)copying data for other software
 option, and provide a simple means for copying data out of a database for later reloading into SQL Server. If you are copying data to or from SQL Server for use with other programs, base your answers to the prompts on the format that the other software requires. REMAP="BO--Body"Your responses to these four prompts provide an extremely flexible system that allows you to read files from other software or to create a file that requires little or no editing to conform to many other data formats. The following sections discuss these prompts and the way they interact to affect the data. Remember that the default responses to these prompts provide the fastest and easiest method to copy data to and from SQL Server. Other responses create custom data formats. RENDERAS="SECT2"File Storage Type   REMAP="BO--Body"The file storage type describes how to store the data in the file. You can copy data into a file either as its database table type, as a character string, or as any datatype for which implicit conversion is supported. User-defined datatypes are copied as their base types. REMAP="BO--Body"LINKEND="X29350"Table 2-3 shows the storage types that can be used with MOREINFO="NONE"bcp, the defaults for each SQL Server datatype, and the legal abbreviations. For the most compact storage, use the default value; for character files, use char. In the table, brackets ([]) indicate that you may use the initial character or the beginning characters of the word; for example, for bit you can use b, bi, or bit. timestamp data is treated as binary(8). The date storage type is the SQL Server internal storage format of datetime, not the host operating system format of the date.TOCENTRY="1"LBL="Table 2-3: " ID="X29350"File Storage Datatypes for bcpCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="8*"COLNAME="C2" COLWIDTH="9*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Table DatatypeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Storage TypeVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"charCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"c[har]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"varcharCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"c[har]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"textCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"T[ext]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"binaryCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"xCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"varbinaryCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"xCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"imageCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"I[mage]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"intCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"i[nt]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallintCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"s[mallint]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"tinyintCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"t[inyint]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"floatCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"f[loat]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"bitCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"b[it]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"moneyCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"m[oney]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"datetimeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"d[atetime]COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"timestampCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"xCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smalldatetimeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"DCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"realCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"rCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallmoneyCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"numericCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"nCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"decimalCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"eREMAP="BO--Body"To see this list while using MOREINFO="NONE"bcp interactively, type a question mark in response to the "Enter the file storage type" prompt. REMAP="BO--Body"The suggested values that appear in the prompts are the defaults. Remember that your response determines how the data is stored in the output file; you need not indicate the column's type in the database. REMAP="BO--Body"MOREINFO="NONE"bcpID="IN34107"Implicit conversion (of datatypes)
 fails if you enter a type that is not either implicitly convertible or char. For example, you may not be able to use smallint for int data (you may get overflow errors), but you can use int for smallint. ID="IN104108"Non-character datatypes, operating system format for;Operating systemsnon-character datatype formattingREMAP="BO--Body"When storing non-character datatypes as their database types, MOREINFO="NONE"bcpID="IN83109"Datatypesbcp file storage types forID="IN88110"bcp (bulk copy utility)file storage typeID="IN82111"File storage type (datatype in bcp)ID="IN79112"Filesdata transfer format (bcp) writes the data to the file in the operating system's data representation format, rather than in human-readable form. ID="IN73113"Prefix length, bcp field
ID="IN86114"bcp (bulk copy utility)prefix lengthRENDERAS="SECT2"Prefix Length   REMAP="BO--Body"By default, MOREINFO="NONE"bcp precedes each field that has a variable storage length with a string of one or more characters indicating the length of the field. This provides the most compact file storage. The default values in the prompts indicate the most efficient prefix length. REMAP="BO--Body"For fixed-length fields, the prefix length should be 0. REMAP="BO--Body"For fields of 255 characters or less, the default prefix length is 1. ID="IN21115"Columnstext or image
For ID="IN100116"text datatype, copying with bcp
textID="IN101117"image datatype, copying with bcp
 or imageID="IN11118"Binary data
 datatypes, the default prefix length is 4. When binary and varbinary datatypes are being converted to char storage types, the default prefix length is 2, since each byte of table data requires 2 bytes of storage. Any data column that can contain null values is considered variable length for MOREINFO="NONE"bcp purposes. This includes columns with integer datatypes that might ordinarily be considered fixed length. Use a length prefix (other than 0) or terminator to denote the length of each row's data. ID="IN34119"Columnsfixed- and variable-lengthID="IN74120"bcp (bulk copy utility)null values andID="IN54121"Null valuesbcp andREMAP="BO--Body"To store data with no prefix before its column, use a prefix length of 0. MOREINFO="NONE"bcp ID="IN65122"Padding, data, and bcp copying
ID="IN17123"Spaces, character
pads each stored field with spaces to the full length specified at the next prompt, "length," unless you supply a terminator. ID="IN34124"Nonprintable characters, host file
ID="IN84125"bcp (bulk copy utility)prefix length
ID="IN71126"Prefix length, bcp fieldREMAP="BO--Body"Note that since length prefixes consist of "native" format integers, they will cause the resulting host file to contain non-printable characters. This causes problems if you try to print out the host file, or try to transmit it using a communications program that cannot handle characters that are not human-readable. ID="IN74127"Storage lengths, bcp file
ID="IN70128"Length of field (bcp)
ID="IN36129"Filesoperating system
ID="IN76130"Datatypesbcp field lengths
ID="IN87131"bcp (bulk copy utility)storage lengthRENDERAS="SECT2"Storage Length   "Length" and "storage length" in this discussion always refer to the operating system file, not to SQL Server field lengths. REMAP="BO--Body"In almost all cases, accept the MOREINFO="NONE"bcp default value for the storage length while copying data out. If you are making a file to reload into SQL Server, the default prefixes and length keep the storage space needed to a minimum. If you are creating a human-readable file, use the default length so that you won't truncate the data or create overflow errors that cause MOREINFO="NONE"bcp to fail. REMAP="BO--Body"It is possible to change the default length by supplying another value. If you are copying character data in from other software, carefully examine the source file before choosing length values. ID="IN48132"Character set conversion from non-character data
ID="IN26133"Columnsdatatype sizes andREMAP="BO--Body"If the storage type is non-character, MOREINFO="NONE"bcp stores the data in the operating system's native data representation and does not prompt for a length. ID="IN63134"Defaultsbcp data conversionREMAP="BO--Body"When MOREINFO="NONE"bcp converts non-character data to character storage, it suggests a default field length large enough to store the data without truncating datetimeID="IN72135"bcp (bulk copy utility)field lengths
 data or causing overflow of numeric data. The default lengths are the number of bytes needed to display the longest value for the SQL Server datatype. 
LINKEND="X28821"Table 2-4
 lists the default field lengths. TOCENTRY="1"LBL="Table 2-4: " ID="X28821"Default Field Lengths in bcpCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="9*"COLNAME="C2" COLWIDTH="7*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"DatatypeCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Default SizeVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"int COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"12 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallint COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"6 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"tinyint COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"3 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"decimal COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"33 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"numeric COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"33 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"float COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"25 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"money COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"24 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"datetime COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"26 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"bit COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"1 byteCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"real COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"25 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallmoney COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"24 bytesCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smalldatetime COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"26 bytesREMAP="BO--Body"If you specify a field length that is too short for numeric data when copying data out, MOREINFO="NONE"bcp prints an overflow message and does not copy the data. REMAP="BO--Body"The default length for binary and varbinary fields is twice the length defined for the column, since each byte of the field requires 2 bytes of storage. REMAP="BO--Body"If you accept the default storage length, the actual amount of storage space allocated depends on whether or not you specify a prefix length and/or terminators: ID="IN59136"Prefix length, bcp fieldMARK="BULL" REMAP="BU--Bullet"If you specify a prefix length of 1, 2, or 4, MOREINFO="NONE"bcp uses a storage space of the actual length of the data plus the length of the prefix plus any terminators. If you specify a prefix length of 0 and no terminator, MOREINFO="NONE"bcp allocates the maximum amount of space shown in the prompt, which is the maximum space that may be needed for the datatype in question. In other words, MOREINFO="NONE"bcp treats the field as if it were fixed length to determine where one field ends and the next begins. For example, if the field is defined as varchar(30), MOREINFO="NONE"bcp uses 30 characters for each value, even if some of the values are only one character long. MOREINFO="NONE"bcpID="IN65137"Padding, data, and bcp copying
ID="IN91138"char datatype, and bcp
 does not know how large any one data value will be before copying all the data, so it always pads char datatypes to their full specified length.RENDERAS="SECT2"Field and Row Terminators   REMAP="BO--Body"A terminator can be used to mark the end of a column or row, separating one from the next. The default is no terminator. Field terminators separate table columns; the row terminator is the field terminator of the last field in the table or file.ID="IN79148"Character format files (bcp)terminators forREMAP="BO--Body"Terminators are very useful for dealing with character data because you can choose human-readable terminators. The MOREINFO="NONE"bcp character option, which uses tabs between each column with a newline at the end of each row, is an example of using terminators that enhance the readability of a data file. ID="IN37149"Application programscopying data forREMAP="BO--Body"When you prepare data for use with other programs, and when you want to use MOREINFO="NONE"bcpID="IN35150"Tabular data, copying
 to prepare tabular data, supply your own terminators. The available terminators are: ID="IN77151"Characters, field and row terminator (bcp)
ID="IN66152"Symbols, field terminator (bcp)MARK="BULL" REMAP="BU--Bullet"Tabs, indicated by \t ID="IN66153"Newline terminator (\n), bcpNewlines, indicated by \n ID="IN86154"Carriage return (\r) data field terminator (bcp)Carriage returns, indicated by \r ID="IN159155"\ (backslash)data field terminator (bcp);Backslash (\):data field terminator (bcp)Backslash, indicated by \ ID="IN62156"Invisible terminators (bcp)Null terminators (no visible terminator), indicated by \0 Any printable character (*, A, t, |, and so forth) Strings of up to 10 printable characters, including some or all of the terminators listed earlier (for example, **\t** end, !!!!!!!!!!, or \t--\n). Control characters (ASCII 0-25) are not printable. ID="IN66157"Symbols, field terminator (bcp)ID="IN65158"Tab data field terminator, bcpREMAP="BO--Body"Choose terminators with patterns that do not appear in any of the data. For example, using a tab terminator with a string of data that contains a tab creates an ambiguity: which tab represents the end of the string? MOREINFO="NONE"bcp always looks for the first possible terminator, which in this case would be incorrect, since the first tab it would encounter would be the one that is part of the data string. ID="IN45159"Operating systemsfile format (native format)
ID="IN79160"bcp (bulk copy utility)native format option
ID="IN61161"Native data format and bcpREMAP="BO--Body"Data in native format can also conflict with terminators. Given a column that contains a 4-byte integer in native format, if the values of these integers are not strictly limited, it will be impossible to choose a terminator that is guaranteed not to appear inside the data. Use MOREINFO="NONE"bcp's native format option for data in native format. ID="IN62162"Terminators (bcp)invisible
ID="IN66163"Null character terminator (bcp)
ID="IN33164"Tabular data, copying
ID="IN92165"bcp (bulk copy utility)prompts and responses
ID="IN91166"bcp (bulk copy utility)defaults for promptsREMAP="BO--Body"Note that "no terminator" is different from a "null terminator," which is an invisible but real character. ID="IN77167"Characters, field and row terminator (bcp)
ID="IN76168"Columnsbcp specifications on
ID="IN67169"Defaultsbcp prompts
ID="IN86170"bcp (bulk copy utility)row terminators
ID="IN58171"Promptsbcp
ID="IN67172"Row terminators, bcp
ID="IN69173"Field terminators, bcp
ID="IN88174"bcp (bulk copy utility)field terminators
ID="IN34175"Filesoperating system
ID="IN67176"Format files (bcp)RENDERAS="SECT1"Using Format Files   REMAP="BO--Body"After gathering information about each field in the table, MOREINFO="NONE"bcpID="IN61177"Savingformat files (bcp) ID="IN60178"Format files (bcp)savingID="IN65179"Datatypesbcp format files forID="IN71180"bcp (bulk copy utility)format files asks if you want to save a format file and prompts for the file name. Use this format file to copy the data back into SQL Server, or to copy data out from the table at another time. When you copy data in or out using an existing format file,MOREINFO="NONE" bcp does not prompt for information; the format file provides the information needed. ID="IN75181"Format files (bcp)exampleREMAP="BO--Body"The following diagram illustrates the format of the MOREINFO="NONE"bcp format files. It shows the MOREINFO="NONE"publishers table from the MOREINFO="NONE"pubs2 database, with all the host file columns in character format, no prefix, the default data length, a newline character at the end of the final column of a row, and tabs as terminators for all other columns.FLOAT="0"FORMAT="UNKNOWN" FILEREF="fig1_4.xwd"LBL="Figure 2-1: " ID="X10514"A bcp Format FileREMAP="BO--Body"Format file columns are separated by tabs, except that the host file datatype and the prefix length are separated by a space.REMAP="BO--Body"The MOREINFO="NONE"bcpID="IN73182"Format files (bcp)example
 version is always the first line of the file, followed by the number of columns on the second line. These are followed by one line for each column in the table.RENDERAS="SECT2"Elements of the Format FileREMAP="BO--Body"The format file consists of the version, number of columns, and a set of columns. These elements are explained in the following sections.ID="IN141183"Version numberbcp, in format file;Format files (bcp):version number inRENDERAS="SECT2"Version REMAP="BO--Body"The version is the MOREINFO="NONE"bcp version number. This is a literal string without quotation marks. In LINKEND="X10514"Figure 2-1
 the version is 10.0.RENDERAS="SECT2"Number of Columns REMAP="BO--Body"Number of columns refers to the number of records in the format file, not including lines 1 and 2. Each column in the host table has one line.RENDERAS="SECT2"Columns in the Format FileREMAP="BO--Body"The columns in the format file are:RENDERAS="SECT3"Host File Column Order REMAP="BO--Body"The host file column order is the sequential number of the field in the host data file, starting with 1.RENDERAS="SECT3"Host File DatatypeREMAP="BO--Body"ID="IN74184"Storage format of data;SYB storage types
ID="IN63185"Datatypesstorage (SYB types)
The host file datatype refers to the storage format of the field in the host data file, not the corresponding database table column datatype. Valid storage formats are:TOCENTRY="1"LBL="Table 2-5: "Host File Datatype Storage FormatCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="5*"COLNAME="C2" COLWIDTH="6*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Storage FormatCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SQL Server DatatypeVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBCHARCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"char/varchar (ASCII)COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBTEXTCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"textCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBBINARYCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"binaryCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBIMAGECOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"imageCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBINT1COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"tinyintCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBINT2COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallintCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBINT4COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"intCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBFLT8COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"floatCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBREALCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"real  COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBBITCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"bitCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBMONEYCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"moneyCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBMONEY4COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smallmoney COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBDATETIMECOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"datetimeCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"SYBDATETIME4COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"smalldatetime RENDERAS="SECT3"Prefix LengthREMAP="BO--Body"Prefix length indicates the number of bytes in the field length prefix. The length prefix is a 0-, 1-, 2-, or 4-byte unsigned integer value embedded in the host data file specifying the actual length of data contained in the field. Some fields may have a length prefix while others do not. REMAP="BO--Body"LINKEND="X81775"Table 2-6 shows the allowable prefix length values. TOCENTRY="1"LBL="Table 2-6: " ID="X81775"Allowable Prefix Length ValuesCOLS="2" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="5*"COLNAME="C2" COLWIDTH="15*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"No. of 
Bytes COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"RangeVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"0COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"NO PREFIXCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"1COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"28-1; 0-255COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"2COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"216-1; 0-65535COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"4COLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"232 -1; 0-4,294,967,295RENDERAS="SECT3"Host File Data LengthREMAP="BO--Body"Host file data length refers to the maximum number of bytes to copy for the field. MOREINFO="NONE"bcp uses either the maximum field length, the length prefix (if any), or the field terminator string (if any) to decide how much data to copy in or out. If more than one method of field length specification is given, MOREINFO="NONE"bcp chooses the one that copies the least amount of data.RENDERAS="SECT3"TerminatorREMAP="BO--Body"The terminator can be up to 30 characters enclosed in quotation marks (" "). The terminator designates the end of data for the host data file field.RENDERAS="SECT3"Server Column OrderREMAP="BO--Body"The host file column order and the server column order together map host data file fields to the database table columns. The value in this field must equal either the MOREINFO="NONE"colid of the table column into which the host data file column is to be loaded, or 0 if the host file field will not be loaded into any table column.RENDERAS="SECT3"Server Column NameREMAP="BO--Body"The server column nameID="IN65186"Format files (bcp)
 is the name of the database table column into which this field is loaded. This is optional.RENDERAS="SECT1"Examples: Copying out Data InteractivelyID="IN34190"Database management systems, other
ID="IN27191"Copy outfor other software
ID="IN90192"bcp (bulk copy utility)copying data for other software
ID="IN38193"Application programscopying data fromREMAP="BO--Body"By changing the default values of the prompts to MOREINFO="NONE"bcp, you can prepare data for use with other software. To create a human-readable file, respond to the MOREINFO="NONE"bcp prompts as follows: MARK="BULL" REMAP="BU--Bullet"char storage type0 prefix lengthDefault field lengthTerminator depends on the software you plan to use. Choose between delimited fields or fixed length fields. Always use "\n", the newline character, to terminate the last field.ID="IN19194"Fixed-length fieldsREMAP="UN--Under"For fixed-length fields, use no terminator. Each field has a fixed length, with spaces to pad the fields. Adjacent fields where the data completely fills the first field seem to run together, since there are no field separators on each line of output. See the example below.ID="IN22195"Comma-delimited output
ID="IN14196"Tabular outputREMAP="UN--Under"For comma-delimited output, use a comma as the terminator for each field. To create tabular output, use the tab character, "\t". See the LINKEND="X12769""bcp"
 examples in Chapter 1.ID="IN34197"Copy out with fixed-length fieldsRENDERAS="SECT2"Copying out Data with Field Lengths ID="IN72198"bcp (bulk copy utility)SDF files and
ID="IN68199"SDF (system data format), and bcp
ID="IN67200"System data format (SDF) and bcpREMAP="BO--Body"The following example uses fixed-length fields to create output in the personal computer format called SDF or "system data format." This format can be easily read or produced by other software. REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"MOREINFO="NONE"bcp pubs2..sales out sal_out REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Password: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field stor_id [char]: Enter prefix-length of field stor_id [0]: Enter length of field stor_id [4]: Enter field terminator [none]: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field ord_num [char]: Enter prefix-length of field ord_num [1]: 0Enter length of field ord_num [20]: Enter field terminator [none]: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field date [datetime]: charEnter prefix-length of field date [1]: 0Enter length of field date [26]: Enter field terminator [none]: \nREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: sal_fmtREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Starting copy...REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"30 rows copied.Clock Time (ms.): total = 211 Avg = 7 (142.18 rows per sec.)REMAP="BO--Body"The contents of sal_out are as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"5023AB-123-DEF-425-1Z3  Oct 31 1985 12:00:00:000AM5023AB-872-DEF-732-2Z1  Nov  6 1985 12:00:00:000AM5023AX-532-FED-452-2Z7  Dec  1 1990 12:00:00:000AM5023BS-345-DSE-860-1F2  Dec 12 1986 12:00:00:000AM5023GH-542-NAD-713-9F9  Mar 15 1987 12:00:00:000AM5023NF-123-ADS-642-9G3  Jul 18 1987 12:00:00:000AM5023XS-135-DER-432-8J2  Mar 21 1991 12:00:00:000AM5023ZA-000-ASD-324-4D1  Jul 27 1988 12:00:00:000AM5023ZD-123-DFG-752-9G8  Mar 21 1991 12:00:00:000AM5023ZS-645-CAT-415-1B2  Mar 21 1991 12:00:00:000AM5023ZZ-999-ZZZ-999-0A0  Mar 21 1991 12:00:00:000AM6380234518              Sep 30 1987 12:00:00:000AM6380342157              Dec 13 1985 12:00:00:000AM6380356921              Feb 17 1991 12:00:00:000AM7066BA27618             Oct 12 1985 12:00:00:000AM7066BA52498             Oct 27 1987 12:00:00:000AM7066BA71224             Aug  5 1988 12:00:00:000AM7067NB-1.142            Jan  2 1987 12:00:00:000AM7067NB-3.142            Jun 13 1990 12:00:00:000AM7131Asoap132            Nov 16 1986 12:00:00:000AM7131Asoap432            Dec 20 1990 12:00:00:000AM7131Fsoap867            Sep  8 1987 12:00:00:000AM7896124152              Aug 14 1986 12:00:00:000AM7896234518              Feb 14 1991 12:00:00:000AM804212-F-9              Jul 13 1986 12:00:00:000AM804213-E-7              May 23 1989 12:00:00:000AM804213-J-9              Jan 13 1988 12:00:00:000AM804255-V-7              Mar 20 1991 12:00:00:000AM804291-A-7              Mar 20 1991 12:00:00:000AM804291-V-7              Mar 20 1991 12:00:00:000AMREMAP="BO--Body"The contents of the format file sal_fmt are as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"10.031     SYBCHAR 0     4      ""     1     stor_id2     SYBCHAR 0     20     ""     2     ord_num3     SYBCHAR 0     26     ""     3     dateID="IN49201"Copy outwith delimiters;Delimiters:copy out withRENDERAS="SECT2"Copying out Data with Delimiters REMAP="BO--Body"In the following examples, MOREINFO="NONE"bcp interactively copies data from the MOREINFO="NONE"publishers table to a file. ID="IN22202"Comma-delimited outputREMAP="BO--Body"The first example creates an output file with commas between all fields in a row and a newline at the end of each row. This example creates a format file (MOREINFO="NONE"pub_fmt) which you can later use to copy the same or similar data back into SQL Server. REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_outREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Password: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field pub_id [char]: Enter prefix length of field pub_id [0]: Enter length of field pub_id [4]: Enter field terminator [none]:, REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field pub_name [char]: Enter prefix length of field pub_name [1]: 0 Enter length of field pub_name [40]: Enter field terminator [none]:, REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field city [char]: Enter prefix length of field city [1]:0 Enter length of field city [20]: Enter field terminator [none]:, REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field state [char]: Enter prefix length of field state [1]:0 Enter length of field state [2]: Enter field terminator [none]:\n REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Do you want to save this format information in a file? [Y/n] y Host filename [bcp.fmt]: pub_fmt REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Starting copy... REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"3 rows copied. Clock Time (ms.): total = 0   Avg = 0  (3.00 rows per sec.) REMAP="BO--Body"These are the results in pub_out: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"0736,New Age Books,Boston,MA 0877,Binnet & Hardley,Washington,DC 1389,Algodata Infosystems,Berkeley,CA REMAP="BO--Body"The contents of pub_fmt are as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"10.041   SYBCHAR  0  4   ","   1   pub_id2   SYBCHAR  0  40  ","   2   pub_name3   SYBCHAR  0  20  ","   3   city4   SYBCHAR  0  2   "\n"  4   stateID="IN14203"Tabular outputREMAP="BO--Body"Similarly, the following example creates tab-delimited output from the table pubs2..MOREINFO="NONE"publishers in the file pub_out.REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"bcp pubs2..publishers out pub_outREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Password: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field pub_id [char]: Enter prefix-length of field pub_id [0]: 0 Enter length of field pub_id [4]: Enter field terminator [none]: \t REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field pub_name [char]: Enter prefix-length of field pub_name [1]: 0 Enter length of field pub_name [40]: Enter field terminator [none]: \t REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field city [char]: Enter prefix-length of field city [1]: 0 Enter length of field city [20]: Enter field terminator [none]: \t REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field state [char]: Enter prefix-length of field state [1]: 0 Enter length of field state [2]: Enter field terminator [none]: \n REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: pub_fmtREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Stating copy...REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"3 rows copied.Clock Time (ms.): total = 120 Avg = 40 (25.00 rows per sec.)REMAP="BO--Body"The contents of pub_out are as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"0736     New Age Books            Boston      MA0877     Binnet & Hardley         Washington  DC1389     Algodata Infosystems     Berkeley    CAREMAP="BO--Body"The contents of the format file pub_fmt are as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"10.041   SYBCHAR  0  4   "\t"   1 pub_id2   SYBCHAR  0  40  "\t"   2 pub_name3   SYBCHAR  0  20  "\t"   3 city4   SYBCHAR  0  2   "\n"   4 stateID="IN87204"bcp (bulk copy utility)copying data out
ID="IN20205"Copy out
ID="IN79206"Interactive bcpcopying data out
ID="IN110207"Copy in;bcp (bulk copy utility)copying data inID="X41596" RENDERAS="SECT1"Examples: Copying in Data Interactively REMAP="BO--Body"To copy data successfully into a table from a file, you must know what the terminators in the file are, or what the field lengths are, and specify them when you use MOREINFO="NONE"bcp. The following examples show how to copy data (either with fixed field lengths or with delimiters) in using MOREINFO="NONE"bcp, with or without a format file.ID="IN49208"Field lengths, copy in;Copy inwith field lengthsRENDERAS="SECT2"Copying in Data with Field LengthsREMAP="BO--Body"In the following example, MOREINFO="NONE"bcp copies data from the file MOREINFO="NONE"salesnew into the table pubs2..MOREINFO="NONE"sales. In the MOREINFO="NONE"salesnew file are three fields: the first is 4 characters long, the second is 20, and the third is 26 characters long. Each row ends with a newline (\n), as follows: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"5023ZS-731-AAB-780-2B9  May 24 1993 12:00:00:000AM5023XC-362-CFB-387-3Z5  May 24 1993 12:00:00:000AM6380837206              May 24 1993 12:00:00:000AM6380838441              May 24 1993 12:00:00:000AMREMAP="BO--Body"Use the following command to copy the data from MOREINFO="NONE"salesnew in interactively: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..sales in salesnewREMAP="BO--Body"The system responds as follows:REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Password: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field stor_id [char]: Enter prefix-length of field stor_id [0]: Enter length of field stor_id [4]: Enter field terminator [none]:  REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field ord_num [char]: Enter prefix-length of field ord_num [1]: 0Enter length of field ord_num [20]: Enter field terminator [none]: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Enter the file storage type of field date [datetime]: charEnter prefix-length of field date [1]: 0Enter length of field date [26]: Enter field terminator [none]: \nREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Do you want to save this
format information in a file? [Y/n] yHost filename [bcp.fmt]: salesin_fmt  Starting copy... 4 rows copied.Clock Time (ms.): total = 45    Avg = 11    (88.89 rows per sec.)REMAP="BO--Body"When you log on to SQL Server and access MOREINFO="NONE"salers, you'll see the following data from salesnew appended to the table: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"select * from sales REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC" stor_id ord_num              date                        ------- -------------------- --------------------------  5023    AB-123-DEF-425-1Z3          Oct 31 1985 12:00AM  5023    AB-872-DEF-732-2Z1          Nov  6 1985 12:00AM  5023    AX-532-FED-452-2Z7          Dec  1 1990 12:00AM  5023    BS-345-DSE-860-1F2          Dec 12 1986 12:00AM  5023    GH-542-NAD-713-9F9          Mar 15 1987 12:00AM  5023    NF-123-ADS-642-9G3          Jul 18 1987 12:00AM  5023    XC-362-CFB-387-3Z5          May 24 1993 12:00AM  5023    XS-135-DER-432-8J2          Mar 21 1991 12:00AM  5023    ZA-000-ASD-324-4D1          Jul 27 1988 12:00AM  5023    ZD-123-DFG-752-9G8          Mar 21 1991 12:00AM  5023    ZS-645-CAT-415-1B2          Mar 21 1991 12:00AM  5023    ZS-731-AAB-780-2B9          May 24 1993 12:00AM  5023    ZZ-999-ZZZ-999-0A0          Mar 21 1991 12:00AM  6380    234518                      Sep 30 1987 12:00AM  6380    342157                      Dec 13 1985 12:00AM  6380    356921                      Feb 17 1991 12:00AM  6380    837206                      May 24 1993 12:00AM  6380    838441                      May 24 1993 12:00AM  7066    BA27618                     Oct 12 1985 12:00AM  7066    BA52498                     Oct 27 1987 12:00AM  7066    BA71224                     Aug  5 1988 12:00AM  7067    NB-1.142                    Jan  2 1987 12:00AM  7067    NB-3.142                    Jun 13 1990 12:00AM  7131    Asoap132                    Nov 16 1986 12:00AM  7131    Asoap432                    Dec 20 1990 12:00AM  7131    Fsoap867                    Sep  8 1987 12:00AM  7896    124152                      Aug 14 1986 12:00AM  7896    234518                      Feb 14 1991 12:00AM  8042    12-F-9                      Jul 13 1986 12:00AM  8042    13-E-7                      May 23 1989 12:00AM  8042    13-J-9                      Jan 13 1988 12:00AM  8042    55-V-7                      Mar 20 1991 12:00AM  8042    91-A-7                      Mar 20 1991 12:00AM  8042    91-V-7                      Mar 20 1991 12:00AM  (34 rows affected) REMAP="BO--Body"Since there is a unique clustered index on the MOREINFO="NONE"stor_id and ord_num columns of MOREINFO="NONE"sales, the new rows were sorted into order. Had there been any violations of the unique index on the columns in the data being copied from the file, MOREINFO="NONE"bcpID="IN35209"Datatypescopying and compatibility
 would have discarded the entire batch that contained a violating row. (A batch size of 1 evaluates each row individually, but loads more slowly.) If the types copied in are incompatible with the database types, the entire copy fails. REMAP="BO--Body"The format file salesin_fmt is the same as the format file created when copying out the data and giving the same responses. ID="IN47210"Copy inwith delimiters;Delimiters:copy in withRENDERAS="SECT2"Copying in Data with DelimitersREMAP="BO--Body"In the following example, MOREINFO="NONE"bcp copies data from the file MOREINFO="NONE"newpubs into the table pubs2..MOREINFO="NONE"publishers. In the MOREINFO="NONE"newpubs file, each field in a row ends with a tab character (\ ) and each row ends with a newline character (\n), as follows: REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"1111    Stone Age Books          Boston       MA 2222    Harley & Davidson        Washington   DC 3333    Infodata Algosystems     Berkeley     CA REMAP="BO--Body"Since MOREINFO="NONE"newpubs contains all character data, you can use the character command line flag and specify the terminators with command line options, as follows: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in newpubs -c -t \\t -r \\n RENDERAS="SECT2"ID="IN69211"Format files (bcp)copying in with
Copying in Data with a Format FileREMAP="BO--Body"To copy data back into SQL Server using the saved format file MOREINFO="NONE"pub_fmt, use the following command: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in pub_out -f pub_fmt REMAP="BO--Body"You can use the MOREINFO="NONE"pub_fmt file to copy any data with the same format into SQL Server. If you have a similar data file with different delimiters, you can change the delimiters in the format file.REMAP="BO--Body"Similarly, you can edit the format file to reflect any changes to the field lengths, so long as all fields have the same length. For example, the file moresales contains the following:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"804213-L-9 Jan 21 1993 12:00AM804255-N-8 Mar 12 1993 12:00AM804291-T-4 Mar 23 1993 12:00AM804291-W-9 Mar 23 1993 12:00AMREMAP="BO--Body"Edit the format file sal_fmt to read as follows:REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"10.031   SYBCHAR  0  4   ""   1   stor_id2   SYBCHAR  0  7   ""   2   ord_num3   SYBCHAR  0  21  "\n" 3   dateREMAP="BO--Body"Then enter the following command:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..sales in moresales -f sal_fmtREMAP="BO--Body"The system responds as follows:REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"Starting copy... ID="IN106212"Copy in;bcp (bulk copy utility)copying data inREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"4 rows copied.Clock Time (ms.): total = 28 Avg = 7 (142.86 rows per sec.)ID="IN64213"Languages, alternatebcp with
ID="IN82214"bcp (bulk copy utility)alternate languages andRENDERAS="SECT1"Using bcp with Alternate Languages REMAP="BO--Body"SQL Server stores its data using a default character set, which varies by platform. If your terminal does not support that default character set, it may send confusing characters to MOREINFO="NONE"bcp when you respond to prompts by typing or by using host file scripts. ID="IN19215"iso_1 character set
ID="IN31216"Character setsplatform default
ID="IN20217"Character setsiso_1
ID="IN83218"bcp (bulk copy utility)non-iso_1 data files andREMAP="BO--Body"Omitting all character set options sets the character set to a default for the platform. Note that the default may not necessarily be the character set that the client is using. (See the System Administration Guide for more information about character sets and the associated flags.)ID="IN23219"Copy inbatch files and
ID="IN20220"Batch files, copy inRENDERAS="SECT1"Copy in and Batch Files ID="IN11221"Filesbatch
ID="IN75222"bcp (bulk copy utility)batch operations
ID="IN71223"Table rows. See Rows, tableREMAP="BO--Body"Batching applies only to bulk copying in; it has no effect when copying out. By default, SQL Server copies all the rows specified in one batch. SQL Server considers each batch to be a separate MOREINFO="NONE"bcpID="IN70224"Roll back processes, bcp insert and
 operation. ID="IN35225"Rows, tablebulk copying and failed
MOREINFO="NONE"bcp copies each batch in a single MOREINFO="NONE"insert transaction, and if the server rejects any row in the batch, the entire MOREINFO="NONE"insert rolls back. MOREINFO="NONE"bcp then continues to the next batch. Only fatal errors roll back the MOREINFO="NONE"insert.ID="IN56226"Batchsize option, bcpREMAP="BO--Body"You can break large input files into smaller units for better recoverability. For example, if 300,000 rows are bulk copied in with a batch size of 100,000 rows, and there is a fatal error after row 200,000, the first two batches-200,000 rows-will have been successfully copied into SQL Server. If batching had not been used, no rows would have been copied into SQL Server. REMAP="BO--Body"When using slow MOREINFO="NONE"bcp, the log entry for the transaction is available for truncation after the batch completes. If you copy in to a database that has MOREINFO="NONE"trunc log on chkpt set MOREINFO="NONE"on, the next automatic checkpoint removes the log entries for completed batches. This breaks up large MOREINFO="NONE"bcp operations and keeps the log from filling.REMAP="BO--Body"You can even set the batchsize to 1, which causes only the defective row to be rejected. This allows you to identify exactly which row failed. Since MOREINFO="NONE"bcp sends rows to SQL Server in batches, it cannot save copies of rows that SQL Server rejects in an error file (for example, when SQL Server encounters a duplicate row for a table that has a unique index). SQL Server generates error messages on a batch-by-batch basis, instead of row-by-row, and rejects each entire batch in which it finds an error. Error messages appear on your terminal.ID="IN92227"Sizepacket size;Packet size, network:specifying with bcpRENDERAS="SECT1"Specifying a Network Packet SizeID="IN37228"Performancebulk copy and packet sizeREMAP="BO--Body"You may want to use larger network packet sizes than the defaults to improve the performance of large bulk copy operations. The MOREINFO="NONE"-A size option specifies the network packet size to use for this MOREINFO="NONE"bcp session. sizeID="IN85229"default network packet size configuration variable
 must be between the values of the MOREINFO="NONE"default network packet size and ID="IN85230"maximum network packet size configuration variable
MOREINFO="NONE"maximum network packet size configuration variablesMOREINFO="NONE", and it must be a multiple of 512. The new packet size is in effect for that MOREINFO="NONE"bcp session only.REMAP="BO--Body"For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..authors out -A 2048 -T 40960REMAP="BO--Body"specifies that SQL Server send 40K of text or image data using a packet size of 2048 for this MOREINFO="NONE"bcp session.ID="IN96231"Copy outtext and image dataRENDERAS="SECT1"Copying out text and image DataID="IN23232"Sizetext or image data
ID="IN75233"bcp (bulk copy utility)copying data out
ID="IN114234"bcp (bulk copy utility)text data copying out
ID="IN100235"text datatype, copying with bcpREMAP="BO--Body"When you copy out text or image data, by default SQL Server copies the first 32K of data in a text or image field. The MOREINFO="NONE"-T flag allows you to specify a different value. For example, if the text field to copy out contains 40K of data, copy out all 40K with the following command:ID="IN115236"bcp (bulk copy utility)image data copying outID="IN101237"image datatype, copying with bcpREMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers out -T 40960REMAP="BO--Body"If a text or image field is larger than the given value or the default, the overflow is not sent.RENDERAS="SECT1"Copy in and Error Files REMAP="BO--Body"When you specify the MOREINFO="NONE"-e flag
with copy in, MOREINFO="NONE"bcp stores certain rows that it cannot copy into SQL Server in the specifiedID="IN15238"Errorsfiles ofID="IN23239"Copy inerror files andID="IN67240"Rows, tablebcp storage of errorID="IN70241"bcp (bulk copy utility)error filesID="IN39242"Errorssaving during copying operationsID="IN39243"Savingerrors during copyingoperationsID="IN52244"Fileserror (bcp)ID="IN60245"Errorsbcp storage of row error file. REMAP="BO--Body"The error file stores a line indicating which row failed and what error occurred, and a line which is an exact copy of the row from the host file. MOREINFO="NONE"bcp overwrites any file of the same name. If MOREINFO="NONE"bcp does not encounter any errors, it does not create the file.REMAP="BO--Body"MOREINFO="NONE"bcp in detects two types of errors: ID="IN22246"Data conversion errorsMARK="BULL" REMAP="BU--Bullet"Data conversion errorsID="IN12247"Columnsnull
ID="IN11248"Null valuesErrors in building the row, such as attempts to insert a NULL into columns that don't accept them, or illegal data formats (such as a 3-byte integer)REMAP="BO--Body"Error messages appear on your terminal. The following example loads the MOREINFO="NONE"newpubs file into the MOREINFO="NONE"publishers database, storing any error rows in the file MOREINFO="NONE"pub_err: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"bcp pubs2..publishers in newpubs -e pub_err REMAP="BO--Body"MOREINFO="NONE"bcp stores only rows when MOREINFO="NONE"bcp detects the error. Since MOREINFO="NONE"bcp sends rows to SQL Server in batches, it cannot save copies of rows that SQL Server rejects (for example, when SQL Server encounters a duplicate row for a table that has a unique index). SQL Server generates error messages on a batch-by-batch basis, instead of row-by-row, and rejects the entire batch if it finds an error. REMAP="BO--Body"It is not considered an error for SQL Server to reject duplicate rows if ID="IN150249"allow_dup_row option to create index, and bcp
MOREINFO="NONE"allow_dup_rowID="IN149250"ignore_dup_key option, create index, and bcp
 or MOREINFO="NONE"ignore_dup_keyID="IN114251"create index command, bcp and duplicate rows
 was set when a table's index was created. The copy proceeds normally, and the duplicate rows are not stored in the MOREINFO="NONE"bcp error file. ID="IN24252"Copy outerror files andRENDERAS="SECT1"Copy out and Error Files REMAP="BO--Body"When you use the MOREINFO="NONE"-e flag
with copy out, MOREINFO="NONE"bcp stores the rows that it cannot copy out in the specifiedID="IN15253"Errorsfiles ofID="IN23254"Copy inerror files andID="IN67255"Rows, tablebcp storage of errorID="IN70256"bcp (bulk copy utility)error filesID="IN39257"Errorssaving during copying operationsID="IN39258"Savingerrors during copying operationsID="IN52259"Fileserror (bcp) ID="IN60260"Errorsbcp storage of row error file. As with copy in, MOREINFO="NONE"bcp overwrites any file of the same name, and does not create the file if no errors occurred. ID="IN35261"Rows, tablebulk copying and failedREMAP="BO--Body"There are two possible cases that log rows in the error file during a copy out: ID="IN22262"Data conversion errorsMARK="BULL" REMAP="BU--Bullet"A data conversion error in one of the row's columns An I/O error in writing to the host fileREMAP="BO--Body"MOREINFO="NONE"bcp logs rows in the error file in the default character format. All data values print as characters, with tabs between the columns, and a newline at the end of each row.ID="IN73263"bcp (bulk copy utility)data integrity
ID="IN39264"Defaultscopying into tables using data
ID="IN66265"RulesRules:copying data into tablesfor copying data into tables
ID="IN37266"Triggerscopying data into tables andRENDERAS="SECT1"Data Integrity: Defaults, Rules, and Triggers   ID="IN90267"bcp (bulk copy utility)table defaults and copying dataREMAP="BO--Body"When copying data into a table, MOREINFO="NONE"bcpID="IN82268"bcp (bulk copy utility)default values for dataID="IN21269"Columnsdefault valueID="IN64270"Columnsbcp specifications onID="IN31271"Datadefault values for missing observes any defaults defined for the columns and datatypes. That is, if there is a null field in the data in a file, MOREINFO="NONE"bcp loads the default value instead during the copy. For example, here are two rows in a file to load into authors: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"409-56-7008,Bennet,Abraham,415 658-9932,6223 Bateman St.,Berkeley,CA,USA,94705 213-46-8915,Green,Marjorie,,309 63rd St. #411,Oakland,CA,USA,94618REMAP="BO--Body"Commas separate the fields; a newline separates the rows. Note that there is no phone number for Marjorie Green. If the MOREINFO="NONE"phone column of the titles table has a default of "unknown," the rows in the loaded table look like this: REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"409-56-7008,Bennet,Abraham,415 658-9932,6223 Bateman St.,Berkeley,CA,USA,94705 213-46-8915,Green,Marjorie,unknown,309 63rd St. #411,Oakland,CA,USA,94618 ID="IN77272"bcp (bulk copy utility)performance issuesREMAP="BO--Body"However, MOREINFO="NONE"bcpID="IN81273"bcp (bulk copy utility)rules and copying dataID="IN84274"bcp (bulk copy utility)triggers and data copying ignores rules and triggers in order to load data at the maximum speed. To find any rows that violate rules and triggers, copy the data into the table and run queries or stored procedures that test the rule or trigger conditions. ID="IN90275"bcp (bulk copy utility)other SQL Server facilities andRENDERAS="SECT1"Bulk Copy Distinguished from Other SQL Server Facilities   ID="IN36276"Moving data with SQL Server commandsREMAP="BO--Body"The bulk copy facilities, which copy entire tables or portions of a single table, are distinct from several other commands and options that also move data from one place to another. These commands are: ID="IN32277"Dumping compared to bulk copying
ID="IN99278"dump database commandbcp andMARK="BULL" REMAP="BU--Bullet"The SQL commands MOREINFO="NONE"dump databaseID="IN100279"load database command, bcp and
, MOREINFO="NONE"load databaseID="IN102280"dump transaction commandbcp and
, MOREINFO="NONE"dump transactionID="IN103281"load transaction command, bcp and
, and MOREINFO="NONE"load transaction. These are used for backup purposes only. Unlike the bulk copy facilities, the dump facilities create a physical image of the entire database. Data dumped with MOREINFO="NONE"dump database or MOREINFO="NONE"dump transaction can be read only by using MOREINFO="NONE"load database or MOREINFO="NONE"load transaction. (See the System Administration Guide or the SQL Server Reference
 Manual for details.) ID="IN38282"Changing data with SQL Server commandsThe data modification commands MOREINFO="NONE"insert, MOREINFO="NONE"update, and MOREINFO="NONE"delete. Use these to add new rows, change existing rows, or remove rows in a table or view. (See the SQL Server Reference Manual or Transact-SQL User's Guide
 for details.)ID="IN92283"insert commandbcp and
ID="IN112284"bcp (bulk copy utility)insert command and
ID="IN74285"insert commandcompared to bulk copyingREMAP="UN--Under"The MOREINFO="NONE"insert command can also be used with a MOREINFO="NONE"selectID="IN98286"select into command, bcp and
 statement in order to move data from one table to another. The MOREINFO="NONE"select statement with an MOREINFO="NONE"into clause can create a new table, based on the columns in the MOREINFO="NONE"select statement and the tables in the MOREINFO="NONE"from clause, and copy the rows specified in the MOREINFO="NONE"where clause. (See the SQL Server Reference Manual or Transact-SQL User's Guide
ID="IN81287"bcp (bulk copy utility)
ID="IN74288"Databases, copying with bcp
 for details.) NUMBER="3" ID="X33269"Using isqlID="IN691"isql utility commandRENDERAS="SECT1"IntroductionREMAP="BO--Body"This chapter introduces the interactive SQL utility MOREINFO="NONE"isql and discusses some MOREINFO="NONE"isql topics: changing the command terminator, the interaction of the performance option and command terminator values, setting the network packet size, and input and output files.RENDERAS="SECT1"How to Use Transact-SQL with the isql UtilityREMAP="BO--Body"You can use SQL directly from the operating system with the stand-alone utility program MOREINFO="NONE"isql. You must have an account, or login, on SQL Server. To access the account, enter at your operating system prompt:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isqlREMAP="BO--Body"The following prompt appears:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"Password:REMAP="BO--Body"Type your password at the prompt and press the Return key. The password does not appear on the screen as you type. The MOREINFO="NONE"isql prompt appears, as follows: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"1>ID="IN122"Transact-SQLREMAP="BO--Body"You can now start issuing Transact-SQL commands. The MOREINFO="NONE"isql program sends the commands to SQL Server, formatting the results and printing them on the standard output. ID="IN603"Command terminator (isql)REMAP="BO--Body"Terminate a command by typing a line beginning with the default command terminator MOREINFO="NONE"go. For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql Password:  1>select * 2>from authors 3>where city = "Oakland" 4>go REMAP="BO--Body"ID="IN884"exit command, isql
To exit MOREINFO="NONE"isql, ID="IN885"quit command, isql
type MOREINFO="NONE"quit or MOREINFO="NONE"exit on a line by itself.ID="IN1316"Formatting isql output;isql utility commandformatting outputRENDERAS="SECT2"Formatting isql OutputREMAP="BO--Body"LINKEND="X39501"Table 3-1 describes the options that change the format of MOREINFO="NONE"isql output:TOCENTRY="1"LBL="Table 3-1: " ID="X39501"Format options for isqlCOLS="3" CHAROFF="50" CHAR="" ALIGN="LEFT"COLNAME="C1" COLWIDTH="6*"COLNAME="C2" COLWIDTH="5*"COLNAME="C3" COLWIDTH="10*"VALIGN="BOTTOM"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"OptionCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"DefaultCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MeaningVALIGN="TOP"COLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"-h headersCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"1COLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Number of rows to print between column headingsCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"-s colseparatorCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"single spaceCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changes the column separator characterCOLNAME="C1" ALIGN="LEFT" ROTATE="0" MOREROWS="0"MOREINFO="NONE"-w columnwidthCOLNAME="C2" ALIGN="LEFT" ROTATE="0" MOREROWS="0"80 charactersCOLNAME="C3" ALIGN="LEFT" ROTATE="0" MOREROWS="0"Changes the line widthREMAP="BO--Body"To include each command issued to MOREINFO="NONE"isql in the output, use MOREINFO="NONE"-eID="IN477"Numbersline, removing from isql
; use MOREINFO="NONE"-n to remove numbering and prompt symbols. For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"iMOREINFO="NONE"sql -e -n -o outputMOREINFO="NONE"Password:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from authors where city = "Oakland" go quitREMAP="UI--UserInput" FORMAT="LINESPECIFIC"cat outputREMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"select *from authorswhere city = "Oakland" au_id       au_lname                                 au_fname      phone        address                                       city                 state country      postalcode  ----------- -------------------------------------- ------------      ------------ ----------------------------------------      -------------------- ----- ------------ ----------  213-46-8915 Green                                  Marjorie      415 986-7020 309 63rd St. #411                             Oakland              CA    USA          94618       274-80-9391 Straight                               Dick                      415 834-2919 5420 College Av.                              Oakland              CA    USA          94609       724-08-9931 Stringer                               Dirk                      415 843-2991 5420 Telegraph Av.                            Oakland              CA    USA          94609       724-80-9391 MacFeather                             Stearns                   415 354-7128 44 Upland Hts.                                Oakland              CA    USA          94612       756-30-7391 Karsen                                 Livia                     415 534-9219 5720 McAuley St.                              Oakland              CA    USA          94609      (5 rows affected)REMAP="BO--Body"Note that the output file does not include the command terminator.RENDERAS="SECT2"Correcting InputREMAP="BO--Body"If you make an error when typing Transact-SQL commands, you can:MARK="BULL" REMAP="BU--Bullet"Type Ctrl-c or type the word ID="IN698"reset command, isql
MOREINFO="NONE"reset on a line by itself. ID="IN379"Query buffer, resetting;Buffer, queryREMAP="UN--Under"This clears the query buffer and returns the MOREINFO="NONE"isql prompt.Type the name of your editor on a line by itself. REMAP="UN--Under"This puts you in a text file where you can edit the query. When you write and save the file, you are returned to MOREINFO="NONE"isql. The query appears; type MOREINFO="NONE"go to execute it. RENDERAS="SECT1"Changing the Command TerminatorREMAP="BO--Body"If you include the command terminator argument (MOREINFO="NONE"-c), you can choose your own terminator symbol. "go" is the default value for this option.REMAP="BO--Body"For example, to use a period as the command terminator, invokeMOREINFO="NONE" isql as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"isql -c.REMAP="BO--Body"A sampleMOREINFO="NONE" isql session with this command terminator looks like this:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"MOREINFO="NONE"1> select name from sysusers2> .REMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"name MOREINFO="NONE"-----------sandykimleslieREMAP="CO--ComputerOutput" FORMAT="LINESPECIFIC"MOREINFO="NONE"(3 rows affected)REMAP="BO--Body"Using theMOREINFO="NONE" isql command terminator option with scripts requires advance planning:ID="IN2910"Scriptscommand terminator inMARK="BULL" REMAP="BU--Bullet"All SQL Server-supplied scripts, such as MOREINFO="NONE"installmaster, use "go". Do not change the command terminator for any session which uses these scripts.Your own scripts may already have "go" in them. Remember to update any of your own scripts to include the terminator you plan to use.ID="IN5011"Statisticsisql
ID="IN9012"Command terminator (isql)statistics option interactionRENDERAS="SECT1"Performance Statistics Interaction with Command Terminator ValuesREMAP="BO--Body"MOREINFO="NONE"isql provides a performance statistics option (MOREINFO="NONE"-p). For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -pREMAP="UI--UserInput" FORMAT="LINESPECIFIC"1> select * from sysobjects2> goREMAP="BO--Body"returns the following statistics:REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"1 xact:Clock Time (ms.): total = 35000 avg = 35000 (0.03 xacts per sec.)REMAP="BO--Body"This means that a single transaction took 3000 milliseconds, so the average is one transaction per 3000 milliseconds. The clock time value reflects the entire transaction, which starts when Client Library builds the query and ends when Client Library returns the information from SQL Server.REMAP="BO--Body"You can gather performance statistics based on executing one or more transactions. To gather statistics on more than one transaction, specify a number after the command terminator (MOREINFO="NONE"go, by default). For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -pREMAP="UI--UserInput" FORMAT="LINESPECIFIC"1> select * from sysobjects2> go 3REMAP="BO--Body"This tells SQL Server to execute three MOREINFO="NONE"select * transactions and report the performance statistics. SQL Server returns:REMAP="COW--CompOutWide" FORMAT="LINESPECIFIC"3 xacts:Clock Time (ms.): total = 41000 avg = 13666 (0.07 xacts per sec.)RENDERAS="SECT1"Setting the Network Packet SizeID="IN7513"Performanceisql network packet size andREMAP="BO--Body"The ID="IN7414"Packet
size, networkspecifying in isqlID="IN7315"Network packet sizespecifying in isqlID="IN7416"Packet size, networkspecifying in isql MOREINFO="NONE"-A size option specifies the network packet size to use for this MOREINFO="NONE"isql session For example:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -A 2048REMAP="BO--Body"sets the packet size to 2,048 bytes for this MOREINFO="NONE"isql session. To check, type:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from sysprocessesREMAP="BO--Body"The value appears under the network_pktsz heading.REMAP="BO--Body"sizeID="IN8517"default network packet size configuration variable
 must be between the values of the MOREINFO="NONE"default network packet size and ID="IN8518"maximum network packet size configuration variable
MOREINFO="NONE"maximum network packet size configuration variables, and must be a multiple of 512. SQL Server uses the closest available packet size that is a multiple of 512 if there is not enough memory available.ID="IN7519"Performanceisql network packet size andREMAP="BO--Body"Use packet sizes larger than the defaults to perform I/O-intensive operations, such as MOREINFO="NONE"readtext or MOREINFO="NONE"writetext operations.REMAP="BO--Body"Setting or changing SQL Server's packet size does not affect the remote procedure call's packet size.RENDERAS="SECT1"Input and Output FilesREMAP="BO--Body"You can specify input and output files on the command line with the MOREINFO="NONE"-i and MOREINFO="NONE"-o options. REMAP="BO--Body"MOREINFO="NONE"isql does not provide
formatting options for the output. However, you can use theID="IN6720"Numbersline, removing from isqlID="IN6221"Line numbers, removing isql MOREINFO="NONE"-n option to eliminate the MOREINFO="NONE"isql prompts, and use other tools to reformat the output.ID="IN5222"Echo input (isql)REMAP="BO--Body"If you use the MOREINFO="NONE"-e option, MOREINFO="NONE"isql echoes the input to output. The resulting output file contains both the queries and their results.RENDERAS="SECT2"UNIX Command Line RedirectionREMAP="BO--Body"ID="IN13123"\> (redirect out), in isql;Redirect out symbol (\>), isql
ID="IN12724"\isql;Redirect in symbol (\isql
The UNIX redirection symbols, "<" and ">", provide a similar mechanism to the MOREINFO="NONE"-i and MOREINFO="NONE"-o options, as follows:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -Usa -Ppassword < input > outputREMAP="BO--Body"You can directMOREINFO="NONE" isql to take input from the terminal, as in the following example: REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -Usa -Ppassword << EOF > outputREMAP="UI--UserInput" FORMAT="LINESPECIFIC"select * from tableREMAP="UI--UserInput" FORMAT="LINESPECIFIC"goREMAP="UI--UserInput" FORMAT="LINESPECIFIC"EOFREMAP="BO--Body"The "<<EOF" instructs MOREINFO="NONE"isql to take input from the terminal until the string "EOF." You can replace "EOF" with any character string. REMAP="BO--Body"Similarly, the following example signals the end of input with Ctrl-d:REMAP="UI--UserInput" FORMAT="LINESPECIFIC"isql -Usa -Ppassword << > outputID="INB6725"isql utility command