SQL Server: Get the Detailed Information Via SERVERPROPERTY

In this article, we will explain

  • How to get the SQL Server Instance Information using “SERVERPROPERTY”?
  • How to get the SQL Server Instance Information remotely?

What’s the SERVERPROPERTY?

SERVERPROPERTY is a System Defined function used to return the SQL Server Instance Information.

SERVERPROPERTY Syntax

SERVERPROPERTY ('propertyname')

propertyname is one of the following values.

  • MachineName.
  • ComputerNamePhysicalNetBIOS.
  • ServerName.
  • InstanceName.
  • InstanceDefaultDataPath.
  • InstanceDefaultLogPath.
  • Edition.
  • EditionID.
  • EngineEdition.
  • ProductBuild.
  • ProductBuildType.
  • ProductLevel.
  • ProductMajorVersion.
  • ProductMinorVersion.
  • ProductUpdateLevel.
  • ProductVersion.
  • BuildClrVersion.
  • Collation.
  • LCID.
  • IsSingleUser.
  • IsIntegratedSecurityOnly.
  • IsHadrEnabled.
  • HadrManagerStatus.
  • IsAdvancedAnalyticsInstalled.
  • IsClustered.
  • IsFullTextInstalled.
  • ProcessID.

Continue reading “SQL Server: Get the Detailed Information Via SERVERPROPERTY”

Advertisements

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”

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”

How to open Microsoft SQL Management Studio after the evaluation period had expired again in SQL Server 2012

In the previous  article, I have explained How to extend the evaluation period for SQL Server 2012 ?.Evaluation perod has expired

In this hint, I will answer the following questions: Continue reading “How to open Microsoft SQL Management Studio after the evaluation period had expired again in SQL Server 2012”

This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database

After I restarted a SharePoint VM, then I tried to browse a SharePoint Site. I got the following error:

This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database. To connect this server to the server farm, use the SharePoint Products Configuration Wizard.

this opeeration can be performed

Cause: Continue reading “This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database”