Need help sending xp_sendmail - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need help sending xp_sendmail

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 15:11:51

I need to send an automatic email to users who have an overdue duedate.

I've tried this but getting incorrect syntax near 'master' Any thoughts?

declare @requestoremail varchar(75)
set @requestoremail = 'someuser'

master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from libraryrequest
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' @requestoremail

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-25 : 15:17:33

exec master..xp_sendmail

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 15:19:07

Okay that worked but now getting indorrect syntax near '@requestoremail' (the last line of the stored procedure)

declare @requestoremail varchar(75)
set @requestoremail = 'someuser'

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from libraryrequest
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' @requestoremail

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-25 : 15:20:29

declare @requestoremail varchar(75), @myquery VARCHAR(4000)
set @requestoremail = 'someuser'
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from libraryrequest
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 15:30:08

Almost there...

Now I'm getting ODBC error 208 (42S02) Invalid object name 'libraryrequest'.
ODBC error 208 (42S02) Invalid object name 'requestors'.
ODBC error 208 (42S02) Invalid object name 'Titles'.

Seems like it doesn't like how I'm joining the tables and how I'm using their alias.

declare @requestoremail varchar(75), @myquery VARCHAR(4000)
set @requestoremail = 'someuser'
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from libraryrequest
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-25 : 15:33:47

Fully qualify 'libraryrequest' IE Mydatabase.dbo.libraryrequest as libraryrequest

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 15:42:38

Almost...

Now I'm getting ODBC error 208 (42S02) Invalid object name 'requestors'.
ODBC error 208 (42S02) Invalid object name 'Titles'.

declare @requestoremail varchar(75), @myquery VARCHAR(4000)
set @requestoremail = 'someuser'
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryreqeust
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-25 : 15:48:33

Same thing, The query is running from master and needs to have all referenced tables fully qualified.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 16:00:22

I tried this:

eclare @requestoremail varchar(75), @myquery VARCHAR(4000)
set @requestoremail = 'someuser'
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryreqeust
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

Now getting:

ODBC error 107 (42000) The column prefix 'Libraryrequest' does not match with a table name or alias name used in the query.
ODBC error 107 (42000) The column prefix 'libraryrequest' does not match with a table name or alias name used in the query.
ODBC error 107 (42000) The column prefix 'libraryrequest' does not match with a table name or alias name used in the query.

Don't understand do I need to put spdtlibrary.dbo in front of everything referring to libraryrequest?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-25 : 16:16:00

Check your spelling libraryreqeust ?????

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 16:44:48

Are you still proof-reading this?

E 12°55'05.25"
N 56°04'39.16"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 08:57:56

Okay thanks for catching my misspelled word I fixed it but now I'm getting this error:

Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient

When I put my address in I got the email but I want it to send an email to email addressess that are over the due date. Any ideas?

declare @requestoremail varchar(75), @myquery VARCHAR(4000)
set @requestoremail = 'requestoremail'
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-26 : 10:20:37

declare @requestoremail varchar(75), @myquery VARCHAR(4000)

Declare GetEmails cursor for
select requestors.requestoremail
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate()
OPEN GetEmails;
FETCH NEXT FROM GetEmails INTO @requestoremail;
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail;
END
close GetEmails
deallocate GetEmails

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 10:35:59

Thanks Jhocutt for all of your help almost there after I cut and paste what you set up for me I am now getting this error message:

mc@yahoo.com
ODBC error 107 (42000) The column prefix 'mc@yahoo' does not match with a table name or alias name used in the query.

Any ideas:

declare @requestoremail varchar(75), @myquery VARCHAR(4000)

Declare GetEmails cursor for
select requestors.requestoremail
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate()
OPEN GetEmails;
FETCH NEXT FROM GetEmails INTO @requestoremail;
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail=' + @requestoremail

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail;
END
close GetEmails
deallocate GetEmails

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-26 : 11:19:53

declare @requestoremail varchar(75), @myquery VARCHAR(4000)

Declare GetEmails cursor for
select requestors.requestoremail
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate()
OPEN GetEmails;
FETCH NEXT FROM GetEmails INTO @requestoremail;
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Libraryrequest.LoanRequestID, Titles.Title, requestors.requestoremail, libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail= ''' + @requestoremail + ''''

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail;
END
close GetEmails
deallocate GetEmails

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 12:13:10

IT WORKS!!! Thanks so much. Could you please explain these lines of code to me so I can understand what you've done

FETCH NEXT FROM GetEmails INTO @requestoremail;
WHILE @@FETCH_STATUS >= 0

FETCH NEXT FROM GetEmails INTO @requestoremail;
END
close GetEmails
deallocate GetEmails

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-02-26 : 12:37:48

Bless jhocutt for all the work on this one. Are you collecting contract fees for all your time???? Seriously though, that shows what these forums are all about. Good job!

Terry

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 13:43:33

I'll say thanks again to jhocutt for sticking with me. These forums are great I'm learning so much from it. Plan to see more of me

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-26 : 13:49:36

You need to start looking in BOL (Books On Line)
All of this is available there
use Northwind
go

declare @Cat as varchar(255)

-- Declare a cursor as a select from one or more tables only return the columns that you need IE no *
Declare GetData cursor for
select categoryname from Northwind.dbo.Categories

--Open the cursor basically runs it and makes the results available
OPEN GetData;

--Retrieves a specific row from a Transact-SQL server cursor.
FETCH NEXT FROM GetData INTO @Cat;

-- as long as the FETCH returns data keep running
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @Cat -- Do somethign with your results
FETCH NEXT FROM GetData INTO @Cat; --Retrieves a specific row from a Transact-SQL server cursor.
END

close GetData
-- FROM BOL
-- Closes an open cursor by releasing the current result set and freeing
-- any cursor locks held on the rows on which the cursor is positioned.
-- CLOSE leaves the data structures accessible for reopening, but fetches and
-- positioned updates are not allowed until the cursor is reopened.
-- CLOSE must be issued on an open cursor; it is not allowed on cursors that have
-- only been declared or are already closed.

deallocate GetData -- Remove the named cursor so it is no longer available
-- from BOL
-- Removes a cursor reference. When the last cursor reference is deallocated,
-- the data structures comprising the cursor are released by Microsoft® SQL Server™.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 14:48:45

Thanks for the explanation I will check out BOL.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:03:19

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources