Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Friday, March 30, 2012

Output Column names in each Row along with the row Value

Hello,

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

Thank you! :-)

YasOn Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?
>
eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?
>
Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3
>
Thank you! :-)
>
Yas


Not sure what you are actually looking at

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')

select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp

drop table #temp|||On 29 Jun, 14:27, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,


>

Quote:

Originally Posted by

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?


>

Quote:

Originally Posted by

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?


>

Quote:

Originally Posted by

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3


>

Quote:

Originally Posted by

Thank you! :-)


>

Quote:

Originally Posted by

Yas


>
Not sure what you are actually looking at
>


Hi, thanks for the response. Basically I want to have a query in a DTS
package that outputs to a text file with each line containing 1 row of
results.
What I would like to have is on each line before a given value is
written the column header for that value is also written. the file is
a semicolon delimmeted file so it would look something like this...

col1Name;value1;col2Name;value2;col3Name;value3... .etc

so...
FirstName;firstNameValue;SecondName;secondNameValu e;Occupation;occupationValue..etc

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?
especially as I want to do this in a DTS package Export Data which
esentially has a create table type command to ouptut to a text file
and throws errors that destination column name not defined.

Thanks :-)

Quote:

Originally Posted by

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')
>
select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp
>
drop table #temp

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?


That would indeed be the way to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 29 Jun, 23:38, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?


>
That would indeed be the way to do it.


Thanks! :-)

Output Buffer Remove Row?

I am using a script component to create the output buffer dynamically. I use the Outputbuffer.AddRow() call. I then set all the fields I want, and its added to the output and later inserted into the database. If a field value fails it causes an error, but the record is partially inserted upto the point where the set field command caused the error. So if I set 10 fields, and it fails on field 5 it inserts data for the 5 fields that worked and nulls into the others.

As a result I have a try catch clause, and if it fails I want to cancell the addition of the new row. Is there a command like RemoveRow(), rollback, etc that can be used to not insert the record in error?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(?)

End Try

Can you provide more of your script? Maybe even all of it?|||

The script is very long. I do the same basic task in three scripts for three different feeds, this is the shortest one. The only difference (basically) between this and the others is the number of field in the outputbuffer. Ok.. Here is the code:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text

Imports Company.SSIS.Functions

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim PaymentRecord As String = System.Text.Encoding.Unicode.GetString(Row.PaymentMethods.GetBlobData(0, CInt(Row.PaymentMethods.Length)))

Dim PaymentArray() As String

Dim pi As Integer

PaymentArray = Parse.StringParse(PaymentRecord, "\|")

For pi = 0 To PaymentArray.Length - 1

If PaymentArray(pi) <> "" Then

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.ServiceRecordID = Row.ServiceRecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.AddRow()

PaymentErrorOutputBuffer.ErrorRecordID = Row.ServiceRecordID

PaymentErrorOutputBuffer.ErrorCode = e.Message

PaymentErrorOutputBuffer.ErrorData.AddBlobData(Encoding.UTF8.GetBytes(PaymentArray(pi)))

End Try

End If

Next

End Sub

End Class

The Company.SSIS.Functions import is a dll I wrote. It has the Parse.StringParse(PaymentRecord, "\|") call. It is simply a regex function. I listed it below.. but basically splits the delimited field into multiple rows. So the goal is that it is called per row, splits the data in the row into an array and stores it as records. If the paymentmethod store fails, the record still gets inserted into the datase with the sequence and service record id. The catch directs creates a second output, that i then store to my error table. I essentially want the catch to still send to my error table, but for the first output not to be created if it fails and enters the catch.

Public Class Parse

Public Shared Function StringParse(ByVal StringValue As String, ByVal ParseValue As String) As String()

If Replace(StringValue, "", Nothing) Is Nothing Then

Return Split(StringValue)

Else

Dim pattern As String = ParseValue & "(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"

Dim r As RegularExpressions.Regex = New RegularExpressions.Regex(pattern)

Return r.Split(StringValue)

End If

End Function

End Class

|||What are some of the reasons why the assignment to PaymentMethod could fail?|||

Payment method is varchar(20) field. The output is defined as String 20. Primary reason of failure is text truncation. On other code it may be bad date/time stamp, non integer value, etc. Typically it is a data error.

I can do a specific check on payment method to make sure its not in error before calling the addrow() so that the output is not called unless it will succeed. But for scenarios I don't think of, or conditions where I need to check 20+ fields this is not as practical. So I was hoping to use a try/catch process to insert the row if its good, otherwise write to the error table.

|||Yep, I hear ya... I just wanted you to think about performing data validation up front, and you have. Good.

Some of the other code experts may have to chime in now. |||Remove the current row with the call to the PipelineBuffer.RemoveRow() method. The script component does not expose the PipelineBuffer.RemoveRow() method directly, but its does expose the PipelineBuffer(s) in PrimeOutput, on which RemoveRow can be called.

