Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Unanswered: Bulk insert not working

    I have a situation. The userID running the Stored Proc is assigned Bulk-Admin privileges . Program creates a temp# table and bulk inserts a text file. The process runs fine running as Sysadmin . However , it fails if run with that UserID with following error

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

    What should I do to fix it .

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you do the #temp table create in the same process?

    Does the id have DDLAdmin permissions?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Hi

    If I apply ddlamin it works but I don't want that ID to have that permission. All is part of a Stored_Proc

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mu guess is the CREATE TABLE #temp failed...

    did you add error checking
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Nope

    The user is bale to create temp table , error shows up when he is loading it . If I make him a dbo or ddl_admin all works fine .

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the create and the bcp?

    Or if it's not too huge the sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Suer

    Here is the main code

    CREATE PROCEDURE dbo.Load_GeneralNotification

    @InputFileName varchar(512),
    @FormatFileName varchar(512)

    AS
    BEGIN

    declare @FullLoadFileName varchar(512)
    declare @FullFormatFileName varchar(512)

    -- Build the file paths based on the parameters
    select @FullLoadFileName = char(39) + @InputFileName + char(39)
    select @FullFormatFileName = char(39) + @FormatFileName + char(39)

    print 'The file to be loaded is: ' + @FullLoadFileName
    + ' and the format file is: ' + @FullFormatFileName


    create table
    #TempLoad ( [Office] [char] (3) NULL ,
    [Account] [char] (6) NULL ,
    [FA] [char] (3) NULL ,
    [KeyAcct] [char] (26) NULL ,
    [AcctType] [char] (6) NULL ,
    [AcctSubType] [char] (6) NULL ,
    [TradCtl] [char] (1) NULL ,
    [DivPay] [char] (1) NULL ,
    [ShortName] [char] (12) NULL ,
    [AddrLine1] [char] (40) NULL ,
    [AddrLine2] [char] (40) NULL ,
    [AddrLine3] [char] (40) NULL ,
    [AddrLine4] [char] (40) NULL ,
    [AddrLine5] [char] (40) NULL ,
    [NotificationType] [char] (10) NULL ,
    [NotificationSubType] [char] (10) NULL ,
    [NotificationDate] [char] (10) NULL ,
    [ErrorID] [char] (50) NULL ,
    [HHTier] [char] (6) NULL ,
    [FaFirstName] [char] (40) NULL ,
    [FaLastName] [char] (40) NULL ,
    [FaPhoneNum] [char] (10) NULL ,
    [FaClub] [char] (15) NULL ,
    [Filler] [char] (144) NULL
    )

    CREATE INDEX [IX_TEMPLOAD_KeyAcct] ON #TempLoad([KeyAcct])
    CREATE INDEX [IX_TEMPLOAD_NotificationType] ON #TempLoad([NotificationType])
    CREATE INDEX [IX_TEMPLOAD_ErrorID] ON #TempLoad([ErrorID])

    -- Execute the load statement
    exec( 'BULK INSERT #TempLoad FROM ' + @FullLoadFileName
    + ' WITH ( FORMATFILE = ' + @FullFormatFileName + ' )' )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •