error creating cursor handle

this is the message wath i got back from a simple table update procedure
error creating cursor handle
WHY??which database engine do you use?
how is anyone supposed to figure out what went wrong if you don't post actual simple table update procedure?
does this error have error code? If so, which one is it?|||the error was solved on anodher forum|||So you cross-posted this message. Generally, it is a bad habit - it causes confusion (should I answer this question or that?). But, if you've already done that, you could have posted link to the answer too.

Error Could not generate asynchronous keyset. The cursor has been deallocated.

Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
END
Just a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.co m...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END
|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).
|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.c om...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).

Error Could not generate asynchronous keyset. The cursor has been deallocated.

Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold
'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.googl
e.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the er
ror messages and that it work on
some server but not on other, that the difference can be this configuration.
You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug
and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.goog
le.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).

Error Could not generate asynchronous keyset. The cursor has been deallocated.

Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).

Error converting data type varchar to float.

following is my stored procedure... it's giving error at second "open cur"

Please help !!!!!!!!!!!!!!!

declare cur cursor for select id from smita.dbo.users where
userid not in (select userid from fpeligibility6...monarch1) and --change table
usercompany = @.companyid and
termdate > getdate() and
datediff(m,updated,getdate()) > 2

open cur

fetch next from cur into @.uid

while @.@.fetch_status = 0
begin
update smita.dbo.users set termdate = getdate(), updated = getdate() where id = @.uid
set @.i = @.i + 1
fetch next from cur into @.uid
end

close cur
deallocate cur

--new users
declare cur cursor for select rtrim(f.employeessn),rtrim(f.userid), rtrim(f.password),
--(substring(f.firstname,1,1) + rtrim(substring(f.lastname,1,15))),'abc123',
rtrim(f.userlevel),
rtrim(f.lastname),rtrim(f.firstname),
rtrim(f.mi),rtrim(f.ssn),
rtrim(f.relation),f.dob,
rtrim(f.sex),rtrim(f.address1),
rtrim(f.address2),rtrim(f.city),
rtrim(f.state),rtrim(f.zipcode),rtrim(f.emailaddre ss), f.activedate, f.termdate,
rtrim(f.usertype),
rtrim(f.usercompany), rtrim(f.usergroup),

f.apptmtgrp,
f.apptmtgrp2,rtrim(f.homephone),
rtrim(f.workphone)
from fpeligibility6...monarch1 f --change table to reflect eligibility file
left outer join smita.dbo.users u on f.userid = u.userid
where u.userid is null

open cur
fetch next from cur into @.employeessn, @.userid, @.password, @.userlevel, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone

while @.@.fetch_status = 0
begin
insert into smita.dbo.users (employeessn, userid, userid1, password, userlevel, oldid, lastname, firstname, middleinitial, ssn, relation, dob, sex, address1, address2, city, state, zipcode, country, emailaddress, activedate, termdate, usertype, usercompany, usergroup, apptmtgrp, apptmtgrp2, homephone, workphone, updated) values (@.employeessn, @.userid, 0, @.password, @.userlevel, 0, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, 'USA', @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone, getdate())
fetch next from cur into @.employeessn, @.userid, @.password, @.userlevel, @.lastname, @.firstname, @.middleinitial, @.ssn, @.relation, @.dob, @.sex, @.address1, @.address2, @.city, @.state, @.zipcode, @.emailaddress, @.active, @.termdate, @.usertype, @.usercompany, @.usergroup, @.apptmtgrp, @.apptmtgrp2, @.homephone, @.workphone
end

close cur
deallocate cur

--updated users termdate
update smita.dbo.users
set termdate = e.termdate, updated = getdate()
from fpeligibility6...monarch1 e --change table name
join smita.dbo.users u on u.userid is not null and u.userid = e.useridSmells like Oracle code...
Drop the cursors, learn how to write SQL, and join the big boys.

