Use master

select name,filename from sysdatabases

Starnge..Its working fine in 2000 when i tried. can you try running the query again?

The code i tried is

Declare @NAMEX varchar(100)

Set @NAMEX = 'S'

Select @NAMEX = @NAMEX +...

Which version of SQL you are using?  I just tried your query now in SQL 2000 and working fine.

Create a temp table with columns:

Category , Apple, banana  , peas

Insert distinct categories in to the temp table.

then loop thru the original table to update temp table inside the loop.

eg:

If Diagnosis='Apple'

update...

Here goes the script for you!!

--- I have created TestDuplicates  table for testing the script...

create table TestDuplicates (Col1 int,Col2 varchar(100),Col3 int)

Insert into TestDuplicates values(1,'a',1)

Insert into TestDuplicates values(2,'b',2)

Insert into TestDuplicates values(2,'b',2)

Insert...

Here goes the Full Script for you..

-- I have created TestInv for testing the script..you have to use the table you are refering in the question.

create table TestInv (itemcode int,itemname...

You can use a cursor to select invoice no,itemcode with group by and having count of item invoiced  >1. If the criteria satisfies the display like invoice no,itemcode,1 (You can...

Create another table with same columns + 1 additional auto increment column.

Now after transfering the data in to the new table, you can get the max of identity column for...

Thanks sergiy...

Sorry David.. I was referring to the query you sent.

SELECT

 Filename

FROM

  (

 SELECT LTRIM(RTRIM(SUBSTRING(DIRINFO,39,LEN(DIRINFO)-38))) FileName, DirInfo FROM Reports

 WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) <> 0 AND SUBSTRING(DIRINFO,25,5)<>'<DIR>'

  )

WHERE

 SUBSTRING(DirInfo, 1, 10)< @dtDiff

David,

Even the Second query you sent throwing the same error. Thanks a lot for your help. for now, I am going with work around as i said in my last...

David,

AS workaround i am changing the code to make it work as below. Now this is owrking in 2005.

But let me know if any other better...

David,

Data is the same in both the cases, i mean in 2000 and 2005. and when i compare with

"SUBSTRING(DirInfo, 1, 10)< @dtDiff"  this is failing in 2005. 

Hope the following works. I have Hard coded @CID as set @CID ='0001,0004' inside proc which can be removed in your original proc.

---------------------------------------

create

Ben, Thanks for the reply. i can use cursor to loop thru all DB's.But all i want the info in the format specified above. usage of temp tables is permitted....