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.

comments powered by Disqus

Featured

  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

  • What's New for Python, Java in Visual Studio Code

    Microsoft announced March 2024 updates to its Python and Java extensions for Visual Studio Code, the open source-based, cross-platform code editor that has repeatedly been named the No. 1 tool in major development surveys.

Subscribe on YouTube