Using multiple CTEs

 Using multiple CTEs

I used this in  SSIS as a script in DFT to get away from creating and writing temp tables.

;WITH  cte1

AS 

( SELECT field1,field2 FROM table1)

,

cte2

AS 

( SELECT field1,field2 FROM table2)

SELECT * FROM cte1

UNION ALL

SELECT * FROM cte2

Insert null value in Database through SSIS

The inbound file had empty string that was causing the file to error when it was trying to insert in the DOB column of the table with the data type on datetime. I used a dervied coulum in the DFT and used the following the the data tranformation editor to solve the error ISNULL(DOB) || TRIM(DOB) == "" ? NULL(DT_DBDATE) : (DT_DBDATE)DOB When the file was loaded the DOB column had NULL for the empty value.

Script: Delete back up file

--script to delete file from sql server instance1

Declare @DeleteDate varchar(50)
Declare @DeleteExecuteSQL varchar(1000)
Set @DeleteDate = cast(DATEADD(day,-3,GetDate()) as varchar(50))
Set   @DeleteExecuteSQL =
'EXECUTE master.sys.xp_delete_file 0,N''V:\MSSQL$instance1\BackUp\Daily\Client000\' + @@servername + '\User'',N''bak'',N' + quotename(@DeleteDate,'''') +  ',1'

print @DeleteExecuteSQL
--Execute (@DeleteExecuteSQL)

Tally table in SQL

I had a situation where I had to write out to a file where the  column3 ( string)  should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.

WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)

,cte AS
(
SELECT
  v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50  + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Explanation ---

select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20


;WITH CTE AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1 FROM CTE WHERE Num < @Max
)


SELECT * FROM CTE
 



----Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

---Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

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