Microsoft SQL Server's bulk copy utility

learn more… | top users | synonyms

12
votes
1answer
3k views

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
7
votes
1answer
14k views

Unable to open BCP host data-file error

Having problem using BCP via xp_cmdshell. I exported the table data to a file as .dat but when I try to import the file to a new table, I am getting the below error. The SQL Server service and the ...
6
votes
2answers
620 views

Raise error if BCP command fails to dump data into a file

Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error ...
5
votes
2answers
670 views

Insert custom header row in BCP output

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an ...
5
votes
2answers
2k views

BULK INSERT - best usage

I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table. I split ...
5
votes
1answer
410 views

bcp database migration

I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use ...
4
votes
2answers
9k views

Minimum requirements to run Sql Server BCP.exe utility

I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe ...
4
votes
3answers
4k views

How to import a varbinary(max) column with bulk import?

I have a varbinary(max) column that I need to bulk import. The data I'm putting into this column is a byte[] in C#. How do I format the data in the comma-delimited file used for bulk import? In ...
4
votes
3answers
8k views

bcp in of datetime type data results in an “Invalid date / time format”

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited. I then bcp in to a truncated table ...
4
votes
1answer
66 views

BCP To Copy Headers

I am using a stored procedure to populate a table with the data that I need. I am then using BCP to copy the data to a .csv file. This process works, the one thing it is missing is that BCP is not ...
4
votes
3answers
215 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
3
votes
3answers
5k views

How to run bcp utility from root drive?

My machine does not have full SQL Server installed. The following distributable packages are installed on my machine:- Microsoft® SQL Server® 2008 R2 Command Line Utilities Microsoft® SQL Server® ...
3
votes
2answers
2k views

Bulk insert with format file: 0 rows affected

When using the BULK INSERT command in SQL Server 2008 it returns: (0 row(s) affected) I am using this command to carry out the bulk insert: BULK INSERT Test FROM 'C:\DataFiles\Tests.dat' ...
3
votes
0answers
78 views

File size when extracting from SQL Server to CSV

I am attempting to extract a table to CSV using BCP from SQL Server 2014 and the size of the file once the data is extracted is actually larger than the size of the database itself. One thing to note ...
2
votes
1answer
30 views

Use BCP To Copy When Data Has Apostrophe In It

Anytime my data has an apostrophe in it, the data is automatically split into the next field (which is skewing the result set). Let's say this is my DDL Create Table OneTwoThree ( userid varchar(...
2
votes
1answer
208 views

Can Unicode columns include non-Unicode values in SQL Server?

I need to convert Unicode column values from UTF16-LE to UTF-8 to then import into MySQL. Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all must be ...
2
votes
1answer
726 views

Header is added when exporting column of type image with BCP to files

I export a column of type image to files using this command: EXEC master..xp_cmdshell 'BCP "SELECT file_name FROM file_table WHERE id = 11 " queryout "C:\bcpdir\test.xml" -T -N' In SQL Management ...
2
votes
2answers
220 views

SQL Server BULK INSERT

I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible? Thanks
2
votes
1answer
2k views

BCP error near the keyword 'as'

I am new to using BCP but I am trying to extract data generated via a stored proc. This is a continuation of my original question which was a stored procedure with temp tables. This procedure has ...
1
vote
3answers
2k views

SELECT Data Set Using Stored Procedure with BCP Error

All, I have a BCP export query. It is failing with the useless error mesage on BCP usage: usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f ...
1
vote
1answer
52 views

BCP did not preserve relational data

I developed a document tracking database and front end C#/.NET app on my local machine (SQLServer Express and VS Express). Once it was working and usable I had our IT branch install the database (SQL ...
1
vote
1answer
2k views

Another BCP Export [Fail]ure

All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs DECLARE @...
1
vote
1answer
433 views

How to quote database names with spaces when using queryout with bcp?

I'm attempting to use bcp on Microsoft SQL Server 2000 to export a couple of columns from a large database table. The database names contain spaces. Here's an example that works that contains an ...
1
vote
1answer
885 views

How to avoid delimiter character in BCP export?

In my project, I need to use SQL 2008 BCP tool to export the result of a stored procedure into a fixed column width file. I used different approaches, but still couldn't get expected result. Things I ...
1
vote
2answers
3k views

Database table data transfer between different servers using bcp.exe and mysqldump

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY. Using MSSQL on two different servers I am using the following command ...
1
vote
1answer
41 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
1
vote
0answers
14 views

Snapshot replication error two days in a row

We have a mature replication system to move data from OLTP to BI. It's been working for years with relatively few troubles. This week we've run into the same problem two days in a row with snapshot ...
1
vote
0answers
275 views

Export using BCP as ASCII text

I have a script that stores data in a temporary table to be exported by running BCP using xp_cmdshell. The trouble is that BCP insists on exporting the data in Unicode and I need it to be in ASCII. ...
1
vote
0answers
213 views

SQL Server BCP imports, but table still has 0 records

I ran the following command from the command line on SQLServer 2008 R2 BCP C4L.dbo.districts in C:\TEMP\districts.sdf -f C:\TEMP\districts_import.fmt -T' No errors were reported and it ran as ...
0
votes
2answers
2k views

xp_cmdshell bcp wont save file

EXEC xp_cmdshell 'bcp "SELECT lastconnectip FROM RF_USER.dbo.tbl_UserAccount" queryout "C:\test.txt" -T -c' The command above runs, the output: NULL Starting copy... 1000 rows successfully bulk-...
0
votes
1answer
25 views

SSIS slow performance to insert short rows

(Edit: I changed the post after some more explicative tests) I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of ...
0
votes
1answer
408 views

“Unable to Open BCP host data-file” using xp_cmdshell from SSMS but command line works

I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written. I noticed the files were piling up and BCP wasn't picking them up so I ...
0
votes
1answer
79 views

SQL Azure Migration Wizard - Duplicate Primary Key

Just did a test at migrating a 120gb database onto RDS using SQL Azure Migration Wizard Because we can't afford any downtime for "just" a test we ran the tool on the live database (meaning data was ...
0
votes
0answers
19 views

bcp copy from snapshot failed due to snap being in transition

We have a SQL job that runs each night to perform a data extract and import function. The job performs against a snapshot and copies from several tables in a serial loop. Today the job reported the ...
0
votes
1answer
77 views

Import quoted CSV

I have a set of quoted CSV files, which I need to import into SQL Server. Files look like this: "ColA","ColB","ColC" "12345","John Smith","-75.33" "12356","Jane Doe","43.12" BCP and BULK INSERT ...
0
votes
1answer
35 views

SQL BCP Utility Writing to SSMS Insead of .csv File

I have a stored procedure in SSMS call sp_testInventoryIssues as follows: USE [ADMIN] GO /****** Object: StoredProcedure [dbo].[sp_testInventoryIssues] Script Date: 06/19/2015 16:02:29 ******/ ...
0
votes
0answers
4k views

BCP Errors: String data, right truncation; Unexpected EOF

OK... I give up. I have been struggling with trying to import a semicolon-separated file (field1;field2) with BCP for the past two days, but I only have been getting these errors: Starting copy... ...
0
votes
0answers
252 views

Best way to move sybase IQ table to Sybase ASE

I use bcp to copy Sybase IQ table to sybase ASE server. Actually bcp first creates some bulk file with extracdet sybase IQ table there, and after just loads that file to Sybase ASE. Is it the one and ...
0
votes
0answers
253 views

Export a varbinary column to file system using existing authentication

I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use ...
-2
votes
1answer
433 views

Errors keeping stored procedures through restore from backup [closed]

We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...