If you have a Web API or a Web Service and want to call from SQL Server to get data in your database, you can do that. In this blog, let's learn how to call a Web API or a Web Service form SQL Server.

Enable configuration in SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Sample API link
-- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile

Sample JSON Request
@Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Content Type
application/json

Create Store Procedure for Calling Web API.
--//========================
    -- if you learn more please visit my blog

    -- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return

Execute Stored Procedure
EXEC  Proc_CallApiFromSQL '8541254874','SE'

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

In our earlier article, we had learned how to create a login in SQL Server with help of GUI as well as T-SQL script. Granting read, write, execute, create, etc. in SQL Server comes under a security context, and being a Database Administrator, it’s very important to make sure that a user must have sufficient permission to access the database and its objects once he/she gets a new login for the server.

The Database Administrator must ensure that no other anonymous user can access the database to perform any unauthorized activity.

By default, the SQL Server denies access to database objects after you create a login for a user. Therefore, if you want to access the database objects with your login, you must have sufficient permissions provided by your database administrator.

Before proceeding, below are the details we’ve provided for the demo:
    Database: CSharpCornerDB
    User: Peter
    Connection Required: 2 (Admin Session, SQL Login Session)

Currently, we’ve connected with Admin login and have three tables with some data in it.

You can find attached script to create database and objects used in this demo. If you want to grant permissions to any user, you must be a member of sysadmin, db_owner or the SA account. Let’s begin with the demo.

Connect to your server session with the SQL login as in the following:

After successful authentication, you’ll get connected to the server with the username as in the following:

As we mapped our user to CSharpCornerDB database, we’ll connect to it. But if you try to connect to other database, you’ll end up with the following error message.

Msg 916, Level 14, State 1, Line 1
The server principal "MChand" is not able to access the database "SQL432DB" under the current security context.

Also if you try to expand other database node, SQL Server throws the following error message window. And the '+' sign disappears as shown below.

So, this simply means that the user can only connect to the database on which it is mapped. We’ll select our mapped database i.e. CSharpCornerDB. As we already know, we have three tables in our database, as mentioned earlier. But if you expand the Tables folder you won’t find any table in it. You’ll only get the following output:

Also, if you try to execute the following query to get data in tables, you’ll get the following error message.
    SELECT * FROM tblArticles 
    SELECT * FROM tblCategories 
    SELECT * FROM tblAuthors 

Error Message:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblCategories', database 'CSharpCornerDB', schema 'dbo'.

Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'tblAuthors', database 'CSharpCornerDB', schema 'dbo'.

This is nothing new but the default behavior of SQL Server to deny access on database objects after creating new login for user. If we want to access those objects, we require read permission for the user with which we’re connected.

To provide READ permission to the user on the database, connectthe server with your admin account or with Windows account and follow the below steps.

Expand Security - Logins, then Right Click on login and then click Properties.

You’ll get Login Properties window as in the following screenshot:

Go to User Mapping tab and select the database on which you want to give permission and at bottom select db_datareader as shown below.

Click Ok and you’re done.

The following is the T-SQL for the same.

    USE [CSharpCornerDB] 
    GO 
    EXEC sp_addrolemember N'db_datareader', N'Peter' 
    GO 

Now go to the session on which you’re connected with Peter login and refresh the Tables folder. If you see all the tables are now visible and are listed below.

Now if you execute the following select query, you’ll be able to see all the data in the table.

    USE CSharpCornerDB 
    GO 
    SELECT * FROM tblArticles 
    SELECT * FROM tblCategories 
    SELECT * FROM tblAuthors 

Also, if you run SELECT command to get table details, you’ll get output for both.

Now let’s insert another record into table tblArticles.
    USE CSharpCornerDB 
    GO 
    INSERT INTO tblArticles values('Strings in C#','Scott') 
    GO 

If you execute the above query, you’ll encounter the following error message:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

The error message simply states that INSERT permission is denied on the object. This means we don’t have permission to write any data in the database objects.

To give WRITE permission on database to the user, follow below steps.

Security - Logins, then double click on Login and you’ll get Login Properties window.

Go to User Mapping Tab and select the database on which you want to give permission.

Under ‘Database role membership for’ section, check ‘db_datawriter’ checkbox as shown below.

Click Ok and you’re done.

The following is the T-SQL script for the same.
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_addrolemember N'db_datawriter', N'Peter' 
    GO 

Now if you execute INSERT command from Peter session, it’ll add the record without any error.

After getting WRITE permission, you can run INSERT, UPDATE and DELETE commands.

If you want to revert back the action you can execute the following query from admin session.
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_droprolemember N'db_datareader', N'Peter' 
    GO 
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_droprolemember N'db_datawriter', N'Peter' 
    GO 

Conclusion
So, this is an article based on SQL Security where we learned how to provide read/write permission for user to read and write data from/to SQL Server database. If you are new to SQL, please read What is SQL.

Hope this article helps you o understand how to provide read write permissions. There are also other permissions which you can try with database on your server. If you need any help in this, please let me know. I’ll try my best to solve those.

Please provide your valuable feedback and comments that will help me in providing a better article the next time. Till then keep learning and keep sharing.

HostForLIFEASP.NET SQL Server 2019 Hosting

Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.

A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.

Syntax
WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

To view the CTE results, use a select query with the name of the CTE expression.
Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

Types of CTE(Common Table Expression)
There are 2 types of CTEs,
    Recursive CTE.
    Non-Recursive CTE.

Recursive CTE
A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.

A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.

We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.

First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.
Declare @RowNo int =1;
;with ROW_CTE as
   (
      SELECT @RowNo as ROWNO
        UNION ALL
      SELECT  ROWNO+1
  FROM  ROW_CTE
  WHERE RowNo < 10
    )

SELECT * FROM ROW_CTE

Output
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Non-Recursive CTE
Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.

As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.

Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.
;with ROW_CTE(ROWNO) as
   (
     SELECT
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name
WHERE id <= 10
    )

SELECT * FROM ROW_CTE

Output:
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Nested CTE (Common Table Expression)
WITH First_CTE
   AS (SELECT 1 EmpId, 'Peter James' Name)

  ,Second_CTE
   AS (SELECT EmpID, Name, 'London' State FROM First_CTE)

SELECT *   FROM Second_CTE

HostForLIFEASP.NET SQL Server 2019 Hosting

SQL Server char data types can be confusing if you are not an experience developer. You may also get to ask a question in an interview, what is the difference between char and varchar in SQL Server? SQL Server has char, varchar, nchar, and nvarcar data types that all are used for storing character data. In this article, learn the difference between CHAR, VARCHAR, NCHAR and NVARCHAR data types. Actually it is simple but sometimes people get confused.

To store data as characters, numeric values and special characters in a database, there are 4 data types that can be used. So what is the difference among all 4 of these data types?
    CHAR vs VARCHAR
    NCHAR vs NVARCHAR

Considering an example, we will look into each one of them.
    DECLARE @string CHAR(20) 
    SET @string = 'Robin' 
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

Note: The LEN() method provides the length of a character excluding trailing blanks stored in the string expression whereas the DATALENGTH() method provides the number of byte spaces occupied by the characters in a string expression.
 
As you know we represent the character values within single quotes, for example 'Robin'. But do you know we can represent these same characters within double quotes similar to programming languages representing a string, for example “Robin”? This can be done by setting the value:
    SET QUOTED_IDENTIFIER OFF

By default, it is set to ON
 
CHAR vs VARCHAR
Talking about the CHAR data type:
    It is a fixed length data type
    Used to store non-Unicode characters
    Occupiers 1 byte of space for each character

If the value provided to a variable of CHAR data type is shorter than the length of a column of declared the size of the variable, then the value would be right-padded with blanks to match the size of column length.
    DECLARE @string CHAR(20) 
    SET @string = 'Robin' 
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, the bytes occupied by the variable are 20 even though the length of the characters is 5. That means that irrespective of the character stored in the column, it will occupy all bytes to store the value.

About the VARCHAR data type:
    It is a variable length data type
    Used to store non-Unicode characters
    Occupies 1 byte of space for each character
    DECLARE @string VARCHAR(20) 
    SET @string = 'Robin' 
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, it is showing DATALENGTH as 5 which means it will use only the number of bytes equal to the number of characters. This will allow me to avoid wasting database space.

Note:  If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a CHAR column defined as NULL is considered as VARCHAR.

When to use what?
If you are sure about the fixed length of the data that would be captured for any specific column then go for CHAR data type and if the data may vary then go for VARCHAR.
 
