Click here to monitor SSC
  • Av rating:
  • Total votes: 304
  • Total comments: 62
Nigel Rivett

Creating CSV Files Using BCP and Stored Procedures

20 November 2006

This article focuses on the use of the Bulk Copy Program (BCP) to create CSV files. Although it is possible to create a CSV file using DTS or SSIS, using BCP is often simpler and more efficient.

I use master..sysobjects as an example table to extract.

Create a simple CSV file

The simplest way to copy data from a database table to file is to use the basic BCP command:

BCP master..sysobjects out c:\sysobjects.txt -c -t, -T –S<servername>

The basic format for the BCP command for creating a CSV file is as follows:

BCP <table> out <filename> <switches>

The switches used here are:

  • -c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
  • -t override the field terminator with ","
  • -T use a trusted connection. Note that U –P may be used for username/password
  • -S connect to this server to execute the command

Note that, like DTS/SSIS, BCP is a client utility, hence you need to supply the connection information.

For transfer of data between SQL servers, in place of –c, use –n or -N for native data format (-N = Unicode). This is much faster and avoids data conversion problems. Please refer to the previous BOL link for the complete format of the BCP command.

As BCP is a command line utility it is executed from T-SQL using xp_cmdshell. Create a directory called BCP on your c: drive and execute:

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Other field and row delimiters

Often, character data includes commas which will be interpreted in the file as a field terminator. A simple way to cater for this is to use a different field delimiter which does not appear in the data. Commonly used characters are pipe (|):

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c –t| -T -S'
+ @@servernameexecmaster..xp_cmdshell@sql

And caret (^):

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c –t^ -T -S'
+ @@servernameexec master..xp_cmdshell @sql

The terminators are not limited to a single character, so if necessary you can use (|^):

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c –t|^ -T -S'
+ @@servernameexec master..xp_cmdshell @sql

Note that this will increase the size of the file and so slow down the import/export. Another way to cater for embedded commas is to "quote encapsulate" character data – more about that later.

It is unusual to need to change the row terminator from the default of crlf (carriage return, line feed) but occasionally you will need the cr or lf on its own.

To do this use the hex value cr = 13 = 0x0D, lf = 10 = 0x0A. If you can't remember these values they are easily obtained, as follows:

select cr = ascii('')select lf = ascii(right('',1))

To use these in the BCP command for the row terminator the –r switch is used:

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
    
c:\bcp\sysobjects.txt -c -t, -r0x0D -T -S'
+ @@servernameexec master..xp_cmdshell @sql

When the resulting file is opened in notepad the row terminators will not end the line – the character should appear as a non-ASCII character (a little oblong). If opened or copied to query analyser (or management studio) these will be interpreted as line breaks and the file will be more readable.

The terminator characters can be encapsulated with double quotes in the command to allow for space. I like to do this for anything other than a single character delimiter. The following example gives a crlf row terminator and | ^ field terminator.

declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out
                 c:\bcp\sysobjects.txt -c -t"| ^" -r"0x0D0A" -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Formatting the extracted data

If you do not require all the columns/rows, or you wish to alter the data, a simple method is to create a view. This can be used in the BCP command in the same way as a table. The following view allows you to extract just the name, the formatted create date and time and then order the results according the date created (most recent first):

use tempdb
go
create view vw_bcpMasterSysobjects
as
  select top 100 percent
      name ,
      crdate = convert(varchar(8), crdate, 112) ,
      crtime = convert(varchar(8), crdate, 108)
   from master..sysobjects
   order by crdate desc
go
declare @sql varchar(8000)
select @sql = 'bcp tempdb..vw_bcpMasterSysobjects out
                 c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Now we can quote encapsulate the extracted data by including the formatting in the view:

use tempdb
go
create view vw_bcpMasterSysobjects
as
   select top 100 percent
      name = '"' + name + '"' ,
      crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
      crtime = '"' + convert(varchar(8), crdate, 108) + '"'
   from master..sysobjects
   order by crdate desc
go
declare @sql varchar(8000)
select @sql = 'bcp tempdb..vw_bcpMasterSysobjects out
                 c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Note:
Quote encapsulation can also be performed by use of a format file.

Complete control – stored procedures

If you examined the BCP syntax in BOL, you might have noticed that it is possible to extract from a query by using the queryout keyword in place of out.

So, for example, an equivalent but neater version of the previous code extract would place the ORDER BY clause in the BCP statement rather than the view:

