Bulk Insert - Cannot perform SET operation.

[Drew]

I am trying to use Bulk Insert for a user that is not sysadmin.
I have already set up the user as a member of "bulkadmin" .

When I run the following script:
DECLARE @SQL VARCHAR(1000)
CREATE TABLE amdbo.#temp (
[id] [varchar] (10) NULL
,[fld2] [varchar] (10) NULL
,[fld3] [varchar] (10) NULL
)
set @SQL =
'BULK INSERT amdbo.#temp
FROM ''F:\test.txt''
WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
= ''\n'')'
EXEC (@SQL)
select * from #temp

I still get the message ...
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '#temp'.
Cannot perform SET operation.

Anyone have an idea what I am doing wrong?
Drew.

[Erland Sommarskog]

Re: Bulk Insert - Cannot perform SET operation.

Drew (cladre@hotmail .com) writes:[color=blue]
> I am trying to use Bulk Insert for a user that is not sysadmin.
> I have already set up the user as a member of "bulkadmin" .
>
> When I run the following script:
> DECLARE @SQL VARCHAR(1000)
> CREATE TABLE amdbo.#temp (
> [id] [varchar] (10) NULL
> ,[fld2] [varchar] (10) NULL
> ,[fld3] [varchar] (10) NULL
> )
> set @SQL =
> 'BULK INSERT amdbo.#temp
> FROM ''F:\test.txt''
> WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
>= ''\n'')'
> EXEC (@SQL)
> select * from #temp
>
> I still get the message ...
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table '#temp'.
> Cannot perform SET operation.[/color]

As I recall you cannot bulk load to temp tables if you are not sysadmin.
Or, this is somewhat weird: the user is the dbo of tempdb.

It's probably better to create a permanent table that this user is
the owner of and use that table as the target.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Drew]

Re: Bulk Insert - Cannot perform SET operation.

The solution I've done is to create a physical table, populate it, and
when the procedure ends, drop the table. Which is pretty much what
you suggested.
Thanks,
Drew



Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9402F2 4BBF061Yazorman @127.0.0.1>...[color=blue]
> Drew (cladre@hotmail .com) writes:[color=green]
> > I am trying to use Bulk Insert for a user that is not sysadmin.
> > I have already set up the user as a member of "bulkadmin" .
> >
> > When I run the following script:
> > DECLARE @SQL VARCHAR(1000)
> > CREATE TABLE amdbo.#temp (
> > [id] [varchar] (10) NULL
> > ,[fld2] [varchar] (10) NULL
> > ,[fld3] [varchar] (10) NULL
> > )
> > set @SQL =
> > 'BULK INSERT amdbo.#temp
> > FROM ''F:\test.txt''
> > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> >= ''\n'')'
> > EXEC (@SQL)
> > select * from #temp
> >
> > I still get the message ...
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table '#temp'.
> > Cannot perform SET operation.[/color]
>
> As I recall you cannot bulk load to temp tables if you are not sysadmin.
> Or, this is somewhat weird: the user is the dbo of tempdb.
>
> It's probably better to create a permanent table that this user is
> the owner of and use that table as the target.[/color]

[CY Lim]

Re: Bulk Insert - Cannot perform SET operation.

I have the similiar issues encountered. Please refer to MS
knowledgebase 302621. Microsoft has confirmed this is an software
issue.














cladre@hotmail. com (Drew) wrote in message news:<d00212d6. 0310200700.3b2b ca9f@posting.go ogle.com>...[color=blue]
> The solution I've done is to create a physical table, populate it, and
> when the procedure ends, drop the table. Which is pretty much what
> you suggested.
> Thanks,
> Drew
>
>
>
> Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9402F2 4BBF061Yazorman @127.0.0.1>...[color=green]
> > Drew (cladre@hotmail .com) writes:[color=darkred]
> > > I am trying to use Bulk Insert for a user that is not sysadmin.
> > > I have already set up the user as a member of "bulkadmin" .
> > >
> > > When I run the following script:
> > > DECLARE @SQL VARCHAR(1000)
> > > CREATE TABLE amdbo.#temp (
> > > [id] [varchar] (10) NULL
> > > ,[fld2] [varchar] (10) NULL
> > > ,[fld3] [varchar] (10) NULL
> > > )
> > > set @SQL =
> > > 'BULK INSERT amdbo.#temp
> > > FROM ''F:\test.txt''
> > > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> > >= ''\n'')'
> > > EXEC (@SQL)
> > > select * from #temp
> > >
> > > I still get the message ...
> > > Server: Msg 8104, Level 16, State 2, Line 1
> > > The current user is not the database or object owner of table '#temp'.
> > > Cannot perform SET operation.[/color]
> >
> > As I recall you cannot bulk load to temp tables if you are not sysadmin.
> > Or, this is somewhat weird: the user is the dbo of tempdb.
> >
> > It's probably better to create a permanent table that this user is
> > the owner of and use that table as the target.[/color][/color]