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'),
];
}
}
?>
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?
and mydatabase is a dummy name, but in the model you specify as tableName()
public static function tableName()
{
return 'schema2.transaction';
}
So if ‘schema2’ is a different db than ‘mydatabase’ check that you can access to schema2.transaction using user/password specified in the configuration.
If ‘schema2’ is the same of ‘mydatabase’, then try to remove schema2 from the table name.
Another test you can do is to activate debug toolbar and check the query that is being execute if is correct, copy it and try to execute the query in a external client using same connection info you use in yii
Another test is to make a 3rd connection which goes directly to schema2 and run the query using this one.
I think yii reports db error directly from the one the driver give, so the one you see is the driver answer.
mydatabase is the database name, schema2 is the name of schema inside mydatabase equal with dbo schema. i cant access that schema2, that is why i came for help. how can i make connection goes directly to schema2?
thanks, anyway i found my problem is with the sql server itself, it uses sqlserver 2000 which my sqlsrv driver didn’t compatible. After i migrated to sqlserver 2008 everything run smooth.
