Post reply

Bulk Insert to a temorary table

  • May 15, 2008 at 6:55 am

    #76990

    Hi;

    I would like to use the following code to insert data from a CSV file into a temporary table

    IF OBJECT_ID(N'TempDb..#MyWells',N'U') IS NOT NULL DROP TABLE #MyWells

    CREATE TABLE #MyWells (UWI VARCHAR(13) PRIMARY KEY CLUSTERED (UWI))

    BULK INSERT #MyWells FROM '\\calfsrv\users3\cb238\wells.csv' with (FieldTerminator = ',', RowTerminator ='')

    I get this error;

    Msg 8104, Level 16, State 2, Line 4

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

    I've been granted Bulk Insert by my DBA.

    When I use the code to create a permanent table it works fine but not with a temporary table.

    Can anyone tell me why that is?

  • May 15, 2008 at 8:49 am

    #815934

    Anyone... anyone...?

  • May 15, 2008 at 8:59 am

    #815946

    try to run it under tempdb

    USE tempdb

    GO

    BULK INSERT #tempfile

    FROM 'C:\temp.dat'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',

    KEEPNULLS

    )

    GO

  • May 15, 2008 at 9:02 am

    #815955

    I copied this from msdn.microsoft

    Copying Data To or From a Temporary Table

    When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:

    bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -Ppassword

    However, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.

  • May 15, 2008 at 10:11 am

    #816024

    Hi Loner. Thanks for the reply.

    I've tried your ideas. Still no joy.

    I think it has something to do with privileges but I'm not sure what it is yet.

    I do know that the code above works fine on my Local instance of SQL2005 where I have sysadmin authority. Unfortunately I don't have that on our enterprize database. I have to believe, though, that there's got to be a way to do this without being sysadmin!

  • May 15, 2008 at 12:42 pm

    #816102

    Have you tried using global temp table?

  • May 15, 2008 at 1:10 pm

    #816114

    Yes I have.

    I have also tried it creating a permanent table (I have dbo authority) and it works as desired. The problem has something to do with it being a temporary - global or local - table.

  • May 15, 2008 at 1:22 pm

    #816119

    Are you logging in using Windows authentication or SQL Server auth?

    If you're using SQL auth, then the BULK INSERT is run under the context of the SQL Server service account, so it wouldn't see temp tables you created (different context).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 15, 2008 at 1:24 pm

    #816120

    I'm using Windows authentication.

  • May 15, 2008 at 4:09 pm

    #816197

    When you try to Bulk Insert into a Temp table, the use must have privs to write to C:\Temp on the server because some working files are created there.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 16, 2008 at 6:41 am

    #816425

    Well! That would be an important piece of information! I've been doing a lot of research on this problem and haven't come across that.

    Do you have any docmenteted reference that I can send to my DBA?

    Thanks Jeff!

  • May 16, 2008 at 7:34 am

    #816472

    No, I sure don't. All I have is an email from my DBA when I ran into the problem when we switched to windows authentication. I'll see if I can dig that up...

    If memory serves correctly, it also has to do with privs on TempDB... for some reason, TempDB acts differently with Windows Authentication... they would have to grant your login with Bulk Insert privs on TempDB, as well. Obviously, that's a problem on reboot because TempDB get's rebuilt.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 16, 2008 at 7:42 am

    #816478

    Crud, I got two problems mixed up.. just talked with my DBA on the phone... she says that the Bulk Insert to TempDB problem is because of the Windows Authentication and the fact that your login has no Bulk Insert privs on TempDB. Since the "job" was externally scheduled to run, her work around was to create a spec "server side" user that used the mixed mode.

    It has nothing to do with C:\Temp privs... that was my other problem... doing open rowsets to read/write spreadsheets.

    Appologies for mixing the two up.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 16, 2008 at 7:54 am

    #816491

    Hmm.

    Sounds like the upshot is that something that should be simple - isn't.

    Here's the nut of my problem.

    I have an application which can execute SQL scripts directly (I have the privilage of writing these ;-)). It has functionality to create a simple UI into which a user can supply query parameters and push an execute button and the query runs (I have the privilage of writing these too...).

    In the particular case I'm working on, one of the parameters that the user may specify is a file path to a CSV which contains PK's of the data that the script is supposed to process.

    I was thinking "how hard can it be to get the data from the file and put it into a temp table and then join the temp table to my data table to get the data I'm looking for..."

    Apparently it's not nearly as easy as it should be...:crying:

  • May 16, 2008 at 7:55 am

    #816493

    So - if I have BULK INSERT privilages in the TempDb I'll be able to make this work?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply