generate csv file from store procedure

Question

Hi Exceperts,

any one suggest how generate csv file from storeprocedure,

please provide any examples

Thanks

Answer 1

Please refer

https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/ 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Thanks for quirk response,

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

where i have t write this code in sp, how apply above code to my sp?

Thanks

Answer 3

Hi 

You can use openrowset function. For example

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Text;Database=F:\Test\;HDR=Yes;', 'SELECT * FROM Test.csv')
SELECT * FROM Table

Answer 4

Hi 

You can use openrowset function. For example

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Text;Database=F:\Test\;HDR=Yes;', 'SELECT * FROM Test.csv')
SELECT * FROM Table

Openrowset is reserved for Excel files, and it's a horrid function at best, if you're going down that route then you're much better off using SSIS. But for the purposes of this question a Bulk Export is sufficient for the OP's needs. 

See this code:

DECLARE @SQL NVARCHAR(4000)

SET @SQL = '
bcp "SELECT STATEMENT HERE" queryout "FILENAME.csv" -t, -c -T'

EXEC MASTER..XP_CMDSHELL @SQL

Thanks.

Answer 5

thanks for all,

shall i change this or not 

EXEC MASTER..XP_CMDSHELL @SQL

thanks

Answer 6

thanks for all,

shall i change this or not 

EXEC MASTER..XP_CMDSHELL @SQL

thanks

...Why would you want to change it? It's correct...

Answer 7

what is master..XP_CMDSHELL@SQL

thanks

Answer 8

what is master..XP_CMDSHELL@SQL

thanks

I think you need to do a bit of exploration and study of SQL Server before you start going any further. Judging on your questions and replies here, you do not seem to grasp concepts of SQL Syntax and the way dynamic SQL behaves.

Here's a BOL article on xp_cmdshell:

http://msdn.microsoft.com/en-us/library/ms175046.aspx

The quick answer for you is that it is a function used in SQL to execute/pass various tasks from the command line to the stack in Windows. 

Thanks.

Answer 9

Really thanks Johny,

i will come with my sp, please keep in this way?

Answer 10

Hi , 

Please once see my below sp , modify as per needs , very thanks, anything wrong in my sp ?

Alter procedure spInvoice
@Invoiceno varchar(16),
@department int=2
AS
BEGIN
select D value from Invoice where Svalue=@Invoiceno
union
select D value from Department where Svalue=@department
exce spInvoice

here i want generate as csv file , where write this below code in sp

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

Answer 11

Hi , 

Please once see my below sp , modify as per needs , very thanks, anything wrong in my sp ?

Alter procedure spInvoice
@Invoiceno varchar(16),
@department int=2
AS
BEGIN
select D value from Invoice where Svalue=@Invoiceno
union
select D value from Department where Svalue=@department
exce spInvoice

here i want generate as csv file , where write this below code in sp

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

This won't work unfortunately. This is not dynamic SQL. You need to concatenate your variables to a statement as a string to get it to work:

ALTER PROCEDURE DBO.SPINVOICE
@INVOICE_NO VARCHAR(16)
, @DEPARTMENT_NO INT = 2

AS

BEGIN

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'BCP "SELECT [D VALUE] FROM DBO.INVOICE WHERE [SVALUE] = ' + @INVOICE_NO + 'UNION ALL SELECT [D VALUE] FROM DBO.DEPARTMENT WHERE [SVALUE] = ' + CAST( @DEPARTMENT AS VARCHAR)+'" QUERYOUT "FILENAME.csv" -t, -c -T'
EXEC MASTER..XP_CMDSHELL @SQL

But as I said earlier, if this is for a production environment I would urge you to learn some more basics before moving onto this.

Answer 12

Thanks John,

Please suggest any goodsites for basics in dynamic sql ..

thanks

Answer 13

Hi John, 

as per as its copy and pastes , shall run the code , before run code any prerequstties?

Thanks

Answer 14

You still need to put your own values in there.

This is the route I took to having a good basic understand of SQL:

Firstly I read the SQL Standard basics on W3Schools - http://www.w3schools.com/sql/

I've referred back to this site many times (although not really anymore) to brush up on the basics and it gave me a good understanding of how select, update, insert and delete all behave and also how to format dates and use basic functions for arithmetic.

Secondly I read the MCTS SQL Server 2008 book on Database Development as part of the training kit. You can find most if not all of this information for free in BOL (Books Online) - http://msdn.microsoft.com/en-us/library/ms130214(v=sql.105).aspx

The third and probably most important step in my learning was implementing what I'd learnt in a practical environment. So having a job using SQL Server is a key factor in my learning experience.

The fourth was joining this forum and joining in discussions/answering questions. You find you pick up alot of tips while attempting to find solutions for someone else that will become useful in the future.

Thanks.