t-sql help lpease

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

  • 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

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

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

  • 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

  • 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