Cursor in ExecuteSQL task "drops" some values
-
Please help me figure out why this script works everywhere except where it should.
Overview
Running a DTS Job accross multiple servers that checks database size. It's a modified sp_spaceused inside a cursor using sysdatabases. The script checks DB stats and inserts values per database into a table on another server.
Setup is:
1. DTS package create
2. Connections set to servers that need to be checked and the server containing the table for the stats.
3. ExecuteSQL Task created per target server
4. Save and Schedule package
Problem:
When running the script that reads the DB size stats in Query analyser, the select from sysdatabases used to populate the cursor works properly and values for each database on the target server are inserted into the stats table. The script also works properly when using "execute step" on a single step in DTS Designer. The script doesn't work when running multiple ExecuteSQL tasks in a DTS Package - It doesn't return db stats for some of the names that should be selected by the cursor on sysdatabases.
Script to check stats:
--declare @idint-- The object id of @objname.
--declare @typecharacter(2) -- The object type.
declare @servername varchar(20)
declare@pagesint-- Working variable for size calc.
declare @dbname sysname
declare @datapage dec(15,2)
declare @datapageMB dec(15,2)
declare @LogpageMB dec(15,2)
declare @logpage dec(15,2)
declare @unallocatedpage decimal(15,2)
declare @bytesperpagedec(15,2)
declare @pagesperMB dec(15,2)
DECLARE @DataPlusLogMB decimal(15,2)
DECLARE @Unallocated decimal(15,2)
declare @SQLstring nvarchar(4000)
-- Get the bytes per page
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'
SET @pagesperMB = 1048576 / @bytesperpage
set @servername = 'Put Server Name Here'
DECLARE SpaceUsed CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
OPEN SpaceUsed
FETCH NEXT FROM SpaceUsed
INTO @DBName
WHILE @@FETCH_STATUS = 0
begin
DBCC UPDATEUSAGE (@dbname) WITH NO_INFOMSGS
-- select @datapage = sum(convert(dec(15),size))
--from @dbname.dbo.sysfiles
--where (status & 64 = 0)
Set @SQLstring = 'select @datapage = sum(convert(dec(15),size)) '
Set @SQLstring = @SQLstring + 'from ' + @dbname + '.dbo.sysfiles '
Set @SQLstring = @SQLstring + 'where (status & 64 = 0)'
Print @SQLstring
exec sp_executesql @SQLstring, N'@datapage decimal(15,0) OUTPUT', @datapage OUTPUT
--select @dbname + ' Data Device: ' + cast(@datapage as varchar(20))
Print @dbname
--select @logpage = sum(convert(dec(15),size))
--from dbo.sysfiles
--where (status & 64 <> 0)
Set @SQLString = 'select @logpage = sum(convert(dec(15),size)) '
Set @SQLString = @SQLString + 'from ' + @dbname + '.dbo.sysfiles '
Set @SQLString = @SQLString + 'where (status & 64 <> 0)'
Print @SQLstring
exec sp_executesql @SQLstring, N'@logpage decimal(15,0) OUTPUT', @logpage OUTPUT
--select @dbname + ' Log Device: ' + cast(@logpage as varchar(20))
Set @SQLString = 'SELECT @unallocatedpage = SUM(CAST(reserved AS decimal(15,0))) '
Set @SQLString = @SQLString + 'FROM '+ @dbname + '.dbo.sysindexes '
Set @SQLString = @SQLString + 'WHERE indid IN (0, 1, 255) '
Print @SQLstring
exec sp_executesql @SQLstring, N'@unallocatedpage decimal(15,0) OUTPUT', @unallocatedpage OUTPUT
--select @dbname + ' Unallocated: ' + cast(@unallocatedpage as varchar(20))
SET @DataPlusLogMB = (@datapage + @logpage) / @pagesperMB
SET @Unallocated =(@datapage - @unallocatedpage) / @pagesperMB
SET @DataPageMB = @datapage / @pagesperMB
SET @LogPageMB = @logpage / @pagesperMB
-- Change Server name Here - refer below
If not Exists (select * from ServerName.DBName.dbo.DatabaseStats
where DatabaseName like @DBName
and datediff(day, recorddate, getdate())=0)
Insert Into ServerName.DBName.dbo.DatabaseStats(ServerName,DatabaseName, UsedSpace,FreeSpace,DataDevice, LogDevice, RecordDate)
Values(@servername, @dbname, @DataPlusLogMB,@Unallocated,@DataPageMB,@LogPageMB, getdate())
FETCH NEXT FROM SpaceUsed
INTO @DBName
END
CLOSE SpaceUsed
DEALLOCATE SpaceUsed
GOAnd the script to create the table for the stats
CREATE TABLE [dbo].[DatabaseStats] (
[StatsID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UsedSpace] [decimal](18, 2) NULL ,
[FreeSpace] [decimal](18, 2) NULL ,
[DataDevice] [decimal](18, 2) NULL ,
[LogDevice] [decimal](18, 2) NULL ,
[RecordDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DatabaseStats] WITH NOCHECK ADD
CONSTRAINT [PK_DatabaseStats] PRIMARY KEY CLUSTERED
(
[StatsID]
) ON [PRIMARY]
GOWhy, why? - it seems so simple but why doesn't it work.
G
-
Are there possibly any database names with invalid characters around them.... thus needing square brackets in the dynamic sql?
Not that I'm anti-cursor, but I do avoid them when I can, so I often use code such as the following if possible:
select [dbname]=db_name(), * into #sysfiles from sysfiles where 1=2
select [dbname]=db_name(), * into #sysindexes from sysindexes where 1=2
exec sp_MSforeachdb '
use
-- that's a square bracket, question mark, square bracket.
dbcc updateusage (0) with no_infomsgs
insert #sysfiles select ''?'', * from sysfiles
insert #sysindexes select ''?'', * from sysindexes
'
-- then process the #sysfiles and #sysindexes tables containing info for all DBsCheers,
- Mark
Edited by - mccork on 12/17/2003 03:31:23 AM
Cheers,
- Mark -
Thanks for response.
I understand why using sp_MSforeachdb would be a good option, So all I have to do is get all the detail out of the temp tables instead of running it per dbname in cursor?
I don't understand what this does though.
select [dbname]=db_name(), * into #sysfiles from sysfiles where 1=2
select [dbname]=db_name(), * into #sysindexes from sysindexes where 1=2G
-
quote:
I don't understand what this does though.select [dbname]=db_name(), * into #sysfiles from sysfiles where 1=2
select [dbname]=db_name(), * into #sysindexes from sysindexes where 1=2
They will create temporary tables (structure only) based on the original tables.
[dbname]=db_name() is necessary to create a new column with the correct datatype.
where 1=2 is a condition that can never be true and no data will be selected.
Therefore the result will be two temporary tables based on the originals with no data (structure only).
p.s sp_MSforeachdb uses a cursor, dynamic sql and a temp table
Edited by - davidburrows on 12/17/2003 06:30:21 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks. I'll put the whole thing together now and post the resulting script.
-
It works.
Thanks for all the input - Much appreciated.
I used the script on SQL 2000 on which @@Servername didn't seem to work.
For SQL 7.0, use @@servername instead of:
declare @servername varchar(30)
SELECT @servername = CONVERT(varchar(30), SERVERPROPERTY('servername'))One last thing... Any ideas on how to get this query to run through Firewall 1. Logs don't show dropping/rejecting of any packets along this path?
/*
Description: Checks size of each Db on server and inserts values into stats DB on linked server.
Date: 17/12/2003
*/
select [dbname]=db_name(), * into #sysfiles from sysfiles where 1=2
select [dbname]=db_name(), * into #sysindexes from sysindexes where 1=2
select ServerName,DatabaseName, UsedSpace,FreeSpace,DataDevice, LogDevice, RecordDate into #stats from ServerName.DatabaseName.dbo.DatabaseStats where 1=2
exec sp_MSforeachdb'
use dbcc updateusage (0) with no_infomsgs
insert #sysfiles select ''?'', * from sysfiles
insert #sysindexes select ''?'', * from sysindexes
declare @servername varchar(30)
declare@pagesint
declare @datapage dec(15,2)
declare @datapageMB dec(15,2)
declare @LogpageMB dec(15,2)
declare @logpage dec(15,2)
declare @unallocatedpage decimal(15,2)
declare @bytesperpagedec(15,2)
declare @pagesperMB dec(15,2)
DECLARE @DataPlusLogMB decimal(15,2)
DECLARE @Unallocated decimal(15,2)
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = ''E''
SET @pagesperMB = 1048576 / @bytesperpage
SELECT @servername = CONVERT(varchar(30), SERVERPROPERTY(''servername''))
select @datapage = sum(convert(dec(15),size))
from #sysfiles
where (status & 64 = 0)
and dbname like ''?''
select @logpage = sum(convert(dec(15),size))
from #sysfiles
where (status & 64 <> 0)
and dbname like ''?''
SELECT @unallocatedpage = SUM(CAST(reserved AS decimal(15,0)))
FROM #sysindexes
WHERE indid IN (0, 1, 255)
and dbname like ''?''
SET @DataPlusLogMB = (@datapage + @logpage) / @pagesperMB
SET @Unallocated =(@datapage - @unallocatedpage) / @pagesperMB
SET @DataPageMB = @datapage / @pagesperMB
SET @LogPageMB = @logpage / @pagesperMB
Insert Into #stats(ServerName,DatabaseName, UsedSpace,FreeSpace,DataDevice, LogDevice, RecordDate)
Values(@servername, ''?'', @DataPlusLogMB,@Unallocated,@DataPageMB,@LogPageMB, getdate())
'
declare @servername varchar(30)
SELECT @servername = CONVERT(varchar(30), SERVERPROPERTY('servername'))
If not Exists (select * from ServerName.DatabaseName.dbo.DatabaseStats
where Servername like @Servername
and datediff(day, recorddate, getdate())=0)
Insert Into ServerName.DatabaseName.dbo.DatabaseStats(ServerName,DatabaseName, UsedSpace,FreeSpace,DataDevice, LogDevice, RecordDate)
select * from #stats
--select* from #stats
drop table #sysfiles
drop table #sysindexes
drop table #stats
-- that's a square bracket, question mark, square bracket.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply