June 5, 2019 at 4:26 pm
Hello, I want to create a script to delete a Stored Procedure. Below is my current script but I am getting error messages. Is it possible to delete PROC in this manner? I am looking to delete a specific PROC that is in multiple databases.
SQLCMD -S MyServerName -d myDBNAME -E -i DROP PROCEDURE myPROCname.sql
June 5, 2019 at 5:35 pm
You can delete a stored procedure using SQLCMD but I can't tell exactly what you are doing in this one.
If you have a script to run, just call that using -i MySQLScript.sql. And your script would have the DROP PROCEDURE statement.
If you aren't using a script, you would do the drop procedure by putting in the query after the -Q (or -q) option to excute: DROP PROCEDURE SchemaName.SomeProcedureName
It almost looks like you may be combining the two approaches. Or it could be something else as we have no idea what error you are getting.
Sue
June 5, 2019 at 5:57 pm
If you want to ask a question which is related to an error message you get, you should always include that error message.
It would also have helped if you and included the contents of your file.
The command you have posted is incorrect, because it is setup in this way:
-S MyServerName - You specify the server. So far so good.
-d myDBNAME - The database, OK.
-E - You specify integrated security.
-i DROP - You tell SQLCMD to read input from the file DROP (a filename without extension.)
PROCEDURE and myPROCname.sql - Two command-line arguments (as options to options), but SQLCMD does not accept arguments.
What your really meant to do is hard to tell. If the name of the file is "DROP PROCEDURE myPROCname.sql", you need to put the name in quotes.
If you intended to drop the procedure which is defined inside the file myPROCname.sql, no that doesn't work.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply