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

SQL Server: How to get the current installed update level

Consider you need to install a new Cumulative Update or Service Pack on your current SQL Server, but you don’t know what’s the current update level that already installed in your environment!!

In this article, we will explain

How to detect the current Cumulative Update / Service Pack that was installed on SQL Server.


Detect the Current Cumulative Update / Service Pack Installed

You can use the ServerProperty as a system defined function to return the SQL Server Instance information like the ProductLevel and ProductUpdateLevel

The ProductLevel returns the version level of the SQL Server instance as the following.

  • RTM‘ = Original release version
  • SPn‘ = Service pack version
  • CTPn‘, = Community Technology Preview version

Example 


select SERVERPROPERTY('ProductLevel') as 'Product Level'

Detect the Current Cumulative Update / Service Pack

Continue reading “SQL Server: How to get the current installed update level”

Convert Rows To Columns Using Dynamic Pivot In SQL Server

In this article, I will explain

How to convert rows to columns using Dynamic Pivot in SQL Server?

Download The code sample for this demo from TechNet Gallery: Dynamic Database Structure – Convert Rows to Columns Using Dynamic Pivot


Scenario

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

convert rows to columns using Dynamic Pivot in SQL Server

  • The “Tables” will be used to hold the table names like Users, Products….etc.

Tables - convert rows to columns using Dynamic Pivot in SQL Server

  • The “Fields” will be used to hold the fields name related to each table.

convert rows to columns using Dynamic Pivot in SQL Server

  • The “Field Value” will be used to hold the value of each field.

Field Values - convert rows to columns using Dynamic Pivot in SQL Server

  • The relationship between the three tables is one-many and the ERD looks like the below:

convert rows to columns using Dynamic Pivot in SQL Server

The desired data should look like:

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.


Steps

Get a list of the “Field Values” (Rows)

Get a list of “Field Values” that should be shown as Rows by doing the following:

  • Create a table “data-query” that will hold the field data values.
BEGIN try
 DROP TABLE ##dataquery
END try

BEGIN catch
END catch

CREATE TABLE ##dataquery
 (
 id INT NOT NULL,
 tablename VARCHAR(50) NOT NULL,
 fieldname VARCHAR(50) NOT NULL,
 fieldvalue VARCHAR(50) NOT NULL
 );
  • Query the field values data filtered by “TableID” and insert the output into the created table in the above step.
INSERT INTO ##dataquery
SELECT Row_number()
 OVER (
 partition BY (fields.fieldname)
 ORDER BY fieldvalue.fieldvalue) ID,
 tables.tablename,
 fields.fieldname,
 fieldvalue.fieldvalue
FROM tables
 INNER JOIN fields
 ON tables.tid = fields.tid
 INNER JOIN fieldvalue
 ON fields.fid = fieldvalue.fid
WHERE tables.tid = @TableID

Output

convert rows to columns using Dynamic Pivot in SQL Server

Get a list of the “Fields” (Columns)

Retrieve the list of the fields filtered by “TableID” by using the below SQL statement


DECLARE @DynamicColumns AS VARCHAR(max)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
 + Quotename(fieldname)
FROM (SELECT DISTINCT fieldname
 FROM fields
 WHERE fields.tid = @TableID) AS FieldList

COALESCE: Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Quotename: Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.Build the Dynamic Pivot Table Query

Build the Dynamic Pivot Table Query

In this section, we will combine the above two queries to can build our Dynamic Pivot


DECLARE @FinalTableStruct AS NVARCHAR(max)

SET @FinalTableStruct = 'SELECT ' + @DynamicColumns +
' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in (' + @DynamicColumns + ') ) p '

EXECUTE(@FinalTableStruct)

Output

convert rows to columns using Dynamic Pivot in SQL Server


Convert rows to columns using Dynamic Pivot in SQL Server

Finally, the Dynamic Pivot Script should look like


DECLARE @TableID AS INT

SET @TableID=1 -- Set Table ID

-- Get a list of the "Field Value" (Rows)
BEGIN try
DROP TABLE ##dataquery
END try

BEGIN catch
END catch

CREATE TABLE ##dataquery
(
id INT NOT NULL,
tablename VARCHAR(50) NOT NULL,
fieldname VARCHAR(50) NOT NULL,
fieldvalue VARCHAR(50) NOT NULL
);

INSERT INTO ##dataquery
SELECT Row_number()
OVER (
partition BY (fields.fieldname)
ORDER BY fieldvalue.fieldvalue) ID,
tables.tablename,
fields.fieldname,
fieldvalue.fieldvalue
FROM tables
INNER JOIN fields
ON tables.tid = fields.tid
INNER JOIN fieldvalue
ON fields.fid = fieldvalue.fid
WHERE tables.tid = @TableID

--Get a list of the "Fields" (Columns)
DECLARE @DynamicColumns AS VARCHAR(max)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
+ Quotename(fieldname)
FROM (SELECT DISTINCT fieldname
FROM fields
WHERE fields.tid = @TableID) AS FieldList

--Build the Dynamic Pivot Table Query
DECLARE @FinalTableStruct AS NVARCHAR(max)

SET @FinalTableStruct = 'SELECT ' + @DynamicColumns
+
' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in ('
+ @DynamicColumns + ') ) p '

EXECUTE(@FinalTableStruct)

Output
How to use Dynamic Pivot in SQL


Applies To
  • SQL Server 2017.
  • SQL Server 2016.
  • SQL Server 2012.
  • SQL Server 2008.

Download

To download the Database Backup and the Dynamic Pivot Script Check TechNet Gallary.

 

Scale-Out Deployment for SQL Reporting Service (SSRS)

In this article, I will explain

How to Scale-Out SQL Reporting Service (SSRS) deployment (Always-on)?


Scenario

I have 2-Tire SharePoint Farm

  • 1 WFE + 1 APP Servers.
  • 2 Database Servers (Always-on).
  • Reporting Service Native Mode.

SharePoint Farm 2 tire topology

Note: If  you need to use Reporting Services with SharePoint. it’s recommanded to configure SSRS SharePoint integrated mode with  Reporting service application service on an indpenedent reporting server to ensure the Performance, Manageability and Security.

But in my case, the customer budget is not enough to apply the best practice, Meanwhile configuring SSRS SharePoint integrated mode with  Reporting service application service on the same WFE + APP server lead to low performance.

So I have decided to work with SSRS Native mode to ensure at least the performance and show reports inside SharePoint via:

And in this case, I should configure SSRS Native mode on both SharePoint WFE Servers because as we know the Database Servers are not accessible over the internet,

Note: If you configured SSRS native mode on only one WFE server. you will get it worked properly. but sometimes when browsing the Report Manager URL you will get 404 error!

This error occurs when the load balancer hit the second WFE node (where SSRS is not installed), so the directory of reporting service will not be found and you will get

404 file or directory not found.

404 - file or directory not found - Scale-out SSRS

On the other side, when the load balancer hit the first WFE node (where SSRS already installed) the  Report Manager URL will work without issue.

So you should configure SSRS on the second SharePoint WFE node by scaling out SSRS deployment to share the same single Report Server Database.

Note: In this article, I considered you already have installed and configured the SSRS in the first node by following the mentioned instruction at Install and Configure SQL Server Reporting Service (SSRS).


Scale-out SSRS Deployment In SQL Server Steps

Continue reading “Scale-Out Deployment for SQL Reporting Service (SSRS)”

Install and Configure SQL Server Reporting Service (SSRS)

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Install and Configure SQL Server Reporting ServiceIn this article, I will elaborate

How to install and configure SQL Server Reporting Service (SSRS) Native Mode.

Applies To: 
  • SQL Server Reporting Services SSRS 2016.
  • SQL Server Reporting Services SSRS 2014
  • SQL Server Reporting Services SSRS 2012.
  • SQL Server Reporting Services SSRS 2008.

Continue reading “Install and Configure SQL Server Reporting Service (SSRS)”

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

In this article, I will explain

Check the connectivity between Application Server and SQL Server Instance in Database Server.


Scenario

If you are working in an implementation team, you will walk through the following:

  • Check the connectivity between the application server and database server.
  • Make sure that the SQL Server port (default is 1433) is opened and accessible through the application server.
  • Make sure that the web site application pool account can access a specific database.
  • Make sure that the SQL Server account can access a specific database.

Check the connectivity between the application server and database server.

Continue reading “How to Check SQL Server Instance Connectivity from the application server to database server for a specific user”