"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

"SELECT DISTINCT and ORDER BY"-problem

Hi!

I can't seem to get my query to work. It returns (translated from Swedish):

Microsoft JET Database Engine (0x80004005)
The ORDER BY-instruction (myDate)..."doesn't work with"...DISTINCT.

This is my query (somewhat simplified):

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT DATEPART(year, myDate) AS 'myColumn_alias' FROM myDB"
sql=sql & " WHERE myName='" & myName & "'"
sql=sql & " ORDER BY myColumn_alias"
rs.Open sql,conn

Is it because I have a WHERE-clause? If so, how should I design my query (I need only the years to populate a "select from" dropdownbox)? Or is it something else?

ThanksI don't think you can refer to a column alias the samw way you refer to a column.

Try:

ORDER BY DATEPART(year, myDate)|||I've tried that, but then I get the message that too few parameters are given and that 1 is expected.

:(|||All I had to do was to use YEAR(myDate) instead of DATEPART(year, myDate). Now it works!

"Order BY" question

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

tobias,

The best thing for you to do would be to have a lookup table that stores your "Status" values, and add a column called "SortOrder" to that table -- let this column define the order of the statuses. Then, in your query, join onto the Status table and do an ORDER BY SortOrder. This will also give you the flexibility of modifying the sort order to something else in the future.

|||

Thanks, vito. Forgive my lack of knowledge here, but could give an example of what the SQL select statement what look like that joined two tables and ORDER(ed) BY SortOrder? Thanks for your help.

|||

tobias5:

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

Vito's suggestion would be to build a lookup table, let's call it Status, that looks like this:

StatusCode SortOrder

evict 3
rehab 4
listing 1
escrow 2

Then your SQL statement would look like this:

SELECT

someColumns

FROM

myTable

INNER JOIN

Status ON myTable.StatusCode = Status.StatusCode

ORDER BY

Status.SortOrder

Alternately, and this is ultimately not as flexible, you could forego the additional table and use a CASE statement for the ordering:

SELECT

someColumns

FROM

myTable
ORDER BY

CASE StatusCode

WHEN 'evict' THEN 3

WHEN 'rehab' THEN 4

WHEN 'listing' THEN 1

WHEN 'escrow' THEN 2

ELSE 5

END

|||

Thanks vito and tmorton. Both of your suggestions worked beautifully.

I VERY much appreciate your help!

Ron

"order by" is slowing page load

hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M

<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL

Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0

Set strOrder = Request.QueryString("Order")

oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>

</head>

<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if

oRS.Open strSQL, oConn, 2, 3

oRS.moveFirst
Do while not oRS.eof

make = oRS("make")
model = oRS("model")
price = oRS("price")


%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop

oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.|||thanks, urm...what's a bubble sort and how do I do it?
and how do I create an index?
the columns to sort by are text and updated regularly by the CMS.

I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.|||Yeah? Go read the IBM or Oracle Manuals....

In any case, are we talking about SQL Server in the first place?

Do you have a dba or are you "it"

And why aren't you using stored procedures?|||This nifty asp you've written is wide open to sql injection. Do you know what that is?|||Oh God, Oracle manuals are the worst...|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.

Brett, what do you mean by "Do you have a dba or are you "it""
Feel free to direct me in the right direction|||Who administers your database?

Like, who creates tables for eaxmple?|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html

Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).|||Parameterise your data accces (don't try cleaning your strings).

Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...|||Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...

Oh yeah...like what?|||replacing apostrophes, equals signs, asterix' and percentages...

Eg. replace(<fieldname>,"'","")|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)|||Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?|||DECLARE @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.x varchar(8000) '
SELECT @.sql = @.sql + 'SET @.x =
CHAR(68)
+CHAR(82)
+CHAR(79)
+CHAR(80)
+CHAR(32)
+CHAR(68)
+CHAR(65)
+CHAR(84)
+CHAR(65)
+CHAR(66)
+CHAR(65)
+CHAR(83)
+CHAR(69)
+CHAR(32)
+CHAR(78)
+CHAR(111)
+CHAR(114)
+CHAR(116)
+CHAR(104)
+CHAR(119)
+CHAR(105)
+CHAR(110)
+CHAR(100)
SELECT @.x'

EXEC(@.sql)|||suffice it to say that scrubbing strings will address only the most elementary injection attacks.

the real fix is: NEVER execute sql that was pasted together based on user input. if you do this, you are vulnerable no matter how tricky your parsing code is.|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
Ahh clever... I did say I was using access but I never considered this|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)

actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

you guys ruin all of my fun. i am too busy these days to lead the posters around by the nose for several posts anyways.|||Haha I remember learning (not to) use bubble sorts 2 years ago... Man those were long lessons!

Imagine your old, grey, chunky, droning computing teacher mumbling on for an hour and a half just to turn round and go "..and that's why you won't ever use this" >:(|||bubble sort is actually ok if you only have a few items to sort. in practice even the most hairbrained algorithm will be fast if there are only 10 items.

as soon as you have 100k to sort though, your code will fall over if you use it.|||Thanks jezemine
is it likely that my small database of 30 or 40 cars with descriptions and specs will slow to the point of timeouts when I ask it to order by 'price' for instance?
my DB seems to have suddenly done this, could it be thatthe amount of data has suddenly broken some threshold? or as I suspect, could 1and1 have changed my DB at a low level and cripled its sort capabilty. It was fine last week.
NB: please keep it simple, all this archane terminology is just making matters worse for my little brain!|||I don't think so...

http://www.autotrader.com/fyc/searchresults.jsp?num_records=&search_type=both&distance=10&address=07052&style_flag=1&make=ASTON&model=&make2=&start_year=1981&end_year=2008&min_price=&max_price=&transmission=&engine=&drive=&doors=&fuel=&max_mileage=&color=&keywords_display=&sort_type=priceDESC&body_code=0&certified=&advanced=&default_sort=priceDESC&keywordsrep=&keywordsfyc=|||actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.

Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)|||I think you can always substitute a semicolon for the GO, anyway.|||Guys...what are you talking about?
what does this do?

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

- is this part of the thread or are you off on a tangent?|||Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

I stand corrected!

I was under the impression that "GO" was only understood by the client tools like osql/sqlcmd/QA as a batch separator. apparently it's also understood by EXEC and sp_executesql.|||Guys...what are you talking about?
what does this do?

- is this part of the thread or are you off on a tangent?
They are off on a semi-tangent, regarding whether the code you are using is safe from SQL Injection attacks.|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...

Ummmm...you...will be...you will...be...

In any case there are ways...but you need control, you must learn control

Use stored procs access only and you'll be fine|||a poorly written proc is just as vulnerable to sql injection. consider this proc:

create proc ExecSql (@.sql varchar(max)) as exec(@.sql)

"Order by" error running DBCC DBREINDEX

I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
following error:

"Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list.
Columns in the order by list must be unique. DBCC execution
completed. If DBCC printed error messages, contact your system
administrator."

I can successfully reindex other tables in this database. I thought
that perhaps I had objects in the database that ended up with the same
name, but I've pretty much ruled that out.

Any suggestions?

Thanks
John D. Morris
mailto://jmorris_42@.hotmail.comjmorris_42@.hotmail.com (John Morris) wrote in message news:<5b4367be.0308050426.10612a9f@.posting.google.com>...
> I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
> following error:
> "Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique. DBCC execution
> completed. If DBCC printed error messages, contact your system
> administrator."
> I can successfully reindex other tables in this database. I thought
> that perhaps I had objects in the database that ended up with the same
> name, but I've pretty much ruled that out.
> Any suggestions?
> Thanks
> John D. Morris
> mailto://jmorris_42@.hotmail.com

Followup:

As it turns out, I had a hidden "Hypothetical" index that the index
tuning wizard decided not to clean up when it was done. The only way
to see the index was in sp_helpindex. It did not appear in the object
browser. MS claims this was fixed in SP1...I guess not. Anyway,
dropped the hypothetical index and all is well.

-jdm|||John,

Can you consistently repro the bug?
--
Arun Marathe
Microsoft Corporation

Disclaimers:
(1) This posting is provided "AS IS" with no warranties, and confers no
rights.
(2) Use of included script samples are subject to the terms specified at:
http://www.microsoft.com/info/cpyright.htm

