August 3, 2016 at 10:48 am
I am trying to include inside a proc and then i wanted to further insert into a table for the purpose of keeping a table of growth trend being called from a agent scheduled job!
I am receiving error
Msg 8164, Level 16, State 1, Procedure usp_GetDiskstats, Line 11 [Batch Start Line 9]
An INSERT EXEC statement cannot be nested.
Tried this scripting out ->
PROC [dbo].[usp_GetDiskSpaceStats]
AS
SET NOCOUNT ON
CREATE TABLE #tmpFileList
(
[ID] int not null,
[Date] [DateTime] not Null,
[VolumeName] [varchar](max) NULL,
[Capacity(GB)] [float] NULL,
[FreeSpace(GB)] [float] NULL
)
INSERT INTO #tmpFileList
EXEC usp_GetDiskstats -- This i have being called see below tsql code
INSERT INTO DiskSpaceStats ([VolumeName], [Capacity GB], [FreeSpace GB])
SELECT [VolumeName], [Capacity GB], [FreeSpace GB]
FROM #tmpFileList
SELECT * FROM DiskSpaceStats
DROP TABLE #tmpdiskspace
DROP TABLE #tmpFileList
-- usp_GetDiskstats Script
declare @sql varchar(400)
set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
CREATE TABLE #tmpdiskspace
(line varchar(MAX))
insert #tmpdiskspace
EXEC xp_cmdshell @sql
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as VolumeName
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'Capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'FreeSpace(GB)'
from #tmpdiskspace
where line like '[A-Z][:]%'
order by VolumeName
DROP TABLE #tmpdiskspace
--Output Results
VolumeNameCapacity(GB)FreeSpace(GB)
C:\ 60 40
D:\ 30 30
E:\ 15 15
S:\ 12 0
T:\ 9 9
August 3, 2016 at 12:08 pm
August 4, 2016 at 4:06 am
Thank you Eirikur for your hint.
This is great but i need a solution for Mounted Volumes preferably using the PowerShell method.
Any other good resources you no of?
August 5, 2016 at 3:33 am
try
if object_id('sp_GetDiskSpaceStats') is not null
drop procedure dbo.sp_GetDiskSpaceStats;
go
create procedure dbo.sp_GetDiskSpaceStats
as
begin
set nocount on;
if object_id('tempdb..#tmpFileList') is not null
drop table #tmpFileList;
create table #tmpFileList
([ID] int identity (1, 1) not null
,[Date] [DATETIME] not null
,[VolumeName] [VARCHAR](max) null
,[Capacity(GB)] [FLOAT] null
,[FreeSpace(GB)] [FLOAT] null
);
--insert into #tmpFileList
exec dbo.usp_GetDiskstats; -- This i have being called see below tsql code
select [VolumeName]
,[Capacity(GB)]
,[FreeSpace(GB)]
from #tmpFileList;
drop table #tmpFileList;
end;
[/ccode]
if object_id('usp_GetDiskstats') is not null
drop procedure dbo.usp_GetDiskstats;
go
create procedure dbo.usp_GetDiskstats
as
begin
declare @sql varchar(400);
declare @localtable bit = 0;
if object_id('tempdb..#tmpFileList') is null
begin
set @localtable = 1;
create table #tmpFileList
([ID] int identity (1, 1) not null
,[Date] [DATETIME] not null
,[VolumeName] [VARCHAR](max) null
,[Capacity(GB)] [FLOAT] null
,[FreeSpace(GB)] [FLOAT] null
);
end;
set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
create table #tmpdiskspace
(line varchar(max)
);
insert #tmpdiskspace
exec xp_cmdshell @sql;
insert into #tmpFileList
([Date]
,VolumeName
,[Capacity(GB)]
,[FreeSpace(GB)]
)
select getdate()
,rtrim(ltrim(substring(line, 1, charindex('|', line) - 1))) as VolumeName
,round(cast(rtrim(ltrim(substring(line, charindex('|', line) + 1,
(charindex('%', line) - 1) - charindex('|', line)))) as float) / 1024, 0) as 'Capacity(GB)'
,round(cast(rtrim(ltrim(substring(line, charindex('%', line) + 1,
(charindex('*', line) - 1) - charindex('%', line)))) as float) / 1024, 0) as 'FreeSpace(GB)'
from #tmpdiskspace
where line like '[A-Z][:]%'
order by VolumeName;
drop table #tmpdiskspace;
if @localtable = 1
begin
select *
from #tmpFileList fl
drop table #tmpFileList
end;
end;
August 5, 2016 at 3:52 am
The first INSERT statement can't work, nested or not, because the ID and date columns in the temp table are not nullable and you haven't specified values for them. Why do you need to stage in a temp table - why not insert directly into the target table?
John
August 5, 2016 at 5:41 am
Thank you frederico this has given me some good results and different thinking which is awesome 😉
August 5, 2016 at 5:42 am
Hi John,
Yes in development and this now holds the data in a permanent tbl 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply