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]

Drew (cl****@hotmail.com) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Drew]

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 <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Drew (cl****@hotmail.com) writes:

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.

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.

[CY Lim]

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




cl****@hotmail.com (Drew) wrote in message news:<d0**************************@posting.google. com>...

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 <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Drew (cl****@hotmail.com) writes:

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.

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.