Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FAQs, bugs and tips from the author


FAQs, bugs and tips from the author

Author
Message
Chris Goldsmith
Chris Goldsmith
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 151
Article: Creating a recycle bin for SQL Server 2005\2008

Hi,
Thanks everybody for the great feedback (in both the forums and to my personal email) on my article. I’m glad it was well received – makes the effort worth while.
I’ve been quite busy since the post but now have time to reply.

I’ve been asked a few questions so I’ll attempt to answer all of them here in the one post.

Chris

Q: Can I have the complete code?
A: Both scripts are with the original article. See the resources section right at the bottom.

Q: What about renaming a procedure, view etc?
A: Good point. Chad Folden emailed me and said he was trying to modifiy the code to do just this. He ran into an issue and hasn't got back to it. He's sent me the code to have a look too. Stay tuned if I get time I'll work on it and post what I find.

Q: Are there any additional considerations for items that are schema bound or have foreign keys associated to them?
A: Yes. As a table is only renamed, not dropped, the FKs will stay on the renamed table. This could get nasty quite quickly. Enhanced code in the DDL trigger could ignore the table rename (pseudo-drop) if it has FKs and warn the DBA.
Jack Corbett has kindly addressed this in the posts.

Q: Why you didn’t do it at the database level?
A: I thought about it for databases. Of course it’s possible but I was more interested in achieving object level UNDROP functionality at the time.

Tip: Great tip by Noel McKinney for putting double underscores in the SP name when in the master. From Noel "However, I use two underscores, for example sp__whyamihere, to avoid collision with current or future stored procedures provided by Microsoft."

Bug:
Chad Folden was kind enough to find a bug and send a fix to me, Chad’s also been working on modifying the script to cater for ALTERs.

From Chad:
"There were two small things that screwed me up here:
We use windows authentication for SQL users and our domain name contains a dash. The dash in @login_name causes the ALTER SCHEMA statement to fail (with incorrect syntax near ‘-‘)
We use windows authentication for SQL users and our user names contains a dots. This also causes the ALTER SCHEMA statement to fail because it thinks the dots are part of the delimeters in the object name
So once I replaced the dots and dashes for the @Login_name variable, your trigger worked flawlessly! Here’s the line in question and the fix I used"


SET @Login_name = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') --who dropped
SET @Login_name = REPLACE(REPLACE(@Login_name,'-','_'),'.','_')

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search