Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kmcvior
Starting Member
3 Posts |
 Posted - 2002-03-19 : 11:25:30
|
| We recently implemented procedures that have caused associated tables to be dropped and re-created by the database.I added an INSERT trigger to a SQL2k table. The trigger was the execution of Stored Procedure "A". "A" selected the inserted row into a cursor. Then, based on certain attributes of the fetched variables (gathered from other tables), "A" passed the attributes to either stored procedure "B" or "C".We then noticed that all tables associated with procedures "A", "B", and "C" had no data and identical create dates (Down to within one second of each other). All indexes were dropped.All procedures were tested and developed in SQL 7.0 and then moved to production on the SQL2k server.It is a mystery how this could have happened. HELP! |
|
|
Jay99
468 Posts |
Posted - 2002-03-19 : 11:34:01
|
Usually this type of thing indicates either a bug in the code, an mix-up when migrating to 2k, a bug in sql server, or in some cases, it has been reported that it was something else.It should be noted that in some extreme situations there is magic involved.   How bout some more details/hints/clue/code . . .Jay<O> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-19 : 11:40:48
|
| I imagine you dropped the production tables when they were "moved" from staging ...setBasedIsTheTruepath<O> |
|
|
|
kmcvior
Starting Member
3 Posts |
Posted - 2002-03-19 : 11:54:04
|
OK Here's some more detail (but not all in order to keep this understandable and avoid passing through a couple hundred lines of code.  ):When a record in inserted into Table "A" a trigger executes a stored procedure, Proc "A", passing various field values from the table.Proc "A" Then opens a cursor gathering data from other tables (tables "B", "C", and "D") using the passed,incoming variables.If condition "X" is met the cursor is closed and the new set of variables is passed on to Proc "B", which creates yet another cursor and executes various other stored procedures and SQL statements (none of which involes a TRUNC, DROP or DELETE)If condition "Y" is met the cursor is closed and the new variables are passed on to Proc "C", which creates yet another cursor and executes various other stored procedures and SQL statements (none of which involes a TRUNC, DROP or DELETE)If condition "X" or "Y" is met Proc "A" then deletes the originally inserted record using it's UniqueIdentifier.After moving this from Dev (SQL2k/NT4.0) to Prod (SQL2k/Win2k) we discovered that Tables "A","B","C", and "D" all had new and almost exactly similar create dates - as if they'd been dropped and re-created!It's crazy, I tell you! |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-19 : 12:30:24
|
Well, from your description, I don't see any reason why....it must be magic....Story time...Once upon a time, at a client site far,far away, I young dba (soon-to-be Yak Master), scripted out a bunch of of procs that he had written.if exists ... drop proc xcreate proc x ....goif exists ... drop proc ycreate proc y ....go...if exist ... drop proc blahcreate proc blahgo The mental-infant, ran the script into the production database, all of the new procs where created, and a peaceful calm rolled across the client site.Now, a couple days later, our young hero experienced a little something called shifting-requirements (aka scope-creep). He was scared .... yes .... but with Books OnLine held firmly to his breast, he made the change to proc blah to satisfy the new requirements. However, just when he though all was well, he noticed that proc blah had reverted to its pre-scope-creep version! Fear and wonder invaded the young hero's mind and he immediately recompiled the proc, injecting the scope-creep logic. But wait, you guessed it, before his very eyes it reverted back to is old version. . . .Now, much like your situation here, the hero went over and over in his head, 'Why is this happening? This is crazy? HELP HELP The world doesn't make sense'. It was only after he calmed down and told the barkeep, 'No sir, four is enough for a decent liquid-lunch', did he realize that his original script (the one way at the top of the message) was missing a 'GO' between two procs. So, whenever someone executed proc (blah-1), it would drop and recreate proc blah.The moral of the story . . . you probably f-ed something up and until you stop thinking things like 'this is crazy' and 'everything is exactly the same', you won't find the error. I can (pretty much) promise you that MS didn't re-write cursor* logic so that it drops and recreates the tables that it references . . .*Cursors are for the iterative. Set-Based is the true path!Jay<O> |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-19 : 12:49:34
|
| You especially notice this if you use Enterprise Manager to script out the procs and you check the "Generate scripts for all dependant objects" check box.All tables that the proc references will be rebuilt.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-19 : 12:57:11
|
| preach the good word jay setBasedIsTheTruepath<O> |
|
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-20 : 06:30:27
|
| is <0> some secret code for Set Based Is The True Path??============The Dabbler! |
|
|
|
|
|
|
|
|
Stored Procedure caused tables to be dropped