CODEWITHSUNDEEP
phpsql-server
Morsi.Hamza
Morsi.Hamza

Reputation: 13

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

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) );

?>

Upvotes: 1

Views: 326

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

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

Upvotes: 1

Related Questions