MS SQL - dblib and define the default Schema

Yii 2.0 Installation and Setup
1

Good day.

I have been working with Yii2 framework for about 2 months now. I recently had to connect part of solution to a MS SQL 2008 server instance. I therefor have 2 db configs defined (one to a local MySQL instance and one then to my MSSQL … as ms_sql)

In the model I set the db connection to the db_ms configed sample.


'db_ms' => [

            'class' => 'yii\db\Connection',

            'dsn' => 'dblib:host=db_mssql.ip.com;Database=crm',

            'username' => 'user',

            'password' => 'password',

            'charset' => 'utf8',

        

        ],

And then in the model, getDb for my MSSQL instance (as in above example):




public static function getDb()

    {

        return \Yii::$app->db_ms; 

    }

Everything works fine (It connects to my MSSQL instance and pulls and inserts data etc) Note still using the Active Record queries.

On the connection to the production MSSQL I have issues in the queries, and I think it is because I do not define he resource/schema table names (I only specify table names).

I am trying to figure out, how do you define the default schema globally?

I do not want update queries in every model to include schema_name.dbo.table_name, is there a way to define schema_name.dbo as default for the dblib config and have working active queries using just table name references.

In the model the query I try to run (which is using a relation to another model) is:




$query = TicketClientRecords::find()->with('clientsPersonalRecords');

        $query->innerJoin('ClientsPersonalRecords', 'ClientsPersonalRecords.ifkTicketClientRecordID=Ticket_ClientRecords.ipkTicketClientRecordID');

        $query->andFilterWhere(['=', 'Ticket_ClientRecords.ifkCallCentreUserAssignedID', '0']);

        $query->all();

If that makes sense?

The error I am getting is:




SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)]

The SQL being executed was: SELECT [Ticket_ClientRecords].* FROM [Ticket_ClientRecords] INNER JOIN [ClientsPersonalRecords] ON ClientsPersonalRecords.ifkTicketClientRecordID=Ticket_ClientRecords.ipkTicketClientRecordID WHERE [Ticket_ClientRecords].[ifkCallCentreUserAssignedID] = '0'

Thanks in advance