Category : SQL Server


SQL Server – Error 1061: The service cannot accept control messages at this time

Sometimes when we try to restart “SQL Server service” we might get an error “Windows could not stop the SQL Server (MSSQLSERVER) service on Local Computer” with error code and description “Error 1061: The service cannot accept control messages at this time“. In this post, “SQL Server – Error 1061: The service cannot accept control messages at this time”, we will discuss the workaround which can help us to fix this issue.

This error occurs when we try to restart the SQL Server service using SSMS object explorer or/and using the services console. Let’s try to restart the SQL Server service using object explorer and services console.

Restarting SQL Server services using Object explorer:

Restarting SQL Server using services console:

Below is the error screenshot:

Fix – Error 1061: The service cannot accept control messages at this time

To fix this issue, we can … More


Read and write data to SQL Server from Spark using pyspark

Apache Spark is a very powerful general-purpose distributed computing framework. It provides a different kind of data abstractions like RDDs, DataFrames, and DataSets on top of the distributed collection of the data. Spark is highly scalable Big data processing engine which can run on a single cluster to thousands of clusters. To follow this exercise, we can install Spark on our local machine and can use Jupyter notebooks to write code in an interactive mode. In this post “Read and write data to SQL Server from Spark using pyspark“, we are going to demonstrate how we can use Apache Spark to read and write data to a SQL Server table.

Read SQL Server table to DataFrame using Spark SQL JDBC connector – pyspark

Spark SQL APIs can read data from any relational data source which supports JDBC driver. We can read the data of a SQL Server table … More


The RPC server is unavailable – SQL Server 2017 installation error 1

During the installation of SQL Server 2017(Or other versions), we can get an error “The RPC server is unavailable” at the very last step of the installation process while executing the action “DReplayControllerConfigAction_install_postmsi_Cpu64“. “The RPC server unavailable error” might also occur at the “Server Configuration” step during the installation process. However, typically this error occurs when we try to install SQL Server on a Remote/Virtual Machine.

Below is the screenshot of the RPC error you may get during the installation of SQL Server 2017 on a remote machine:

The above error message stops the installation process at the final step and when we click on “Retry” button, it keeps prompting the same error message again and again. If you do not get any appropriate solution, this solution might help you to resolve this RPC error which has occurred due to the domain name issue on the … More


Python use case – Resampling time series data (Upsampling and downsampling) – SQL Server 2017

Resampling time series data in SQL Server using Python’s pandas library

In this post, we are going to learn how we can use the power of Python in SQL Server 2017 to resample time series data using Python’s pandas library. Sometimes, we get the sample data (observations) at a different frequency (higher or lower) than the required frequency level. In such kind of scenarios, we need to modify the frequency of the given samples as per the frequency of the required outcome. Modifying the frequency of time series data using T-SQL query becomes a tedious task especially when we need to perform upsampling as we need to generate more rows than what we have in the sample dataset. The Python’s pandas module has in-built capabilities for frequency conversion. With the help of pandas resample method, we can increase or decrease the time series observation frequencies with only few lines of … More


Conditional group by in SQL Server

In this post, we are going to learn how we can apply a conditional GROUP BY clause on a column, based on the values of another column. Assume that we have a table named tbl_EmpSaleDetail which contains the sales records for each employee. Let’s have a look at the table data.

In the above table, we have these columns:

EmpName – Stores the name of the employee

SaleDate – Date of sale

SaleAmount – Amount of the sale

IsActive – Indicates whether the employee is active or not.

Now, we need this output.

In this output, we can see that all the data of inactive employees have been aggregated to a single row labeled as “–Inactive Employees Sales–” (Highlighted in red). However, the sum of the sales of the active employees are aggregated individually. Before writing the conditional group by query, lets create the sample table … More


Python use case – Import data from excel to sql server table – SQL Server 2017

If we need to import data from an excel file into SQL Server, we can use these methods:

  1. SQL Server Import Export Wizard
  2. Create an SSIS package to read excel file and load data into a SQL Server table
  3. Use T-SQL OPENROWSET query
  4. Use the read_excel method of Python’s pandas library (Only available in SQL Server 2017 onwards)

In this post “Python use case – Import data from excel to sql server table – SQL Server 2017”, we are going to learn that how we can use the power of Python in SQL Server 2017 to read a given excel file in a SQL table directly. With the integration of Python in SQL Server 2017, we can use the pandas read_excel method to read a given excel file with lots of customizations in SQL Server.

Assume that we have an excel file named EmployeeList.xlsx as this. Click here to More


Python use case – Import zipped file without unzipping it in SSIS and SQL Server – SQL Server 2017

Import zipped CSV file without unzipping it in SSIS using SQL Server 2017

SQL Server Integration Services (SSIS) is one of the most popular ETL tools. It has many built-in components which can be used in order to automate the enterprise ETL(Extract, Transform, and Load). Also, if we need a customized component which is not available in SSIS, we can simply create it by writing our own piece of code in C# using Script Task or Script Component.

In this post, we are going to explore that how we can read and load a zipped CSV file in SQL Server without unzipping it using SSIS along with SQL Server 2017. Reading a zipped file directly (without unzipping it) will save some time required in order to write the text file on the physical disk and then reading it from there. As of now, we don’t have any built-in component in … More


Find and Delete all duplicate rows but keep one

In this post “Find and Delete all duplicate rows but keep one”, we are going to discuss that how we can find and delete all the duplicate rows of a table except one row. Assume that we have a table named tbl_sample which has four columns – EmpId, EmpName, Age, and City. This table has some duplicate data (in all the four columns) which needs to be deleted except the original one row. To demonstrate this, let’s create the dummy table with some sample data.

Here is the code to create the dummy table with sample data:

Let’s have a look at the table data now.

In above table, we can see that we have multiple duplicate rows for EmpId 1, 2, 3 and 4. To find the duplicate records from the above table, we can use this query. This query will output all the duplicate rows from the … More


Import CSV file into SQL Server using T-SQL query 4

Sometimes, we need to read an external CSV file using T-SQL query in SQL Server. Due to some functional limitations, we cannot use the import-export wizard functionality in such kinds of scenarios as we need the result set in the middle of the execution of the other queries. There, we can use the BULK INSERT SQL command which helps us to import a data file into SQL Server table directly.

Let’s have a look at the sample CSV file which we want to import into a SQL table. The CSV file is this.

To download the sample CSV file, click here. The above CSV file uses comma as a column delimiter and contains 6 columns which are:

PersonID – Stores the Id of the person.

FullName – Stores the full name of the person.

PreferredName – Stores the preferred name of the person.

SearchName – Stores the search name … More


Python use case – Convert rows into comma separated values in a column – SQL Server 2017

In this post, we are going to learn how we can leverage python in SQL server to generate comma separated values.

If we want to combine all values of a single column it is fairly easy as we can use COALESCE function to do that. Here is a reference to the already existing post. But have you ever thought what would happen if we needed a comma separated value in a column along with other columns? In that scenario, this approach would not work.

We can get comma separated values in a column along with other columns using FOR XML PATH query wrapped inside a sub-query, but there also we would need to take care of HTML encoded characters like < and >.

Now, with python’s integration with SQL Server 2017, it can be achieved very easily and efficiently as we do not have to rely on subqueries and … More