SQL Server Performance

execute stored procedure with output to a .csv fil

Discussion in 'SQL Server DTS-Related Questions' started by jrichardson, Nov 2, 2005.

  1. jrichardson New Member

    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 />
  2. Luis Martin Moderator

    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

    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.

  3. satya Moderator

    You can take help of ISQL or OSQL utility to create output file as .CSV.Check books online.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. jrichardson New Member

    Thank you !!!

Share This Page