none
bulk insert with four part names - fails with "Invalid object name"

    Question

  • I am using SQL 2008. following command FAILs :

    BULK INSERT
    [AW-2008].[BULKINSERT].[dbo].[int999] FROM
    'C:\BULKINSERT\INT999.txt' WITH (FIELDTERMINATOR = ',')

    error  :Msg 208, Level 16, State 82, Line 4
    Invalid object name 'int999'.

    Following command WORKs ( so my linked server is working):

    INSERT
    INTO [AW-2008].[BULKINSERT].[dbo].[int999]
    ([COL1]) VALUES (1)
    GO

    Also verified that INT999.txt exists in C:\BULKINSERT

    Friday, July 10, 2009 9:46 PM

Answers

  • The problem you are encountering is documented in BOL. The problem lies in that when a user is logged into an instance via windows authenication and trying to load data into another instance.  The workarounds are to setup delgation or to use a sql login instead.

    Here is the snippet:  Link:  http://msdn.microsoft.com/en-us/library/ms188365.aspx
    --------------------------------------------

    Security Account Delegation (Impersonation)

    If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

    When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

    To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

    For more information about this and other security considerations for using BULK INSERT, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).


    http://jahaines.blogspot.com/
    Monday, July 20, 2009 4:55 PM

All replies

  • Asmita,

    As far as I know, you can't do a BULK INSERT using a linked server and why do you want to do this anyway?

    When you use four part name, linked servers are used to access the tables and there are quite a few limitations on what you can do.



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Sunday, July 19, 2009 6:45 AM
  • I agree with Sankar.  Why do you need to perform the bulk insert from one server and load it in another?  Why not load the file from [AW-2008]?  The best option is to create a bcp process that just loads the data onto [AW-2008], from [AW-2008].  If you have to make a remote call, I would create a remote bulk insert stored procedure on [AW-2008] and execute it from the local server.  You may even want to look at creating a SSIS package.  You can schedule the package via SQL Agent Jobs.
    http://jahaines.blogspot.com/
    Monday, July 20, 2009 5:11 AM
  • if you can't copy the files over to the remove server, you can consider BULK insert into local server and then INSERT into the remote server via linked server.
    KH Tan
    Monday, July 20, 2009 5:20 AM
  • Thanks all for your replies.

    I want to use linked server  because  I may not essentially go to SQL server instance while doing bulk insert. My AW-2008 can be a DB2  instance.
    I am also using SSIS, but my main goal is to get it working through SSIS  and DQP both and compare the performance.
    thanks again.

    I will post if  I find if FOUR PART names are supported in BULK INSERT.

    I am  also trying to get it working through EXEC ('QUERY') like this :

    EXEC ( 'BULK INSERT BULKINSERT.dbo.int999  FROM 

    ''\\servername\BULKINSERT\INT999.txt'' WITH (FIELDTERMINATOR = '','')') AT [AW-2008]

        
    but I am getting following error :
      
             

    Msg 4861, Level 16, State 1, Line 1

    Cannot bulk load because the file "\\servername\BULKINSERT\INT999.txt" could not be opened. Operating system error code 5(Access is denied.).

    Any thoughts or comments appreciated.
    thanks,
    Asmita


    amolasu
    Monday, July 20, 2009 4:43 PM
  • The problem you are encountering is documented in BOL. The problem lies in that when a user is logged into an instance via windows authenication and trying to load data into another instance.  The workarounds are to setup delgation or to use a sql login instead.

    Here is the snippet:  Link:  http://msdn.microsoft.com/en-us/library/ms188365.aspx
    --------------------------------------------

    Security Account Delegation (Impersonation)

    If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

    When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

    To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

    For more information about this and other security considerations for using BULK INSERT, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).


    http://jahaines.blogspot.com/
    Monday, July 20, 2009 4:55 PM