Deezil.us

Don’t mix and match databases, kids.

So, in my job, I have to deal a lot with Visual Basic (still currently VB 6, even though development is speeding on towards C#), and even more inside of SQL Server (2000/2005).  All of our applications are semi-self contained, with interfacing dlls to pass calls between the modules that have to communicate.  So, in all, everything is very independent.  There’s only one thing that we do that allows external communication, but it is relatively minor.  We have the ability to create a view of the data to be able to interface with 3rd party GIS solutions.  Usually, we set it up, it works, and that’s it.  That is, until something went wrong with it today.  This customer had a server changed out earlier this year, and unknown to anyone at the time, the mapping link was no longer working.  So, I took the call, let them know that the server name had changed, and that was it.  They accepted that as the workaround, and we ended the call.  Well, they called in later that day, and it still wasn’t working.  So I started digging.  Tried to set everything up as it had never had the mapping installed, didn’t work.  Tried setting up the ODBC DSN to see what was happening with it, and it wouldn’t allow the connection.  After re-setting the password on the account, and it still not connecting to the database, something connected in my brain.  When we set up a new server, we don’t keep the original master database, we use the one with the new install.  Well, the new server didn’t have the new log-in in it… until I tried to set it up earlier.  So what was up?

I then remembered that earlier, the setup of the solution had complained about something, and I thought it was just complaining about the SQL view being there.  Ran the same sql command, and looked at the other error that was right above the complaint about the view.  It complained about the login already created.  So I deleted it from the users in the master database.  And it still complained.

So, I had to dig more.

Found that the user was also stuck on the database.  But why wouldn’t it work, didn’t I just create it?  The underlying answer to all of this: no, I didn’t create it.  It had been there all the time.  There was one customer login that was with the old master database, and when the old server was turned off, it severed the connection with the external database.  The new customer login was a fully different account, just happened to share the same name.  In the end, I had to remove all traces of the login from the database, the schema, and the roles, and then have the stored procedures re-create all them.  In the end, the amount of work was 5 minutes.  The discovery took over an hour.  And the experience, as is said, was priceless.