standard ways of create object in existing database

  • Hi everyone,

    I usually write sql script for table, procedure and DTS package in testing database and then after finishing testing, I create script use sql analyzer and then execute in production database.

    Is this the right way or do you have a better way?

    Thanks

    Betty

  • In very short yes.  Except maybe that you need to have a backup of the production and test databases (before/after) and also you need to have a script to test that the application still works with the new changes.

  • Hi Ninja,

    Yes, backup production database before apply ing new change is a good paractice. But I am not sure "have a script to test that the application still works with the new changes."

    I usually test the application or some other applications directly if the application will use this object or some applications could be impacted by the change. But not sure how to write a script to test it.

    I like to learn, can you tell me more.

    thank you.

    Betty

  • I'm not expert on that matter.  All I can propose is that you search this site on "change management".  You should find some answers there.

  • Any process will work, it really depends on a number of things that are site dependent. Things like size, number of servers number and type of environments, use of source code control. But when you boil everything down, no matter wht step in your process you are at, as a DBA you have to ask yourself (before going on to the next step) "Can I recover" ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • One thing you might want to do is to have a script template to use from the get-go, instead of just writing in the query analyzer then scripting it out. 

    Copy the template into query analyzer, edit your code in this, applying against your database, and saving to local file.  We do this so that all procedures have standard error checking as well as standard permissions.  Here is our VSS template that we always start with - for error handling we call a stored procedure that takes parameters and composes an XML string that we send back to the application where it is consumed by the application.  We have a special error table that lists customized errors - things that the user can correct about their data entry.  If the error is one of those (instead of a generic error number) then the app will look up the 'friendly' message in our error table and handle that differently in the application.  Otherwise they use the message we set just prior to the database update call to display in a pop-up box that tells the user to call support.  We also have don't allow any users or even the app to access tables directly - only through stored procedures, so the template also sets permissions at the end:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[ProcedureName]

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE ProcedureName

    @userID      uniqueidentifier --required parameter 

    -- required test call statement

    -- exec ProcedureName /* add parameters here for testing */

     AS

     BEGIN

     SET NOCOUNT ON;

     SET XACT_ABORT ON;

     -- declare the error handling variables

     declare @ErrorLogID int   -- The value that gets put into the db tables

     declare @errStr varchar(500)  -- holds original error message returned by SQL and customer error messages

     declare @ErrorToRaise varchar(1200)  -- xml formatted varchar string returned to data requestor

     declare @errNum int   -- This will map to the error defined in the CypressError database. 

          -- The ClientSide programmers will use this information to log errors.

     -- declare and set any processing variables

     BEGIN TRY

        BEGIN TRANSACTION;

      -- Do any custom error checking - provides specific feedback to customer

      if  /* check for error condition here */

      BEGIN

       set @errNum = 50XXX  -- catalog this in the custom error database     

       set @errStr = NULL  -- this should remain null.

       -- we're passing a custom error message via RAISERROR

       RAISERROR ('The thing you''re trying to do isn''t going to work.', 16, 1)

      END

    -- Here is standard error handling (no specific customer feedback)  

    -- Set error message up in case there is a problem with update

      set @ErrStr = 'Error inserting /updating/ deleting the table record.'

      /* do stuff here */

      -- Reset error message for success afterwards

      set @ErrStr = NULL

        -- If the previous statements succeed, commit the transaction

         COMMIT TRANSACTION;

     END TRY

     BEGIN CATCH

      -- Rollback any active or uncommittable transactions before continuing

         IF XACT_STATE() <> 0 ROLLBACK TRAN   

      -- format the errStr to capture all err messages/numbers (system as well as customized)

      set @errStr =  

        coalesce(error_message(),'')  + -- Capture SQL error message if it exists and load it in first

        (case when error_message() is null then '' else ' ' end) +  -- insert a space if needed

        coalesce(@errStr,'') -- Last is optional proc level mssg

      set @errNum = coalesce(error_number(), @errNum, 50001) -- 50001 error in the CypressError database:general, unexpected error

      -- inserting information in the ErrorLog and return LogID

      EXECUTE dbo.ErrorLogIns @ErrorLogID = @ErrorLogID OUTPUT, @userID = @userID;

      -- Create XML formatted string to return to calling

      SELECT  @ErrorToRaise = dbo.udfFormatErrorMsg (@errStr, @errNum, @errorLogID)

      RAISERROR (@ErrorToRaise, 16, 1)

     END CATCH;

     SET NOCOUNT OFF

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    GRANT EXEC ON ProcedureName to GeneralApplicationUsers

    go

  • Hi Norm,

    It's a really good suggestion. Especially the part you said you will not allow any user to access tables directly but only procedure. Although I didn't undestand all you presented here.

    I believe it is quite good business practice. Thank you very much for your detailed message.

    Any resources you can direct me to for this issue.

    Betty

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

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