t-sql help lpease

Question

t-sql help lpease

MC-231403

SSC-Addicted

Points: 421
More actions
August 27, 2008 at 5:47 am

#192815

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))

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

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

MC-231403 SSC-Addicted Points: 421 More actions · Answer 2

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 ')'.

Steve Jones - SSC Editor SSC Guru Points: 738848 More actions · Answer 3

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Chinna SSCertifiable Points: 5408 More actions · Answer 5

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