SP in Fundamental level in SQL Server

SP_RENAMEDB: Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax: SP_RENAMEDB ‘OLD DATABASENAME’, ‘NEW DATABASENAME’

            E.g.: SP_RENAMEDB  ‘NRSTT’, ‘NRSTTS’

The above statement renames (changes the database name) NRSTT to NRSTTS

SP_RENAME: This stored procedure is used for changing the name of the table and for changing the name of the column

i. Syntax to change the name of the table

SP_RENAME ‘OLD TABLENAME’, ‘NEW TABLENAME’

E.g.      SP_RENAME ‘EMP’, ‘EMPLOY’

The above stored procedure changes the name of EMP table to EMPLOY

ii. Syntax to change the name of the column

SP_RENAME ‘TABLE.OLDCOLUMN NAME’, ‘NEW COLUMNNAME’

E.g.      SP_RENAME ‘STUDENT.ADR’, ‘ADDRESS’

The above stored procedure changes the name of ADR column to ADDRESS in STUDENT table.

SP_HELP: This stored procedure is used to display the description of a specific table.

Syntax: SP_HELP TABLENAME

E.g.: SP_HELP EMP

The above stored procedure displays the description of EMP table

SP_DATADASES: This Stored procedure displays the list of databases available in SQL Server.

Syntax: SP_DATABASES

SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax: SP_TABLES

SP_HELPDB: This stored procedure is used to display the description of  master and log data file information of a specific database

Syntax: SP_HELPDB Database-Name

Ex: SP_HELPDB SAMPLE

SP_SPACEUSED: This stored procedure is used to find the memory status of the current database

Syntax: SP_SPACEUSED

How to Install SQL Server on Linux CentOS 7 - SQL Server on Linux

# Let's check if mssql-server repository already exists?

sudo ls /etc/yum.repos.d | grep mssql-server.repo

# If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install

sudo cat/etc/yum.repos.d/mssql-server.repo

sudo rm -rf /etc/yum.repos.d/mssql-server.repo

# Configure and Download SQL Server Repository

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config...

# Install SQL Server using below commands

sudo yum install -y mssql-server

# Configure SQL Server and Setup SA password

sudo /opt/mssql/bin/mssql-conf setup

# Restart SQL Server Services

systemctl restart mssql-server

# Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config...

sudo yum install -y mssql-tools unixODBC-devel

#Set Environment Tools to your Path Environment


# Connect to SQL Server using sqlcmd tool

sqlcmd -S localhost -U SA -P Pass@123


# Connect to SQL Server Remotely using SQL Server Authentication

SSMS - Error and Resolution

# Enable Firewall port 1433


sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload



# Enable and start SQL Server Agent services
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true


install sql server on centos 7
install sql server  2017 on CentOS 7
install sql server management studio on linux

How To Find The Highest Salary In SQL Server

Introduction:

In this blog we will discuss how to find the highest salary & the second highest salary..

Query to find the highest salary:

SELECT * FROM EMPLOYEE ORDER BY SALARY DESC  

SELECT MAX(SALARY) FROM EMPLOYEE   

Query to find second highest salary:

SELECT*FROM EMPLOYEE ORDER BY SALARY DESC  

SELECT MAX(SALARY) FROM EMPLOYEE  

WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE) 

Database 'DBA' cannot be opened. It is in the middle of a restore. (SQL Server, Error: 927)

TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'DBA'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Database 'DBA' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

Solution:

There was a bug in SQL server management studio.. Please try to execute following stmts..


On mirror server
ALTER DATABASE DBA
   SET PARTNER = 'TCP://primaryservername.domain.com:5022'


On primary server

ALTER DATABASE DBA
   SET PARTNER = 'TCP://Mirrorservername.domain.com:5022'


Thanks

SQL Server Database Migration Checklist

Please find the below mentioned Database Migration checklist

Sr.No	Pre Migration Checklist
1	Analyze the disk space of the target server for the new database.
2	Confirm the data and log file location for the target server.
3	Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level,etc).
4	Collect the information of dependent applications, make sure application services will be stopped during the database migration.
5	Collect the information of database logins, users and their permissions.
6	Check the database for the Orphan users if any.
7	Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers).
8	Check, if the database is part of any maintenance plan.
	Database Migration Checklist
Sr No.
1	Stop the application services.
2	Change the database to single user mode.
3	Take the latest backup of all the databases involved in migration.
4	Stop the SQL Services on live server.
5	Copy the backup from live to destination server.
6	Restore the databases on the target server on the appropriate drives.
7	Cross check the database properties as per pre-migration checklist output.
8	Execute the output of Login transfer script on the target server, to create logins on the target server.
9	Check for Orphan Users and Fix Orphan Users.
10	Execute DBCC UPDATEUSAGE on the restored database.
11	Rebuild Indexes ,As per the requirement.
12	Update statistics.
13	Recompile procedures.
14	Configure Full backup, Log backup, integrity check, rebuild index jobs.
	Post Migration Checklist
Sr No.
1	Check the integrity of database.
2	Start the application services, check the application functionality.
3	Check the SQL Server Error Log for login failures and other errors.