I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats
Showing posts with label loop. Show all posts
Showing posts with label loop. Show all posts
Monday, March 19, 2012
Help with a loop to collect database names and tables within those databases.
I am trying to create a loop that will go though all the databases and
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
WlyFragAmt INT, -- Maximum wly fragmentation to allow (5
is default)
WlyMaxRebuidAmt INT, -- Maximum index size durring the
wend to do a rebuild instead of defrag (100 Default)
LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, WlyFragAmt, Priority, LastRun etc...
How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
W
lyFragAmt INT, -- Maximum wly fragmentation to allow (5is default)
W
lyMaxRebuidAmt INT, -- Maximum index size durring thew
end to do a rebuild instead of defrag (100 Default)LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, W
lyFragAmt, Priority, LastRun etc...How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.
Help With A Loop in SQL
Here is my problem, I am trying to loop a query (its has been
precalculated into a temp table) until it get to the end of the temp
table. I was hoping that I could do it with a simple SET command, but
when I run the command. I keep getting the error
"The multi-part identifier "#Critical_Alert.Computer" could not be
bound." for each field. I think that the HTML which is required, is
throwing it off.
Any Ideas?
Thanks
-Matt-Is that TSQL code? Is see many things that shouldn't compile:
>
WHILE #Critical_Alert != EOL
What is EOL? And, #Critical_Alert is a table name.
It seems you are trying to loop a table, which isn't done in above way. If y
ou really want to loop,
procedurally, check out DECLARE CURSOR in Books Online for syntax and exampl
e. But a loop is slow.
and since white spaces are ignored in HTML (?), perhaps you can accomplish t
he same with a simple
SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1139938046.281934.290910@.o13g2000cwo.googlegroups.com...
>
Here is my problem, I am trying to loop a query (its has been
>
precalculated into a temp table) until it get to the end of the temp
>
table. I was hoping that I could do it with a simple SET command, but
>
when I run the command. I keep getting the error
>
"The multi-part identifier "#Critical_Alert.Computer" could not be
>
bound." for each field. I think that the HTML which is required, is
>
throwing it off.
>
>
Any Ideas?
>
>
>
>
Thanks
>
>
-Matt-
>
|||Thanks for reply.
This is TSQL, and its to send an HTML encoded message reporting disk
statistics.
This is the only section of the program that I am having issues with
everything else appears to work great. All I want to do is export what
is in the temp table directly into the html string. But I keep getting
"The multi-part identifier "#Critical_Alert.Computer" could not be
bound." to fix this, its probably something super obvious. II am
including the full script in this post.
Hope this all makes sense.
-Matt-|||I'm sorry, but your code is way off. You cannot refer to a column name if yo
u aren't "inside" a
SELECT statement. The SQL language doesn't work that way, quite simply. Does
the temp table contains
more than one row? Either you have to do it with a cursor. Or if order or th
e rows doesn't matter,
you can use a dirty trick:
DECLARE @.str varchar(8000)
SET @.str = ''
SELECT @.str = @.str + au_lname + ' ' + au_lname
FROM authors
PRINT @.str
If the temp table contains only one row, you just do:
SELECT @.v1 = c1 + @.v2 = c2
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1139940670.231117.98610@.g47g2000cwa.googlegroups.com...
>
Thanks for reply.
>
>
This is TSQL, and its to send an HTML encoded message reporting disk
>
statistics.
>
This is the only section of the program that I am having issues with
>
everything else appears to work great. All I want to do is export what
>
is in the temp table directly into the html string. But I keep getting
>
"The multi-part identifier "#Critical_Alert.Computer" could not be
>
bound." to fix this, its probably something super obvious. II am
>
including the full script in this post.
>
>
>
>
Hope this all makes sense.
>
>
-Matt-
>
|||The Temp Table contains multiple rows that I have presorted to remove
any extra data that should not be in there. I will try the Cursor, but
have little experience with it.|||Using your example i now get Msg 8115, Level 16, State 6, Line 52
Arithmetic overflow error converting varchar to data type numeric.
DECLARE @.tableHTML NVARCHAR(MAX);
DECLARE @.CriticalHTML NVARCHAR(MAX);
DECLARE @.Critical_Value INT
DECLARE @.GigaByte_Conv INT
SET @.GigaByte_Conv = 1024
SET @.Critical_Value = 10
CREATE TABLE #Critical_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
Date DATETIME,
)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM Server.Database.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-60,GETDATE()) AND Percentage <
@.Critical_Value
ORDER BY Computer, Drive
Select * From #Critical_Alert
/***********************
Declare Critical Header
***********************/
SET @.CriticalHTML =
N'<table style="text-align: left; width: 100%;" border="1"
cellpadding="2" cellspacing="0">
<tbody>
<tr style="font-family: Arial; font-weight: bold; color: rgb(255,
255, 255);">
<td style="text-align: center; background-color: rgb(255, 0, 0);"
colspan="6"rowspan="1"><font size="+3">DISKSPACE WARNING</td>
</tr>
<tr>
<td style="font-family: Arial; font-weight: bold;">Computer
Name</td>
<td style="font-family: Arial; font-weight: bold;">Drive</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Total Size</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Space Used</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Space Free</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
--IF @.@.ROWCOUNT > 0
--BEGIN
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td>' + Computer + '</td>
<td>' + Drive + '</td>
<td style="text-align: right;">' + DiskSpace + '</td>
<td style="text-align: right;">' + UsedSpace + '</td>
<td style="text-align: right;">' + FreeSpace + '</td>
<td style="text-align: right;">' + Percentage + '</td>
</tr>'
FROM #Critical_Alert
Print @.CriticalHTML
--END
/***********************
Declare Critical Footer
***********************/
SET @.CriticalHTML = @.CriticalHTML +
N' </tbody>
</table>'
SET @.tableHTML = @.CriticalHTML
Drop Table #Critical_Alert|||'+' is an overloaded operand in T-SQL; a plus sign can mean either
string concatenation OR addition of two numeric values. The engine
gets
when you try to concatenate a string to a numeric value.
Try:
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td>' + Computer + '</td>
<td>' + Drive + '</td>
<td style="text-align: right;">' + CONVERT(varchar(10),
DiskSpace) + '</td>
...
</tr>'
FROM #Critical_Alert
HTH,
Stu|||Here is the finalized script if anyone cares. It might not be as
elegant as I would like, but it works. If anyone wants to improve this
feel free to do so. I only ask is what ever improvements you may make
you post them back up on the web.
-Matt-
CREATE PROCEDURE [dbo].[sp_UD_Send_Disk_Reports]
AS
SET NOCOUNT ON
-- Declare variables
DECLARE @.tableHTML NVARCHAR(MAX);
DECLARE @.StyleHTML NVARCHAR(MAX);
DECLARE @.CriticalHTML NVARCHAR(MAX);
DECLARE @.WarningHTML NVARCHAR(MAX);
SET @.tableHTML = ''
SET @.StyleHTML = ''
SET @.CriticalHTML = ''
SET @.WarningHTML = ''
DECLARE @.Critical_Value INT
DECLARE @.Warning_Value INT
DECLARE @.GigaByte_Conv INT
SET @.GigaByte_Conv = 1024
SET @.Critical_Value = 3
SET @.Warning_Value = 10
DECLARE @.Critical_Flag INT
DECLARE @.Warning_Flag INT
SET @.Critical_Flag = 0
SET @.Warning_Flag = 0
/***********************
Declare Styles Header
***********************/
SELECT @.StyleHTML =
'<style>
<!--
body { margin: 0; font-family: Arial; font-size:10pt }
.Column_Header { font-size: 10pt; font-weight: bold;}
.Critical_Header { font-size: 20pt; font-weight: bold; text-align:
center; background-color: #FF0000 }
.Critical_Body { font-size: 10pt; background-color: #FFCCCC }
.Warning_Header { font-size: 20pt; font-weight: bold; text-align:
center; background-color: #FFFF00 }
.Warning_Body { font-size: 10pt; background-color: #FFFFCC }
.Good_Header { font-size: 20pt; font-weight: bold; text-align: center;
background-color: #00FF00 }
.Good_Body { font-size: 10pt; background-color: #CCFFCC }
-->
</style>'
SELECT @.tableHTML = @.tableHTML + @.StyleHTML --Append @.WarningHTML to
@.tableHTML
CREATE TABLE #Critical_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
)
INSERT INTO #Critical_Alert (Computer, Drive, DiskSpace, UsedSpace,
FreeSpace, Percentage)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM SERVER.DATABASE.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-15,GETDATE()) AND Percentage <=
@.Critical_Value
ORDER BY Computer, Drive
IF @.@.ROWCOUNT > 0
SET @.Critical_Flag = 1
IF @.Critical_Flag = 1 BEGIN
/***********************
Declare Critical Header
***********************/
SELECT @.CriticalHTML =
'<table style="width: 100%;" border="0" cellpadding="2"
cellspacing="0">
<tbody>
<tr>
<td class="Critical_Header" colspan="6"rowspan="1">DISK SPACE
CRITICAL</td>
</tr>
<tr>
<td class="Column_Header">Computer Name</td>
<td class="Column_Header"><p align="center">Drive</td>
<td class="Column_Header"><p>Total Size (GB)</td>
<td class="Column_Header"><p>Space Used (GB)</td>
<td class="Column_Header"><p>Space Free (GB)</td>
<td class="Column_Header"><p>% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td class="Critical_Body">' + Computer + '</td>
<td class="Critical_Body"><p align="center">' + Drive + '</td>
<td class="Critical_Body"><p>' + cast(DiskSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(UsedSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(FreeSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(Percentage AS
varchar(12)) + '% </td>
</tr>'
FROM #Critical_Alert
/***********************
Declare Critical Footer
***********************/
SELECT @.CriticalHTML = @.CriticalHTML +
'</tbody></table><br>'
SELECT @.tableHTML = @.tableHTML + @.CriticalHTML --Append @.CriticalHTML
to @.tableHTML
END
Drop Table #Critical_Alert
CREATE TABLE #Warning_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
)
INSERT INTO #Warning_Alert (Computer, Drive, DiskSpace, UsedSpace,
FreeSpace, Percentage)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM dbdev1.dbadmin.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-15,GETDATE()) AND Percentage <=
@.Warning_Value AND Percentage > @.Critical_Value
ORDER BY Computer, Drive
IF @.@.ROWCOUNT > 0
SET @.Warning_Flag = 1
IF @.Warning_Flag = 1 BEGIN
/***********************
Declare Warning Header
***********************/
SELECT @.WarningHTML =
'<table style="width: 100%;" border="0" cellpadding="2"
cellspacing="0">
<tbody>
<tr>
<td class="Warning_Header" colspan="6"rowspan="1">DISK SPACE
WARNING</td>
</tr>
<tr>
<td class="Column_Header">Computer Name</td>
<td class="Column_Header"><p align="center">Drive</td>
<td class="Column_Header"><p>Total Size (GB)</td>
<td class="Column_Header"><p>Space Used (GB)</td>
<td class="Column_Header"><p>Space Free (GB)</td>
<td class="Column_Header"><p>% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
SELECT @.WarningHTML = @.WarningHTML +
'<tr>
<td class="Warning_Body">' + Computer + '</td>
<td class="Warning_Body"><p align="center">' + Drive + '</td>
<td class="Warning_Body"><p>' + cast(DiskSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(UsedSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(FreeSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(Percentage AS
varchar(12)) + '% </td>
</tr>'
FROM #Warning_Alert
/***********************
Declare Warning Footer
***********************/
SELECT @.WarningHTML = @.WarningHTML +
'</tbody></table><br>'
SELECT @.tableHTML = @.tableHTML + @.WarningHTML --Append @.WarningHTML to
@.tableHTML
END
Drop Table #Warning_Alert
/***********************
Declare Compiled Footer
***********************/
SELECT @.tableHTML = @.tableHTML + 'Compiled Date: ' +
convert(varchar,getdate())
/***********************
Send E-Mmail
***********************/
IF @.Critical_Flag = 1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@.recipients=SOMEONE@.SOMEWHERE.COM',
@.subject = 'Disk Space Critical',
@.importance = 'High',
@.body = @.tableHTML,
@.body_format = 'HTML' ;
END
ELSE IF @.Warning_Flag = 1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@.recipients='SOMEONE@.SOMEWHERE.COM',
@.subject = 'Disk Space Warning',
@.importance = 'Normal',
@.body = @.tableHTML,
@.body_format = 'HTML' ;
END
precalculated into a temp table) until it get to the end of the temp
table. I was hoping that I could do it with a simple SET command, but
when I run the command. I keep getting the error
"The multi-part identifier "#Critical_Alert.Computer" could not be
bound." for each field. I think that the HTML which is required, is
throwing it off.
Any Ideas?
code:
BEGIN
WHILE #Critical_Alert != EOL
SET @.CriticalHTML = @.CriticalHTML +
'<
tr>
<
td>
''' + #Critical_Alert.Computer + '''<
/td>
<
td>
''' + #Critical_Alert.Drive + '''<
/td>
<
td style="text-align: right;
">
''' + #Critical_Alert.DiskSpace +
'''<
/td>
<
td style="text-align: right;
">
''' + #Critical_Alert.UsedSpace +
'''<
/td>
<
td style="text-align: right;
">
''' + #Critical_Alert.FreeSpace +
'''<
/td>
<
td style="text-align: right;
">
''' + #Critical_Alert.Percentage +
'''<
/td>
<
/tr>
'
END
Thanks
-Matt-Is that TSQL code? Is see many things that shouldn't compile:
>
WHILE #Critical_Alert != EOL
What is EOL? And, #Critical_Alert is a table name.
It seems you are trying to loop a table, which isn't done in above way. If y
ou really want to loop,
procedurally, check out DECLARE CURSOR in Books Online for syntax and exampl
e. But a loop is slow.
and since white spaces are ignored in HTML (?), perhaps you can accomplish t
he same with a simple
SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1139938046.281934.290910@.o13g2000cwo.googlegroups.com...
>
Here is my problem, I am trying to loop a query (its has been
>
precalculated into a temp table) until it get to the end of the temp
>
table. I was hoping that I could do it with a simple SET command, but
>
when I run the command. I keep getting the error
>
"The multi-part identifier "#Critical_Alert.Computer" could not be
>
bound." for each field. I think that the HTML which is required, is
>
throwing it off.
>
>
Any Ideas?
>
>
code:
>
>
BEGIN
>
WHILE #Critical_Alert != EOL
>
SET @.CriticalHTML = @.CriticalHTML +
>
'<
tr>
>
<
td>
''' + #Critical_Alert.Computer + '''<
/td>
>
<
td>
''' + #Critical_Alert.Drive + '''<
/td>
>
<
td style="text-align: right;
">
''' + #Critical_Alert.DiskSpace +
>
'''<
/td>
>
<
td style="text-align: right;
">
''' + #Critical_Alert.UsedSpace +
>
'''<
/td>
>
<
td style="text-align: right;
">
''' + #Critical_Alert.FreeSpace +
>
'''<
/td>
>
<
td style="text-align: right;
">
''' + #Critical_Alert.Percentage +
>
'''<
/td>
>
<
/tr>
'
>
END
>
>
>
>
Thanks
>
>
-Matt-
>
|||Thanks for reply.
This is TSQL, and its to send an HTML encoded message reporting disk
statistics.
This is the only section of the program that I am having issues with
everything else appears to work great. All I want to do is export what
is in the temp table directly into the html string. But I keep getting
"The multi-part identifier "#Critical_Alert.Computer" could not be
bound." to fix this, its probably something super obvious. II am
including the full script in this post.
code:
DECLARE @.tableHTML NVARCHAR(MAX);
DECLARE @.CriticalHTML NVARCHAR(MAX);
DECLARE @.Critical_Value INT
DECLARE @.GigaByte_Conv INT
SET @.GigaByte_Conv = 1024
SET @.Critical_Value = 10
CREATE TABLE #Critical_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
Date DATETIME,
)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM Server.Database.dbo.DiskMonitor_DriveSpace
WHERE Percentage <
@.Critical_Value
ORDER BY Computer, Drive
Select * From #Critical_Alert
/***********************
Declare Critical Header
***********************/
SET @.CriticalHTML =
N'<
table style="text-align: left;
width: 100%;
" border="1"
cellpadding="2" cellspacing="0">
<
tbody>
<
tr style="font-family: Arial;
font-weight: bold;
color: rgb(255,
255, 255);
">
<
td style="text-align: center;
background-color: rgb(255, 0, 0);
"
colspan="6"rowspan="1">
<
font size="+3">
DISKSPACE WARNING<
/font>
<
/td>
<
/tr>
<
tr>
<
td style="font-family: Arial;
font-weight: bold;
">
Computer
Name<
/td>
<
td style="font-family: Arial;
font-weight: bold;
">
Drive<
/td>
<
td style="text-align: right;
font-family: Arial;
font-weight:
bold;
">
Total Size<
/td>
<
td style="text-align: right;
font-family: Arial;
font-weight:
bold;
">
Space Used<
/td>
<
td style="text-align: right;
font-family: Arial;
font-weight:
bold;
">
Space Free<
/td>
<
td style="text-align: right;
font-family: Arial;
font-weight:
bold;
">
% Free<
/td>
<
/tr>
'
/***********************
Declare Critical Body
***********************/
IF @.@.ROWCOUNT >
0
BEGIN
SET @.CriticalHTML = @.CriticalHTML +
N'<
tr>
<
td>
' + #Critical_Alert.computer + '<
/td>
<
td>
' + #Critical_Alert.Drive + '<
/td>
<
td style="text-align: right;
">
' + #Critical_Alert.DiskSpace +
'<
/td>
<
td style="text-align: right;
">
' + #Critical_Alert.UsedSpace +
'<
/td>
<
td style="text-align: right;
">
' + #Critical_Alert.FreeSpace +
'<
/td>
<
td style="text-align: right;
">
' + #Critical_Alert.Percentage +
'<
/td>
<
/tr>
'
END
/***********************
Declare Critical Footer
***********************/
SET @.CriticalHTML = @.CriticalHTML +
N' <
/tbody>
<
/table>
'
SET @.tableHTML = @.CriticalHTML
Drop Table #Critical_Alert
EXEC msdb.dbo.sp_send_dbmail
@.recipients='Someone@.Somewhere.com',
@.subject = 'Test DiskSpace Usage',
@.body = @.tableHTML,
@.body_format = 'HTML' ;
Hope this all makes sense.
-Matt-|||I'm sorry, but your code is way off. You cannot refer to a column name if yo
u aren't "inside" a
SELECT statement. The SQL language doesn't work that way, quite simply. Does
the temp table contains
more than one row? Either you have to do it with a cursor. Or if order or th
e rows doesn't matter,
you can use a dirty trick:
DECLARE @.str varchar(8000)
SET @.str = ''
SELECT @.str = @.str + au_lname + ' ' + au_lname
FROM authors
PRINT @.str
If the temp table contains only one row, you just do:
SELECT @.v1 = c1 + @.v2 = c2
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1139940670.231117.98610@.g47g2000cwa.googlegroups.com...
>
Thanks for reply.
>
>
This is TSQL, and its to send an HTML encoded message reporting disk
>
statistics.
>
This is the only section of the program that I am having issues with
>
everything else appears to work great. All I want to do is export what
>
is in the temp table directly into the html string. But I keep getting
>
"The multi-part identifier "#Critical_Alert.Computer" could not be
>
bound." to fix this, its probably something super obvious. II am
>
including the full script in this post.
>
>
code:
>
>
DECLARE @.tableHTML NVARCHAR(MAX);
>
DECLARE @.CriticalHTML NVARCHAR(MAX);
>
DECLARE @.Critical_Value INT
>
DECLARE @.GigaByte_Conv INT
>
SET @.GigaByte_Conv = 1024
>
SET @.Critical_Value = 10
>
>
CREATE TABLE #Critical_Alert (
>
Computer VARCHAR (128),
>
Drive VARCHAR (2),
>
DiskSpace DECIMAL (28,2),
>
UsedSpace DECIMAL (28,2),
>
FreeSpace DECIMAL (28,2),
>
Percentage DECIMAL (10,2),
>
Date DATETIME,
>
)
>
SELECT Computer,
>
Drive,
>
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
>
>
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
>
>
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
>
Percentage
>
FROM Server.Database.dbo.DiskMonitor_DriveSpace
>
WHERE Percentage <
@.Critical_Value
>
ORDER BY Computer, Drive
>
>
Select * From #Critical_Alert
>
/***********************
>
Declare Critical Header
>
***********************/
>
SET @.CriticalHTML =
>
N'<
table style="text-align: left;
width: 100%;
" border="1"
>
cellpadding="2" cellspacing="0">
>
<
tbody>
>
<
tr style="font-family: Arial;
font-weight: bold;
color: rgb(255,
>
255, 255);
">
>
<
td style="text-align: center;
background-color: rgb(255, 0, 0);
"
>
colspan="6"rowspan="1">
<
font size="+3">
DISKSPACE WARNING<
/font>
<
/td>
>
<
/tr>
>
<
tr>
>
<
td style="font-family: Arial;
font-weight: bold;
">
Computer
>
Name<
/td>
>
<
td style="font-family: Arial;
font-weight: bold;
">
Drive<
/td>
>
<
td style="text-align: right;
font-family: Arial;
font-weight:
>
bold;
">
Total Size<
/td>
>
<
td style="text-align: right;
font-family: Arial;
font-weight:
>
bold;
">
Space Used<
/td>
>
<
td style="text-align: right;
font-family: Arial;
font-weight:
>
bold;
">
Space Free<
/td>
>
<
td style="text-align: right;
font-family: Arial;
font-weight:
>
bold;
">
% Free<
/td>
>
<
/tr>
'
>
>
/***********************
>
Declare Critical Body
>
***********************/
>
IF @.@.ROWCOUNT >
0
>
BEGIN
>
SET @.CriticalHTML = @.CriticalHTML +
>
N'<
tr>
>
<
td>
' + #Critical_Alert.computer + '<
/td>
>
<
td>
' + #Critical_Alert.Drive + '<
/td>
>
<
td style="text-align: right;
">
' + #Critical_Alert.DiskSpace +
>
'<
/td>
>
<
td style="text-align: right;
">
' + #Critical_Alert.UsedSpace +
>
'<
/td>
>
<
td style="text-align: right;
">
' + #Critical_Alert.FreeSpace +
>
'<
/td>
>
<
td style="text-align: right;
">
' + #Critical_Alert.Percentage +
>
'<
/td>
>
<
/tr>
'
>
>
END
>
/***********************
>
Declare Critical Footer
>
***********************/
>
SET @.CriticalHTML = @.CriticalHTML +
>
N' <
/tbody>
>
<
/table>
'
>
>
SET @.tableHTML = @.CriticalHTML
>
>
Drop Table #Critical_Alert
>
>
EXEC msdb.dbo.sp_send_dbmail
>
@.recipients='Someone@.Somewhere.com',
>
@.subject = 'Test DiskSpace Usage',
>
@.body = @.tableHTML,
>
@.body_format = 'HTML' ;
>
>
>
>
Hope this all makes sense.
>
>
-Matt-
>
|||The Temp Table contains multiple rows that I have presorted to remove
any extra data that should not be in there. I will try the Cursor, but
have little experience with it.|||Using your example i now get Msg 8115, Level 16, State 6, Line 52
Arithmetic overflow error converting varchar to data type numeric.
DECLARE @.tableHTML NVARCHAR(MAX);
DECLARE @.CriticalHTML NVARCHAR(MAX);
DECLARE @.Critical_Value INT
DECLARE @.GigaByte_Conv INT
SET @.GigaByte_Conv = 1024
SET @.Critical_Value = 10
CREATE TABLE #Critical_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
Date DATETIME,
)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM Server.Database.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-60,GETDATE()) AND Percentage <
@.Critical_Value
ORDER BY Computer, Drive
Select * From #Critical_Alert
/***********************
Declare Critical Header
***********************/
SET @.CriticalHTML =
N'<table style="text-align: left; width: 100%;" border="1"
cellpadding="2" cellspacing="0">
<tbody>
<tr style="font-family: Arial; font-weight: bold; color: rgb(255,
255, 255);">
<td style="text-align: center; background-color: rgb(255, 0, 0);"
colspan="6"rowspan="1"><font size="+3">DISKSPACE WARNING</td>
</tr>
<tr>
<td style="font-family: Arial; font-weight: bold;">Computer
Name</td>
<td style="font-family: Arial; font-weight: bold;">Drive</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Total Size</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Space Used</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">Space Free</td>
<td style="text-align: right; font-family: Arial; font-weight:
bold;">% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
--IF @.@.ROWCOUNT > 0
--BEGIN
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td>' + Computer + '</td>
<td>' + Drive + '</td>
<td style="text-align: right;">' + DiskSpace + '</td>
<td style="text-align: right;">' + UsedSpace + '</td>
<td style="text-align: right;">' + FreeSpace + '</td>
<td style="text-align: right;">' + Percentage + '</td>
</tr>'
FROM #Critical_Alert
Print @.CriticalHTML
--END
/***********************
Declare Critical Footer
***********************/
SET @.CriticalHTML = @.CriticalHTML +
N' </tbody>
</table>'
SET @.tableHTML = @.CriticalHTML
Drop Table #Critical_Alert|||'+' is an overloaded operand in T-SQL; a plus sign can mean either
string concatenation OR addition of two numeric values. The engine
gets
when you try to concatenate a string to a numeric value.Try:
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td>' + Computer + '</td>
<td>' + Drive + '</td>
<td style="text-align: right;">' + CONVERT(varchar(10),
DiskSpace) + '</td>
...
</tr>'
FROM #Critical_Alert
HTH,
Stu|||Here is the finalized script if anyone cares. It might not be as
elegant as I would like, but it works. If anyone wants to improve this
feel free to do so. I only ask is what ever improvements you may make
you post them back up on the web.
-Matt-
CREATE PROCEDURE [dbo].[sp_UD_Send_Disk_Reports]
AS
SET NOCOUNT ON
-- Declare variables
DECLARE @.tableHTML NVARCHAR(MAX);
DECLARE @.StyleHTML NVARCHAR(MAX);
DECLARE @.CriticalHTML NVARCHAR(MAX);
DECLARE @.WarningHTML NVARCHAR(MAX);
SET @.tableHTML = ''
SET @.StyleHTML = ''
SET @.CriticalHTML = ''
SET @.WarningHTML = ''
DECLARE @.Critical_Value INT
DECLARE @.Warning_Value INT
DECLARE @.GigaByte_Conv INT
SET @.GigaByte_Conv = 1024
SET @.Critical_Value = 3
SET @.Warning_Value = 10
DECLARE @.Critical_Flag INT
DECLARE @.Warning_Flag INT
SET @.Critical_Flag = 0
SET @.Warning_Flag = 0
/***********************
Declare Styles Header
***********************/
SELECT @.StyleHTML =
'<style>
<!--
body { margin: 0; font-family: Arial; font-size:10pt }
.Column_Header { font-size: 10pt; font-weight: bold;}
.Critical_Header { font-size: 20pt; font-weight: bold; text-align:
center; background-color: #FF0000 }
.Critical_Body { font-size: 10pt; background-color: #FFCCCC }
.Warning_Header { font-size: 20pt; font-weight: bold; text-align:
center; background-color: #FFFF00 }
.Warning_Body { font-size: 10pt; background-color: #FFFFCC }
.Good_Header { font-size: 20pt; font-weight: bold; text-align: center;
background-color: #00FF00 }
.Good_Body { font-size: 10pt; background-color: #CCFFCC }
-->
</style>'
SELECT @.tableHTML = @.tableHTML + @.StyleHTML --Append @.WarningHTML to
@.tableHTML
CREATE TABLE #Critical_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
)
INSERT INTO #Critical_Alert (Computer, Drive, DiskSpace, UsedSpace,
FreeSpace, Percentage)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM SERVER.DATABASE.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-15,GETDATE()) AND Percentage <=
@.Critical_Value
ORDER BY Computer, Drive
IF @.@.ROWCOUNT > 0
SET @.Critical_Flag = 1
IF @.Critical_Flag = 1 BEGIN
/***********************
Declare Critical Header
***********************/
SELECT @.CriticalHTML =
'<table style="width: 100%;" border="0" cellpadding="2"
cellspacing="0">
<tbody>
<tr>
<td class="Critical_Header" colspan="6"rowspan="1">DISK SPACE
CRITICAL</td>
</tr>
<tr>
<td class="Column_Header">Computer Name</td>
<td class="Column_Header"><p align="center">Drive</td>
<td class="Column_Header"><p>Total Size (GB)</td>
<td class="Column_Header"><p>Space Used (GB)</td>
<td class="Column_Header"><p>Space Free (GB)</td>
<td class="Column_Header"><p>% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
SELECT @.CriticalHTML = @.CriticalHTML +
'<tr>
<td class="Critical_Body">' + Computer + '</td>
<td class="Critical_Body"><p align="center">' + Drive + '</td>
<td class="Critical_Body"><p>' + cast(DiskSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(UsedSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(FreeSpace AS
varchar(12)) + '</td>
<td class="Critical_Body"><p>' + cast(Percentage AS
varchar(12)) + '% </td>
</tr>'
FROM #Critical_Alert
/***********************
Declare Critical Footer
***********************/
SELECT @.CriticalHTML = @.CriticalHTML +
'</tbody></table><br>'
SELECT @.tableHTML = @.tableHTML + @.CriticalHTML --Append @.CriticalHTML
to @.tableHTML
END
Drop Table #Critical_Alert
CREATE TABLE #Warning_Alert (
Computer VARCHAR (128),
Drive VARCHAR (2),
DiskSpace DECIMAL (28,2),
UsedSpace DECIMAL (28,2),
FreeSpace DECIMAL (28,2),
Percentage DECIMAL (10,2),
)
INSERT INTO #Warning_Alert (Computer, Drive, DiskSpace, UsedSpace,
FreeSpace, Percentage)
SELECT Computer,
Drive,
DiskSpace=convert(decimal(28,2),(DiskSiz
e/@.GigaByte_Conv)),
UsedSpace=convert(decimal(28,2),((DiskSi
ze-FreeSpace)/@.GigaByte_Conv)),
FreeSpace=convert(decimal(28,2),(FreeSpa
ce/@.GigaByte_Conv)),
Percentage
FROM dbdev1.dbadmin.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-15,GETDATE()) AND Percentage <=
@.Warning_Value AND Percentage > @.Critical_Value
ORDER BY Computer, Drive
IF @.@.ROWCOUNT > 0
SET @.Warning_Flag = 1
IF @.Warning_Flag = 1 BEGIN
/***********************
Declare Warning Header
***********************/
SELECT @.WarningHTML =
'<table style="width: 100%;" border="0" cellpadding="2"
cellspacing="0">
<tbody>
<tr>
<td class="Warning_Header" colspan="6"rowspan="1">DISK SPACE
WARNING</td>
</tr>
<tr>
<td class="Column_Header">Computer Name</td>
<td class="Column_Header"><p align="center">Drive</td>
<td class="Column_Header"><p>Total Size (GB)</td>
<td class="Column_Header"><p>Space Used (GB)</td>
<td class="Column_Header"><p>Space Free (GB)</td>
<td class="Column_Header"><p>% Free</td>
</tr>'
/***********************
Declare Critical Body
***********************/
SELECT @.WarningHTML = @.WarningHTML +
'<tr>
<td class="Warning_Body">' + Computer + '</td>
<td class="Warning_Body"><p align="center">' + Drive + '</td>
<td class="Warning_Body"><p>' + cast(DiskSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(UsedSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(FreeSpace AS
varchar(12)) + '</td>
<td class="Warning_Body"><p>' + cast(Percentage AS
varchar(12)) + '% </td>
</tr>'
FROM #Warning_Alert
/***********************
Declare Warning Footer
***********************/
SELECT @.WarningHTML = @.WarningHTML +
'</tbody></table><br>'
SELECT @.tableHTML = @.tableHTML + @.WarningHTML --Append @.WarningHTML to
@.tableHTML
END
Drop Table #Warning_Alert
/***********************
Declare Compiled Footer
***********************/
SELECT @.tableHTML = @.tableHTML + 'Compiled Date: ' +
convert(varchar,getdate())
/***********************
Send E-Mmail
***********************/
IF @.Critical_Flag = 1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@.recipients=SOMEONE@.SOMEWHERE.COM',
@.subject = 'Disk Space Critical',
@.importance = 'High',
@.body = @.tableHTML,
@.body_format = 'HTML' ;
END
ELSE IF @.Warning_Flag = 1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@.recipients='SOMEONE@.SOMEWHERE.COM',
@.subject = 'Disk Space Warning',
@.importance = 'Normal',
@.body = @.tableHTML,
@.body_format = 'HTML' ;
END
Monday, March 12, 2012
Help w/syntax select in a while loop
I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:
'AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop'
Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:
Also I'm trying really hard not to use temp tables in this example
Result from select statement below
sh_serial
----
53565
53597
sh_serial
----
53565
53597
sh_serial
----
sh_serial
----
53588
53597
Desired results:
sh_serial
----
53588
53597
53565
Syntax:
declare @.start int
select @.start = 580
declare @.stop int
select @.stop = 900
while @.start <= @.stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in
(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))
select @.start = @.start + 1
end
I'd appreciate any help. Thanks! :oInsert into a table variable and select distinct from that when you leave the loop?|||cause of business logic I cannot use temp tables or tables...
is there a way that I can put this into some sort of a derived table and then do the select distinct?|||I was gonna rewrite it...but I got scared...
SQL 2000?
DECLARE @.start int, @.stop int
SELECT @.start = 580, @.stop = 900
DECLARE @.x TABLE(shSerial int)
WHILE @.start <= @.stop
BEGIN
INSERT INTO @.x(shSerial)
SELECT sh_serial
FROM casemas, schilin
WHERE schi_shser = sh_serial
AND schi_itemno = '004852'
AND sh_serial <> 600000
AND sh_serial in (53565,53588,53597)
AND sh_serial in ( SELECT DISTINCT sh_serial
FROM casemas, schilin
WHERE (schi_shser = sh_serial)
AND (schi_itemno = '004852')
AND sh_serial in (53565,53588,53597)
AND @.start NOT IN ( SELECT sh_istart
FROM casemas
WHERE sh_istart in (SELECT sh_istop
FROM casemas
WHERE sh_serial in (53565,53588,53597)))
AND @.start between sh_istart and sh_istop
GROUP BY sh_serial
HAVING (sum(schi_qty) + 1 < 4 ))
SELECT @.start = @.start + 1
END
SELECT DISTINCT shSerial FROM @.x|||Thanks Brett,
and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time
'AND sh_serial in (53565,53588,53597)'
and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks! :)|||Store those values in a temp table (@. or #) and do WHERE EXISTS (...).
'AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop'
Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:
Also I'm trying really hard not to use temp tables in this example
Result from select statement below
sh_serial
----
53565
53597
sh_serial
----
53565
53597
sh_serial
----
sh_serial
----
53588
53597
Desired results:
sh_serial
----
53588
53597
53565
Syntax:
declare @.start int
select @.start = 580
declare @.stop int
select @.stop = 900
while @.start <= @.stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in
(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))
select @.start = @.start + 1
end
I'd appreciate any help. Thanks! :oInsert into a table variable and select distinct from that when you leave the loop?|||cause of business logic I cannot use temp tables or tables...
is there a way that I can put this into some sort of a derived table and then do the select distinct?|||I was gonna rewrite it...but I got scared...
SQL 2000?
DECLARE @.start int, @.stop int
SELECT @.start = 580, @.stop = 900
DECLARE @.x TABLE(shSerial int)
WHILE @.start <= @.stop
BEGIN
INSERT INTO @.x(shSerial)
SELECT sh_serial
FROM casemas, schilin
WHERE schi_shser = sh_serial
AND schi_itemno = '004852'
AND sh_serial <> 600000
AND sh_serial in (53565,53588,53597)
AND sh_serial in ( SELECT DISTINCT sh_serial
FROM casemas, schilin
WHERE (schi_shser = sh_serial)
AND (schi_itemno = '004852')
AND sh_serial in (53565,53588,53597)
AND @.start NOT IN ( SELECT sh_istart
FROM casemas
WHERE sh_istart in (SELECT sh_istop
FROM casemas
WHERE sh_serial in (53565,53588,53597)))
AND @.start between sh_istart and sh_istop
GROUP BY sh_serial
HAVING (sum(schi_qty) + 1 < 4 ))
SELECT @.start = @.start + 1
END
SELECT DISTINCT shSerial FROM @.x|||Thanks Brett,
and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time
'AND sh_serial in (53565,53588,53597)'
and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks! :)|||Store those values in a temp table (@. or #) and do WHERE EXISTS (...).
Subscribe to:
Posts (Atom)