Restore Database: temp

Showing posts with label temp. Show all posts

Friday, March 23, 2012

Help with a SQL Query using temp tables

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.Basically all I did was move your manditory temp table out of the where clause and add ORs to the where so that if a table was empty you would evaluate to TRUE for that table. Maybe not the best solution but this should get you going.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
join ##tblTempAssetFilter [TempAssetFilter] on ActualWork.intAssetID = TempAssetFilter.intAssetID
WHERE (Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) or not exists(select * from ##tblTempRepairTypeFilter)
AND (Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) or not exists(select * from ##tblTempRepairFilter)
AND (Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter) or not exists(select * from ##tblTempRepairElementFilter)|||Paul,

Thank you for your reply, this has indeed fixed my problem. I am now returning a recordset with the desired results using my temporary tables.

Thanks again.|||Cool!

Here is a twist, if you do not need global temp tables I would switch to using table variables, there are some restrictions to using table variables but they can be much faster.

Also, if you can pre test the emptiness of your tables and store the reslts in a bit variable you can speed things up even more. As it is you are testing for empty temp tables each time you move to a new row.

Wednesday, March 21, 2012

help with a simple query

hi all, i was wondering if there is an alternate way of creating a simple
query that'' get the results below, without using temp tables or declaring
any cursors.
Table:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi
A Bob 2/2/2007 notes coke
A Bob 2/2/2007 notes sarsi
Result:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi,coke,sarsi
any suggestions will be appreciated
Thanks a million!No. Use a FAST_FORWARD cursor and table variable/temp table, process
the data on the client side of your code, or use a reporting product
such as Reporting Services or (ack) Crystal to do it.
-Dave
ChiWhiteSox wrote:
> hi all, i was wondering if there is an alternate way of creating a simple
> query that'' get the results below, without using temp tables or declaring
> any cursors.
> Table:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi
> A Bob 2/2/2007 notes coke
> A Bob 2/2/2007 notes sarsi
> Result:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi,coke,sarsi
> any suggestions will be appreciated
> Thanks a million!|||See: http://www.aspfaq.com/show.asp?id=2529
Razvansql

Monday, March 19, 2012

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 12, 2012

Help with ##table

Hi,

I am trying to drop a global temp table (##table) with sa rights and I get no joy. Does anyone have an idea what this could be caused by. The table is used by a sp which checks if the table is there and it drops it if its there.

Tnx for help.

Hi Kudah

Just tested creating,selecting from and dropping a global temp tabl;e with no issues. Your best bet is to post the code of the SP if possible and if not the ## table creation script and any supporting error messages.

Cheers

Charl

|||

Are you getting an error message? Can you post part of the code?
Normally this shouldn't be an issue.

|||create table ##a(name varchar(90))
select * from tempdb.##aa
if (select count(*) from tempdb..sysobjects where name='##a' ) > 0
print 'table is available'
else
print 'table dropped'
The above should give you proper results
-srikanthr
|||Quite possible that another user might be accessing the ##table since it is a global temporary table.

Help with ##table

Hi,

Tnx for help.

Hi Kudah

Cheers

Charl

|||

Are you getting an error message? Can you post part of the code?
Normally this shouldn't be an issue.

Friday, March 9, 2012

Help using dynamic queries in temp table

The dynamic sql is used for link server. Can someone help. Im getting an error

CREATEPROCEDURE GSCLink

(

@.LinkCompanynvarchar(50),@.Pageint,@.RecsPerPageint)

SET

NOCOUNTON

--Create temp table

CREATE

TABLE #TempTable

(

IDintIDENTITY,Companynvarchar(50),AcctIDint,IsActivebit)

INSERT

INTO #TempTable(Name, AccountID, Active)

--dynamic sql

DECLARE

@.sqlnvarchar(4000)

SET

@.sql='SELECT a.Name, a.AccountID, a.Active

FROM CRMSBALINK.'

+ @.LinkCompany+'.dbo.AccountTable a

LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

ON a.AccountID = b.oaAccountID

WHERE oaAccountID IS NULL

ORDER BY Name ASC'

EXEC

sp_executesql @.sql

--Find out the first and last record

DECLARE

@.FirstRecint

DECLARE

@.LastRecint

SELECT

@.FirstRec=(@.Page- 1)* @.RecsPerPage

SELECT

@.LastRec=(@.Page* @.RecsPerPage+ 1)

--Return the set of paged records, plus an indication of more records or not

SELECT

*,(SELECTCOUNT(*)FROM #TempTable TIWHERE TI.ID>= @.LastRec)AS MoreRecords

FROM

#TempTable

WHERE

ID> @.FirstRecAND ID< @.LastRec

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

Here is the problem:

INSERT

INTO #TempTable(Name, AccountID, Active)

It could be something like this:

INTO #TempTable values('Name', 'AccountID', 'Active')

or INTO #TempTable values(@.Name, @.AccountID, @.Active) (if those variables are defined)

or ...

Hope it helps.

|||Isnt the result of the dynamic sql put on the temp table automatically? If not... how can I capture the results of the dynamic sql?|||I got it working... Thanks anyway

Friday, March 23, 2012

Help with a SQL Query using temp tables

Wednesday, March 21, 2012

help with a simple query

Monday, March 19, 2012

Help With A Loop in SQL

Monday, March 12, 2012

Help with ##table

Help with ##table

Friday, March 9, 2012

Help using dynamic queries in temp table

Restore Database

Blog Archive

About Me