db mail query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andre - 09 Jul 2008 00:48 GMT

SQL 2005 (9.00.3054)

I recently built a replacement server for our old Prod server. The old
server also had SQL 2005 but was running SQL Mail. I'm only enabling DB
Mail on the new server but am running into some issues. I have migrated all
the sql mail params to the new db mail params but when I exec the sproc that
has db mail calls it gives the error "error formatting query. probably
invalid parameters". I have a var that I use as my query param and it's
been working in SQL Mail for at least 4 years, probably longer. I've read
several BOL articles on db mail and have yet to find anything that talks
about changes in the @query param. Can anyone tell me what differences
there could be in the @query param, or why I might be getting this error?

Thanks, Andre

Reply to this Message

Ekrem Önsoy - 09 Jul 2008 09:50 GMT

Hello Andre,

I don't know if you've already seen or not but I'll suggest you to take a
look at to the following link from BOL:
http://technet.microsoft.com/en-us/library/ms190307.aspx

Compare your Database Mail commands with the rules and examples in that
link.

Signature

Ekrem Önsoy

> SQL 2005 (9.00.3054)
>
[quoted text clipped - 11 lines]
>
> Thanks, Andre

Reply to this Message

Andre - 09 Jul 2008 18:53 GMT

Thanks for the reply. Yes, I've seen the article.

I think I have it fixed. I found 2 problems with the @query var in
sp_send_dbmail. The first issue I found is that it can't/won't execute a
command such as this: select * from ##Temp. When I have that command the
sproc just runs and runs and runs. I have to kill the sproc's process and
the process that is spawn from the @query command. I changed my sproc to
update a local table and read from that table, instead of the global temp
table, and it now works.

The other issue I found sure seems like a bug to me. This doesn't work in
@query: select * from dbo.MyTable. In order to get it to work I have to
drop the dbo., and then it works just fine. With the dbo. in the query I
get a message about an improperly formed query, or something like that. Why
wouldn't it be able to access a table with dbo.?

Andre

Reply to this Message

Ekrem Önsoy - 09 Jul 2008 19:48 GMT

I don't think your second problem is a bug or some problem of SQL Server.

If you've already seen that link that I gave you in my previous post, then
you should have seen the following example in that link:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder
WHERE DueDate > ''2004-04-30''
AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

Check out the @query part of the example above, you'll see that 3 part
naming is used in that example. "AdventureWorks.Production.WorkOrder".
DatabaseName+SchemaName+ObjectName. So, the "dbo" user in your case is
"Production" here. (I don't mean to insult, just trying to clarify it). So
Schema Name can be used without any problem.

I don't know your query and what SQL Server yields exactly but you may want
to put them in brackets, such as: [dbo].[your_table]

Or you may want to post your query for further investigation.

Signature

Ekrem Önsoy

> Thanks for the reply. Yes, I've seen the article.
>
[quoted text clipped - 13 lines]
>
> Andre

Reply to this Message

Andre - 29 Jul 2008 04:04 GMT

Ekrem,

Thanks for pointing out the 3 part naming; I had overlooked that. I've
changed all my queries and things appear to be working well.

I will say I find it odd that I have to use the 3 part naming, since I'm
using @execute_query_database. I would think that since I tell it which db
to execute the query in, I would then only have to define the table's owner.

Andre

Reply to this Message