Convert Rows To Columns Using Dynamic Pivot In SQL Server

In this article, we will show How to convert rows to columns using Dynamic Pivot in SQL Server.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Note: The demo in this article based on database from the TechNet Gallery.


Scenario

We have decided to build a dynamic database by creating only three tables as shown below:

 Continue reading “Convert Rows To Columns Using Dynamic Pivot In SQL Server”

Advertisements

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

I have an ASP.Net website that connects to SQL server always-on failover cluster instance.

But I have a strange behavior when I configured the Connection String inside Web.Config as SQL Authentication, the connection has been connected successfully.

Server=load balancer IP;Database=Database Name;User Id=User; Password=Password; 

Bu when I tried to configure the Connection String as Windows Authentication.

Server=load balancer IP;Database=Database Name;Integrated Security =true;

I got the following error.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.
This could be because the pre-login handshake failed or the server was unable to respond back in time.

connection-timeout-expired.png

I made sure that the application pool service account has elevated privilege with SQL Server as public server role and user mapping database. but unfortunately, the issue still persist.

Cause: Continue reading “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment”

How to Check SQL Server Instance Connectivity from the application server to database server for a specific user without using SQL Server Management Studio

In some situation, you may need to check the SQL Server Instance connectivity from the application server to database server for a specific user especially when you don’t have access to SQL Servers on your farm based on your company policy.

You may try to perform Ping cmdlet for the SQL Server IP and Telnet to SQL TCP port.but that means There’s a network connectivity between two servers and you can reach the database server from the application server.

But what about if you need to test connectivity to SQL Server Instance for a specific user without using SQL Server Management Studio?

In this case, you can test connectivity to SQL Server Instance by creating a data link UDL file as I have mentioned below:

Steps: Continue reading “How to Check SQL Server Instance Connectivity from the application server to database server for a specific user without using SQL Server Management Studio”

Get The build number of the latest Cumulative Update / Service Pack that was installed in SQL Server

In this article, I’ll show How to

Get the build number of the latest Cumulative Update / Service Pack that has been installed in SQL Server.

Scenario:

I decided to configure PowerPivot for SharePoint 2013 Enterprise Edition with Service Pack 1 that requires installing SQL Server 2012 Service Pack 1 as a prerequisite.

So that I need to check if the SQL Server Service Pack 1 has been installed on SQL Server or not!

So in this article, by getting the build number of the latest Cumulative Update / Service Pack that has been installed in SQL Server by following the mentioned below:

Steps: Continue reading “Get The build number of the latest Cumulative Update / Service Pack that was installed in SQL Server”

Shrink a transaction log file Maintenance Plan in SQL Server

The Major factor that faces me when I was trying to create a Shrink a transaction log file Maintenance Plan is the shrink option in Maintenance Tasks that include shrink operation for both data file and transaction log file, and there is not an option for only log file!

maintinance plan wizard 29

In this article, I am trying to perform a shrink operation for only the transaction log file via Maintenance Plan to run on a regular basis or on demand based on your requirement.

Before you start, you should be aware of the following

  • To create or manage maintenance plans, you must be a member
    of the sysadmin fixed server role.
  • The SQL Server performance will be affected during executing The Shrink operation. Thence, I advise performing the shrink operation out working hours.
  • The Shrink operation causes index fragmentation and can slow the performance of queries that search a range of the index. So you should consider rebuilding the indexes to eliminate the fragmentation before the shrink operation has been completed .

Therefore, It’s not recommended to perform the shrink operation periodically! Only in some circumstances that you need to reduce the physical size. (Check the possible responses to a full transaction log and suggested how to avoid it in the future).

Steps: Continue reading “Shrink a transaction log file Maintenance Plan in SQL Server”

BACKUP LOG cannot be performed because there is no current database backup in SQL Server

I got the following error:

BACKUP LOG cannot be performed because there is no current database backup in SQL Server

BACKUP LOG cannot be performed because there is no current database backup SSMS1

When I tried to take a log Backup via Management Studio by doing the mentioned steps below: Continue reading “BACKUP LOG cannot be performed because there is no current database backup in SQL Server”

Service ‘Microsoft SqlServer Management IRegistrationService’ not found During opening SQL Management Studio

When I tried to open SQL Management Studio for SQL Server 2012, I got the following error:

Service 'Microsoft.SqlServer.Management.IRegistrationService' not found

microsoft.sqlserver.management. registration service not found

In my case, I pressed OK and everything is working properly.but I need to avoid showing this error again,

Cause: Continue reading “Service ‘Microsoft SqlServer Management IRegistrationService’ not found During opening SQL Management Studio”