Post reply

Column Order when pulled from Stored Procedure

  • June 7, 2012 at 3:13 am

    #262064

    All of my reports call a stored procedure and return a bunch of columns in a dataset. In some cases the order of these columns in Visual Studio matches the SP, in some cases it doesn't!

    It seems to be especially bad when you alter the SP and then 'refresh fields' in VS.

    Does anyone else encounter this behaviour? - With reports containing over 140 columns it can get very irritating. Is there a way to tell VS to use the SP column ordering over anything else?!

  • June 7, 2012 at 3:26 am

    #1498049

    Are you using a SELECT * in the stored procedure or are you SELECTing each column in a specific order?

    I.e.

    SELECT col1, col2, col3 ... colN FROM myTable

    vs.

    SELECT * FROM myTable

    If you are using SELECT * then the column order isn't set in stone, specifying the column order should return a data set with the correct columns.

    Have you tried using a different method i.e. OPENROWSET or (e.g. if using ASP with a language like VB.NET / VBScript) ADODB.RecordSet?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • June 7, 2012 at 3:37 am

    #1498051

    Specifically defining columns, at no point in any of the stored procedures would select * be used (well, except in cases of a temp table but this wouldn't be in the final select).

    I've not tried anything besides supplying the stored procedure during the report creation stage... I did look when refreshing fields to see if there were any options for the data set, I saw I could manually re order the columns but that's not what I'm really after!

    Just seems very odd, run the SP in management studio and it's as expected, refresh the fields in VS and the order is totally sodded up!

  • June 7, 2012 at 4:31 am

    #1498061

    Ah well, that's me out of ideas. I thought the problem might be with the way the query results were being passed to the reporting engine.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply