Modified Stored Procedure Scripts Wrong ANSI Settings

Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER OFF

go

However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

Which is not even the default for my connection.

I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.

Any suggestions? Is this a bug? I'm using SQL2005 RTM.

Matthew Martin

I would do this from your script in the query pane:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

GO

Should be something like this at the end.

HTH, Jens Suessmeyer.

|||

True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.

Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.

Matthew Martin

|||

This is a known issue in SSMS. It will be fixed in SP1.

Model Query Designer

I added the RS extension to RSReportServer.config. Then, I restarted
the report server. Now it works!Could you please explain how and where exactly in the config file you added
it?
Thanks,
Nancy
<fmatamoros@.yahoo.com> wrote in message
news:1179967924.559739.15150@.d30g2000prg.googlegroups.com...
>I added the RS extension to RSReportServer.config. Then, I restarted
> the report server. Now it works!
>sql

Model DB in Warm-Standby

Can anyone help me with this? I tried to use the query
analyzer to bring the Model DB back on-line:
restore database model
with recovery
But this is what I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.I am assuming that no one else has an open connection to
the model database. You will get this error if you have
the model database "open" in Enterprise Manager and
simultaneously try to execute the RESTORE command from
query analyzer. Hope this helps.
Tim
>--Original Message--
>Can anyone help me with this? I tried to use the query
>analyzer to bring the Model DB back on-line:
>restore database model
>with recovery
>But this is what I get:
>Server: Msg 3101, Level 16, State 1, Line 1
>Exclusive access could not be obtained because the
>database is in use.
>Server: Msg 3013, Level 16, State 1, Line 1
>RESTORE DATABASE is terminating abnormally.
>.
>sql

MOD Equivalent in SQl Server?

I'm trying to build a query that I'd like only run on records with an odd
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:

> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.

Mixed Mode vs. Windows Authentication

I am trying to create a query that can determine if a user id is using mixed mode/windows/both authentication. I need to do this so that it can run on both sql server 2000 and 2005, meaning I can't use any of the sys.* views. Is there a single query could use for both systems?
-Kyle

Hi kschlap,

there is no method for your issue.

but maybe you can try to build a view to select cross these 2 servers.

create view v_loginid_info

as

select 'servname'='mssql2k', loginid

from mssql2k.master.dbo.sysprocesses

union all

select 'servname'='mssql2k05', loginid

from mssql2k05.master.sys.sysprocess

try to think about.

hoping this can help you.

Best Regrads,

Hunt.

|||I have found the query...

select name, is_policy_checked
from sys.sql_logins

When I use this query, it doesn't pick up the windows authenticated users. Is there a way to get it to pick up all users?
-Kyle|||

How about something like this:

select name, isntuser from syslogins

isntuser=1 means Windows authentication

isntuser=0 means SQL Server authentication

Ben

sql

Missing Values

Hi,
Can anyone help me with a query?
Let say I have a table called tblTest with one int field called NumOfCall.
NumOfCall hold numbers in sequential order but some are missing. for
example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
I need a query that will tell me what values are missing.
Thanks
FredFrinton wrote:
> Hi,
> Can anyone help me with a query?
> Let say I have a table called tblTest with one int field called NumOfCall.
> NumOfCall hold numbers in sequential order but some are missing. for
> example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
> I need a query that will tell me what values are missing.
> Thanks
> Fred
Use a table (Numbers) that contains all the potential numbers you want
to look for:
SELECT num
FROM Numbers AS N
WHERE NOT EXISTS
(SELECT *
FROM tblTest
WHERE numofcall = N.num)
AND N.num BETWEEN 1 AND 100 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Another option is to use:
SELECT col + 1
FROM tbl
WHERE col < ( SELECT MAX( col ) FROM tbl )
AND NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
If you have series of missing numbers do:
SELECT start + 1, end - 1
FROM ( SELECT t1.col, MIN( t2.col )
FROM tbl t1
INNER JOIN tbl t2
WHERE t1.col < t2.col
GROUP BY t1.col ) D ( start, end )
WHERE start < end - 1 ;
--
Anith

missing value

hi,

I am using time series algorithm.and my prediction query is like this

SELECT PredictTimeSeries([Performance]) FROM [Stud_Model]

The output is like this

Date Perf

9/11/2006 90

10/11/2006 92

11/11/2006 93

12/11/2006 -- (no prediction)

1/11/2007 --(no prediction)

I dnt know why there is no prediction after certail date?

Thanks,

Karthik

The time series algorithm is based on regression trees which can become unstable as you move away from the time horizon, since you have predictions based on predictions. The algorithm tries to detect when this instability occurs and stops predicting at that point.

In future releases, we will have more user control over prediction stability and the behavior of the algorithm around this stability.

Missing tools in SSMS 2005

I am using SSMS since a few weeks, and I found it is missing a few
tools/utilities from the old Query Analyzer. One of the things which hit
me is the lack of the Debug (I know I can do it in VS 2005, but I can't
understand why I can't debug in SMSS), another is the lack of support
for Sql Server 2000 diagrams, and then there are annoying things as
forcing us to use a different shortcut to bookmark and so on.
The worst of all, anyway, is that we still can't apply a customized
format to the sql documents. In the development team I am working in
we're using a formatting code convention quite different from that
recommended by the Books Online, so every time I use the Query Designer,
I have then manually to 'beautify' the resulting T-SQL code. Even in VS
2005 there seems not to be a way to format all of a SQL document, as it
is possible to do with c# i.e..
Get me right, so far my experience with SMSS has been positive, only
these and other little defaillances are marking the difference between
an useful tool and a tool to recommend.
ale
http://www.riolo.org
Hi
Check out the SQL Server Magazine article on debugging:
http://www.windowsitpro.com/Article/...754/47754.html
For diagrams look in Books online on how to set them up and add one
http://msdn2.microsoft.com/en-us/library/ms189279.aspx
John
"Alessandro Riolo" <alessandro.riolo@.sen.it> wrote in message
news:etpS%23FiEGHA.644@.TK2MSFTNGP09.phx.gbl...
>I am using SSMS since a few weeks, and I found it is missing a few
>tools/utilities from the old Query Analyzer. One of the things which hit me
>is the lack of the Debug (I know I can do it in VS 2005, but I can't
>understand why I can't debug in SMSS), another is the lack of support for
>Sql Server 2000 diagrams, and then there are annoying things as forcing us
>to use a different shortcut to bookmark and so on.
> The worst of all, anyway, is that we still can't apply a customized format
> to the sql documents. In the development team I am working in we're using
> a formatting code convention quite different from that recommended by the
> Books Online, so every time I use the Query Designer, I have then manually
> to 'beautify' the resulting T-SQL code. Even in VS 2005 there seems not to
> be a way to format all of a SQL document, as it is possible to do with c#
> i.e..
> Get me right, so far my experience with SMSS has been positive, only these
> and other little defaillances are marking the difference between an useful
> tool and a tool to recommend.
> --
> ale
> http://www.riolo.org
|||Go to the Product Feedback Center and put in a request to add the features
that are missing
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Alessandro Riolo" <alessandro.riolo@.sen.it> wrote in message
news:etpS%23FiEGHA.644@.TK2MSFTNGP09.phx.gbl...
>I am using SSMS since a few weeks, and I found it is missing a few
>tools/utilities from the old Query Analyzer. One of the things which hit me
>is the lack of the Debug (I know I can do it in VS 2005, but I can't
>understand why I can't debug in SMSS), another is the lack of support for
>Sql Server 2000 diagrams, and then there are annoying things as forcing us
>to use a different shortcut to bookmark and so on.
> The worst of all, anyway, is that we still can't apply a customized format
> to the sql documents. In the development team I am working in we're using
> a formatting code convention quite different from that recommended by the
> Books Online, so every time I use the Query Designer, I have then manually
> to 'beautify' the resulting T-SQL code. Even in VS 2005 there seems not to
> be a way to format all of a SQL document, as it is possible to do with c#
> i.e..
> Get me right, so far my experience with SMSS has been positive, only these
> and other little defaillances are marking the difference between an useful
> tool and a tool to recommend.
> --
> ale
> http://www.riolo.org
|||Michael Hotek wrote:
> Go to the Product Feedback Center and put in a request to add the features
> that are missing
I already did it for one of the most annoying of the lack, a
customizable sql document formatter either in SSMS either in VS2005:
http://lab.msdn.microsoft.com/produc...e-43fe9c69ad8f
ale
http://www.riolo.org
|||Alessandro Riolo (alessandro.riolo@.sen.it) writes:
> The worst of all, anyway, is that we still can't apply a customized
> format to the sql documents. In the development team I am working in
> we're using a formatting code convention quite different from that
> recommended by the Books Online, so every time I use the Query Designer,
I can't say that the most serious problem with the Query Designer is
that you cannot customize how it formats the code. A much more serious
problem is that it may rewrite queries to have a different meaning.
That tool is dangerous!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||John Bell wrote:
> For diagrams look in Books online on how to set them up and add one
> http://msdn2.microsoft.com/en-us/library/ms189279.aspx
Quoting the BOL:
"SQL Server 2005 database diagrams and SQL Server 2000 database diagrams
are created and rendered differently. Because of these differences, SQL
Server Management Studio cannot work with SQL Server 2000 diagrams. Use
SQL Server 2000 Enterprise Manager"
ale
http://www.riolo.org

missing SP when I query for it's text

I have code below to extract a table name and a verb. I receive 4 rows
back. I think fine. Unfortunatly my programmer says that the SP in
question is not part of the return set.
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%PreNote_Transaction_Lookup%'
and Routine_definition like '%insert%'
I change the query to fit the same code he is looking at:
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
and I get 0 rows back.
This is from the SP itself:
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where
Prenote_Transaction_ID = @.Prenote_Trans_ID
I have even put insert into in all caps. Still no rows returned?
Any ideas?Below work fine on my machine:
USE tempdb
GO
CREATE PROC A
AS
DECLARE @.Prenote_Trans_ID int
DECLARE @.transaction_id int
DECLARE @.Count int
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID =@.Prenote_Trans_ID
END
GO
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
Is your database case sensitive? Do you have > 4000 charactes in the procedure?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"_Stephen" <srussell@.electracash.com> wrote in message
news:O%23Ee6sHRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I have code below to extract a table name and a verb. I receive 4 rows back. I think fine.
>Unfortunatly my programmer says that the SP in question is not part of the return set.
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%PreNote_Transaction_Lookup%'
> and Routine_definition like '%insert%'
> I change the query to fit the same code he is looking at:
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%insert into PreNote_Transaction_Lookup%'
> and I get 0 rows back.
> This is from the SP itself:
> IF @.Prenote_Trans_ID > 0
> BEGIN
> INSERT INTO PreNote_Transaction_Lookup Values (@.Prenote_Trans_ID,null,cast(@.transaction_id as
> varchar(10)))
> Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID => @.Prenote_Trans_ID
>
> I have even put insert into in all caps. Still no rows returned?
> Any ideas?
>

missing SP when I query for it's text

I have code below to extract a table name and a verb. I receive 4 rows
back. I think fine. Unfortunatly my programmer says that the SP in
question is not part of the return set.
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%PreNote_Transaction_Lookup%'
and Routine_definition like '%insert%'
I change the query to fit the same code he is looking at:
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
and I get 0 rows back.
This is from the SP itself:
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where
Prenote_Transaction_ID = @.Prenote_Trans_ID
I have even put insert into in all caps. Still no rows returned?
Any ideas?
Below work fine on my machine:
USE tempdb
GO
CREATE PROC A
AS
DECLARE @.Prenote_Trans_ID int
DECLARE @.transaction_id int
DECLARE @.Count int
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID =
@.Prenote_Trans_ID
END
GO
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
Is your database case sensitive? Do you have > 4000 charactes in the procedure?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"_Stephen" <srussell@.electracash.com> wrote in message
news:O%23Ee6sHRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I have code below to extract a table name and a verb. I receive 4 rows back. I think fine.
>Unfortunatly my programmer says that the SP in question is not part of the return set.
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%PreNote_Transaction_Lookup%'
> and Routine_definition like '%insert%'
> I change the query to fit the same code he is looking at:
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%insert into PreNote_Transaction_Lookup%'
> and I get 0 rows back.
> This is from the SP itself:
> IF @.Prenote_Trans_ID > 0
> BEGIN
> INSERT INTO PreNote_Transaction_Lookup Values (@.Prenote_Trans_ID,null,cast(@.transaction_id as
> varchar(10)))
> Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID =
> @.Prenote_Trans_ID
>
> I have even put insert into in all caps. Still no rows returned?
> Any ideas?
>

missing SP when I query for it's text

I have code below to extract a table name and a verb. I receive 4 rows
back. I think fine. Unfortunatly my programmer says that the SP in
question is not part of the return set.
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%PreNote_Transaction_Lookup%'
and Routine_definition like '%insert%'
I change the query to fit the same code he is looking at:
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
and I get 0 rows back.
This is from the SP itself:
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transactio
n_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where
Prenote_Transaction_ID = @.Prenote_Trans_ID
I have even put insert into in all caps. Still no rows returned?
Any ideas?Below work fine on my machine:
USE tempdb
GO
CREATE PROC A
AS
DECLARE @.Prenote_Trans_ID int
DECLARE @.transaction_id int
DECLARE @.Count int
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transactio
n_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Trans
action_ID =
@.Prenote_Trans_ID
END
GO
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
Is your database case sensitive? Do you have > 4000 charactes in the procedu
re?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"_Stephen" <srussell@.electracash.com> wrote in message
news:O%23Ee6sHRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I have code below to extract a table name and a verb. I receive 4 rows bac
k. I think fine.
>Unfortunatly my programmer says that the SP in question is not part of the
return set.
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%PreNote_Transaction_Lookup%'
> and Routine_definition like '%insert%'
> I change the query to fit the same code he is looking at:
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%insert into PreNote_Transaction_Lookup%'
> and I get 0 rows back.
> This is from the SP itself:
> IF @.Prenote_Trans_ID > 0
> BEGIN
> INSERT INTO PreNote_Transaction_Lookup Values (@.Prenote_Trans_ID,null,cast
(@.transaction_id as
> varchar(10)))
> Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Tra
nsaction_ID =
> @.Prenote_Trans_ID
>
> I have even put insert into in all caps. Still no rows returned?
> Any ideas?
>

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 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 Operators Error

I am getting the following error when trying to post data into SQL:
Description: Syntax error (missing operator) in query
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
I am using a custom query in FrontPage:
INSERT INTO Results (Name, Email, Comments, File) VALUES
('::Name::', '::Email::', '::Comments::', '::File::')
It looks ok to me but not sure why I am getting the above error. Any
ideas?The error message doesn't come from SQL Server. If you are using SQL Server as a back-end, you might
want to use Profiler to see what SQL is submitted to the database engine. In any event, you should
check this out in a group focused on either Jet or FrontPage (since these are the applications using
SQL Server in this case).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<bjorgenson@.charter.net> wrote in message
news:1142880890.534952.11320@.g10g2000cwb.googlegroups.com...
>I am getting the following error when trying to post data into SQL:
> Description: Syntax error (missing operator) in query
> Number: -2147217900 (0x80040E14)
> Source: Microsoft JET Database Engine
> I am using a custom query in FrontPage:
> INSERT INTO Results (Name, Email, Comments, File) VALUES
> ('::Name::', '::Email::', '::Comments::', '::File::')
> It looks ok to me but not sure why I am getting the above error. Any
> ideas?
>

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.

Missing first row

Why is is in SSMSE or through code in VB.NET when running a query on a linked server that is an Excel spreadsheet is the first row not returned?

My spreadsheet has 320 rows with no column headings.

My select query within SSMSE returns only 319, the first row is ommitted, I beleive treated as a column heading.

If I insert a row at the very beginnning and enter any jibberish this row is ommitted and I get all my data.

Now I dont really want to have to tell my users that to get the import function of my app to work correctly they have to do this?

Is there a way to configure it to not treat first row as headings?

Thanks

http://support.microsoft.com/kb/257819/en-gb

Missing fields in MSmerge_history table under SQL Server 2005

Hi,

I have a query that uses the following fields from MSmerge_history:

MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration

Below is the query that I am using:

SELECT

MSmerge_agents.subscriber_name AS SubscriberName,

MSmerge_history.start_time AS SyncTime,

MSmerge_history.runstatus AS SyncStatusID,

MSmerge_history.comments AS Comments,

MSmerge_history.duration AS Duration

FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history

ON MSmerge_agents.id = MSmerge_history.agent_id

WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()

AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN

(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)

My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?

thanks

Romeo

You should start using table distribution.dbo.MSmerge_sessions, which has a much better summary of all sync sessions.

Missing fields in MSmerge_history table under SQL Server 2005

Hi,

I have a query that uses the following fields from MSmerge_history:

MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration

Below is the query that I am using:

SELECT

MSmerge_agents.subscriber_name AS SubscriberName,

MSmerge_history.start_time AS SyncTime,

MSmerge_history.runstatus AS SyncStatusID,

MSmerge_history.comments AS Comments,

MSmerge_history.duration AS Duration

FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history

ON MSmerge_agents.id = MSmerge_history.agent_id

WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()

AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN

(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)

My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?

thanks

Romeo

You should start using table distribution.dbo.MSmerge_sessions, which has a much better summary of all sync sessions.