Thanks for all of your replies. But i think some of u have not understood
the question clearly.
Look i am using order by clause when using the 'insert into.....' statement
so it should be ordered but it is not.
just have a look at different sets of data that i am getting as a result of
different executions:
counter CCode field1 field2
----------- -------------- -------------
------------------------
1 ACETRAVE0001 ACETRAVE0001 Ace Travel
2 ADVANCED0001 ADVANCED0001 Advanced Office systems
3 ALLENSON0001 ALLENSON0001 Allenson Properties
4 AMERICAN0001 AMERICAN0001 AmericaCharge
5 ASSOCIAT0001 ASSOCIAT0001 Associated Insurance Inc.
6 ATTRACTI00001 ATTRACTI00001 Attractive Telephone Co.
counter CCode field1 field2
----------- -------------- ---------------
-----------------------------------------------------------------
1 PAGEMAST0001 PAGEMAST0001 PageMaster
2 PERMIERS0001 PERMIERS0001 Premier System, Inc.
3 PRINTER1 PRINTER1 Print Vendor
4 PROFESSI0001 PROFESSI0001 Professional Travel Consultant
5 READYREN0001 READYREN0001 Ready Rentals
Please try this sp. at your site and let me know of any difference in the
output.
Thanks
nomi
>From: "Albert van Biljon " <[Email address protected]
>To: [Email address protected]
>Subject: Re: Strange problem
>Date: Tue, 29 Jun 2004 06:09:47 -0700 (PDT)
>
>**A LazyDBA.com subscriber has responded to your lazydba.com post**
>**LazyDBA.com mail shield has forwarded you this email,
>**and removed any attachments, and kept your email address secret
>**from this person, and any viruses/trojans.
>**If you reply to this email, the person will see your email address as
>normal
>**Anything below this line is the original email text
>
>
>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]
> >
> >
>
>
>
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
MS Sql Server LazyDBA home page