Laravel and MS SQL Server Database connection is t

问题:

Guys, I tried lots of things mentioned in Stackoverflow and laracasts for this issue but none worked, hence posting this in hope of getting some answer or advice. Also, most of the things provided belongs to MYSQL not for MS SQL.

I am using Windows 10 and using Laravel to connect to my Microsoft SQL Server 2012 Express edition.

I have the .erv file as following:

DB_CONNECTION=sqlsrv
DB_HOST=W1234567\SQLEXPRESS
DB_PORT=1433
DB_DATABASE=My_Database_Name
DB_USERNAME=sa
DB_PASSWORD=My_Password

The given details are correct but when I am running a simple query I am getting following error message in my Laravel Model:

(3/3) QueryException
could not find driver (SQL: SELECT COUNT(*) AS count FROM TBL_Data
WHERE ACTIVE_IND = 1
)

I have the WAMP running properly, I have added the SQLSRV file to my WAMP.

The entire thing was working fine, now I moved the code to different system there I am getting this error (Both the system has the same credentials only change is the Server name which I have changed) so the code should work properly as there are no changes done.

I tried checking the SQL Server TCP/IP port number in SQL Server Configuration Manager and changed it to 1433 still no luck.

I checked most of the answers here and they belong to PDO exception and asked people to uncomment the part extension=php_pdo_mysql.dll but in my case its SQL Server and normal wamp is working perfectly only seems to be problem with LARAVEL.

Can anyone provide the solutions for this?

Edited part below with some more info:

I checked if the credentials given are correct and if I am able to connect to db using a simple function in controller: My Route: Route::get('/Test', 'My_Controller@Test');

My Controller:

public function Test(Request $request)
{
  if(DB::connection()->getDatabaseName())
   {
    echo "connected successfully to database ".DB::connection()->getDatabaseName();
  }

}

Output in the Chrome window: connected successfully to database My_Database_Name

As we see from above message, I am able to connect properly but still when I run the query I get the error:

(3/3) QueryException
could not find driver (SQL: SELECT * FROM TBL_Data WHERE ACTIVE_IND = 1 )

Added the phpinfo(); in the controller and tried to hit the URL and got the following info related to the PDO:

PDO support enabled

PDO drivers mysql, SQLite

SQLSRV is not enabled here is this something which is causing the issue?

回答1:

This did NOT work for me but may work for some people hence posting this answer, hope this will be helpful

I tried to change the port number for SQL Server still this one did not work, If anyone wants to try they can try this and see if this one will work.

The issue was with MS-SQL Server 2012 Express didn't have port 1433 in the SQL Server Configuration Manager > SQL Server Network Configuration > TCP/IP > Properties> TCP Port was BLANK! Just type 1433 and restart the services and BAM!! Like Magic!

回答2:

So I was finally able to figure out the issue, Posting the same so it will help someone who has struck for days and ripping their hair out to find the solution.

1. Make sure you have given proper port in the Laravel while connection and its same as the one SQL Server Configuration Manager TCP/IP.

2. Make sure your TCP/IP is Enabled in SQL Server Configuration Manager.

3. I had some Extension which were missing in my php.ini file so I copy pasted these extension on to my php.ini file in the PHP of WAMP.

   extension=php_bz2.dll

   extension=php_curl.dll

   extension=php_sqlsrv_56_ts.dll

   extension=php_sqlsrv_56_nts.dll

   extension=php_com_dotnet.dll

   ;extension=php_enchant.dll

   extension=php_fileinfo.dll

   ;extension=php_ftp.dll

   extension=php_gd2.dll

   extension=php_gettext.dll

   extension=php_gmp.dll

   extension=php_intl.dll

   extension=php_imap.dll

   ;extension=php_interbase.dll

   extension=php_ldap.dll

   extension=php_mbstring.dll

   extension=php_exif.dll ; Must be after mbstring as it depends on it

   extension=php_mysqli.dll

   extension=php_odbc.dll

   extension=php_openssl.dll

   ;extension=php_pdo_firebird.dll

   extension=php_pdo_mysql.dll

   ;extension=php_pdo_oci.dll

   ;extension=php_oci8_12c.dll ; Use with Oracle Database 12c Instant Client

   extension=php_pdo_odbc.dll

   ;extension=php_pdo_pgsql.dll

   extension=php_pdo_sqlite.dll

   ;extension=php_pgsql.dll

   ;extension=php_phpdbg_webhelper.dll

   ;extension=php_shmop.dll

4. Every time you change something in the .env file in the Laravel, you need to refresh it otherwise it will still use the old data which you have provided.

   php artisan config:clear

   php artisan config:cache

5. Restart the WAMP Server and the Laravel server php artisan server

6. My .env file in the Laravel:

   DB_CONNECTION=sqlsrv

   DB_HOST=127.0.0.1

   DB_PORT=1433

   DB_DATABASE=DataBase_Name

   DB_USERNAME=sa

   DB_PASSWORD=MyPass

Hopefully this should work for you guys, Otherwise all the best and try to find the resolution and please let me also know.

回答3:

Check the php.ini file in the following location C:\wamp\bin\php\php5.6.35\php.ini and add the following extension if it's not already present.

extension=php_pdo_sqlsrv_56_ts.dll
extension=php_pdo_sqlsrv_56_nts.dll

This will also resolve the problem.