Cursor and order by

I noticed that I cannot use ORDER BY in cursor, but in my case I need to order data in cursor.
I'm thinking about selecting data for cursor from temp table where data is ordered but may be there are other decisions to get data in cursor ordered?

declare CR cursor ...

for

select ..

from ...

order by ...

|||This query works:
select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date]

Works

Now I try

declare @.MembersValues cursor
set @.MembersValues = cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])

And I get message "Incorrect syntax near the keyword 'order'."
I use SQL server 2005 Express

|||

It doesn't like the parens around the select. It gives the error message in the wrong spot. It is not obvious what the parser is thinking.

By the way, Cursors are rarely required. Have you considered using a set query instead?

|||I didn't get an error when running without parents. But it's still paradox why it's working properly while running without ORDER BY with parents.

Thanks for the help|||

In the second case, you need to quote the cursor declare statement as a string.

declare @.MembersValues cursor
set @.MembersValues = N'cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])'

|||

No. This is not valid syntax. See the SET statement topic in BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms189484.aspx

Current Time

Hi. I have a critical design issue. Not a regular time dimension case.

i work for a set of schools where each school academic has a different academic calendar. say some schools start in august, others in september.. i generated the date keys per school to populate the time dimension. so my time dimension looks like this:

DATE_KEY,

DATE_SCHOOL,

DATE_DATE,

DATE_YEAR,

DATE_MONTH,

DATE_WEEK,

DATE_DAY_NUMBER,

DATE_FISCAL_ACAD_YEAR,

DATE_FISCAL_TERM, DATE_FISCAL_WEEK,

DATE_FISCAL_WEEKDAY_NUMBER,

DATE_FISCAL_ACAD_YEAR_DAY_NUMBER,

DATE_HOLIDAY, (y/n)

DATE_WEEKEND, (y/n)

DATE_DAY_NAME,

DATE_FISCAL_TERM_FIRST_DAY,

DATE_FISCAL_TERM_LAST_DAY,

DATE_FISCAL_WEEK_FIRST_DAY,

DATE_FISCAL_WEEK_LAST_DAY

I need help in finding a design to get the current term/current week/current day so that a school manager would get his current's school week's data or term data when he logs in to the system.

Thanks

Could it be that there are only a few sets of dates applicable? In which case you could create Calendars listing the relevant dates and link this back to the school.|||

I added fields to the schools dimension stating the current acad year, term,week,and day per school. and these fields are to be updated at each ETL process. I will use them in the queries.

thanks

CS vs CI database

If I have to send a database to someone and that person can
be using a Case Sensitive or a Case Insensitive SQL Server 2000,
what do I do?

Should i create two databases: 1 CS and 1 CI?

Can't I simply work on 1 CS database all the time and whatever
the person's SQL Server Sensitivity is setup, my database would
work fine? I want to avoid having to have and work on two
databases for this particular purpose.

What happens if someone puts a CS db when their SQL Server
is setup as CI? Would they have a CS db inside a CI SQL Server
environment? Is there a way to convert the CS database to a CI
database by changing the database's properties or something?

Thank youCase-sensitivity of data is determined by the Collation, which is set at the
column-level rather than the database or server level. There's no problem in
principle with mixing different collations on the same server, in the same
database or even in the same table.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:5cqdnViCKOWr8xLfRVn-1w@.giganews.com...
> Case-sensitivity of data is determined by the Collation, which is set at
> the column-level rather than the database or server level. There's no
> problem in principle with mixing different collations on the same server,
> in the same database or even in the same table.
> --
> David Portas
> SQL Server MVP
> --

There is one case where mixing collations can be a problem - joins and
queries on other databases, either on the same server or on linked servers.

I've seen this come up several times with temp tables - if tempdb does not
have the same collation as your user databases, you can have problems such
as queries and joins returning unexpected results, or even "invalid object
name" errors if you have code in procs, views etc. which has temp table
names in different cases (#tmp vs #Tmp).

Probably the 'safest' approach is to develop in a case-sensitive
environment, because you know your code will work in a case-insensitive
one - the reverse is not true (I believe this is what Kalen Delaney
suggested in Inside SQL Server 2000). If you do need some queries to be
case-insensitve, then you can use COLLATE in the query, which is probably
easier to manage than changing collations at the table level.

Simon|||serge (sergea@.nospam.ehmail.com) writes:
> If I have to send a database to someone and that person can
> be using a Case Sensitive or a Case Insensitive SQL Server 2000,
> what do I do?
> Should i create two databases: 1 CS and 1 CI?
> Can't I simply work on 1 CS database all the time and whatever
> the person's SQL Server Sensitivity is setup, my database would
> work fine? I want to avoid having to have and work on two
> databases for this particular purpose.
> What happens if someone puts a CS db when their SQL Server
> is setup as CI? Would they have a CS db inside a CI SQL Server
> environment? Is there a way to convert the CS database to a CI
> database by changing the database's properties or something?

It depends what you mean with "send a database". If you send the physical
database, or a backup thereof, you can work with whichever collation
you prefer. Just make sure that you use "COLLATE DATABASE_DEFAULT"
on all character columns in temp tables and table variables (including
return tables from user-defined functions). As an extra precaution,
you should test that database on a server with a different default
collation than the database.

If you instead send a script of the database, you don't need COLLATE
with your temp tables, as you can assume that the database will be
installed with the default collation of the target server. In this
case you should develop with a case-sensitive collation. Furthermore,
you should use only lowercase names. Or at very least you should
use a naming convention, so you don't have an object called "gadgets"
and another "GADGETS", which would cause errors on a case-insensitive
collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Simons point about temp tables can be also be mitigated if you create the
temporary tables separately and specify the column collation.

Also consistent use of case throughout your programs/procedures helps with
cache re-use, therefore it is a good idea to make sure code is case
consistent even if you are not using case sensitivity.

John

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:428f7271_3@.news.bluewin.ch...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:5cqdnViCKOWr8xLfRVn-1w@.giganews.com...
>> Case-sensitivity of data is determined by the Collation, which is set at
>> the column-level rather than the database or server level. There's no
>> problem in principle with mixing different collations on the same server,
>> in the same database or even in the same table.
>>
>> --
>> David Portas
>> SQL Server MVP
>> --
>>
>>
> There is one case where mixing collations can be a problem - joins and
> queries on other databases, either on the same server or on linked
> servers.
> I've seen this come up several times with temp tables - if tempdb does not
> have the same collation as your user databases, you can have problems such
> as queries and joins returning unexpected results, or even "invalid object
> name" errors if you have code in procs, views etc. which has temp table
> names in different cases (#tmp vs #Tmp).
> Probably the 'safest' approach is to develop in a case-sensitive
> environment, because you know your code will work in a case-insensitive
> one - the reverse is not true (I believe this is what Kalen Delaney
> suggested in Inside SQL Server 2000). If you do need some queries to be
> case-insensitve, then you can use COLLATE in the query, which is probably
> easier to manage than changing collations at the table level.
> Simon