DEV Community

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 Odunayo Ogungbure 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
Enter fullscreen mode Exit fullscreen mode

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'),
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

 php artisan migrate
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • Restart xamp server.

Run the migration command in our terminal.

 php artisan migrate
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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 🖖

Discussion

pic
Editor guide
Collapse
maygallegos profile image
maygallegos

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.

Collapse
rjalvarez profile image
Rafael Alvarez

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

Collapse
mr_steelze profile image
Odunayo Ogungbure Author

I think what you have to do is

DB::connection()->statement()
Collapse
ziscore profile image
Shailendra

This worked like a charm! Thanks a lot!

Collapse
demabio profile image
Demabio

This came in so handy Sholley.Many thanks

Collapse
mr_steelze profile image
Collapse
ediri_aghwotu profile image
Aghwotu Ovuoke

Thank you so much. This really helped me.

Collapse
mr_steelze profile image
Collapse
cokasio profile image
cokasio

Awesome! Thank you!

Collapse
dheeraj270693 profile image
dheeraj270693

Any help for Mamp, how to use the php_pdo_sqlsrv_7x_ts.dll and php_sqlsrv_7x_ts.dll file.