Taking your scenario as example, save the underlying pipeline buffers, and call any PipelineBuffer method on them, including RemoveRow.

Imports System

Imports

Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits

UserComponent

Private

normalBuffer As PipelineBuffer

Private

errorBuffer As PipelineBuffer

Public Overrides Sub

Input0_ProcessInputRow _

(ByVal

Row As Input0Buffer)

Try

With

PaymentOutputBuffer

.AddRow()

.FirstString =

Row.GeneratedStr1

End

With

Catch

ex As Exception

With

PaymentErrorOutputBuffer

.AddRow()

.ErrorMessage =

ex.Message.Substring(0, _

Math.Min(ex.Message.Length,

250))

End

With

normalBuffer.RemoveRow()

' Remove

the row from normal, as normalBuffer

' is the

underlying PipelineBuffer

' for

PaymentOutputBuffer

End Try

End Sub

Public Overrides Sub

PrimeOutput(ByVal Outputs As Integer, _

ByVal

OutputIDs() As Integer,

_

ByVal

Buffers() As

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' save

underlying PipelineBuffer to allow for calls to

'

PipelineBuffer methods

normalBuffer = Buffers(0)

errorBuffer = Buffers(1)

MyBase.PrimeOutput(Outputs,

OutputIDs, Buffers)

End Sub

End Class

|||Thanks.. .that works and does what I need. I was curious about the Buffers(x) value and if there is another way to set it to the specific outputbuffer. I tried to look at the help on the Pipeline buffer, but was not able to determine it. I want to be able to reference the Buffers(PaymentOutputBuffer) vs. a 0 so that if people add additional buffers, etc it will not break. Is there a way to make this go off the actual buffer name?|||Yes, the buffers can be referenced by name.

The OutputIDs array parameter to PrimeOutput has the integer buffer ids (not the offset) in coordinated order with the Buffers array.

The buffer's name is available via a call to ComponentMetaData.OutputCollection.FindObjectById(<id from OutputIds here>).Name.

See the following BOL reference for the OutputCollection methods: ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Pipeline_Wrapper_IDTSOutputCollection90_Members.htmsql

Output Buffer Remove Row?

I am using a script component to create the output buffer dynamically. I use the Outputbuffer.AddRow() call. I then set all the fields I want, and its added to the output and later inserted into the database. If a field value fails it causes an error, but the record is partially inserted upto the point where the set field command caused the error. So if I set 10 fields, and it fails on field 5 it inserts data for the 5 fields that worked and nulls into the others.

As a result I have a try catch clause, and if it fails I want to cancell the addition of the new row. Is there a command like RemoveRow(), rollback, etc that can be used to not insert the record in error?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(?)

End Try

Can you provide more of your script? Maybe even all of it?|||

The script is very long. I do the same basic task in three scripts for three different feeds, this is the shortest one. The only difference (basically) between this and the others is the number of field in the outputbuffer. Ok.. Here is the code:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text

Imports Company.SSIS.Functions

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim PaymentRecord As String = System.Text.Encoding.Unicode.GetString(Row.PaymentMethods.GetBlobData(0, CInt(Row.PaymentMethods.Length)))

Dim PaymentArray() As String

Dim pi As Integer

PaymentArray = Parse.StringParse(PaymentRecord, "\|")

For pi = 0 To PaymentArray.Length - 1

If PaymentArray(pi) <> "" Then

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.ServiceRecordID = Row.ServiceRecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.AddRow()

PaymentErrorOutputBuffer.ErrorRecordID = Row.ServiceRecordID

PaymentErrorOutputBuffer.ErrorCode = e.Message

PaymentErrorOutputBuffer.ErrorData.AddBlobData(Encoding.UTF8.GetBytes(PaymentArray(pi)))

End Try

End If

Next

End Sub

End Class

The Company.SSIS.Functions import is a dll I wrote. It has the Parse.StringParse(PaymentRecord, "\|") call. It is simply a regex function. I listed it below.. but basically splits the delimited field into multiple rows. So the goal is that it is called per row, splits the data in the row into an array and stores it as records. If the paymentmethod store fails, the record still gets inserted into the datase with the sequence and service record id. The catch directs creates a second output, that i then store to my error table. I essentially want the catch to still send to my error table, but for the first output not to be created if it fails and enters the catch.

Public Class Parse

Public Shared Function StringParse(ByVal StringValue As String, ByVal ParseValue As String) As String()

If Replace(StringValue, "", Nothing) Is Nothing Then

Return Split(StringValue)

Else

Dim pattern As String = ParseValue & "(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"

Dim r As RegularExpressions.Regex = New RegularExpressions.Regex(pattern)

Return r.Split(StringValue)

End If

End Function

End Class

|||What are some of the reasons why the assignment to PaymentMethod could fail?|||

Payment method is varchar(20) field. The output is defined as String 20. Primary reason of failure is text truncation. On other code it may be bad date/time stamp, non integer value, etc. Typically it is a data error.

I can do a specific check on payment method to make sure its not in error before calling the addrow() so that the output is not called unless it will succeed. But for scenarios I don't think of, or conditions where I need to check 20+ fields this is not as practical. So I was hoping to use a try/catch process to insert the row if its good, otherwise write to the error table.

|||Yep, I hear ya... I just wanted you to think about performing data validation up front, and you have. Good.

Some of the other code experts may have to chime in now. |||Remove the current row with the call to the PipelineBuffer.RemoveRow() method. The script component does not expose the PipelineBuffer.RemoveRow() method directly, but its does expose the PipelineBuffer(s) in PrimeOutput, on which RemoveRow can be called.

Taking your scenario as example, save the underlying pipeline buffers, and call any PipelineBuffer method on them, including RemoveRow.

Imports System

Imports

Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits

UserComponent

Private

normalBuffer As PipelineBuffer

Private

errorBuffer As PipelineBuffer

Public Overrides Sub

Input0_ProcessInputRow _

(ByVal

Row As Input0Buffer)

Try

With

PaymentOutputBuffer

.AddRow()

.FirstString =

Row.GeneratedStr1

End

With

Catch

ex As Exception

With

PaymentErrorOutputBuffer

.AddRow()

.ErrorMessage =

ex.Message.Substring(0, _

Math.Min(ex.Message.Length,

250))

End

With

normalBuffer.RemoveRow()

' Remove

the row from normal, as normalBuffer

' is the

underlying PipelineBuffer

' for

PaymentOutputBuffer

End Try

End Sub

Public Overrides Sub

PrimeOutput(ByVal Outputs As Integer, _

ByVal

OutputIDs() As Integer,

_

ByVal

Buffers() As

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' save

underlying PipelineBuffer to allow for calls to

'

PipelineBuffer methods

normalBuffer = Buffers(0)

errorBuffer = Buffers(1)

MyBase.PrimeOutput(Outputs,

OutputIDs, Buffers)

End Sub

End Class

|||Thanks.. .that works and does what I need. I was curious about the Buffers(x) value and if there is another way to set it to the specific outputbuffer. I tried to look at the help on the Pipeline buffer, but was not able to determine it. I want to be able to reference the Buffers(PaymentOutputBuffer) vs. a 0 so that if people add additional buffers, etc it will not break. Is there a way to make this go off the actual buffer name?|||Yes, the buffers can be referenced by name.

The OutputIDs array parameter to PrimeOutput has the integer buffer ids (not the offset) in coordinated order with the Buffers array.

The buffer's name is available via a call to ComponentMetaData.OutputCollection.FindObjectById(<id from OutputIds here>).Name.

See the following BOL reference for the OutputCollection methods: ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Pipeline_Wrapper_IDTSOutputCollection90_Members.htm

Monday, March 26, 2012

Outer join runs differently on SQL Server and Oracle

Oracle 9i:
create table t(i integer)
Table created
insert into t values(1)
1 row inserted
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
0 rows selected
-- I beleive this is wrong
drop table t
Table dropped
the same query against MS SQL Server 2000:
create table t(i integer)
insert into t values(1)
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
i1 i2
-- --
1 NULL
-- I think this is correct
(1 row(s) affected)
drop table t
What do you thinkAK wrote:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
I think you're using an unpatched release of Oracle 9i, whichever
release that may be (9i says NOTHING about which release or patch level
you're using):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL>
SQL>
SQL> select t1.i i1, t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
The results look exactly the same to me between the two. Care to try
again, this time with a patched release of Oracle?
David Fitzjarrell|||AK schrieb:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
>
Oracle ansi join implementation had some bugs ( much of them fixed in
recent versions/patches ), but in your case i definitely can't reproduce
your behaviour ( both on 9iR2 and 10gR1/R2)
oracle@.col-fc1-02:~/sql >sqlplus scott/tiger
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
Here is output for 10gR1
oracle@.wks01:~> sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0
3 /
I1 I2
-- --
1
SQL>
Best regards
Maxim|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback

Wednesday, March 7, 2012

OSQL DELETE Using LIKE, with a cascading effect

Edit: Sorry This is OSQL.

What I use as my query is:
"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"

The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.
This is only MSDE so I don't have anyother way to open the table.

Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?

Thanks of all your help, I am just a tech support guy beating his head against a wall..do not use like with datetime. use > < >= <=. to have cascading deletes you have to setup your forien key constraints to do so.|||Edit: Sorry This is OSQL.I did have my nose up to the monitor first time I saw the thread muttering "is that a Q?".

Title edited.

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

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

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