Sql query result ambegous

Question

Post reply

Sql query result ambegous

achleshsoni

Mr or Mrs. 500

Points: 504
More actions
May 23, 2009 at 6:30 am

#384642

Sql server provides ambiguous result of queries suddenly working fine and it works start properly again after sometime or after restart of mssql service.
how can i get what is wrong happening with server? sql log shows no error ? how can i figure tht out?
earlier i got problem like :
1. i got issue like cachestore flush which has been done because of aspnetdb autoclose ON
link : http://www.sqlservercentral.com/Forums/FindPost682373.aspx
Then i got issue like
2.
My sa login continuously stopped working and work only when i change the password of sa.
Which i fixed by using windows NT service and disabled the mixed mode authentication.
http://www.sqlservercentral.com/Forums/FindPost720532.aspx
Now still i am having the same issue.. My queries show ambiguous result.... Pls help me how i can get more details of the same..

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

achleshsoni (5/23/2009)My queries show ambiguous result

Prove it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

achleshsoni Mr or Mrs. 500 Points: 504 More actions · Answer 2

On home page of my website there is very simple query i.e. select * from some table" which shows a list of data... and it was working for last 2-3 year now suddenly it start displaying "2" or "3" instead of list of data... and after restart of server it started working fine....

achleshsoni Mr or Mrs. 500 Points: 504 More actions · Answer 3

PaulB (5/23/2009)
achleshsoni (5/23/2009)My queries show ambiguous result
Prove it.

Please look at my last 2 posted topics.. url has been given on first post of this topic

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 4

You have not really explained what is wrong.

SQL Server does not return ambiguous results unless you mean that

SELECT * from MyTable

doesn't return the same results in the same order. It might or might not, because there is no ORDER BY, but it will ALWAYS return the same results. It returns the entire contents of the table.

Show what used to return, and what it returning now.

As a further note, don't use SELECT *, list the actual columns. It's just poor practice.

achleshsoni Mr or Mrs. 500 Points: 504 More actions · Answer 5

Steve Jones - Editor (5/23/2009)
You have not really explained what is wrong.
SQL Server does not return ambiguous results unless you mean that
SELECT * from MyTable
doesn't return the same results in the same order. It might or might not, because there is no ORDER BY, but it will ALWAYS return the same results. It returns the entire contents of the table.
Show what used to return, and what it returning now.
As a further note, don't use SELECT *, list the actual columns. It's just poor practice.

As i said it suppose to return a list data( which it was returning for several years) and once after this problem

You may experience a decrease in query performance(http://support.microsoft.com/kb/917828 ) it start returning one number 2 etc...

Yes it was column only * i mentioned for simplicity....

and i said each time when i get this kind of result at that time sql log shows startup events.

Thanks in advance..

achleshsoni Mr or Mrs. 500 Points: 504 More actions · Answer 6

Steve Jones - Editor (5/23/2009)
You have not really explained what is wrong.
SQL Server does not return ambiguous results unless you mean that
SELECT * from MyTable
doesn't return the same results in the same order. It might or might not, because there is no ORDER BY, but it will ALWAYS return the same results. It returns the entire contents of the table.
Show what used to return, and what it returning now.
As a further note, don't use SELECT *, list the actual columns. It's just poor practice.

This forum also shows the same problem:::

[url= http://www.netnewsgroups.net/group/microsoft.public.dotnet.framework.aspnet/topic34227.aspx%5D

http://www.netnewsgroups.net/group/microsoft.public.dotnet.framework.aspnet/topic34227.aspx%5B/url%5D

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 7

I did once see an issue with SQL Server 2000 SP3a where a particular query returned different results (as in a different number of rows) depending on whether parallelism was used - the problem was repeatable by amending the OPTION (MAXDOP x) query hint to toggle the use of parallelism. Presumably this bug has long-since been fixed though.

Chris

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 8

SELECT * is typically resolved at compile time, and expanded to be the columns. So over time, if you've modified the schema, and a plan exists, this might not return columns or error out.

Ordering aside (explained above) the results are the same.

You need to provide an explanation and some samples of what you think is ambiguous. This

now suddenly it start displaying "2" or "3" instead of list of data... and after restart of server it started working fine

means nothing to us. We have no idea what the "list of data" is and what you mean by "2" or "3". Was it returning "apple, orange, pear" and now it returns "2"

Give us specific examples of what used to happen and now what happens and we are happy to try and help you. POST the correct code (change table/column names if you must), but post the code, not say you have some query like...