August 27, 2008 at 5:47 am
HI I'm trying to run a scrit that will output csv file can you please help
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_Send_Unsubscribed_Emails_Spring]
(
@lAgencyID INT
,@sRecipients varchar(250)
)
AS
BEGIN
SET NOCOUNT ON
Declare @FromDatedatetime
Declare @ToDatedatetime
declare @ssubject varchar(200)
--DECLARE @tableHTML NVARCHAR(MAX)
-- get the 1st of last month
if (datepart(month,getDate())-1) <> 0
BEGIN
set @FromDate = convert(datetime, convert(varchar,datepart(month,getDate())-1) + '/1/' + convert(varchar,datepart(year,getDate())))
END
ELSE
BEGIN
set @FromDate = '12/1/' + convert(varchar,datepart(year,getDate())-1)
END
-- get the 1st of this month
set @ToDate = convert(datetime, convert(varchar,datepart(month,getDate())) + '/1/' + convert(varchar,datepart(year,getDate())))
-- set the subject of the e-mail
set @ssubject = (select upper(sName) from tCompanies where lid = @lAgencyID) + ' :Unsubscribed Candidates month beginning ' + convert(varchar,@FromDate)
-- create the for the e-mail
SELECT distinct
td = pd.lAlternativeID, '',
td = pd.lCandidateID, '',
td = coalesce(c.sFirstName,ca.sFirstName), '',
td = coalesce(c.sLastName, ca.sLastName), '',
td = coalesce(c.sEmail1, ca.sEmail1), ''
from tsm4.dbo.tprivatepool_deleted pd with (nolock)
inner join tsm4.dbo.tCompanies co with (nolock) on pd.lAgencyID = co.lid
left outer join tsm4.dbo.tCandidate c with (nolock) on pd.lCandidateID = c.lid
left outer join tsm4.dbo.tCandidate_archive ca with (nolock) on pd.lCandidateID = ca.lid
-- inner join tsm4.dbo.tCandidate c on c.lid = pd.lCandidateID
where pd.dtenteredon between @FromDate and @ToDate
and pd.lAgencyID = @lAgencyID
--and pd.lCandidateID not in (select distinct lOldProfileID from tsm4.dbo.tChangedProfileIDs with (nolock) where lAgencyID = pd.lAgencyID)
and lAlternativeID is not null
and pd.lCandidateID not in (select lid from tsm4.dbo.tTransposedDeletes t with (nolock))
select count(distinct lAlternativeID)
from tsm4.dbo.tprivatepool_deleted pd with (nolock)
where pd.dtenteredon between @FromDate and @ToDate
and pd.lAgencyID = @lAgencyID
and lAlternativeID is not null
and pd.lCandidateID not in (select lid from tsm4.dbo.tTransposedDeletes t with (nolock))
-- AS NVARCHAR(MAX) ) +
(select count(distinct pd.lCandidateID)
from tsm4.dbo.tprivatepool_deleted pd with (nolock)
where pd.dtenteredon between @FromDate and @ToDate
and pd.lAgencyID = @lAgencyID
and lAlternativeID is not null
and pd.lCandidateID not in (select lid from tsm4.dbo.tTransposedDeletes t with (nolock))
August 27, 2008 at 6:02 am
I can't test your script, but what is exactly the problem? Assuming this script works well, you just want the output to a file?
If you're running this script in a job, check the 'advanced' tab in 'job step properties'
Wilfred
The best things in life are the simple things
August 27, 2008 at 6:22 am
When running this script in the query window I get the following error
Msg 102, Level 15, State 1, Procedure SP_Send_Unsubscribed_Emails_Spring, Line 47
Incorrect syntax near ')'.
August 27, 2008 at 7:16 am
You have mismatched parens, one in the wrong place, or bad code to one side of the parens. Go through the code section by section, look for line 47 and then examine the code.
I don't know what was in the batch, so line 47 could be in a few places. Look in SSMS for the line numbers in the status bar.
August 27, 2008 at 7:16 am
Each BEGIN should have one corresponding END.
This is also likely to cause some problems...
SELECT distinct
td = pd.lAlternativeID, '',
td = pd.lCandidateID, '',
td = coalesce(c.sFirstName,ca.sFirstName), '',
td = coalesce(c.sLastName, ca.sLastName), '',
td = coalesce(c.sEmail1, ca.sEmail1), ''
Guessing, it looks like a whole chunk of the procedure wasn't copied. The beginning is there, and the middle...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2008 at 11:33 am
Hello mary i have checked with your code evrythng works good .....
as chris sais u need to have an end if u have an begin ,....
i added end to ur code then it worked fine and remove the open bracket in front of the last select as u r not closing it again....
(select count(distinct pd.lCandidateID)
from tsm4.dbo.tprivatepool_deleted pd with (nolock)
where pd.dtenteredon between @FromDate and @ToDate
and pd.lAgencyID = @lAgencyID
and lAlternativeID is not null
and pd.lCandidateID not in (select lid from tsm4.dbo.tTransposedDeletes t with (nolock))
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply