T-SQL How to select only the Second row from order by Select statement

The blog article shows you how to select only the second row of data after you use order by in T-SQL statement. For example, I have a table named Transactions and I issued a sql command as below.

SELECT FROM [transactions] order by TransDate desc

I only want to get the second row on the table below.

Use the statement below then you are able to get just the second row.

with cte as

(

select *

, ROW_NUMBER() over (order by TransDate) as rn

from [dbo].[transactions]

)

select * from cte where rn = 2

Reference: https://stackoverflow.com/questions/3617152/t-sql-how-to-select-only-second-row-from-a-table

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

Crystal Report Error Missing report ID

This blog article shows you one of the possible ways to resolve the Crystal Report Missing report ID.

To resolve this error. Disable the policy:

System cryptography: Use FIPS 140 compliant cryptographic algorithms, including encryption, hashing and signing algorithms.

Other information: https://answers.sap.com/questions/12475007/what-type-of-secure-connection-sap-crystal-report.html

AI Business Basics

Decision makers exploring AI and looking to understand:

• Business benefits like improved productivity and reduced risk.
  
• Best practices for building your AI strategy.
  
• Practical applications for AI in your business.
  
• Real-world examples of companies using AI.
  

Read here:
https://bit.ly/2PKxHK1

(I am not sure how long Microsoft will leave the link alive).

CTO April 2021 articles and resources

These are the good reads found in this month.

Should I still buy Intel CPUs?

https://www.pcworld.com/article/3605355/should-i-still-buy-intel-cpus-ask-an-expert.html

What is AI? Everything you need to know about Artificial Intelligence

• WHAT ARE THE USES FOR AI?
  

• WHAT ARE THE DIFFERENT TYPES OF AI?
  

• WHAT CAN NARROW AI DO?
  

https://www.zdnet.com/article/what-is-ai-everything-you-need-to-know-about-artificial-intelligence/

Miracle on Ice: Business, Technology and National Security Leadership Lessons from the 1980 Olympics USA Hockey Team

• Coach Herb Brooks convinced everyone that something different had to occur than had been occurring in the past. “Only way to succeed is to change the way we play the game. ” Then he engineered a strategy.

https://ctovision.com/miracle-ice-business-technology-national-security-leadership-lessons-1980-olympics-usa-hockey-team/

What is the dark web? How to access it and what you’ll find

• Deep web vs. dark web: What’s the difference?

https://www.csoonline.com/article/3249765/what-is-the-dark-web-how-to-access-it-and-what-youll-find.html

Introducing Microsoft .NET 6

• Mixing WebAssembly and the desktop

https://www.infoworld.com/article/3608951/introducing-microsoft-net-6.html

Resources:

A Quick-Start Guide to Accelerate Digital Selling

https://bit.ly/3a4wbcC

Digital transformation infographic

https://bit.ly/3wOjaO7

Blazor Template Component

This blog article shows provides you the code for https://docs.microsoft.com/en-us/aspnet/core/blazor/components/templated-components?view=aspnetcore-5.0?WT.mc_id=DP-MVP-36769 article. Microsoft will update the page with source code likely. Before that I create the code and enhance it.

Create a Blazor Server App. In Shared folder create a Blazor Component name TableTemplate.razor (Shared/TableTemplate.razor).

@typeparam TItem

<table class=”table”>

    <thead>

        <tr>@TableHeader</tr>

    </thead>

    <tbody>

        @foreach (var item in Items)

        {

            <tr>@RowTemplate(item)</tr>

        }

    </tbody>

</table>

@code
{

    [Parameter]

    public RenderFragment TableHeader { get; set; }

    [Parameter]

    public RenderFragment<TItem> RowTemplate { get; set; }

    [Parameter]

    public IReadOnlyList<TItem> Items { get; set; }

}

In the Page folder create a page Pets.razor (Pages/Pets.razor).

@page “/pets”

<h1>Pets</h1>

<TableTemplate Items=”pets” Context=”pet”>

    <TableHeader>

        <th>ID</th>

        <th>Name</th>

        <th>Pet Type</th>

    </TableHeader>

    <RowTemplate>

        <td>@pet.PetId</td>

        <td>@pet.Name</td>

        <td>@pet.PetType</td>

    </RowTemplate>

</TableTemplate>

@code
{

    private List<Pet> pets = new()

    {

        new Pet { PetId = 2, Name = “Mr. Bigglesworth”, PetType=“Dog” },

        new Pet { PetId = 4, Name = “Salem Saberhagen”, PetType = “Dog” },

        new Pet { PetId = 7, Name = “K-9”, PetType = “Cat” }

    };

    private class Pet

    {

        public int PetId { get; set; }

        public string Name { get; set; }

        public string PetType { get; set; }

    }

}

The folder structure will look like the following.

I added Pets to the navigation menu. You can download the code below and run.

Source code download https://github.com/chanmmn/BlazorAppTemplate/tree/master/TemplateComponent.

Create Microsoft 365 user accounts with PowerShell

This blog article shows you how to create an user account in Office 365 using PowerShell.

Install-Module -Name AzureADPreview -AllowClobber

Connect-AzureAD

$PasswordProfile=New-Object -TypeName Microsoft.Open.AzureAD.Model.PasswordProfile 

$PasswordProfile.Password=“abc123”

New-AzureADUser -DisplayName “user4” -GivenName “user” -SurName “4” -UserPrincipalName “user4@domain.onmicrosoft.com” -PasswordProfile $PasswordProfile -AccountEnabled $true

Beware this script does not include license assignment.

Reference: https://docs.microsoft.com/en-us/powershell/module/azuread/new-azureaduser?view=azureadps-2.0?WT.mc_id=DP-MVP-36769

ASP.NET Blazor Server call Web Api

This blog article shows you one of the possible way for ASP.NET Blazor Server to call ASP.NET Web Api. First, both side is having the below object.

public class Customer

{

    public string CustomerCode { get; set; }

    public string CustomerName { get; set; }

}

In the Web Api Get you return a Customer object as below.

public Customer Get()

{

    Customer custom = new Customer()

    { CustomerCode = “1”, CustomerName = “CMM” };

    return custom;

}

In ASP.NET Blazor Server app, the Blazor component call the Web Api like the code below.

public async Task<Customer> GetData(int dummy)

{

    HttpClient client = new HttpClient();

    client.BaseAddress = new Uri(URL);

    string strres = “”;

    client.DefaultRequestHeaders.Accept.Add(

    new MediaTypeWithQualityHeaderValue(“application/json”));

    Customer custom = new Customer();

    HttpResponseMessage response = await client.GetAsync(URL);

    Customer data = await response.Content.ReadAsAsync<Customer>();

    return data;

}

Source code download: BlazorApp/BlazorServerCallWebApi at master · chanmmn/BlazorApp (github.com)

Reference: https://docs.microsoft.com/en-us/aspnet/core/blazor/security/server/additional-scenarios?view=aspnetcore-5.0?WT.mc_id=DP-MVP-36769

An unhandled exception occurred: Cannot find module ‘@angular-devkit/build-angular/package.json’ Angular Error

This short blog article shows you how to resolve the Angular error “An unhandled exception occurred: Cannot find module ‘@angular-devkit/build-angular/package.json'” when you are running ng serve.

Use rm to remove node_modules directory
if you
are in Linux. In Windows, delete the node_modules folder using File Explorer.

1. rm -r node_modules (or delete your node_modules folder manually)

2. npm cache clean (npm > v5 add –force so: npm cache clean –force)

3. npm install

Run ng serve again, the website should be running.

Reference: https://stackoverflow.com/questions/42585663/cannot-find-module-angular-compiler

SQL Server generate Create Table script using Excel VBA

This blog article shows you how to slightly simplify the generation of Create Table SQL script. The article expects you know how to run Excel macro. To make this macro work Cell A1 is always a file path where you want to save your script. A2 is your table name. Cell A3 onward will be the name of the database table column. Start from Cell B3 onward the column should consist of valid SQL Server data type like the picture below.

Run the macro below.

Sub WriteFile()

    Dim myFile As String

    Dim rng As Range

    Dim cellValue As Variant

    Dim num As Integer

    Dim j As Integer

    Dim str1 As String

    Dim strOpenBracket As String

    Dim strCloseBracket As String

    Dim strTable As String

    str1 = “CREATE TABLE ”

    strOpenBracket = “(”

    strCloseBracket = “)”

    Range(“A1”).Select

    myFile = ActiveCell.Value

    Range(“A2”).Select

    strTable = ActiveCell.Value

    Open myFile For Output As #1

    Print #1, str1 & strTable

    Print #1, strOpenBracket

    ActiveCell.Offset(1, 0).Select

    If (ActiveCell.Value <> “”) Then

        Print #1, ActiveCell.Value & ” ” & ActiveCell.Offset(0, 1).Value

    End If

    ‘Print #1, ActiveCell.Value

    ActiveCell.Offset(1, 0).Select

    While (ActiveCell.Value <> “”)

        Print #1, “,” & ActiveCell.Value & ” ” & ActiveCell.Offset(0, 1).Value

        ActiveCell.Offset(1, 0).Select

    Wend

    Print #1, strCloseBracket

    Close #1

