Query needs to be modified

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.

LVL 1
espanolanthonyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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
espanolanthonyAuthor Commented:
it will send me all info about all the drives on the server
0
espanolanthonyAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

espanolanthonyAuthor Commented:
any ideas friends?????
0
Anthony PerkinsCommented:
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
espanolanthonyAuthor Commented:
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
Anthony PerkinsCommented:
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
espanolanthonyAuthor Commented:
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
Anthony PerkinsCommented:
If I have time tonight, I will re-write those Stored Procedures using Database Mail.
0
espanolanthonyAuthor Commented:
perfect and thanks for the help. i really appreciate it.
0
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
espanolanthonyAuthor Commented:
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
espanolanthonyAuthor Commented:
any yes how should i give multiple email addresses?
0
espanolanthonyAuthor Commented:
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
espanolanthonyAuthor Commented:
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
espanolanthonyAuthor Commented:
or show me something that will take multiple emails please............
0
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
espanolanthonyAuthor Commented:
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
espanolanthonyAuthor Commented:
OK atleast i can see the status i was looking in master
0
Anthony PerkinsCommented:
>>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
espanolanthonyAuthor Commented:
sorry for the confusion  thanks a lot.  for the help and answering my smallest questions.
0
espanolanthonyAuthor Commented:
Perfect and very easy to understand. thanks again.......
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.