May 2, 2006 at 7:33 am
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?
May 2, 2006 at 9:10 am
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 Varsha
May 2, 2006 at 9:18 am
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)
May 2, 2006 at 1:12 pm
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 Varsha
May 2, 2006 at 2:35 pm
thx Yelena -- much appreciated!
June 20, 2007 at 5:57 am
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/
May 21, 2008 at 11:24 am
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.
May 21, 2008 at 12:09 pm
If you really want to see the result, set it to result to text.
May 21, 2008 at 2:08 pm
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
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2008 at 2:30 pm
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?
May 21, 2008 at 2:55 pm
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
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2008 at 3:01 pm
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?
May 21, 2008 at 7:09 pm
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
Ownermay be different from Schema)
Columnscount of the max number of columns ever used)
HasClusIdx1 if table has a clustered index, 0 otherwise
RowCount
IndexKBspace used by the table's indexes
DataKBspace 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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply