|
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 />
from MyTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi
my data field is integer i give a query like this
select * from news where newsid like '123%'
it display error
i want to display news where my newsid is start with 123
please help me
|
|
|
|
|
There is nothing wrong with the code you posted ... what error do you get?
|
|
|
|
|
Yes there is the like operator is not valid agains a numeric or date field. Note the field is an integer. Either turn it into a varchar or use numeric operators
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You need to cast your newsid to varchar.
|
|
|
|
|
I was thinking the same thing, but like implicitly coverts int's to varchar. I think it would help a lot if we knew what the error is instead of guessing.
|
|
|
|
|
Scott Clewell wrote: but like implicitly coverts int's to varchar
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
RAH
|
|
|
|
|
i got it why you got this error!!!
You cannot use a Like wild card with Integers .. is ur newsid is int?
Cheers,
ZAK
|
|
|
|
|
Hi i am having table like this:
Name Dates
N1 7-21-2008
N1 7-24-2008
N2 7-21-2008
N2 7-27-2008
N3 7-24-2008
Now i want o/p like this:
Name Dates
N1 7-21-2008,7-24-2008
N2 7-21-2008,7-27-2008
N3 7-24-2008
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
Well, what do YOU think the sql should be? Have a try, post your sql and then someone may help you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Have a try, post your sql and then someone may help you
Thank you. OP was beginning to sound like an annoying mooch who wants someone to do their work for them.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Exactly. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Beginning to?
Sounded like it from post number 2. 
|
|
|
|
|
Not just the past two or so, but the OP was going on about the same thing the other day. Did get some replies. It just goes to show how true it is with my sig from John Simmons 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi everybody,
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?
Thank you... 
|
|
|
|
|
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)
SqlException
"
But on all other cases, it works well. It's very strange I think. Have you any ideas?
|
|
|
|
|
Hi,
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
Mika
|
|
|
|
|
Timeout is set to default, it's not defined in connection string. About connection pool - I don't know, I haven't direct access to this server for now.
Answer your questions:
- Request is a select statement
- I'm using linq database context
- error occurs after 10 -50 seconds.
- no, I don't
- no I'm not
This error occurs with 80% frequency, not always.
Thanks.
|
|
|
|
|
Hmm,
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
Mika
|
|
|
|
|
Thanks,I'll try 
|
|
|
|
|
Situation changed, now it works, but not always. I reproduced the same query several times.
1. I logged in
2. Make this query - everything okay
3. Then I have tried to repeat it - exception occured
4. Then I made browser refresh(F5), and tried again - works well.
I have tried to reproduce the same actions locally - with the same database on my local MS SQL Server 2005 - everything good in 100%! I don't know what to say
|
|
|
|
|
Ok,
- When you say that situation changed, what did you do in order to achieve this change in behaviour?
- Do you close the connection after running the query and before the connection object goes out of scope?
Because what you wrote, it seems that something from the first execution is affecting the second, something that browser refresh resets.
If you could post the key-parts of the code (connection opening, partial connection string without any user information, command execution and connection closing) it would help a lot (I hope  ).
Mika
|
|
|
|
|
My connection string:
"Data Source=dbserver;Initial Catalog=mydb;Persist Security Info=True;User ID=user;Password=password;Connection Timeout=60;Packet Size=16384;Pooling=false"
I could not pass all programming code, it's very huge, I'll describe common steps,so, I click some button then:
1. EntitiesDataContext db = MyProject.Misc.HttpDataContext<entitiesdatacontext>.Current;
2. Client client = Application.GetClient();
3. db.GetPartsByClientType(...)</entitiesdatacontext>
Here is this property for my class httpDataContext<t> for step 1:
public static T Current {
get {
string key = typeof(T).FullName;
T datacontext = (T)System.Web.HttpContext.Current.Items[key];
if (datacontext == null)
System.Web.HttpContext.Current.Items.Add(key, datacontext = new T());
return datacontext;
}
}
For step 2 . In function GetClient I'm using the same
EntitiesDataContext db = MyProject.Misc.HttpDataContext<entitiesdatacontext>.Current;</entitiesdatacontext> to get db context.
And then on step 3 my query executing several time in cycle "for".
I hope this information will help
|
|
|
|
|
Ok,
Didn't yet have any specific idea.
Is the connection open all the time during a single request? I mean that there is no way that the connection could be closed or disposed by your code before the failing statement?
Could you:
- wire StateChange event for the connection
- every time the state changes, write to a log file (or similar) both CurrentState and OriginalState from event arguments
- wire InfoMessage event for the connection
- write every element from Errors collection from message event argument. Also write Message and Source
- wire Disposed event for the connection
- when Disposed occurs, write info about this to the log file
- write to the log file every time you get data context from httpcontext
- write to the log file every time you call open method on the sql connection
- write to the log file every time you execute the statements (especially the failing one)
- write to the log file every time you call close method on the sql connection
This should give a good output which shows the order how things are happening and some extra info what is actually happening.
Another thing that I'm thinking of is that why this doesn't happen on your local SQL Server but happens if you try to use SQL Server on another server. This could be because:
- network problems (packets are occasionally not delivered)
- the server under heavy stress and cannot respond
What do you think, could the reason be in those?
Mika
|
|
|
|
|
Hi frns,
select CONVERT(varchar ,[regdate],103) Dates,[user],[client facility],sum([payroll score])LC from [sheet1$]
group by [user],[client facility], CONVERT(varchar ,[regdate],103) order by [user]
i am programatically retreving data from excel sheet. for retriving data from excel the query i have written is the above but it is giving me error.
the error is :Syntax error (missing operator) in query expression 'CONVERT(varchar ,[regdate],103) Dates'.
How to use the convert function in excel query
regards
sunilwise
modified on Monday, December 1, 2008 4:53 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
