can you suppress the "Changing any part of an object name.." message on sp_rename?
-
I've got a script to execute a huge release effort. It's a .cmd file that invokes isql from the command line. In some of the .sql files that get called, columns and tables are renamed. Dozens of them. I'd like the output to be clean only showing errors, but instead, I get dozens of these caution messages:
"Caution: Changing any part of an object name could break scripts and stored procedures."
Thanks SQL, I think I got it. Why such an annoying message?
SET ANSI_WARNINGS OFF is feckless.
-
SET ANSI_CAUTIONS OFF
Too bad that didn't work either.
The only thing I can come up with is to use OLE/ActiveX/COM+ to launch osql from within your script to execute the rename. That way the caution is reported to a seperate output pipe/file and not seen. It's a lot more complicated, has some error handling difficulties (you probably should report the output to a file so that any error messages can be seen), it will perform slower, and it requires system administrator rights. Other than that it's a splendid idea.
-
One possibility is to either modify or copy the stored procedure sp_rename in master db. Of course, the standard warnings about changing system stored procedures exist, e.g., could be overwritten with next service pack, etc. My recommendation is to make a custom stored procedure that copies sp_rename with the one modification below.
Get text of sp_rename, and comment out the line:
raiserror(15477,-1,-1)
Create a new stored procedure in master sp_rename_custom or what have you (use your company name to make it unlikely to every be overwritten), and use that in your command script. You'll have to use sp_configure to allow system updates.
Or, take the change of modify sp_rename itself. This particular modification should be pretty harmless.
No warranties expressed or implied on this one. The process should be fine, and I add my own SPs to master as needed. Modifying existing SPs is another story.
Scott Thornburg
-
nice. thanks for the suggestions gang.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply