Query Results exceeding Max Number in 2005

  • Express12

    SSCoach

    Points: 18431

    Receiving the following error when issuing a QUERY in SQL 2005 Query Execution:

    The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

    When I looked at TOOLS\OPTIONS\QUERY RESULTS\RESULTS TO GRID, my non-XML data size is 65,535  (there is no XML stored in the output)

    How can I increase the Results displayed?

     

    BT
  • Yelena Varshal

    SSC-Dedicated

    Points: 34144

    Could you please post the relevant code here? It looks like the query tries to return many resul sets, it is not the big size of data, it is many select statements. Also, that happens if you configure query options to return results in text?


    Regards,
    Yelena Varshal

  • Express12

    SSCoach

    Points: 18431

    Here is the SQL (renders table sizes):

    --Declare/Open/Fetch/Close/Deallocate CURSOR sample

    declare @UserTableName nvarchar(40)

    declare UserTableSize cursor for

     select rtrim(name) from dbo.sysobjects

      where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

    open UserTableSize

    fetch next from UserTableSize into @UserTableName

    while @@fetch_status = 0

      begin

      exec sp_spaceused @UserTableName

      fetch next from UserTableSize into @UserTableName

      end

    close UserTableSize

    deallocate UserTableSize

    --(THX for any assistance)

     

    BT
  • Yelena Varshal

    SSC-Dedicated

    Points: 34144

    Hi,

    I tested your query on my database containg more then 600 tables and I got the same result when outputting to Grid. This is expected: you are running the cursor and each statement inside the while loop is executed as a separate request. I also did not find how to change the max number of returned resultsets in Grid, but the following works:

    Workarounds:

    1. You may specify Results to Text or results to File, both work fine, I tested.

    2. Tested the following with the inserting into temp table, works fine with Results to Grid. The results pane contains the resulting table, the Messages do say "Could not find table ##TempTable. Will try to resolve this table name later." But then it works fine

     

    CREATE

    TABLE ##TempTable

    (

    name nvarchar(128)

    ,

    rows char(11)

    ,

    reserved varchar(18)

    ,

    data varchar(18)

    ,

    index_size varchar(18)

    ,

    unused varchar(18)

    )

    declare

    @UserTableName nvarchar(40)

    declare

    UserTableSize cursor for

    select rtrim(name) from dbo.sysobjects

    where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

    open

    UserTableSize

    fetch

    next from UserTableSize into @UserTableName

    while

    @@fetch_status = 0

    begin

    Insert

    ##TempTable

    exec

    sp_spaceused @UserTableName

    fetch next from UserTableSize into @UserTableName

    end

    close

    UserTableSize

    deallocate

    UserTableSize

    Select

    * from ##TempTable

    Drop

    Table ##TempTable

     


    Regards,
    Yelena Varshal

  • Express12

    SSCoach

    Points: 18431

    thx Yelena -- much appreciated!

    BT
  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    I get this with a simple while loop, which I can't post as it's client code, the results display in text but not grid mode - I'd say as I'm on a rollup post sp2 this surely has to be a bug?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Zebedatious

    SSC Enthusiast

    Points: 173

    I don't need to see the result set. Surely there's a better way... Frankly ridiculous, the cursor is for searching or updating an individual row, why would I need to see every row.... An already slow method is required to be even slower for no just cause.

  • Loner

    SSC-Insane

    Points: 21279

    If you really want to see the result, set it to result to text.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87950

    You can avoid the cursor and the call to sp_spaceused using the following (stolen 😉 from the standard report 'Disk Usage by Table').

    ;WITH partitionStats

    AS (SELECT

    ps.object_id,

    SUM(CASE

    WHEN (ps.index_id < 2) THEN row_count

    ELSE 0

    END) AS [rows],

    SUM(ps.reserved_page_count) AS reserved,

    SUM(CASE

    WHEN (ps.index_id < 2)

    THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

    END) AS data,

    SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    GROUP BY ps.object_id)

    ,internalTables

    AS (SELECT

    it.parent_id,

    SUM(ps.reserved_page_count) AS reserved,

    SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

    WHERE it.internal_type IN (202,204)

    GROUP BY it.parent_id)

    SELECT

    (row_number() over(order by a3.name, a2.name))%2 as l1,

    a3.name AS [schemaname],

    a2.name AS [tablename],

    a1.rows as row_count,

    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

    a1.data * 8 AS data,

    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,

    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused

    FROM

    partitionStats As a1

    LEFT OUTER JOIN internalTables AS a4 ON (a4.parent_id = a1.object_id)

    INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'

    ORDER BY a3.name, a2.name

    Jeff

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    jeff.williams3188 (5/21/2008)


    You can avoid the cursor and the call to sp_spaceused using the following (stolen 😉 from the standard report 'Disk Usage by Table').

    ;WITH partitionStats

    AS (SELECT

    ps.object_id,

    SUM(CASE

    WHEN (ps.index_id < 2) THEN row_count

    ELSE 0

    END) AS [rows],

    SUM(ps.reserved_page_count) AS reserved,

    SUM(CASE

    WHEN (ps.index_id < 2)

    THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

    END) AS data,

    SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    GROUP BY ps.object_id)

    ,internalTables

    AS (SELECT

    it.parent_id,

    SUM(ps.reserved_page_count) AS reserved,

    SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

    WHERE it.internal_type IN (202,204)

    GROUP BY it.parent_id)

    SELECT

    (row_number() over(order by a3.name, a2.name))%2 as l1,

    a3.name AS [schemaname],

    a2.name AS [tablename],

    a1.rows as row_count,

    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

    a1.data * 8 AS data,

    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,

    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused

    FROM

    partitionStats As a1

    LEFT OUTER JOIN internalTables AS a4 ON (a4.parent_id = a1.object_id)

    INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'

    ORDER BY a3.name, a2.name

    Jeff

    Why steal when you can export?

    Run the Disk Usage by table, right-click, export....

    I know I know - it doesn't actually involve any T-SQL....I'm sad too:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87950

    Because then you can create your own and publish it to your own report server. Or, build a daily process to track growth by table.

    And better yet, if you publish to your own report server you can schedule the report to run every day and deliver the report in Excel format by email.

    Now, for the really lazy - you can download the actual report used in SSMS at:

    http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    jeff.williams3188 (5/21/2008)


    Because then you can create your own and publish it to your own report server. Or, build a daily process to track growth by table.

    And better yet, if you publish to your own report server you can schedule the report to run every day and deliver the report in Excel format by email.

    Now, for the really lazy - you can download the actual report used in SSMS at:

    http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx

    Now THERE's something I can go with. Pull the reports and use them as your own. Nice find there Jeff!

    All right, I'll rephrase - why steal the CODE when you can steal THE REPORT? (don't answer that.....:hehe::cool::w00t:;):P:D:))

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • RBarryYoung

    SSC Guru

    Points: 143327

    There is also this view which was included in the daily emailer just last month:

    /*

    vwTableInfo - Table Information View

    This view display space and storage information for every table in the database.

    Columns are:

    Schema

    Name

    Owner may be different from Schema)

    Columns count of the max number of columns ever used)

    HasClusIdx 1 if table has a clustered index, 0 otherwise

    RowCount

    IndexKB space used by the table's indexes

    DataKB space used by the table's data

    16-March-2008, RBarryYoung@gmail.com

    */

    --CREATE VIEW vwTableInfo

    -- AS

    SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]

    , tbl.Name

    , Coalesce((Select pr.name

    From sys.database_principals pr

    Where pr.principal_id = tbl.principal_id)

    , SCHEMA_NAME(tbl.schema_id)) as [Owner]

    , tbl.max_column_id_used as [Columns]

    , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]

    , Coalesce( ( select sum (spart.rows) from sys.partitions spart

    where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    where i.object_id = tbl.object_id )

    , 0.0) AS [IndexKB]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    where i.object_id = tbl.object_id)

    , 0.0) AS [DataKB]

    , tbl.create_date, tbl.modify_date

    FROM sys.tables AS tbl

    INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)

    INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

    or see my article at: http://www.sqlservercentral.com/scripts/tables/62545/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 13 posts - 1 through 13 (of 13 total)

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