sp_send_mal formatting error - SQL Server Forums

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)

sp_send_mal formatting error

Author

Topic

opowell
Starting Member

12 Posts

Posted - 2009-10-13 : 15:01:34

SQL Server 2k5 SP4
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: ?Msg 208, Level 16, State 1, Server Blank Line 1

.

Syntax:
DECLARE @qry varchar(254)
SET @qry = 'SELECT TOP 1 [PostTime], [databaseUser], [Event],[TSQL] FROM dbo.DatabaseLog ORDER BY [DatabaseLogID]'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'my.name@companyName.com',
@body = 'AltDbaInventory <br>',
@body_format = 'HTML',
@subject = 'SQL SERVER DDL Audit',
@attach_query_result_as_file= 1,
@query = @qry,
@profile_name = 'DatabaseMailProfile';

This user as full rights to the tables, what i'm I missing?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 15:19:50

Maybe because the @query should be of type nvarchar(max)?

No, you're never too old to Yak'n'Roll if you're too young to die.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-10-13 : 15:21:03

Don't have access to BOL at the moment but I don't think there is a parameter called "@profile_name" - but I could be wrong.

Edit: My bad. after looking at BOL, this is a valid parameter.....

Terry

-- Procrastinate now!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 15:23:55

[code]sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @from_address = ] 'from_address' ]
[ , [ @reply_to = ] 'reply_to' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ][/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-14 : 10:37:53

Try specifying the database in which to run the query, either:


@execute_query_database = myDatabase,
or 
SET @qry = 'SELECT TOP 1 [PostTime], [databaseUser], [Event],[TSQL] FROM myDatabase.dbo.DatabaseLog ORDER BY [DatabaseLogID]'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources