Next Run Date column not sorting

We've just installed SQL Server 2000 on one of our servers and have
noticed a strange behavior. When clicking the column headings in the
job display in Enterprise Manager, the list is sorted (first click
ascending, then descending) on the column clicked except for "Next Run
Date." When we click on "Next Run Date," the list is reordered, but
randomly. Each time we click it we get a different order but never
ascending or descending. This is true whether we're at the server or
on a remote machine. We never saw this behavior with SQL Server 7.0.

Any ideas? Thanks!"Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
news:e3153db3.0410110535.129c4159@.posting.google.c om...
> We've just installed SQL Server 2000 on one of our servers and have
> noticed a strange behavior. When clicking the column headings in the
> job display in Enterprise Manager, the list is sorted (first click
> ascending, then descending) on the column clicked except for "Next Run
> Date." When we click on "Next Run Date," the list is reordered, but
> randomly. Each time we click it we get a different order but never
> ascending or descending. This is true whether we're at the server or
> on a remote machine. We never saw this behavior with SQL Server 7.0.
> Any ideas? Thanks!

The problem with Enterprise Manager is that you never really know what it's
doing. If you run a SELECT query with an ORDER BY clause in Query Analyzer,
does it return the correct order? QA is a much better and simpler tool for
retreiving and manipulating data than EM, because there's no guesswork
involved and you can control exactly what data you retrieve and how.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416ad914_2@.news.bluewin.ch>...
> "Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
> news:e3153db3.0410110535.129c4159@.posting.google.c om...
> The problem with Enterprise Manager is that you never really know what it's
> doing. If you run a SELECT query with an ORDER BY clause in Query Analyzer,
> does it return the correct order? QA is a much better and simpler tool for
> retreiving and manipulating data than EM, because there's no guesswork
> involved and you can control exactly what data you retrieve and how.
> Simon

ORDER BY works fine in QA. Ordering also works fine in other columns
of the job list in Ent. Mgr. The "Next Run Date" column is the only
one that is acting weird. If I use SQL 2000 Ent. Mgr. from my laptop
to look at any of our servers (4 SQL 7.0 & 1 SQL 2000) the same
behavior occurs. Ent. Mgr. on our 7.0 servers works fine but, of
course, cannot connect to the 2000 server.

Rich|||"Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
news:e3153db3.0410120501.7ab303bc@.posting.google.c om...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:<416ad914_2@.news.bluewin.ch>...
>> "Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
>> news:e3153db3.0410110535.129c4159@.posting.google.c om...
>>
>> The problem with Enterprise Manager is that you never really know what
>> it's
>> doing. If you run a SELECT query with an ORDER BY clause in Query
>> Analyzer,
>> does it return the correct order? QA is a much better and simpler tool
>> for
>> retreiving and manipulating data than EM, because there's no guesswork
>> involved and you can control exactly what data you retrieve and how.
>>
>> Simon
> ORDER BY works fine in QA. Ordering also works fine in other columns
> of the job list in Ent. Mgr. The "Next Run Date" column is the only
> one that is acting weird. If I use SQL 2000 Ent. Mgr. from my laptop
> to look at any of our servers (4 SQL 7.0 & 1 SQL 2000) the same
> behavior occurs. Ent. Mgr. on our 7.0 servers works fine but, of
> course, cannot connect to the 2000 server.
> Rich

If it's a date column, then perhaps some sort of regional settings issue
might be causing it? Although if it works properly in QA, then I personally
wouldn't worry about it; EM has plenty of other data manipulation issues
anyway, as described in some detail here:

http://www.aspfaq.com/show.asp?id=2455

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416bd8fc$1_3@.news.bluewin.ch>...
> If it's a date column, then perhaps some sort of regional settings issue
> might be causing it? Although if it works properly in QA, then I personally
> wouldn't worry about it; EM has plenty of other data manipulation issues
> anyway, as described in some detail here:
> http://www.aspfaq.com/show.asp?id=2455
> Simon

If the job list was sorted consistently when I click on the column
heading I'd believe it was a regional setting. But everytime I click
the "Next Run Time" heading, I get a random result. The other columns
sort ascending on the first click and descending on a second click and
continue to flip-flop as many times as they are clicked. Not so with
the "Next Run Time" column. There's no rhyme or reason to the order
that jobs are displayed no matter how many times I click -- it's
different each time -- not just a reverse of the previous display.

QA doesn't help when managing jobs -- as the article points out EM is
the preferred tool for that -- QA queries would simply be too long and
too complex. I'm just puzzled by what could cause this weird behavior
in EM.

Thanks,
Rich

NEWSEQUENTIALID behavior changed from Apr CTP to June CTP

There may be a bug in the June CTP version of NEWSEQUENTIALID.

NEWSEQUENTIALID had very predictable behavior in the Apr CTP build. It incremented in obvious, clear, consistent amounts. Doing inserts right after another or having widely range time intervals between inserts made no difference.

With the June CTP build, things are not as predictable and do not appear to be sequential. The following output shows the guids that were created. The test plan inserted 10 rows, waited some varying interval of time, then inserted another 10 rows, etc. until all rows were inserted.

Waiting a period of time between inserts of 10 rows appeared to introduce gaps into the sequence. Additionally, the last group of 10 inserts appears to have guid values that fill in some of the gap left by the previous inserts.

30608b57-5dee-d911-83a8-0003ff3ba433 insert 10 rows
31608b57-5dee-d911-83a8-0003ff3ba433
...
39608b57-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
80ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
89ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
8aab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
93ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
94ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
9dab5672-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
c0430490-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
c9430490-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
a0143ddb-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
a8143ddb-5dee-d911-83a8-0003ff3ba433

This looks like it could be a bug becuase NEWSEQUENTIALID() values are supposed to be allocated in strictly increasing order. Can you send me a repro? Thanks.

Eric
e h a n s@.microsoft.com|||

Code was sent to Eric Hanson by email. It is also shown below:

USE AdventureWorks
GO

CREATE TABLE dbo.SEQTEST(
[ID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[Name] [nchar](50) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_SEQTEST] PRIMARY KEY CLUSTERED
)
GO

Make a console application using the following code. Run the console application several times.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace NEWSEQUENTIALID
{
class Program
{
static void Main(string[] args)
{
Inserter lInsert = new Inserter();

for (int i = 0; i < 10; i++)
lInsert.InsertRow(String.Format("batch " + DateTime.Now.ToLongTimeString() + " insert number {0}", i));
}
}
public class Inserter
{
private static string _insertCommand =
"INSERT INTO SEQTEST (Name) OUTPUT INSERTED.ID Values(@.Name)";

public bool InsertRow(string Name)
{
SqlConnection lConnection = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=AdventureWorks");
lConnection.Open();

SqlCommand lInsertCmd = new SqlCommand(_insertCommand, lConnection);
lInsertCmd.Parameters.AddWithValue("Name", Name);

IDataReader lResult = lInsertCmd.ExecuteReader();

while (lResult.Read())
{
Console.WriteLine("ID: {0} Name: {1}", lResult.GetGuid(0).ToString(), Name);
}

lConnection.Close();

return true;
}
}
}

|||It appears that although the behavior may have indeed changed from the April to June CTPs, everything is still working properly. It is probably not wise to try to infer guid sort order by visually inspecting the character representations of the guids. If someone could confirm this by explaining the character representation of and also the internal structures of guids, that would be great.

NEWSEQUENTIALID in the April CTP consistently produced results that were very obviously increasing by looking at the character representations of the guids. The character pattern was completely predictable.

NEWSEQUENTIALID in the June CTP generates results that actually do follow an ascending sort order in SQL as evidenced by ORDER BY. The difference in behavior is that the character representation of the guids does not obviously show the ascending sort when casually inspecting the characters.|||

NEWSEQUENTIALID is not meant to generate gap-less ranges. It's only guarantee is that it will generate strictly increasing values.

As for the part of your repro where you observed that the values did not appear to be increasing. We defined "increasing" in different terms than the character value you see. This is because SQL Server implements it's own comparison semantics on GUIDs.

If you read the first 4 bytes of your GUID value from right to left, you'll notice that they are in strictly increasing order.

If I copy paste your generated GUIDS, and reverse the first DWORD, we see:
578b6030-5dee-d911-83a8-0003ff3ba433 insert 10 rows
578b6031-5dee-d911-83a8-0003ff3ba433
...
578b6039-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
7256ab80-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
7256ab89-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab8a-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab93-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab94-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab9d-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
900443c0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
900443c9-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
db3d14a0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
db3d14a8-5dee-d911-83a8-0003ff3ba433

Which better describes how we define increasing.

I will check with documentation folks to see if it's possible to be clearer on this.

Let me know if you need more details,

Thanks
-Mat
mathh-at-microsoft-dot-com