Export to CSV file - via SP

[UHNSTrust]

At the end of my stored procedure I need to be able to output a table to a csv file.

I have had a search through the forum but can't find (or don't know what I am looking for) what I should be doing.

Is there a function that you can use?

Any help would be appreciated as I am quite new to SQL programming.

Thanks in advance

Jonathan

 

[dky1e]

serach for bcp utility on books on line.

 

[UHNSTrust]

I have tried using the following within a SP but am getting an error.

exec master..xp_cmdshell 'bcp "CISDataSQL.dbo.zzzCSVUploadFile" out "\\deanesly\cisdata\jonathanpugh\cisdb\zzzCSVUploadFile.csv" -c -t'

The error message is Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

Is this because it cannot open the existing CSV file (maybe a permissions problem with the SQL server default login)?

Is there any way around this without having to change the server login rights to directories?

Thanks

Jonathan

 

[ClayG]

Something like ..

exec master..xp_cmdshell 'BCP dbname.owner.tablename Out "c:\Temp\file.csv" -c -S servername -T'

Empty strings are a nuisance with this - BCP writes the ascii(0) character, because it uses a zero-length string for NULLS (dont confuse a database NULL with ascii(0) called the Null character). Turn empty strings into NULLS with nullif().

You can use a global ##temp table with bcp, but not a local #temp table. Or BCP has Queryout option - but including a query in the same statement gets messy.

 

[UHNSTrust]

ClayG, thanks for the reply. I have looked at your command line and appart from the '-S servername' there is no difference to the syntax.

I have put the servername in as you suggested but I still get the same error.

Jonathan

 

[JamesLean]

This does sound like a permissions problem. The SQL Server service account needs to have permissions to be able to access the csv file.

This means it must be using a domain account and not the local system account.

--James