Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJ
Anthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>
|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =
category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =
category_group.category_id
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com