DB Mail error: Error formatting query...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DB Mail error: Error formatting query...

Author

Topic

ninjadbskillznot
Starting Member

4 Posts

Posted - 2011-12-20 : 17:40:12

Hello,

I'm getting this error while trying to send DB mail with the sp_send_dbmail procedure. The error is something like this:

Executed as user: [domain]\[account]. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).  The step failed.

It seems that the error is tripped when the SQL Server Agent job that I created tries to execute the step that runs the procedure below (approx code; I changed some details for anonymity purposes).

I have checked on Google for ways around the error, but nothing has worked so far. At this point, I can't event tell whether the problem is a syntax error or a permissions error with the procedure or Agent setup.

I am able to run the same job on another server, but that server is not clustered, whereas the one throwing the error is. Not sure if that is helpful, but it is true. :-)

Also, as I said, just one job step produces an error, so some of the email procedure calls succeed, and other email sent from the server (maintenance plan results etc.) appear to be working fine. So DB Mail as a whole seems functional.

Has anyone dealt with this kind of error who can help me fix it?

Thanks in advance for any help.

- ninjadbskillznot



/****** Object:  StoredProcedure [dbo].[usp_SendDriveSpaceAlert]    Script Date: 12/20/2011 13:16:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_SendDriveSpaceAlert]
AS

/* 
Based on code here:
http://www.sqlservercentral.com/Forums/Topic113227-5-1.aspx
*/

DECLARE @LowSpaceDriveCount INT

DECLARE @MsgSubject VARCHAR(250)
DECLARE @MsgBody VARCHAR(500)

--70% full - send FYI (email)
SELECT @LowSpaceDriveCount = COUNT(*)
FROM dbadb.dbo.DiskSpaceLog
WHERE DATEDIFF(d,freespace_timestamp,GETDATE()) = 0
AND free_percent < 30

IF @LowSpaceDriveCount > 0
BEGIN
	SET @MsgSubject = 'Drive Free Space and Database Details for ' + CAST(@@servername AS VARCHAR(50))
	SET @MsgBody = 'Drive Free Space and Database Details for ' + CAST(@@servername AS VARCHAR(50))

	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Database Alert Profile',
	@subject = @MsgSubject,
	@body = @MsgBody,
	@recipients = 'me@emaildomain.com',
	@execute_query_database = 'dbadb',
	@attach_query_result_as_file = 1,
	@query = 'EXEC usp_output_drivespace 30'
END


--80% full - send warning (email + SMS)
SELECT @LowSpaceDriveCount = COUNT(*)
FROM dbadb.dbo.DiskSpaceLog
WHERE DATEDIFF(d,freespace_timestamp,GETDATE()) = 0
AND free_percent < 20

IF @LowSpaceDriveCount > 0
BEGIN
	SET @MsgBody = @@servername + ': One or more drives has less than 20% free space!'

	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Database Alert Profile',
	@subject = @MsgSubject,
	@body = @MsgBody,
	@recipients = 'me@emaildomain.com',
	@execute_query_database = 'dbadb',
	@attach_query_result_as_file = 1,
	@query = 'EXEC usp_output_drivespace 20'
	
	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Database Alert Profile',
	@body = @MsgBody,
	@recipients = '6465551212@textmsgemail.com',
	@subject = @MsgSubject,
	@execute_query_database = 'dbadb'
END

--90% full - send warning (email + SMS)
SELECT @LowSpaceDriveCount = COUNT(*)
FROM dbadb.dbo.DiskSpaceLog
WHERE DATEDIFF(d,freespace_timestamp,GETDATE()) = 0
AND free_percent < 10

IF @LowSpaceDriveCount > 0
BEGIN
	SET @MsgBody = @@servername + ': One or more drives has less than 10% free space!'
	SET @MsgSubject = 'Low disk space on database server: '  + @@servername + ''

	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Database Alert Profile',
	@subject = @MsgSubject,
	@body = @MsgBody,
	@recipients = 'me@emaildomain.com',
	@execute_query_database = 'dbadb',
	@attach_query_result_as_file = 1,
	@query = 'EXEC usp_output_drivespace 10'
	
	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Database Alert Profile',
	@body = @MsgBody,
	@subject = @MsgSubject,
	@recipients = '6465551212@textmsgemail.com',
	@execute_query_database = 'dbadb'
	
END

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources