Trigers and procedures become invalid automaticaly

Question

Trigers and procedures become invalid automaticaly

Amin Ali Khan

SSC Rookie

Points: 48
More actions
January 17, 2008 at 11:30 pm

#119690

Hi to all
I have been appointed as DBA recently in company . the application and database was developed in VB and SQL server by former DBA who left. for a few days some of the procedures and triggers become invalid automatically. and one thing more the former DBA also took with him the code. know we just have the exe
please help me

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'become invalid'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amin Ali Khan SSC Rookie Points: 48 More actions · Answer 2

I am new on SQL server. I have worked on Oracle but very little experience of SQL Sever. I mean i have to compile the procedure and triggers again and again every time the system is restarted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Compile by running them?

By recreating them?

By calling sp_recompile (Which actually does nothing other than mark the execution plan to be recompiled on next run) ?

Some other way?

What happens if you don't 'compile' them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLWannabe SSCrazy Points: 2842 More actions · Answer 4

Is it possible that when the former DBA left, his account was disabled, and the VB app is using a connection with his credentials? I've experienced that before when someone created some apps with his personal credentials. When he left, a bunch of stuff blew up and it took us forever to figure out why.

Amin Ali Khan SSC Rookie Points: 48 More actions · Answer 5

thank you all for your information

And sorry for answering late.

what i actually figured out is that, the former DBA have written some sort of code in the application which actually drops the procedure and triggers. specifically between 1 pm and 2 pm every day. is there any way that i stop these drop commands else i have to recreate these procedures and triggers everyday. fortunately i found old backup from which i extracted those procedures and triggers.

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

find out if there is any job/maintenance plan running at that time to drop the procs, if yes then stop that job or maint plan.

"More Green More Oxygen !! Plant a tree today"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Hi

If its a job thats dropping and recreating the procs disable it.

Otherwise change the code that does this

and if for any reason u cant change the code use a DDL Trigger that

rollsback the dropping of a procedure and creating of a procedure, but this would be the least preferred option.

Any idea why this dropping and recreating of the procs?

"Keep Trying"

Ian Yates SSCoach Points: 19738 More actions · Answer 8

If you cannot change his code's behaviour (ie the dropping of procs is not a server-side job) then you could (and it's clumsy) create a job to run every 5 minutes in that uncertain hour to create the procs, triggers, etc if they're not present. In fact you could run this every few minutes on your server.

When you script the (for example) triggers, chances are the script will start with an

IF NOT EXISTS(select * from ....)

to ensure that you don't try to create the object twice and receive an error. This IF check should be fast so you could run the code very often and not have much load on your server.

Amin Ali Khan SSC Rookie Points: 48 More actions · Answer 9

I thank you each and every individual

SSC Eights!

Mr or Mrs. 500

SSC Veteran

Grasshopper

SSCrazy

Minaz Amin

for such a great support and help.

Well i have checked and there is no jobs that is dropping the procedures and triggers. and the idea to create a job that creates the procedures and triggers is great, hope this will solve the problem until we find the permanent solution. lets hope !!! its almost time 1 pm, the problem is approaching

Regards

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

Ian Yates (1/20/2008)
If you cannot change his code's behaviour (ie the dropping of procs is not a server-side job) then you could (and it's clumsy) create a job to run every 5 minutes in that uncertain hour to create the procs, triggers, etc if they're not present. In fact you could run this every few minutes on your server.
When you script the (for example) triggers, chances are the script will start with an
IF NOT EXISTS(select * from ....)
to ensure that you don't try to create the object twice and receive an error. This IF check should be fast so you could run the code very often and not have much load on your server.

Would this be better than a DDL trigger ?

"Keep Trying"