"John D. Morris" <jmorris@.sni.org> wrote in message
news:d034511a.0308051015.60cc849a@.posting.google.c om...
> jmorris_42@.hotmail.com (John Morris) wrote in message
news:<5b4367be.0308050426.10612a9f@.posting.google.com>...
> > I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
> > following error:
> > "Server: Msg 169, Level 15, State 2, Line 2
> > A column has been specified more than once in the order by list.
> > Columns in the order by list must be unique. DBCC execution
> > completed. If DBCC printed error messages, contact your system
> > administrator."
> > I can successfully reindex other tables in this database. I thought
> > that perhaps I had objects in the database that ended up with the same
> > name, but I've pretty much ruled that out.
> > Any suggestions?
> > Thanks
> > John D. Morris
> > mailto://jmorris_42@.hotmail.com
> Followup:
> As it turns out, I had a hidden "Hypothetical" index that the index
> tuning wizard decided not to clean up when it was done. The only way
> to see the index was in sp_helpindex. It did not appear in the object
> browser. MS claims this was fixed in SP1...I guess not. Anyway,
> dropped the hypothetical index and all is well.
> -jdm

"Order by" clause

I run the script below in sql2005 and am receiving the error msg 209. Do I
need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
them displyed in front.
Thank you.
Charlie
select STATE, TAXAMNT, SUBTOTAL, *
from sop30200
where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO
FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.
While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.
There are two solutions. One is to specify
ORDER BY sop30200.STATE
and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
chas2006 wrote:

>I run the script below in sql2005 and am receiving the error msg 209. Do I
>need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
>them displyed in front.
>Thank you.
>Charlie
>select STATE, TAXAMNT, SUBTOTAL, *
>from sop30200
>where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
>AND SOPTYPE IN (3,4)
>AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
>--AND TAXEXMT1 = ' '
>AND SUBTOTAL <> 0
>--AND SOPNUMBE LIKE 'NCINV%'
>AND VOIDSTTS = 0
>AND SOPNUMBE NOT IN
>(SELECT INVNO
>FROM VERTEX..REGPRERETURNSTBL
>WHERE COMPCD = 'ABS'
>and invno between '20070201' and '20070228')
>--AND SHIPTOGEOCODE LIKE '31%')
>ORDER BY STATE DESC
>
>Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'STATE'.
>

"Order by" by parameter in stored procedure

I would like to pass the name of a column to a stored procedure, so the
result of the query would be ordered by that column.
I tried this:
CREATE Procedure ProductsByTab
(
@.TabID int,
@.Order nvarchar (50)
)
AS
SELECT
*
FROM
Product
WHERE
TabID = @.TabID
ORDER BY
@.Order
GO
but I get this message:
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.
Is it possible to do what I want? What am I doing wrong?
Thank you.i have the same thing but i use a different approach, i pass a flag
representing the numeric order of the field
select * from ttt
order by case @.flag when 1 then name when 2 then description else '' end,
case @.flag when 3 then amount else 0 end,name,description,amount
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||Thanks! Your replies were absolutely efective.

"limit" in sql server

someone suggested I use:

select top 20 * from t
where pkc not in (select top 10 pkc from t order by pkc) order by pkc

to simulate the limit function in mysql.

i want to replace the 10 in the inner select with a variable. When I do this:

select top 20 * from t
where pkc not in (select top @.counter pkc from t order by pkc) order by pkc

it gives me in an error in sproc. Pls help!!It needs to be dynamic

DECLARE @.SQL varchar(8000), @.Counter int
SELECT @.Counter = 10
SELECT @.SQL = 'select top 20 * from t'
+ ' where pkc not in (select top '
+ CONVERT(varchar(15),@.counter)
+ ' pkc from t order by pkc) order by pkc'
SELECT @.SQL
EXEC(@.SQL)

"Keys out of order on page (1:10579653), slots 24 and 25..." error messages

