I had to move the data files from one driver to another. After moving the data files when I brought the database online it gave"Access denied" error and I found out that the reason was that the MSSQL$instance account did not have permission to the data file.
I right clicked on the folder and on the security tab tried to add the user to give the missing permission but could not locate the user. I had to do the following to find the user in the user list
To add the missing permissions
chose the local computer and type in "NT SERVICE\MSSQL$instance
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Thursday, June 7, 2018
Wednesday, May 2, 2018
Error handling into a table
Developers need to call the uspErrorHandling stored procedure in their code with the optional parameter list.
ALTER PROCEDURE [dbo].[uspErrorLogTest] (@date as datetime)
AS
--sample exec [uspErrorLogTest] '11/02/2018'
BEGIN TRY
declare @paramList varchar(4000) = '@date=' + cast( @date as varchar)
-- SELECT 1/0
select cast(@date as int) /0
END TRY
BEGIN CATCH
begin
EXEC dbo.uspErrorHandling @paramList
end
END CATCH
if object_id('uspErrorHandling','P') is not null
drop procedure uspErrorHandling
GO
CREATE procedure dbo.uspErrorHandling (@ErrorProcParameterList varchar(4000) = null)
AS
-- Declaration statements
DECLARE @ErrorNumber int
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(200)
DECLARE @ErrorLine int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @TransactionDateTime datetime
-- Initialize variables
SELECT @ErrorNumber = isnull(error_number(),0),
@ErrorMessage = isnull(error_message(),'NULL Message'),
@ErrorSeverity = isnull(error_severity(),0),
@ErrorState = isnull(error_state(),1),
@ErrorLine = isnull(error_line(), 0),
@ErrorProcedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@TransactionDateTime = GETDATE();
select @ErrorMessage
-- Insert into the dbo.ErrorLog table
INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine,
ErrorProcedure, ErrorParameterList,UserName, HostName,TransactionDateTime)
SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,
@ErrorProcedure, @ErrorProcParameterList,@UserName, @HostName, @TransactionDateTime
GO
--Table where we log the errors
CREATE TABLE [dbo].[ErrorHandling](
[ErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL,
[Error_Procedure] [varchar](200) NOT NULL,
[Error_Line] [int] NOT NULL,
[UserName] [varchar](128) NULL,
[HostName] [varchar](128) NULL,
[TransactionDateTime] [datetime] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ErrorHandlingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER PROCEDURE [dbo].[uspErrorLogTest] (@date as datetime)
AS
--sample exec [uspErrorLogTest] '11/02/2018'
BEGIN TRY
declare @paramList varchar(4000) = '@date=' + cast( @date as varchar)
-- SELECT 1/0
select cast(@date as int) /0
END TRY
BEGIN CATCH
begin
EXEC dbo.uspErrorHandling @paramList
end
END CATCH
if object_id('uspErrorHandling','P') is not null
drop procedure uspErrorHandling
GO
CREATE procedure dbo.uspErrorHandling (@ErrorProcParameterList varchar(4000) = null)
AS
-- Declaration statements
DECLARE @ErrorNumber int
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(200)
DECLARE @ErrorLine int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @TransactionDateTime datetime
-- Initialize variables
SELECT @ErrorNumber = isnull(error_number(),0),
@ErrorMessage = isnull(error_message(),'NULL Message'),
@ErrorSeverity = isnull(error_severity(),0),
@ErrorState = isnull(error_state(),1),
@ErrorLine = isnull(error_line(), 0),
@ErrorProcedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@TransactionDateTime = GETDATE();
select @ErrorMessage
-- Insert into the dbo.ErrorLog table
INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine,
ErrorProcedure, ErrorParameterList,UserName, HostName,TransactionDateTime)
SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,
@ErrorProcedure, @ErrorProcParameterList,@UserName, @HostName, @TransactionDateTime
GO
--Table where we log the errors
CREATE TABLE [dbo].[ErrorHandling](
[ErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL,
[Error_Procedure] [varchar](200) NOT NULL,
[Error_Line] [int] NOT NULL,
[UserName] [varchar](128) NULL,
[HostName] [varchar](128) NULL,
[TransactionDateTime] [datetime] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ErrorHandlingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Subscribe to:
Posts (Atom)