Solved

sp_OA oddity

Posted on 2011-03-14
22
912 Views
Last Modified: 2012-05-11
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
Comment
Question by:dbaSQL
  • 12
  • 10
22 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35131474
In sp_send_db_mail @query is executed in separate session. Try pointing full path (database.schema.table)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35131507
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35131700
Have you tried to send any other query output by sp_send_db_mail?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35131905
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35136584
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35138551
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138844
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35140429
Mine is the same, except 'BUILTIN\Administrators' is not in permission path.  How is that changed?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35140436
both logins are sysadmin
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35140456
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35140490
And both logins are in the local Administrators group on the server.  I'm not sure what needs to be changed.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35141196
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35141446
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35144064
I'm not sure what you mean by wrapping with the sp_start_job.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35144913
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35145956
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35147450
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35147632
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35150037
I've seen that, but to be honest, I'm unsure how to handle the C# compilation.  
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35151198
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35175951
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 35175957
Not the solution, but very informative.  Thank you!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now