locked
ORDER BY not working with ADO or OLE RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am inserting rows into an Excel file and the ORDER BY is coming out wrong.

     

    When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should.

    But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...

     

    SET @sql =  '  INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@finalFile+';HDR=YES'',

    ''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')

    SELECT ID,priority,comments FROM OurTable WHERE orgId='+@orgId+' ORDER BY priority,ID'

     

    EXECUTE (@sql)

     

    Please help!! Thank you!

    Wednesday, August 29, 2007 9:26 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The order by is ordering them in string order.  The type coming in must be a string type.  Try converting them to integer to get the right order (e.g. ORDER BY convert(integer, priority.ID).  That should clear things up.

     

    Hope that helps,

     

    John

     

    Thursday, August 30, 2007 12:07 AM