May 12, 2004 at 11:14 am
When I call the procedure below I get the error "ODBC error 208 (42S02) Invalid object name 'shipmentsA'." Can anyone explain? Thanks.
CREATE PROCEDURE dbo.shipNotification4 AS
declare @customerID int
declare @qryString nvarchar(200)
select @customerID = 2554
select @qryString = 'select top 5 * from shipmentsA where customerID = ' + cast(@customerID as char)
EXEC master.dbo.xp_sendmail
@recipients = 'joe@somedomain.com',
@subject = 'Shipments',
@message = 'Your shipments',
@query = @qryString
or "ODBC error 208 (42S02) Invalid object name 'shipmentsA'."
May 12, 2004 at 11:34 am
Try fully qualifying the shipmentsA table in the select @querystring.
May 12, 2004 at 11:46 am
It works now! Thanks.
May 12, 2004 at 12:25 pm
Actually, I still have a problem if the table in my query string is a temp table. How do I fully qualify the name of a temp table? When I call the procedure below, I get "Invalid object name '#shipmentsB'". I have tried many syntaxes for fully qualifying the temp table, but nothing seems to work. Thanks in advance.
CREATE PROCEDURE dbo.shipNotification4 AS
declare @customerID int
declare @qryString nvarchar(200)
select @customerID = 2554
select top 5 *
into #shipmentsB
from shipmentsA
select @qryString = 'select top 5 * from #shipmentsB'
EXEC master.dbo.xp_sendmail
@recipients = 'joe@somedomain.com',
@subject = 'Shipments',
@message = 'Your shipments',
@query = @qryString
May 12, 2004 at 12:41 pm
Temporary tables are stored in tempdb, so you would reference it as TempDB..#shipmentsB
May 14, 2004 at 7:21 am
xp_sendmail opens its own connection . . . hence, local temp tables may be out of scope. you might want to use a global temp table and the proc's "@dbuse" argument.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply