SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP)


Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP)

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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
Attachments
Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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.
Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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.
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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!
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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/
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1596
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)


mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4164 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • SQL_ME_RICH
    SQL_ME_RICH
    SSC Eights!
    SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

    Group: General Forum Members
    Points: 930 Visits: 1596
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search