September 26, 2006 at 10:17 am
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
September 26, 2006 at 10:56 am
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.
September 26, 2006 at 11:22 am
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
September 26, 2006 at 11:29 am
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.
September 27, 2006 at 9:24 am
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."
September 27, 2006 at 10:47 am
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
September 27, 2006 at 12:16 pm
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