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

  • Xamarin.Forms 5 Preview Ships Ahead of .NET 6 Transition to MAUI

    Microsoft shipped a pre-release version of Xamarin.Forms 5 ahead of a planned transition to MAUI, which will take over beginning with the release of .NET 6 in November 2021.

  • ML.NET Improves Object Detection

    Microsoft improved the object detection capabilities of its ML.NET machine learning framework for .NET developers, adding the ability to train custom models with Model Builder in Visual Studio.

  • More Improvements for VS Code's New Python Language Server

    Microsoft announced more improvements for the new Python language server for Visual Studio Code, Pylance, specializing in rich type information.

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

Upcoming Events