Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQ
You need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>sql

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Normalisation - Query

Hi all,
I need to design apet database whee customers get regeistered and then pst their pet deals. They are either for sale or just rehoming.

Can someone please take a few moments to have a look at what I have done so far and tell me if it is any good.

This is my 6th time re-designing the tables.

Many thanks.

ps: owners can be either Private or part of an Organisation.

Customer table:
Customer#, C_Fname, C_Lname, C.address1, C.address2, C.postCode, C.fax
C.country, UKlocation, C.Hphone, C.Mphone, C.e-mail, (rehoming-services?
yes/no)

Organisation details:
customer#, Org#, Org.website, Org.AreaCoverd,
Org.activities, Org.addinfo, Org.Logo

Members of Org details:
Member#, Org#, Member_name, Member_email, member_Phone, member_ext.

Host table
customer#, host#, animals accepted?, max_no_days, price_per_day

Pets table:
pet#, picture, p_type, p_breed

Permenant pets for rehoming
pet#, pet_perm#, name, p.color, p.d_o_b, age,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, microchipped.

pets for sale:
Pet#, sale#, no.ofMales, PriceperMale, No.ofFemales, PriceperFemale,what is the difference between a customer and a member? can a customer be more than one member? can a member be more than one customer?

what is the difference between a customer and a host? can a customer be more than one host? can a host be more than one customer?

and i don't understand how you can have "number of males" attached to a given pet

do you have an entity-relationship diagram for this structure? that would really help you straighten out which foreign keys go where

see Data Modeling (http://www.utexas.edu/cc/database/datamodeling/) for an excellent tutorial

rudy
http://rudy.ca/

NOOPS in SQL query

Hello DBA's:
I need to exclude some words while doing a string comparison to get a good result. I do not need words like The, Of, And, In while comparing two strings. In short the record
The ABC Company OF XYZ
should match with BOTH
1. ABC
2. Company XYZCould you give us a bit more info of what you wanted to do? Or post your query if you can?|||You could use function replace() for deleting all 'noise' words. List of 'noise' words you can find \Mssql\Ftdata\Sqlserver\Config if Full-Text Search is installed.|||You can use the full text search feature of MS Sql server to do this kind of search operations.|||Have you looked into using the 'CONTAINS' clause?

You can do something like:

SELECT CompanyName
FROM Companies
WHERE CONTAINS(CompanyName, ' "ABC" OR "company" NEAR "XYZ" ')
GO

(You have to have FullText Indexing turned on for this to work)

Noob: Why does this Query work?

I'm wondering how/why this query works. Trying to get my head wrapped
around SQL. Basically the Query deletes from the Import table all
records that are already in FooStrings so that when I do an insert from
the FooStringsImport table into the FooStrings table, then I won't get
primary key violations.

DELETE FROM FooStringsImport
WHERE EXISTS
(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

It seems to work fine, but I'm wondering about how the EXISTS keyword
works.

(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
This part is going to return only records from FooStrings correct? Or
does it do a cartesian product since I've specified more than one table
in the WHERE statement?

I wonder if it only returns records in FooStrings, then I don't see how
a record from FooStringsImport would "EXISTS" in the records returned
from FooStrings.

The reason I wondered about the cartesian product is because, if only
FooStrings is specified in the FROM part of the SELECT statement, then
I was thinking it is only going to return FooString records. These
records would then be returned by the select statement to the WHERE
EXISTS, which would look for FooStringImport records, but would find
none because the select statement only returned FooString records.

I'm guessing maybe because it has to do a cartesian product to evaluate
the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
ALL the fields, and not just those in FooStrings.

FooStrings and FooStringsImport are identically structured tables,
where the FooKey is set as the primary key in each table:

CREATE TABLE [dbo].[FooStrings] (
[FooKey] [bigint] NOT NULL ,
[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks in advance. I'm so appreciative of the help I've gotten here,
as I've been able to write several very useful queries on my own now
after everyones help and plus lots of reading on my own.On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

> I'm guessing maybe because it has to do a cartesian product to evaluate
> the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
> ALL the fields, and not just those in FooStrings.

Close. The "WHERE EXISTS" is processed such that *no* columns will be
returned by the SELECT * inside it. Instead, it just notes whether any row
was returned and assigns true or false. You could get the same behavior by
replacing SELECT * with SELECT 1, or
SQRT(LEN(FooStringsImport.FooKey))*3.1415926535, or even SELECT NULL.
All of them will do exactly the same thing: completely ignore what comes
after the word SELECT.

Conceptually, the query processor is doing a separate "SELECT *" statement
for each and every row in FooStrings. If that separate statement returns a
row, it deletes the corresponding row from FooStrings; if it doesn't, then
it doesn't.

In actuality, the query optimizer doesn't do that. It rewrites the query as
if it were a JOIN (aka cartesian product, as you stated) and uses that to
decide which rows to delete from FooStringsImport.|||It's called a Correlated Subquery. Theoretically you can assume that the
subquery is executed once for every row in the main (outer) part of the
statement. The correlation part is the reference to the outer table
(FooStringsImport.FooKey). In other words EXISTS returns true for any
particular row in FooStringsImport if there is at least one row in
FooStrings that matches FooStringsImport.FooKey.

This is a Selection operation, not a Join and a DELETE statement rather than
a query, so I don't see that it helps much to think in terms of a Cartesian
Product. In fact SQL Server may use join operations to execute statements
involving subqueries but the server is not going to join every row to every
row before deciding which rows to delete.

See also Joe Celko's narrative on how queries work:
http://www.google.co.uk/groups?selm...ooglegroups.com

By the way, NOT EXISTS is also possible. So rather than do a DELETE followed
by an INSERT why not just filter the INSERT statement in a similar way:

INSERT INTO FooStrings (...)
SELECT ...
FROM FooStringsImport
WHERE NOT EXISTS
(SELECT *
FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

--
David Portas
SQL Server MVP
--|||On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

>I'm wondering how/why this query works. Trying to get my head wrapped
>around SQL. Basically the Query deletes from the Import table all
>records that are already in FooStrings so that when I do an insert from
>the FooStringsImport table into the FooStrings table, then I won't get
>primary key violations.
>DELETE FROM FooStringsImport
>WHERE EXISTS
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>It seems to work fine, but I'm wondering about how the EXISTS keyword
>works.
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>This part is going to return only records from FooStrings correct? Or
>does it do a cartesian product since I've specified more than one table
>in the WHERE statement?
(snip)

Hi Shumaker,

This is called a correlated subquery. Correlated, because the
"FooStringsImport.FooKey" correlates it to the part of the query outside
the subquery.

The way that this will be executed (in theory - the optimizer usually has
some nifty trcks to get the same result faster, but the slow version is
easier to understand):

- For the DELETE statement, start with the first row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that first row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the first row of FooStringImport; if it's false,
retain it.
- Move to the second row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that second row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the second row of FooStringImport; if it's false,
retain it.
- Move to the third row of FooStringsImport;
(etc etc etc -- until all rows of FooStringImport have been processed).

(Note - the terms "first", "second", etc are used very loosely here, as a
table has no intrinsic order)

The main difference between correlated and non-correlated subqueries, is
that a non-correlated subquery only has to be evaluated once, and the
result set can be retained and reused during the complete query
evaluation, whereas ta correlated subquery has to be recomputed for each
row in the "surrounding" layer of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks everyone! This is great info.sql

Noob Query Problem -This will be easy for you

This is driving me crazy. I am using a simple ASP table editor to make changes to an Access table. The following command is causing me to get an error:

UPDATE rates Set IRItype='A2', IRIProductName='Stylish', SQfeet='803', Bathrooms=1, IRIunitprice='600',

This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The SQL command is automaticly generated by the ASP script. I do not know if that extra comma is the cause of the error.is that all there was to the UPDATE statement? if so, it produces an error because it ends with a dangling comma

typically, you would have a WHERE clause, unless your intention is to update all rows to those values

so yeah, look into the ASP script|||The extra comma is certainly a problem. Also, make sure SQfeet and IRIunitprice are text values in the table. If they are numeric, then you wouldn't want the single quote surrounding the values.

noob alert! What i thought was a real simple query...

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber

Thanks for any suggestions you might have.
Jthis perhaps?
SELECT itemNumberSet.itemNumber
FROM itemNumberSet
LEFT OUTER
JOIN itemUPCtable
ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
ORDER
BY itemNumberSet.itemNumber|||I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

Thanks again
J|||you can go a long way with online free sql tutorials

however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

there are a couple of good sites listed here: http://r937.com/links.cfm?links=sqlsql

non-sa users get access denied in OLE DB query

On SQL Server 2005, 64 bit, this query:
SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
MISDB2A.FASMSCAC_STD_CARRIER_CODE')
Gets this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
I works if it is run by the sa login, or by a login that has been granted
the sysadmin Server Role.
How can I permission non-sa logins to access the OLE DB provider?Ad-hoc remote queries are disabled by default in SQL 2005. Check the
"Surface Area Configuration for Features (Ad Hoc Remote Queries) - Database
Engine " topic in BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/fe754945-2cd2-4cc1-8ae1
-4de01907b382.htm).
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:0896B540-3262-42A8-8D4E-97D900C35A91@.microsoft.com...
> On SQL Server 2005, 64 bit, this query:
> SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
> SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
> MISDB2A.FASMSCAC_STD_CARRIER_CODE')
> Gets this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access
> this provider through a linked server.
> I works if it is run by the sa login, or by a login that has been granted
> the sysadmin Server Role.
> How can I permission non-sa logins to access the OLE DB provider?|||It has been turned on!
With it left off, EVERYBODY gets this mssage:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
So, with ad hoc turned on, how do you permission non-sa logins to access the
OLE DB provider?|||Stu (Stu@.discussions.microsoft.com) writes:
> But turned on, uness you have sysadmin privs, you get this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access this provider through a linked server.
> So, with ad hoc turned on, how do you permission non-sa logins to access
> the OLE DB provider?
Books Online gives us this little tidbit:
OPENROWSET can be used to access remote data from OLE DB data sources
only when the DisallowAdhocAccess registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.
So that seems to be the key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> i search the registry for DisallowAdhocAccess and I found that under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL.1\Providers
> there is a whole list of providers. Of these SQLNLCI has
> DisallowAdhocAccess
> set to 0, but the others not. I did a test with a plain-test user, and
> it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
> did not have DisallowAdhocAccess listed.
Yes, you nailed it. In BOL, it also stands "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option." In addition, there is a funny thing: you can set this
option to 1 through SSMS (Server Objects - Linked Servers - Providers -
properties of a specific provider). When you check the checkbox, SSMS
creates the registry key. However, you cannot set it to 0 with SSMS. When
you uncheck the checkbox, SSMS deletes the key. So the only option to set it
to 0 is with registry editor (ok, or with SMO, I guess). Not very
recommendable.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Yes, you nailed it. In BOL, it also stands "This option controls the
> ability of non-administrators to run ad hoc queries. Administrators are
> not affected by this option." In addition, there is a funny thing: you
> can set this option to 1 through SSMS (Server Objects - Linked Servers -
> Providers - properties of a specific provider). When you check the
> checkbox, SSMS creates the registry key. However, you cannot set it to 0
> with SSMS. When you uncheck the checkbox, SSMS deletes the key. So the
> only option to set it to 0 is with registry editor (ok, or with SMO, I
> guess).
Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
In my opinion this is a bug. Someone has apparently not understood how
this provider option works. I've submitted
https://connect.microsoft.com/SQLSe...=2574
94
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm working now.
Thanks guys, for your research and feedback!|||Voted:-)
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98D4B72B433ADYazorman@.127.0.0.1...
> Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
> In my opinion this is a bug. Someone has apparently not understood how
> this provider option works. I've submitted
> https://connect.microsoft.com/SQLSe...=25
7494
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Non-queried parameter giving 'Invalid Column Name' error

I have created a non-queried boolean parameter called HideOptionalColumns.
This field doesn't exist in my query result set. I use this value in the
Hidden attribute of the Visiblity property for the column to hide or display
certain columns based on the user's runtime selection. It works fine when I
View Report in VS - the columns are hiddden/displayed appropriately. But
when I run the report, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'myDset'.
Invalid column name 'HideOptionalColumns'.
This seems to imply that it does need that parameter in the result set
itself. But that configuration doesn't work since the rows are then filtered
out based on that parameter. Is there another way to accomplish this hiding
of columns?
Thank you!On Apr 30, 4:04 pm, marian <mar...@.discussions.microsoft.com> wrote:
> I have created a non-queried boolean parameter called HideOptionalColumns.
Instead of using a parameter, you could place another field/textbox
right in your table or matrix. For each column you want to hide, set
the Hide option to trigger from the field or text box you add. I use
this a lot and it works without any problems.
> This field doesn't exist in my query result set. I use this value in the
> Hidden attribute of the Visiblity property for the column to hide or display
> certain columns based on the user's runtime selection. It works fine when I
> View Report in VS - the columns are hiddden/displayed appropriately. But
> when I run the report, I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'myDset'.
> Invalid column name 'HideOptionalColumns'.
> This seems to imply that it does need that parameter in the result set
> itself. But that configuration doesn't work since the rows are then filtered
> out based on that parameter. Is there another way to accomplish this hiding
> of columns?
> Thank you!sql

non-queried param problem

I know there have been a bunch of posts re: setting a param to 'All'.
I have done this successfully when using a query param but an using a
non-queried list for this project and the methods I've used in the
past such as " (cy.Division IN (@.Div) OR '(All)' in(@.Div)) " in the
dataset do not work. I get an error stating that I can't convert
'all' to an int data type.
I also tried doing multi-select but get a 'SQL error near ','"
message. I only have two actual values for the parameter plus all.
I've someone could please tell me how to get this going I'd appreciate
it.
DebraYou are confusing what is shown versus the value. If the parameter is an
integer then when putting in the parameter lis have the label be All and the
value be some value that your data never is (like 0 or a negative or
something). The important thing here is that the value cannot exist in your
data.
Then you can still do this:
select * from sometable where (somefield = @.MyParam or @.MyParam = 0)
Note that the 0 is whatever you have made the All parameter value be (-1,
9999, etc).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Debralous" <debras@.ehtc.com> wrote in message
news:1170270898.588687.306440@.q2g2000cwa.googlegroups.com...
>I know there have been a bunch of posts re: setting a param to 'All'.
> I have done this successfully when using a query param but an using a
> non-queried list for this project and the methods I've used in the
> past such as " (cy.Division IN (@.Div) OR '(All)' in(@.Div)) " in the
> dataset do not work. I get an error stating that I can't convert
> 'all' to an int data type.
> I also tried doing multi-select but get a 'SQL error near ','"
> message. I only have two actual values for the parameter plus all.
> I've someone could please tell me how to get this going I'd appreciate
> it.
> Debra
>|||Thank you so much. Obvious once you point it out! I appreciate you
taking the time!

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol =
25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thx
On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =
>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes.
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.

>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.

>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!

>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.

>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!

>im a developer, so your patience is appreciated.
I hear that.
J.
|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:

> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm

> Well, if it's *around* 26m, then yes.
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.

> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.

> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.

> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much
|||Some comments inline:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
>
> thx just to confirm
>
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular pages soon again? The
purpose of caching data is for the data to be in cache next tome someone refers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. See:
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp

>
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment what is best for you we
would know what the queries you want to support look like.

> thx much

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol =
25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thxOn Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =
>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes.
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.

>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.

>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!

>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.

>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!

>im a developer, so your patience is appreciated.
I hear that.
J.|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.
4ax.com:

> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm

> Well, if it's *around* 26m, then yes.
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.

> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.

> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.

> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much|||Some comments inline:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.
4ax.com:
>
> thx just to confirm
>
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular page
s soon again? The
purpose of caching data is for the data to be in cache next tome someone ref
ers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. Se
e:
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp

>
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment wh
at is best for you we
would know what the queries you want to support look like.

> thx muchsql

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol = 25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thxOn Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes. :)
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.
>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.
>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!
>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.
>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!
>im a developer, so your patience is appreciated.
I hear that.
J.|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
> Well, if it's *around* 26m, then yes. :)
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.
>>4) the solution is to create a nonclustered index on that col,
>>correct?
> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.
>>5) is it ok if i add more than one column to that nonclustered index?
> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much|||Some comments inline:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
>> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
>> <usenet.20.jimbo-black@.antichef.net> wrote:
> thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
>> Well, if it's *around* 26m, then yes. :)
>> It doesn't store the results in memory, but as long as it has to read
>> them into memory to look at momentarily, it uses available memory to
>> cache them, just in case you ask for them again soon.
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
>> It will release it depending on competing demands.
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular pages soon again? The
purpose of caching data is for the data to be in cache next tome someone refers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
>>4) the solution is to create a nonclustered index on that col,
>>correct?
>> Maybe.
>> It will help if the statistics on the distribution of values is such
>> that SQLServer thinks it's going to be cheaper to use the index. This
>> is generally the case if you're going to return just a few rows. But
>> if your query is going to return more than about 20% of the table, an
>> index won't help, scans are generally much faster. If the values
>> being selected are all contiguous, a clustered index may still be
>> faster than a complete scan. As always, "it depends" is applicable.
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
>>5) is it ok if i add more than one column to that nonclustered index?
>> OK by me.
>> SQLServer only remembers the statistics for the first column, and an
>> index on two columns is less efficient at fetching values selected by
>> only one column, but if your query is by two fields, putting an index
>> on both at once is called a "covering index" and becomes an excellent
>> idea!
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment what is best for you we
would know what the queries you want to support look like.
> thx much

Nonempty Problem

following query is nt working properly. need a solution (need to filter the non empty rows)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
non empty CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]NonEmpty({filter(CROSSJOIN([CustomTimeSet],[GeneralLedgerSet]),[Measures].[MdaCodeTotal] <> 0 )})on rows

I am a bit suspicious about the [Measures].[Description] measure. Is this a calculated measure? It could be what is causing the non empty clause not to work. If you are using SSAS 2005, something like the following might work:

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
nonempty( CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) , {Measures.MdaCodeTotal})on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]

Note I am using the second parameter in the NonEmpty() function to set the measure context for the non empty evaluation.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
--
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
Amin
SQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>
|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--