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 |
|
Zim327
Yak Posting Veteran
62 Posts |
 Posted - 2009-09-04 : 16:28:11
|
| Ok, here's the situation:I noticed that 1 UDF and 1 stored proc went missing in the production db. (I tried looking in the transaction log but I don't rights to do that)I then tried to manually add the udf and proc from script. the udf went in but the proc kept failing (i forget the error code. it didn't like the fact that i was using getdate in the proc)so in desperation I used dts to copy the proc from the dev to production system. I UNCHECKED the "Copy Data" option, only selected the missing proc and ran it. Well, the proc went in but ALL the data was erased! (luckily, the production db only had test data in it. but still!)Can anyone tell me 1) what the hell i did wrong and 2) how can I confirm who or what removed the 2 objects in the first place?Thanks,Best regards, Zim(Eternal Yak God Emperor from the Future) |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-09-08 : 12:21:11
|
| Well, DTS was overkill for one thing. You should have just scripted out the UDF and the sproc and then run the script on your target database.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
 |
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-09-09 : 09:42:50
|
| that was the first thing i tried. the udf worked but the proc kept throwing an error.Best regards, Zim(Eternal Yak God Emperor from the Future) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-10 : 02:41:22
|
quote:
Originally posted by Zim327
that was the first thing i tried. the udf worked but the proc kept throwing an error.Best regards, Zim(Eternal Yak God Emperor from the Future)
What was the error?MadhivananFailing to plan is Planning to fail |
|
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-09-10 : 09:08:46
|
| Sorry I didn't record the error but if memory serves: it said something like error 1023: and it was complaining about the GETDATE() function I was using in the stored procedure. But the stored proc was identical to the one from the dev db. Both the dev and production db's are SQL 2000.Best regards, Zim(Eternal Yak God Emperor from the Future) |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-09-11 : 12:26:24
|
| have you different compatability levels on the difference databases? |
|
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-09-11 : 16:05:34
|
| Thanks for replying.I'm not familiar with that can you tell me how to check compatibility levels?meanwhile, I'll look it up... |
|
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-09-11 : 16:12:57
|
| I used this:sp_dbcmptlevel 'DatabaseName';And I got this on both db's:The current compatibility level is 80 = sql 2000 server |
|
|
|
|
|
|
|
|
DTS erased all the data!