Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

T-SQL PLEASE HELP!!!!

7 posts in Windows NT Last posting was on 2000-07-26 11:51:44.0Z
John Posted on 2000-07-22 11:20:11.0Z
From: "John" <John@zelcom.com>
Subject: T-SQL PLEASE HELP!!!!
Date: Sat, 22 Jul 2000 04:20:11 -0700
Lines: 42
Organization: Zelcom
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <zJPKgA98$GA.196@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 208.41.91.34
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1835
Article PK: 1089322

Hello,

The following is a script for batch birthday emails. The problem
is that when it reads the next email address, and it's shorter than the
previous email address, the @recipient for xp_sendmail isn't clearing out
before hand. Shouldn't the @recipient clear out each time the xp_sendmail
command is executed? Any help would be greatly appreciated. Thanks in
advance.
John

declare cur_next cursor for
select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
from CHILD_TABLE
where datepart(mm, CHILD_BIRTHDATE) = datepart(mm, getdate())
and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
for read only
go
open cur_next
go
declare @c int, @name varchar(255) , @email varchar(255)
select @c = count(*)
from CHILD_TABLE
where datepart(mm, CHILD_BIRTHDATE) = datepart(mm, getdate())
and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
while @c >0
begin
fetch cur_next into @email, @name
select @email = '"'+@email+'"'
select @name = @name + "'s birthday"


execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE PROBLEM
OCCURS*/
@subject = @name,
@message = "birthday message here"


select @c = @c -1
end
close cur_next


Rob Verschoor Posted on 2000-07-22 14:13:26.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <zJPKgA98$GA.196@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Sat, 22 Jul 2000 16:13:26 +0200
Lines: 75
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <0r6qkl#8$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0411.pvu.euronet.nl 194.134.165.156
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1834
Article PK: 1089318

I don't seen anything wrong with your code w.r.t. the handling of the
xp_* parameters. However, what I find remarkable is the way you're
using a cursor to fetch from but use a completely different type of
loop-control logic; this seems *very* strange to me. Why not simple
test for @@sqlstatus and forget about the "select count(*)" stuff ?

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0
Certified Sybase Professional DBA/SQL Developer for ASE 11.5
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

"John" <John@zelcom.com> wrote in message
news:zJPKgA98$GA.196@forums.sybase.com...
> Hello,
>
> The following is a script for batch birthday emails. The
problem
> is that when it reads the next email address, and it's shorter than
the
> previous email address, the @recipient for xp_sendmail isn't
clearing out
> before hand. Shouldn't the @recipient clear out each time the
xp_sendmail
> command is executed? Any help would be greatly appreciated. Thanks
in
> advance.
> John
>
> declare cur_next cursor for
> select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> from CHILD_TABLE
> where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
getdate())
> and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> for read only
> go
> open cur_next
> go
> declare @c int, @name varchar(255) , @email varchar(255)
> select @c = count(*)
> from CHILD_TABLE
> where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
getdate())
> and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> while @c >0
> begin
> fetch cur_next into @email, @name
> select @email = '"'+@email+'"'
> select @name = @name + "'s birthday"
>
>
> execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
PROBLEM
> OCCURS*/
> @subject = @name,
> @message = "birthday message here"
>
>
> select @c = @c -1
> end
> close cur_next
>
>


John Posted on 2000-07-24 18:38:32.0Z
From: "John" <John@zelcom.com>
References: <zJPKgA98$GA.196@forums.sybase.com> <0r6qkl#8$GA.85@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Mon, 24 Jul 2000 11:38:32 -0700
Lines: 86
Organization: Zelcom
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <FCE0t#Z9$GA.259@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 208.41.91.34
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1833
Article PK: 1089321

Rob,
Any ideas on why the @recipient is not clearing out when going to
read in the next @email? I am still new to Sybase and sql what is the
@@sqlstatus variable? Is there another way to initialize the @recipient
variable? Thanks.......

John

Rob Verschoor <rob@sypron.nl> wrote in message
news:0r6qkl#8$GA.85@forums.sybase.com...
> I don't seen anything wrong with your code w.r.t. the handling of the
> xp_* parameters. However, what I find remarkable is the way you're
> using a cursor to fetch from but use a completely different type of
> loop-control logic; this seems *very* strange to me. Why not simple
> test for @@sqlstatus and forget about the "select count(*)" stuff ?
>
> HTH,
>
> Rob
> ----------------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.0
> Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> Certified DBA/Performance & Tuning Specialist for Sybase System 11
>
> email mailto:rob@sypron.nl.*No*Spam*Please*
> WWW http://www.euronet.nl/~syp_rob
> snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> ----------------------------------------------------------------------
>
> "John" <John@zelcom.com> wrote in message
> news:zJPKgA98$GA.196@forums.sybase.com...
> > Hello,
> >
> > The following is a script for batch birthday emails. The
> problem
> > is that when it reads the next email address, and it's shorter than
> the
> > previous email address, the @recipient for xp_sendmail isn't
> clearing out
> > before hand. Shouldn't the @recipient clear out each time the
> xp_sendmail
> > command is executed? Any help would be greatly appreciated. Thanks
> in
> > advance.
> > John
> >
> > declare cur_next cursor for
> > select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> > from CHILD_TABLE
> > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> getdate())
> > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > for read only
> > go
> > open cur_next
> > go
> > declare @c int, @name varchar(255) , @email varchar(255)
> > select @c = count(*)
> > from CHILD_TABLE
> > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> getdate())
> > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > while @c >0
> > begin
> > fetch cur_next into @email, @name
> > select @email = '"'+@email+'"'
> > select @name = @name + "'s birthday"
> >
> >
> > execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
> PROBLEM
> > OCCURS*/
> > @subject = @name,
> > @message = "birthday message here"
> >
> >
> > select @c = @c -1
> > end
> > close cur_next
> >
> >
>
>


Andy Price Posted on 2000-07-25 08:31:46.0Z
From: "Andy Price" <andy.price@canada.com>
References: <zJPKgA98$GA.196@forums.sybase.com> <0r6qkl#8$GA.85@forums.sybase.com> <FCE0t#Z9$GA.259@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Tue, 25 Jul 2000 09:31:46 +0100
Lines: 120
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <Q2OppPh9$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn84-ras15.screaming.net 212.49.238.84
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1822
Article PK: 1089290

John,

The problem you are having with the @email variable is that you are not
reinitiailising it on every loop. Therefore if you have an email of 50
chars, and then the next loop you have one of 30 characters, you will still
have characters 31-50 of the first one.

Just do this after your execute xp_sendmail command to reinitialise it with
spaces:
select @email = space(255)

That will solve your problem.

Also, as Rob says, you need to sort out your cursor logic. It must run
abysmally slow like this (do you come from a mainframe background? - joke
*s*). Read "Cursors and Performance" in Chapter 8 of the Performance and
Tuning guide (well, chapter 8 in v12 of the manuals, should be close to that
in other versions).

Andy

--

Andy Price
Sybase Certified Professional - Adaptive Server DBA v12.0
Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5

"John" <John@zelcom.com> wrote in message
news:FCE0t#Z9$GA.259@forums.sybase.com...
> Rob,
> Any ideas on why the @recipient is not clearing out when going to
> read in the next @email? I am still new to Sybase and sql what is the
> @@sqlstatus variable? Is there another way to initialize the @recipient
> variable? Thanks.......
>
> John
> Rob Verschoor <rob@sypron.nl> wrote in message
> news:0r6qkl#8$GA.85@forums.sybase.com...
> > I don't seen anything wrong with your code w.r.t. the handling of the
> > xp_* parameters. However, what I find remarkable is the way you're
> > using a cursor to fetch from but use a completely different type of
> > loop-control logic; this seems *very* strange to me. Why not simple
> > test for @@sqlstatus and forget about the "select count(*)" stuff ?
> >
> > HTH,
> >
> > Rob
> > ----------------------------------------------------------------------
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.0
> > Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> > Certified DBA/Performance & Tuning Specialist for Sybase System 11
> >
> > email mailto:rob@sypron.nl.*No*Spam*Please*
> > WWW http://www.euronet.nl/~syp_rob
> > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > ----------------------------------------------------------------------
> >
> > "John" <John@zelcom.com> wrote in message
> > news:zJPKgA98$GA.196@forums.sybase.com...
> > > Hello,
> > >
> > > The following is a script for batch birthday emails. The
> > problem
> > > is that when it reads the next email address, and it's shorter than
> > the
> > > previous email address, the @recipient for xp_sendmail isn't
> > clearing out
> > > before hand. Shouldn't the @recipient clear out each time the
> > xp_sendmail
> > > command is executed? Any help would be greatly appreciated. Thanks
> > in
> > > advance.
> > > John
> > >
> > > declare cur_next cursor for
> > > select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> > > from CHILD_TABLE
> > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > getdate())
> > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > > for read only
> > > go
> > > open cur_next
> > > go
> > > declare @c int, @name varchar(255) , @email varchar(255)
> > > select @c = count(*)
> > > from CHILD_TABLE
> > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > getdate())
> > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > > while @c >0
> > > begin
> > > fetch cur_next into @email, @name
> > > select @email = '"'+@email+'"'
> > > select @name = @name + "'s birthday"
> > >
> > >
> > > execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
> > PROBLEM
> > > OCCURS*/
> > > @subject = @name,
> > > @message = "birthday message here"
> > >
> > >
> > > select @c = @c -1
> > > end
> > > close cur_next
> > >
> > >
> >
> >
>
>


