Tag: SQL 2019

Categories
Date & Times script SQL

SQL Function to Generate Date Table

Update: Please try the Improved version of this script.

-- select * from [dbo].[GenDates]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'y', getdate()-16383, getdate()+16384 );
create or alter function [dbo].[GenDates](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3) )
as
begin;
	set @Increment = lower( @Increment );

	with [cte]( [Date] ) AS (
		select @Start
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date] )
	select [Date]
	from [cte]
	option (maxrecursion 32767);

	return;
end;
Categories
How To

Enable Windows Firewall for SQL Server

Run these commands on the Windows Server to open the ports for listening on a named instance of SQL Server.
Note: Remember to enable TCP in the SQL Server Configuration Manager.

netsh firewall set portopening protocol=TCP port=1433 name=SQLServerTCP mode=ENABLE scope=SUBNET profile=CURRENT

netsh firewall set portopening protocol=UDP port=1434 name=SQLServerUDP mode=ENABLE scope=SUBNET profile=CURRENT

For more help, refer to SQL Server & Windows Firewall.

Categories
How To script SQL

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- NOTE: This is an EXAMPLE to show it is possible
-- to achieve moving rows using an implicit transaction
-- from one table into another table.

-- Set up example tables, Foo & Bar.
drop table if exists [dbo].[Bar];
drop table if exists [dbo].[Foo];

create table [dbo].[Bar](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] null index ix_mycol
)

create table [dbo].[Foo](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] index ix_mycol,
)
GO

-- Create some dummy data
insert into [dbo].[Foo]( [MyKey], [MyColumn] )
values (1, 11), (2, 22), (3, 33), (4, 44);
GO 10240

-- Verify the rows before.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

-- Move rows from table Foo into table Bar using two styles.

-- Method 1: "Direct" move
delete
from [dbo].[Foo]
output [deleted].[MyKey], [deleted].[MyColumn]
into [dbo].[Bar]( [MyKey], [MyColumn] )
where [ID] % 3 = 1;


-- Method 2: "Subquery" move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [ID] % 3 = 2
) [MovedRows]
where [MovedRows].[MyKey] = 2;
-- Note: The last where is not needed, just showing it is possible to re-filter incoming [from the deleted output] rows.

-- Verify the counts after.
-- Note: Any deleted rows filtered out via Method 2 are now gone.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

Categories
script SQL

Send a text message from SQL Server 2019

NOTE: You must have SQL Server database mail enabled for this stored procedure to work.

create or alter proc [dbo].[SendTextMessage](
	@number sysname
	,@message nvarchar(max)
	,@subject sysname = null
	,@provider nvarchar(20) = N'Verizon'
)
as
begin;
	set nocount on;

	set @number = trim(@number);
	if @number is null or len(@number)<7 begin;
		set @message = N'@number is too short';
		throw 51000, @message, 1;
	end;

	set @message = trim(@message);
	if @message is null or len(@message)<2 begin;
		set @message = N'@message is too short';
		throw 51000, @message, 1;
	end;

	set @subject = trim(@subject);
	if @subject is null set @subject = @@SERVERNAME + N' Notification';

	declare @to nvarchar(255) = @number + N'@'+
		case @provider
			when N'Verizon' then N'vtext.com'
			when N'VZ' then N'vtext.com'
			when N'V' then N'vtext.com'
			when N'ATT' then N'txt.att.net'
			when N'SPRINT' then N'messaging.sprintpcs.com'
			when N'TMOBILE' then N'tmomail.net'
	end;

	if nullif(@provider,N'@') is null begin;
		throw 51000, 'Unknown provider. Message not sent.', 1;
	end;

	begin try;
		declare @mailitem_id int;
		exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @mailitem_id=@mailitem_id output;
	end try
	begin catch;
		throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1;
	end catch;
end;