SQL Server - Export to CSV

You can export data via the BCP command and via an SSIS package discounting the CLR way of doing it.

To do it via a stored procedure though is more difficult as you need to run BCP commands using the command line and xp_cmdshell is usually locked down due to security issues.

Do you have any issue with this?

Do you have a BCP code sample... ?  I can't find one that works.
thx

I get

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

Any ideas?

Ok, it seems the Path variable on the server, where you are connecting is not set properly... can you let us know the version of sql server you are working with?

Using Management Studio 2008. But I believe the SQL server is 2000

if you are using sql 2008, then ensure that your PATH variable is having the below paths included

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;

Select allOpen in new window

Where is the PATH variable set?

Is SQL Server installed on the system you are running the BCP command from?

check this out
http://java.com/en/download/help/path.xml

sql is not on running BCP from..
I will try the path thing

I get the same message when I run on the sql server box

I get all these errors...............?????

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

You need to change the bit that says servername\instance to your SQL instance.

I get the same message.... it works for you?

Yes this works for me. Could you post the block of code that you are using?

declare @sql nvarchar(500),@sql1 nvarchar(4000)
declare @FilePath nvarchar(500)
set @sql = 'SET NOCOUNT ON; select top 10 * from cfgoa.test'
set @FilePath = 'C:\myfile.csv'
set @sql1 = 'BCP "'+@sql+'" queryout "'+@FilePath + '" /T /S  CFGSQL2\local /c '
exec master.dbo.xp_cmdshell @sql1

I copied your code, changed the instance and table name and it works fine for me. Could this be a connectivity issue on your server?

I get the following output!

NULL
Starting copy...
NULL
7 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (7000.00 rows per sec.)
NULL

I can connect fine to the DB. Any other ideas?
thx

What service account is the SQL Server service running under?