Restore Database: 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?

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>

</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 19, 2012

Help With A Loop in SQL

No comments:

Post a Comment

Restore Database

Blog Archive

About Me