loading...
Cover image for Setting up a Laravel project with SQL Server AND XAMPP / WAMP on Windows

Setting up a Laravel project with SQL Server AND XAMPP / WAMP on Windows

mr_steelze profile image Sholley O. Updated on ・3 min read

I spent a large part of yesterday trying to connect an existing Laravel project to MsSQL. I made some mistakes and used different tutorials but I finally got it setup.

Note: This article assumes you have SQL Server, xampp or wamp and laravel correctly installed on your system.

First, we create a fresh Laravel installation and when it's complete we can open the folder in our editor.

laravel new mssql

Open the database.php file in the config folder and make this change.

 /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */
    //from
    'default' => env('DB_CONNECTION', 'mysql'),
    //to
    'default' => env('DB_CONNECTION', 'sqlsrv'),

This is not really important as we are still going to update the .env file, but hey I just feel like doing it :).

Next, we need to update our .env file database credentials

DB_CONNECTION=sqlsrv
DB_HOST=127.0.0.1
DB_PORT=1433
DB_DATABASE=laravel
DB_USERNAME= #SQL Server username
DB_PASSWORD= #SQL Server password

Let's run our migrations using the migrate artisan command in our terminal.

 php artisan migrate

We should get the error below. This means we need to download Microsoft drivers for PHP for SQL Server. (If your migrations did run successfully, then there's no further setup to do).

 Illuminate\Database\QueryException: could not find driver 
(SQL: select * from sysobjects where type = 'U' and name = migrations)

You can download the appropriate drivers from Microsoft store or this GitHub page. I used the latter so I can easily download the drivers for my PHP version(Windows-7.2.zip). (I'm using PHP 7.2).

Extract the contents of the zip file and copy the php_pdo_sqlsrv_7x_ts.dll and php_sqlsrv_7x_ts.dll file.

On WAMPP

  • Paste the files in C:\wamp64\bin\php\php7.x\ext
  • Open the php.ini file in C:\wamp64\bin\php\php7.x folder and add the following lines
 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll
  • Open the php.ini file in C:\wamp64\bin\apache\apache2.4.xx\bin folder and add the following lines
 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll
  • Restart wampp server.

On XAMP

  • Paste the files in C:\xampp\php\ext
  • Open the php.ini file in C:\xampp\php folder and add the following lines
 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll
  • Restart xamp server.

Run the migration command in our terminal.

 php artisan migrate

Now we should be able to run our migrations.

Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.01 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.01 seconds)

Bonus: (I'm on SQL Server 2012 by the way) Using unsignedBigInteger() will throw an error, instead use bigInteger(). More on this here.

UPDATE: You might need to download Microsoft ODBC Driver to fix this - Illuminate\Database\QueryException : SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712

That’s All Folks!
Happy Coding 🖖

Posted on by:

mr_steelze profile

Sholley O.

@mr_steelze

Backend developer passionate about building and fixing stuffs, and good music.

Discussion

markdown guide
 

Hola, me marca un error

Illuminate\Database\QueryException : SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Proveedor de TCP: No se puede establecer una conexión ya que el equipo de destino denegó expresamente dicha conexión.
(SQL: select * from sysobjects where type = 'U' and name = migrations)

at C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connection.php:669
665| // If an exception occurs when attempting to run a query, we'll format the error
666| // message to include the bindings with SQL, which will make this exception a
667| // lot more helpful to the developer instead of just the database's errors.
668| catch (Exception $e) {

669| throw new QueryException(
670| $query, $this->prepareBindings($bindings), $e
671| );
672| }
673|

Exception trace:

1 PDOException::("SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Proveedor de TCP: No se puede establecer una conexión ya que el equipo de destino denegó expresamente dicha conexión.
")
C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

2 PDO::__construct("sqlsrv:Server=127.0.0.1,1433;Database=laravel", "userSQL", "passSQL", [])
C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

Please use the argument -v to see more details.

 

This worked like a charm! Thanks a lot!

 

729/5000
Hello, nice to greet you, I have a system made in Laravel 6 (migrated from Laravel 4) and MySQL but I must also connect it with SQL Server, there I only have to use a stored procedure to store some records in another system, as I should make the call from the controller? I have used the following but all give error
DB :: connection ('sqlsrv') -> SELECT ('SET NOCOUNT ON; CALL SP_APIRserve Service2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? ', $ arrangement);
and
DB :: connection ('sqlsrv') -> insert ('SET NOCOUNT ON; EXECUTE SP_APIRserve Service2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? ', $ arrangement);
and
DB :: connection ('sqlsrv') -> insert ('EXECUTE SP_APIRserveService2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?', $ arrangement);
my email rjalvarez85@gmail.com

 

I think what you have to do is

DB::connection()->statement()
 

Thank you so much. This really helped me.