sp error 'Cannot use empty object or column names'

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)

sp error 'Cannot use empty object or column names'

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-04-28 : 10:56:47

I'm running a stored procedure that sends certain emails to users depending on certain fields. Emails go out fine but i always get the error, 'Cannot use empty object or column names. Use a single space if necessary.' any ideas? here is my sp:

declare @vcEmailBody varchar(8000),@ResponseCode int,@vcEmailTitle varchar(100),@vcEmailName varchar(50),@vcEmailFileName varchar(250)

declare @intDaysOut int
declare @BusinessDays int
declare @CheckDate datetime
set @CheckDate = CONVERT(varchar(10), getdate()+1, 1)
set @BusinessDays = 0
set @intDaysOut = 0
WHILE @BusinessDays < 2
BEGIN
set @intDaysOut = @intDaysOut + 1
if (select count(*) from tblholidays where dtholidays=@CheckDate)>0
sET @CheckDate = @CheckDate + 1
else
begin
set @BusinessDays = @BusinessDays + 1
if @BusinessDays < 2
sET @CheckDate = @CheckDate + 1
end
END

select @intdaysout
DECLARE @intTransactionID int,@intCustomerID int,@vcEmail varchar(100),@vcEmail2 varchar(100),@vcFirstName varchar(30),@vcLastName varchar(50),@dtDueDate varchar(10),@numAmount varchar(10),@intPayFrequencyID int,@intLenderID varchar(10),@intDeferrals int,@lenderEmail varchar(100),@vcSystemEmailAddress varchar(100)
DECLARE cursorDeferral CURSOR FOR

select
max(t.inttransactionid) as inttransactionid,
max(c.intcustomerid) as intcustomerid,
max(vcemail) as vcemail,
max(vcemail2) as vcemail2,
max(vcfirstname) as vcfirstname,
max(vclastname) as vclastname,
convert(varchar(10),max(dtduedate),1) as dtduedate,
'$' + cast(sum(numamount) as varchar(10)) as totaldue,
max(intpayfrequencyid) as intpayfrequencyid,
max(c.intlenderid) as intlenderid,
(select count(intdeferralid) from tbldeferrals where inttransactionid=t.inttransactionid and bitapproved=1) as deferralcount,
max(vcCollEmailAddress) as vcCollEmailAddress,
max(vcSystemEmailAddress) as vcSystemEmailAddress
from tblTransactions t
left join tbltransactionregister r on r.inttransactionid=t.inttransactionid
left join tblCustomers c on t.intcustomerid=c.intcustomerid
left join tblLenders l on l.intlenderid=c.intlenderid
where bitcomplete=1
and inttransactionstatusid=1
and dtduedate=convert(varchar(10),getdate()+@intDaysOut,1)
group by t.inttransactionid
having sum(numamount)>0
order by dtduedate

OPEN cursorDeferral
FETCH cursorDeferral INTO @intTransactionID,@intCustomerID,@vcEmail,@vcEmail2,@vcFirstName,@vcLastName,@dtDueDate,@numAmount,@intPayFrequencyID,@intLenderID,@intDeferrals,@lenderEmail,@vcSystemEmailAddress

WHILE @@FETCH_STATUS = 0
BEGIN

--figure correct email to send out
if @intPayFrequencyID=3 or @intPayFrequencyID=9
begin
set @vcEmailName = test1.htm'
set @vcEmailTitle = 'test1'
end
else if @intDeferrals=0
begin
set @vcEmailName = 'test2.htm'
set @vcEmailTitle = 'test2'
end
else if @intDeferrals=4
begin
set @vcEmailName = 'test3.htm'
set @vcEmailTitle = 'test3'
end
else if @intDeferrals>0 and @intDeferrals<4
begin
set @vcEmailName = 'test4.htm'
set @vcEmailTitle = 'test4'
end

--get correct email body and format
set @vcEmailFileName = 'c:\' + @intLenderID + '\' + @vcEmailName
CREATE TABLE temp(vcEmailBody varchar(8000))
SET QUOTED_IDENTIFIER OFF
exec("BULK INSERT temp FROM '" + @vcEmailFileName + "' WITH(DATAFILETYPE = 'char',FIELDTERMINATOR = '',ROWTERMINATOR = '')")
SET QUOTED_IDENTIFIER On
set @vcEmailBody = (select vcEmailBody from temp)
drop table temp
SET @vcEmailBody = (SELECT REPLACE(@vcEmailBody, '', @vcFirstName))
SET @vcEmailBody = (SELECT REPLACE(@vcEmailBody, '', @vcLastName))
SET @vcEmailBody = (SELECT REPLACE(@vcEmailBody, '', @vcFirstName + @vcLastName))
SET @vcEmailBody = (SELECT REPLACE(@vcEmailBody, '', @dtDueDate))
SET @vcEmailBody = (SELECT REPLACE(@vcEmailBody, '', @numAmount))

--send email
EXEC @ResponseCode = master..xp_smtp_sendmail
@FROM = @lenderEmail,
@FROM_NAME = 'test',
@TO = 'myemailaddress@test.com',
--@TO = @vcEmail,
--@CC = @vcEmail2,
--@BCC = @vcSystemEmailAddress,
@subject = 'test',
@type = 'text/html',
@message = @vcEmailBody,
@server = 'ourserver'

FETCH cursorDeferral INTO @intTransactionID,@intCustomerID,@vcEmail,@vcEmail2,@vcFirstName,@vcLastName,@dtDueDate,@numAmount,@intPayFrequencyID,@intLenderID,@intDeferrals,@lenderEmail,@vcSystemEmailAddress
END

CLOSE cursorDeferral
DEALLOCATE cursorDeferral

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 13:30:41

I believe you problem is with these lines:

quote:
SET QUOTED_IDENTIFIER OFF
exec("BULK INSERT temp FROM '" + @vcEmailFileName + "' WITH(DATAFILETYPE = 'char',FIELDTERMINATOR = '',ROWTERMINATOR = '')")
SET QUOTED_IDENTIFIER On

From BOL:

quote:
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

I think you just need to remove the SET statments and loose the double quotes from your bulk insert command.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources