Post reply

Execute remote Bulk Insert query on SQL server(server1) with source data on server2 using PERL script executed on server2

  • November 8, 2007 at 8:00 am

    #70272

    Server 1 - batch Server

    Server 2 - database server (windows SQL 2005)

    Same domain ID XXXXX was created on both servers and has access to both the servers.

    Our requirement is to insert records into table (table1) on the database server with data from flat file(A.LOD) on the batch server.

    We are using a Bulk insert query

    BULK INSERT table1 FROM '\\serv1\folder\A.LOD' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR = '',MAXERRORS=2);

    We use domain ID XXXXX to log in to batch server

    We run a PERL script that logs onto Server2(DB server) using the same domain ID XXXXX. When we run the bulk insert query we get the error

    DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot bulk load because the file "\\\\serv1\folder\A.LOD" could not be opened. Operating system error code 5(Access is denied.). (SQL-42000)(DB

    D: st_execute/SQLExecute err=-1) at sampleperl.pl line 156.

    When I logon directly to DB server and

    exactly same query is executed through MS SQL server Management Studio it is successful.

    Or

    When I move the flat file to the Database server and run PERL from the batch server to execute it works.

    There seems to be no access issues on the flat file in question (\\\\serv1\folder\A.LOD) from the database server as I can go to run and type the path and access the file.

    Please help

  • November 12, 2007 at 10:16 am

    #750317

    How do you run the Perl script?

    Did you try to create a job in the Task Scheduler on the database server that executes this script in the security context of the domain user XXXXX ? One thing: when you login to Query Analyzer or Management Studio, your credentials are the ones that are running the script. You have to make sure:

    1. The script is run in the security context of your domain user on the database server. There acould be delegation problems when you run the script from another server.

    2. If the script is run by SQL Server, it has to be started on the acount that has permissions to your share on the batch server. If the script is run by SQL Server Agent, the agent has to be started on the domain account that has permissions on your share.

    3. The best way to figure out what account is running the script is to run the job, get an error message, log in to the batch server and check what account has Login Failed record in the Security Log.

    4. Check that both Share and NTFS permissions are correct for the suer who tries to login to the share to get the file.

    Regards,Yelena Varsha

  • November 12, 2007 at 2:34 pm

    #750381

    I am just trying to run the job from command prompt in the batch server. I use the same login ID password on both machine( as well on SQL manager). It is a domain ID.

  • May 28, 2009 at 3:28 pm

    #1001802

    did you ever figure out your bulk insert problem? it sounds like something i am getting on our 2008\SP1 installation? it works on 2005 and not on the 2008?

    thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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