End Sub

You should get the output as follow.

CREATE TABLE Customer

(

CustomerCode varchar(10)

,CustomerName varchar(100)

)

Source Code Download: https://github.com/chanmmn/vba/GenerateTable.xlsm

Reference: https://chanmingman.wordpress.com/2020/05/31/getting-started-with-vba-in-office/

https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office/?WT.mc_id=DP-MVP-36769

https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/what-s-new-for-vba-in-office-2019?WT.mc_id=DP-MVP-36769

https://chanmingman.wordpress.com/2018/12/20/ms-access-vba-accessing-to-sql-server/?WT.mc_id=DP-MVP-36769

SQL Server Tail log backup and restore

This blog article shows you how to backup tail log and restore tail log. The while simulation code is below. You are required to have basic knowledge of SQL Server. There is no such command as BACKUP TAIL LOG. The command is BACKUP LOG … WITH NO_TRUNCATE, COPY_ONLY. Beware there are many youtube videos out there on Tail Log is just no going to work. You need to follow the below sequence accordingly.

— Create a database for Tail log back up and restore test

CREATE DATABASE [backupdemo]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N’backupdemo’, FILENAME = N’c:\database\backupdemo.mdf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N’backupdemo_log’, FILENAME = N’c:\database\backupdemo_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )

GO

— Switch to backupdemo database

USE backupdemo

— Create a table for transaction

CREATE TABLE [Test](

[c1] int identity,

[c2] varchar(100)

)

— Insert 1 record

Insert INTO [Test] VALUES (‘transcation 1’)

— Do a full backup

BACKUP Database [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Full.bak’ WITH INIT

— Insert another 2 records

Insert INTO [Test] VALUES (‘transcation 2’)

Insert INTO [Test] VALUES (‘transcation 3’)

— Do a transaction log backup

BACKUP LOG [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Log.bak’ WITH INIT

— Insert another 2 records but this time no backup before delete the mdf file

Insert INTO [Test] VALUES (‘transcation 4’)

Insert INTO [Test] VALUES (‘transcation 5’)

— Verify the data before crash the database

SELECT * FROM [backupdemo].[dbo].[Test]

— simulate crash, take it offline and delete the mdf file

USE MASTER

Go

Alter database [backupdemo] set single_user with rollback immediate

Go

Alter database [backupdemo] SET OFFLINE

GO

— Delete backupdemo mdf

EXECUTE master.dbo.sp_configure N’show advanced options’, 1; RECONFIGURE

EXECUTE master.dbo.sp_configure N’xp_cmdshell’, 1; RECONFIGURE

EXECUTE master.dbo.xp_cmdshell N’Del c:\database\backupdemo.mdf’

EXECUTE master.dbo.sp_configure N’xp_cmdshell’, 0; RECONFIGURE

EXECUTE master.dbo.sp_configure N’show advanced options’, 0; RECONFIGURE

— Bring crash database back online

Alter database [backupdemo] SET ONLINE

— Backup the tail log. WITH NO_TRUNCATE, COPY_ONLY IS a must

BACKUP LOG [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Log_Tail.bak’

WITH NO_TRUNCATE, COPY_ONLY;

— Restore Full backup with NORECOVERY

Restore database [backupdemo]

FROM DISK = N’c:\database\backup\backupdemo_Full.bak’

WITH

MOVE N’backupdemo’ TO N’c:\database\backupdemo.mdf’,

MOVE N’backupdemo_log’ TO N’c:\database\backupdemo_log.ldf’,

REPLACE, NORECOVERY;

GO

— Restore Log backup with NORECOVERY

Restore LOG [backupdemo]

FROM DISK = N’c:\database\backup\backupdemo_Log.bak’

WITH NORECOVERY;

GO

— Restore tail log backup with NORECOVERY

Restore LOG [backupdemo]

FROM DISK = N’c:\database\backup\backupdemo_Log_Tail.bak’

WITH RECOVERY;

GO

— Verify the data

SELECT * FROM [backupdemo].[dbo].[Test]

Script download: https://github.com/chanmmn/SQLUtilities/tree/master/TailBackupRestoreScript

Reference: https://www.sqlshack.com/tail-log-backup-and-restore-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-server-ver15/?WT.mc_id=DP-MVP-36769