select distinct [date],<br />
(select count(t1.status) from MyTable as t1 where status=2 and t1.date=MyTable.[date]) as [Total 2],<br />
(select count(t1.status) from MyTable as t1 where status=3 and t1.date=MyTable.[date]) as [Total 3]<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
Ok - I was not aware of that, it still feels like he is attempting the wrong thing, I would never rely on an implicit conversion there is a cost in the implicit conversion that I find unacceptable and the bloody thing will bite you sometime in the future.
Never underestimate the power of human stupidity
I made a primitive cost control database system using Microsoft Access 2007 as a front-end (don't ask my why did I use Microsoft Access!!!) and SQL Server 2005 Enterprise as a back-end
This program depends on regrouping data, so I have sections and each section in front of it has the total of it's item
Everything is working smoothly, except that the last line in any list, which is empty, gives me #Error in calculated controls
What should I do to avoid that?
Hi! I have such problem: when my web application make a request to
MS SQL Server 2005, and the last one have to send back a lots of data, it's throwing an error:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
But on all other cases, it works well. It's very strange I think. Have you any ideas?
The first thing that comes in mind is timeout. What is your operation timeout defined in connection and how long the data retrieval is taking (until you get the error).
Another thing is your connection pool. Sure that the connection isn't aging out?
Few additional questions:
- is the request a select statement or procedure call
- check the state of the connection before executing the command. Is it open?
- does the error occur immediately or when some time has elapsed
- what about database server side? Do you see the open connection
- are you able to trace the statement execution at server side
What you could try is (modify only one thing at once and if nothing is changed, return to original configuration):
- add connection timeout-parameter to connection string ("Connection Timeout=60") The default id 15 seconds.
If the server is running low on resources, it may be unable to give you a connection in 15 seconds.
- increase packet size by adding "Packet Size=16384". Default is 8192.
This way the server doesn't need to send so many packets especially when large amount of data is to be sended
- Try disabling connection pooling by adding "Pooling=false" to your connection string. Default is true.
This will eliminate possible aging problems in pool
if possible at database server side you should check:
- start the query and check in database, how your connection is doing.
This can be done by using Management studio or sp_who stored procedure. You should see your connection in "runnable" state
- check the error logs for sql server. When the query is returning you an error, is anything recorded in error log
And of course you could try to run the select statement using 'normal' SqlCommand class along with data reader without LINQ. Just to make sure that LINQ isn't causing any problems.
I'm sorry that I cannot be more specific, but the problem isn't the easiest one to solve.
If you find out anything, please post your findings
Last Visit: 31-Dec-99 18:00 Last Update: 30-Sep-23 14:08