need help with cursors - 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 2000 Forums

Transact-SQL (2000)

need help with cursors

Author

Topic

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 12:31:40

Hey all,

I am trying to write a cursor with fetches some data and later sends an email to the managers.

I have the cursor working allright but when I send an email to the managers all they get is the last row from the cursor.. I just cannot figure out what seems to be the problem.

Can anyone help me with it?? here is my code for it..
TIA

SET NOCOUNT ON

DECLARE @message VARCHAR(1000)
DECLARE @message1 VARCHAR(8000)
DECLARE @message2 VARCHAR(8000)
DECLARE @message3 VARCHAR(8000)
DECLARE @buffer VARCHAR(8000)
DECLARE @email VARCHAR(200)
DECLARE @tablePK INT
DECLARE @lastname VARCHAR(50)
DECLARE @firstname VARCHAR(50)
DECLARE @commPhone VARCHAR(20)
DECLARE @commPhoneExt VARCHAR(5)
DECLARE @dsn VARCHAR(20)
DECLARE @uic VARCHAR(10)
DECLARE @name VARCHAR(500)
DECLARE @city VARCHAR(50)
DECLARE @state VARCHAR(3)
DECLARE @auic VARCHAR(10)
DECLARE @alname VARCHAR(50)
DECLARE @afname VARCHAR(50)
DECLARE @acPhone VARCHAR(20)
DECLARE @acPhoneExt VARCHAR(6)
DECLARE @to VARCHAR(100)
DECLARE @from VARCHAR(100)
DECLARE @subject VARCHAR(100)
DECLARE @body VARCHAR(8000)

DECLARE dctr_cursor CURSOR FOR
SELECT tablePK , lastname , firstname
FROM tbl_personnel
WHERE privilege = 'DCTR' and email <> 'dctr'

OPEN dctr_cursor

FETCH NEXT FROM dctr_cursor
INTO @tablePK , @lastname , @firstname

WHILE @@FETCH_STATUS = 0
BEGIN
/* First Module for the DCTR Personal Details */
DECLARE info_cursor CURSOR FOR
SELECT p.firstname , p.lastname , p.commPhone , p.commPhoneExt , p.DSN
FROM tbl_personnel p
WHERE p.tablePK = @tablePK

OPEN info_Cursor
FETCH NEXT FROM info_cursor INTO @firstname , @lastname , @commPhone , @commPhoneExt , @dsn
IF @@FETCH_STATUS <> 0
PRINT 'ERROR'
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM info_cursor INTO @firstname , @lastname , @commPhone , @commPhoneExt , @dsn
SELECT @message1 = @firstname + ' ' + @lastname + ' ' + @commPhone + ' ' + @commPhoneExt + ' ' + @dsn

END

CLOSE info_cursor
DEALLOCATE info_cursor

/* Next module is to declare the command cursor */
DECLARE commands_cursor CURSOR FOR
SELECT c.uic , c.name , c.city , c.state
FROM tbl_commands c INNER JOIN
tbl_nmciFunctionJunction j ON c.tablePK = j.commandFK INNER JOIN
tbl_personnel p ON j.personnelFK = p.tablePK
WHERE (p.privilege = 'DCTR') and p.tablePK = @tablePK -- Variable value from the outer cursor
ORDER BY c.uic ASC

OPEN commands_cursor
FETCH NEXT FROM commands_cursor INTO @uic , @name , @city , @state
/* Validation to see if there are any records returned */
IF @@FETCH_STATUS <> 0
PRINT ' <<No Commands Associated>>'

WHILE @@FETCH_STATUS = 0

BEGIN
SET @buffer = @message2
FETCH NEXT FROM commands_cursor INTO @uic , @name , @city , @state
SELECT @message2 = RTRIM(@buffer) + SPACE(80) + LTRIM(@message2)
SELECT @message2 = RTRIM(@uic) + SPACE(5) + RTRIM(@name) + SPACE(5) + RTRIM(@city) + SPACE(5) + LTRIM(@state)
END

CLOSE commands_cursor
DEALLOCATE commands_cursor

/* ACTR Cursor.. Will get the information for ACTR's associated to that DCTR and their command uic */
DECLARE actr_cursor CURSOR FOR
SELECT DISTINCT tbl_commands.uic , tbl_personnel.firstName, tbl_personnel.lastName, tbl_personnel.commPhone , tbl_personnel.commPhoneExt
FROM tbl_personnel tbl_personnel_1
INNER JOIN tbl_nmcifunctionjunction tbl_nmcifunctionjunction_1 ON tbl_personnel_1.tablePK = tbl_nmcifunctionjunction_1.personnelFK
INNER JOIN tbl_nmcifunctionjunction ON tbl_nmcifunctionjunction_1.commandFK = tbl_nmcifunctionjunction.commandFK
INNER JOIN tbl_personnel ON tbl_nmcifunctionjunction.personnelFK = tbl_personnel.tablePK INNER JOIN
tbl_commands ON tbl_nmciFunctionJunction.commandFK = tbl_commands.tablePK
WHERE (tbl_personnel_1.tablePK = @tablePK) AND (tbl_personnel.privilege = 'ACTR')
ORDER BY tbl_personnel.lastname

/* Opening the ACTR Cursor to fetch data */

OPEN actr_cursor
FETCH NEXT FROM actr_cursor INTO @auic , @afname , @alname, @acPhone , @acPhoneExt
/* Validation to see if there are any records returned */
IF @@FETCH_STATUS <> 0
PRINT ' <<No ACTR Information Available>>'

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM actr_cursor INTO @auic , @afname , @alname, @acPhone , @acPhoneExt
SELECT @message3 = RTRIM(@auic) + SPACE(5) + RTRIM(@afname) + SPACE(5) + RTRIM(@alname) + SPACE(5) + LTRIM(@acPhone) + SPACE(5) + LTRIM(@acPhoneExt)
END

CLOSE actr_cursor
DEALLOCATE actr_cursor

/* Get the email portion set up */
SET @from = 'myemail@domain.com'
SET @to = @from
SET @subject = 'DCTR Quarterly Report'
SELECT @body = RTRIM(@message1) + SPACE(80) + LTRIM(@message2) + SPACE(80) + LTRIM(@message3)
--PRINT @body
DECLARE dctrEmail CURSOR FAST_FORWARD FOR
SELECT email
FROM tbl_personnel
WHERE tablePK = @tablePK
-- Open the declared cursor

OPEN dctrEmail

FETCH NEXT FROM dctrEmail INTO @email
IF @@FETCH_STATUS <> 0
PRINT ' <<No Email Available>>'

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM dctrEmail INTO @email
END
CLOSE dctrEmail
DEALLOCATE dctrEmail

EXEC sndMail @from,@to,@subject,@body

FETCH NEXT FROM dctr_cursor
INTO @tablePK , @lastname , @firstname
END

CLOSE dctr_cursor
DEALLOCATE dctr_cursor

here is the code for the send mail proc

CREATE PROCEDURE sndMail

@from VARCHAR (255),
@to VARCHAR (500),
@subject VARCHAR (255),
@body VARCHAR (8000)

AS

DECLARE @mail_ID INT
DECLARE @hr INT

EXEC @hr = sp_OACreate 'cdonts.newMail', @mail_ID OUT
EXEC @hr = sp_OASetProperty @mail_ID, 'from', @from
EXEC @hr = sp_OASetProperty @mail_ID, 'to', @to
EXEC @hr = sp_OASetProperty @mail_ID, 'subject', @subject
EXEC @hr = sp_OASetProperty @mail_ID, 'importance', 2
EXEC @hr = sp_OASetProperty @mail_ID, 'body', @body
EXEC @hr = sp_OAMethod @mail_ID, 'send', NULL
EXEC @hr = sp_OADestroy @mail_ID
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 12:35:28

Why do you need to use a cursor for this?

From what I can tell, the problem is this:

SELECT email
FROM tbl_personnel
WHERE tablePK = @tablePK

The above query can only return one row.

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 12:48:24

I have 13 different people I need to pull the information for ..

DECLARE dctr_cursor CURSOR FOR
SELECT tablePK , lastname , firstname
FROM tbl_personnel
WHERE privilege = 'DCTR' and email <> 'dctr'

gives me the Key for those 13 people

SELECT email
FROM tbl_personnel
WHERE tablePK = @tablePK

will give me the email ID for that tablePK which is currently fetched into the cursor..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 12:50:40

Comment out this line:

EXEC sndMail @from,@to,@subject,@body

In place of it, put:

PRINT @from
PRINT @to
PRINT @subject
PRINT @body

Do you get all of the results that you expect, or just the one?

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 12:50:52

further more.. It is sending 13 different emails to the managers.. but its just sending the last record for that manager instead of the entire recordset.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 12:56:50

Of course it is sending 13 different emails. You are processing each row separately.

Could you explain what you are trying to do? The code that you posted is not easily understandable with all of the cursors. If you can explain in words what you are trying to do, then perhaps we can come up with a solution for you. Do not explain what your code is doing, but rather explain what your requirement is.

Tara

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-26 : 12:58:19

It looks like your sending the same email to a bunch of people. You can do this without cursors real easy. Form the @body first and then form a comma delimited list of all the emails it needs to goto buy doing something like this:


declare @list varchar(1000)
set @list = ''

select @list = @list + rtrim(email) + ', ' from employees

print left(@list, len(@list) - 2)

Then send it with that list as the BCC property. No cursors needed.

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 13:02:38

Tara,

This is what I want to do.

Get a list of all DCTR's

for every DCTR show the following Information

1 DCTR (firstname , lastname , phoneNumber)
2 Command Information (list of all commands associated to that DCTR)
3 ACTR Information (list of all ACTR's associated to that DCTR)
---- Send an email to individual DCTR's with their information (steps 1,2,3)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 13:17:37

I have stripped down your code to show you a simpler way (it still uses a loop though):



DECLARE @WhichOne INT
DECLARE @firstname VARCHAR(50)
DECLARE @lastname VARCHAR(50)
DECLARE @commPhone VARCHAR(50)
DECLARE @commPhoneExt VARCHAR(50)
DECLARE @DSN VARCHAR(50)
DECLARE @message1 VARCHAR(500)
DECLARE @from VARCHAR(50)
DECLARE @to VARCHAR(50)
DECLARE @subject VARCHAR(50)
DECLARE @body VARCHAR(50)

SELECT tablePK , lastname , firstname, commPhone, commPhoneExt, DSN
INTO #Temp
FROM tbl_personnel 
WHERE privilege = 'DCTR' and email <> 'dctr'

WHILE (SELECT COUNT(*) FROM #Temp) <> 0
BEGIN

	SELECT TOP 1 @WhichOne = tablePK
	FROM #Temp
	ORDER BY tablePK

	SELECT @firstname = p.firstname , @lastname = p.lastname , @commPhone = p.commPhone , @commPhoneExt = p.commPhoneExt , @dsn = p.DSN
	FROM tbl_personnel p
	WHERE privilege = 'DCTR' and email <> 'dctr' AND tablePK = @WhichOne

	SELECT @message1 = @firstname + ' ' + @lastname + ' ' + @commPhone + ' ' + @commPhoneExt + ' ' + @dsn

	SET @from = 'myemail@domain.com'
	SET @to = @from
	SET @subject = 'DCTR Quarterly Report'
	
	SELECT @body = RTRIM(@message1)
	
	EXEC sndMail @from,@to,@subject,@body

	DELETE FROM #Temp
	WHERE tablePK = @WhichOne

END

DROP TABLE #Temp

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 13:53:35

Thanks a lot.. that works fine.. but I am getting the same problem when I run this query against it..

SELECT c.uic , c.name , c.city , c.state
FROM tbl_commands c INNER JOIN
tbl_nmciFunctionJunction j ON c.tablePK = j.commandFK INNER JOIN
tbl_personnel p ON j.personnelFK = p.tablePK
WHERE (p.privilege = 'DCTR') and p.tablePK = @WhichOne
ORDER BY c.uic ASC

it is still sending an email with the last record. (1 DCTR - Many Commands)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 13:59:34

If you change @WhichOne to a hard-coded value, what does it show?

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 14:12:38

If I change @whichOne to a hardcoded value it gets into an indefinite loop

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-26 : 14:13:36

Just and FYI, cdonts is no longer available on Windows Server 2003. You have to use 'cdo.message'.

Goto this site for an example...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286430

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:13:40

Just run the simple query, not the whole thing. Run the SELECT that you just posted with a hard-coded value. Does it show all of the data that you expect it to for that tablePK?

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 14:16:37

running the simple query with the hardcoded value gives me what I want.!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:19:04

So are you receiving the multiple e-mails with my version? You should be, but each e-mail should have different data.

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 14:26:09

Yes and No.. If I run the exact query of what you posted I get multiple emails (so far so good) but when I modify the query to run this part SELECT @uic = c.uic , @name = c.name , @city = c.city , @state = c.state
FROM tbl_commands c INNER JOIN
tbl_nmciFunctionJunction j ON c.tablePK = j.commandFK INNER JOIN
tbl_personnel p ON j.personnelFK = p.tablePK
WHERE (p.privilege = 'DCTR') and p.tablePK = @WhichOne
ORDER BY c.uic ASC

I get multiple emails but only with the last record as opposed to multiple records for that TablePK..

am I confusing everyone now?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:29:24

Yes, that's because you've changed the query. You are putting the selected columns into variables. And yes you'll only receive the last row in the query. Do you really need to capture the columns into @uic, @name, @city, and @state into variables? Cuz that's where the problem is.

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 14:40:12

No I don't need to capture the columns into variables.
This is a sample report I am looking to generate which gets sent to the respective DCTR's

DCTR Info:
Firstname Lastname CommPhone CommPhoneExt DSN (based on @whichOne)

Command Detail
Uic Name City State (based on @whichone for that DCTR)
xxxxx NAS Pensacola Pensacola FL
xxxxx NAS SanDiego San Diego CA

ACTR Info

Uic Firstname lastname (based on @whichone for that DCTR)
xxxxx Jon Doe
xxxxx Jane Doe
thats why I had those 3 cursors ..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:59:50

So does it work for you without the variables?

Tara

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-01-26 : 15:09:53

Yes it does.. I just need to figure out how to send this SELECT c.uic , c.name , c.city , c.state
FROM tbl_commands c INNER JOIN
tbl_nmciFunctionJunction j ON c.tablePK = j.commandFK INNER JOIN
tbl_personnel p ON j.personnelFK = p.tablePK
WHERE (p.privilege = 'DCTR') and p.tablePK = @WhichOne

to another variable so the record set becomes a part of my body.

ex: SET @message2 = SELECT c.uic , c.name , c.city , c.state
FROM tbl_commands c INNER JOIN
tbl_nmciFunctionJunction j ON c.tablePK = j.commandFK INNER JOIN
tbl_personnel p ON j.personnelFK = p.tablePK
WHERE (p.privilege = 'DCTR') and p.tablePK = @WhichOne

or something like that

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources