Re: Strange problem

Re: Strange problem

 

  

As I understand it, with one run, the counters get the values 1 to 5
for values A to E for instance, and with the next run, the counters 1
to 5 are assigned to F to J.

When you are updating the Counter-column, you don't do the updates in
any particular order, which means you depend on the way SQL Server
returns it to you, which is not very predictable if you have no
WHERE-clause.

update STable
set counter = @counter,
@counter = @counter + 1
where sid=@sid
and STypeID=@STypeID
ORDER BY STable. ...

Can you try it and let us know?

--- nomi nomi <mssqldba-ezmlmshield-x17408429.[Email address protected]
wrote:
>
> Albert,
> Thanks for your response.
> But i have checked it numerous times the problem is with 'insert
> into...'
> and not with update.
>
> u can check it by inserting a select stmt. before insert statement.
>
> Thanks
> nomi
>
> >From: "Albert van Biljon " <[Email address protected]
> >To: [Email address protected]
> >Subject: Re: Strange problem
> >Date: Wed, 30 Jun 2004 00:44:44 -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
> >
> >
> >I think I understand the problem now.
> >
> >The problem is probably not with the insert, but with the update -
> try
> >an ORDER BY in your update clause (update #tempSTable...) and see if
> >you get better results.
> >
> >--- nomi nomi <mssqldba-ezmlmshield-x22212693.[Email address
> protected]
> >wrote:
> > > 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


MS Sql Server LazyDBA home page