Error 169 rebulding Index.

Anyone ever got this error ?

Error 169. Severity 15. A column has been specified more
than once in the order by list. Columns in the order by
list must be unique.

I am trying to rebuild a clustered index on a table and it
generates this error. I copied the table to another server and rebuild the index without any problems, I then made a copy of the table with a different name on the same server and rebuilt the index without any hassles. The error doesn't make sense given the context. I figured it must be a data corruption issue but I cannot find the problem. Any ideas ?

YorkieQ1 Any ideas ?

A1 What results do you get running:

dbcc Checktable (ProblemTableName) WITH ALL_ERRORMSGS

-- in single user Mode

dbcc Checktable (ProblemTableName, REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS

-- And if that turns up nothing:

dbcc CheckDB (ProblemTableName) WITH ALL_ERRORMSGS

-- in single user Mode
dbcc CheckDB (ProblemTableName, REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS|||I have tried some of your suggestions, I cannot at this stage run the single user mode queries but so far nothing has turned up.|||RE: So far nothing has turned up.

You might want to try scheduling the rebuild of the clustered index for a time when no-one would be accessing it (or check to see if any locks related to user activity that involves the table may be causing the behavior).|||The current table that my live application is using is fine, it doesn't have the error however in the process of finding the error I renamed the dud table and made a copy that is now the live table. The system is working but I need to try and find the cause of the problem.

We have some maintenance applications that do cleanup on the table to keep the system running smooth and they were the ones that started to crash, somehow I though of rebuilding the index on the table and that is how I found the problem. If I copied the table and created identical indexes on the new table then I had no problems but if I tried to rebuild the indexes on the dud table I got the errors, I still do.

Problem is that you cannot rebuild the indexes on the table without having the error, in future I know what to look for if the same errors start occuring but rebuilding indexes daily can be a costly exercise on busy tables?|||RE: Problem is that you cannot rebuild the indexes on the table without having the error, in future I know what to look for if the same errors start occuring but rebuilding indexes daily can be a costly exercise on busy tables?

True enough, I'd be concerned about the cause, locus, timing and frequency as well. DBCC INDEXDEFRAG is fairly gentle and may be of help. (It might fail on the bad page and provide some additional clues as well. From there maybe use dbcc page to investigate further?)

Does this happen often?|||To my knowledge this was the first time we caught it, we have had strange hassles before but because of the obscurity of the error I hadn't looked there before. The database is a big one, 20GB, well it's big in my experience and we move about 250 000 records through this table everyday, so it can be very busy. I have the feeling that this is the first time this problem has occured because in the past the other issues have resolved themselves but this one could not be fixed by just rebuilding an index.

I will run the index defrag on the dud table and see what turns up. One more thing which is strange is that even when I ran a insert (into another table) and delete on this 'dud' table is came up with the error 169. Go figure?

Is it worth putting sp3 on windows2000 with SQL2000 sp2?|||Also, I forgot to mention, use dbcc showcontig with dbcc indexdefrag (to provide a measure that lets one get to know when defragging is probably going to be useful).

How fragmented is the EvilClusteredIndex if you run showcontig? For example

Use Pubs
Go

DBCC SHOWCONTIG (Authors, UPKCL_auidind) WITH TABLERESULTS, ALL_LEVELS

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS|||To my knowledge this was the first time we caught it, we have had strange hassles before but because of the obscurity of the error I hadn't looked there before. The database is a big one, 20GB, well it's big in my experience and we move about 250 000 records through this table everyday, so it can be very busy. I have the feeling that this is the first time this problem has occured because in the past the other issues have resolved themselves but this one could not be fixed by just rebuilding an index.

I will run the index defrag on the dud table and see what turns up. Good.

One more thing which is strange is that even when I ran a insert (into another table) and delete on this 'dud' table is came up with the error 169. Go figure? An interesting clue.
Is it worth putting sp3 on windows2000 with SQL2000 sp2?
The servers I run are there (some are also running the hotfix) but I don't think that will address your issue.|||Not defragmented at all, It might have been before I originally tried to recreate the index but I will never know now. When I run the query it scans 2054 pages and moves no pages. This is seriously one of those ghost in the machine stories, the one when you start a job as some who does not have a superstitious bone in your body and everyday you think evermore that the SQL box is after you. Superstitious Queerie Language. Still running tests.|||Not defragmented at all, It might have been before I originally tried to recreate the index but I will never know now.

I guess you droped i.e.(Drop EvilIndex or Alter Table drop index) and tried a fresh Create already?|||Had to. It was the last option I had. I still have the table with the evil index and it still produces the error when I try and rebuild the index so I can keep playing with it but fortunately I have another identical table without the error running live. Using DTS I copied the table and using query analyser I scripted the indexes on the old onto the new table, works like a dream, same data, no problem with index. Might be a bad spot on a disk? Wouldn't know where though because the filegroup is spread accross a few drives.|||I still have the table with the evil index and it still produces the error when I try and rebuild the index so I can keep playing with it but fortunately I have another identical table without the error running live.

Clearly corruption of some form, (though apparently undetectable by dbcc etc.), very unusual indeed; if you find out more please let us know.sql

ERROR : Host-file columns may be skipped only when copying into the Server

Hi All,

I need to make a query from the SQL Server 2K and save this data as an XML file. What i am trying to do is to execute a bcp utility as follows:

EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -Sservername -Usa -Ppwd -C RAW -r -t'

with bcp.fmt file formatted as

8.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS

The error is as follows:

Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Does anyone have an idea about the problem? Also other techniques to generate XML file are also welcomed:)

Thanks in advance,

Bahtiyar KARANLIKYour select statment only returns one column and you are using a format file that says bcp will be receiving four columns from the select statment.|||Hi,
How can my SQL statement can return only one column? When i execute the statement via Query Analyzer it displays the correct information?

Am i missing a point??

Bahtiyar KARANLIK|||If you execute
SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers
In QA you will get four columns, CustID, CustName,CustSurname and CustEmail.

If you execute
SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW
In QA you will have a resultset with one column. Within the one column you will have an XML string that contains the four above mentioned columns, but they will be contained in one column!

If you execute
select au_id,au_lname,phone,contract from pubs.dbo.authors

you should get something like:

au_id au_lname phone contract
---- ------------ ---- ---
172-32-1176 White 408 496-7223 1

If you execute
select au_id,au_lname,phone,contract from pubsdbo.authors for xml raw

you should get something like:

XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------
<row au_id="172-32-1176" au_lname="White" phone="408 496-7223" contract="1"/>

The first has four colums in the result set the second has only one.

Error : Difference of two datetime columns caused overflow at runtime.

At my job is a dts package that is failing in SQL 2005. I am not a SQL
expert. I am just trying to fix. I put the query in Query Analyzer
and get this error:

(4322 row(s) affected)

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.

I am just trying to understand what this means, what I should be
looking for and what could be wrong. Here is the query:

SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,
1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,
1970',
s.Warranty_Enddate) AS Support_EndDt,
DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) AS
Registration_Date, c.FirstName AS enduser_fname,
c.LastName AS enduser_lname, c.CompanyName AS
enduser_companyname, c.ContactEmail AS enduser_email, c.Address AS
enduser_address1,
c.Address2 AS enduser_address2, c.City AS
enduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,
c.WorkPhone AS enduser_phone,
c.Fax AS enduser_fax, d.DealerName AS
dealer_companyname, d.ContactFirstName AS dealer_fname,
d.ContactLastName AS dealer_name,
d.Address1 AS dealer_address, d.City AS
dealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,
d.ContactPhone AS dealer_phone,
d.ContactFax AS dealer_fax,
ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknown
IWP product') AS product_type, '' AS extra1,
'' AS extra2, '' AS extra3, '' AS extra4, '' AS
extra5, '' AS extra6, '' AS extra7
FROM tblInventory i full outer JOIN
tblDealers d ON i.DealerID = d.DealerID full
OUTER JOIN
tblSupport s ON i.InventoryID = s.InventoryID
full outer JOIN
tblCustomers c ON s.InventoryID = c.InventoryID
LEFT OUTER JOIN
tblProducts p ON LEFT(i.SerialNumber,
PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefix
WHERE i.SerialNumber <> ''

Any ideas would be greatly appreciated.geekwagon@.gmail.com (geekwagon@.gmail.com) writes:
> At my job is a dts package that is failing in SQL 2005. I am not a SQL
> expert. I am just trying to fix. I put the query in Query Analyzer
> and get this error:
>
> (4322 row(s) affected)
> Server: Msg 535, Level 16, State 1, Line 1
> Difference of two datetime columns caused overflow at runtime.
>
> I am just trying to understand what this means, what I should be
> looking for and what could be wrong. Here is the query:
>
> SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,
> 1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,
> 1970',
> s.Warranty_Enddate) AS Support_EndDt,
> DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) AS

One or of the rows has a value that is in 2038 or later in one of the
columns. My bets are on Warranty_Enddate and somehas put in 99991231
for an infinite warranty.

(2038-01-19 03:14:07.000 is the time when the number of seconds since
1970-01-01 exceeds the range of an un integer.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>From Transact-SQL Reference:

DATEDIFF produces an error if the result is out of range for integer
values. For milliseconds, the maximum number is 24 days, 20 hours, 31
minutes and 23.647 seconds. For seconds, the maximum number is 68 years.