SQLServer and Laravel: Invalid Object Name error

Question

Following code is working if I'm using MySQL. When i switch to MS SQLServer i get error. I have already enabled the pdo sqlsrv driver. My database has a table called products, But it is showing invalid object name.

Product.php (Model)

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $connection = 'sqlsrv';
}
?>

database.php

'sqlsrv' => [
        'driver'   => 'sqlsrv',
        'host'     => env('DB_EXT_HOST', 'localhost'),
        'database' => env('DB_EXT_DATABASE', 'forge'),
        'username' => env('DB_EXT_USERNAME', 'forge'),
        'password' => env('DB_EXT_PASSWORD', ''),
        'charset'  => 'utf8',
        'prefix'   => '',
    ]

Controller:

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Product;
use App\Http\Requests;

class APIController extends Controller
{
    public function index(Request $request)
    {
        $products = Product::paginate(5);
        return response(array(
            'error' => false,
            'products' =>$products->toArray(),
        ),200);
    }
}
?>

Output:

SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'products'. (SQL: select count(*) as aggregate from [products])

What schema does the products table have? You may need to specify that as well (e.g [dbo].[products]) if the schema in question is not the database default. — J.M Smith
– J.M Smith, Commented Dec 22, 2016 at 9:19
Run that query manually from your code and reference the table as [forge].[dbo].[products] and see if you get the same error. SELECT COUNT(*) FROM [forge].[dbo].[products] — John Joseph
– John Joseph, Commented Dec 22, 2016 at 10:46
Are you sure Laravel is connecting to the same database that you're looking at? Do a dd((new Product)->getConnection()->getName(), config('database.connections.sqlsrv')) to verify the connection name and the connection details are correct. — patricus
– patricus, Commented Dec 23, 2016 at 7:24

halfer · Accepted Answer · 2019-10-20 15:50:30Z

2

I know I'm very late in party but this could help to other user.

From the documentation:

plural name of the class will be used as the table name unless another name is explicitly specified

So in your case, table name products is different then model product. just add below line into your model:

protected $table = 'products';

edited Oct 20, 2019 at 15:50

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Aug 16, 2017 at 9:27

AddWeb Solution Pvt Ltd

21.8k5 gold badges30 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J.M Smith · Accepted Answer · 2016-12-22 09:38:29Z

0

If adding the correct schema doesn't work, it could be a permission issue for your user. I'm guessing you are accessing the database with the "forge" user - does that user have select permission on that table?

You may have to run this to be certain:

GRANT SELECT ON dbo.products TO forge

answered Dec 22, 2016 at 9:38

J.M Smith

3912 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQLServer and Laravel: Invalid Object Name error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related