use tempdb
go
create view vw_bcpMasterSysobjects
as
   select
      name = '"' + name + '"' ,
      crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
      crtime = '"' + convert(varchar(8), crdate, 108) + '"'
   from master..sysobjects
go
declare @sql varchar(8000)
select @sql = 'bcp "select * from tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Of course, strictly speaking, the view was not necessary at all as the query could have been included in the BCP statement but that can get difficult to maintain.

The queryout method allows you to BCP from the result of a stored procedure, which opens up a lot of possibilities and offers a lot of control over the file format. For anything other than a simple table extract I would tend to use this method rather than a view. I would also format each line within the stored procedure. This means that the formatting can be tested independently from the file creation.

Employing this technique, the above extract becomes:

use tempdb
go
create proc s_bcpMasterSysobjects
as
   select   '"' + name + '"'
            + ',' + '"' + convert(varchar(8), crdate, 112) + '"'
            + ',' + '"' + convert(varchar(8), crdate, 108) + '"'
   from master..sysobjects
   order by crdate desc
go
declare @sql varchar(8000)
select @sql = 'bcp "exec tempdb..s_bcpMasterSysobjects"
queryout c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

More complex formatting

We can now change the format of the extracted data to anything we require. For example, to include column names at the top and a rowcount at the end:

use tempdb
go
create proc s_bcpMasterSysobjects
as
      set nocount on
     
      create table #a (seq int, crdate datetime, s varchar(1000))
      -- header - column headers
      insert      #a (seq, crdate, s)
      select      1, null,
                  '"name","crdate","crtime"'
     
      -- data
      insert      #a (seq, crdate, s)
      select      2, crdate,
                          '"' + name + '"'
                  + ',' + '"' + convert(varchar(8), crdate, 112) + '"'
                  + ',' + '"' + convert(varchar(8), crdate, 108) + '"'
      from master..sysobjects

      -- trailer - rowcount
      insert      #a (seq, crdate, s)
      select      3, null,
                  'rowcount = ' + convert(varchar(20),count(*)-1)
      from #a

      select      s
      from  #a
      order by seq, crdate desc
go

This stored procedure will format the data as required, but when it is run via the BCP command it receives an error:

"Invalid object name '#a'"

Using the profiler you can see that when using queryout, the query is called twice. BCP tries to get the result set format by calling the stored procedure with fmtonly. This does not execute the stored procedure but returns the format of the resultset. Unfortunately, it does not work if the stored procedure creates and uses a temp table. This problem can be circumvented by including a set fmtonly off command in the execution:

declare @sql varchar(8000)
select @sql = 'bcp "set fmtonly off exec tempdb..s_bcpMasterSysobjects"
queryout c:\bcp\sysobjects.txt -c -T -S'
+ @@servername
exec master..xp_cmdshell @sql

Note that the column terminator is not used since the resultset is a single column.

---Editor's Note---
This was the only piece of code that I failed to get working. The stored procedure executed fine outside the BCP, but when I tried to run the above command I received an error:

SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error
NULL

The author was unable to replicate this error. Is anyone aware of what might be causing this on my machine?
---End Editor's Note---

Look at this execution using the profiler and you will see set fmtonly off followed by set fmtonly on. There is a potential problem with this though: it means that the stored procedure is executed twice, once for the format and once to extract the data – due to the fmtonly setting, both these calls will produce a resultset. This needs to be taken into account when considering how long the process will take and its impact on your system.

As the stored procedure is executed twice it should not contain any updates of permanent data. Particularly if data is flagged as exported then the flagging will be performed on the first call and no data will be extracted for the second call. All in all, this method should be used with caution and comments (warnings) placed in the stored procedure for the aid of future maintenance.

To get round all of these problems, use the stored procedure to insert into a table then, from the BCP, extract from that table. This gives you added features that can be valuable on systems that are performing a lot of extracts:

  • It gives a record of the data extracted – if the file is lost it can be recreated.
  • The data extracted can be presented to a user for viewing from the database.
  • If there are problems with the file, the support can see the data without needing access to the file.

For this process, the format stored procedure, s_bcpMasterSysobjects, will insert into the table using an identifier and the BCP will extract using the same identifier. This process can be controlled be (a control) stored procedure which would allocate the identifier and pass it to the format stored procedure which inserted the extracted data into a table with that identifier. It then calls another stored procedure or in-line code to create files (via BCP) for each identifier which is not marked as complete. The identifier is flagged as complete after the file creation. In this way the file creation becomes independent of the extract of the data.

This identifier can be passed to both processes from a controlling stored procedure or the format stored procedure can get the next available identifier and the extract can flag the identifier as extracted – so it extracts everything that has not been extracted, allowing the export to be independent of the extract.

Which technique you use will depend on your system. The following code demonstrates the use of a controlling stored procedure:

use tempdb
go
create table Extract
      (
      Extract_ID  int ,
      Seq1        varchar(100) null ,
      Seq2        varchar(100) null ,
      Seq3        varchar(100) null ,
      Data        varchar(8000) ,
      InsertDate  datetime default getdate()
      )
go
create proc s_bcpMasterSysobjects
@ExtractID int
as
declare
@rowcount int
      set nocount on
     
      -- header - column headers
      insert      Extract (Extract_ID, Seq1, Data)
      select      @ExtractID,
                  '01' ,
                  '"name","crdate","crtime"'
     
      -- data
      insert      Extract (Extract_ID, Seq1, Seq2, Data)
      select      @ExtractID,
                  '02' ,
                  + convert(varchar(100), '99990101' - crdate, 121) ,
                          '"' + name + '"'
                  + ',' + '"' + convert(varchar(8), crdate, 112) + '"'
                  + ',' + '"' + convert(varchar(8), crdate, 108) + '"'
      from master..sysobjects

      select @rowcount = @@rowcount
     
      -- trailer - rowcount
      insert      Extract (Extract_ID, Seq1, Data)
      select      @ExtractID,
                  '03' ,
                  'rowcount = ' + convert(varchar(20),@rowcount)
go
create proc s_Extract
@ExtractID int
as
      select      Data
      from  Extract
      where Extract_ID = @ExtractID
      order by Seq1, Seq2, Seq3
go

Now the data is extracted via:

Create table ExportLog (Export_id int, Status varchar(20))

Insert ExportLog select 25, 'Extracting'
exec
tempdb..s_bcpMasterSysobjects 25
update
ExportLog set Status = 'Exporting' where Export_id = 25
declare
@sql varchar(8000)
select @sql = 'bcp "exec tempdb..s_Extract 25"
queryout c:\bcp\sysobjects.txt -c -T -S'
+ @@servername
exec master..xp_cmdshell @sql
update
ExportLog set Status = 'complete' where Export_id = 25

You can view the data extracted via:

exec tempdb..s_Extract 25

Alternatively, you can use the following, which will also give the date that the data was extracted:

select * from tempdb..Extract order by Seq1, Seq2, Seq3

Note:
For the extract I have given three sort columns Seq1, Seq2, Seq3 which are all order ascending. This means that the extracting stored procedure needs to place the values in ascending order (see the date manipulation). This could have ascending and descending sort columns or the use could be dependent on the extract type.

For a system which is performing a lot of small exports I will usually implement this process, and a similar process for imports.

Extracting all tables from a database

This is usually performed to transfer data between servers/databases and so should use the native data format. The easiest way is to create the BCP statements from a query then execute them:

select 'exec master..xp_cmdshell'
            + ' '''
            + 'bcp'
            + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
            + ' out'
            + ' c:\bcp\'
            + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.bcp'
            + ' -N'
            + ' -T'
            + ' -S' + @@servername
            + ''''
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

The result of this will be a series of BCP statements of the form:

exec master..xp_cmdshell 'bcp tempdb.dbo.Extract out
c:\bcp\tempdb.dbo.Extract.bcp -N -T –S<servername>'

which will extract all tables in the database. To import just change the "out" to "in".

Summary

We have seen how to use BCP to perform a simple extract of a table to a CSV file, how to perform slightly more complex extracts using a view, extracting using a stored procedure to format the data and a system which allows the data to be extracted and saved in a table and viewed/exported from there.

Nigel Rivett

Author profile:

Nigel spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server for about 10 years from v4.2 to v2005, and was awarded Microsoft MVP status in 2003. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Nigel Rivett

Rate this article:   Avg rating: from a total of 304 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Nigel does it again!
Posted by: Anonymous (not signed in)
Posted on: Monday, November 20, 2006 at 3:54 PM
Message: I loved this article. Heavens only knows how many articles I've read on BCP but here was definitely something new here. I'd never thought of the 'set fmtonly' issue, and the verious work-rounds.


Subject: Using BCP
Posted by: Phil Factor (view profile)
Posted on: Tuesday, November 21, 2006 at 8:23 AM
Message: Nigel,
Great article.
BCP is a wonderful trip down memory lane. Are there, as I suspect, good performance reasons for using BCP over the other tools that Microsoft now provide? It certainly is guaranteed to work on any version!

Subject: re: Using BCP
Posted by: nigelrivett (view profile)
Posted on: Wednesday, November 22, 2006 at 11:50 AM
Message: bcp should be the fastest method for transferring text files (or bulk insert for importing).

I would always use it for small files for speed of development. If there are a lot of files to deal with then the package load time (especially with SSIS) can be the biggest overhead.

There are times that bcp isn't so useful though. If run from a stored proc then it is easy to run it on the server but not so easy from a remote machine (not that difficult though) - but then you would need a method to run the other applications too.
I try to stay away from format files as I like to keep everything in one place.
If data needs to be transformed then that may be simpler, faster or take less resources in a package than using bcp.

One reason I dislike DTS is that people tend to include flow control and unrelated tasks in a single package and lose the separation of processes.
SSIS has the same problem but is a more complete application - just needs someone with a bit of system design experience to oversee what is being done.

Subject: Using BCP
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 29, 2006 at 5:08 PM
Message: Let me qualify my comment by saying I am a SQL developer and a big fan of using BCP. It is a fast and customizable way to move data around. I can do much more with the data from BCP than I can with most tools.

However, every DBA that I have worked with says BCP leaves open a gaping security whole. Applications and users that have access to xp_cmdshell manipulate the machine that is running the system. The comment I hear most often is that nobody but the NA and DBA need access to the machine the database runs on.

Your thoughts?

Subject: Nice one
Posted by: Anonymous (not signed in)
Posted on: Monday, December 04, 2006 at 9:11 AM
Message: Great article!

The only drawback of BCP exporting is that sometimes you'd need column names for the data and BCP doesn't give you that.

I've coverd this a while ago:
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Mladen

Subject: reply
Posted by: nigelrivett (view profile)
Posted on: Tuesday, December 05, 2006 at 9:40 AM
Message: >> The only drawback of BCP exporting is that sometimes you'd need column names for the data and BCP doesn't give you that.

That's covered in the article - one of the formatting SPs gives column names in the first row.

>> The comment I hear most often is that nobody but the NA and DBA need access to the machine the database runs on.
That's true - in a production environment this sort of thing would usually be run from the scheduler using sa (or some user with sa permissions).
Also remember that bcp is a client utility - you can connect to a remote server to save the file locally - although that will be slower due to the network. Often useful to run the bcp on a local sql server but connect to a remote server for the data.

Subject: I think something is missed
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 05, 2006 at 12:26 PM
Message: How about doesnt have a field terminator?

The terminators are not limited to a single character, so if necessary you can use (|^)

But in case i dont want terminators , how can i do?

C. Lages

Subject: No field terminator
Posted by: nigelrivett (view profile)
Posted on: Wednesday, December 06, 2006 at 4:35 AM
Message: If by that you mean fixed field length then you can just use the format sp to concatenate the fields with no separator.

Subject: Bad solution
Posted by: Anonymous (not signed in)
Posted on: Wednesday, January 10, 2007 at 12:34 PM
Message: Using xp_cmdshell and bcp in a sproc is not a good solution. It's not secure. Learn how to use DTS or write a .Net program to do this. PUshing data out of the server with a sproc is bad. Massive security issue.

Subject: re: bad solution
Posted by: Phil Factor (view profile)
Posted on: Thursday, January 11, 2007 at 12:29 PM
Message: The systems I write make use of xp_cmdshell, and I use routines very like Nigels. In fact I pinched one or two from him.
I use them in what I have always believed to be a perfectly secure way. The task goes into a 'queue' (simply a table treated as a LIFO) which is polled by a stored procedure called by the SQL Server Scheduler. The particular step in the job, or the entire job can be run as a specified user with the rights, and just the rights, necessary to perform the operation. If an operation needs to be performed, it is done so under, or as, that user. Only one action can be performed, and it is a simple matter to ensure that the parameters that are provided in the queue conform to legal specifications. The user is, of course, only used by the scheduled step or job.
I'm struggling to work out how this is insecure. Can you please elaborate, as I'd hate to have to stop using methods like this, when I don't understand the security loophole.

Subject: Bad solution
Posted by: nigelrivett (view profile)
Posted on: Saturday, January 13, 2007 at 9:32 AM
Message: Think you're misunderstanding the concepts.
bcp is just a tool (a command line executable) used to extract the data. I am calling it using xp_cmdshell here because it is usually simplest. Of course in some environments that is not avalable (in the same way that dts/ssis might not be available for exactly the same reasons).

There is usually a way around this e.g. set up another server to run the aplication (whether it's bcp, dts, ssis).

Not that these solutions are all client applications and all have the same limitations - they can all be executed by xp_cmdshell but all of them can be executed by other means so that is not an issue.

The point is to use the method that is best for the objective. Often people will automatically go for dts or ssis (not usually a .net app but...) whereas it can be a lot simpler and faster using other methods. Sure in some environments it may be more complicated to set up and that may change the decision but the important think is to know what is available and the pros and cons, and not to dismiss a solution just because it might not be the best in another situation.

Dts I feel is not a particulaly good product and vastly overused by application developers who want to dabble in database soltions.
SSIS I feel to be a lot better but again tends to be used as an isolated application builder rather than as one of a suite of tools to build a system.

Subject: bcp column display in .csv
Posted by: Anonymous (not signed in)
Posted on: Friday, February 09, 2007 at 5:29 AM
Message: Using bcp command how i display the output data with the column names?

Subject: Import text file data into sql server table
Posted by: Anonymous (not signed in)
Posted on: Saturday, February 17, 2007 at 2:50 PM
Message: Hi,
I read your article and i'm much impressed reading it. I need your expert suggestion/help in solving my task. I need to import around 200 text files data into sql server table. Can you suggest me how this can be done. I really appreciate your help in this regard.
Thanks,
Hemal
hemal_s_shah@yahoo.ca

Subject: SQL to Access
Posted by: Anonymous (not signed in)
Posted on: Sunday, February 25, 2007 at 6:13 AM
Message: Say I have to export data from MS-SQL to an Access database. How do I do that? I know that you can use an intermediary text file but the problem is that I don't know of a method that will import that file in an authomated manner (not manually) into the Access database.

I ask because in my situation I cannot use a DTS package because the client that has to use the export/import facility doesn't have installed the SQL server and the DtsRun utility for that matter.

Subject: SQL to Access
Posted by: Anonymous (not signed in)
Posted on: Monday, February 26, 2007 at 8:12 AM
Message: Say I have to export data from MS-SQL to an Access database. How do I do that? I know that you can use an intermediary text file but the problem is that I don't know of a method that will import that file in an authomated manner (not manually) into the Access database.

I ask because in my situation I cannot use a DTS package because the client that has to use the export/import facility doesn't have installed the SQL server and the DtsRun utility for that matter.

Subject: Thanks Nigel ..
Posted by: Anonymous (not signed in)
Posted on: Tuesday, March 06, 2007 at 12:47 AM
Message: One of the best articles I've ever read.
Thanks Nigel.
You have kept the name 'simple-talk'.

Subject: Good article
Posted by: Anonymous (not signed in)
Posted on: Tuesday, March 27, 2007 at 9:04 PM
Message: I ended up finding this article by searching for "SQLState = HY010, NativeError = 0" and catching the editor's note. My error was using a #tmp file. I repeated the editor's error when I tried your fix. Fixed that problem by making the #tmp a "(select ... from ...) a" result set.

Ran into another problem - bcp will only accept the first result set output. (Produced only 1 output result set at a time, but the second one blew up.) Had to split the sproc into two different sprocs.

Subject: Error
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 25, 2007 at 8:00 AM
Message: SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
NULL

Im getting this message when I try to run Procedutre in Server.. any help


Subject: re - Error
Posted by: nigelrivett (view profile)
Posted on: Wednesday, April 25, 2007 at 12:40 PM
Message: What are you trying to run - can you post the command.
The error is saying that you cannot connect to the server.
Do you have a space or suchlike in the server\instance name?

Can you post the command that is being executed.
You can use the profiler to see if the server is being contacted and refusing the login.

Subject: Just doesn't work
Posted by: Anonymous (not signed in)
Posted on: Sunday, May 06, 2007 at 11:30 AM
Message: Dear Nigel,

You've done a wonderful job laying out everything and I think you explain everything very clearly.

However, just doesn't work for me. I'm a newbie and using SQL 2005 express and the follwoing just doesn't work for me. See:

use s300
go
if exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vw_XRef')
drop view vw_XRef
go

create view vw_XRef
as
SELECT *
FROM s300.dbo.UAC5DPS3
go

declare @sql varchar(1000)
select @sql = 'bcp s300..vw_XRef out
c:\BeneSys\xRef.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

Add I receive the following message:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

Can anyone lend me some direction?

Subject: An error message when there is a invalid object while creating Stored Procedure.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 09, 2007 at 2:26 AM
Message: Is there any keyword to display an error message when there is a invalid object while creating Stored Procedure.

Subject: Error Generation at time of creating Store Procedure
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 09, 2007 at 2:38 AM
Message: Can we display an error message if a Stored Procedure contains invalid objects at the time of creating the stored procedure.

Subject: Solution for "Function sequence error" in editor's note
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 09, 2007 at 6:35 AM
Message: I got the same error as in the editor's note:

SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error
NULL

I able to solve it by turn on the Database Mail under Surface Area Configuration for Features

Here are the details:
http://yytechblog.com/2007/05/09/function-sequence-error-in-bcp-with-xp_cmdshell/

Subject: Excellent
Posted by: Anonymous (not signed in)
Posted on: Monday, May 14, 2007 at 9:43 PM
Message: Thanks so much - learned a few very useful techniques!

Subject: help needed
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 23, 2007 at 2:48 AM
Message: Is there any possiblity to increase the length of the Work Sheet in Excel or is there any possiblity to have a different name for the Work sheet?

Subject: Excellent
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 23, 2007 at 6:11 PM
Message: Thanks so much - learned a few very useful techniques!

Subject: Unknown argument 'ûS...'
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 23, 2007 at 6:14 PM
Message: I get the following error with the -S parameter. Any idea? MySqlServer is my sql server name.

C:\>BCP Northwind..Employees out c:\Temp\bcp_csv_
northwindEmployees.txt -c -t, -T -SMySqlServer

Unknown argument 'ûSMySqlServer' on command line.

Subject: Excellent
Posted by: Anonymous (not signed in)
Posted on: Friday, May 25, 2007 at 4:03 AM
Message: I am trying to create a .xls file using bcp command and i am successfully able to create. But i need create a multiple sheets in a single .xls file.

Thanks in Advance

Subject: how you can load an XML file directly into sql table
Posted by: Ramesh babu (not signed in)
Posted on: Friday, June 01, 2007 at 4:55 AM
Message: plz help me

Subject: Solution for "Function sequence error" in editor's note
Posted by: Dmytro Andriychenko (not signed in)
Posted on: Monday, June 04, 2007 at 8:52 AM
Message: Had a problem with this error:

SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error

coming up when using bcp. The problem seem to occure only with SQL 2005 version of bcp.exe. The version of bcp.exe from SQL 2000 runs fine! What one can do is simply use the old version until MS comes up with a fix. All you need is bcp.exe and bcp.rll from SQL 2000 installation.

Subject: re: how you can load an XML file directly into sql table
Posted by: Phil Factor (view profile)
Posted on: Monday, June 04, 2007 at 3:13 PM
Message: I've dealt with this question in the forums:

http://www.simple-talk.com/community/forums/ShowThread.aspx?PostID=30267#30796

Subject: Column name
Posted by: Dale (not signed in)
Posted on: Tuesday, June 12, 2007 at 1:33 PM
Message: Is the method covered in the article the only one that can be used to show the column names at the top of the file?

Are there simpler way? Using the presented method would be too heavy in the long run.

Subject: Export SQL to XML
Posted by: russt (not signed in)
Posted on: Wednesday, July 04, 2007 at 10:54 AM
Message: Hi,

I am new to SQL stored procedure and I would like to know how to export save an SQL table to XML using bcp?

Cheers.

Subject: Export SQL to XML
Posted by: russt (not signed in)
Posted on: Wednesday, July 04, 2007 at 10:56 AM
Message: Hi,

I am new to SQL stored procedure and I would like to know how to export save an SQL table to XML using bcp?

Cheers.

Subject: re: Export SQL to XML
Posted by: nigelrivett (view profile)
Posted on: Thursday, July 05, 2007 at 5:23 AM
Message: You can format the sql in the stored proceudure or have a look at sp_makewebtask

Subject: re: Column name
Posted by: nigelrivett (view profile)
Posted on: Thursday, July 05, 2007 at 5:25 AM
Message: Try it - you might be surprised at how efficient it is.

Otherwise have a look at SSIS (or dts) or osql.

Subject: Thankyou Nigel Rivett
Posted by: Pastone (not signed in)
Posted on: Sunday, July 29, 2007 at 3:28 AM
Message: Thanks for a good article on BCP. It has given me some ideas for a project I'm currently working on.

Subject: what is BCP
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 18, 2007 at 1:57 AM
Message: I want to know wat is BCP and how it is used to create CSV file frm Table
Pls if u give code how to use it

Subject: what is BCP
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 18, 2007 at 2:45 AM
Message: I want to know wat is BCP and how it is used to create CSV file frm Table
Pls if u give code how to use it

Subject: re what is BCP
Posted by: nigelrivett (view profile)
Posted on: Friday, September 07, 2007 at 6:53 AM
Message: BCP is bulk copy program.
>> how it is used to create CSV file frm Table
Well that's what the article is about and there's lot's of code examples there.

If you don't want to use a query then just change the quetry to the table name and use out instead of queryout.
And have a look in books online.

Subject: oops
Posted by: nigelrivett (view profile)
Posted on: Friday, September 07, 2007 at 7:01 AM
Message: sorry about the apostrophe and t and missing o.
And the syntax I guess.

Subject: column separator quoting
Posted by: Curtis (view profile)
Posted on: Friday, September 14, 2007 at 4:18 PM
Message: A very helpful article. Thank you.

However, I think I missed something. How does one gracefully deal with quoting embedded separator characters? Some of the fields I'm dumping are free-text entry, so they may have commas in them, but I need to produce a .csv file for each table to feed to mysqlimport. The db schema changes periodically, so I don't want to have to create (and maintain) a view for every table I'm exporting, just to escape every column. Most .csv creation tools automatically quote and escape values... how do I get bcp to behave nicely?

Thanks?
Curtis

Subject: Vertical bar doesn't work
Posted by: Steve H (not signed in)
Posted on: Sunday, September 30, 2007 at 11:15 AM
Message: Good day.

I just saw this article yesterday and it is precisely what I need. I am trying to work with the limitations of SSIS and need to use the bcp out using the sp_cmdshell. I want to use a single stored procedure to process x number of data exports by passing in the parameters.

I have constructed a test and if I use a comma as the delimiter (or for that matter, almost any character, it works like a dream. However, if I use a vertical bar as follows:

Declare @outputstring varchar(8000)
SELECT @outputstring = 'bcp sds.dbo.TSDS_VND_EXP out ' +
'\\Euterpe\data\gap\SDS_Data\essbase_sourcing\Sourcing_Vendor.txt -c -t| -T -S'+ @@servername

exec master..xp_cmdshell @outputstring

I get the following output error:

'-T' is not recognized as an internal or external command,
operable program or batch file.
NULL

Now, I remember in the dark recesses of my mind that I had this difficulty many years ago and that there is an OS level setting that needs to be changed. But I have no recollection of what that might be.

Anyone have any ideas?

Thank you.

Subject: pipe char
Posted by: Jesse (not signed in)
Posted on: Thursday, October 04, 2007 at 3:05 AM
Message: try -t^| instead of -t|

| is a special char in the shell, used for pipes, so it has to be escaped.

Subject: no file found
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 28, 2007 at 1:56 AM
Message: i have this code..

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\trial.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -Usa -Ppassword -c'

EXEC master..xp_cmdshell @bcpCommand



.......but why cant i find the output file (trial.txt)? what is the problem?

thanks.

Subject: great article
Posted by: Anonymous (not signed in)
Posted on: Monday, December 24, 2007 at 5:56 AM
Message: Thanks for useful examples.

Subject: set fmtonly
Posted by: Hawkins Dale (not signed in)
Posted on: Tuesday, January 08, 2008 at 9:46 AM
Message: This article saved my butt.

I had a bcp process in production that was creating the expected extract properly. I had to add a new column, which meant rewriting the stored procedure so as to use a temp table (a table variable, actually), and the stupid thing broke: it would only return the first column.

Sticking SET FMTONLY OFF in front of the EXEC fixed it.

Thanks!

Subject: BCP
Posted by: Anonymous (not signed in)
Posted on: Thursday, January 10, 2008 at 9:58 AM
Message: Hey. I'm having issues with the BCP statement. Here is my statement

bcp "SELECT Number from ACHAData..Number" queryout c:\Contact.txt -c -T

It sits in stored procedure(which has no other lines of code ocntained in it) I get the following error

Incorrect syntax near 'queryout'.

Any thoughts?

Subject: delimiter
Posted by: hizakemi (view profile)
Posted on: Tuesday, February 12, 2008 at 8:01 AM
Message: Hello--

I need to Ç as my field delimiter and Æ as my row delimiter in bcp script. I use the script below but I dont get the right outcome- HELP

bcp databse.dbo.table out table.dat -S myserver -U myserid -P mypassword -c -tÇ -r"Æ"

Josephine

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 19, 2008 at 2:30 PM
Message: Thanks! For those who are not technical enough, you may look for www.sqlscripter.com to export data to Csv.

Subject: Is it possible to add column heading with BCP?
Posted by: chrissclee (view profile)
Posted on: Tuesday, November 11, 2008 at 1:40 PM
Message: Hi,

Is there way to include column in CSV file when using BCP? Thanks.

Chris

Subject: Convert CSV to Text file
Posted by: Ramanathan (view profile)
Posted on: Wednesday, November 19, 2008 at 6:53 AM
Message: hi

I thank to you for explaining a .csv file convert to .txt with some delimiters vise versa. good works.

with regards.
K.Ramanathan
ramji_ramanathan@yahoo.com

Subject: Very well done!
Posted by: Jeff Moden (view profile)
Posted on: Friday, February 27, 2009 at 11:43 AM
Message: I was trying to same myself some time by looking for examples of the different uses of the BCP command to show the folks at work. I believe I've just found one of the most comprehensive set of examples I could have asked for. The big bonus was learning the trick to address temp tables... that's gonna be a huge time saver for me.

Thanks for the great article, Nigel.

Subject: No file 2
Posted by: JusticeV (view profile)
Posted on: Tuesday, December 22, 2009 at 11:51 AM
Message: Having the same issue another here had where I get:

Add I receive the following message:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

But no file created.

Any help would be very appreciated

Subject: using bcp with temp files
Posted by: john.campbell (view profile)
Posted on: Wednesday, February 10, 2010 at 8:22 AM
Message: I have found that in order to bcp out a #temp file from a sp the temp table needs to be declared as a global temp table ##temp. The scope of a #temp table is local and only readable in that query window or sp, whereas the bcp command is a dos command and does not have access to the #temp file, whereas it can the ##temp file just fine. When using this I always search for the ##temp file at the beginning of the sp and kill them, since they can hang around if there are communications problems between servers.
Great article!

Subject: Is there a workround
Posted by: mbouarroudj (view profile)
Posted on: Wednesday, February 10, 2010 at 11:33 AM
Message: Hi Nigel and thanks for this vey nice article

Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?

Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?

Thanks

Subject: Is there a workround
Posted by: mbouarroudj (view profile)
Posted on: Wednesday, February 10, 2010 at 12:40 PM
Message: Hi Nigel and thanks for this vey nice article

Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?

Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?

Thanks

Subject: Is there a workround
Posted by: mbouarroudj (view profile)
Posted on: Wednesday, February 10, 2010 at 2:30 PM
Message: Hi Nigel and thanks for this vey nice article

Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?

Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?

Thanks

Subject: Is there a workround
Posted by: mbouarroudj (view profile)
Posted on: Thursday, February 11, 2010 at 10:26 AM
Message: Hi Nigel and thanks for this vey nice article

Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?

Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?

Thanks

Subject: Column Names
Posted by: maxbrackett (view profile)
Posted on: Thursday, February 11, 2010 at 4:02 PM
Message: If column names are not going to be dynamic, this works nicely....

"SELECT ''Column1'', ''Column2'', ''Column3'' UNION SELECT Column1, Column2, Column3 FROM Table"

Thanks for the page, great help!
Max

Subject: About BCP, a question from SQL2005 Migration
Posted by: Lina (view profile)
Posted on: Monday, February 15, 2010 at 12:29 PM
Message: In our original DTS jobs, we use Stored Procedures and run BCP commands to Import/Export files with data manipulating steps. In our new SQL 2005 environment we can not have the permission for Admin and BCP. We can not use BULK INSERT neither because of the networking access.

I know we can use SSIS TRANSFORM, but I need to "chop" Stored Procedures to pieces (data manipulating steps) to insert the SSIS TRANSFORM task, we have about 400 hundreds Stored Procedures. It sounds crazy.

Do you think if there is any other ways to work for the situation ?

Thank you so much for your help.
Lina

Subject: Regarding error Function sequence error
Posted by: Prasoon.1983 (view profile)
Posted on: Tuesday, July 20, 2010 at 1:18 AM
Message: Hi,

When I used "set fmtonly off " with stored procedure as query to bcp, I got the same error "Function sequence error" but then I made the below changes to my SP to get it worked.

1. The furst statement in SP should be a SELECT statement.
2. Insert, Update statement should come after SELECT.
3. DECLARE or SET statements are fine before the SELECT.

With warm regards
Prasoon Kumar Gupta

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.