John Posted on 2000-07-25 21:41:57.0Z
From: "John" <John@zelcom.com>
References: <zJPKgA98$GA.196@forums.sybase.com> <0r6qkl#8$GA.85@forums.sybase.com> <FCE0t#Z9$GA.259@forums.sybase.com> <Q2OppPh9$GA.85@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Tue, 25 Jul 2000 14:41:57 -0700
Lines: 133
Organization: Zelcom
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <itp$1Jo9$GA.196@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 208.41.91.34
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1821
Article PK: 1089292

Andy, the only problem with that is, instead of characters there are spaces
and so you get under exchange 'john@zelcom.com
' and it doesn't send that either...

Andy Price <andy.price@canada.com> wrote in message
news:Q2OppPh9$GA.85@forums.sybase.com...
> John,
>
> The problem you are having with the @email variable is that you are not
> reinitiailising it on every loop. Therefore if you have an email of 50
> chars, and then the next loop you have one of 30 characters, you will
still
> have characters 31-50 of the first one.
>
> Just do this after your execute xp_sendmail command to reinitialise it
with
> spaces:
> select @email = space(255)
>
> That will solve your problem.
>
> Also, as Rob says, you need to sort out your cursor logic. It must run
> abysmally slow like this (do you come from a mainframe background? - joke
> *s*). Read "Cursors and Performance" in Chapter 8 of the Performance and
> Tuning guide (well, chapter 8 in v12 of the manuals, should be close to
that
> in other versions).
>
> Andy
>
> --
>
> Andy Price
> Sybase Certified Professional - Adaptive Server DBA v12.0
> Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5
>
>
>
>
> "John" <John@zelcom.com> wrote in message
> news:FCE0t#Z9$GA.259@forums.sybase.com...
> > Rob,
> > Any ideas on why the @recipient is not clearing out when going
to
> > read in the next @email? I am still new to Sybase and sql what is the
> > @@sqlstatus variable? Is there another way to initialize the @recipient
> > variable? Thanks.......
> >
> > John
> > Rob Verschoor <rob@sypron.nl> wrote in message
> > news:0r6qkl#8$GA.85@forums.sybase.com...
> > > I don't seen anything wrong with your code w.r.t. the handling of the
> > > xp_* parameters. However, what I find remarkable is the way you're
> > > using a cursor to fetch from but use a completely different type of
> > > loop-control logic; this seems *very* strange to me. Why not simple
> > > test for @@sqlstatus and forget about the "select count(*)" stuff ?
> > >
> > > HTH,
> > >
> > > Rob
> > > ----------------------------------------------------------------------
> > > Rob Verschoor
> > >
> > > Certified Sybase Professional DBA for ASE 12.0
> > > Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> > > Certified DBA/Performance & Tuning Specialist for Sybase System 11
> > >
> > > email mailto:rob@sypron.nl.*No*Spam*Please*
> > > WWW http://www.euronet.nl/~syp_rob
> > > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > > ----------------------------------------------------------------------
> > >
> > > "John" <John@zelcom.com> wrote in message
> > > news:zJPKgA98$GA.196@forums.sybase.com...
> > > > Hello,
> > > >
> > > > The following is a script for batch birthday emails. The
> > > problem
> > > > is that when it reads the next email address, and it's shorter than
> > > the
> > > > previous email address, the @recipient for xp_sendmail isn't
> > > clearing out
> > > > before hand. Shouldn't the @recipient clear out each time the
> > > xp_sendmail
> > > > command is executed? Any help would be greatly appreciated. Thanks
> > > in
> > > > advance.
> > > > John
> > > >
> > > > declare cur_next cursor for
> > > > select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> > > > from CHILD_TABLE
> > > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > > getdate())
> > > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > > > for read only
> > > > go
> > > > open cur_next
> > > > go
> > > > declare @c int, @name varchar(255) , @email varchar(255)
> > > > select @c = count(*)
> > > > from CHILD_TABLE
> > > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > > getdate())
> > > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd, getdate())
> > > > while @c >0
> > > > begin
> > > > fetch cur_next into @email, @name
> > > > select @email = '"'+@email+'"'
> > > > select @name = @name + "'s birthday"
> > > >
> > > >
> > > > execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
> > > PROBLEM
> > > > OCCURS*/
> > > > @subject = @name,
> > > > @message = "birthday message here"
> > > >
> > > >
> > > > select @c = @c -1
> > > > end
> > > > close cur_next
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Andy Price Posted on 2000-07-26 11:51:44.0Z
From: "Andy Price" <andy.price@canada.com>
References: <zJPKgA98$GA.196@forums.sybase.com> <0r6qkl#8$GA.85@forums.sybase.com> <FCE0t#Z9$GA.259@forums.sybase.com> <Q2OppPh9$GA.85@forums.sybase.com> <itp$1Jo9$GA.196@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Wed, 26 Jul 2000 12:51:44 +0100
Lines: 157
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <zQiyCkv9$GA.259@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn44-ras52.screaming.net 212.188.147.44
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1820
Article PK: 1089291

John,

Think about it. Just use the rtrim(char_expr) function to get rid of the
trailing spaces before using xp_sendmail, then you are left with a clean
@email string.

Andy

"John" <John@zelcom.com> wrote in message
news:itp$1Jo9$GA.196@forums.sybase.com...
> Andy, the only problem with that is, instead of characters there are
spaces
> and so you get under exchange 'john@zelcom.com
> ' and it doesn't send that either...
>
>
> Andy Price <andy.price@canada.com> wrote in message
> news:Q2OppPh9$GA.85@forums.sybase.com...
> > John,
> >
> > The problem you are having with the @email variable is that you are not
> > reinitiailising it on every loop. Therefore if you have an email of 50
> > chars, and then the next loop you have one of 30 characters, you will
> still
> > have characters 31-50 of the first one.
> >
> > Just do this after your execute xp_sendmail command to reinitialise it
> with
> > spaces:
> > select @email = space(255)
> >
> > That will solve your problem.
> >
> > Also, as Rob says, you need to sort out your cursor logic. It must run
> > abysmally slow like this (do you come from a mainframe background? -
joke
> > *s*). Read "Cursors and Performance" in Chapter 8 of the Performance and
> > Tuning guide (well, chapter 8 in v12 of the manuals, should be close to
> that
> > in other versions).
> >
> > Andy
> >
> > --
> >
> > Andy Price
> > Sybase Certified Professional - Adaptive Server DBA v12.0
> > Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5
> >
> >
> >
> >
> > "John" <John@zelcom.com> wrote in message
> > news:FCE0t#Z9$GA.259@forums.sybase.com...
> > > Rob,
> > > Any ideas on why the @recipient is not clearing out when going
> to
> > > read in the next @email? I am still new to Sybase and sql what is the
> > > @@sqlstatus variable? Is there another way to initialize the
@recipient
> > > variable? Thanks.......
> > >
> > > John
> > > Rob Verschoor <rob@sypron.nl> wrote in message
> > > news:0r6qkl#8$GA.85@forums.sybase.com...
> > > > I don't seen anything wrong with your code w.r.t. the handling of
the
> > > > xp_* parameters. However, what I find remarkable is the way you're
> > > > using a cursor to fetch from but use a completely different type of
> > > > loop-control logic; this seems *very* strange to me. Why not simple
> > > > test for @@sqlstatus and forget about the "select count(*)" stuff ?
> > > >
> > > > HTH,
> > > >
> > > > Rob
> > >
> ----------------------------------------------------------------------
> > > > Rob Verschoor
> > > >
> > > > Certified Sybase Professional DBA for ASE 12.0
> > > > Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> > > > Certified DBA/Performance & Tuning Specialist for Sybase System 11
> > > >
> > > > email mailto:rob@sypron.nl.*No*Spam*Please*
> > > > WWW http://www.euronet.nl/~syp_rob
> > > > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > >
> ----------------------------------------------------------------------
> > > >
> > > > "John" <John@zelcom.com> wrote in message
> > > > news:zJPKgA98$GA.196@forums.sybase.com...
> > > > > Hello,
> > > > >
> > > > > The following is a script for batch birthday emails.
The
> > > > problem
> > > > > is that when it reads the next email address, and it's shorter
than
> > > > the
> > > > > previous email address, the @recipient for xp_sendmail isn't
> > > > clearing out
> > > > > before hand. Shouldn't the @recipient clear out each time the
> > > > xp_sendmail
> > > > > command is executed? Any help would be greatly appreciated. Thanks
> > > > in
> > > > > advance.
> > > > >
John
> > > > >
> > > > > declare cur_next cursor for
> > > > > select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> > > > > from CHILD_TABLE
> > > > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > > > getdate())
> > > > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd,
getdate())
> > > > > for read only
> > > > > go
> > > > > open cur_next
> > > > > go
> > > > > declare @c int, @name varchar(255) , @email varchar(255)
> > > > > select @c = count(*)
> > > > > from CHILD_TABLE
> > > > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > > > getdate())
> > > > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd,
getdate())
> > > > > while @c >0
> > > > > begin
> > > > > fetch cur_next into @email, @name
> > > > > select @email = '"'+@email+'"'
> > > > > select @name = @name + "'s birthday"
> > > > >
> > > > >
> > > > > execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
> > > > PROBLEM
> > > > > OCCURS*/
> > > > > @subject = @name,
> > > > > @message = "birthday message here"
> > > > >
> > > > >
> > > > > select @c = @c -1
> > > > > end
> > > > > close cur_next
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Rob Verschoor Posted on 2000-07-24 21:23:14.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <zJPKgA98$GA.196@forums.sybase.com> <0r6qkl#8$GA.85@forums.sybase.com> <FCE0t#Z9$GA.259@forums.sybase.com>
Subject: Re: T-SQL PLEASE HELP!!!!
Date: Mon, 24 Jul 2000 23:23:14 +0200
Lines: 117
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <YQ6Fucb9$GA.290@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0222.pvu.euronet.nl 194.134.164.223
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1830
Article PK: 1089315

