execute stored procedure with output to a .csv fil | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

execute stored procedure with output to a .csv fil

have the following code (below) to create a SP which gets called by Crystal reports to display a report on a screen. <br /><br />What I need help with …. I need to find out of there is a way in T-SQL or SQL Server to execute this Store Procedure and have the output go to a .csv file???? <br /><br />CREATE PROCEDURE SP_Program_Schedule<br />@Searchtext varchar(100) = null,<br />@StartDate datetime = null,<br />@EndDate datetime = null,<br />@StartTime smallint = null,<br />@EndTime smallint = null,<br />@House_No varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = null,<br />@Premiere char(1) = null,<br />@Liveflag char(1) = null,<br />@Sddflag char(1) = null<br />AS<br />SET NOCOUNT ON<br />SELECT DISTINCT <br />dbo.Schedule_Reports.Report_Show_Date, dbo.Schedule_Reports.Report_Show_Time, dbo.Schedule_Reports.Report_Sort_Order, <br />dbo.Schedule_tester.tester_Show_End_Time, dbo.Schedule_tester.tester_Show_Name, dbo.Schedule_tester.tester_House_No, <br />dbo.Vw_Genre_Catgry_Shows.Category_Code, dbo.Vw_Genre_Catgry_Shows.Category_Desc, dbo.Vw_Genre_Catgry_Shows.Genre_Code, <br />dbo.Vw_Genre_Catgry_Shows.Genre_Desc, DATENAME(Weekday, dbo.Schedule_Reports.Report_Show_Date) AS WD, <br />dbo.Schedule_tester.tester_Episode_No, dbo.Schedule_tester.tester_Premiere_Flag, dbo.Schedule_tester.tester_Live_SDD_Flag, <br />dbo.Episodes.Episode_Title AS tester_Episode_Title, dbo.Episodes.Episode_Desc AS tester_Episode_Desc<br />FROM dbo.Episodes RIGHT OUTER JOIN<br />dbo.Schedule_tester ON dbo.Episodes.Episode_No = dbo.Schedule_tester.tester_Episode_No AND dbo.Episodes.Episode_Title = dbo.Schedule_tester.tester_Episode_Title RIGHT OUTER JOIN<br />dbo.Schedule_Reports ON dbo.Schedule_tester.tester_Show_Date = dbo.Schedule_Reports.Report_Show_Date AND <br />dbo.Schedule_tester.tester_Show_Start_Time = dbo.Schedule_Reports.Report_Show_Time LEFT OUTER JOIN<br />dbo.Vw_Genre_Catgry_Shows ON dbo.Schedule_tester.tester_Show_Name = dbo.Vw_Genre_Catgry_Shows.Show_Name<br />WHERE dbo.Schedule_Reports.Report_Show_Date &gt;= COALESCE(@StartDate, dbo.Schedule_Reports.Report_Show_Date ) <br />and dbo.Schedule_Reports.Report_Show_Date &lt;= COALESCE(@EndDate, dbo.Schedule_Reports.Report_Show_Date ) <br />and dbo.Schedule_Reports.Report_Sort_order &gt;= COALESCE(@StartTime, dbo.Schedule_Reports.Report_Sort_order)<br />and dbo.Schedule_Reports.Report_Sort_order &lt;= COALESCE(@EndTime, dbo.Schedule_Reports.Report_Sort_order)<br />and ( dbo.Episodes.Episode_Desc like COALESCE(@Searchtext, dbo.Episodes.Episode_Desc) <br />OR dbo.Episodes.Episode_Title like COALESCE(@Searchtext, dbo.Episodes.Episode_Title) <br />OR dbo.Schedule_tester.tester_Show_Name like COALESCE(@Searchtext, dbo.Schedule_tester.tester_Show_Name ))<br />and dbo.Schedule_tester.tester_House_No = COALESCE(@House_No, dbo.Schedule_tester.tester_House_No)<br />and dbo.Schedule_tester.tester_Premiere_Flag = COALESCE(@Premiere, dbo.Schedule_tester.tester_Premiere_Flag)<br />and (dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Liveflag, dbo.Schedule_tester.tester_Live_SDD_Flag) OR<br />dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Sddflag, dbo.Schedule_tester.tester_Live_SDD_Flag))<br />GO<br />
What about to create a job and write the output in .csv file?. When you create a Job, the step "New Job Step" has a "Advanced" tab. You can create output there.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
You can take help of ISQL or OSQL utility to create output file as .CSV.Check books online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Thank you !!!
]]>