Django ProgrammingError [42S02] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Invalid object name

5
votes

I am using Django version 2.0.2. My Django application is connected to MS SQL (connection below).

  DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'projectcreation',
        'HOST': 'Host_name',
        'OPTIONS': {
        'driver': 'ODBC Driver 13 for SQL Server',
        'dns': 'projectcreation'
        },
}
}

So, I am getting the following error when trying to access my Django application:

django.db.utils.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'projectcreation_partner'. (208) (SQLExecDirectW)")

I believe that when I created tables (makemigrations+migrate) instead of creating tables that look like this: [projectcreation].[dbo].[projectcreation_partner] it created tables like this [projectcreation].[username].[projectcreation_partner]. So instead putting dbo which I actually expected to see it put my username..

I suppose that when the app tries to connect to db it cannot find it because it is looking for one containing dbo not username.. Of course, I could be wrong..

If you need more information please let me know, any help is greatly appreciated.

Note: both db and application are called "projectcreation", "partner" is name of one of the models.

3
sql-serverdjango
it is the same as your default db in connection: default db will be used if other was not specified; dbo scheme is default in sqlsrv - is used if other is not specified. I mean seems that you predicted the reason correctly. - revoua
@revoua thanks a lot for your reply! Ok, so if I understood correctly you are suggesting me to put "username" instead of "default"? - GileBrt
no, you need to specify scheme in query or repopulate data without scheme (or put dbo instead of username) - revoua
@GileBrt In SQL Server, you can set a default schema for a user. In SSMS, if you go to Databases -> [DBNAME] -> Security -> Users -> [username], click the General section. What does it say for "Default schema"? I keep mine set to dbo. - FlipperPA
@FlipperPA Thank you for your comment on where to find this default schema for db users. It helped me fix it. :) I'll post the solution later. - GileBrt

3 Answers

2
votes

Here is what worked for me:

Firstly, I deleted all the tables belonging to this "username" schema (backed up the data first).

Secondly, before doing the migration, I changed my default schema in SQL Server to "dbo". For some reason my default schema was named after my username, this was what started the problem in the first place. To change your default schema go to Databases -> [DBNAME] -> Security -> Users -> [username]. In Properties under General you should be able to change "Default schema" field's value.

And, thirdly I migrated. First with this command: 

python manage.py migrate --fake projectcreation zero

Note that the "projectcreation" is the name of my app. After that, this command:

python manage.py migrate projectcreation

What this did is rollback to the initial migration (zero), and apply it. Without this rollback, there were no changes in the database.

And that's it.

0
votes

It could also be that in your models.py, you have

class tblOne(models.Model):
    col1 = ...

    class meta:  # This should be upper case 'M', so it should be 'class Meta'
        managed=...
        db_table='...'

When it should be

class tblOne(models.Model):
    col1 = ...

    class Meta:
        managed=...
        db_table='...'

In my case,

Because of this little typo, it caused my migration files to be mis-configured (Some of the models' manage=False option wasn't be written in the migration files when I did makemigrations), and so the django decided to create some tables in one database, but not all.

So when Django started referencing the mis-configured table, Django wasn't sure where to look for it (Similar to your case, but in my case, Django would be looking for the misconfigured table projectcreation_partner, when it should be looking for table partner).

0
votes

I had faced a similar issue, this is because some of Django related tables are not available in the database. I have executed the below command to resolve my issue.

python manage.py migrate