ORDER BY not working with ADO or OLE RRS feed

  • Question

  • 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


  • 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,




    Thursday, August 30, 2007 12:07 AM