multi-value dropdownlist as a .net control?

Does anyone know if I can utilize the same multi-value dropdownlist control that is available in Report Manager and the ReportViewer parameters section, in my own ASPX pages? This is the control that has checkboxes in a dropdown list.

If not, can anyone point me to a third party?

Thanks,

Brian

Brian,
know exactly what you are talking about as I was searching for an item like this myself. I have not found any similar control yet but managed to seperate the client side code that is driving the control in the ReportViewer. Did not have time to write a custom control that renders out this client side code but this is definitely doable. Let me know if you want to have the commented client side code that drives the dropdown (it does not work in Firefox though but should be good enough as an example).

Cheers,
Niels

multi-value dropdownlist as a .net control?

Does anyone know if I can utilize the same multi-value dropdownlist control that is available in Report Manager and the ReportViewer parameters section, in my own ASPX pages? This is the control that has checkboxes in a dropdown list.

If not, can anyone point me to a third party?

Thanks,

Brian

Brian,
know exactly what you are talking about as I was searching for an item like this myself. I have not found any similar control yet but managed to seperate the client side code that is driving the control in the ReportViewer. Did not have time to write a custom control that renders out this client side code but this is definitely doable. Let me know if you want to have the commented client side code that drives the dropdown (it does not work in Firefox though but should be good enough as an example).

Cheers,
Niels

Multiuser Databinding in SQL Server 2000?

Hi

I am developing one multi user application in windows environment using VC++ .NET with SQL Server 2000. How Can I create dataset for multi user data updating in my SQL Server 2000. Where I can get help and sample code?

Thanks

Jose

Jose,

The DataSet is an off-line data cache. The DataAdapter is used to submit the pending changes stored in the DataSet. In the DataAdapter's updating logic, you can specify your own concurrency options to control whether you want to use a "last in wins" approach with only the primary key column(s) in the WHERE clause, or use other columns in the WHERE clause to ensure you don't overwrite changes made by another user.

I hope this information proves helpful.

David Sceppa
ADO.NET Program Manager
Microsoft|||Hi

Thankyou for the nice explanation. One more question how can I write custom primary key value. for example (50710000001) like this. 5 is for year, 07 is for month, 10 is for place, and remaining is unique automatic generating number. How can I write program in SQL Server 2000.

