a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------

A transport-level error has occurred when receiving results from the server.(provider:TCP Provid

Hi,

I am using SQL Server 2005,

while trying to retrieve data from the database; I am getting the following

error:

A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me out?
Waiting for your response

Sudhakar

Hi Sudhakar

Please can you be a little more specific.

Are you getting this error in SQL Query Analyzer? Are you getting this from some sort of Access or .NET front end application? Or is it from an ASP application?

if you provide a little more info im sure we can work this out.

Barry

|||Hi Barry,

I had developed a Web Application using ASP.Net 2005 and using SQL Server 2005 as backend ,

Please review the following code:

**************************************************************

Try
Dim con As New SqlConnection("server=101998-WEB1;database=TriMark;Integrated Security=SSPI")
con.Open()
Dim da As New SqlDataAdapter("select * from tblusers", con)
Dim ds As New DataSet
da.Fill(ds)
Catch ex As Exception
Response.Write(ex.Message)
End Try

*************************************************************

I was getting fllowing error when we try to fetch data from the database through .net application.

Error: A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me to sort out this?

Waiting for your response

Sudhakar

|||

Hi, Sudhakar

To lock down the issue, can you provide the stack info when the error occured? Also, can you following the blog:http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx.

to collect ETW trace and send it to protcols@.microsoft.com

Thanks!

Ming.

|||

Is there any possibility the application uses one connection from multiple threads?

If one thread closed the connection, and another tried to use it it could generate this error.

|||Sudhakar, Can you please share with me your findings on this issue. I am seeing similar issue randomly Thanks.sql

A transport-level error has occurred when receiving results from the server.(provider:TCP Provid

Hi,

I am using SQL Server 2005,

while trying to retrieve data from the database; I am getting the following

error:

A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me out?
Waiting for your response

Sudhakar

Hi Sudhakar

Please can you be a little more specific.

Are you getting this error in SQL Query Analyzer? Are you getting this from some sort of Access or .NET front end application? Or is it from an ASP application?

if you provide a little more info im sure we can work this out.

Barry

|||Hi Barry,

I had developed a Web Application using ASP.Net 2005 and using SQL Server 2005 as backend ,

Please review the following code:

**************************************************************

Try
Dim con As New SqlConnection("server=101998-WEB1;database=TriMark;Integrated Security=SSPI")
con.Open()
Dim da As New SqlDataAdapter("select * from tblusers", con)
Dim ds As New DataSet
da.Fill(ds)
Catch ex As Exception
Response.Write(ex.Message)
End Try

*************************************************************

I was getting fllowing error when we try to fetch data from the database through .net application.

Error: A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me to sort out this?

Waiting for your response

Sudhakar

|||

Hi, Sudhakar

To lock down the issue, can you provide the stack info when the error occured? Also, can you following the blog:http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx.

to collect ETW trace and send it to protcols@.microsoft.com

Thanks!

Ming.

|||

Is there any possibility the application uses one connection from multiple threads?

If one thread closed the connection, and another tried to use it it could generate this error.

|||Sudhakar, Can you please share with me your findings on this issue. I am seeing similar issue randomly Thanks.

A transport-level error has occurred when receiving results from the server.(provider:TCP Pr

Hi,

I am using SQL Server 2005,

while trying to retrieve data from the database; I am getting the following

error:

A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me out?
Waiting for your response

Sudhakar

Hi Sudhakar

Please can you be a little more specific.

Are you getting this error in SQL Query Analyzer? Are you getting this from some sort of Access or .NET front end application? Or is it from an ASP application?

if you provide a little more info im sure we can work this out.

Barry

|||Hi Barry,

I had developed a Web Application using ASP.Net 2005 and using SQL Server 2005 as backend ,

Please review the following code:

**************************************************************

Try
Dim con As New SqlConnection("server=101998-WEB1;database=TriMark;Integrated Security=SSPI")
con.Open()
Dim da As New SqlDataAdapter("select * from tblusers", con)
Dim ds As New DataSet
da.Fill(ds)
Catch ex As Exception
Response.Write(ex.Message)
End Try

*************************************************************

I was getting fllowing error when we try to fetch data from the database through .net application.

Error: A transport-level error has occurred when receiving results

from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)

But I am getting this error randomly.

Can some one help me to sort out this?

Waiting for your response

Sudhakar

|||

Hi, Sudhakar

To lock down the issue, can you provide the stack info when the error occured? Also, can you following the blog:http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx.

to collect ETW trace and send it to protcols@.microsoft.com

Thanks!

Ming.

|||

Is there any possibility the application uses one connection from multiple threads?

If one thread closed the connection, and another tried to use it it could generate this error.

|||Sudhakar, Can you please share with me your findings on this issue. I am seeing similar issue randomly Thanks.

A question of Sum

Hi,
how can I retrieve the sum till the end of each page and put it in the end
of corresponding page?
Ex: Page1 Page 2
10 40
20 50
30 60
Sum: 60 Sum: 60 (from the previous page) + 150 = 210
Any help would be appreciated.You do not know when paging occurs, expect by having groups break on a
page... SO you would create groups and do the sums in the group footers,
breaking each group on a new page...
--
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
"Goncalo" <Goncalo@.discussions.microsoft.com> wrote in message
news:125001F8-B57C-4CC2-A7D7-750FECE57E69@.microsoft.com...
> Hi,
> how can I retrieve the sum till the end of each page and put it in the end
> of corresponding page?
> Ex: Page1 Page 2
> 10 40
> 20 50
> 30 60
> Sum: 60 Sum: 60 (from the previous page) + 150 => 210
> Any help would be appreciated.
>|||Thanks for answering the question...
but i still have the same problem when groups expand to more then 1 page,
right?
Goncalo
"Wayne Snyder" wrote:
> You do not know when paging occurs, expect by having groups break on a
> page... SO you would create groups and do the sums in the group footers,
> breaking each group on a new page...
> --
> 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
> "Goncalo" <Goncalo@.discussions.microsoft.com> wrote in message
> news:125001F8-B57C-4CC2-A7D7-750FECE57E69@.microsoft.com...
> > Hi,
> > how can I retrieve the sum till the end of each page and put it in the end
> > of corresponding page?
> >
> > Ex: Page1 Page 2
> > 10 40
> > 20 50
> > 30 60
> >
> > Sum: 60 Sum: 60 (from the previous page) + 150 => > 210
> >
> > Any help would be appreciated.
> >
>
>