Stored Procedure hits another database?

[DebiJo]

Please forgive me as I am not a SQL programmer. We have a tool, I3 for SQL performance monitoring. You can select a database, then drill down into the code that's executing and see what it does, what it hits and how it is performing.

So, we are looking in it today. And we see from our production database, code that is clearly hitting our test database. It references the test database by name (they do reside on the same server). We looked at the stored procedure, but the database name is not prefixed on any of the select statements. We checked the ODBC connections from our application server, and it appears to be fine too. How could this be happening? Something in .net or a bad index or what?

Any ideas?
Thanks in advance,
Debi
SQL 2000 SP3a

 

[Qik3Coder]

Are you creating the initial connection to the test database? Then running the stored procedure from there?

-The answer to your problem may not be the answer to your question.

 

[DebiJo]

I'm not running the stored procedure myself. The stored procedure is being executed from a .net program. This is a large system we bought from software vendor. I don't know how to see the .net code. The ODBC connection from the app server only points to our production database.

Thanks again,
Debi

 

[gmmastros]

There can be other code that is run during a stored procedure. If the proc is adding, updating, or deleting data, then there could be triggers on the table that incorrectly reference your test database.

Your procedure could also be calling a user defined function or even another stored procedure that directly references your test database.

If the problematic code is in a function or another stored procedure, this code may help you find it.

Select * 
From   Information_Schema.Routines 
Where  Routine_definition LIke '%[!]NameOfTestDatabase[/!]%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

I think it is possible that there is more than one connection string active in the .net code. I would ask the vendor if they can find the problem. Possibly they have seen that problem before and will know immediately where to look. It sounds like a developer forgot to clean up some test code when deploying.

Questions about posting. See faq183-874