Export data in SQL 2005 using Bulk Copy Program

Hello Experts,

I have select statement and would like to export the data into a .txt file using Bulk Copy.  

Thanks....
thewayne73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jose_juanCommented:
Hi,

uhm... what's problem? you put "Bulk Copy" on "SQL Query Syntax" zone but.

SQL Syntax for bulk only is

   BULK INSERT ...

and only import data into sql tables.

To export data you would use bcp (Bulk Copy) but this is not a sql syntax command.

what do you need do?

Regards.




0
thewayne73Author Commented:
Hi Jose,

I am trying to do and export of the file.  Basically, I have a simple select from a view that returns around 70,000 records.  I would like to save that output to a .txt programatically.  

Is there a way to save the output in the sql statement itself?

thanks
0
thewayne73Author Commented:
I did find this:

master..xp_cmdshell 'bcp "select statment"'

but not sure how to use it.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

jose_juanCommented:
Hi,

the only way (I known) to export txt data from sql instruction are mapping a text datasource (e.g. linked server) then use a trivial insert

INSERT INTO my_linked_txt..my_table ( ... )
SELECT ...
FROM my_huge_table

but I suggest to you use a bcp.exe command line

bcp.exe "my_database.my_user.my_table" out my_output.txt -S my_sql_server ....

see options executing

>bcp.exe
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"]

over sql instructions you can execute this command using

exec master..xp_cmdshell 'bcp.exe ...'

but your user must have sysadmins role.

Good luck!
0
thewayne73Author Commented:
Thanks jose,

here is the script I have:

EXEC master..xp_cmdshell 'bcp "
select *
  from database..table_view
 order by 3"
queryout "\\servername\folder\output file.txt" -Sservername'
GO

but when i run it, i just get the output that you listed above (usage).
0
jose_juanCommented:
Hi,

uhm....

You must effort...

DON'T get my listed above, the first line (I'm sure) is a message alert (warn to you must use the -U or -T parameters).

on the other hand, read "bcp.exe" documentation to get your EXACT solution and undertand.

come on!

NOTE: try it

exec master..xp_cmdshell 'bcp.exe "my_db..my_table" out "c:\data.txt" -T -c'
exec master..xp_cmdshell 'dir c:\*.txt'
exec master..xp_cmdshell 'type c:\data.txt'




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thewayne73Author Commented:
There were a couple of problems.  

- The script needs to be on a single line when running from query
- '-S' I was not using the servername it was running from, instead was using server name the file is being saved to.


0
jose_juanCommented:
Hi thewayne73,

problems?

You can write the script with many lines, but it (the xp_cmdshell parameter) MUST BE a command line (cmd.exe), you should refer to "cmd.exe" documentation for a complete support.

Without -S parameter, the script run perfectly if you run locally (obviously).

No matter, but I disagree of your B grade. Your question:

"I have select statement and would like to export the data into a .txt file using Bulk Copy. "

It had answer fully.

Regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.