Regards,
Jose

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > I created a .net program which uses multithreading. The threads
> > concurrently access the same db resources. This has generated no end
> > of sql server deadlocks, which our dba is struggling to resolve.
> >
> > I am very close to giving up and going ahead and serializing all
> > database access for the application. This will reduce performance
> > quite a bit, although it will eliminate all the deadlocks.
> >
> > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > mine for building an app that hits the db so hard, but we are unable
> > to solve all the deadlocking issues using the errorlog and profiling
> > combo that has been suggested. We solve one deadlock issue, only to
> > see another one revealed later, perhaps on different resources,
> > perhaps not.
> >
> > I have heard there are many applications that use the Singleton
> > pattern to serialize db access accross the board, just to avoid
> > situations like this.
> >
> > Any feedback welcome.
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
--
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > Deadlocks are usually caused by 2 or more users attempting to update the
> > same set of tables in opposite orders. If you have one sp that updates
> > Orders and then Details while another updates details first and then
> orders
> > this will eventually cause a dead lock. If they are both in the same
> order
> > you most likely get only blocking which is normal and can be controlled.
> Do
> > you have an example of 2 procedures that deadlock on each other and the
> > associated DDL for those tables? Deadlocking is almost always a result
of
> a
> > poor schema or more likely poor data access methods. Serialization is
not
> > the way to go, you should do it right and find the cause so you can fix
> it.
> > Maybe these will help:
> >
> >
> > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > Blocking
> >
> >
> >
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > I created a .net program which uses multithreading. The threads
> > > concurrently access the same db resources. This has generated no end
> > > of sql server deadlocks, which our dba is struggling to resolve.
> > >
> > > I am very close to giving up and going ahead and serializing all
> > > database access for the application. This will reduce performance
> > > quite a bit, although it will eliminate all the deadlocks.
> > >
> > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > mine for building an app that hits the db so hard, but we are unable
> > > to solve all the deadlocking issues using the errorlog and profiling
> > > combo that has been suggested. We solve one deadlock issue, only to
> > > see another one revealed later, perhaps on different resources,
> > > perhaps not.
> > >
> > > I have heard there are many applications that use the Singleton
> > > pattern to serialize db access accross the board, just to avoid
> > > situations like this.
> > >
> > > Any feedback welcome.
> >
> >
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec:(0x53915568) Value:0x558fa140 Cost:(0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> > Yes there are other reasons why dead locks occur but the main reason is
as I
> > posted. Without more details it's all a guess so the most logical or
common
> > reason is what I posted.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Christian Smith" <csmith@.digex.com> wrote in message
> > news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Can't deadlocking also happen for locks other than tables? It can
happen
> > > for RID or KEY locks as well within the same table, can't it?
> > >
> > > Christian Smith
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > > Deadlocks are usually caused by 2 or more users attempting to update
the
> > > > same set of tables in opposite orders. If you have one sp that
updates
> > > > Orders and then Details while another updates details first and then
> > orders
> > > > this will eventually cause a dead lock. If they are both in the
same
> > order
> > > > you most likely get only blocking which is normal and can be
controlled.
> > Do
> > > > you have an example of 2 procedures that deadlock on each other and
the
> > > > associated DDL for those tables? Deadlocking is almost always a
result
> > of
> > a
> > > > poor schema or more likely poor data access methods. Serialization
is
> > not
> > > > the way to go, you should do it right and find the cause so you can
fix
> > it.
> > > > Maybe these will help:
> > > >
> > > >
> > > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > > http://www.support.microsoft.com/?id=271509 How to monitor SQL
2000
> > > > Blocking
> > > >
> > > >
> > > >
> > >
> >
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > > I created a .net program which uses multithreading. The threads
> > > > > concurrently access the same db resources. This has generated no
end
> > > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > > >
> > > > > I am very close to giving up and going ahead and serializing all
> > > > > database access for the application. This will reduce performance
> > > > > quite a bit, although it will eliminate all the deadlocks.
> > > > >
> > > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe
it's
> > > > > mine for building an app that hits the db so hard, but we are
unable
> > > > > to solve all the deadlocking issues using the errorlog and
profiling
> > > > > combo that has been suggested. We solve one deadlock issue, only
to
> > > > > see another one revealed later, perhaps on different resources,
> > > > > perhaps not.
> > > > >
> > > > > I have heard there are many applications that use the Singleton
> > > > > pattern to serialize db access accross the board, just to avoid
> > > > > situations like this.
> > > > >
> > > > > Any feedback welcome.
> > > >
> > > >
> > >
> > >|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/t...=books&n=507846
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> orders
> order
> Do
of
> a
not
> it.
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec0x53915568) Value:0x558fa140 Cost0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
as I
common
happen
the
updates
same
controlled.
the
result
is
fix
2000
http://www.amazon.com/exec/obidos/t...=books&n=507846
end
it's
unable
profiling
to|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

MultiTable Dataset

First off let me say that some of this is very confusing as it would
appear that the MS SQL group and the ADO.NET group need to get together
to mesh their termonology a bit.
I have a program that uses ReportViewer. To this program I load up an
ADO.NET dataset that contains serveral tables. I also load in an RDLC
report file and display the resulting report.
Up until now this has worked great and I have gotten every report I
need worked out. To keep this simple lets go with something we all
know all so well. My ADO.NET dataset contains three tables:
Customers, Orders, Parts. Each one of them is related to the other.
How does one go about displaying a report that would appear as follows:
Customer 1
-Order 1
--Part 1
--Part 2
--Part 3
-Order 2
--Part 4
--Part 5
Customer 2
-Order3
etc...
I have tried numerous things and read a ton of different posts, but all
to no avail. I know I must be doing something wrong when it comes to
setting this up. There do not seem to be many examples with multiple
tables in a single ADO dataset. Unless I am just missing something.
Also, as an aside, why did MS decide to call each table in a Report a
dataset while a dataset in ADO.NET contains many different tables?
Any help would be greatly appreciated.
Thanks,
AdamYou have to made a Select woth Joins of Customer, Order, Part
So for each part you have one record with the data Customer and Order within.
Then you can group this records in the report and so on.
So you will have only one Resultset for this kind of Report.
OK, The naming-conventions in ReportViewer differs a little bit.
Maybe they will sit together for the next release ;-)
--
LG HOLAN
"akeroo" wrote:
> First off let me say that some of this is very confusing as it would
> appear that the MS SQL group and the ADO.NET group need to get together
> to mesh their termonology a bit.
> I have a program that uses ReportViewer. To this program I load up an
> ADO.NET dataset that contains serveral tables. I also load in an RDLC
> report file and display the resulting report.
> Up until now this has worked great and I have gotten every report I
> need worked out. To keep this simple lets go with something we all
> know all so well. My ADO.NET dataset contains three tables:
> Customers, Orders, Parts. Each one of them is related to the other.
> How does one go about displaying a report that would appear as follows:
> Customer 1
> -Order 1
> --Part 1
> --Part 2
> --Part 3
> -Order 2
> --Part 4
> --Part 5
> Customer 2
> -Order3
> etc...
> I have tried numerous things and read a ton of different posts, but all
> to no avail. I know I must be doing something wrong when it comes to
> setting this up. There do not seem to be many examples with multiple
> tables in a single ADO dataset. Unless I am just missing something.
> Also, as an aside, why did MS decide to call each table in a Report a
> dataset while a dataset in ADO.NET contains many different tables?
> Any help would be greatly appreciated.
> Thanks,
> Adam
>|||Although you can do it, this gets quite messy. The clean way (but it is more
difficult when operating in local mode with the viewer control) is to use
subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"holan" <holan@.noemaol.noemail> wrote in message
news:FA17AF8F-0A21-4A92-BDB9-45A194502517@.microsoft.com...
> You have to made a Select woth Joins of Customer, Order, Part
> So for each part you have one record with the data Customer and Order
> within.
> Then you can group this records in the report and so on.
> So you will have only one Resultset for this kind of Report.
> OK, The naming-conventions in ReportViewer differs a little bit.
> Maybe they will sit together for the next release ;-)
> --
> LG HOLAN
>
> "akeroo" wrote:
>> First off let me say that some of this is very confusing as it would
>> appear that the MS SQL group and the ADO.NET group need to get together
>> to mesh their termonology a bit.
>> I have a program that uses ReportViewer. To this program I load up an
>> ADO.NET dataset that contains serveral tables. I also load in an RDLC
>> report file and display the resulting report.
>> Up until now this has worked great and I have gotten every report I
>> need worked out. To keep this simple lets go with something we all
>> know all so well. My ADO.NET dataset contains three tables:
>> Customers, Orders, Parts. Each one of them is related to the other.
>> How does one go about displaying a report that would appear as follows:
>> Customer 1
>> -Order 1
>> --Part 1
>> --Part 2
>> --Part 3
>> -Order 2
>> --Part 4
>> --Part 5
>> Customer 2
>> -Order3
>> etc...
>> I have tried numerous things and read a ton of different posts, but all
>> to no avail. I know I must be doing something wrong when it comes to
>> setting this up. There do not seem to be many examples with multiple
>> tables in a single ADO dataset. Unless I am just missing something.
>> Also, as an aside, why did MS decide to call each table in a Report a
>> dataset while a dataset in ADO.NET contains many different tables?
>> Any help would be greatly appreciated.
>> Thanks,
>> Adam
>>|||Thanks... I ended up going with subreports and it is working fine.
The joins were not quite going to work for me as my exact situation
wasn't an A to B to C relationship. It was an A-B A-C setup, which
means the joins would have ended up being A-B * C number of records and
that would have gotten really ugly.
thanks,
Adam

multiple values per table cell?

I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value.
Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:

<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>

Putting aside the question of the style being used for a moment, the contents of the column is not *really* multiple values, but a single XML string. This allows it to be easily read into a XmlDocument object and parsed.

As for the reason behind this approach, the only one I can thing of would be to store dynamic categories of information that is not known at design time. While none come to mind immediately, I'm sure that there might be times when this approach is quite valid. It is *not*, however, a classic ASP thing, but a database design decision. As to whether you should change the approach, I would if the attributes in the XML string were always the same. Better to use a database to do what databases were intended to do ;)

HTH

Multiple users, SourceSafe, and Reporting Services

I am new to the .NET world. I have created a Solution with some Projects in
them for Reporting Services. I have successfually integrated SourceSafe with
my environment and Added the solutions to Sourcesafe.
Now I have a co-worker who needs to begin sharing my solution and Projects.
I have him set up in SourceSafe but can not seem to find a way to get his
environment to "check out" my solution and bind his environment into
SourceSafe like I have mine bound.In the VS IDE, try File>Source Control>Open From Source Control
HTH
"mlapoint" wrote:
> I am new to the .NET world. I have created a Solution with some Projects in
> them for Reporting Services. I have successfually integrated SourceSafe with
> my environment and Added the solutions to Sourcesafe.
> Now I have a co-worker who needs to begin sharing my solution and Projects.
> I have him set up in SourceSafe but can not seem to find a way to get his
> environment to "check out" my solution and bind his environment into
> SourceSafe like I have mine bound.|||Worked like a charm. Thanks!
"Alien2_51" wrote:
> In the VS IDE, try File>Source Control>Open From Source Control
> HTH
> "mlapoint" wrote:
> > I am new to the .NET world. I have created a Solution with some Projects in
> > them for Reporting Services. I have successfually integrated SourceSafe with
> > my environment and Added the solutions to Sourcesafe.
> >
> > Now I have a co-worker who needs to begin sharing my solution and Projects.
> > I have him set up in SourceSafe but can not seem to find a way to get his
> > environment to "check out" my solution and bind his environment into
> > SourceSafe like I have mine bound.

Multiple users logging into a database

I'm developing an application in ASP.Net that uses SQLServer. The database person in the team wants to have each user have a seperate login to the database so he can tell who has done what changes in the database. Is this a good practice? Or are their performance\security issues with this model?For system performance it is better to use one account (to use one connection string) and connection pooling. My solution is as follows. Create your own users and log-in system. You can save current username or id to Session. Then create a table system_events to your database. Everytime user makes something you can insert a new row that describes user action. Also, and it is maybe even better, you can use plain text logs for this.|||We implemented this feature and what we did was write a stored procedure to check the old value against the new values being updated in the database and then it writes only the changes to a seperate table designed for tracking changes. We then wrote a page to display change history by date range in a D-Grid. It works very well and there has been no noticable performance hit since implementation.

CREATE PROCEDURE AddChangeHistory
(
@.propertyID INT,
@.userName VARCHAR(75), --Assigned in session when a user logins
@.tableName VARCHAR(75),
@.fieldName VARCHAR(80),
@.fieldChangeVal varchar(200)
)
AS
DECLARE @.fieldInitVal varchar(200)
BEGIN TRAN
CREATE TABLE #tempVal
(
fieldInitVal VARCHAR(200)
)
INSERT INTO #tempVal exec('SELECT TOP 1 ' + @.fieldName + ' FROM ' + @.tableName + ' WHERE propertyID=' + @.propertyID + '')
SELECT @.fieldInitVal = (SELECT top 1 * FROM #tempVal)
DROP TABLE #tempVal
IF(LOWER(@.fieldInitVal) <> LOWER(@.fieldChangeVal))
BEGIN
INSERT INTO ChangeHistory(propertyID, userName, tableName, fieldName, fieldInitVal, fieldChangeVal)
VALUES (@.propertyID, @.userName, @.tableName, @.fieldName, @.fieldInitVal, @.fieldChangeVal)
END
IF(@.@.ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
GO

SPROC to update my database

CREATE PROCEDURE dbo.oPK_UpdateAdminHours
(
@.Role AS varchar(100),
@.UserName AS varchar(100),
@.vcManager AS varchar(50),
@.vcAssistant1 AS varchar(50),
@.vcAssistant2 AS varchar(50),
@.vcLeasingManager AS varchar(50),
@.vcMarketingDirector AS varchar(50),
@.vcLeasing1 AS varchar(50),
@.vcLeasing2 AS varchar(50),
@.vcLeasing3 AS varchar(50),
@.vcLeasing4 AS varchar(50),
@.vcLeasing5 AS varchar(50),
@.bMon_Fri AS bit,
@.cDisplaySummer_Winter AS char(1),
@.vcOfficeHoursSummerMon AS varchar(25),
@.vcOfficeHoursWinterMon AS varchar(25),
@.vcOfficeHoursSummerTue AS varchar(25),
@.vcOfficeHoursWinterTue AS varchar(25),
@.vcOfficeHoursSummerWed AS varchar(25),
@.vcOfficeHoursWinterWed AS varchar(25),
@.vcOfficeHoursSummerThur AS varchar(25),
@.vcOfficeHoursWinterThur AS varchar(25),
@.vcOfficeHoursSummerFri AS varchar(25),
@.vcOfficeHoursWinterFri AS varchar(25),
@.vcOfficeHoursSummerSat AS varchar(25),
@.vcOfficeHoursWinterSat AS varchar(25),
@.vcOfficeHoursSummerSun AS varchar(25),
@.vcOfficeHoursWinterSun AS varchar(25),
@.vcTimeZone AS varchar(50),
@.PropertyID AS INT
)
AS
--Calling the AddChangeHistory SPROC to check the values before updating the Database
exec AddChangeHistory @.propertyID, @.userName, 'vcManager',@.vcManager
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant1',@.vcAssistant1
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant2',@.vcAssistant2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasingManager',@.vcLeasingManager
exec AddChangeHistory @.propertyID, @.userName, 'vcMarketingDirector',@.vcMarketingDirector
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing1',@.vcLeasing1
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing2',@.vcLeasing2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing3',@.vcLeasing3
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing4',@.vcLeasing4
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing5',@.vcLeasing5
exec AddChangeHistory @.propertyID, @.userName, 'bMon_Fri',@.bMon_Fri
exec AddChangeHistory @.propertyID, @.userName, 'cDisplaySummer_Winter',@.cDisplaySummer_Winter
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerMon',@.vcOfficeHoursSummerMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterMon',@.vcOfficeHoursWinterMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerTue',@.vcOfficeHoursSummerTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterTue',@.vcOfficeHoursWinterTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerWed',@.vcOfficeHoursSummerWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterWed',@.vcOfficeHoursWinterWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerThur',@.vcOfficeHoursSummerThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterThur',@.vcOfficeHoursWinterThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerFri',@.vcOfficeHoursSummerFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterFri',@.vcOfficeHoursWinterFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSat',@.vcOfficeHoursSummerSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSat',@.vcOfficeHoursWinterSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSun',@.vcOfficeHoursSummerSun
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSun',@.vcOfficeHoursWinterSun
exec AddChangeHistory @.propertyID, @.userName, 'vcTimeZone ',@.vcTimeZone
--Put your Update code here (removed for space)

Hope this helps.|||Thank you so much. That is what I needed.

Multiple SqlDataSource Controls on a Page

Thanks for your help.

I'm just getting into ASP.NET 2.0 and started using the SqlDataSource control to declaritively connect to a database. I was wondering if I have two SqlDataSource controls on one page with two select commands querying the same database, will the that create to seperate instances of opening and closing the connection to the database, or will it open the database once, execute both queries, and then close the connection. It seems that if it creates two separate connections, it would still be more efficient to to connect to the database programmatically using ADO.NET. You would only need to make one trip to the database, pull down all of your data, and then close the connection.

Adrian

I believe it will open two connections. Which is what you would probably want anyhow. That would allow SQL Server to prioritize and minimize response time.

That said, most of the time you can write code to do things more efficiently than depending on a generic library. It's a trade off, and often the code you may generate may not actually be more efficient unless you are also able to spend the time to implement the more advanced features of the library, in this case, the controls ability to selectively databind and cache data.

multiple SQL commands at once

I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands?

MainQuery = "declare @.MinGrades as Table(GradeID Bigint) " & _
" INSERT @.MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _
" FROM CreditGrades INNER JOIN" & _
" CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _
" WHERE (CreditGrades.x0x30 = - 1 OR " & _
" CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _
" CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _
" CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _
" CreditGrades.x3xNOD >= 0) " & _
" GROUP BY CreditGradeSplits.CreditGradeGroupID" & _
" Select * from @.MinGrades "Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.|||

Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.

I suspect you realize that you don't really need to do that query in 3 commands, and can combine them into a single select.