Post reply

Anyone have a process to save a procedure to network location, then drop it?

  • October 31, 2013 at 2:45 pm

    #279539

    I have a bunch of developers that currently use a production database to develop their reporting procedures. When they've finished with the ones they're working on that rename the procedure "z_del_ProcedureName" - this way I know it's okay toast them.

    I'd like to know if anyone has a ready-to-go process that will fetch all the procedures in a database named "z_del_*", script them out to a valid network location as a create/drop, then physically drop them from the database.

    I'm thinking there has to be a power shell script or something similar for this - Anyone?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • October 31, 2013 at 11:03 pm

    #1663331

    Use Generate scripts , scroll and select only the necessary SP's.

    By the way why are the developing it on a prod server , don't you have dev server fro this and where will the testing happens and how will you check the deployment then?

    Regards
    Durai Nagarajan

  • October 31, 2013 at 11:04 pm

    #1663332

    one more question , Why developers have access to production?

    Regards
    Durai Nagarajan

  • October 31, 2013 at 11:19 pm

    #1663333

    Thanks for the suggestion, much appreciated - I know I could do that but I'm looking for an automated way it can be done (something I can place in a recurring job).

    In a perfect world they wouldn't have access to production. However, this is not the case and it's something I have to live with for the foreseeable future as their DEV server is being built.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • October 31, 2013 at 11:24 pm

    #1663334

    You could select the process from the information_schema.routines view then loop through them with a drop procedure TSQL statement.

    Edit. Additional details.

    Then just select out the procedure details from the routine_definition field into a table, push that table to the file system then delete the procedure if required.

  • November 1, 2013 at 1:34 am

    #1663350

    What about creating a stored procedure database on your server.

    1. Loop through sys.procedures for _del_

    2. Fetch text from syscomments definition from sql_modules

    3. Create proc on stored procedure database

    4. Delete procedure from prod database

    5. Backup stored procedure database to network location

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • November 19, 2013 at 12:48 pm

    #1667551

    Through Google searching I've come up with a great way to automatically script out the procedures, but currently am having trouble getting the delete portion to work

    Powershell script$rootDrive = "C:\Temp\"

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

    $MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")

    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "NAS2-RPTPROD1"

    $dbName = $srv.databases["SSRS_Run_Reports"]

    $procs = $dbName.StoredProcedures

    $MyScripter.Server=$srv

    #STORED PROCEDURES

    if($procs -ne $null)

    {

    foreach ($proc in $procs)

    {

    #Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"

    if ( $proc.Name.IndexOf("z_del_") -eq 0)

    {

    $fileName = $proc.name.Replace("/", "")

    "Scripting SP $fileName"

    $scriptfile = "$rootDrive\$filename.sql"

    $MyScripter.Options.FileName = $scriptfile

    $MyScripter.Options.AppendToFile = "False"

    $MyScripter.Script($proc)|out-null

    $proc.drop()

    }

    }

    }

    Generates this error on the $proc.drop():

    An error occurred while enumerating through a collection: Collection was modifi

    ed; enumeration operation may not execute..

    At C:\Temp\test.ps1:16 char:12

    + foreach <<<< ($proc in $procs)

    Any suggestions?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply