DBMail Syntax Problem - 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

General SQL Server Forums

New to SQL Server Programming

DBMail Syntax Problem

Author

Topic

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2009-02-04 : 15:48:09

I am beginning to use DBmail for routine functions in SQL Server, but am having trouble with executing a specific query.

My code is as follows;

DECLARE @q varchar (254)
SET @q = 'select userid, login, failedlogins from dbo.users where userid = adminuser'
exec msdb.dbo.sp_send_dbmail @recipients = 'user@emailaddress.com'
, @subject = 'Test Subject'
, @body = 'Testing'
, @importance = 'High'
, @query = @q
, @attach_query_result_as_file = 1

But returns 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 478
Query execution failed: ?Msg 207, Level 16, State 1, Server TORSQL1, Line 1
Invalid column name 'adminuser'.

Now, I am running this from the master db and have also tried using fully-qualified object names, to no avail. Am I missing some issue with the syntax??

I should add we are using SQL2k5 64bit

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 15:51:52

quote:
Originally posted by munkdogg

I am beginning to use DBmail for routine functions in SQL Server, but am having trouble with executing a specific query.

My code is as follows;

DECLARE @q varchar (254)
SET @q = 'select userid, login, failedlogins from dbo.users where userid = 'adminuser'
exec msdb.dbo.sp_send_dbmail @recipients = 'user@emailaddress.com'
, @subject = 'Test Subject'
, @body = 'Testing'
, @importance = 'High'
, @query = @q
, @attach_query_result_as_file = 1

But returns 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 478
Query execution failed: ?Msg 207, Level 16, State 1, Server TORSQL1, Line 1
Invalid column name 'adminuser'.

Now, I am running this from the master db and have also tried using fully-qualified object names, to no avail. Am I missing some issue with the syntax??

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2009-02-04 : 15:58:16

quote:
Originally posted by sodeep

quote:
Originally posted by munkdogg

I am beginning to use DBmail for routine functions in SQL Server, but am having trouble with executing a specific query.

My code is as follows;

DECLARE @q varchar (254)
SET @q = 'select userid, login, failedlogins from dbo.users where userid = 'adminuser'
exec msdb.dbo.sp_send_dbmail @recipients = 'user@emailaddress.com'
, @subject = 'Test Subject'
, @body = 'Testing'
, @importance = 'High'
, @query = @q
, @attach_query_result_as_file = 1

But returns 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 478
Query execution failed: ?Msg 207, Level 16, State 1, Server TORSQL1, Line 1
Invalid column name 'adminuser'.

Now, I am running this from the master db and have also tried using fully-qualified object names, to no avail. Am I missing some issue with the syntax??

It would seem that pre-pending the userid with a single-quote (no close quote required?) produced a different error;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'adminuser'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string '
, @query = @q
, @attach_query_result_as_file = 1
'.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 16:06:17

quote:
Originally posted by munkdogg

quote:
Originally posted by sodeep

quote:
Originally posted by munkdogg

I am beginning to use DBmail for routine functions in SQL Server, but am having trouble with executing a specific query.

My code is as follows;

DECLARE @q varchar (254)
SET @q = 'select userid, login, failedlogins from dbo.users where userid = 'adminuser''
exec msdb.dbo.sp_send_dbmail @recipients = 'user@emailaddress.com'
, @subject = 'Test Subject'
, @body = 'Testing'
, @importance = 'High'
, @query = @q
, @attach_query_result_as_file = 1

But returns 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 478
Query execution failed: ?Msg 207, Level 16, State 1, Server TORSQL1, Line 1
Invalid column name 'adminuser'.

Now, I am running this from the master db and have also tried using fully-qualified object names, to no avail. Am I missing some issue with the syntax??

It would seem that pre-pending the userid with a single-quote (no close quote required?) produced a different error;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'adminuser'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string '
, @query = @q
, @attach_query_result_as_file = 1
'.

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-02-05 : 09:20:17

Thanks for your responses.

Adding the close quote now returns a different syntax error;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'adminuser'.

pyromelana
Starting Member

23 Posts

Posted - 2009-03-17 : 10:24:33

compare:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361954
http://thesource.ofallevil.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&tid=5bb82f32-96e1-4249-881d-d54412947a5c&cat=&lang=&cr=&sloc=&p=1
http://www.mydatabasesupport.com/forums/sqlserver-programming/393098-sp_send_dbmail-error-formatting-query-probably-invalid-parameter.html

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-04-27 : 16:08:50

Set you @q = 'select userid, login, failedlogins from dbo.users where userid = ''adminuser'' '

Because its quoted in the statement alone, you will need to double quote it.

Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources