Answered by:
generate csv file from store procedure

Question
Answers
-
Please refer
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
- Marked as answer by Allen Li - MSFTModerator Wednesday, July 24, 2013 1:49 AM
All replies
-
Please refer
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
- Marked as answer by Allen Li - MSFTModerator Wednesday, July 24, 2013 1:49 AM
-
-
-
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.
-
-
-
-
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.
-
-
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 -
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@sqlThis 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.
-
-
-
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.