January 17, 2008 at 11:30 pm
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
January 17, 2008 at 11:58 pm
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
January 18, 2008 at 12:05 am
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.
January 18, 2008 at 12:15 am
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
January 18, 2008 at 9:20 am
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.
January 20, 2008 at 10:21 pm
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.
January 20, 2008 at 10:54 pm
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"
January 20, 2008 at 11:13 pm
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"
January 20, 2008 at 11:31 pm
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.
January 21, 2008 at 12:12 am
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
January 21, 2008 at 11:21 pm
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"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply