Earlier today I was in a discussion with the team's DBA on where explicit
database transaction locks should be placed - in the .Net application or in
the stored procedures. This can up when we ran into some concurrency
problems for our web application. Is here any guidelines or standards on
where the best place is for explicit locks? We use SQL Server 2000 and 2005
only (no Oracle and MySql). However, we do have several databases that make
up the datasource for the applications. All of the T-SQL code is in stored
procedures.

Also, with the CLR now in SQL Server 2005, is there push to move business
code from the business layer directly into SQL Server?

Thanks
Bob

As a general rule, it's always better to have explicit transactions
executing on the server, not from the client. You have more control
using stored procedures (error handling, wait queues, etc) than you do
from the client where more round trips are involved. As far as pushing
business code to the server, that's not what the CLR was designed for.
It's useful for procedural code that would otherwise require
non-performant server-side cursors or the like. T-SQL was never a true
programming language, and the CLR fills in the gaps.

-mary

On Thu, 30 Aug 2007 13:14:23 -0400, "Robert Strickland"
<(E-Mail Removed)> wrote:

>Earlier today I was in a discussion with the team's DBA on where explicit
>database transaction locks should be placed - in the .Net application or in
>the stored procedures. This can up when we ran into some concurrency
>problems for our web application. Is here any guidelines or standards on
>where the best place is for explicit locks? We use SQL Server 2000 and 2005
>only (no Oracle and MySql). However, we do have several databases that make
>up the datasource for the applications. All of the T-SQL code is in stored
>procedures.
>
>Also, with the CLR now in SQL Server 2005, is there push to move business
>code from the business layer directly into SQL Server?
>
>Thanks
>Bob
>

Thanks

"Mary Chipman [MSFT]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> As a general rule, it's always better to have explicit transactions
> executing on the server, not from the client. You have more control
> using stored procedures (error handling, wait queues, etc) than you do
> from the client where more round trips are involved. As far as pushing
> business code to the server, that's not what the CLR was designed for.
> It's useful for procedural code that would otherwise require
> non-performant server-side cursors or the like. T-SQL was never a true
> programming language, and the CLR fills in the gaps.
>
> -mary
>
> On Thu, 30 Aug 2007 13:14:23 -0400, "Robert Strickland"
> <(E-Mail Removed)> wrote:
>
>>Earlier today I was in a discussion with the team's DBA on where explicit
>>database transaction locks should be placed - in the .Net application or
>>in
>>the stored procedures. This can up when we ran into some concurrency
>>problems for our web application. Is here any guidelines or standards on
>>where the best place is for explicit locks? We use SQL Server 2000 and
>>2005
>>only (no Oracle and MySql). However, we do have several databases that
>>make
>>up the datasource for the applications. All of the T-SQL code is in stored
>>procedures.
>>
>>Also, with the CLR now in SQL Server 2005, is there push to move business
>>code from the business layer directly into SQL Server?
>>
>>Thanks
>>Bob
>>