MMC DataBase Properties Error

Hello.
In SQL Server 2000, when I right click on a database and then select
Properties,
I receive the following error message: "Microsoft Management Console has
encountered a problem and needs to close".
I tried a couple of databases with the same results and also rebooted but
that didn't resolve the issue.
Any suggestions will be greatly appreciated :-)
RitaOn May 18, 5:19 pm, RitaG <R...@.discussions.microsoft.com> wrote:
> Hello.
> In SQL Server 2000, when I right click on a database and then select
> Properties,
> I receive the following error message: "Microsoft Management Console has
> encountered a problem and needs to close".
> I tried a couple of databases with the same results and also rebooted but
> that didn't resolve the issue.
> Any suggestions will be greatly appreciated :-)
> Rita
You may need to uninstall and reinstall client tools if the databases
are fine. This has happened to me and I was working with client tools
on my desktop viewing a registered server instance. I never found out
the issue, but reinstalling the client tools did the trick.
Hope this helps.
Kristina|||Thanks so much Kristina - I'm going to try that now!
"Kristina" wrote:
> On May 18, 5:19 pm, RitaG <R...@.discussions.microsoft.com> wrote:
> > Hello.
> >
> > In SQL Server 2000, when I right click on a database and then select
> > Properties,
> > I receive the following error message: "Microsoft Management Console has
> > encountered a problem and needs to close".
> >
> > I tried a couple of databases with the same results and also rebooted but
> > that didn't resolve the issue.
> >
> > Any suggestions will be greatly appreciated :-)
> >
> > Rita
> You may need to uninstall and reinstall client tools if the databases
> are fine. This has happened to me and I was working with client tools
> on my desktop viewing a registered server instance. I never found out
> the issue, but reinstalling the client tools did the trick.
> Hope this helps.
> Kristina
>

Mix Relative and strict dates

I am trying to write a report where I can have a drop down with values like

yesterday,today,lastweek.
But I also want the user to be able to select a specific range, say 7/1/2006 to 7/5/2006.

Also why can't i update the value of a parameter through to an expression? Like set the value to =Today(). It throws an error about the type.

Can someone point me in the right direction here?

Parameters are typed so you cannot mix relative dates with specific ones. So, you will need muliple parameters and way to determine which ones to use. I would use three parameters, the first one is a drop down with the realative dates and an entry like "Specify." The other two are DateTime parameters for specifying the start and end dates.

The "Specify" entry is to indicate that the user wants to specify specific dates. Otherwise, the user would choose the relative date and the specific dates would be ignored.

For the issue about an error being thrown when you are using the expression, =Today(): make sure the parameter's type is set to DateTime or use =Today().ToString() as the expression, if the parameter's type is string.

Ian|||

I normally use = today, no parentheses, in the parameters

99

|||

I cannot assign a parameter of type DateTime, through the web service. SetParameters() to '=Today()'

|||I think the issue is that you are passing the string '=Today()' where a DateTime is expected. Expressions are not evaluated here, so you need to specifiy the actual value, instead of an expression. E.g., DateTime.Today.

Or you can place the "=Today()" expression directly in the Report for the parameter, where it will be evaluated.|||once you deploy the report with an expression for the value of a parameter, can you change that, without re-deploying the report. Is there a call that can be made to change that expression in the report?|||Expressions are compiled at publish-time and cannot be changed without re-publishing the report. This re-publishing can be done programmatically, but I think there are better alternatives.

What are you looking to accomplish with changing the expression?

Ian|||I am trying to get the user be able to select either the relative dates, yesterday, lastweek... OR select a strict date range in a linked report. Also do you know if there is a control which only displays the parameters of the report, which can be used to display them.|||What would be the way to determine which one to use? Can this all be done while designing the report? do you have an example?

missing tables in sysobjects

Hi,
I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,
N'IsUserTable') = 1" to get all user-defined tables(about 17000
tables)
and found out there are tables missing in sysobjects.
How can I refresh/rebuild sysobjects table?
is there a better way to get a list of user-defined tables?

thanks,second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0401061038.11ea8cc7@.posting.google.com>...
> Hi,
> I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,
> N'IsUserTable') = 1" to get all user-defined tables(about 17000
> tables)
> and found out there are tables missing in sysobjects.
> How can I refresh/rebuild sysobjects table?
> is there a better way to get a list of user-defined tables?
> thanks,

Perhaps what is missing are VIEWS?

Try the following

select * from INFORMATION_SCHEMA.tables

Please let me know how you get on.|||neo (second714@.hotmail.com) writes:
> I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,
> N'IsUserTable') = 1" to get all user-defined tables(about 17000
> tables)
> and found out there are tables missing in sysobjects.
> How can I refresh/rebuild sysobjects table?
> is there a better way to get a list of user-defined tables?

So how to you know they are missing? I'd say that if a table is not in
sysobjects it does not exist...

You can use DBCC CHECKCATALOG to check the system tables for inconsistency.

Mystery Man's suggestion that you might be looking for views seems worth
investigating. I'll add table-valued functions to the list.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Missing Subscriber tab in EM Replication Configure wizard

Hi All,
On a server I configured publication of a database and push subcriptions
successfully using Enterprise Manager. But later when I select the
"Configure Publishing, Subsribers and Distribution" option again by either
right clicking on Replication folder or Menu, I find Subscribers tab is
missing, I see only Distributor and Publishers tabs.
Can anyone please tell me what I am missing anything here?
Thanks a lot,
VM
The server is not configured as a publisher. Enable it as a publisher and
click apply and ok. Reopen this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"VM" <VM@.discussions.microsoft.com> wrote in message
news:8C3C9EA9-B273-4CA6-99BF-CA51B6E25294@.microsoft.com...
> Hi All,
> On a server I configured publication of a database and push subcriptions
> successfully using Enterprise Manager. But later when I select the
> "Configure Publishing, Subsribers and Distribution" option again by
> either
> right clicking on Replication folder or Menu, I find Subscribers tab is
> missing, I see only Distributor and Publishers tabs.
> Can anyone please tell me what I am missing anything here?
> Thanks a lot,
> VM

Missing something with report filter / parameter

It must be obvious, but I'm just not seeing my mistake.
I'm running this query against an Oracle db as the driving dataset
Select Count(id), value1,value2,groupid
from mytable
where createDate between :startDate and :endDate
group by value1,value2,groupid
my second dataset comes from this query
select groupid,groupname
from mygroups
I've got start and end date set as report parameters and they work just
fine. What I've been trying to do is make a full pull of the primary
dataset and allow users to filter it at the report level.
When I add a group filter to the driving dataset...
CStr(Fields!groupid.Value) = =Parameters!groupid.Value,
the report viewer returns an Oracle error that not all parameters have
been passed. How the heck is that happening? Isn't the full dataset
supposed to be pulled to the report and at that point supposed to be
filterable? What am I missing ?
Should I be passing the groupid in the driving data set with the Union
"All" work around and then filter or ?
--
Garth H
webdev511@.spamcop.net
Microsoft Certified Technology Specialist
Microsoft Certified Professional
Macromedia Certified DeveloperI think I figured this one out.
It ended up working when i explicitly pulled the groupid's that I want
to filter on.
It's not how I thought it should work, but there it is.
Garth H
webdev511@.spamcop.net
Microsoft Certified Technology Specialist
Microsoft Certified Professional
Macromedia Certified Developer

Missing SELECT statement, but where?

Hi
I have created a question in Access and then copied the SQL question from it.
When I try to run it in eg SQL plus I get an error. I can't see what's wrong.
SQL> SELECT MC_POSUM.ID, MC_POLINE.ID, MC_INVTRANS.TQTY_AMT, MC_PCITEM.ITID, MC_INVTRANS.PRSD_DTTM,
MC_EMPLOYEE.AENM
2 FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_P
ODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTR
ANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_
PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC
_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI
3 WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 3
2050) AND ((MC_INVTRANS.TRNTYP) Like 5));
FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
hi christina,
First off this is a SQL Server newsgroup. So try putting your question in some of the oracle
newgroup/forum. However, with some oracle knowledge i've here are little annotations on my
part.
As far as i know ansi sql syntaxes are only available in oracle from oracle9i so if you are
using oralce8i this is not supported.
Also, enclosing identifiers in square brackets is syntactically correct in SQL Server and not
in Oracle. See following corrected query(should work in oracle9i).
SELECT MC_POSUM.POSUMOI
FROM
--one bracket missing below
(((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN
MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI
= MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI =
MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN
MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE
MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI) --remove as
WHERE (((MC_EMPLOYEE_1.AENM) Not Like MC_EMPLOYEE.AENM) AND ((MC_STOREROOM.STOROI) Not Like
32050) AND ((MC_INVTRANS.TRNTYP) Like 5)) --no square bracket required.
--End of query
MC_PODEL
Also once where clause is over it is syntactically incorrect to use FROM clause again.
Vishal Parkar
vgparkar@.yahoo.co.in

Missing records

I am trying to find missing Checks from Check Table (I know there are no
tables).
Here is a sample table (oops did it again) :):
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'TestTable')
DROP table TestTable
CREATE TABLE [dbo].[TestTable] (
[SeqNumber] [int] NULL ,
[AName] [varchar] (30) NULL
) ON [PRIMARY]
GO
Insert TestTable Values (1,'Joe')
Insert TestTable Values (2,'Mary')
Insert TestTable Values (3,'Frank')
Insert TestTable Values (5,'Larry')
Insert TestTable Values (6,'Jerry')
Insert TestTable Values (7,'Greg')
Insert TestTable Values (10,'Ron')
Insert TestTable Values (11,'Pat')
Insert TestTable Values (12,'Beth')
Insert TestTable Values (13,'Lynn')
Insert TestTable Values (20,'Sharon')
go
Is there an easy way to do a select and come back with the missing sequences
(in this case - 4,8,9,14,15,16,17,18,19).
Thanks,
TomUse a table consisting of all the possible check numbers:
SELECT N.num
FROM numbers AS N
LEFT JOIN testtable AS T
ON N.num = T.seqnumber
WHERE T.seqnumber IS NULL
AND N.num BETWEEN 1 AND 20 ;
Or, if you prefer to see ranges of numbers:
SELECT T1.seqnumber+1 AS missing_from,
MIN(T2.seqnumber)-1 AS missing_to
FROM testtable AS T1
JOIN testtable AS T2
ON T1.seqnumber < T2.seqnumber
GROUP BY T1.seqnumber
HAVING T1.seqnumber < MIN(T2.seqnumber)-1 ;
David Portas
SQL Server MVP
--

Missing Record - Phantom Record

Hi All,

Have come across something weird and am after some help.

Say i run this query where rec_id is a column of table arlhrl,

select * from arlhrl where rec_id >= 14260

This returns to me 2 records with rec_id's of 14260 and 14261

Then I run this query

select * from arlhrl where rec_id >= 14263

This returns 7 records with rec_ids of 14263 up.

How come the first query doesn't return the records returned by the
2nd query also?

If I select for 14262 no records are returned. It is like this is a
phantom record or has an end of file character in it.

I tried re-creating the indexes but to no avail. If anyone has any
ideas about what could be causing it or how to fix it it would be much
appreciated.

Thanks in advance,

AndrewAndrew wrote:
> Hi All,
> Have come across something weird and am after some help.
> Say i run this query where rec_id is a column of table arlhrl,
> select * from arlhrl where rec_id >= 14260
> This returns to me 2 records with rec_id's of 14260 and 14261
> Then I run this query
> select * from arlhrl where rec_id >= 14263
> This returns 7 records with rec_ids of 14263 up.
> How come the first query doesn't return the records returned by the
> 2nd query also?
> If I select for 14262 no records are returned. It is like this is a
> phantom record or has an end of file character in it.
> I tried re-creating the indexes but to no avail. If anyone has any
> ideas about what could be causing it or how to fix it it would be much
> appreciated.
> Thanks in advance,
> Andrew

Hi,

First, stupid question - is the field 'rec_id' of integer type?
Why i am asking is because i had a similar example myself when i started with my new job - i was
quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)

Second, what i'd do when i get into an unexplainable glitch:

SELECT * INTO <new table> FROM <your table
And try to query the records from the new table without setting any indexes - just as is - as you
know SELECT INTO just copies raw data without any underlying stuff.
See what you'll get.
From my experience there are a of of people who are allowed to mess with SQL databases but don't
have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
imagine what another person could do - believe me, i just got quite a few awsome examples within the
last month since i got this job :)

Let me know how it works!

Andrey|||Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> Andrew wrote:
> > Hi All,
> > Have come across something weird and am after some help.
> > Say i run this query where rec_id is a column of table arlhrl,
> > select * from arlhrl where rec_id >= 14260
> > This returns to me 2 records with rec_id's of 14260 and 14261
> > Then I run this query
> > select * from arlhrl where rec_id >= 14263
> > This returns 7 records with rec_ids of 14263 up.
> > How come the first query doesn't return the records returned by the
> > 2nd query also?
> > If I select for 14262 no records are returned. It is like this is a
> > phantom record or has an end of file character in it.
> > I tried re-creating the indexes but to no avail. If anyone has any
> > ideas about what could be causing it or how to fix it it would be much
> > appreciated.
> > Thanks in advance,
> > Andrew
>
> Hi,
> First, stupid question - is the field 'rec_id' of integer type?
> Why i am asking is because i had a similar example myself when i started with my new job - i was
> quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>
> Second, what i'd do when i get into an unexplainable glitch:
> SELECT * INTO <new table> FROM <your table>
> And try to query the records from the new table without setting any indexes - just as is - as you
> know SELECT INTO just copies raw data without any underlying stuff.
> See what you'll get.
> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> imagine what another person could do - believe me, i just got quite a few awsome examples within the
> last month since i got this job :)
> Let me know how it works!
> Andrey

Hi Andrey,

Thanks for your reply. I tried as you mentioned, inserting into new
table etc but to no avail. I did figure out what the problem was
though.

This particular table had been upsized from a foxpro table. One of the
columns in the foxpro table had a maximum value of numeric 9999.
Somehow, someone had tried to insert a value large than this so foxpro
put in ****. On the upsize, and I can only assume here, sql must have
thought 'hang on, you must mean infinity here' and put a bit-wise
pattern (1.#INF) for infinity into this particular column for the
record.

This only became evident when using Enterprise Manager and returning
all rows on the given table, it did display the record with the value
1.#INF in the column for the 'missing' record. As to why it displayed
in EM and not Query Analyser is anyone's guess, but surely the queries
that led me to this initial discovery shouldn't have behaved like
this!!??

posting

http://groups.google.com/groups?q=%...le .com&rnum=1

gives some ideas.

Thanks anyway,

Andrew|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

Well, EM and QA might show you diferent results because they are using diferent methods of 'talking'
to sql server.
QA is using isql.com, precisely it's isqlw.com version, which is an old DB lib based way of connection.
EM, i guess, is using ODBC or OLEDB connection.

I also had a headache not long time ago, when i used sql console tools to make Python work with sql
server. I had a table with varcha fields which had around couple thousand characters of text each.

When i used isql.com to retreive those text records, text returned truncated, around 300 to 600
characters left.. SO i started using osql.com instead, and no headache.

So resume is - every time you're in doubt, use both EM and QA

PS. BTW, I didn't know sql server can store 'infinity' values. Thanks for the info!

WYGL,
Andrey|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

And how did you get rid of that infinity value in the in field?|||I got rid of the infinity value using EM open table then typed in the
value I wanted.

Andrey <leyandrew@.yahoo.com> wrote in message news:<pir4d.28770$wV.19066@.attbi_s54>...
> Andrew wrote:
> > Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> >>Andrew wrote:
> >>
> >>>Hi All,
> >>>
> >>>Have come across something weird and am after some help.
> >>>
> >>>Say i run this query where rec_id is a column of table arlhrl,
> >>>
> >>>select * from arlhrl where rec_id >= 14260
> >>>
> >>>This returns to me 2 records with rec_id's of 14260 and 14261
> >>>
> >>>Then I run this query
> >>>
> >>>select * from arlhrl where rec_id >= 14263
> >>>
> >>>This returns 7 records with rec_ids of 14263 up.
> >>>
> >>>How come the first query doesn't return the records returned by the
> >>>2nd query also?
> >>>
> >>>If I select for 14262 no records are returned. It is like this is a
> >>>phantom record or has an end of file character in it.
> >>>
> >>>I tried re-creating the indexes but to no avail. If anyone has any
> >>>ideas about what could be causing it or how to fix it it would be much
> >>>appreciated.
> >>>
> >>>Thanks in advance,
> >>>
> >>>Andrew
> >>
> >>
> >>Hi,
> >>
> >>First, stupid question - is the field 'rec_id' of integer type?
> >>Why i am asking is because i had a similar example myself when i started with my new job - i was
> >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
> >>
> >>
> >>Second, what i'd do when i get into an unexplainable glitch:
> >>
> >>SELECT * INTO <new table> FROM <your table>
> >>
> >>And try to query the records from the new table without setting any indexes - just as is - as you
> >>know SELECT INTO just copies raw data without any underlying stuff.
> >>See what you'll get.
> >> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> >>imagine what another person could do - believe me, i just got quite a few awsome examples within the
> >>last month since i got this job :)
> >>
> >>Let me know how it works!
> >>
> >>Andrey
> > Hi Andrey,
> > Thanks for your reply. I tried as you mentioned, inserting into new
> > table etc but to no avail. I did figure out what the problem was
> > though.
> > This particular table had been upsized from a foxpro table. One of the
> > columns in the foxpro table had a maximum value of numeric 9999.
> > Somehow, someone had tried to insert a value large than this so foxpro
> > put in ****. On the upsize, and I can only assume here, sql must have
> > thought 'hang on, you must mean infinity here' and put a bit-wise
> > pattern (1.#INF) for infinity into this particular column for the
> > record.
> > This only became evident when using Enterprise Manager and returning
> > all rows on the given table, it did display the record with the value
> > 1.#INF in the column for the 'missing' record. As to why it displayed
> > in EM and not Query Analyser is anyone's guess, but surely the queries
> > that led me to this initial discovery shouldn't have behaved like
> > this!!??
> > posting
> > http://groups.google.com/groups?q=%...le .com&rnum=1
> > gives some ideas.
> > Thanks anyway,
> > Andrew
>
> And how did you get rid of that infinity value in the in field?

missing or invalid option

here is my code:
SET SERVEROUTPUT ON
DECLARE
numbrows NUMBER (2) := '&numbrows';
name s_dept.name%TYPE;
CURSOR c_emp IS
SELECT last_name,dept_id
FROM s_emp;
TYPE last_dept_table_type IS TABLE OF
c_emp%ROWTYPE
INDEX BY BINARY_INTEGER;
last_dept_table last_dept_table_type;
CURSOR c_dept IS
SELECT name,id
FROM s_dept;
TYPE dept_table_type IS TABLE OF
c_dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
BEGIN
OPEN c_dept;
OPEN c_emp;
FOR i IN 1..numbrows LOOP
FETCH c_emp INTO last_dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
FETCH c_dept INTO dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
IF last_dept_table(i).dept_id = dept_table(i).id THEN
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_table(i).name);
END IF;
END LOOP;
CLOSE c_emp;
CLOSE c_dept;
END;
/

and this is my problem:

Enter value for numbrows: 5
old 3: numbrows NUMBER (2) := '&numbrows
new 3: numbrows NUMBER (2) := '5';
SET SERVEROUTPUT ON
*
ERROR at line 1:
ORA-00922: missing or invalid option

Can anybody help?1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.

2) If you are using SQL Plus, what version of SQL Plus, e.g.:

SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

When I run your code I get:

Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';

Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?

Originally posted by andrewst
1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.

2) If you are using SQL Plus, what version of SQL Plus, e.g.:

SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

When I run your code I get:

Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';

Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||Originally posted by bbk
I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0

NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:

SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Sorry about that :
Release 9.2.0.1.0 - Production on Fri Feb 21 12:53:24 2003

(c) 1982, 2002, Oracle Corporation. All rights reserved.

I tried leaving a blank line and now i get the following msg:
Enter value for numbrows: 5
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '5';
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_
*
ERROR at line 27:
ORA-06550: line 27, column 7:
PLS-00201: identifier 'DBMS_OUPUT.PUT_LINE' must be declared
ORA-06550: line 27, column 7:
PL/SQL: Statement ignored

Originally posted by andrewst
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0

NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:

SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Yes, well DBMS_OUPUT is a typo, isn't it!

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details into
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
> > When I was going through Kalens Query Tuning and Optimization book , she
> > provided the query below to find missing indices. Results are below.
> >
> > select object_name(t1.object_id)
> > TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> > from
> > sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> > sys.dm_db_missing_index_groups t3
> > where database_id=db_id()
> > and t1.index_handle=t3.index_handle
> > and t2.group_handle=t3.index_group_handle
> > and object_name(object_id) = 'tableA'
> > order by 1 , 2 desc
> >
> > Output :
> >
> > TblName seeks Equality_cols Inequality_cols Included_cols
> >
> > TableA 3609843 [Col1] NULL [Col2]
> > TableA 3434018 [Col2], [Col1] NULL NULL
> > TableA 703743 [Col1] [Col3] [Col2]
> > TableA 495032 [Col2], [Col1] [Col3] NULL
> >
> > So how do I create these indices now ?
> >
> > For the first entry, is it stating to create an index on col1 with Col2 as
> > included col ?
> > second entry, I guess it wants a covered index on Col2,Col1
> > For the 3rd and 4th entry I dont know what it wants us to create.
> >
> > The 3rd entry has a column listed for each of the 3 column types namely
> > equality,inequality and included.
> >
> > Thanks
> >
> > Please help me figure this out.
> >
> >|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.
Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>
|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details into
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =
mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =
sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_
columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.include
d_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details in
to
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
>|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =
mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =
sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.include
d_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.