Run SQL Server BLOG – Just another Select of Ms SQL DBA site

2nd September 202415th October 2024

Check the SQL Server version

There are always some new features in the database server, which has SQL version checking requirements. For example: GRANT VIEW DATABASE PERFORMANCE STATE TO [user] can run, if your server at least SQL Server 2022.

In this case if you don’t want to see error in an older version , you have to check it:

IF (CAST(LEFT( CAST(SERVERPROPERTY('ProductVersion') as varchar(50)), 2) AS INT) >= 16)

BEGIN
    -- Execute the command if the version is at least SQL Server 2022
    SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'GREAT! SQL Server version is at least 2022' as Message;
END
ELSE
BEGIN
    -- Optional: Handle the case where the version is less than SQL Server 2022
	SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'SQL Server version is less than 2022. There is no : VIEW DATABASE PERFORMANCE STATE.'  as Message;
END

IF (CAST(LEFT( CAST(SERVERPROPERTY('ProductVersion') as varchar(50)), 2) AS INT) >= 16)

BEGIN

-- Execute the command if the version is at least SQL Server 2022

SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'GREAT! SQL Server version is at least 2022' as Message;

END

ELSE

BEGIN

-- Optional: Handle the case where the version is less than SQL Server 2022

SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'SQL Server version is less than 2022. There is no : VIEW DATABASE PERFORMANCE STATE.' as Message;

END

7th July 20247th July 2024

DateTime calculator to UTC to Local Server time to a specific TimeZone

In SQL server you can get the local datetime UTC time . Convert it to different TimeZone:

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentUTC DATETIME = GETUTCDATE();
DECLARE @TimeZoneLocal VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZoneLocal OUT

SELECT 
@TimeZoneLocal AS ServerTimeZone,
GETDATE() AS ServerCurrentDT,
GETUTCDATE()  AS 'GetUTCDATE',
GETDATE() AT TIME ZONE 'UTC' AS 'UTC',
GETUTCDATE() AT TIME ZONE 'UTC'  AT TIME ZONE 'Eastern Standard Time' AS 'to EST',
GETUTCDATE() AT TIME ZONE 'UTC'  AT TIME ZONE 'Pacific Standard Time' AS 'to PST',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS 'to CET',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE'India Standard Time' AS 'to IST';

DECLARE @CurrentDateTime DATETIME = GETDATE();

DECLARE @CurrentUTC DATETIME = GETUTCDATE();

DECLARE @TimeZoneLocal VARCHAR(50)

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZoneLocal OUT

SELECT

@TimeZoneLocal AS ServerTimeZone,

GETDATE() AS ServerCurrentDT,

GETUTCDATE() AS 'GetUTCDATE',

GETDATE() AT TIME ZONE 'UTC' AS 'UTC',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS 'to EST',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS 'to PST',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS 'to CET',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE'India Standard Time' AS 'to IST';

Convert a local datetime or the server local time to different timezone:

DECLARE @OneDatetime DATETIME = '2024-07-05 16:21:00.000'; -- Replace with your datetime value in Local server datetime
Set @OneDatetime = GETDATE() -- you can comment out if you have spec datetime for local server  in previous line
DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


SELECT 
@OneDatetime  AS OneDatetime ,
@OneDatetime AT TIME ZONE @TimeZone  AS 'Local_OneDatetime' ,
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Pacific Standard Time')AS 'to PST',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Eastern Standard Time') AS 'to EST',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'UTC' ) AS 'to UTC',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Central European Standard Time') AS 'to CET',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'India Standard Time') AS 'to IST',
@TimeZone AS ServerTimeZone,
GETDATE()  AS Server_Currrent_GetDate,
@OneDatetime AT TIME ZONE @TimeZone AS  'Server_current_offset'

DECLARE @OneDatetime DATETIME = '2024-07-05 16:21:00.000'; -- Replace with your datetime value in Local server datetime

Set @OneDatetime = GETDATE() -- you can comment out if you have spec datetime for local server in previous line

DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT

SELECT

@OneDatetime AS OneDatetime ,

@OneDatetime AT TIME ZONE @TimeZone AS 'Local_OneDatetime' ,

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Pacific Standard Time')AS 'to PST',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Eastern Standard Time') AS 'to EST',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'UTC' ) AS 'to UTC',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Central European Standard Time') AS 'to CET',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'India Standard Time') AS 'to IST',

@TimeZone AS ServerTimeZone,

GETDATE() AS Server_Currrent_GetDate,

@OneDatetime AT TIME ZONE @TimeZone AS 'Server_current_offset'

Convert UTC datetime or the server UTC time to different timezone:

DECLARE @UTCOneDatetime DATETIME = '2024-07-05 20:21:00'; -- Replace with your datetime value in Local server datetime
Set @UTCOneDatetime = GETUTCDATE() -- yo can comment out if you have UTC datetime
DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


SELECT 
@UTCOneDatetime  AS UTCOneDatetime ,
--@UTCOneDatetime AT TIME ZONE @TimeZone  AS 'UTC_OneDatetime' ,
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')AS 'to PST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS 'to EST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' ) AS 'to UTC',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time') AS 'to CET',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time') AS 'to IST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC') AS UTC,
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone) AS '--> To_LocalServerTime <--',
@TimeZone AS ServerTimeZone,
GETDATE()  AS 'Server_Current_GetDate',
GETUTCDATE()  AS 'Server_Current_GetUTCDate',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS  'Server_Current_offset'

DECLARE @UTCOneDatetime DATETIME = '2024-07-05 20:21:00'; -- Replace with your datetime value in Local server datetime

Set @UTCOneDatetime = GETUTCDATE() -- yo can comment out if you have UTC datetime

DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT

SELECT

@UTCOneDatetime AS UTCOneDatetime ,

--@UTCOneDatetime AT TIME ZONE @TimeZone AS 'UTC_OneDatetime' ,

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')AS 'to PST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS 'to EST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' ) AS 'to UTC',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time') AS 'to CET',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time') AS 'to IST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC') AS UTC,

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone) AS '--> To_LocalServerTime <--',

@TimeZone AS ServerTimeZone,

GETDATE() AS 'Server_Current_GetDate',

GETUTCDATE() AS 'Server_Current_GetUTCDate',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS 'Server_Current_offset'

Continue reading “DateTime calculator to UTC to Local Server time to a specific TimeZone”

15th February 202416th February 2024

Install Oracle in docker

Install Oracle express

docker command in windows powershell (WSL installed):

docker login container-registry.oracle.com
docker container create -it   --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123  container-registry.oracle.com/database/express:latest
docker start oracletest

# chek it later
docker exec oracletest env

docker login container-registry.oracle.com

docker container create -it --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123 container-registry.oracle.com/database/express:latest

docker start oracletest

# chek it later

docker exec oracletest env

download oracle sqldeveloper Windows 64-bit with JDK 11 included:

https://www.oracle.com/database/sqldeveloper/technologies/download/

14th February 2024

Add read rights to all existing and new user databases

The following script add read user rights for every user databases and system model databases for the new databases to readeruser login.

USE [master]
GO
CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'


EXEC sp_executesql @statement


FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

USE [master]

CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

DECLARE @dbname VARCHAR(50)

DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR

LOCAL FAST_FORWARD

FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','msdb','tempdb','distribution')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname

END

CLOSE db_cursor

DEALLOCATE db_cursor

13th February 2024

Resolve SID to domain account

There are reasons you need to resolve SID. You can do it by powershell:

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")
$objUser = $SID.Translate([System.Security.Principal.NTAccount])
$objUser.Value

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")

$objUser = $SID.Translate([System.Security.Principal.NTAccount])

$objUser.Value

19th October 202319th October 2023

Export a huge table to csv text file

The most easiest and quick method even for large table using the bcp:

bcp dbname.dbo.tablename out T:\temp\table5.txt -S localhost -T -c -t, -a 32768

1	bcp dbname.dbo.tablename out T:\temp\table5.txt -S localhost -T -c -t, -a 32768

if you use unicode add: -w

11th June 202311th June 2023

DBATools Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.

If you get the error:

Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.

You can set in dbatools:

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register

1	Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register

# Set these defaults for all future sessions on this machine
Set-DbatoolsInsecureConnection

# Set defaults just for this session
Set-DbatoolsInsecureConnection -SessionOnly

# Set these defaults for all future sessions on this machine

Set-DbatoolsInsecureConnection

# Set defaults just for this session

Set-DbatoolsInsecureConnection -SessionOnly

dbatools sql The certificate chain was issued by an authority that is not trusted.

11th June 202311th June 2023

Docker build

Some usefull docker command in powershell windows :

Prerequisite: Visual Studio Code Remote Development Extension Pack

# 1. Build the infrastructur in github directory:
docker-compose build

# 2. Run the infrastructure
docker-compose up

# 3.When done, clean up the environement by running
docker-compose down

# 1. Build the infrastructur in github directory:

docker-compose build

# 2. Run the infrastructure

docker-compose up

# 3.When done, clean up the environement by running

docker-compose down

18th May 202318th May 2023

VS Code and Git and Docker

Download a git project from github.
Create Visual Studio project workspae
Build containers

Commands in powershell (in the new empty project folder):

# download the code
git clone https://github.com/docker/welcome-to-docker

# enter to project folder
cd .\welcome-to-docker\

# open it in Visual Studio Code
code .

# build the container
docker build -t welcome-to-docker .

# yo can start it in Docker Desktop

# download the code

git clone https://github.com/docker/welcome-to-docker

# enter to project folder

cd .\welcome-to-docker\

# open it in Visual Studio Code

code .

# build the container

docker build -t welcome-to-docker .

# yo can start it in Docker Desktop

14th April 202314th April 2023

SQL Server using Active Directory Kerberos authentication on RedHat linux

This is an SSMS screenshot where a SQL user and two (windows) domain users connected to SQL Server:

SQL Server on Red Hat Linux using kerberos AD authentication

some hint if you want to deploy it:
– at first you have to join the linux server to the Active Directory
sudo realm join SQLPLAYGROUND.COM -U ‘azureadmin’ -v
– kinit and klist help you to check kerberos tickets
– you can use create -k /var/opt/mssql/secrets/mssql.keytab only in Ubuntu
– you have to use ktpass /princ MSSQLSvc/REDHATSQL2019.SQLPLAYGROUND.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLPLAYGROUND\sqluser /out mssql.keytab -setpass -setupn /kvno /pass in Red-Hat linux