Bulk Insert permission issue - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

Bulk Insert permission issue

Author

Topic

ac_786
Starting Member

2 Posts

Posted - 2006-04-25 : 12:05:34

I am having an issue with the Bulk insert not being able to insert into a Temp (#) table. The user has been made the Bulk insert admin. and CAN BULK INSERT fine into a regular table. My issue is that I would like to use a #Tmp_table. The procedure fails on the Bulk insert statement with the following error message:
"The current user is not the database or object owner of table '#TmpStage'. Cannot perform SET operation. ".

Here is the procedure :

CREATE PROCEDURE spDoBulkInsert(@FiletoLoad VARCHAR(200))
AS
BEGIN

DECLARE @Sql VARCHAR(2000)

CREATE #TmpStage (Col1 INT, Col2 VARCHAR(20))

--I simply create the Bulk insert statement here.
SELECT @Sql = ' BULK INSERT #TmpStage ' +
' From ''' + @FiletoLoad + '''' +
' WITH (' +
' ROWS_PER_BATCH = 999999,' +
' BATCHSIZE = 999999,' +
' FIELDTERMINATOR = '','', ' +
' FIRSTROW = 2,' +
' ROWTERMINATOR = ''\n'',' +
' TABLOCK )'

EXEC (@Sql)
INSERT INTO TblRealTable (Col1, Col2) SELECT Col1, Col2 FROM #TmpStage

END

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 23:57:35

instead of local use global temp table
##tmpstage

and be sure to drop afterwards

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources