• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

sp_OA oddity

The procedure works just fine as-is, but I decided I wanted to output the avail drive space, per drive, if a shortage is detected.  No changes except for @query, and now it's failing, with this error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

High-light the code by itself, it works just fine.  The email is delivered, with the drive specs in the body of the output.
        EXEC msdb..sp_send_dbmail
        @profile_name = 'profilemname',
        @recipients = 'email@domain.com',
        @blind_copy_recipients = 'me@email.com',
        @subject = 'servername Drive Space',
        @body = 'Available disk space is less than 50%.',
        @query = 'SET NOCOUNT ON; SELECT drive,freespace from dbo.drives SET NOCOUNT OFF;',
                @importance='High';

But, the procedure execution fails with the error posted above.  I honestly would rather not use sp_OA, but at this point, i don't really care.  I simply need to monitor the total and available drive space, such that I can alert if/when a certain percentage available is met.  

Quick fix is best, I've got to get around this quickly.  Any suggestions are really appreciated.





/****** Object:  StoredProcedure [dbo].[usp_DriveSpace]    Script Date: 03/14/2011 13:23:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_DriveSpace] 
AS
SET NOCOUNT ON;
/*
Allows for the regular monitoring of the available drive space.
EXEC dbo.usp_DriveSpace
*/
BEGIN
	DECLARE @hr int,
	        @fso int,
		@drive char(1),
			@odrive int,
			@TotalSize varchar(20),
			@MB Numeric; 
			
			SET @MB = 1048576
			
			TRUNCATE TABLE dbo.Drives
			INSERT dbo.Drives(drive,FreeSpace) 
			EXEC master.dbo.xp_fixeddrives 
			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 dbo.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 dbo.Drives 
						SET TotalSize=@TotalSize/@MB 
						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

				--UPDATE PctFree
				UPDATE dbo.Drives
				SET PctFree = CAST(FreeSpace AS FLOAT)/TotalSize*100

				IF EXISTS(SELECT 1 FROM dbo.Drives WHERE PctFree <=50)
				BEGIN
				  EXEC msdb..sp_send_dbmail 
    	                               @profile_name = 'profilemname',
	                               @recipients = 'email@domain.com',
	                               @blind_copy_recipients = 'me@email.com',
	                               @subject = 'servername Drive Space',
	                               @body = 'Available disk space is less than 50%.',
	                               @query = 'SET NOCOUNT ON; SELECT drive,freespace from dbo.drives SET NOCOUNT OFF;',
		                      @importance='High';
				END
				ELSE
				BEGIN
					SELECT Drive, CONVERT(CHAR(5),PctFree) + ' PctFree' FROM dbo.Drives
				END
					
				IF @@ERROR <> 0
				BEGIN
					RAISERROR ('Failure executing the usp_DriveSpace procedure.',16,1)
					RETURN
				END
END

SET NOCOUNT OFF;





GO

Open in new window

0
dbaSQL
Asked:
dbaSQL
  • 12
  • 10
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
In sp_send_db_mail @query is executed in separate session. Try pointing full path (database.schema.table)
0
 
dbaSQLAuthor Commented:
If you mean within the @query, I do.  I just genericized it for my post.  This is the actual @query:

        @query = 'SET NOCOUNT ON; SELECT drive,freespace from databasename.dbo.drives SET NOCOUNT OFF;',
0
 
Daniel_PLDB Expert/ArchitectCommented:
Have you tried to send any other query output by sp_send_db_mail?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
dbaSQLAuthor Commented:
I have numerous other procs which use @query just fine.  It is just this one that is problematic, and I believe it is because this one also has the sp_OA logic.
0
 
Daniel_PLDB Expert/ArchitectCommented:
It's definitely not an issue of sp_OA. I did some research on my server using your code - hope you don't mind ;) - and I did managed to send those query results.
I've identified problem in account which was executing procedure same trying to send email. Sending email without @query was working fine but with wasn't.
I took sp_send_db_mail part of your sp and tried to execute, then I've found some more errors with better explanation.

Please verify SQL Server can lookup AD for your account. It couldn't with mine, when I changed connectiuon context to another account (code below returned proper values) everything went ok and I've received email with free space :)
 
EXEC sys.xp_logininfo
@acctname = 'Domain\User'

Open in new window

0
 
dbaSQLAuthor Commented:
The procedure above is run by the Agent on a daily basis, under the SQL Server service account, which is an admin.
With @query, it fails whenever run, but the Agent or by myself.

I have checked xp_logininfo for both logins (myself and the service account), they both come back with privilege = admin.

What did you change?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Account which I'm using has results as:
name - Domain\username
type - user
priviledge - admin
mapped login name - same as name
permission path - BUILTIN\Administrators

0
 
dbaSQLAuthor Commented:
Mine is the same, except 'BUILTIN\Administrators' is not in permission path.  How is that changed?
0
 
dbaSQLAuthor Commented:
both logins are sysadmin
0
 
dbaSQLAuthor Commented:
By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations
0
 
dbaSQLAuthor Commented:
And both logins are in the local Administrators group on the server.  I'm not sure what needs to be changed.
0
 
Daniel_PLDB Expert/ArchitectCommented:
I tried to test it at home, I've got the same error. I did managed to send same email outside the proc but not using that proc. I'll try to find out why it was possible on that machine at my work.
0
 
Daniel_PLDB Expert/ArchitectCommented:
I wrapped there sp_start_job instead of sp_send_db_mail and that worked. You can try this temporarly. I'll try to find out how I did managed to send mail with query results.

MS isn't goin to fix that:
https://connect.microsoft.com/SQLServer/feedback/details/361954/sp-send-dbmail-fails-when-query-parameter-and-ole-automation-is-used-in-same-batch#
0
 
dbaSQLAuthor Commented:
I'm not sure what you mean by wrapping with the sp_start_job.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Sorry, I just took sp_send_db_mail part and created a job. In place of that part i put sp_start_job and it's working. But as I said, it's not exactly what you wanted have.
0
 
Daniel_PLDB Expert/ArchitectCommented:
I give up, I'm not able to reproduce the same today. I don't know, maybe yesterday I made mistake. I read about this behaviour and sugestion is to use CLR instead of sp_OA.
I managed to send email by taking sp_send_db_mail code part to job and start that job in that part of proc.
I'm sorry.
0
 
dbaSQLAuthor Commented:
No need to apologize, I appreciate your input thus far.

The existing procedure worked fine, until I added @query to it.  In the event that a shortage is detected, I want to see the space available on each drive.  I want that included in the email notification.

As I said before, I honestly would rather not use sp_OA, but at this point, i don't really care.  I simply need to monitor the total and available drive space, such that I can alert if/when a certain percentage available is met.   For example, if any of the drives on a server are at less than 50% available, I want my notification to include the amount of freespace left on the drive(s), like this:

drive    freespace  
-----     -----------
C            11708
D          115370




Any suggestions?
0
 
Daniel_PLDB Expert/ArchitectCommented:
0
 
dbaSQLAuthor Commented:
I've seen that, but to be honest, I'm unsure how to handle the C# compilation.  
0
 
Daniel_PLDB Expert/ArchitectCommented:
Ok, first you need c# compiler, you can use Jeff Key's Snippet Compiler:
http://www.sliver.com/dotnet/SnippetCompiler/

In snippet compiler paste code from wesite I posted:
 snippet compiler
Create .dll file.
Then like in instructions use code:
 
USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE

ALTER DATABASE <your db> SET TRUSTWORTHY ON
GO

USE <your db>
GO

CREATE ASSEMBLY DiskSpace 
FROM '<path to compiled assembly (dll file)'
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace
GO

Open in new window


Procedure is running great:
 ssms
The rest of the process you can handle by yourself :)
0
 
dbaSQLAuthor Commented:
thank you for all of your input, daniel. i had some problems on the C# compilation, so i worked around things a little differently.   see here:  http://connect.microsoft.com/SQLServer/feedback/details/361954/sp-send-dbmail-fails-when-query-parameter-and-ole-automation-is-used-in-same-batch
(same one you posted above)

i split sp_OA and sp_send_dbmail into different sessions, and now it works fine.  Basically, just added another procedure to read my drives table, and sp_send_dbmail based on the content within.  (you know, if a shortage is detected, an email is sent, which contains the drive specs, like this:


   servername available disk space
         
   drive freespace  
   ----- -----------
   C           11713
   D          115345


Another piece of code, but it works.  

Again, I appreciate all of your input, and I'd like to award you the points for your time.
0
 
dbaSQLAuthor Commented:
Not the solution, but very informative.  Thank you!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now