We have discovered when we migrated from Sybase to M$SQL that Sybase returns
row incrementally from top of table to bottom - whereas M$SQL seemed to jump
around - nothing that a ORDER BY didn't fix - but it was strange
nonetheless.

___________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200


-----Original Message-----
From: Albert van Biljon
[mailto:mssqldba-ezmlmshield-x22296642.[Email address protected]
Sent: Tuesday, June 29, 2004 6:10 AM
To: LazyDBA Discussion
Subject: Re: Strange problem

So, are you saying that when you do the SELECT on STable and you
specify the ORDER BY clause on Counter, that the numbers in the Counter
column go for instance: 4, 5, 3, 6, 1, 2, etc. in other words, not 1,
2, 3, 4, 5, 6 sorted?

That would be difficult to believe... I cannot really see how the
collations can influence an integer column either.

--- nomi nomi <mssqldba-ezmlmshield-x63496923.[Email address protected]
wrote:
> Hi Everybody,
>
> I am having a strange problem, i wonder if anybody knows the
> solution:
>
> I have written the following SP:
>
> CREATE PROCEDURE sp_search_results
> @SID varchar(50),
> @STypeID int,
>
> AS
>
> SELECT field1,field2,field3 INTO #tempsearch FROM tbl_cfields ORDER
> BY
> field1
>
> insert into STable (SID, STypeID, counter, cCode, cdesc)
> select @SID, @STypeID, 0, field1, field2
> from #tempsearch order by field1
>
> update STable
> set counter = @counter,
> @counter = @counter + 1
> where sid=@sid
> and STypeID=@STypeID
>
>
> But when i execute this query:
>
> select counter,
> CCode,
> field1,
> field2
> FROM STable
> WHERE sid=@sid
> and stypeid=6
> order by counter
>
> The result set aways has a different order, It is not sorted always.
> The
> problem occurs in the insert into statement, it does not insert the
> data in
> sorted order inspite of order by clause. i Have created STable with
> the
> following script.
>
>
> CREATE TABLE STable (
> SID varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> STypeID int NOT NULL ,
> Counter int NOT NULL ,
> IntID int NULL ,
> CCode char (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> Cdesc [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON PRIMARY
>
>
> I have even created STable without specifying the collations like
> this:
>
> CREATE TABLE STable (
> SID varchar (50) NOT NULL ,
> STypeID int NOT NULL ,
> Counter int NOT NULL ,
> IntID int NULL ,
> CCode char (256) NULL ,
> Cdesc [varchar] (256) NULL
> ) ON PRIMARY
>
> but it doesn't work.
>
>
> However when i use a temporary table instead of Permanent table it
> works
> fine and the data is always sorted, the code is like this:
>
>
> CREATE PROCEDURE sp_search_results
> @SID varchar(50),
> @STypeID int,
>
> AS
>
> SELECT field1,field2,field3 INTO #tempsearch FROM tbl_cfields ORDER
> BY
> field1
>
> insert into #tempSTable (SID, STypeID, counter, cCode, cdesc)
> select @SID, @STypeID, 0, field1, field2
> from #tempsearch order by field1
>
> update #tempSTable
> set counter = @counter,
> @counter = @counter + 1
> where sid=@sid
> and STypeID=@STypeID
>
> insert into STable (SID, STypeID, counter, cCode, cdesc)
> select SID, STypeID, counter, cCode, cdesc
> from #tempSTable
>
> I presume that it has something to do with collations, but tempdb and
> the
> database that i have has same collation settings. Can anybody tell me
> the
> reason?
>
> Thanks
> nomi
>
> _________________________________________________________________
> MSN Toolbar provides one-click access to Hotmail from any Web page -
> FREE
> download!
> http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]

MS Sql Server LazyDBA home page