update smita.dbo.users
set termdate = getedate(),
updated = getdate
from smita.dbo.users
left outer join fpeligibility6...monarch1
on smita.dbo.users.userid = fpeligibility6...monarch1.userid
where usercompany = @.companyid
and termdate > getdate()
and datediff(m,updated,getdate()) > 2
and fpeligibility6...monarch1.userid is null

--new users
insert into smita.dbo.users
(employeessn,
userid,
userid1,
password,
userlevel,
oldid,
lastname,
firstname,
middleinitial,
ssn,
relation,
dob,
sex,
address1,
address2,
city,
state,
zipcode,
country,
emailaddress,
activedate,
termdate,
usertype,
usercompany,
usergroup,
apptmtgrp,
apptmtgrp2,
homephone,
workphone,
updated)
select rtrim(f.employeessn),
rtrim(f.userid),
0,
rtrim(f.password),
--(substring(f.firstname,1,1) + rtrim(substring(f.lastname,1,15))),'abc123',
rtrim(f.userlevel),
0,
rtrim(f.lastname),
rtrim(f.firstname),
rtrim(f.mi),
rtrim(f.ssn),
rtrim(f.relation),
f.dob,
rtrim(f.sex),
rtrim(f.address1),
rtrim(f.address2),
rtrim(f.city),
rtrim(f.state),
rtrim(f.zipcode),
'USA',
rtrim(f.emailaddress),
f.activedate,
f.termdate,
rtrim(f.usertype),
rtrim(f.usercompany),
rtrim(f.usergroup),
f.apptmtgrp,
f.apptmtgrp2,
rtrim(f.homephone),
rtrim(f.workphone),
getdate()
from fpeligibility6...monarch1 f --change table to reflect eligibility file
left outer join smita.dbo.users u on f.userid = u.userid
where u.userid is null

Run the above code (instead of your cursors), and check the line that gives you the conversion error.
And what the heck was "set @.i = @.i + 1" for, anyway?|||One thing I'll pick up on is how you have written your code... All lower case with no indentations etc (ok, I know the forum will remove a lot of indents; depending on your method, but hey) - this makes it much much harder to read and follow through.
Not how blindman has written his code as a clear list with indents (no capitalized keywords though :shocked:!)

Much easier to follow through and better for debugging. Remember this next time you write any SQL ;)|||Thanks man ... I mean blindman.....

I will remember that georgev....

Error catch in SQL

Hi everyone, I am using an SQL extended stored procedure to send emails in a
DTS package using a cursor that goes through each row in a table.

Email sending code below
======================
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
======================
Fetch Next From EmailCursor ...

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would like
to be able to do is "catch the error", something like this (C# code used as
example)

try
{
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
} catch {
exec master.dbo.xp_smtp_sendmail
@.FROM = "arealaddress@.mybusiness.com",
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
}

Is this possible? Normally I would do all the email validation before the
email is entered into the database but unfortunately, I do not have access
to the application code so I am stuck doing it this way.

Thanks in advance
MarkMark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> Now the problem I have is that if an individual email address in invalid
> then an error occurs and the whole DTS package falls over. What I would
> like to be able to do is "catch the error", something like this (C# code
> used as example)

In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
coming version SQL2005 has the TRY CATCH you are looking for.

Presumably you should be able to have the DTS package to swallow the
error, but I don't know DTS so I can't give any details. The nice and
friendly people in microsoft.public.sqlserver.dts may have some ideas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland
Regards
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9554F1DAF21E5Yazorman@.127.0.0.1...
> Mark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> > Now the problem I have is that if an individual email address in invalid
> > then an error occurs and the whole DTS package falls over. What I would
> > like to be able to do is "catch the error", something like this (C# code
> > used as example)
> In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
> coming version SQL2005 has the TRY CATCH you are looking for.
> Presumably you should be able to have the DTS package to swallow the
> error, but I don't know DTS so I can't give any details. The nice and
> friendly people in microsoft.public.sqlserver.dts may have some ideas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp