Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT query
Hi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||?
|||?
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegr oups.com...
> ?
>
|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.
|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegr oups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>
|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query
|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegr oups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>
|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Friday, March 30, 2012
OUTPUT Clause SQL Server 2005
Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.|||Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@.OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.|||Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@.OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
OUTPUT Clause SQL Server 2005
Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books
Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-
08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books
Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-
08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
Friday, March 23, 2012
Out of Range Error When Updating A Decimal Value
I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny?
Parameter value '100.0' is out of range.
Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.Exception Details:System.ArgumentException: Parameter value '100.0' is out of range.
Source Error:
Line 1841: int returnValue;
Line 1842: try {
Line 1843: returnValue = command.ExecuteNonQuery();
Line 1844: }
Line 1845: finally {
Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs Line: 1843
Stack Trace:
[ArgumentException: Parameter value '100.0' is out of range.]
System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +4085
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +11
5
StatisticsDALTableAdapters.StatisticsTableAdapter.UpdateStatistics(Nullable`1 ID, Nullable`1 PlayerID, Nullable`1 FG, Nullable`1 TTP, Nullable`1 FT, Nullable`1 RPG, Nullable`1 DEFR, Nullable`1 OFFR, Nullable`1 APG, Nullable`1 SPG, Nullable`1 BPG, Nullable`1 Turnovers, Nullable`1 PPG, String SYear, String EYear, String StatisticsType) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs:1843
YourHoopSpace.BLL.StatisticsBLL.UpdateStatistic(Int32 ID, Int32 playerID, Nullable`1 fg, Nullable`1 ttp, Nullable`1 ft, Nullable`1 rpg, Nullable`1 defr, Nullable`1 offr, Nullable`1 apg, Nullable`1 spg, Nullable`1 bpg, Nullable`1 turnovers, Nullable`1 ppg, String sYear, String eYear, String statisticsType) in f:\MyProfile\App_Code\BLL\StatisticsBLL.cs:128
Is this the problem?
int returnValue;
Try decimal in your code.|||
HI~
How do you pass the parameters?
Here is sample code:
string str_test = TextBox1.Text; SqlConnection conn =new SqlConnection("Server=T-YOUNGF\\SQLEXPRESS;Database=pubs;uid=sa;pwd=pwd"); SqlCommand cmd =new SqlCommand("update decimal_test3 set column1=@.column1", conn); cmd.Parameters.AddWithValue("@.column1", 100.0); conn.Open();int i = cmd.ExecuteNonQuery(); Response.Write(i.ToString() +" row(s) has updated"); conn.Dispose();And table:
create table decimal_test3 (column1decimal (4,1));|||The problem was on my update I needed a decimal of (4,1) instead of (3,1) which I changed in the table not the stored procedure. Why does it need 4 instead of 3 to store 100?|||
decimal of (4,1) can hold upto 999.9
decimal of (3,1) can hold upto 99.9
So 100 is invalid.See decimal and numeric for details.
|||Thank you. I looked at that page, but really couldnt figure it out but now I know.sqlFriday, March 9, 2012
osql timeout
Hi,
I have several big tables with rows more than 25 mil rows
and to update/delete/insert data in these tables,
it can take minutes.
I use BULK Insert/DELETE/Update with osql.
While I run one of these updates,
if I try to select, it seems like both read and write get locked.
Shouldn't SQL resolve this kind of locking?
I have several big tables with rows more than 25 mil rows
and to update/delete/insert data in these tables,
it can take minutes.
I use BULK Insert/DELETE/Update with osql.
While I run one of these updates,
if I try to select, it seems like both read and write get locked.
Shouldn't SQL resolve this kind of locking?
I left these to see if it gets resolved but both never returned.
So I need to kill these processes.
Does anyone have any scripts to find how long queries are running?
Also I need to make osql timeout and tried -t but it didn't work.
I used -t 1200 with DELETE in osql but it was running for more than 40
minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
it and it worked.
Shouldn't the query get killed after 10 minutes?
What is exactly -t option for ?
thanks,(second714@.hotmail.com) writes:
> I have several big tables with rows more than 25 mil rows
> and to update/delete/insert data in these tables,
> it can take minutes.
> I use BULK Insert/DELETE/Update with osql.
> While I run one of these updates,
> if I try to select, it seems like both read and write get locked.
> Shouldn't SQL resolve this kind of locking?
If they are deadlocked, that is waiting for each other, SQL Server should
indeed detect this situation, and select one of them as a deadlock
victim.
But it may be the case that the first process is blocked by something
else, and when you try to select, the SELECT statement is blocked by
the update.
I have a tool on my web site, aba_lockinfo, which is good for looking
at locking chains. You can get it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
> Does anyone have any scripts to find how long queries are running?
You cannot really get the time how long a certain query has been
running, but you can see when a process last submitted a batch. The
latter is included in the information returned by aba_lockinfo.
> Also I need to make osql timeout and tried -t but it didn't work.
> I used -t 1200 with DELETE in osql but it was running for more than 40
> minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
> it and it worked.
> Shouldn't the query get killed after 10 minutes?
> What is exactly -t option for ?
I didn't have the patience to try -t 1200, but I did try this:
E:\temp>osql -E -t 10
1> WAITFOR DELAY '00:00:20'
2> go
Timeout expired
So it does seem to work. I can't say why your DELETE did not timeout, but
it might be that it has to rollback the deletion that far, and rolling
back takes longer time than executing the command.
--
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 for the info!
I'll try aba_lockinfo and test timeout more with select.
thanks again,
Monday, February 20, 2012
Orphaned Sessions and locks
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
CB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>
|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
CB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>
|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
Orphaned Sessions and locks
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
--
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
--
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
Orphaned Sessions and locks
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests wa
s
> to update a row in the database and break the network connection before th
e
> transaction could be commited. This locks the row in the database for abo
ut
> 5 minutes before Sql Server realises that the communication has been broke
n,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests wa
s
> to update a row in the database and break the network connection before th
e
> transaction could be commited. This locks the row in the database for abo
ut
> 5 minutes before Sql Server realises that the communication has been broke
n,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
Subscribe to:
Posts (Atom)