Query Syntax: status

Showing posts with label status. Show all posts

Friday, March 23, 2012

query tunning

I have a dropdown box to running a query filtering mt records.
The dropdown has 4 option: ALL, Close, Open, Pause.
which filter the status of records.
When I change dropdown from all to others works fine, but when I change from
the Close, Open or Pause to All then it takes more than 10 seconds. It seems
that the SQL server needs more time to return the records from others to ALL
,
but it taks less time from ALL to others.
Any suggestions is great appreciated,As far as we dont know what code happens when you chenge the Combobox its
hard to help you in here. Perhaps you might run the profiler to watch the
incoming queries...
HTH, Jens Suessmeyer.
http.//www.sqlserver2005.de
--
"Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks for the message,
The dropdown box just run the query.
I will user profilter to watch it.
Thanks,
"Jens Sü?meyer" wrote:

> As far as we don′t know what code happens when you chenge the Combobox it
s
> hard to help you in here. Perhaps you might run the profiler to watch the
> incoming queries...
> HTH, Jens Suessmeyer.
> --
> http.//www.sqlserver2005.de
> --
>
> "Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||Chances are it is using an index and returning only a small portion of the
rows when it is other than ALL. When it is set to ALL it has to scan the
whole table and return a lot of rows.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks!
You are right,
Are there any suggestions to resolve the issue?
Should I analyze the index fields?
Is it possible to change index fields will resolve the issue?
Thanks millions,
"Andrew J. Kelly" wrote:

> Chances are it is using an index and returning only a small portion of the
> rows when it is other than ALL. When it is set to ALL it has to scan the
> whole table and return a lot of rows.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||If there are no other columns which can be used in the WHERE clause then you
have no choice but to scan the table. Indexes won't help if there is no
WHERE clause. I don't know your business logic but you would probably be
best served by always requiring some field to be used as the search
criteria. Rarely is ALL a good choice. If there are thousands of rows or
more then the user usually can not sift through all of them effectively
anyway.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
> Thanks!
> You are right,
> Are there any suggestions to resolve the issue?
> Should I analyze the index fields?
> Is it possible to change index fields will resolve the issue?
> Thanks millions,
>
> "Andrew J. Kelly" wrote:
>|||Thanks millions,
Souris,
"Andrew J. Kelly" wrote:

> If there are no other columns which can be used in the WHERE clause then y
ou
> have no choice but to scan the table. Indexes won't help if there is no
> WHERE clause. I don't know your business logic but you would probably be
> best served by always requiring some field to be used as the search
> criteria. Rarely is ALL a good choice. If there are thousands of rows or
> more then the user usually can not sift through all of them effectively
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
>
>sql

Friday, March 9, 2012

Query to find invalid objects?

Is there a system table that holds data on invalid objects/procedures? In
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to try
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
could mean different things. The short story is "no", there is no such table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are called
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> could mean different things. The short story is "no", there is no such table...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > Is there a system table that holds data on invalid objects/procedures? In
> > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > the invalid objects. Is there anything similar in SQL Server?
> >
> > thanks!
> > Susan
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
> > Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> > could mean different things. The short story is "no", there is no such table...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> > news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > > Is there a system table that holds data on invalid objects/procedures? In
> > > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > > the invalid objects. Is there anything similar in SQL Server?
> > >
> > > thanks!
> > > Susan
> >
> >|||As already posted, the sp_depends and sysdependencies tables (and the 2005 catalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL, deferred name
resolution etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so
>> it
>> could mean different things. The short story is "no", there is no such table...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
>> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>> > Is there a system table that holds data on invalid objects/procedures? In
>> > Oracle, I can query dba_objects table where status='INVALID' to find all of
>> > the invalid objects. Is there anything similar in SQL Server?
>> >
>> > thanks!
>> > Susan
>>

query to detemine trigger status

I know how to retrieve the list of triggers in teh database.
Select * from sysobjects where xtype = 'TR'
What I want to know is how do i tell whether the trigger is disabled or
enabled. I would appreciate if anybody can help me this.
thanks
MB...and status&2048=2048
will tell you if it's disabled
Mohan wrote:

>I know how to retrieve the list of triggers in teh database.
>Select * from sysobjects where xtype = 'TR'
>What I want to know is how do i tell whether the trigger is disabled or
>enabled. I would appreciate if anybody can help me this.
>thanks
>MB
>
>|||Lookup the argument ExecIsTriggerDisabled for the meta-data function
OBJECTPROPERTY in SQL Server Books Online.
Anith

Saturday, February 25, 2012

Query the latest info

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004

sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
ShaffiqYou want to list only the cars that have been sold?

Select * from table where status = 'sold'|||You want to list only the cars that have been sold?

Select * from table where status = 'sold'

hi,
its easy, but sometime cars that has been sold were return back , so this not considered sold. I want to display cars that solid has been sold...sometimes got status sold, return and sold back... but sometimes only sold..for this case only Pl3 and PL4.. any idea? thanks|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')

Thx,
I' d tried before but it will not display cars that have tree status( sold, return, sold) for this case you can see at PL4|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'

Hi r937,

Thx for the solution it works well thank also to pshisbey

Friday, March 23, 2012

query tunning

Friday, March 9, 2012

Query to find invalid objects?

Query to find invalid objects?

query to detemine trigger status

Saturday, February 25, 2012

Query the latest info

Query Syntax

Blog Archive

About Me