Changing Default Database File Locations in SQL

I missed to specify the default location for the data file and the log file while installing SQL SERVER . So when I created a database and did not specify the default location for the files it will be created on the location defined in the server settings. So if we want  always the data files and log files to be created on a particular location we can
change the server properties through SSMS - > Database Settings -> Database default locations .

However this requires the sql instance to be restarted for the changes to take effect.

Adding missing permission to a folder for the MSSQL$instance account

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

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   

How to read mixed data type from excel file in SSIS

To fix the issue where SSIS was reading mixed data  type wrong from an EXCEL file I had to change the registry information
I had a file where the source file had account numbers where some rows had value as integer and some had alpha numeric character. When the account number had first few rows as integer the remaining rows with the alphanumeric account number value was not getting read and the value was getting imported as NULL. I followed the following steps to fix this issue in EXCEL 2012 .

1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 14.0 ► Access Connectivity Engine ► Engines.
3. Double-click the Excel node.
4. In the right panel, double-click the TypeGuessRows entry.
5. Change the value data from 8 to 0.
6. Click OK.
7. Select File ► Exit to exit the Registry Editor window.

This modification instructs Excel to scan all of the rows in the table for their data types. This action might decrease performance on very large tables, but the importation of data will be more accurate. You can also set TypeGuessRows from 0 to 16, inclusive, where 0 scans all of the rows and the other numbers set the number of rows to scan first.