@@sqlstatus is used for loop control in cursors and indicates whether
the end of the result set has been reached yet. I suggest you read the
section on cursors in the T-SQL user manual (downloadable from
http://sybooks.sybase.com).
I have no idea why this parameter problem occurs...

HTH,

Rob V.

"John" <John@zelcom.com> wrote in message
news:FCE0t#Z9$GA.259@forums.sybase.com...
> Rob,
> Any ideas on why the @recipient is not clearing out when
going to
> read in the next @email? I am still new to Sybase and sql what is
the
> @@sqlstatus variable? Is there another way to initialize the
@recipient
> variable? Thanks.......
>
> John
> Rob Verschoor <rob@sypron.nl> wrote in message
> news:0r6qkl#8$GA.85@forums.sybase.com...
> > I don't seen anything wrong with your code w.r.t. the handling of
the
> > xp_* parameters. However, what I find remarkable is the way you're
> > using a cursor to fetch from but use a completely different type
of
> > loop-control logic; this seems *very* strange to me. Why not
simple
> > test for @@sqlstatus and forget about the "select count(*)" stuff
?
> >
> > HTH,
> >
> > Rob
>
> --------------------------------------------------------------------
--
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.0
> > Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> > Certified DBA/Performance & Tuning Specialist for Sybase System 11
> >
> > email mailto:rob@sypron.nl.*No*Spam*Please*
> > WWW http://www.euronet.nl/~syp_rob
> > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
>
> --------------------------------------------------------------------
--
> >
> > "John" <John@zelcom.com> wrote in message
> > news:zJPKgA98$GA.196@forums.sybase.com...
> > > Hello,
> > >
> > > The following is a script for batch birthday emails.
The
> > problem
> > > is that when it reads the next email address, and it's shorter
than
> > the
> > > previous email address, the @recipient for xp_sendmail isn't
> > clearing out
> > > before hand. Shouldn't the @recipient clear out each time the
> > xp_sendmail
> > > command is executed? Any help would be greatly appreciated.
Thanks
> > in
> > > advance.
> > >
John
> > >
> > > declare cur_next cursor for
> > > select CHILD_TABLE.CHILD_EMAIL, CHILD_TABLE.CHILD_FNAME
> > > from CHILD_TABLE
> > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > getdate())
> > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd,
getdate())
> > > for read only
> > > go
> > > open cur_next
> > > go
> > > declare @c int, @name varchar(255) , @email varchar(255)
> > > select @c = count(*)
> > > from CHILD_TABLE
> > > where datepart(mm, CHILD_BIRTHDATE) = datepart(mm,
> > getdate())
> > > and datepart(dd, CHILD_BIRTHDATE) = datepart(dd,
getdate())
> > > while @c >0
> > > begin
> > > fetch cur_next into @email, @name
> > > select @email = '"'+@email+'"'
> > > select @name = @name + "'s birthday"
> > >
> > >
> > > execute xp_sendmail @recipient = @email, /*THIS IS WHERE THE
> > PROBLEM
> > > OCCURS*/
> > > @subject = @name,
> > > @message = "birthday message here"
> > >
> > >
> > > select @c = @c -1
> > > end
> > > close cur_next
> > >
> > >
> >
> >
>
>