by Orryn Sledge
You can use various methods to import and export SQL Server data. Almost all systems require some type of data transfer. BCP is the utility provided by SQL Server to transfer data. This chapter discusses in detail the BCP utility and also covers alternatives to BCP.
BCP stands for Bulk Copy Program. It is the only tool SQL Server provides (with the exception of a few tricks discussed later in this chapter) to import and export data. Data can be either native mode (SQL Server specific) or character mode (ASCII text). ASCII text data is commonly used to share data between SQL Server and other systems (see Figure 16.1).
Figure 16.1.
Common uses of BCP.
Is It Love or Hate?
As a DBA, you will probably have a love/hate relationship with BCP. BCP is limited in scope and lacks common file formats, but it does provide excellent performance. For those new to SQL Server, the following list provides some insight into BCP. These are the reasons why I like BCP:
Performance: BCP is one the fastest raw data loaders around. I have seen BCP turn in impressive performance numbers compared to other import/export products.
Minimal overhead: Because BCP is command-line based, it requires a nominal amount of memory to run compared to today's memory-intensive GUI applications. This leaves memory for other tasks. Now for the drawbacks of BCP. These are the reasons why I hate BCP:
Unforgiving syntax: BCP's switches are case- and order sensitive. This is because BCP's origins stem back to Sybase and the UNIX world, where commands are case sensitive. I wish that Microsoft would break with tradition and implement a user-friendly version of BCP.
Minimal file support: Basically, the choices are ASCII text, native SQL Server format, or nothing at all. Do not try to load an Excel spreadsheet or an Access database directly into SQL Server; it will never work. To load non-SQL Server data, you must export the data as ASCII text and then import the text file into SQL Server. It would be nice if BCP could directly import/export today's popular file formats such as Access, Excel, dBASE, and so on.
Inadequate error messages: BCP's error messages are minimal and too generic. I wish that Microsoft would enhance BCP's error messages to be more informative and specific. As you can see, BCP is far from perfect, but it is the only utility provided by SQL Server to import/export data. Oh well! With this in mind, the remainder of this chapter will provide you with some useful tips and tricks to make your life easier when you use BCP.
Use the following syntax to perform BCP operations:
bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
NOTE: With BCP, you can use - or / to preface a switch. For example, the following two statements are equivalent:
bcp pubs..sales out sales.out /c /Usa /P
bcp pubs..sales out sales.out -c -Usa -P
Parameter | Explanation |
database_name | Name of the database being accessed.The database name is optional; if the database name is omitted, the user's default database is used (optional). |
owner | Owner of the table or view being accessed (optional). Tip: Use the .. symbol to specify ownership. The .. syntax is more generic than specifying an owner (for example: pubs..authors instead of pubs.dbo.authors). |
table_name | Name of the table or view being accessed (required). Tip: Use the # or ## symbol to copy a temporary table. |
in | out | Direction of data transfer, where in means import and out means export (required). |
datafile | The name of the data file for an import or the name of the file to be created during an export. A path can be included with this statement, such as c:\mssql\binn\authors.txt (required). |
/m maxerrors | Maximum number of errors that can occur before the BCP operation is terminated. Each failed insert counts as one error. Default value is 10 (optional). |
/f formatfile | The name of the format file used to import or export data. A path can be included with this statement, such as c:\mssql\binn\authors.fmt (optional). |
/e errfile | The name of the error file to store BCP error messages and unsuccessfully transferred
rows. A path can be included with this statement, such as c:\mssql\binn\authors.err
(optional). Tip: Error files are useful for pinpointing BCP errors during unattended operations such as nightly data imports. |
/F firstrow | The number of the first row to copy (optional). |
/L lastrow | The number of the last row to copy (optional). Tip: The /F and /L switches are useful when copying portions of data. For example, to export the first 1,000 records from a table, use the following syntax: /F 1 /L 1000 |
/b batchsize | The number of rows transferred in a batch. The default setting is the number of rows in the data file (optional). |
/n | Native data mode. Native data is SQL Server specific. Native data mode does not prompt the user for field information (optional). |
/c | Character data mode. Character data (ASCII) can be transferred to and from SQL Server
tables and other non-SQL Server products. Character mode does not prompt the user
for field information. By default, fields are tab delimited and rows are newline
delimited (optional). Tip: Character data mode is usually easier to work with than native data mode. |
/E | New with version 6.x. Used when importing data into a table that contains an identity
datatype and you want to populate the column with values from the data file. If this
switch is omitted, SQL Server automatically populates the identity column
and ignores the field's corresponding data values in the import file (optional).
The following example shows how the /E switch impacts data imports: Sample table structure: id int identity(1,1) descr char(15) Sample data file: 5 xxx 6 yyy 7 zzz BCP syntax WITHOUT the /E switch: bcp sales..table2 in table2.txt /c /U sa /P Results: id descr ---------- ----------------------------------- 1 xxx 2 yyy 3 zzz Notice the values in the id column. SQL Server populated the id column with an automatically incremented data value. It ignored the values 5,6,7 in the data file. The following is BCP syntax with the /E switch: bcp sales..table2 in table2.txt /c /E /U sa /P Results: id descr ----------- ------------- 5 xxx 6 yyy 7 zzz With the /E switch, the values in the text file were observed and SQL Server did not automatically generate a set of data values for the id column. Tip: Use the /E switch to preserve data values when you are unloading and reloading data in a table that contains an identity datatype. Otherwise, SQL Server automatically populates the identity column with its own set of values. |
/t field_term | Field terminator (optional). See Table 16.1 for BCP terminators. |
/r row_term | Row terminator (optional). See Table 16.1 for BCP terminators. |
/i inputfile | File to redirect input. This switch is not generally used (optional). |
/o outputfile | File to redirect BCP output (optional). Tip: Use the /o switch to log BCP output during unattended BCP operation. This creates a useful trail of BCP output that can be used to monitor and diagnose BCP performance and execution. |
/U login_id | SQL Server login ID (required). |
/P password | SQL Server password. If the password is omitted, BCP prompts you for a password (required).
Note: If you are using integrated security or your SQL Server login does not have a password, BCP still prompts you for a password. To bypass BCP's prompt, use the /P switch without a password, as in the following example: BCP pubs..authors in authors.txt /U sa /P |
/S servername | The name of the server that contains the database and table you are working with. The /S servername switch is required if you are using BCP from a remote client on a network (optional). |
/v | Displays the version of DB Library in use (optional). Note: If you are concurrently running SQL Server version 6.x and version 4.2x, be certain that you are using the correct version of BCP. To determine which version of BCP is in use, type BCP /v. Version 6.x's copyright date will be greater than or equal to 1995. |
/a packet_size | The number of bytes contained in a network packet. The default value for Windows
NT Server and Windows NT clients is 4096 ; the default value for MS-DOS clients is
512. Valid sizes are 512 to 65535 bytes (optional). Tip: Depending own your network architecture, you may be able to improve BCP performance by increasing the packet size. Try setting the packet size between 4096 and 8192 bytes. Use the statistics returned by BCP (the clock time and rows per second) to help tailor this setting. |
Terminator Type | Syntax |
tab | \t |
new line | \n |
carriage return | \r |
backslash | \\ |
NULL terminator | \0 |
user-defined terminator | character (^, %, *, and so on) |
No permissions are required to run the BCP command-line utility. However, to use BCP to copy data into a table, the user must be granted INSERT permission to the target table. To export data from a table, the user must be granted SELECT permission for the source table.
BCP can import/export data in character file format or native file format. Character mode is plain old ASCII text. Use the /c switch or a format file to specify character mode. Native mode uses special formatting characters internal to SQL Server to represent data. Use native mode only when you are transferring data between SQL Server tables. Use the /n switch to specify native mode. Following is sample output from character mode BCP:
bcp pubs..jobs out jobs.txt /c /U sa /P
1 New Hire - Job not specified 10 10
2 Chief Executive Officer 200 250
3 Business Operations Manager 175 225
TIP: Character mode is usually easier to work with than native mode because you can view the contents of a character mode data file with a standard text editor.
Interactive BCP is used to selectively import or export data. Interactive mode
is automatically activated when the following switches are not included in
the BCP statement:
/n (native format)
/c (character format)
/f (format file)
Through the use of interactive prompts, you can tailor BCP to your import and export specifications. Interactive BCP prompts you for four pieces of information:
The following are sample interactive BCP prompts:
Enter the file storage type of field discounttype [char]:
Enter prefix-length of field discounttype [0]:
Enter length of field discounttype [40]:
Enter field terminator [none]:
TIP: When importing data, you can skip a column by entering 0 for prefix length, 0 for length, and no terminator. You cannot skip a column when exporting data.
At the end of an interactive BCP session, you receive the following prompt:
Do you want to save this format information in a file?
[Y/n]
Host filename [bcp.fmt]:
If you answer yes at this prompt, your interactive responses are saved to a format file. This enables you to specify at a later time the /f switch (format file) to automatically reuse the information from your interactive BCP session.
The file storage type specifies the datatypes used to read from and write
to data files. Table 16.2 lists valid file storage types.
TIP: When working with ASCII files, set all file storage types to char, regardless of the table's datatypes. This is the only way you can load ASCII data into SQL Server using BCP.
SQL Server uses the prefix length to store compacted data. When working
in native mode, accept the default values whenever possible.
TIP: When working with fixed-width ASCII data, set the prefix length to 0.
The field length specifies the number of bytes required to store a SQL Server datatype. Use default field lengths whenever possible, otherwise data truncation or overflow errors may occur. Table 16.3 lists default field lengths.
TIP: When importing and exporting ASCII fixed-width data files, you may need to modify the field length to match your import/export specification. For example, to export a char(15) column as a 25-byte piece of data, specify a field length of 25. This pads the data length to 25 bytes.
The field terminator prompt controls how field data is delimited (separated).
The default delimiter is no terminator. See Table 16.4 for valid field terminators.
TIP: The last field in a table acts as a row terminator. To separate rows with a newline delimiter, specify \n at the field terminator prompt.
NOTE: At the BCP command line, you can also use the /t (field terminator) and /r (row terminator) switches to specify terminators.
Terminator Type | Syntax |
tab | \t |
new line | \n |
carriage return | \r |
backslash | \\ |
NULL terminator | \0 |
user-defined terminator | character (^, %, *, and so on) |
A format file is a template for BCP to use when you import or export data. With this template, you can define how BCP should transfer your data.
The easiest way to create a format file is to initiate an interactive BCP session. Interactive mode is initiated when you do not specify one of the following switches:
At the end of your interactive session, you see the following prompt:
Do you want to save this format information in a file?
[Y/n] y
Host filename [bcp.fmt]:sample.fmt
At this prompt, enter a filename to save the format information. SQL Server then creates a format file, which is really just an ASCII text file (see Figure 16.2). You can make modifications to an existing format file by using a standard text editor.
Figure 16.2.
A sample format file.
TIP: Use the FMT extension when saving format files to simplify file identification.
Once you have saved the format file, you can reuse the format file by specifying the /f (format file) switch, as in the following example:
bcp sales..discounts in discount.txt /f sample.fmt /U sa /P
NOTE: If a table has a corresponding format file, any column modification to the table must be reflected in the format file. For example, if you drop a column from a table, you must also remove the column from the format file.
This section describes how to use BCP to perform typical import and export routines. The examples discussed in this section use the pubs..discounts table.
The following is the structure of the discounts table:
discounttype varchar (40)
stor_id varchar
lowqty smallint
highqty smallint
discount decimal(0, 0)
The following is discounts table data:
discounttype stor_id lowqty highqty discount
---------------------------------------- ------- ------ ------- --------
Initial Customer (null) (null) (null) 10.50
Volume Discount (null) 100 1000 6.70
Customer Discount 8042 (null) (null) 5.00
This example uses the /c switch to load a data file that contains tab-delimited fields and newline-delimited rows. For this example, the import data is contained in a file named disc.txt. Following are the contents of the sample import file:
Preferred Customer 6380 200 800 5.5
Valued Customer 7896 100 1000 8.5
The following syntax shows how to import the contents of the disc.txt file into the discounts table:
bcp pubs..discounts in disc.txt /c /U sa /P
This example uses the /c switch to export data to a file with tab-delimited fields and newline-delimited rows. The following syntax shows how to export the contents of the discounts table to the discount.out file:
bcp pubs..discounts out discount.out /c /U sa /P
Following is the output:
Initial Customer 10.50
Volume Discount 100 1000 6.70
Customer Discount 8042 5.00
This example imports a data file that contains comma-delimited fields and newline-delimited rows. The /t switch specifies a comma delimiter; the /r\n switch specifies a newline row delimiter. For this example, the import data is contained in a file named disc2.txt. Following are the contents of the sample import file:
Preferred Customer,6380,200,800,5.5
Valued Customer,7896,100,1000,8.5
The following syntax shows how to import the contents of the disc2.txt file into the discounts table:
bcp pubs..discounts in disc2.txt /c /t, /r\n /U sa /P
This example exports the discounts table to a file with comma-delimited fields and newline row delimiters. The following syntax shows how to export the contents of the discounts table to the disc3.txt file:
bcp pubs..discounts out disc3.txt /c /t, /r\n /U sa /P
Following is the output:
Initial Customer,,,,10.50
Volume Discount,,100,1000,6.70
Customer Discount,8042,,,5.00
This example uses a fixed-length ASCII text file named disc4.txt. Table 16.5 shows the layout of the text file.
Column Name | File Length | File Position |
discounttype | 40 | 1-39 |
stor_id | 4 | 40-43 |
lowqty | 6 | 44-49 |
highqty | 6 | 50-55 |
discount | 5 | 56-60 |
123456789012345678901234567890123456789012345678901234567890
Preferred Customer 6380200 800 5.5
Valued Customer 7896100 1000 8.5
For fixed-length data transfers, SQL Server needs to know the field positions in the data file. An easy way to do this is to use interactive BCP. To begin interactive BCP, use the following command:
bcp sales..discounts in disc4.txt /U sa /P
For the first two prompts, you can accept the default values because they match
the layout in the data file. For the third, forth, and fifth prompts (see the highlighted
text in Figure 16.3), you have to override the default prompts.
NOTE: When importing fixed-length ASCII data, always use char for the file storage type and 0 for the prefix length.
Suppose that you need to export the discounts table in a fixed-length file format and the format must follow the specification used in the previous example. No problem; you can reuse the format file you created in the previous example (see Figure 16.4).
The following syntax shows how to export the contents of the discounts table to the disc4.out file:
bcp pubs..discounts out disc4.out /c /f disc4.fmt /U sa /P
Figure 16.3.
Interactive BCP responses.
Figure 16.4.
The disc4.fmt format file.
Suppose that you want to skip the columns stor_id, lowqty, and highqty when you load the disc4.txt ASCII file. To do this, you must modify the format file. To skip a column, enter 0 for the table column order (see Figure 16.5).
Figure 16.5.
The format file used to skip columns.
After you modify your format file, you can use the following BCP syntax to load the
data:
bcp pubs..discounts in disc4.txt /c /f disc4.fmt /U sa /P
BCP does not allow you to skip a column in a table during an export. However, you can trick BCP into skipping a column by creating a view that references only the columns you want to export, thus skipping unwanted columns. Then use BCP to export the data from the view.
The following syntax shows how to export only the discounttype and discount columns from the discounts table:
create view discounts_view as
select output = convert(char(40),discounttype) + convert(char(5),discount)
from discounts
Next, create a format file that contains one column (see Figure 16.6). Only one column is listed in the format file because the view concatenates the discounttype and discount columns.
Figure 16.6.
The format file used to export data from a view.
Finally, use BCP to export the data from the view:
bcp pubs..discounts_view out discview.txt /f discview.fmt /U sa /P
The following is sample output:
Initial Customer 10.50
Volume Discount 6.70
Customer Discount 5.00
When importing data, BCP has two modes of operation: fast mode and slow mode.
As you probably guessed, the fast mode runs faster than the slow mode. The performance
difference is caused by the logging of transactions. Fast mode bypasses the transaction
log; slow mode posts all data inserts to the transaction log.
NOTE: You need to be concerned with fast and slow mode BCP only when you import data. BCP does not use a fast or slow mode when you export data. When you run BCP, SQL Server automatically decides which BCP mode to run. There is no BCP switch that allows you to toggle between fast and slow modes.
In SQL Server 6.x, two factors determine whether BCP can run in fast mode: the SELECT INTO/BULKCOPY option and indexes. For BCP to run in fast mode, the following two conditions must be true:
If either of these conditions is FALSE, BCP runs in slow mode (see Figure 16.7).
Figure 16.7.
How SQL Server determines which BCP mode to run.
You may be asking yourself, "Why not always run BCP in fast mode?" The answer is based on the following three factors:
Backup Strategy To run the fast mode of BCP, you must have the SELECT INTO/BULKCOPY
option set to TRUE. By setting this option to TRUE, you may be
sacrificing data recovery for BCP performance. When SELECT INTO/BULKCOPY
is set to TRUE, you cannot back up the transaction log for a database. Instead,
you can only back up the entire database. This means that you will be unable
to use the transaction log to provide up-to-the-minute data recovery. Window of Opportunity
Fast mode BCP requires that the target table not contain any indexes. This means
that you must consider the downtime involved with dropping the indexes, loading the
data, and re-creating the indexes. For a table that requires 24-hour data access,
it is not feasible to drop and re-create indexes.
TIP: To significantly reduce the time required to create a clustered index, have your import data presorted on the fields that make up your clustered index. Then use the WITH SORTED_DATA option to create the clustered index, as in the following example:
CREATE CLUSTERED INDEX pk_idx ON table1 (id) WITH SORTED_DATA
TIP: The WITH SORTED_DATA option bypasses the physical data sort step normally used to create a clustered index.
Available Database Space A clustered index requires free space equal to approximately 120 percent the size of the table. For example, a 100M table requires approximately 120M of free database space to create the clustered index. If you are tight on disk space, you may not be able to drop and re-create a clustered index for a large table.
Table 16.6 helps clarify the differences between the two modes.
Fast Mode | Slow Mode | |
PROS | Fast! Operations are not logged. Don't have to worry about filling up the transaction log. | Maximum recoverability. |
CONS | Zero recoverability. Must dump the database after using BCP. Cannot dump the transaction log. Indexes must be rebuilt after loading the data | Slow! Every insert is written to the transaction log. Can easily fill up the transaction log during large data imports, thus complicating the import process. |
NOTE: BCP may or may not notify you about which mode it is using. You do not receive a message that slow mode is in use when you import data into a table with an existing index.
When using BCP to import data into a SQL Server table, it is important that you
understand how triggers, rules, defaults, constraints, and unique indexes are enforced.
Many people forget that certain types of objects are bypassed when using BCP to import
data. Table 16.7 summarizes which objects are enforced when BCP is used to import
data.
Object | Enforced? |
Default | Yes |
Unique index/unique constraints | Yes |
Primary key and foreign key constraints | Yes |
Check constraint | No |
Rule | No |
Trigger | No |
CAUTION: Do not forget that triggers, check constraints, and rules are not enforced when using BCP. To prevent data integrity problems, load your data into a work table and run it through a validation routine similar to the validation defined in your triggers, constraints, and table rules. Once you are satisfied that the data meets your integrity requirements, transfer the data to your target table.
NOTE: Before version 6.x, you could enforce primary key and foreign key relationships only through the use of triggers. Because BCP bypasses triggers, additional steps were required to ensure that you did not violate primary key and foreign key integrity. This problem has been resolved in version 6.x through the use of primary key and foreign key constraints, which are enforced by BCP.
Be on the lookout for the following traps. They always seem to be lurking out there.
Use the following tips to help simplify data imports and exports:
emp_id char(3)
hire_date datetime
CREATE VIEW date_example_view AS
SELECT emp_id,convert(char(12),hire_date,1)
FROM sample_table
bcp sales..date_example_view out sample.out /c /Usa /P
NOTE: Refer to the CONVERT function in Appendix D for other date formats.
NOTE: You do not have to issue an UPDATE STATISTICS command if the table's indexes are dropped before the BCP operation is performed and re-created after the BCP operation. Under this scenario, the statistics will be up to date.
CREATE PROCEDURE usp_load_example AS
/* flush out work table */
truncate table table1
/* BCP in data */
exec master..xp_cmdshell "bcp sales..table1 in C:\mssql\binn\table1.txt /c /Usa /P"
/* run summary procedures */
exec usp_summary1
exec usp_summaryN
CAUTION: Do not use xp_cmdshell to call BCP from within a user-defined transaction in a stored procedure. Doing so can lead to endless blocking!
TIP: Do you ever have to export or import data for all the tables in a database? To perform this task, you could manually create BCP scripts, but that can be tedious and time consuming--especially if you must use BCP to copy data from numerous tables in the database. An alternative to manually creating the scripts is to build a SQL statement that automatically generates the BCP syntax. Suppose that you need to export data from all the tables in the pubs database. To generate the BCP syntax, create a query that references the sysobjects table (each database in SQL Server has a sysobjects table and a corresponding record for each object in the database). In the WHERE clause of the query, specify type = `U' (this clause returns only user tables). The following is a sample query used to generate the BCP syntax (the -c switch is used in this example to export the data in a tab-delimited character format):
select `bcp pubs..' + name + ` out ` + name + `.txt' + ` -c -Usa -Ppassword -Stfnserver'
from pubs..sysobjects
where type ='U'
order by nameFollowing is the output from the query:
bcp pubs..authors out authors.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..discounts out discounts.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..employee out employee.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..jobs out jobs.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..pub_info out pub_info.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..publishers out publishers.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..roysched out roysched.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..sales out sales.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..stores out stores.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..titleauthor out titleauthor.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..titles out titles.txt -c -Usa -Ppassword -StfnserverNow that you have the proper BCP syntax, you can save the output from the query to a BAT file and automatically run the file from the command line. As you see, the combination of SQL Server syntax with information from the system tables can simplify common DBA chores.
The following sections discuss alternatives to BCP. These alternatives are simple tips and tricks that can simplify data transfers.
The SQL Server DBA Assistant allows you to graphically export data from SQL Server into common file formats. The product was developed using Visual Basic 4.0 and SQL-OLE technology.
NOTE: The SQL Server DBA Assistant is on the CD that accompanies this book. The product requires a 32-bit operating system (NT or Windows 95).
Follow these steps to use the SQL Server DBA Assistant to export data:
Figure 16.8.
The Bulk Copy tab in the SQL Server DBA Assistant window.
Figure 16.9.
The number of rows exported.
The SELECT INTO statement creates a new table with a structure identical to the structure in the SELECT statement, along with any matching data. For example, the following SQL statement copies the authors table from the pubs database to the sales database:
SELECT *
INTO sales..authors
FROM pubs..authors
NOTE: The database option SELECT INTO/BULKCOPY must be set to TRUE if you want to use SELECT INTO to create a new table in a database. However, the SELECT INTO/BULKCOPY setting has no impact on your ability to create a temporary table in SQL Server.
An INSERT statement combined with a SELECT statement appends data into a target table with data from the source table. For example, the following SQL statement copies any rows returned from the SQL statement into the target_table:
INSERT INTO target_table
SELECT * FROM source_table
TIP: Sometimes you must drop and re-create a table to perform a table modification. Most people use BCP to copy out the data, drop and re-create the table, and then use BCP to copy the data back in. In this scenario, it may be complicated to use BCP to reload your data if you made extensive changes to your table. An alternative to using BCP is to use SELECT INTO to create a working copy of your table. Drop and re-create the table and then use INSERT INTO to reload the data from the work table.
An easy way to save data in a fixed-width format is to issue a SQL SELECT query from the Query window in the Enterprise Manager. The query results are displayed in the Results tab. Click the Save Query/Result button to save the query's output. The output is saved in an ASCII text file in a fixed-width data format with newline separators (see Figure 16.10).
Figure 16.10.
Saving SQL output as a text file.
TIP: Use Query Options to prevent column names and row counts from being displayed in the Results tab. From the Query dialog box, click the Query Options button; the Query Options dialog box appears. From this dialog box, select the Query Flag tab. Set No Count Display to TRUE. On the Format Options tab, set Print Headers to FALSE.
For some inexplicable reason, the graphical version of BCP found in SQL Server
4.2's Object Manager was not included in SQL Server 6.x. This is unfortunate because
the graphical version of BCP in SQL Server 4.2x's Object Manager greatly simplified
BCP usage. However, you can still run Version 4.2x's Object Manager against SQL Server
6.x to take advantage of graphical BCP. The following steps explain how to install
and use version 4.2x's Object Manager to access the graphical version of BCP.
CAUTION: The Object Manager works only with version 6.x tables that use version 4.2x datatypes. This means that you cannot use BCP to copy tables that use identity, decimal, or other SQL Server 6.x enhancements.
Figure 16.11.
The object60.sql script.
TIP: To determine which version of SQL Server you are logged in to, use the @@version global variable. For example:
SELECT @@versionFollowing is the output:
Microsoft SQL Server 6.50 - 6.50.201 (Intel X86)
Apr 3 1996 02:55:53
Copyright (c) 1988-1996 Microsoft Corporation
Figure 16.12.
Object Manager's Transfer Data dialog box.
The Transfer Manager is a graphical tool used to transfer databases and objects. These objects can be transferred between different databases, different servers, and even different versions of SQL Server. Following are the types of objects that can be transferred:
TIP: The Transfer Manager is useful for moving a database from a development server to a production server or for moving a table and its data between the two servers.
NOTE: The Transfer Manager allows you to transfer objects and data between SQL Server 6.x and SQL Server 4.2x. Be aware that several new reserved words were added in SQL Server 6.x. If a reserved word is present in your 4.2x Transact SQL statement, the transfer to SQL Server 6.x will fail. The Transfer Manager also enables you to transfer a SQL Server 6.x database to a SQL Server 4.2x server. However, the database can- not contain any SQL Server 6.x language enhancements such as identity datatypes, decimal datatypes, declarative referential integrity, and so on.
The following steps explain how to use the Transfer Manager:
Figure 16.13.
The Transfer menu option.
Figure 16.14.
The Transfer Manager window.
Following are important notes to remember when importing and exporting data:
As you can see from reading this chapter, you need to know a lot when it comes
to importing and exporting data in SQL Server. The next chapter discusses common
SQL Server errors.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.