NCHAR vs NVARCHAR
Similar to CHAR data type, the NCHAR data type:
    Is a fixed length data type
    Used to store Unicode characters (for example the languages Arabic, German and so on)
    Occupies 2 bytes of space for each character

    DECLARE @string NCHAR(20) 
    SET @string = 'Robin' 
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, the data length column shows 40 bytes even though the size declared is 20. It's because NCHAR holds 2 bytes of space for each character.

About the NVARCHAR data type:
    It is a variable-length data type
    Used to store Unicode characters
    Occupies 2 bytes of space for each character
    DECLARE @string NVARCHAR(20) 
    SET @string = 'Robin' 
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As in the output above, you will observe DATALENGTH column is showing only 10 as a value. That is because it occupies 2 bytes of space for each character and the data length is only 5 characters, therefore it will occupy 10 bytes of space in the database.

When to use what?
If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR.
 
Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that.

This article explains the differences between CHAR, VARCHAR, NCHAR and NVARCHAR data types. All of these data types are used to store characters, numbers or special characters. I hope you like this small article and will that it will be helpful to you at some point of time. Leave your comments whether its good or bad. Sharing is valuable no matter what :)

HostForLIFEASP.NET SQL Server 2019 Hosting

UNION Keyword
The result set from two or more SELECT operations is combined by the UNION command but only distinct values.

There are two basic rules are followed when you want to use Union Keyword or Union All.

    All queries must have the same amount of columns and column ordering.
    Each query requires that the data types of the columns on the involving table be the same or compatible.

Syntax
SELECT <COLUMN1>,<COLUMN2> FROM <TABLE1>
UNION
SELECT <COLUMN1>,<COLUMN2> FROM <TABLE2>

Example
SELECT Name FROM Employee
UNION
SELECT Name FROM Worker

In the above example, it returns only unique names from both tables.

UNION ALL Keyword
The result set from two or more SELECT operations is combined by the UNION ALL command, and it also allows duplicate values.

SELECT <COLUMN1>,<COLUMN2> FROM <TABLE1>
UNION ALL
SELECT <COLUMN1>,<COLUMN2> FROM <TABLE2>

Example
SELECT Name FROM Employee
UNION ALL
SELECT Name FROM Worker

In the above example, it returns all names from both tables, and if names are duplicates, it also returns.

The UNION and UNION ALL command combine the result set of two or more SELECT statements.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, you will learn how to format dates in SQL Server using the format function. In this article, we will learn how to format dates using Format function in SQL Server.
Built-in function in SQL Server to get the DateTime value in a specific format

By using some built-in function in SQL Server we can get the DateTime value in a specific format.

For example,
GETDATE()

It returns server DateTime in “YYYY-MM-DD HH:mm:ss.fff” format.
SELECT GETDATE() AS [GETDATE()]
Result:-2022-06-09 12:28:37.787

GETUTCDATE()
It returns DateTime in GMT.
SELECT GETUTCDATE() AS [GETDATE()];
Result:-2022-06-09 07:10:54.350

SYSDATETIME()
It returns the server’s DateTime
SELECT SYSDATETIME() AS [GETDATE()];
Result:2022-06-09 12:41:46.8713228

SYSDATETIMEOFFSET()
It returns the server’s DateTime with time zone in which SQL Server instance is running.
SELECT SYSDATETIMEOFFSET() AS [GETDATE()];
Result:2022-06-09 12:42:15.7936382 +05:30

SYSUTCDATETIME()
It returns server DateTime in GMT.
SELECT SYSUTCDATETIME() AS [GETDATE()];
Result:2022-06-09 07:12:54.4664815

CURRENT_TIMESTAMP
It returns current DateTime of the server.
SELECT CURRENT_TIMESTAMP AS [GETDATE()];
Result:2022-06-09 12:43:40.650

After the CONVERT function, SQL Server added a function (FORMAT) to handle date formatting, giving us a new way to format dates in SQL Server.

To format the date and time data types from a date column (Date, DateTime, etc. Data type) in a table or a variant such as GETDATE(), use the FORMAT function.
Date Format with FORMAT Function

We have many ways to format dates as given below

DD/MM/YYYY
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date;
Result:09/06/2022

DD/MM/YYYY, HH:MM:SS
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date;
Result:09/06/2022, 04:56:44

DDDD,MMMM,YYYY
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date;
Result:Thursday, June, 2022

MMM DD YYYY
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date;
Result:Jun 09 2022

MM.DD.YY
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.09.22

MM-DD-YY
SELECT FORMAT (getdate(), 'MM-dd-yy') as date;
Result:06-09-22

HH:MM:SS TT
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date;
Result:05:17:37 PM

MM/DD/YYYY (Standard: USA)
SELECT FORMAT (getdate(), 'd','us') as date;
Result:06/09/2022

YYYY-MM-DD HH:MM:SS TT
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date;
Result:2022-06-09 05:18:55 PM

YYYY.MM.DD HH:MM:SS T
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date;
Result:2022.06.09 05:19:53 P

DDDD,MMM,YYYY in Spanish
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date;
Result:jueves, junio, 2022

DDDD DD, MMMM,YYYY in Japanese

SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date;
Result:木曜日 09, 6月, 2022

Date Format with Culture
We can get regional formatting by using the culture option as shown below:

English-USA
SELECT FORMAT (getdate(), 'd', 'en-US') as date;
Result:6/10/2022

French-France
SELECT FORMAT (getdate(), 'd', 'fr-FR') as date;
Result:10/06/2022

French - Belgium
SELECT FORMAT (getdate(), 'd', 'fr-BE') as date;
Result:10-06-22

French - Canada
SELECT FORMAT (getdate(), 'd', 'fr-CA') as date;
Result:2022-06-10

Danish - Denmark
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.10.22

Dari - Afghanistan
SELECT FORMAT (getdate(), 'd', 'prs-AF') as date;
Result:1401/3/20

Simplified Chinese
SELECT FORMAT (getdate(), 'd', 'zh-CN') as date;
Result:2022/6/10

Divehi - Maldives
SELECT FORMAT (getdate(), 'd', 'dv-MV') as date;
Result:10/06/22

Bosnian Latin
SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date;
Result:10. 6. 2022.


isiXhosa / Xhosa - South Africa
SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date;
Result:2022-06-10

Hungarian - Hungary
SELECT FORMAT (getdate(), 'd', 'hu-HU') as date;
Result:2022. 06. 10.

Spanish - Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date;
Result:10/6/2022

Here is a list of all CultureInfo codes along with country names and language.

As you saw above, we have used a lot of options for date and time formatting, which are detailed below,

• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

HostForLIFEASP.NET SQL Server 2019 Hosting

Triggers
Triggers are a special type of procedure that are fired automatically when a query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way. This article with code snippet explains how to write create, update, and delete triggers in SQL.

Trigger Creation
The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.
CREATE TRIGGER trigger_name ON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] As sql_statements

FOR is same as AFTER but it is for backward compatibility.

Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many number of AFTER triggers. Since two or more triggers on table can cause confusion to manage and to debug however its better to place all the related code in one trigger for each action. A view can't have AFTER triggers.

CREATE TRIGGER Shashi_INSERT_UPDATEON Shashi AFTER INSERT,UPDATEASUPDATE ShashiSET ln_name = UPPER(ln_name) WHERE Shashi_id in (SELECT Shashi_id from Inserted)

The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that's created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.

An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.

An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.

INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.

How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.

The syntax of the DROP triggers statement.
DROP TRIGGER trigger_name [,...]

The syntax of the ALTER TRIGGER statement
ALTER TRIGGER trigger_nameON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]As sql_statements

HostForLIFEASP.NET SQL Server 2019 Hosting

For development or testing purposes, SQL users often need to restore the same database on the same PC or server with a different name. In this tutorial, I am going to explain the detailed steps to restore the same database with different names in SQL Server.

We advise against attaching or restoring databases from unauthorized or untrusted sources for security reasons. These databases may contain malicious code that can alter the physical database structure or schema, run unwanted T-SQL code, or cause problems.

This tutorial will show you various ways to restore the same database with different names in SQL Server.
Method 1 - Using SQL Server Management Studio (SSMS)

Step 1
Open SQL Server Management Studio (SSMS) and connect to the SQL Server Instance.

Step 2
In Object Explorer, right-click Databases and select "Restore Database...".

Step 3
The Restore Database window will appear on the screen. On the General page, use the Source section to specify the source and location of the backup set to be restored. Now, select the Device option and click the Browse (...) button.

Step 3a
Select backup devices window will appear on the screen. Click the Add button to select one or more backup device(s) for the Backup media box.

