Find First Sunday of Next Month using EOMONTH is SQL Server

This below query to get First Sunday of Next Month.

DECLARE @tbl_Test_EOMONTH AS TABLE

(

SampleDate DATETIME

)

 

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-12-20')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-11-08')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-10-16')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-09-26')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2016-01-31') 

SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,DATEADD(DAY,0,EOMONTH([SampleDate])))

,EOMONTH([SampleDate])) AS FirstSunday_ofTheNextMonth

FROM @tbl_Test_EOMONTH

GO

Script to find a list of Weekends between two Dates in SQL Server

This below query to get all weekends between the given date range.

DECLARE @beginDate DATE = '20201201'

DECLARE @endDate DATE = '20201231'  

 

DECLARE @Weekend TABLE

(

 Weekend DATE PRIMARY KEY

,IsWeekend BIT

)

 

WHILE @beginDate <= @endDate 

BEGIN 

INSERT INTO @Weekend 

SELECT @beginDate AS Weekend 

,(CASE WHEN DATEPART(WEEKDAY, @beginDate) In (7, 1) THEN 1 ELSE 0 END) AS IsWeekend 

SET @beginDate = DateAdd(Day, 1, @beginDate) 

END

 

SELECT Weekend FROM @Weekend WHERE IsWeekend = 1

List all Dates between two dates in SQL Server

This below query to get all dates between the given date range.

DECLARE @StartDateTime DATETIME

DECLARE @EndDateTime DATETIME

 

SET @StartDateTime = '2020-01-01'

SET @EndDateTime = '2020-01-12';

 

WITH DateRange(DateData) AS 

(

    SELECT @StartDateTime as Date

    UNION ALL

    SELECT DATEADD(d,1,DateData)

    FROM DateRange 

    WHERE DateData < @EndDateTime

)

SELECT DateData

FROM DateRange

OPTION (MAXRECURSION 0)

GO

Import data from .sql file to SQL Server database using sqlcmd utility

sqlcmd is a command line utility that is part of the standard installation of SQL Server 2005 or higher, which enables interactive interface with any SQL Server instance to help perform the following tasks:

1. Execute Transact-SQL (T-SQL) statements in SSMS
2. Call a T-SQL script file
3. Use of environment variables in command mode
4. Store the output results of executed queries in a specified text file

How to use SQLCMD

This utility can be download and install from below link 

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?redirectedfrom=MSDN&view=sql-server-ver15

Example:

After installation of sqlcmd utility

Step 1. Make a database in you SQL server with student table (id int, name varchar(100))

Step 2. make a TEST.sql script file with below code

           USE TestDB;

           INSERT INTO student (id, name)

           VALUES (1,'Neeraj Gusain')

Step 3. open command prompt and write below line to run mentioned script in .sql

            sqlcmd -S 10.100.0.5\SQLEXPRESS -U sa -P admin@123 -i D:\TEST.sql

sqlcmd is a simple, yet powerful scripting environment that helps with the automation of several tasks related to SQL Server. For example, you can write and execute a script that logs you into a specific instance of SQL Server, executes a script from a specified path, and redirects the output to a certain file.

Most of us SQL database administrators are already using PowerShell. The Invoke-SqlCmd cmdlet, from the sqlserver module that’s part of every SQL Server distribution since 2008, packs most of the capabilities of sqlcmd. Also, in SQL Server Management Studio, we have a SQLCMD mode, that simulates the capabilities of sqlcmd, and accepts some of the commands that are not part of the T-SQL language.

sqlcmd was an extension of the osql and isql command line utilities, that contains a rich set of parameters and options to enable automation of various administrative tasks.

1. It’s simple to bind SQL in a .bat file.
2. It runs interactively across various OS platforms.
3. It’s easy to pass command line arguments to a SQL file using sqlcmd.

How to enable SQLCMD

OK, so sqlcmd is a great tool; of that, we’re all convinced. Let’s now talk about how the query editor in SSMS can be used to write statements as sqlcmd scripts. The following example talks about the integration of Windows system commands and T-SQL statements into a single script.

It’s importatnt to remember that sqlcmd mode is not enabled by default. To enter into the sqlcmd mode in the SSMS query editor, follow the steps below:

1. In the Object Explorer right click the SQL Server and then click New Query
   OR
   Open a new Database Engine Query Editor window.
2. On the Query editor, click SQLCMD Mode.

To enable SQLCMD scripting mode:

1. On the SSMS, go to the Tools menu, select Options.
2. Expand the Query Execution tab, and select SQL Server, click the General page.
3. Check the box against By default open new queries in SQLCMD Mode.

Some important points to remember

1. SQLCMD commands must be prefixed with a colon. This makes the difference between SQLCMD commands and Transact-SQL clear.
2. The :CONNECT keyword is used to connect to a different server using the same session context.
3. The Operating system commands (in this case, if exists) and del commands used for the demo must start with two exclamation points (!!). The !! indicate that the commands are OS command, which will be executed using the cmd.exe command processor. In this example, the OS command, if exist $(file1) del $(file1) is passed as arguments to cmd.exe.
   
4. The variables that are used as part of the SQLCMD script are case-sensitive. For example, if we query the environment variables, we can see that COMPUTERNAME and computername are two different variables in the output. In the first, the environment variable is queried to return the name of the computer, where as in the second, SSMS isn’t able to resolve the variable and reports an error.

:connect hqdbsp18 PRINT '$(COMPUTERNAME)'   GO :connect hqdbsp17 PRINT '$(computername)'

Examples of SQLCMD

The following example defines an integration of operating system commands and SQL statements together.

Variable are defined using SETVAR, connection is built using CONNECT the keyword, operating system commands are defined using !!, the output file is declared using the OUT keyword, and SQL Statements are placed to fetch the results based on the connection string.

--variable declartion :setvar subscriber1 HQMESRP01   :setvar subscriber2 HQMESRP02 :setvar file1 c:\Data1.txt :setvar file2 c:\Data2.txt   --check for the file and delete if exists !!if exist $(file1) del $(file1) !!if exist $(file2) del $(file2)   --connect to to the subscription 1   :Connect $(subscriber1) --redirect the SQL output to file :out $(file1) select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079 GO   --connect to to the subscription 2   :Connect $(subscriber2)   --redirect the SQL output to file :out $(file2) select * from [MES_HIST_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_HIST_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079

SQLCMD with scripting variables, and Windows scripting in SSMS

In this example, login is created across multiple servers. T-SQL, sqlcmd and Windows scripting are all integrated, and run using the SSMS interface to create logins.

1. Enable XP_CMDSHELL
2. Define the T-SQL in a SQL file
3. Build the dynamic SQL
4. Verify the output

STEP 1: In this step, xp_cmdshell is enabled. It is not a recommended option, though. This has only been enabled for the purpose of the demo.

sp_configure 'show advanced options', 1;   GO   RECONFIGURE;   GO   sp_configure 'xp_cmdShell', 1;   GO   RECONFIGURE;   GO

STEP 2: A SQL file, SysAdminLoginCreation.sql is created with the create login SQL statement.

USE [master] GO CREATE LOGIN [SQLShackTest] WITH PASSWORD=N'SQLShackDemo123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

The file looks like below

We create a for loop to loop through each SQL instances.

1. Iterate through the input file.
2. FOR Parameter %j defines the name of the SQL instance.
3. Call sqlcmd using the parameters as part of the command.
4. Repeat for each data item.

MASTER..XP_CMDSHELL 'for /f %j in (\\networkshare\IsDba\server.txt) do sqlcmd -S %j -i \\networkshare \IsDba\SysAdminLoginCreation.sql -E'

sqlcmd executed successfully!

Let’s check whether the logins are created on the those servers

:connect hqdbsp18 select * from sys.syslogins where name='SQLShackTest' GO :connect hqdbsp17 select * from sys.syslogins where name='SQLShackTest'

The logins have also been created for the listed servers.

Passing variables (or argument) as T-SQL and SQL Script file

We can pass the variables as an input to the T-SQL or the SQL script file in the sqlcmd command console. The scripting variables are declared using :SETVAR keyword. The corresponding parameter values passed to T-SQL or SQL script file. The values are enclosed in $(variable name) are fed to the SQLs during the run time. Using -v (version) switch, the parameters are declared and fed to the script. We can list any number of variables with no delimiter in between the parameters. Lets follow the example to understand the mechanism better:

1. The salesorderdetails is the name of table assigned to tablename variable
2. The select statement is prepared along with the tablename variable

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E 1> use Python2017 2> :SETVAR tablename salesorderdetail 2> select top(10) * from $(tablename); 3> go

We can assume that database name will be provided as a SQLCMD variable during the deployment process; we can have exactly the same file deployed to all environments. The following example has two parameters, db and tablename. The input parameters are separated by a space in between.

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E -i n:\IsDba\SalesOrderDetails.sql -v db=Python2017  tablename=salesorderdetail

Summary