Wednesday, March 7, 2012

Permission problems with a #temp table

Hi,

I am having problems running a stored procedure that was created by the sa account. The stored procedure creates a temp table ( #temp_basf) and performs a bulk insert into it. The user account has execute privileges against the stored procedure. The error I am getting is:

The current user is not the database or object owner of table '#temp_basf'. Cannot perform SET operation.

Here is the code from stored procedure:


create procedure processFarmChemDataFile
as
begin
create table #temp_basf
(RTUId varchar(15),
LName varchar(20),
Address1 varchar(50),
City varchar(20),
StateCode varchar(9),
ZipCode varchar(15),
CName varchar(30),
Voice varchar(30),
Name varchar(25),
TankCap varchar(15),
TankHgt varchar(9),
notUsed varchar(20),
ProdDesc varchar(20),
InvTime smalldatetime,
GrVolume varchar(15))
end
begin
bulk insert #temp_basf from 'c:\testdata.txt' with
(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
end
begin
update #temp_basf
set RTUId = replace(RTUId,'"',''),
LName = replace(LName,'"',''),
Address1 = replace(Address1,'"',''),
City = replace(City,'"',''),
StateCode = replace(StateCode,'"',''),
ZipCode = replace(ZipCode,'"',''),
CName = replace(CName,'"',''),
Voice = replace(Voice,'"',''),
Name = 'Tank '+replace(Name,'"',''),
TankCap = replace(TankCap,'"',''),
TankHgt = replace(TankHgt,'"',''),
notUsed = replace(notUsed,'"',''),
ProdDesc = replace(ProdDesc,'"',''),
InvTime = replace(InvTime,'"',''),
GrVolume = replace(GrVolume,'"','')
end
begin
select CustomerId,LocationId,TankId,Temp,Alarms,SpecGrav,
CoeffExp,ProdCode,a.ProdDesc,UserProdId,UserTankId,
b.InvTime,b.RTUId,b.GrVolume,ChartId
from Tank a
inner join #temp_basf b on a.Name = upper(b.Name)
end

ThanksThe procedure seems to have a problem performing the bulk insert. I have tried boosting the user's account privileges by adding them to the BulkAdmin server role and dbo.owner roles. So far that hasn't helped. Does anyone have any other suggestions?

Thanks

No comments:

Post a Comment