date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date field issue

I have column 'date' and its data type is datetime. But when I try to sort this column by date Its not doing the sort. What could be the reason.It was working fine until last 1 ort 2 days.
Thanks.Good question. Maybe if you provided some specifics and code examples (see the sticky note at the top of the forum), you might even get some assistance and an answer.

So with the information you have given, I would guess the phase of the moon, or that you are ordering after doing some sort of string conversion.

Date dimension Sort order

Hi-

I have a date dimension with year (number), month(Month name), and day(number) as part of a cube which I'm generating an offline cube from.

While browsing the cube in either VS or in the offline cube, through Excel, the months end up sorted alphabetically (April, August...) and the days of the month are sorted in the fashion of 1,10,11...2,20,21...

Anyone have any suggestions or ideas of what I'm doing wrong?

Thanks,

Tristan

You should mark your days as 01, 02, 3... to avoid the order issue you are seeing.

For the month, you should create a Month of year attribute (Jan - 01, Feb - 02, etc...) and then use the sort property ont he month attribute to sort it by the Month of Year attribute....

|||That worked perfect! Thanks much!

Date dimension Sort order

We created Time Dimension from Dimension Wizard , the months default sort by alphabetically, I try to set ‘order by attributeName’ in BI Studio, but ‘OrderByAttribute’ from Properties is an empty dropdown box and can not enter the word.

What I'm doing wrong?

Thanks.
In Time Dimension created from Dimension Wizard, there is a month_name attribute which is sorted by alphabetically 'April, August...', and another attribute month_of_year_name. I want to display month_name, but order by 'month_of_year_name', how can I do this? or has another way to sort month name?

Any idea?

Thanks in advance.
|||

To solve this problem you will have to add a second column for the month number in the data source view.

Use the TSQL function DatePart() for this.

Use this month number as the key column and the month name as the name column in the properties pane for the attribute.

For the month number you will have to add year in the key as a collection because a month number(1-12) is not unique over years.

This is also found in the properties for the month attribute.

SSAS2005 sorts by key as a default so this should work.

HTH

Thomas Ivarsson

|||Great! It works.

Thank you very much Thomas for the help.

Date columns exported as dates when exported into Excel

Is it possible to have a DateTime field exported into excel as a datetime
fields so that the user can sort in Excel on this datefield?are you right clicking the field in RS, then going to properties and then
setting the format to 'Date'?
Not sure how excel interperates this though.
"MicroMoth" wrote:
> Is it possible to have a DateTime field exported into excel as a datetime
> fields so that the user can sort in Excel on this datefield?

Date & Time formatting to sort by date and time.

Hi,
I have a Startdate and StartTime in my table, I would like to combine to the
2 columns as one column so that it can be sorted as date.
startDate - smalldatetime
startTime - nvarchar ( I cannot change this)
my current query:
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ses_start_time end,
from Course
where courseID=1
order by StartsAt
The problem with the above query is that it does not sort by date and timecorrection to my query -
current query:
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ StartTime end,
from Course
where courseID=1
order by StartsAt
"Mike" wrote:

> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to t
he
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||Can you give us some sample data? Did you try
ORDER BY Convert(DATETIME, convert(varchar, StartDate,111)+case when
StartTime is null then '' else ' '+ses_start_time end)
Also, suggest you define varchar(length) and not just leave the default.
You will be surprised when you get varchar(30) in some places and varchar(1)
in others. Finally, I also suggest a non-ambiguous ANSI-standard format
style, such as 112 or 120.
A
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1E03CBCF-31DD-4530-BE5C-DD3F2F2134EF@.microsoft.com...
> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to
> the
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||> Finally, I also suggest a non-ambiguous ANSI-standard format style, such
> as 112 or 120.
...this will sort correctly without the additional conversion (provided you
are storing time in 24H/military time, not 12H/AM/PM format).|||time is 12h format
sample data:
2006/05/14 11:00AM
2006/05/14 4:00 AM
2006/05/14 9:00 AM
I would like it to sort as
2006/05/14 4:00 AM
2006/05/14 9:00 AM
2006/05/14 11:00 AM
I tried your way but the 'AM' and 'PM' is not displayed.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you give us some sample data? Did you try
> ORDER BY Convert(DATETIME, convert(varchar, StartDate,111)+case when
> StartTime is null then '' else ' '+ses_start_time end)
> Also, suggest you define varchar(length) and not just leave the default.
> You will be surprised when you get varchar(30) in some places and varchar(
1)
> in others. Finally, I also suggest a non-ambiguous ANSI-standard format
> style, such as 112 or 120.
> A
>
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1E03CBCF-31DD-4530-BE5C-DD3F2F2134EF@.microsoft.com...
>
>|||try this... untested..
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ses_start_time end,
from Course
where courseID=1
order by startdate asc, cast(starttime as datetime) asc
"Mike" wrote:

> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to t
he
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||> I tried your way but the 'AM' and 'PM' is not displayed.
What is "my way"? Can you show specs (see http://www.aspfaq.com/5006)
including sample data (INSERT statements), and the query you ran that
somehow dropped off the AM/PM?