Prev: Performance Problems with sp_cursorfetch on SQL 2000 - Need Help!!
Next: SP3 Install instructions
From: PatB on 19 Jan 2009 10:55
On SQL Server 2008 Enterprise 64-bit:

I am trying to attach a query to an email with DB Mail. It works in fine in
SSMS, but when I put the TSQL script into a job it fails with the following
error:

Sql Severity 16
Sql Message ID 22050
Executed as user: DOMAIN\SQLServerAgent. Error formatting query, probably
invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Script Start
--------------------------------------------------------------------
DECLARE @q varchar(2000)

SET @q = 'select top 10 * from tnt.dbo.mailings;'
--SET @q = 'select 1 TestFieldName;'

exec sp_send_dbmail @recipients = 'patb(a)emailaddress.com'
, @subject = 'Test Subject'
, @body = 'Testing'
, @importance = 'High'
, @query = @q
, @attach_query_result_as_file = 1
, @query_result_width = 400
Script End
----------------------------------------------------------------------

The problem seems to occur when ever I specify the @Query parameter of
sp_send_dbmail. If I remove the parameter the email is sent.

If I change the query to attach to not actually query a database see
commented line in the script above, it attaches the query as expected. Note
that I am executing the job (for testing) by right clicking the job and
selecting "Start Job..." I have not tried scheduling the job.

I'm not sure where to go with this. Both I and SQLServerAgent have sys
admin rights. It would seem like a permissions problem. I've seen lots of
references on the Internet for that error but very little help.

Does anyone have any thoughts on this?

--
Pat B
BCC Software, Inc
A BÖWE BELL + HOWELL COMPANY
From: Ekrem Önsoy on 20 Jan 2009 09:37
Hi PatB,

I tried your code (copy\paste) in my test environment and it works in Query
Editor and in a Job. I don't get the error you mentioned.

The only thing I changed is the query. I used an existing test table in a
test database. I just ask you to focus on that table and query because the
problem must be there.

--
Ekrem Önsoy



"PatB" <PatB(a)community.nospam> wrote in message
news:AC890C37-44DF-4B3D-A371-956FA3F47E06(a)microsoft.com...
> On SQL Server 2008 Enterprise 64-bit:
>
> I am trying to attach a query to an email with DB Mail. It works in fine
> in
> SSMS, but when I put the TSQL script into a job it fails with the
> following
> error:
>
> Sql Severity 16
> Sql Message ID 22050
> Executed as user: DOMAIN\SQLServerAgent. Error formatting query, probably
> invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
>
> Script Start
> --------------------------------------------------------------------
> DECLARE @q varchar(2000)
>
> SET @q = 'select top 10 * from tnt.dbo.mailings;'
> --SET @q = 'select 1 TestFieldName;'
>
> exec sp_send_dbmail @recipients = 'patb(a)emailaddress.com'
> , @subject = 'Test Subject'
> , @body = 'Testing'
> , @importance = 'High'
> , @query = @q
> , @attach_query_result_as_file = 1
> , @query_result_width = 400
> Script End
> ----------------------------------------------------------------------
>
> The problem seems to occur when ever I specify the @Query parameter of
> sp_send_dbmail. If I remove the parameter the email is sent.
>
> If I change the query to attach to not actually query a database see
> commented line in the script above, it attaches the query as expected.
> Note
> that I am executing the job (for testing) by right clicking the job and
> selecting "Start Job..." I have not tried scheduling the job.
>
> I'm not sure where to go with this. Both I and SQLServerAgent have sys
> admin rights. It would seem like a permissions problem. I've seen lots
> of
> references on the Internet for that error but very little help.
>
> Does anyone have any thoughts on this?
>
> --
> Pat B
> BCC Software, Inc
> A BÖWE BELL + HOWELL COMPANY

 | 
Pages: 1
Prev: Performance Problems with sp_cursorfetch on SQL 2000 - Need Help!!
Next: SP3 Install instructions