SqlAlchemy ProgrammingError 42000 and MS SQL

I have been working on a software inventory script lately using SqlAlchemy on Windows XP to connect to a newly created table in a Microsoft SQL Server 2005 database. I created the table using Aqua Data Studio, logging in as the SQL Administrator (sa) and thought all was well until I tried to commit some data to the table. Here’s the screwy error that I received:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "dbo.software" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')

Now, what does that mean? I know the database exists because I just created it with Aqua Data Studio (a database management suite) and I gave myself the following permissions: Select, Insert, Update and Delete. If you Google this error, you’ll end up finding four references to one thread. They don’t really have a solution in the thread, although they mention that adding permission to alter the schema might work. Note that this problem only happens when you use the declarative syntax. Anyway, at this point, you probably want to see my code, so take a look:

from sqlalchemy import create_engine, Column, DateTime, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import datetime

uri = "mssql://username:pw@mssqlServerPath/Inventory"
engine = create_engine(uri)
engine.echo = True
Base = declarative_base(engine)

########################################################################
class Software(Base):
    """
    SqlAlchemy table representation of "software" login
    """
    __tablename__ = "software"
    __table_args__ = {"schema":"dbo"}
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    date_added = Column(DateTime)
    date_checked = Column(DateTime)
    machine_name = Column(String(25))
    version = Column(String(25))
    
    #----------------------------------------------------------------------
    def __init__(self, name, date_added, date_checked, machine_name, version):
        """"""
        self.name = name
        self.date_added = date_added
        self.date_checked = date_checked
        self.machine_name = machine_name
        self.version = version
        
Session = sessionmaker(bind=engine)
session = Session()
now = datetime.datetime.now()
new_record = Software("Adobe Acrobat", now, now, "MCIS0467", "9.0")
session.add(new_record)
session.commit()

From what my boss could figure out, the declarative syntax using an identity-based query system and if the identity isn’t set, then SqlAlchemy cannot find the database. If you use SqlAlchemy to create the table, then you won’t have this problem. Anyway, it turns out that you have to use Microsoft SQL Server Management Studio to rectify this particular situation. Load it up and navigate to the correct database and table, then open the columns tree. Right-click the primary key field and choose “Modify”. There’s a “Column Properties” tab at the bottom of the screen. Go there and scroll down to Identity Specification and expand it. Finally, make sure that the “(Is Identity)” field is set to “Yes”. Save it and you’re done!

This is a pretty weird and rare issue so you probably won’t have it, but I thought it was an interesting problem and I wanted to document the solution. Note: I was using Python 2.5 with SqlAlchemy 0.6.6 on Windows XP.