Strange Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Strange Issue

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-04 : 09:50:06

I have the following Cursor that picks up a pdf file from a directory and emails it to clients. I have checked the query, which i have changed for this posting, and the query returns a single record for each of the licences that should get a pdf file. What I am struggling to get to the bottom of, is that some , not all are getting two identical emails instead of just the one. The clients getting two emails are are not in sequence in the SQL list, so Im at a loss as to why some are getting two emails and some are getting one, when they should only be getting one. Any help on this would me most helpful

DECLARE CURSOR_IVNOICEDISTRIBUTE CURSOR FOR
Select FullName
,EmailAccounts
,RefNumber
From invoice
INNER JOIN client
ON Licence = FullName
WHERE RefNumber Between @StartInvoice AND @EndInvoice
Order By FullName

OPEN CURSOR_IVNOICEDISTRIBUTE
FETCH NEXT FROM CURSOR_IVNOICEDISTRIBUTE
INTO @Licence, @EMAIL, @InvoiceNumber

WHILE @@FETCH_STATUS = 0
BEGIN

Set @Title = 'Test'
Set @Message = 'TEST MESSAGE'
SET @filepath = '\\Server\' + RIGHT ('00' + CAST(DATEPART(MONTH,DATEADD(m,-1,GETDATE()))AS VARCHAR(2)), 2) +'-'+ CAST(YEAR(DATEADD(m,-1,GETDATE())) AS VARCHAR(4)) +
'\' + 'Invoice Number ' +@InvoiceNumber + ' For Licence '+ @LICENCE +'.PDF'
DECLARE @return_value int

EXEC @return_value = dbo.SendEmailReports
@Addressee = @EMAIL,
@Title = @Title,
@Message = @Message ,
@filepath = @filepath

-- Get the next Licence.
FETCH NEXT FROM CURSOR_IVNOICEDISTRIBUTE
INTO @LICENCE, @EMAIL , @InvoiceNumber
END
CLOSE CURSOR_IVNOICEDISTRIBUTE
DEALLOCATE CURSOR_IVNOICEDISTRIBUTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:31:03

first check if below query is returning duplicate clinet info


Select FullName 
,EmailAccounts
,RefNumber
From invoice
INNER JOIN client 
ON Licence = FullName 
WHERE RefNumber Between @StartInvoice AND @EndInvoice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-04 : 10:46:36

Hi,

I have checked this already. The query only returns 1 record for each client

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-04 : 12:44:17

Look like you had change for testing purpose. So if you can, provide the result of that query

Select FullName
,EmailAccounts
,RefNumber
From invoice
INNER JOIN client
ON Licence = FullName
WHERE RefNumber Between @StartInvoice AND @EndInvoice
Order By FullName

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-04 : 13:04:54

quote:
Originally posted by namman

Look like you had change for testing purpose. So if you can, provide the result of that query

Select FullName
,EmailAccounts
,RefNumber
From invoice
INNER JOIN client
ON Licence = FullName
WHERE RefNumber Between @StartInvoice AND @EndInvoice
Order By FullName

Hi , the query result is ok. Only one record is shown for each client. in this cursor the sql produces a list of 516 records. For testing I have sent them all to a google mail account and the 516 emails i should have recieved ends up doubling up as if the cursor is running throughtwice

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-04 : 13:40:50

Hi Pete

As you say, the problem may be on the cursor not the query. Let focus on that first. Say, you have 516 records returning from the query, but you get double that number in the email.

Check if the loop runs twice or email send twice
(ex: by print @count++ in the while loop)

Check while condition by replace with other (like @count <= 516). (you already confirmed this but just check)

If loop runs only once, it means problem is the email. Check your email system. What is the purpose of @return_value ? And that may confuse the email system ?

If everything is OK, so check other code, make sure it is NOT calling the store procedure twice.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-04 : 13:43:45

What does the procedure SendEmailReports do?

--
Gail Shaw
SQL Server MVP

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-04 : 14:36:48

Hi guys,

I think the problem has been sorted. By declaring my variables after

OPEN CURSOR_IVNOICEDISTRIBUTE
i only get one email for each client. I am going to run a few more test runs to confirm this

cheers for the help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-04 : 18:10:26

The location the variables are declared will have no effect on how the query executes, they can be declared at any point before being used.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources