November 2, 2005 at 9:20 am
I have the following code (below) to create a SP which gets called by Crystal reports to display a report on a screen.
What I need help with .... I need to find out of there is a way in T-SQL or SQL Server to execute this SP and have the output go to a .csv file????
CREATE PROCEDURE SP_Program_Schedule
@Searchtext varchar(100) = null,
@StartDate datetime = null,
@EndDate datetime = null,
@StartTime smallint = null,
@EndTime smallint = null,
@House_No varchar(8) = null,
@Premiere char(1) = null,
@Liveflag char(1) = null,
@Sddflag char(1) = null
AS
SET NOCOUNT ON
SELECT DISTINCT
dbo.Schedule_Reports.Report_Show_Date, dbo.Schedule_Reports.Report_Show_Time, dbo.Schedule_Reports.Report_Sort_Order,
dbo.Schedule_tester.tester_Show_End_Time, dbo.Schedule_tester.tester_Show_Name, dbo.Schedule_tester.tester_House_No,
dbo.Vw_Genre_Catgry_Shows.Category_Code, dbo.Vw_Genre_Catgry_Shows.Category_Desc, dbo.Vw_Genre_Catgry_Shows.Genre_Code,
dbo.Vw_Genre_Catgry_Shows.Genre_Desc, DATENAME(Weekday, dbo.Schedule_Reports.Report_Show_Date) AS WD,
dbo.Schedule_tester.tester_Episode_No, dbo.Schedule_tester.tester_Premiere_Flag, dbo.Schedule_tester.tester_Live_SDD_Flag,
dbo.Episodes.Episode_Title AS tester_Episode_Title, dbo.Episodes.Episode_Desc AS tester_Episode_Desc
FROM dbo.Episodes RIGHT OUTER JOIN
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
dbo.Schedule_Reports ON dbo.Schedule_tester.tester_Show_Date = dbo.Schedule_Reports.Report_Show_Date AND
dbo.Schedule_tester.tester_Show_Start_Time = dbo.Schedule_Reports.Report_Show_Time LEFT OUTER JOIN
dbo.Vw_Genre_Catgry_Shows ON dbo.Schedule_tester.tester_Show_Name = dbo.Vw_Genre_Catgry_Shows.Show_Name
WHERE dbo.Schedule_Reports.Report_Show_Date >= COALESCE(@StartDate, dbo.Schedule_Reports.Report_Show_Date )
and dbo.Schedule_Reports.Report_Show_Date <= COALESCE(@EndDate, dbo.Schedule_Reports.Report_Show_Date )
and dbo.Schedule_Reports.Report_Sort_order >= COALESCE(@StartTime, dbo.Schedule_Reports.Report_Sort_order)
and dbo.Schedule_Reports.Report_Sort_order <= COALESCE(@EndTime, dbo.Schedule_Reports.Report_Sort_order)
and ( dbo.Episodes.Episode_Desc like COALESCE(@Searchtext, dbo.Episodes.Episode_Desc)
OR dbo.Episodes.Episode_Title like COALESCE(@Searchtext, dbo.Episodes.Episode_Title)
OR dbo.Schedule_tester.tester_Show_Name like COALESCE(@Searchtext, dbo.Schedule_tester.tester_Show_Name ))
and dbo.Schedule_tester.tester_House_No = COALESCE(@House_No, dbo.Schedule_tester.tester_House_No)
and dbo.Schedule_tester.tester_Premiere_Flag = COALESCE(@Premiere, dbo.Schedule_tester.tester_Premiere_Flag)
and (dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Liveflag, dbo.Schedule_tester.tester_Live_SDD_Flag) OR
dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Sddflag, dbo.Schedule_tester.tester_Live_SDD_Flag))
GO
November 2, 2005 at 10:23 am
Have you tried using DTS? That should do it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2005 at 3:00 pm
SET @cmdString = 'bcp "EXECUTE ' + DB_Name() + '.dbo.YourSP ''' + @Param1Value + ''', ''' + ... ''', ''' + @ParamNvalue + '' ' " queryout \\Server\Folder\Filename.csv -c -q -t, -k -Username -Ppassword'
Don't forget all @ParamValues must be varchar datatype, e.g. date must be convert(varchar(20), @SuppliedDate, 120). It will be converted to proper types implicitly in you SP.
Execute this string using
EXEC master..xp_cmdshell @cmdString
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply