20 November 2006

Creating CSV Files Using BCP and Stored Procedures

Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing on a stored procedure approach that gives complete control of the format of the extracted data.

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:

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:

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 (|):

And caret (^):

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

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:

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

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.

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):

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

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:

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:

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:

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:

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:

Now the data is extracted via:

You can view the data extracted via:

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

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:

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

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 548597 times – thanks for reading.

Tags: , , , , , ,

  • Rate
    [Total: 322    Average: 4.1/5]
  • Share

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.

View all articles by Nigel Rivett

  • Anonymous

    Nigel does it again!
    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.

  • Phil Factor

    Using BCP
    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!

  • nigelrivett

    re: Using BCP
    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.

  • Anonymous

    Using BCP
    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?

  • Anonymous

    Nice one
    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

  • nigelrivett

    reply
    >> 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.

  • Anonymous

    I think something is missed
    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

  • nigelrivett

    No field terminator
    If by that you mean fixed field length then you can just use the format sp to concatenate the fields with no separator.

  • Anonymous

    Bad solution
    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.

  • Phil Factor

    re: bad solution
    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.

  • nigelrivett

    Bad solution
    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.

  • Anonymous

    bcp column display in .csv
    Using bcp command how i display the output data with the column names?

  • Anonymous

    Import text file data into sql server table
    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

  • Anonymous

    SQL to Access
    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.

  • Anonymous

    SQL to Access
    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.

  • Anonymous

    Thanks Nigel ..
    One of the best articles I’ve ever read.
    Thanks Nigel.
    You have kept the name ‘simple-talk’.

  • Anonymous

    Good article
    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.

  • Anonymous

    Error
    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

  • nigelrivett

    re – Error
    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 serverinstance 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.

  • Anonymous

    Just doesn’t work
    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:BeneSysxRef.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?

  • Anonymous

    An error message when there is a invalid object while creating Stored Procedure.
    Is there any keyword to display an error message when there is a invalid object while creating Stored Procedure.

  • Anonymous

    Error Generation at time of creating Store Procedure
    Can we display an error message if a Stored Procedure contains invalid objects at the time of creating the stored procedure.

  • Anonymous

    Solution for “Function sequence error” in editor’s note
    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/

  • Anonymous

    Excellent
    Thanks so much – learned a few very useful techniques!

  • Anonymous

    help needed
    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?

  • Anonymous

    Excellent
    Thanks so much – learned a few very useful techniques!

  • Anonymous

    Unknown argument ‘ûS…’
    I get the following error with the -S parameter. Any idea? MySqlServer is my sql server name.

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

    Unknown argument ‘ûSMySqlServer’ on command line.

  • Anonymous

    Excellent
    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

  • Ramesh babu

    how you can load an XML file directly into sql table
    plz help me

  • Dmytro Andriychenko

    Solution for “Function sequence error” in editor’s note
    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.

  • Phil Factor

    re: how you can load an XML file directly into sql table
    I’ve dealt with this question in the forums:

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

  • Dale

    Column name
    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.

  • russt

    Export SQL to XML
    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.

  • russt

    Export SQL to XML
    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.

  • nigelrivett

    re: Export SQL to XML
    You can format the sql in the stored proceudure or have a look at sp_makewebtask

  • nigelrivett

    re: Column name
    Try it – you might be surprised at how efficient it is.

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

  • Pastone

    Thankyou Nigel Rivett
    Thanks for a good article on BCP. It has given me some ideas for a project I’m currently working on.

  • Anonymous

    what is BCP
    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

  • Anonymous

    what is BCP
    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

  • nigelrivett

    re what is BCP
    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.

  • nigelrivett

    oops
    sorry about the apostrophe and t and missing o.
    And the syntax I guess.

  • Curtis

    column separator quoting
    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

  • Steve H

    Vertical bar doesn’t work
    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 ‘ +
    ‘\EuterpedatagapSDS_Dataessbase_sourcingSourcing_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.

  • Jesse

    pipe char
    try -t^| instead of -t|

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

  • Anonymous

    no file found
    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.

  • Anonymous

    great article
    Thanks for useful examples.

  • Hawkins Dale

    set fmtonly
    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!

  • Anonymous

    BCP
    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?

  • hizakemi

    delimiter
    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

  • Anonymous

    Thanks
    Thanks! For those who are not technical enough, you may look for http://www.sqlscripter.com to export data to Csv.

  • chrissclee

    Is it possible to add column heading with BCP?
    Hi,

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

    Chris

  • Ramanathan

    Convert CSV to Text file
    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

  • Jeff Moden

    Very well done!
    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.

  • JusticeV

    No file 2
    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

  • john.campbell

    using bcp with temp files
    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!

  • mbouarroudj

    Is there a workround
    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

  • mbouarroudj

    Is there a workround
    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

  • mbouarroudj

    Is there a workround
    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

  • mbouarroudj

    Is there a workround
    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

  • maxbrackett

    Column Names
    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

  • Lina

    About BCP, a question from SQL2005 Migration
    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

  • Prasoon.1983

    Regarding error Function sequence error
    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

  • benker

    Error handler for BCP
    Thanks for the information on BCP.It will be very much appreciated if there is an article on how to code the error handler part for BCP.When BCP is called from a stored procedure its out-off-process and there is no way to kill that process if something goes wrong with the bcp.I faced this when loading data from tables to a tabbed dilimited file and bcp kept on working on the background and it held locks on the tables (sql run from bcp) and a simple count(*) query on a table never gave me a result until i manually stopped the process from the task manager.So please help !!!