Step 3b
Locate and select the SQL database backup file(s) (.bak) that you want to restore, and click the OK button to proceed.

Step 3c
After adding the desired backup file to the Backup media list box, click on the OK button to proceed.

Step 4
In the Destination section, the Databases box is automatically populated with the name of the database to be restored. Now, you need to rename the destination database, to do so enter the new name in the Database box. And, leave the default values as it is in the "Restore to" box and "Backup sets to restore" grid.

For example, here I have renamed the Destination Database to "CSHARPCORNER_Backup".

Step 5
Going to the Files page, proceed with the steps to specify the new location or name of the database files (Data and Log files).
    The file names in our case are "CSHARPCORNER_Backup.mdf" and "CSHARPCORNER_Backup_log.ldf," respectively.

Note:

• When you rename the destination database, SSMS itself renames both the data and log files to "Restore As". I remember that in earlier versions of SSMS you had to change manually the names of the files, otherwise, they would conflict with existing files.
• To prevent conflicts, avoid placing new database files in the same directory as the existing database. If necessary, modify the directory name. However, make sure the drive you are using has enough space.

Step 6
Proceeding with the steps again, go to the Options page. Here, you need to make sure that the following options are checked. And, click the OK button.
    "Overwrite the existing database (WITH REPLACE)" under the Restore options section.
    "Take tail-log backup before restore" under the Tail-log backup section. (optional)

Congratulations! the "Database 'CSHARPCORNER_Backup' restored successfully" message shows that our backup database has been successfully restored.

Method 2 - Using Transact-SQL (T-SQL)
With the help of T-SQL, users can also restore the database with a different database name. Follow the below steps to proceed.

Step 1
Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore. This statement returns a list of the database and log files contained in the backup set. The basic syntax is as follows:
RESTORE FILELISTONLY FROM DISK = <Backup_file_location>

Step 2
To restore a full database backup, use the RESTORE DATABASE statement. Use the MOVE option to relocate each of the database files (.mdf & .ldf) and to avoid conflicts with existing files. Because data and log files are restored to their original locations by default. Use the following basic T-SQL syntax to restore the database to a new location and a new name.
RESTORE DATABASE [NEW_DATABASE_NAME]
FROM DISK = N'<BACKUP_FILE_PATH/BACKUP_FILE_NAME.BAK>'
[ WITH
{
    [ **RECOVERY** | NORECOVERY ]
    [ , ] [ FILE = { *backup_set_file_number* | @*backup_set_file_number* } ]
    [ , ] MOVE '*Logical_File_Name_In_Backup*' TO '*Operating_System_File_Name*' [ ,...*n* ]
}

Example
Execute the following T-SQL queries to restore the database with the same name on the same PC or server. Follow the below steps to proceed.

Step 1
Determine the logical and physical names of the files in the backup set by executing the following query.
RESTORE FILELISTONLY FROM DISK = N'F:\FinalDev\Database Backups\CSHARPCORNER\CSHARPCORNER.bak'

Step 2
Once you have obtained the logical and physical names of the database files, execute the below query to restore the same database with a different name.
RESTORE DATABASE [CSHARPCORNER_Backup] FROM DISK = N'F:\FinalDev\Database Backups\CSHARPCORNER\CSHARPCORNER.bak'
WITH FILE = 1,
MOVE N'CSHARPCORNER' TO N'F:\FinalDev\Microsoft SQL Server\DATA\CSHARPCORNER_Backup.mdf',
MOVE N'CSHARPCORNER_log' TO N'F:\FinalDev\Microsoft SQL Server\DATA\CSHARPCORNER_Backup_log.ldf',
NOUNLOAD, REPLACE, STATS = 5
GO

Problems associated with restoring the database

Both methods of restoring the database with the new name are effective and efficient. However, these techniques are not without drawbacks, and you may encounter difficulties such as the following:

    Invalid file format.
    Invalid source file path.
    Incorrect data and log file name.
    Inconsistency errors in the database.
    Insufficient disk space to restore the database.
    Inadequate SQL permissions to run the T-SQL statement.
    To restore an encrypted database, you must have the certificate or asymmetric key that was used to encrypt it. Otherwise, you cannot restore the database without a certificate or asymmetric key.

Note
Apart from these issues, incorrect implementation of a single step to restore a database can corrupt the database and result in potential data loss. Additionally, you may encounter the "SQL database restore failed, database in use" error message.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog, we learn how to calculate credit debit Transactions like banking report using SQL Sever.

Step 1:  Create Table valued function for calculate credit, debit amount with total balance
USE [SqlBank]

CREATE   FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN

DECLARE @TempAC_ID BIGINT;

SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
  C ON A.CID=C.CID WHERE c.CID=@CID)

DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
 CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime  ,
Remarks varchar(max)  , IsmailSend int
)

INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,
 CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
 THEN tr.TR_Amt ELSE 0 END  Balance,
 Tr.TR_CrDrType  ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
 Tr.Remarks , Tr.IsmailSend

FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
      join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;

WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate  ,t2.Remarks ,t2.IsmailSend
)

INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
 )
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType  ,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran  with(nolock)
WHERE AC_ID=@TempAC_ID

RETURN
END

Step 2: Create Procedure & Call above function in Procedure

USE [SqlBank]

CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN
DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint
DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;

BEGIN TRAN Tbl_Transaction_Tran
   BEGIN  TRY
      IF(@Flag = 'Tran')
      BEGIN

IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
BEGIN
 SELECT  a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
 ,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
   FROM dbo.FNGetTransaction(@CID) a
--      JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType
END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
    END
ELSE IF(@Flag = 'IN')
    BEGIN
    SET @Temp_ACID = (SELECT Top 1 A.AC_ID  FROM Tbl_Account A with(nolock)
     Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)
    DECLARE @SenderName varchar(max)
        SET @SenderName = (SELECT Top 1 c.CName  FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)
    DECLARE @ReciverName varchar(max)
       SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID
       WHERE A.AC_No=@AC_No)
SET @TempTRAmount = (
 SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
   FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)
if(@TR_Amt > @TempTRAmount)
BEGIN
Select 'Insuffitient Balance' as msg
END
ELSE
  BEGIN
  Declare @FixScratchAmt decimal(18,2)=500;
  --if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
  --begin
  Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
  ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)
  set @Tran_ID = @@IDENTITY;
  set @TR_ID= @Tran_ID;
  set @Tran_ScopID= SCOPE_IDENTITY();
  Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)
  if(@TR_Amt >= @FixScratchAmt)
  begin
   Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
  END

Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
  CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
  +Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
  set @MMTR_ID = @@IDENTITY;
  set @MTR_ID = @MMTR_ID;
    END
    END
IF(@@TRANCOUNT > 0)
  BEGIN
  COmmit tran Tbl_Transaction_Tran
  END
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
            BEGIN
            ROLLBACK TRAN Tbl_Transaction_Tran
            END
            DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
            ,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
            ,@ERRORSTATE varchar(max), @ErroFrm varchar(max)

            SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
                   @ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
                  @ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'

        EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm
END CATCH
END

Step 3: Execute Procedure to Check Report
exec [dbo].[PROC_TRansaction]

@CID =2,@Flag='Tran'

I hope it works!

HostForLIFEASP.NET SQL Server 2019 Hosting

If you are a database administrator and facing issues while reconnecting log files to MDF files, read this blog. We have shared some easy ways to reconnect the log file to the MDF file.

Methods to Reconnect Log File to MDF File
Here in this blog, I am going to discuss two simple methods to reconnect SQL Log file to MDF database files. The methods are:

    SQL Server Management Studio
    Reconnect Log File without the Transaction File

Let’s explain both methods separately.
SQL Server Management Studio (SSMS) to Reconnect Log File to MDF File
    Login to your computer and search for SQL Server Management Studio.
    Connect to the database either by selecting Windows or Server Authentication.

    Right-click on the Database option and choose Attach. It will lead you to the attached database wizard.
    Now, click on the Add button.
    Here, from the list of database files, select the MDF file you wish to connect with the log file and click on the OK button.
    Click on the message “transaction log file not found” and click OK.
    Finally, reconnect the Log file to the MDF file whether it is connected properly or not.

This is how you can easily reconnect LDF files with the MDF database files.
Reconnect Log File without the Transaction File

Step 1
Specify the DB name and the .MDF file location using the Create Database with Attach option script given below,
USE [master]
Go
CREATE DATABASE [Tester] ON
(FileName = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Tester.mdf’)
For Attach
GO

Step 2
Once the script is executed, a message will display saying a new transaction log file was created.
Thanks!

HostForLIFEASP.NET SQL Server 2019 Hosting