?
Solved

Query needs to be modified

Posted on 2010-04-02
23
Medium Priority
?
669 Views
Last Modified: 2013-11-24
Hello friends, i had posted the same thing today but i want to let you know that when the sp's are created and when ran it runs fine in the test servers but when i run against Prod it runs successfully but doesn't email me and i have also attached the message that i get. basically this whole process will send information of the disk space when ran

Stored procedure 1-------------

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_diskspace]    Script Date: 04/02/2010 13:36:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[usp_diskspace]
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB,
 ServerName = replace( @query ,
 'master.dbo.xp_fixeddrives',''),
 FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =
 convert(varchar(20),
 Totalsize),
 @errorfreespace =freespace,
 @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),
 @errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server =
 '+@@servername+' <BR> Drive=' + @errordrive+' <BR>
 Percentage free=' +convert(varchar(2),
 @free)+'% <BR> TotalSpace ='+ @errortotalspace  +'MB <BR>  
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date + '<BR>'
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server =
 '+@@servername+'<BR>
 Drive=' + @errordrive+'<BR>
 Percentage free=' +convert(varchar(2),
 @free)+'% <BR> TotalSpace ='+ @errortotalspace  +'MB <BR>
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date + '<BR>'
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)


GO






stored procedure 2------------

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_send_cdosysmail]    Script Date: 04/02/2010 13:36:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE  PROCEDURE [dbo].[usp_send_cdosysmail]
@From varchar(500) ="",
@To varchar(500) ,
@Subject varchar(500)=" ",
@Body varchar(4000) =" ",
@SMTPServer varchar(25)=""
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg,
 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg



GO




now the email sending part--------------

declare @out varchar(8000)
declare @From varchar(200)
declare @To varchar(200)
declare @subject varchar(300)
declare @SMTPServer varchar(100)
declare @minimumspace int
set @minimumspace = 95
set @SMTPServer ='ibm.corp'
set @From ='abc@yahoo.com'
set @To ='abc@yahoo.com'
set @subject = 'Disk space available: '+@@servername
exec usp_diskspace @minimumspace,@out OUTPUT
if @OUT is not null or ltrim(rtrim(@OUT))<>''
begin
exec usp_send_cdosysmail @From ,@To ,@Subject,@OUT,@SMTPServer
end






when the last part (email sending part )  is ran i get


----Result
-2147211483

--Message
(1 row(s) affected)
 Source: ODSOLE Extended Procedure
 Description: sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]



any idea what's wrong here. Please help me as this needs to be implement soon.

0
Comment
Question by:espanolanthony
  • 15
  • 7
23 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 29505883
could it be todo with the fact that you have inconsistent definitions for the @body parameter?

in cdo_sysmail you define it as 4000 but i believe you possible send 8000?

how long is the message you are actually sending?
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29518467
it will send me all info about all the drives on the server
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29518913
the output will be somewhat like this



-----Server = IBM-CORE\SQL2008:
 Drive=C: Percentage free=83%
TotalSpace =272037MB
 FreeSpace =228369MB  

-----Server = IBM-CORE\SQL2008:
 Drive=D: Percentage free=90%
 TotalSpace =204798MB
 FreeSpace =186341MB
Date =Apr 1 2010 6:06:16:827AM
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 1

Author Comment

by:espanolanthony
ID: 29612593
any ideas friends?????
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29688792
You did not respond to Lowfatspread question.  But I would hazard to guess that you do not have CDO installed.  But then again, I have no idea why you are using CDO instead if DB Mail.
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29788128
i set the value to from 4000 to 8000 but same error and actually i don't know how do i use DB mail for this solutions. if i am not wrong at least i have to create the first two stored procedures right? then what would be the next step?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29789657
Setting up DB Mail is as easy as following a Wizard.  See here from BOL:
Database Mail Configuration Wizard
http://msdn.microsoft.com/en-us/library/ms175951.aspx

Here is another link that should help:
How to Set Up Database Mail on SQL Server 2005
http://www.sqlserverclub.com/articles/how-to-set-up-database-mail-on-sql-server-2005.aspx
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29789913
i already had set up DB mail, the question i had is how do i use DB to work with this scripts.
Let me explain what i understand.
first i create SP's -
[dbo].[usp_diskspace]  
[usp_send_cdosysmail]
right?
then what would be the next step?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29794544
If I have time tonight, I will re-write those Stored Procedures using Database Mail.
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29794761
perfect and thanks for the help. i really appreciate it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 29858309
Assuming that:
1.  usp_diskspace returns valid information and
2.  Database Mail is setup correctly

Then all you have to do is replace the Stored Procedure usp_send_cdosysmail with a call to msdb.dbo.sp_send_dbmail as in the following:
DECLARE @Out varchar(8000),
		@To varchar(200),
		@Subject varchar(300),
		@MinimumSpace int,
		@RC integer

SELECT  @minimumspace = 95,
        @To = 'abc@yahoo.com',
        @Subject = 'Disk space available: ' + @@servername
		
EXEC usp_DiskSpace @MinimumSpace, @Out OUTPUT

IF LEN(ISNULL(LTRIM(@OUT), '')) <> 0 
    BEGIN
--      EXEC usp_send_cdosysmail @From, @To, @Subject, @OUT, @SMTPServer
        EXEC @RC = msdb.dbo.sp_send_dbmail @recipients = @To, @subject = @Subject, @body_format = 'HTML', @body = @OUT

        IF @RC = 0 
            PRINT 'Success'
        ELSE 
            PRINT 'Failure'
    END

Open in new window

0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29909843
thanks brother, but when i run the above query in the test i get

Mail queued.
Success

and i receive mail in my email  so that very good.
so now i implemented in the prod server

says same thing in the output screen

Mail queued.
Success


but since more than 2 hours haven't received any mails. any idea why this delay?
Please help
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29913278
any yes how should i give multiple email addresses?
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29913498
if i do below then it doesn't work.  see below to see the two email adress that i used.

DECLARE @Out varchar(8000),
                @To varchar(200),
                @Subject varchar(300),
                @MinimumSpace int,
                @RC integer

SELECT  @minimumspace = 95,
        @To = 'xyz@yahoo.com', 'abc@gmail.com',
        @Subject = 'Disk space available is : ' + @@servername
               
EXEC usp_DiskSpace @MinimumSpace, @Out OUTPUT

IF LEN(ISNULL(LTRIM(@OUT), '')) <> 0
    BEGIN
        EXEC @RC = msdb.dbo.sp_send_dbmail @recipients = @To, @subject = @Subject, @body_format = 'HTML', @body = @OUT

        IF @RC = 0
            PRINT 'Success'
        ELSE
            PRINT 'Failure'
    END


gives error like--------------------
Msg 141, Level 15, State 1, Line 7
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.




0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29916548
i did like this and atleast the one with yahoo gets email but gmail does not

SELECT  @minimumspace = 95,
        @To = 'xyz@yahoo.com, abc@gmail.com',
        @Subject = 'Disk space available is : ' + @@servername
         

any idea why
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 29924336
or show me something that will take multiple emails please............
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29963995
Multiple emails need to be separated by semi colons ("punto y coma") and not comas.  So instead of:
@To = 'xyz@yahoo.com, abc@gmail.com',
It needs to be:
@To = 'xyz@yahoo.com;abc@gmail.com',
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29964579
Incidentally, you can find out what mails were successful and which are not (and the reason) by simply running this query (you need to belong to the sysadmin role):
USE MSDB

SELECT          TOP 100 *
FROM          sysmail_sentitems
ORDER BY          mailitem_id DESC

To get just the ones that failed and reason:
SELECT          TOP 100 *
FROM          sysmail_FailedItems f
          INNER JOIN dbo.sysmail_log l ON f.mailitem_id = l.mailitem_id
ORDER BY          f.sent_Date DESC
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 30013226
so i have set up a mail profile for xyz user name which is xyz @yahoo.com but have not set up a profile for abc which is abc@gmail.com so in this case can i include the gmail id by seperating the semicolumns?

and the queries that you gave me to check the status gives the following error:------
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysmail_FailedItems'.
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 30014984
OK atleast i can see the status i was looking in master
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 30015097
>>so i have set up a mail profile for xyz user name which is xyz @yahoo.com but have not set up a profile for abc which is abc@gmail.com so in this case can i include the gmail id by seperating the semicolumns?<<
No.  I did not say that.  You only need one profile.

>>and the queries that you gave me to check the status gives the following error:------<<
Isn't the error message obvious? You are either not a sysadmin or you are in the wrong database, you need to be in the MSDB database.  Did you not see the USE statement?
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 30016553
sorry for the confusion  thanks a lot.  for the help and answering my smallest questions.
0
 
LVL 1

Author Closing Comment

by:espanolanthony
ID: 31710433
Perfect and very easy to understand. thanks again.......
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question