0

I am posting this question after being completely drained of any possible solution I could think. I was working on a WPF application which was connected to a DB (provided via CONFIGs). Few months back, the DB was changed (let's assume initially the DB name was DB1 which got changed to DB2). Now, even I have updated the DB in all my CONFIG files, my application doesn't run stating the following reason:

Could not locate stored procedure "ABC"

Now, this SP is present in the new DB i.e. DB2 but not in DB1 After analyzing the trace via SQL Profiler, it seems the application is sending few queries to DB1, and the rest to DB2.

I have tried the following steps:

  1. Find the reference of DB1 in my entire C drive. It was in VS Backup Files and SQL Temp Files. Removed the entire VS Backup Files and SQL Temp Files
  2. Clear the cached data from DB1 and DB2 both

None of the above approaches have helped in resolving the issue. Can anyone help me with this creepy SQL issue. It would be of great help.

Thanks in advance.

Gaurav Ahuja
  • 885
  • 1
  • 7
  • 11
  • Without the code that's running, not sure what you're hoping from us here, I'm afraid. – Thom A Jun 03 '20 at 11:57
  • Did you look if you have any hard coded connectionstrings in your source code? – Steve Jun 03 '20 at 11:57
  • 1
    Check the default database for the login you are using. Perhaps the database context is not specified in the connection string at all so it falls back on the login's database. That would explain why you can't find the issue searching for DB1. The solution may be changing the login default database to DB2. – Dan Guzman Jun 03 '20 at 12:10
  • Well, I am not sure if SQL Server caches the connections made by an application or it everytime picks the connection from the config. Looking for help around that area only. There are no hard-coded connection strings in code. – Gaurav Ahuja Jun 03 '20 at 12:11
  • @DanGuzman how do I change the login default database? – Gaurav Ahuja Jun 03 '20 at 12:12
  • @GauravAhuja, `ALTER LOGIN YourLogin WITH DEFAULT_DATABASE = DB2;`. This can also be changed in SSMS via the login properties under Security-->Logins in Object Explorer. – Dan Guzman Jun 03 '20 at 12:19
  • But don't do that - because that relies on an assumption. Your connection should determine the database to use for all tsql queries in your application. Do you intend to change the default database for every login every time you want to use a different environment? That leads to madness! Quite frankly - fix the application code. – SMor Jun 03 '20 at 12:50
  • Option 1: "Take Offline" DB1. Run your app. See where it crashes. Debug this code. Other suggestions: It is also possible that database name is built dynamically somewhere, maybe you have some linked servers or other sources pointing to your old DB. As @DanGuzman suggested, maybe some of your logins have default database specified. SQL code in either the app or the database is using 3 part names ([dbname].[schema].[tablename]). – Alex Jun 04 '20 at 02:29
  • Do you use SQL Server [synonyms](https://learn.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-ver15)? – Alex Jun 04 '20 at 02:36
  • Did you manage to resolve this issue? – Alex Jun 10 '20 at 09:31
  • 1
    @Alex yeah my database name was being fetched from environment variables instead of CONFIG files. Just removed the key from env. variables, and it worked. – Gaurav Ahuja Jun 10 '20 at 12:41

0 Answers0