I'm trying to write a scheduled job that is meant to run this sql statement which looks for null's in a table: i.e select * from bpdata where prdkey is null
and if it returns any records i want an email fired off to a particular email address.

Whats the best way of doing this in sql server 2000?

You could use the standard SQL scheduler to call a stored procedure that uses xp_sendmail. That way, you have more control over the circumstances that trigger a mail. SQL Mail needs to be set up to do this, but I'm assuming that it already is if you're talking about firing emails from SQL Server.

i think the big question really is,
"is it pronounced S Q L or sequel"

S Q L dammit

stupid f***ers that say sequel

sequel if you're talking about the language, SQL if you're talking about the server. But that's just me.

SQL2000's email is pretty homo. It requires outlook client to be installed on the sql server and the sql server agent service account to have a profile

In my opinion, if you really do have to email from sql server use this other guys implementation which uses smtp rather than exchange and outlook. XPSMTP Mail. I'm told the guy that wrote it is involved with the implementation of mail in SQL2005 which is significantly better.

Another way if you have a bit more infrastructure at your disposal such as MOM or other event managers, is to have the sql job log a windows event (xp_logevent) with a specific message 'PRDKEY IS NULL FOUND'. When MOM finds that event it knows to send a file from a particular location to a particular email address/group.

You would produce the file by instead of using a tsql job step, use a 'operating system command' job step and go like this: osql -Q"select * from [mydatabase].[itsowner].bpdata where prdkey is null" -oD:\Output\MyOutputFile.txt -S[myserverinstance] -E

That assumes you have a D drive, sql server agent service account can write to D:\output and that you are using windows authentication

You would need to get a little bit more advanced and probably wrap the osql command in a stored procedure that does the xp_logevent call if there are rows that exist. So your -Q would be -Q"exec [mydatabase].dbo.spnMyProc"

and your proc would be something like:

create procedure spnMyProc
As
IF EXISTS (SELECT 1 FROM bpdata where prdkey is null) BEGIN
select * from bpdata where prdkey is null
xp_logevent ('PRDKEY IS NULL FOUND', 16, 1)
end

RETURN 0
GO

That way it only produces the event if the null data was found, that way you aren't mailing out empty reports on every run