Tag: SQL 2019
Categories
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
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.
-- 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];
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;
