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

Question

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.

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, Mar 06 '18 at 22:35
@revoua thanks a lot for your reply! Ok, so if I understood correctly you are suggesting me to put "username" instead of "default"? — GileBrt, Mar 06 '18 at 22:47
no, you need to specify scheme in query or repopulate data without scheme (or put dbo instead of username) — revoua, Mar 06 '18 at 22:50
@revoua I am thinking of deleting the tables and just doing the migrations again, I can more or less easily insert the data again... but I am clueless of how it came to this "username" thing in the first place... Do you maybe have an idea? — GileBrt, Mar 06 '18 at 22:56
as I understand Django by default makes named migrations; https://stackoverflow.com/questions/1146599/how-do-i-change-db-schema-to-dbo — revoua, Mar 06 '18 at 23:12
@revoua I thought that django by default chooses dbo schema, at least it was like that for me before. For sql server default schema is dbo. [link](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server) — GileBrt, Mar 06 '18 at 23:23
https://stackoverflow.com/questions/1160598/how-to-use-schemas-in-django — revoua, Mar 06 '18 at 23:30
If you are using SQL Server, I would *highly* recommend using the default `dbo` schema. Django isn't set up to use multiple schemata, without some unsupported hacks. — FlipperPA, Mar 07 '18 at 01:54
@FlipperPA thanks a lot for participating! I didn't create this "username" schema by choice.. I really don't know why Django translated the model to database like that.. this is the first time I am using Django 2.0.2 with mssql server, is it maybe something that 2.0.2 does? When I used the Django 1.8 and 1.11 the tables created in mssql server were always a part of "dbo" schema.. — GileBrt, Mar 07 '18 at 08:09
@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, Mar 07 '18 at 11:28
Also, are you using `django-pyodbc-azure` as your Django DB engine? — FlipperPA, Mar 07 '18 at 11:29
@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, Mar 07 '18 at 11:52

GileBrt · Accepted Answer · 2019-11-08T13:39:48.943

2

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.

edited Nov 08 '19 at 13:39

answered Mar 12 '18 at 15:39

GileBrt

1,830
3
20
28

In my case, go to Databases -> [DBNAME] -> Security -> Users -> [username] and username is "dbo". But it still shows the "Invalid object name" error is given below. ('42S02', "[42S02] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'accounts_studentprofile'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") – miltonbhowmick Nov 30 '19 at 05:56
In my case, it was I had lower case 'class meta:' where it should have been 'class Meta:'. This caused my migration files to be mis-configured (The manage=False option wasn't be written in the migration files when I did makemigrations), and so the django wasn't sure where to look for the correct tables. – Yi Zong Kuang Jan 02 '21 at 09:08

score 0 · Answer 2 · answered Jan 02 '21 at 09:22

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).

score 0 · Answer 3 · answered Sep 08 '21 at 13:34

0

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

answered Sep 08 '21 at 13:34

Sathiamoorthy

8,831
9
65
77

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

3 Answers3

Linked