Questions tagged [bcp]

Microsoft SQL Server's bulk copy utility

Filter by
Sorted by
Tagged with
7
votes
2answers
368 views

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, ...
1
vote
0answers
21 views

What would be an appropiate BatchSize for bulk insert 34 GB of data (JSON files) into SQL Azure?

I got like 25,000 JSON files whose size (in average) is 1.2 MB (also, the average of records per JSON is 5,000), and the total size is like 34 GB. I have been reviewed the BCP utility from SQL Server, ...
1
vote
1answer
46 views

exporting a sql view to a csv file

Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice ...
0
votes
0answers
44 views

Errors with trying to run a stored procedure with local file output

I'm trying to run a stored procedure that I'm passing some variables to. The output is being writted to a CSV file on a local file share. The CSV file gets created, but I'm hitting some errors ...
0
votes
0answers
131 views

Unable to open BCP host data-file

Having an issue with exporting files to a network drive via the BCP host data file. There is no problem in exporting a file to the C drive of the machine that the SQL Server is installed on, however ...
1
vote
1answer
53 views

Can BCP Out cause resource contention

I had these two things running in parallel in the same database - BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c ...
0
votes
0answers
29 views

Sybase ASE 16.0 BCP in character problem

I am using SYBASE ASE 16.0 and in my tables i have some values like "KOCAELİ", "İZMİR", "TALATPAŞA" . They are readable in csv file however when i put database with bcp in command, i have ...
0
votes
0answers
94 views

bcp: dsn specified an unsupported driver

A straight-forward call to bcp works on linux and not on windows. Ubuntu ~$ /opt/mssql-tools/bin/bcp "select @@version as v" queryout foo.csv \ -D -S mydsn -c -U myuser -t, -r '\n' -P '...' ...
1
vote
1answer
225 views

How to send parameters to BCP inside dynamic sql using sp_executesql

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a ...
1
vote
1answer
440 views

How to troubleshoot “BCP copy in failed”

I have a table that looks something like this in a SQL server database in Azure: CREATE TABLE [dbo].[TableName]( [col01] [int] IDENTITY(1,1) NOT NULL, [col02] [varchar](255) NOT NULL, [col03] [...
0
votes
2answers
128 views

Get Headers from SQL Table

I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"...., 'header50' as "header50" from MyTable) It doesn't work above a ...
1
vote
0answers
471 views

How do I handle the “Invalid field size for datatype” when using bcp?

I'm trying to get a database into Azure SQL Database using bcp, but first I'm trying to get it to load into a local database on my dev machine. I've only used bcp once before, years ago, so I'm a ...
2
votes
0answers
661 views

Unexpected EOF encountered in BCP data-file

I am trying to load a dump file into a SQL Server table using bcp.exe but it fails to insert one row. Input Data File Content [\\\\SERVER\INPUT_SOURCE_FILE.DAT]: COL001|||COL002|||COL003XXDATA001|||...
0
votes
1answer
231 views

BCP via Linux Centos 7, cannot connect to Azure SQL

I installed sqlcmd, bcp and odbc 17 for microsoft. then I connected to Azure SQL with SQLCMD - it worked OK then I tried to import some data with BCP and i got an error I use: bcp schema....
3
votes
2answers
600 views

Use non-printable characters in SQL Server BCP xml format file as TERMINATOR

I have a flat-text data file containing records which fields are separated by the non printable character "File Separator" (0x1c). I am trying to use SQL Server's bcp utility to load this data into my ...
0
votes
1answer
317 views

how to delete the file which is open in bcp.exe

I have run the bcp command in query window to generate the tsv file. In between I cancelled the query. I tried to kill the SPID. But it is killed/rollback. I tried to delete the tsv file in server. ...
1
vote
1answer
2k views

How to save a varchar(MAX) to a file?

For my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
1
vote
0answers
908 views

Exporting to Excel from SQL Server - BCP Using a Stored Procedure Headers not shown

I am running a Stored procedure to export data to an Excel sheet. Issue is that The column headers are being displayed at the bottom or they are missing at times. What am I missing here ? SQL Server ...
2
votes
1answer
390 views

MSSql bcp transaction rollback on error

I am trying to bulk insert data from a file to MS SQL Server using the bcp utility but it is not functioning as I expect it to with regard to errors. I want to understand what I'm doing incorrectly or ...
0
votes
1answer
65 views

Is BulkCopy available for SQL-Server 2012 [closed]

Is BulkCopy still supported by SQL-Server 2012?
11
votes
1answer
1k views

bcp command Incorrect syntax near '�'. Character is actually: “ä”

I have mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line: bcp DBname.dbo.Täble_Name out Täble_Name -c -k -...
2
votes
1answer
729 views

Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files

Not sure if anyone has run into this problem before and I am going a little bit crazy. I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file ...
6
votes
2answers
1k views

Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?

What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations. The bcp route for a 73.5 GB file filled up ...
4
votes
2answers
8k views

BCP woes: Unexpected EOF encountered in BCP data-file

I have a table defined as such: CREATE TABLE [dbo].[IpMetadata]( [StartIp] [bigint] NOT NULL, [EndIp] [bigint] NOT NULL, [CountryCode] [char](10) NOT NULL, [ProxyType] [varchar](50) ...
0
votes
1answer
984 views

Sql server bulk insert remove text qualifier

I have a flat file having hundred thousand of records separated by special kind of character, I have used format file for removing that text qualifier. Works fine with comma separated file. Note: I ...
1
vote
1answer
3k views

Bulk insert top 1000 rows from a big flat file

I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?
-1
votes
1answer
238 views

Close BCP window without seeing it finish

A user executes the following bcp command locally to import data into SQL Server database. bcp TEST_DB.dbo.TEST_TABLE IN TEST_TABLE.dat -N -T Generally, the command prompt window may display ...
3
votes
1answer
667 views

bcp error: date invalid date format

I'm a bcp newbie. I want to insert a large file. I'm having a hard time uploading datetime format. Table SQL: "Create Table dbo.my_test ( " + \ "transaction_id BIGINT ," + \ "transaction_no ...
1
vote
0answers
1k views

bcp command inserts 0 rows copied

I'm a bcp newbie and I want to insert a large file. After bunch of errors I made the following toy example, that I'm trying to use in order to build a baseline that I can build on. Table SQL: "...
1
vote
2answers
747 views

Export data using bcp to deal with corrupted data

I am getting consistency errors while running DBCC CHECKDB on a table that has corrupted rows on SQL Server 2014. I am running into corruption errors after the first 30,000 rows (total 1.4 million (14 ...
2
votes
1answer
791 views

Can SQL Server's BCP output to multiple gzip compressed files?

I've been looking into bcp.exe on SQL Server and am wondering if its abilities are limited to what the documentation actually says... Is there no native way to output to multiple files? And ...
0
votes
1answer
728 views

Error in “BCP IN” operation

I have a table in 2 different database. In Database_A, there is a table created as: CREATE TABLE tbl_Employee( E_Id int null ,E_Name varchar(24) not null ,Joining_Date datetime not null ...
1
vote
0answers
383 views

Issue while using BCP

I am trying to add data from a text file into SQL Server Database Table. Sample Data from text file (I donot know how to attach file here so added image sample) : I first used Import/Export option ...
2
votes
1answer
761 views

Insert more rows at a time using bcp

User is using the bcp command to load data from a Linux command prompt. As of now it is loading 1000 rows at a time. There are 9970001000 total rows to send to SQL Server. If he wants to insert more ...
0
votes
2answers
2k views

Commands creating format files to import txt file into sql server

I'm trying to create a format file using bcp utility following the instructions on this page: Using a format file to bulk import data https://technet.microsoft.com/en-us/library/ms178129(v=sql.105)....
0
votes
1answer
148 views

Bulk exporting SQL Server 7 data and bulk importing it to SQL Server 2008 R2

Like the title suggests, I want to bulk export data within an sql server 7.0 database into a file and then import it to an sql server 2008 r2 database, I did the following, I first typed this command ...
0
votes
1answer
616 views

Use Distinct With Cast As XML

Can you run Distinct() on XML? I ask because if you look at this garbage data, it produces this error output SQLState = 37000, NativeError = 402 Error = [Microsoft][SQL Server Native Client 10....
1
vote
1answer
82 views

BCP To Show Full Text Values

I have a long varchar field that I am exporting to a csv with BCP. The export goes as it should, however in the actual cell itself the csv file shows X.XXXXXX+13 instead of XXXXXXXXXXXXXX Am I ...
0
votes
1answer
150 views

Problem with bcp

I am trying to load my table with some data using bcp. Below is the bcp command that I am using bcp Job.DBO.FileWatch in ABC.txt -C -t\t -S server\rept -T -f bcp1.fmt The schema of the FileWatch ...
4
votes
1answer
2k views

BCP export more rows per batch to file (queryout)

I am using Windows 10 and running this straight from the command prompt (Administrator). Is there a way to change the row count being output to a text file? BCP "SELECT * FROM db.dbo.table WHERE ...
0
votes
2answers
3k views

BCP code to extract a large amount of data from MS SQL Server

I have been using Python to perform a SQL query and retrieve data from the previous date every day since June and load it into a Postgres database which is local. Each date has about 2 million rows ...
4
votes
0answers
722 views

BCP neither gives results nor outputs anything when using valid statements but it does throw errors when passing invalid parameters

I have to use bcp command-line tool to export data from an SQL Server database to a file in a Red Hat server. I am (apparently) using valid statements but bcp is not producing any kind of output/...
2
votes
0answers
174 views

Image Data Type Export Different with BCP vs SSIS

I have a Stored Procedure process(scheduled SQL Job) created using BCP to export PDF file data from a image datatype to a PDF file on a network share. Due to a client request, I'm wanting to ...
0
votes
3answers
2k views

Error: Invalid object for global temporary table

I'm new to stored procedure & my application requirement is to collect data in global temporary table & bulk copy using bcp. I'm calling stored procedure, which copies data into global ...
1
vote
1answer
643 views

How to use Case Statement with BCP

I am using BCP as follows, bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n This works 100%. I have omitted the rest of it because it is just server, user, password ...
2
votes
1answer
2k views

BCP utility:“Copy direction must be either 'in', 'out' or 'format'”

I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error: Copy ...
0
votes
1answer
524 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 ...
2
votes
1answer
2k 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 ...
3
votes
1answer
707 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(...
4
votes
1answer
3k 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 ...