none
How to update Stored Procedures Only

    Question

  • Hi,

    I had a deployed an application to client machine. I gave him updates several times and now i am tired to provide him updates for stored procedures one by one. Is there any way using which i can create a package of all stored procedures and update all at once without hassle. So, everything will get updated regardless of new changes. Also new procedures will be created if not exist.

    Tuesday, February 12, 2019 11:15 AM

All replies

  • yes

    You can create a database project in SSDT including all code parts and then do incremental deployment using dacpacs

    see

    https://www.codeproject.com/Articles/1211951/SQL-Server-Database-Project-SSDT-Continuous-Delive


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 12, 2019 11:29 AM
  • you could make a copy of your database, call it DB_COPY. Then run a TRUNCATE on all the tables, if you don't want him to have your data. Make a backup and give him the backup. It would be up to him to apply changes or get his data into his restored DB_Copy. 

    he could also run this query to see what sprocs had changed or been added since the last backup. He could copy out the module definition and run it to get the updated code.

    select * from sys.objects so inner join sys.sql_modules sm
        on so.object_id = sm.object_id
        where type_desc = 'sql_stored_procedure' 
        and create_date > '01/01/2018' or
        modify_date > '01/01/2018'

    • Edited by TheBrenda Tuesday, February 12, 2019 3:57 PM
    Tuesday, February 12, 2019 3:51 PM
  • I like the approach of SSDT but is there any way like publish feature of Visual Studio? Just Asking
    Tuesday, February 12, 2019 4:46 PM
  • Using SSDT is really a very good way, I will also try this method.
    Wednesday, February 13, 2019 3:36 AM