Select Schema and table in sqlserver with Yii2?

Question

i have sql server database which have multiple schema. i tried to select from one of table from different schema using sqlsrv driver but is says invalid configuration, The table does not exist. how can i select the schema in table using sqlsrv driver?

myconnection

<?php 
    return [
        'class' => 'yii\db\Connection',
        'dsn' => 'sqlsrv:Server=192.168.10.70;Database=mydatabase', // MS SQL Server, sqlsrv driver public ip
        'username' => 'user_username',
        'password' => 'user_password',
        'charset' => 'utf8',
    ];
?>

my model

<?php
    namespace app\models;

    use Yii;
    use yii\db\ActiveRecord;

    class Transaction extends \yii\db\ActiveRecord
    {
        /**
         * @inheritdoc
         */
        public static function tableName()
        {
            return 'schema2.transaction';
        }

        public static function getDb()
        {
            return \Yii::$app->db3;  // use the "db3" application component
        }

        /**
         * @inheritdoc
         */
        public function rules()
        {
            return [[['id', 'number', 'amount', 'date', 'status', 'description'], 'required']];
        }

        /**
         * @inheritdoc
         */
        public function attributeLabels()
        {
            return [
            'id' => Yii::t('app', 'Uid'),
            'number' => Yii::t('app', 'Number'),
            'date' => Yii::t('app', 'Date'),
            'amount' => Yii::t('app', 'Amount'),
            'description' => Yii::t('app', 'Item Description'),
            'status' => Yii::t('app', 'status'),
            ];
        }
    }
?>

Did you can using Yii2 Migrate for create table into SQL Server 2008? — Blackjack, Nov 30 '17 at 14:41

score 3 · Answer 1 · answered Jan 16 '15 at 07:51

3

The full table name in MS SQL contains three parts: schema, owner, and table name, for example:

dbo.user1.table1

This means that the table1 was created by user1 in the schema dbo.

If you don't know the owner or it is default user (sa), you can pass it like this:

dbo..table1

So I suggest you to try out

return 'schema2..transaction';

or

return 'schema2.YOUR_USER_NAME_HERE.transaction';

for the correct resolving the table name from different schema.

answered Jan 16 '15 at 07:51

VMAtm

27,943
17
79
125

tried your solution, using schema2..transaction returned SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot use empty object or column names. Use a single space if necessary. 2nd solution return Invalid object name – Gamma Jan 16 '15 at 08:31
this is weird, when i try schema2.xxx it returned Invalid object name that is because there is no table xxx but when i tried schema2.transaction it said table not exist. is it bug in yii? – Gamma Jan 16 '15 at 10:03
I've tried to create table into SQL Server through Yii Migrate, but It only contain 2 parts, example: root.user. It cause an Issue when using GII, because GII can read any table in the database, how do I can make the table can contain three part, example: dbo.root.user? – Blackjack Nov 30 '17 at 14:45
You probably should ask your own question, not comment other's answers – VMAtm Nov 30 '17 at 20:01

score 0 · Accepted Answer · answered Jan 27 '15 at 02:51

0

well i found the answer. I can't access it because the database is using sqlserver 2000 and my driver did not compatible with it. After i migrate it to sql server 2008 everything crystal clear. Hope this helps anyone.

answered Jan 27 '15 at 02:51

Gamma

331
1
3
15

Select Schema and table in sqlserver with Yii2?

2 Answers2