Recursive user-defined function in SQL Server example

Recursive user-defined function

create function RecursiveTest(@V int)

returns @t table (i int)

as

   begin

   set @v = @v - 1

   if @v<>0

     insert into @t 

     select @v union 

     select * from dbo.RecursiveTest(@v)

   else

     insert into @t values (0)

   return

   end

How to Call Recursive user-defined function:

select * from dbo.RecursiveTest(10) 

Github Link: https://github.com/adi501/SQL-Server-Examples

How to update models in VS 2019 from database in Entity Framework in ASP.NET Core

• Go to Tools -> Nuget Package Manager -> Package Manager Console (as below image)

• Next go to Package Manager Console and add below script and run as below image. To Update already created models via Scaffold-DbContext we need to use "Force" param as below script

Scaffold-DbContext "Server=ServerName;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -force

How to find recently executed queries in SQL Server

select dest.text as Query,deqs.last_execution_time 

from sys.dm_exec_query_stats as deqs

cross apply sys.dm_exec_sql_text(deqs.sql_handle) as dest

order by deqs.last_execution_time desc

(or)

select dest.text as Query,deqs.last_execution_time 

from sys.dm_exec_query_stats as deqs

cross apply sys.dm_exec_sql_text(deqs.sql_handle) as dest

where dest.text like '%dm_exec_query_stats%'

order by deqs.last_execution_time desc

Comma Separated Value in SQL Query

 

--- create Table

create table Tbl_Exp(Id int identity(1,1) primary key,Emp_Name varchar(50),Working_Day varchar(5))

-- insert some test data

insert into Tbl_Exp values('Adi','M')

insert into Tbl_Exp values('Adi','T')

insert into Tbl_Exp values('Adi','W')

insert into Tbl_Exp values('Adi','TH')

insert into Tbl_Exp values('Pavan','F')

insert into Tbl_Exp values('Pavan','S')

insert into Tbl_Exp values('Pavan','M')

insert into Tbl_Exp values('Madhan','T')

insert into Tbl_Exp values('Madhan','W')

select * from Tbl_Exp

--how to use stuff [to replace some data], by using below query we are going to Replace 1st 2 letters with "***"

select stuff('abcdef',1,2,'***')

-- How to get comma separated value using XML path

Select ','+Working_Day from Tbl_Exp for xml path('')

-- how to remove 1 st values using stuff

select stuff((Select ','+Working_Day from Tbl_Exp for xml path('')),1,1,'')

-- Final Query

select distinct Emp_Name,

(select stuff((Select ','+Working_Day from Tbl_Exp as a where a.Emp_Name=b.Emp_Name for xml path('')),1,1,'') as a) as 'comma_separated'

from Tbl_Exp as b

How to Display the Previous Row and Next Row value in SELECT statement

 In SQL Server by using LAG & LEAD We can find then Previous & Next row values as below example.

-- create Table

create table Tbl_Test(Id int)

-- Insert some test data into Table

insert into Tbl_Test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)

-- find Current,Previous Row Value

select Id,Lag(Id,1,0) over(Order by Id) as Previous_Value  from Tbl_Test

-- find Current,Next Row Value 

select Id,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display Previous,Current, Next row Values

select Lag(Id,1,0) over(Order by Id) as Previous_Value,Id as Current_Value,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display 2nd Previous,Current, 2nd Next row Values

select Lag(Id,2,0) over(Order by Id) as '2nd_Previous_Value',Id as Current_Value,LEAD(Id,2,0) over(Order by Id) as '2nd_Next_Value'  from Tbl_Test

Split Comma Separated List Without Using a Function in SQL Server

DECLARE @t TABLE
(
EmployeeID varchar(100),
Certs VARCHAR(8000)
)
INSERT @t VALUES ('Param1:','B.E.,MCA, MCDBA, PGDCA'), ('Param2:','M.Com.,B.Sc.'), ('Param1:','M.Sc.,M.Tech.')

SELECT EmployeeID,
EmployeeID+LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


Output:

How to configure database mail in sql server 2016 and above

GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp 
    @profile_name = 'TestDB_Mail', 
    @description = 'Profile used for sending outgoing notifications using Gmail.' ; 
GO

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
    @profile_name = 'TestDB_Mail', 
    @principal_name = 'public', 
    @is_default = 1 ;
GO

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp 
    @account_name = 'Gmail', 
    @description = 'Test mail.', 
    @email_address = 'XXXXXXXX', 
    @display_name = 'Automated Mailer', 
    @mailserver_name = 'smtp.gmail.com',
    @port = 25,
    @enable_ssl = 1,
    @username = 'XXXXXXX',
    @password = 'XXXXXX' ; 
GO

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
    @profile_name = 'TestDB_Mail', 
    @account_name = 'Gmail', 
    @sequence_number =1 ; 
GO

--To Send email

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'TestDB_Mail',
     @recipients = 'XXXXXX',
     @body = 'The database mail configuration was completed successfully1.',
     @subject = 'Automated Success Message';
GO

----Rrecall Everything

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'TestDB_Mail'
GO

Send email from SQL Server stored procedure

Step 1: Enable Reconfigure in SQL by using below script.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Step 2: Create Stored procedure by using below script

CREATE PROCEDURE [dbo].[sp_send_mail]

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),

@output_mesg varchar(10) output,

@output_desc varchar(1000) output

AS

DECLARE @imsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 

  'smtp.gmail.com' 

--UserName

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', 

  'sender@gmail.com' 

--Password

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 

  'xxxxxx' 

--UseSSL

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value', 

  'True' 

--PORT 

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value', 

  '465' 

--Requires Aunthentication None(0) / Basic(1)

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 

  '1' 

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.

IF @hr <>0 

SELECT @hr

BEGIN

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

--set @output_desc = ' source: ' + @source

set @output_desc =  @description

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

END

EXEC @hr = sp_oadestroy @imsg

Step 3: Execute Stored procedure (to send email to sender) by using below script

DECLARE @out_desc varchar(1000),

@out_mesg varchar(10)

EXEC sp_send_mail 'sender@gmail.com',

'receiver@gmail.com',

'Hello', 

'<b>This is s Test Mail</b>',

'htmlbody', @output_mesg = @out_mesg output, @output_desc = @out_desc output

PRINT @out_mesg

PRINT @out_desc

When was the last successful backup in sqlserver?

Just right click on the Database and click on properties on the sql management studio.

On top of window the last full backup completion time and log backup completion time is shown.This is the simplest way of finding the success of a daily backup without even typing a command.

How to find out the SQL Server installation date?

SELECT create_date as 'Installation Date' FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM'