How do we troubleshoot a SQL script again?
-
So, we manage database changes here by having a script for each object (proc, view, etc) and schema change, which is fine. But all those scripts have to be checked into two places, since the process used on development machines to update the databases (shell out to osql for each script, for each database, and they wonder why it takes so long to run builds...) is different than on the build server which involves pulling all those scripts from the other location they have to checked in to, and then concatenating them in various combinations for a total of 5 scripts. At least two seem to be unused and one that isn't used often. One script creates a new database from scratch, the other updates from the previous database version to the current. Please note that there is also no way to create a schema at a point in time for a previous version, or to even know fully what changed in older versions, since the changes for the current version are copied into the folders that create the "new" script at the end of each development cycle. Source control is useless for this, as it's sourcesafe, and there's no atomic checkins, or even a decent change log.
I will also note that the program that does these operations on the scripts was written by previous employees, and after 15 minutes of waffling, one of the other developers could not give the boss a good explanation on what each of the scripts did, something that I managed in about 5 minutes, just poking around.
We're currently having a problem with the script that gets output by this program doesn't run on the build machine, which is holding me up, as I'm working on the next version of our software, which involves un-registering and re-registering a bunch of VB6 COM DLLs, so I don't enjoy doing that often. The individual script files work, but when combined, they don't.
The running theory is there's some stray GO in the script in a comment or something causing the program that updates the database to break, since that seems to read in this script, split it on GO, and then run each one. Instead of using, oh, I dunno, SMO. But they also haven't done any testing to establish what the actual problem is. So they've been going back and forth doing voodoo, changing one thing, seeing if it runs, if it doesn't try again. This has been going on all day.
I don't have the heart to ask why they don't fire up SQL Analyzer, capture a trace while the updater is running, and see where it bombs out. Or updating the updater program for some additional logging.
tl;dr: Co-workers trying to troubleshoot bass-ackwards legacy program without actually doing any analysis
-
-- You build/update your dev database using a different process than production?
-- You can't create a schema at a point in time for a previous version, although you're using source control which could pull out a version of the files at a particular point in time or for a label?
-- Who is responsible for delivering the scripts to Prod/combining the scripts? Dev?
-- This has been going on all day; you know a way for them to identify the issue quickly, but haven't told them about it?
-- unregistering/reregistering vb6 com dlls, by hand apparently, not in a batch file.
Seems to me that the real problem is the people in dev.
-
@DrPepper said:
-- You build/update your dev database using a different process than production?
Completely different build scripts for the two. But around here, "That's the way we've done it, and it works"... until it doesn't
@DrPepper said:You can't create a schema at a point in time for a previous version, although you're using source control which could pull out a version of the files at a particular point in time or for a label?
We use SourceSafe for source control (See above for the reasoning). Something else I've been pushing to get changed. No labels either, though SourceSafe supports them. Someone copies what's in the dev folder into a "production" folder. Sometimes.
@DrPepper said:Who is responsible for delivering the scripts to Prod/combining the scripts? Dev?
Some program on the build server. I don't know if I've ever seen it.
@DrPepper said:This has been going on all day; you know a way for them to identify the issue quickly, but haven't told them about it?
While I could be helpful with them, neither seem to be the type to apply critical thinking in the future, so if I take ownership of this, then I'll be the one in charge of troubleshooting this thing all the time. It doesn't help that one perceives me as a threat...
@DrPepper said:unregistering/reregistering vb6 com dlls, by hand apparently, not in a batch file.
Oh, there's a batch file that works, sometimes. If I don't run the build script that rebuilds all the VB6 DLLs even if they don't change, and project/compatibility isn't turned on, so the GUIDs change frequently, and I have to make sure I expunge the registry of this stuff. And switch the IIS web root, since this application can only be run from the root of a domain (hard coded)
-
@bardofspoons42 said:
But they also haven't done any testing to establish what the actual problem is. So they've been going back and forth doing voodoo, changing one thing, seeing if it runs, if it doesn't try again. This has been going on all day.
These people breathe? No wonder we're running out of oxygen!
-
Don't let Ben L know that GO could be breaking your scripts!
-
-
@joe.edwards said:
@Zemm said:
Meh, probably doesn't matter anyway. His denial is powerful enough to easily ignore that.Don't let Ben L know that GO could be breaking your scripts!
+1
