XP_Sendmail with @Query

[VitoMark]

When using XP_Sendmail with @Query I recieve this error message:
ODBC error 2812 (42000) Could not find stored procedure 'sp_setuserbylogin'
Any idea what's happening?

 

[Wyldcard9]

Are you in the Master database?

Here is sample code that I have working:

EXEC xp_sendmail @recipients = 'put-in-your-email-addrss',
@subject = 'SQL email test',
@message = 'Paul says HELLO from SQL'

 

[VitoMark]

Hello Wyldcard9,

This is wat I try tu use (f.i. from master database):
EXEC xp_sendmail @recipients = 'mark.jansen@vito.be',
@subject = 'SQL email test',
@query='select count(*) from sysobjects',
@message = 'Paul says HELLO from SQL'.
It's the @query-parameter that causes the error.

We have 6 SQL-server machines with SQL 2000, just 2 of them have this stange behaviour. I don't find any references to "sp_setuserbylogin". This sp seems not to exist in any SQL-server database.

Greetz,
Mark

 

[jms8222]

Check in master under extended stored procedures.

 

[Wyldcard9]

mark,

Is your 'select count(*) from sysobjects' query in the stored procedures of Master?

I remember having a similiar problem once. I solved it by creating a SQL server Agent job with two steps.

First step in Master database:

exec retrieve_full_cache (which is my called query). That query is in the Master DB.

The second step is the exec of xp_sendmail.

Also are you trying to get the results of your query? I think you might need the
'@attach_results =' clause.

Note you will get the results everytime even if it returns no rows(headers only). To get around that, make a small table, first query reads the table you are checking, and populates your small table. The second query then reads that small table, and if there are any rows then sends out an email.

I am not sure which way you are going with this.

 

[VitoMark]

Hello,
Thanks for your answer.
A little bit more information.
Whatever query I use, Whatever database I'm in, I only receive this error (sp_setuserbylogin not found) when I'm using the @query parameter on 2 of our 6 SQL server 2000 machines.
I don't have any idea where this stored procedure exists. I don't find any information about this sp.
We are now comparing the status of de service packs on de misbehavying SQL servers.
Greetings

 

[VitoMark]

Good news!
We solved the problem: upgrade SQL Server 2000 with service pack 3A.