bulk insert working in ssms but not working while executing from php

53
February 12, 2019, at 8:40 PM

I'm trying to insert the data from a text file into my database. I'm using SQL Server Management Studio 17.

I'm using this query:

BULK INSERT db.[dbo].[mytable]
    FROM 'path/file.txt'
    WITH 
        (FIELDTERMINATOR = ';',
         ROWTERMINATOR = '\n') 

The problem is that it's working in SQL Server Management Studio but when i'm trying to execute this php script it shows an error even though I granted the user the permission to bulk after following this:

You do not have permission to use the bulk load statement

The error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4834 [code] => 4834 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]You do not have permission to use the bulk load statement. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]You do not have permission to use the bulk load statement. ) )

The code i'm using

<?php
$serverName = "SQLEXPRESS"; 
$connectionInfo = array( "Database"=>"master");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}    
$query1 = "BULK INSERT db.[dbo].[mytable]
    FROM 'path/file.txt'
    WITH 
        (FIELDTERMINATOR = ';',
         ROWTERMINATOR = '\n')";
$result1 = sqlsrv_query($conn,$query1) or  die( print_r(sqlsrv_errors(), true) );
?>
Answer 1

Account that is used by your PHP application need this permission:

GRANT ADMINISTER BULK OPERATIONS TO [server_login]

Your SSMS process seems run using other credentials and they have such permission.

The other reason can be if SQL Server service account does not have enough permissions to read the source file

READ ALSO
Laravel/LaraCSV - Global Variable in beforeEach

Laravel/LaraCSV - Global Variable in beforeEach

I have a function in my Controller where I'm trying to export acsv file using LaraCSV

54
Cannot get difference between two timestamps

Cannot get difference between two timestamps

I want return the number of dates which have a difference less than 30 minutesThese are the records stored in the database:

56
Parse SOAP web service XML response

Parse SOAP web service XML response

I´m consuming a WS and it gives me data as XMLWhen the response returns to my PHP it came as Object and not as XML

46
How to extract in an array using Simple HTML Dom

How to extract in an array using Simple HTML Dom

I am trying to extract some values parsing html from a websiteWhere are many values with the same tag and when I try to extract them, I got back just "array" word

50