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 🖖
Top comments (12)
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) {
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.
do you mind help taking a look, i had this error
Illuminate\Database\QueryException
could not find driver (SQL: select * from information_schema.tables where table_schema = forge and table_name = migrations and table_type = 'BASE TABLE')
at C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:712
708▕ // If an exception occurs when attempting to run a query, we'll format the error
709▕ // message to include the bindings with SQL, which will make this exception a
710▕ // lot more helpful to the developer instead of just the database's errors.
711▕ catch (Exception $e) {
➜ 712▕ throw new QueryException(
713▕ $query, $this->prepareBindings($bindings), $e
714▕ );
715▕ }
716▕ }
1 C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDOException::("could not find driver")
2 C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDO::__construct()
Bugsnag Warning: Couldn't notify. cURL error 60: SSL certificate problem: unable to get local issuer certificate (see curl.haxx.se/libcurl/c/libcurl-err...
) for notify.bugsnag.com
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
Thank you so much. This really helped me.
Glad it did 🤗
This came in so handy Sholley.Many thanks
🥰
I need help, i have this error.
PHP Warning: Module "pdo_sqlsrv" is already loaded in Unknown on line 0
Warning: Module "pdo_sqlsrv" is already loaded in Unknown on line 0
Illuminate\Database\QueryException
SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
(SQL: select * from sys.sysobjects where id = object_id(migrations) and xtype in ('U', 'V'))
at C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:712
708▕ // If an exception occurs when attempting to run a query, we'll format the error
709▕ // message to include the bindings with SQL, which will make this exception a
710▕ // lot more helpful to the developer instead of just the database's errors.
711▕ catch (Exception $e) {
➜ 712▕ throw new QueryException(
713▕ $query, $this->prepareBindings($bindings), $e
714▕ );
715▕ }
716▕ }
1 C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDOException::("SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
")
2 C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDO::__construct("sqlsrv:Server=localhost,1433;Database=coindev", "coindev", "coindev@2022.34", [])
Awesome! Thank you!
Any help for Mamp, how to use the php_pdo_sqlsrv_7x_ts.dll and php_sqlsrv_7x_ts.dll file.