Hi,
I have sql server 2000, sp3, and several production databases on this
server. From time to time, my integrity check jobs fail on some of databases
with the error message shown above (not always the same page/slot). This
errors occur on index pages, and I resolve them by rebuilding affected
indexes. Then everything works fine 1-2 weeks, and then again.
Hardware check didn't show anything wrong, but I'm still suspecting that
something is wrong with the disks (EMC, btw). It might be that some disk
block is corrupted. How can I check if it is always the same disk block
(from (1:10579653), slots 24 and 25)?
Interesting part is that I can use corrupted index by forcing the query
through hint, and it doesn't report any error.
Any help?
Thanks,
Peja
I'd run a DBCC CheckDB immediately.
"peja" <peja@.sympatico.ca> wrote in message
news:eJQ76UXrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have sql server 2000, sp3, and several production databases on this
> server. From time to time, my integrity check jobs fail on some of
> databases
> with the error message shown above (not always the same page/slot). This
> errors occur on index pages, and I resolve them by rebuilding affected
> indexes. Then everything works fine 1-2 weeks, and then again.
> Hardware check didn't show anything wrong, but I'm still suspecting that
> something is wrong with the disks (EMC, btw). It might be that some disk
> block is corrupted. How can I check if it is always the same disk block
> (from (1:10579653), slots 24 and 25)?
> Interesting part is that I can use corrupted index by forcing the query
> through hint, and it doesn't report any error.
> Any help?
> Thanks,
> Peja
>

"Keys out of order on page (1:10579653), slots 24 and 25..." error messages

Hi,
I have sql server 2000, sp3, and several production databases on this
server. From time to time, my integrity check jobs fail on some of databases
with the error message shown above (not always the same page/slot). This
errors occur on index pages, and I resolve them by rebuilding affected
indexes. Then everything works fine 1-2 weeks, and then again.
Hardware check didn't show anything wrong, but I'm still suspecting that
something is wrong with the disks (EMC, btw). It might be that some disk
block is corrupted. How can I check if it is always the same disk block
(from (1:10579653), slots 24 and 25)?
Interesting part is that I can use corrupted index by forcing the query
through hint, and it doesn't report any error.
Any help?
Thanks,
PejaI'd run a DBCC CheckDB immediately.
"peja" <peja@.sympatico.ca> wrote in message
news:eJQ76UXrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have sql server 2000, sp3, and several production databases on this
> server. From time to time, my integrity check jobs fail on some of
> databases
> with the error message shown above (not always the same page/slot). This
> errors occur on index pages, and I resolve them by rebuilding affected
> indexes. Then everything works fine 1-2 weeks, and then again.
> hardware check didn't show anything wrong, but I'm still suspecting that
> something is wrong with the disks (EMC, btw). It might be that some disk
> block is corrupted. How can I check if it is always the same disk block
> (from (1:10579653), slots 24 and 25)?
> Interesting part is that I can use corrupted index by forcing the query
> through hint, and it doesn't report any error.
> Any help?
> Thanks,
> Peja
>

"Keys out of order on page (1:10579653), slots 24 and 25..." error messages

Hi,
I have sql server 2000, sp3, and several production databases on this
server. From time to time, my integrity check jobs fail on some of databases
with the error message shown above (not always the same page/slot). This
errors occur on index pages, and I resolve them by rebuilding affected
indexes. Then everything works fine 1-2 weeks, and then again.
Hardware check didn't show anything wrong, but I'm still suspecting that
something is wrong with the disks (EMC, btw). It might be that some disk
block is corrupted. How can I check if it is always the same disk block
(from (1:10579653), slots 24 and 25)?
Interesting part is that I can use corrupted index by forcing the query
through hint, and it doesn't report any error.
Any help?
Thanks,
PejaI'd run a DBCC CheckDB immediately.
"peja" <peja@.sympatico.ca> wrote in message
news:eJQ76UXrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have sql server 2000, sp3, and several production databases on this
> server. From time to time, my integrity check jobs fail on some of
> databases
> with the error message shown above (not always the same page/slot). This
> errors occur on index pages, and I resolve them by rebuilding affected
> indexes. Then everything works fine 1-2 weeks, and then again.
> Hardware check didn't show anything wrong, but I'm still suspecting that
> something is wrong with the disks (EMC, btw). It might be that some disk
> block is corrupted. How can I check if it is always the same disk block
> (from (1:10579653), slots 24 and 25)?
> Interesting part is that I can use corrupted index by forcing the query
> through hint, and it doesn't report any error.
> Any help?
> Thanks,
> Peja
>