Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP) Expand / Collapse
Author
Message
Posted Friday, June 13, 2014 10:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
It's not my program, but it's a really nice Stored Procedure that was written by Richard Ding back in 2008 called sp_sos. It basically is a nice way to display all the size calcs on your database tables (data and index). I have not been able to find a 2012 friendly version of it (due to it using a COMPUTE BY aggregate at the end of the PROC), but was hoping someone out here might have some idea on how to re-write it using the ROLL UP function or some other approach. Here is the section of code in question, but I'll attach a copy of the whole PROC as well...

    UPDATE ##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'

IF @Count = 1 -- when only 1 row, no need to sum up total
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
ELSE
BEGIN
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
END
END

RETURN (0)

GO

Thank you in advance!

SQL_ME_RICH


  Post Attachments 
URL_for_SQL_Script_download_Tip_on_Sp_SOS.doc (30 views, 67.00 KB)
Post #1580637
Posted Saturday, June 14, 2014 2:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Simply comment the compute part and replace with a SELECT statement

-- COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])

SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM dbo.##FO


Would work for all SQL Server versions.
Post #1580804
Posted Tuesday, June 17, 2014 10:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
Sachin - thank you for starting things out here with this logic. I appreciate it! I am still getting compile errors (though it will make the Stored Procedure now) in that the bracketed columns are showing to be invalid. When I try to run it after compiling it, it comes back with the following error:

Msg 50000, Level 16, State 1, Procedure sp_SOS, Line 259
No records were found macthcing your criteria.


There definitely are matching records, so I once again am - stuck.
Post #1582444
Posted Tuesday, June 17, 2014 10:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
The error is not because of the change.The error is thrown by the SP instead.Try searching for text "No records were found macthcing your criteria" in the SP.

matching in misspelled as "macthcing" in the SP.
Post #1582461
Posted Tuesday, June 17, 2014 11:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
Now I just get the same error with the word 'matching' correctly spelled. That is just the literal string that SQL calls if it does not find matching records.
Post #1582483
Posted Monday, July 28, 2014 10:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
Reviving this thread in the hope of finding a solution. I have used a Stored Procedure by Richard Ding for the past few years called sp_SOS. It works great on SQL 2008 R2 down, but from SQL 2012 (and I am guessing SQL 2014 as well) it will not work. The issue is with the SP using a COMPUTE statement in it's logic. I was advised to simply change that to a SELECT statement and that it would run, but I have had no such success. I have included the full code this time (was simply attached in an earlier thread) in hopes that someone might be able to get this to work. Here is the code (both the old COMPUTE line and new SELECT line toward the bottom of the script are included):

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_SOS] Script Date: 7/28/2014 9:54:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_SOS]
@DbName sysname = NULL,
@SchemaName sysname = NULL,
@ObjectName sysname = N'%',
@TopClause nvarchar(20) = NULL,
@ObjectType nvarchar(50) = NULL,
@ShowInternalTable nvarchar(3) = NULL,
@OrderBy nvarchar(100) = NULL,
@UpdateUsage bit = 0
AS

/*=================================================================================================

Author: Richard Ding

Created: Mar. 03, 2008

Modified: Mar. 17, 2008

Purpose: Manipulate object size calculation and display for SS 2000/2005/2008

Parameters:
@DbName: default is the current database
@SchemaName: default is null showing all schemas
@ObjectName: default is "%" including all objects in "LIKE" clause
@TopClause: default is null showing all objects. Can be "TOP N" or "TOP N PERCENT"
@ObjectType: default is "S", "U", "V", "SQ" and "IT". All objects that can be sized
@ShowInternalTable: default is "Yes", when listing IT, the Parent excludes it in size
@OrderBy: default is by object name, can be any size related column
@UpdateUsage: default is 0, meaning "do not run DBCC UPDATEUSAGE"

Note: SS 2000/2005/2008 portable using dynamic SQL to bypass validation error;
Use ISNULL to allow prefilled default parameter values;
Use "DBCC UPDATEUSAGE" with caution as it can hold up large databases;
Unicode compatible and case insensitive;

Sample codes:

EXEC dbo.sp_SOS;
EXEC dbo.sp_SOS 'AdventureWorks', NULL, '%', NULL, 'U', 'No', 'T', 1;
sp_SOS 'TRACE', NULL, NULL, Null, ' ,,, ,;SQ,; u ;;;,, v ,,;iT , ;', 'No', N'N', 0;
sp_SOS NULL, NULL, NULL, NULL, 'U', 'Yes', N'U', 1;
sp_SOS 'AdventureWorks', 'Person%', 'Contact%', NULL, 'U', 'no', 'N', 0;
sp_SOS 'AdventureWorks', NULL, NULL, N'Top 100 Percent', 'S', 'yes', N'N', 1;
sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT', 'yes', 'N', 1;
sp_SOS 'TRACE', NULL, 'Vw_DARS_217_overnight_activity_11142007', ' top 10 ', 'v', 'yes', 'N', 0;
sp_SOS 'AdventureWorks', NULL, 'xml%', ' top 10 ', null, 'yes', 'N', 1;
sp_SOS 'AdventureWorks2008', NULL, 'sales%', NULL, ' ,,; u ;;;,, v ', 'No', N'N', 1;
sp_SOS NULL, NULL, NULL, N'Top 100 Percent', ' ;;Q, U;V,', N'Y', 1;

=================================================================================================*/

SET NOCOUNT ON;

-- Input parameter validity checking
DECLARE @SELECT nvarchar(2500),
@WHERE_Schema nvarchar(200),
@WHERE_Object nvarchar(200),
@WHERE_Type nvarchar(200),
@WHERE_Final nvarchar(1000),
@ID int,
@Version nchar(2),
@String nvarchar(4000),
@Count bigint,
@GroupBy nvarchar(450);

IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y')
BEGIN
RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are:
''N'' --> Listing by object name
''R'' --> Listing by number of records
''T'' --> Listing by total size
''U'' --> Listing by used portion (excluding free space)
''I'' --> Listing by index size
''D'' --> Listing by data size
''F'' --> Listing by unused (free) space
''Y'' --> Listing by object type ', 16, 1)
RETURN (-1)
END;

-- Object Type Validation and Clean up
DECLARE @OTV nvarchar(10), @OTC nvarchar(10);
SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,
N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''),
N'V', N''), N'IT', N''), N'S', N'');
IF LEN(@OTV) <> 0 -- only allow comma, semi colon and space around S,U,V,SQ,IT
BEGIN
RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them,
separated by space, comma or semicolon.

S -> System table;
U -> User table;
V -> Indexed view;
SQ -> Service Queue;
IT -> Internal Table', 16, 1)
RETURN (-1)
END
ELSE -- passed validation
BEGIN
SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N''))
SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL
(@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S',
N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''')
END

---- common ----
SELECT @DbName = ISNULL(@DbName, DB_NAME()),
@Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1,
CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1),
@OrderBy = N'ORDER BY [' +
CASE ISNULL(@OrderBy, N'N')
WHEN N'N' THEN N'Object Name] ASC '
WHEN N'R' THEN N'Rows] DESC, [Object Name] ASC '
WHEN N'T' THEN N'Total(MB)] DESC, [Object Name] ASC '
WHEN N'U' THEN N'Used(MB)] DESC, [Object Name] ASC '
WHEN N'I' THEN N'Index(MB)] DESC, [Object Name] ASC '
WHEN N'D' THEN N'Data(MB)] DESC, [Object Name] ASC '
WHEN N'F' THEN N'Unused(MB)] DESC, [Object Name] ASC '
WHEN N'Y' THEN N'Type] ASC, [Object Name] ASC '
END;

---------------------- SS 2000 -----------------------------------
IF @Version = N'8'
BEGIN
SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') +
N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''Object Name'',
o.type AS ''Type'',
MAX(i.[rows]) AS ''Rows'',
CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'',
CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'',
CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'',
CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END)
* 8.000/1024)) AS ''Index(MB)'',
CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END
* 8.000/1024)) AS ''Data(MB)''
FROM dbo.sysindexes i WITH (NOLOCK)
JOIN dbo.sysobjects o WITH (NOLOCK)
ON i.id = o.id
WHERE i.name NOT LIKE ''_WA_Sys_%''
AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ',
-- SS 2000 calculation as below:
-- "reserved" = total size;
-- "dpages" = data used;
-- "used" = used portion (contains data and index);
-- text or image column: use "used" for data size
-- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused).
@WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%')
+ N''' AND o.type IN (' + @ObjectType + N') ',
@GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ',
@String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
END

------------------- ss 2k5 ------------------------------------------------------
IF @Version IN (N'9', N'10')
BEGIN
SELECT @String = N'
IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL
DROP TABLE dbo.##BO

CREATE TABLE dbo.##BO (
ID int identity,
DOI bigint null, -- Daughter Object Id
DON sysname null, -- Daughter Object Name
DSI int null, -- Daughter Schema Id
DSN sysname null, -- Daughter Schema Name
DOT varchar(10) null, -- Daughter Object Type
DFN sysname null, -- Daughter Full Name
POI bigint null, -- Parent Object Id
PON sysname null, -- Parent Object Name
PSI bigint null, -- Parent Schema Id
PSN sysname null, -- Parent Schema Name
POT varchar(10) null, -- Parent Object Type
PFN sysname null -- Parent Full Name
)

INSERT INTO dbo.##BO (DOI, DSI, DOT, POI)
SELECT object_id, schema_id, type, Parent_object_id
FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'')
USE ' + @DbName + N'
UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI END
UPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POI
UPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON,
PFN = schema_name(PSI)+ ''.'' + object_name(POI)
'
EXEC (@String)

SELECT
@WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END,
@SELECT = N'USE ' + @DbName + N'
SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') +
N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN
ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''Object Name'',
' + @WHERE_Type + N' AS ''Type'',
SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN
CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END
ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END
- CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN
ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END
- CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE 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 END) * 8.000/1024 AS ''Index(MB)'',
SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE 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 END) * 8.000/1024 AS ''Data(MB)''
FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t
ON ps.object_id = t.DOI
',
@ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'',
''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,
@WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN
@WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%')
+ ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' '
ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END, -- DON or PON
@WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema +
N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',
@GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN
THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''
SELECT @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
-- SELECT @String AS 'STRING'
END

----- common ------
IF OBJECT_ID(N'tempdb.dbo.##FO', N'U') IS NOT NULL
DROP TABLE dbo.##FO;

CREATE TABLE dbo.##FO (
ID int identity,
[Object Name] sysname,
[Type] varchar(2),
[Rows] bigint,
[Total(MB)] dec(10,3),
[-] nchar(1),
[Unused(MB)] dec(10,3),
[==] nchar(2),
[Used(MB)] dec(10,3),
[=] nchar(1),
[Index(MB)] dec(10,3),
[+] nchar(1),
[Data(MB)] dec(10,3) );

INSERT INTO ##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)])
EXEC (@String);

SELECT @Count = COUNT(*) FROM ##FO;

IF @Count = 0
BEGIN
RAISERROR (N'No records were found matching your criteria.', 16, 1)
RETURN (-1)
END
ELSE -- There're at least one records
BEGIN
-- Run DBCC UPDATEUSAGE to correct wrong values
IF ISNULL(@UpdateUsage, 0) = 1
BEGIN
SELECT @ObjectName = N'', @ID = 0
WHILE 1 = 1
BEGIN
SELECT TOP 1 @ObjectName = CASE WHEN [Object Name] LIKE N'%(%' THEN
SUBSTRING([Object Name], 1, CHARINDEX(N'(', [Object Name])-2) ELSE [Object Name] END
, @ID = ID FROM ##FO WHERE ID > @ID ORDER BY ID ASC
IF @@ROWCOUNT = 0
BREAK
PRINT N'==> DBCC UPDATEUSAGE (' + @DbName + N', ''' + @ObjectName + N''') WITH COUNT_ROWS'
DBCC UPDATEUSAGE (@DbName, @ObjectName) WITH COUNT_ROWS
PRINT N''
END

PRINT N''
TRUNCATE TABLE ##FO
INSERT INTO ##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],
[Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String)
END
ELSE
PRINT N'(Warning: Run "DBCC UPDATEUSAGE" on suspicious objects. It may incur overhead on big databases.)'
PRINT N''

UPDATE ##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'

IF @Count = 1 -- when only 1 row, no need to sum up total
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
ELSE
BEGIN
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
--COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM ##FO
END
END

RETURN (0)


Thanks to all of you for any help with this!
Post #1596974
Posted Monday, July 28, 2014 11:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
This is another approach I am looking at, but I am not having much success calling it from the sp_SOS SP.

https://sql2012computeby.codeplex.com/
Post #1596986
Posted Monday, July 28, 2014 12:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:57 PM
Points: 485, Visits: 1,374
Have now tried adding a GROUP BY 'Column' WITH ROLLUP, but still same error...

    IF @Count = 1  -- when only 1 row, no need to sum up total
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
ELSE
BEGIN
--EXECUTE dbo.proc_EmulateComputeQuery
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO ORDER BY ID ASC
--COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM ##FO
GROUP BY [Total(MB)] WITH ROLLUP
END
END

RETURN (0)

Post #1597018
Posted Monday, July 28, 2014 5:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:04 PM
Points: 1,817, Visits: 5,918
You will need to add support for SQL2012 by changing line 163 to this:

IF @Version IN (N'9', N'10', N'11')

I would change the final select to this:

        SELECT [ID],[Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM ##FO
union all
SELECT MAX([ID])+1,'==========','',SUM([Rows]),SUM([Total(MB)]),[-], SUM([Unused(MB)]),[==], SUM([Used(MB)]), [=], SUM([Index(MB)]), [+], SUM([Data(MB)])
FROM ##FO
GROUP BY [-],[==],[=],[+]
ORDER BY ID ASC

And finally, I would delete this procedure and rewrite it in a safe way, using parameterised queries and sp_executesql instead of concatenating strings and using EXEC(@String) <-- this provides no protection from SQL Injection attacks.



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1597116
    Posted Tuesday, July 29, 2014 10:30 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Friday, December 19, 2014 1:57 PM
    Points: 485, Visits: 1,374
    mister.magoo - Thank you! That finally got it!

    I will also look into how I can rewrite this with SQL Injection attacks in mind, and see if I cannot go with a more parameterized approach to safeguard against those possibilities.

    Post #1597394
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse