I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Thursday, March 29, 2012
Consoldate columns in a SELECT
I have a vendor supplied orders table that tracks the fee types associated
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:
AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.
See CREATE TABLE and INSERT statements as well as expected results below.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)
Expect Results:
OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113
Partial SELECT statement
SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERSAs you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:
ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
CASE seq WHEN 1 THEN FEE_1_CD
WHEN 2 THEN FEE_2_CD
WHEN 3 THEN FEE_3_CD
WHEN 4 THEN FEE_4_CD
WHEN 5 THEN FEE_5_CD
WHEN 6 THEN FEE_6_CD
END,
CASE seq WHEN 1 THEN FEE_1_AMT
WHEN 2 THEN FEE_2_AMT
WHEN 3 THEN FEE_3_AMT
WHEN 4 THEN FEE_4_AMT
WHEN 5 THEN FEE_5_AMT
WHEN 6 THEN FEE_6_AMT
END
FROM Orders, ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6) S ( seq )
GO
Now you can do the query for consolidation like:
SELECT order_id, order_type, order_date,
SUM( CASE WHEN code IN ( 'A', 'B' )
THEN Amt ELSE 0 END ) AS "AB_",
SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;
Anith|||cant you just do this, or am I missing something?
SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS
Basically, just add the collumns using coaless to handle the nulls.
"Terri" <terri@.cybernets.com> wrote in message
news:dqovlt$v0d$2@.reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
> See CREATE TABLE and INSERT statements as well as expected results below.
> Thanks to anyone who could help.
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
> INSERT INTO ORDERS
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
> 1,Buy,20060117,8,9
> 2,Buy,20060117,0,11
> 3,Sell,20060117,15,0
> 4,Buy,20060116,103,0
> 5,Sell,20060117,0,54
> 6,Buy,20060116,20,20
> 7,Buy,20060117,3,113
> Partial SELECT statement
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> Perform calcs here AS AB_Consolidation,
> Perform calcs here AS CDE_Consolidation
> FROM ORDERS
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> cant you just do this, or am I missing something?
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
> coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
> CDE_Consolidation
> FROM ORDERS
> Basically, just add the collumns using coaless to handle the nulls.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:
AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.
See CREATE TABLE and INSERT statements as well as expected results below.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)
Expect Results:
OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113
Partial SELECT statement
SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERSAs you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:
ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
CASE seq WHEN 1 THEN FEE_1_CD
WHEN 2 THEN FEE_2_CD
WHEN 3 THEN FEE_3_CD
WHEN 4 THEN FEE_4_CD
WHEN 5 THEN FEE_5_CD
WHEN 6 THEN FEE_6_CD
END,
CASE seq WHEN 1 THEN FEE_1_AMT
WHEN 2 THEN FEE_2_AMT
WHEN 3 THEN FEE_3_AMT
WHEN 4 THEN FEE_4_AMT
WHEN 5 THEN FEE_5_AMT
WHEN 6 THEN FEE_6_AMT
END
FROM Orders, ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6) S ( seq )
GO
Now you can do the query for consolidation like:
SELECT order_id, order_type, order_date,
SUM( CASE WHEN code IN ( 'A', 'B' )
THEN Amt ELSE 0 END ) AS "AB_",
SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;
Anith|||cant you just do this, or am I missing something?
SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS
Basically, just add the collumns using coaless to handle the nulls.
"Terri" <terri@.cybernets.com> wrote in message
news:dqovlt$v0d$2@.reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
> See CREATE TABLE and INSERT statements as well as expected results below.
> Thanks to anyone who could help.
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
> INSERT INTO ORDERS
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
> 1,Buy,20060117,8,9
> 2,Buy,20060117,0,11
> 3,Sell,20060117,15,0
> 4,Buy,20060116,103,0
> 5,Sell,20060117,0,54
> 6,Buy,20060116,20,20
> 7,Buy,20060117,3,113
> Partial SELECT statement
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> Perform calcs here AS AB_Consolidation,
> Perform calcs here AS CDE_Consolidation
> FROM ORDERS
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> cant you just do this, or am I missing something?
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
> coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
> CDE_Consolidation
> FROM ORDERS
> Basically, just add the collumns using coaless to handle the nulls.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.
Monday, March 19, 2012
connections
Hi ,
Want to calculate how much connections remaining in my db server . select
@.@.connections is a way to calculate no. of connections . Can I dedcut this
value from maximum connections & get the value .
Thanks
ARRSelect @.@.max_connections - @.@.connections
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
"Aju" <ajuonline@.yahoo.com> wrote in message
news:umachlvIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Hi ,
> Want to calculate how much connections remaining in my db server . select
> @.@.connections is a way to calculate no. of connections . Can I dedcut this
> value from maximum connections & get the value .
> Thanks
> ARR
>
Want to calculate how much connections remaining in my db server . select
@.@.connections is a way to calculate no. of connections . Can I dedcut this
value from maximum connections & get the value .
Thanks
ARRSelect @.@.max_connections - @.@.connections
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
"Aju" <ajuonline@.yahoo.com> wrote in message
news:umachlvIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Hi ,
> Want to calculate how much connections remaining in my db server . select
> @.@.connections is a way to calculate no. of connections . Can I dedcut this
> value from maximum connections & get the value .
> Thanks
> ARR
>
Wednesday, March 7, 2012
Connection timeout when using transactions
I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.
It goes like this:
SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();
// do some inserts and updates using the transaction
SqlConnection conn2 = new SqlConnection(...);
conn2.Open();
// select using conn2 and decide what to do
conn2.Close();
// do more inserts and updates based on that decision
trans.Commit();
conn1.Commit();
It probably has something to do with the connection pool returning the
already open conn1 to connection2.
In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.
Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?
Thanks.Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
> Thanks.
Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Have you changed the default CommandTimeout from the default? I think
> the default for ASP/ASP.NET is 30 seconds.
No, and it is not the problem. The whole process takes less than 1 second.
The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.
Can anyone reproduce this?|||Natan Vivo wrote:
> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I think
>> the default for ASP/ASP.NET is 30 seconds.
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Natan Vivo wrote:
> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I think
>> the default for ASP/ASP.NET is 30 seconds.
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone. :)
Neil B|||Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
>
No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
>From your description, it's almost certainly a deadlock on sql server.
Without seeing the database code, it's going to be difficult to help
you out.
To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.
Damien|||Tracy McKibben wrote:
> Natan Vivo wrote:
>> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I
>> think the default for ASP/ASP.NET is 30 seconds.
>> No, and it is not the problem. The whole process takes less than 1
>> second.
>> The thing is exactly what I told. If I open an second connection and
>> select something, while connection one is in transaction, it hangs and
>> gives me a connection timeout.
>> Can anyone reproduce this?
> Not knowing what selects you're running in connection 2, I'd have to say
> you're probably deadlocking against the open transaction in connection 1.
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
>
Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.
It seems I can solve this by using the right IsolationLevel when
begining the transaction.
I'll test it later. Thanks!|||Natan Vivo wrote:
> Thanks. It was really a deadlock, the select was selecting data from the
> same table I had already update.
> It seems I can solve this by using the right IsolationLevel when
> begining the transaction.
> I'll test it later. Thanks!
Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
2005 after opening a transaction in asn asp.net application.
It goes like this:
SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();
// do some inserts and updates using the transaction
SqlConnection conn2 = new SqlConnection(...);
conn2.Open();
// select using conn2 and decide what to do
conn2.Close();
// do more inserts and updates based on that decision
trans.Commit();
conn1.Commit();
It probably has something to do with the connection pool returning the
already open conn1 to connection2.
In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.
Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?
Thanks.Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
> Thanks.
Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Have you changed the default CommandTimeout from the default? I think
> the default for ASP/ASP.NET is 30 seconds.
No, and it is not the problem. The whole process takes less than 1 second.
The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.
Can anyone reproduce this?|||Natan Vivo wrote:
> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I think
>> the default for ASP/ASP.NET is 30 seconds.
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Natan Vivo wrote:
> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I think
>> the default for ASP/ASP.NET is 30 seconds.
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone. :)
Neil B|||Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
>
No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
>From your description, it's almost certainly a deadlock on sql server.
Without seeing the database code, it's going to be difficult to help
you out.
To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.
Damien|||Tracy McKibben wrote:
> Natan Vivo wrote:
>> Tracy McKibben wrote:
>> Have you changed the default CommandTimeout from the default? I
>> think the default for ASP/ASP.NET is 30 seconds.
>> No, and it is not the problem. The whole process takes less than 1
>> second.
>> The thing is exactly what I told. If I open an second connection and
>> select something, while connection one is in transaction, it hangs and
>> gives me a connection timeout.
>> Can anyone reproduce this?
> Not knowing what selects you're running in connection 2, I'd have to say
> you're probably deadlocking against the open transaction in connection 1.
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
>
Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.
It seems I can solve this by using the right IsolationLevel when
begining the transaction.
I'll test it later. Thanks!|||Natan Vivo wrote:
> Thanks. It was really a deadlock, the select was selecting data from the
> same table I had already update.
> It seems I can solve this by using the right IsolationLevel when
> begining the transaction.
> I'll test it later. Thanks!
Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Connection timeout when using transactions
I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.
It goes like this:
SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();
// do some inserts and updates using the transaction
SqlConnection conn2 = new SqlConnection(...);
conn2.Open();
// select using conn2 and decide what to do
conn2.Close();
// do more inserts and updates based on that decision
trans.Commit();
conn1.Commit();
It probably has something to do with the connection pool returning the
already open conn1 to connection2.
In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.
Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?
Thanks.Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
> Thanks.
Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Have you changed the default CommandTimeout from the default? I think
> the default for ASP/ASP.NET is 30 seconds.
No, and it is not the problem. The whole process takes less than 1 second.
The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.
Can anyone reproduce this?|||Natan Vivo wrote:
> Tracy McKibben wrote:
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.
http://realsqlguy.com/twiki/bin/vie...latingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Natan Vivo wrote:
> Tracy McKibben wrote:
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone.
Neil B|||Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
>
No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
>From your description, it's almost certainly a deadlock on sql server.
Without seeing the database code, it's going to be difficult to help
you out.
To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.
Damien|||Tracy McKibben wrote:
> Natan Vivo wrote:
> Not knowing what selects you're running in connection 2, I'd have to say
> you're probably deadlocking against the open transaction in connection 1.
> http://realsqlguy.com/twiki/bin/vie...latingADeadlock
>
Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.
It seems I can solve this by using the right IsolationLevel when
begining the transaction.
I'll test it later. Thanks!|||Natan Vivo wrote:
> Thanks. It was really a deadlock, the select was selecting data from the
> same table I had already update.
> It seems I can solve this by using the right IsolationLevel when
> begining the transaction.
> I'll test it later. Thanks!
Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
2005 after opening a transaction in asn asp.net application.
It goes like this:
SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();
// do some inserts and updates using the transaction
SqlConnection conn2 = new SqlConnection(...);
conn2.Open();
// select using conn2 and decide what to do
conn2.Close();
// do more inserts and updates based on that decision
trans.Commit();
conn1.Commit();
It probably has something to do with the connection pool returning the
already open conn1 to connection2.
In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.
Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?
Thanks.Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
> Thanks.
Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Have you changed the default CommandTimeout from the default? I think
> the default for ASP/ASP.NET is 30 seconds.
No, and it is not the problem. The whole process takes less than 1 second.
The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.
Can anyone reproduce this?|||Natan Vivo wrote:
> Tracy McKibben wrote:
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.
http://realsqlguy.com/twiki/bin/vie...latingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Natan Vivo wrote:
> Tracy McKibben wrote:
> No, and it is not the problem. The whole process takes less than 1 second.
> The thing is exactly what I told. If I open an second connection and
> select something, while connection one is in transaction, it hangs and
> gives me a connection timeout.
> Can anyone reproduce this?
Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone.
Neil B|||Natan Vivo wrote:
> I'm getting a timeout error while trying to select data from SQL server
> 2005 after opening a transaction in asn asp.net application.
> It goes like this:
> SqlConnection conn1 = new SqlConnection(...);
> conn1.Open();
> SqlTransaction trans = conn1.Open();
> // do some inserts and updates using the transaction
> SqlConnection conn2 = new SqlConnection(...);
> conn2.Open();
> // select using conn2 and decide what to do
> conn2.Close();
> // do more inserts and updates based on that decision
> trans.Commit();
> conn1.Commit();
> It probably has something to do with the connection pool returning the
> already open conn1 to connection2.
>
No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.
> In the actual process, the select using the 2nd connection is being
> opened in another method, and I don't want to overload the method to
> receive the transaction.
> Is there any other option? Is there any way to really open another
> connection, or the connection pool to not return open connections being
> used in transactions?
>From your description, it's almost certainly a deadlock on sql server.
Without seeing the database code, it's going to be difficult to help
you out.
To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.
Damien|||Tracy McKibben wrote:
> Natan Vivo wrote:
> Not knowing what selects you're running in connection 2, I'd have to say
> you're probably deadlocking against the open transaction in connection 1.
> http://realsqlguy.com/twiki/bin/vie...latingADeadlock
>
Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.
It seems I can solve this by using the right IsolationLevel when
begining the transaction.
I'll test it later. Thanks!|||Natan Vivo wrote:
> Thanks. It was really a deadlock, the select was selecting data from the
> same table I had already update.
> It seems I can solve this by using the right IsolationLevel when
> begining the transaction.
> I'll test it later. Thanks!
Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Saturday, February 25, 2012
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Friday, February 24, 2012
Connection String Options ...Urgent .. plz reply
Hi
In my project , we are using Dsn and DSN less connection, for certain functionality
we are providing users to select tables and views .
we don't want that all system tables and views are listed for selecting , how can we achieve this functionality?
Is there any options in the connection string for restricting system tables and views?
Any help is much appriciated
Thanks
Saurabh
Book mark the following site;http://www.connectionstrings.com/
Tuesday, February 14, 2012
Connection reset error using JDBC
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?
Subscribe to:
Posts (Atom)