DevDisasters
DBA Goes Columbo on 'Invalid Object'
Mike worked as a SQL developer for a non-profit, debt management company that handled student loans. The developer environment consisted of IBM Lombardi Business Process Management software running within JBOSS application servers with SQL Server 2005 databases on the back-end for storage. The team used a dedicated Java Database Connectivity (JDBC) driver for each SQL Server database to take advantage of database connection object pooling and improved performance.
This worked pretty well, until one day, Mike's manager got an "Invalid Object" error. It happened at 6:00 a.m., but after a few seconds this error went away, and everything was back to normal.
After a few days, the "Invalid Object" error came back again. This time it stayed a little longer, for a few minutes. When Mike finally got permission to look at it in production, everything was back to normal. Mike had no clue why this happened and could not reproduce it in the development environment.
Low Profile
He decided to set up a profiler to log all database activities. The profiler had been running for a few days, but the "Invalid Object" error didn't occur at all. Because the profiler slowed down the production system, it had to be stopped.
Immediately after the profiler was stopped, the "Invalid Object" error came back again. This time it stayed for much longer, even after Mike had the profiler restarted. Now he had a big trace file to investigate.
From the profiler trace file, he saw the query that had caused the error:
Select * from customer
This query was executed through the custJDBC connector. The custJDBC connector was set up to be against the CustomerDB database, and the Customer table was a valid table within the CustomerDB database. But this query just couldn't find the customer table in the customerDB database. Strange!
Google Query
Mike searched on Google for the "Invalid Object" error message and found tons of answers. He verified many of them, such as "check the spelling" and "check the permission," but none of them solved the problem. He also ran the query in SQL Server Management Studio, and, sure enough, there was no problem with querying the customer table in the CustomerDB database.
With no success looking at the query itself, Mike started to look at the SPIDs. An SPID is the session ID of the current user process. Because his team used the same user to connect to the same database through the same JDBC connector, the SPID was the same for all queries to the same database. Mike loaded the trace details to a SQL database, filtered the trace records for that SPID, and saw a suspicious SQL statement with the same SPID, just prior to the "select * from customer" SQL query:
Use ProductDB
ProductDB was another database on the same server as the CustomerDB database, so apparently the database context for the custJDBC connector had been changed from CustomerDB to ProductDB.
No Use
Finally, it turned out that Eric, a report developer, had written that piece of code. Because Eric didn't know how to create a new JDBC connector, he reused the custJDBC one, but started his SQL with a "use ProductDB" statement (based on his Microsoft Access experience).
This report was only executed once in a while, and that's why the problem occurred intermittently.
As a result, Mike's team added a rule to their code guidelines: Don't put use statements in SQL code.
About the Author
Kathleen Richards is the editor of RedDevNews.com and executive editor of Visual Studio Magazine.