Category : SQL Server


An Introduction to mssql-cli – Cross platform Interactive command line tool for SQL Server

In this post, we are going to discuss a new cross-platform and interactive command-line query tool that can be used to communicate with Microsoft SQL Server. Unlike sqlcmd command-line utility, mssql-cli supports cross-platform and can be used on Windows, macOS, Linux, Ubuntu, Debian, CentOS, Fedora, and etc. This is an open-source tool and it supports a very-rich and interactive command-line experience.

The mssql-cli is written in Python and it is released under open source BSD-3 license by Microsoft. It provides a very user-friendly terminal experience for T-SQL queries. It is a very small REPL utility and takes very less amount on the disk. It provides a highly interactive interface for writing T-SQL queries. Let’s discuss the features of the mssql-cli in detail.

Features of mssql-cli

Below we have listed the main features of mssql-cli:

1. Auto-completion

It offers an improved command-line experience for T-SQL by enabling support for IntelliSense … More


Delete statement

In the previous chapter, we discussed the T-SQL’s Update statement. Now, in this chapter, we are going to discuss how we can use T-SQL’s DELETE command to delete records from a table in SQL Server. We will also discuss some best practices we need to follow while using the DELETE command in MS SQL Server.

What is DELETE command

DELETE command is used to delete/remove existing records from a SQL table. Depending on the need, we can delete all or a few selected records from a table. If required, we can delete the records in a table based on the records which exist in other tables. However, in this case, we need to join all the table(s) in the FROM clause of the DELETE statement. Let’s have a look at the syntax of the DELETE statement:

Syntax of DELETE command in SQL Server

To delete all the records in … More


Interactive Data Analysis with SQL Server using Jupyter Notebooks

In this post “Interactive Data Analysis with SQL Server using Jupyter Notebooks“, we will demonstrate how we can use Jupyter Notebooks for interactive data analysis with SQL Server. Jupyter notebooks are one of the most useful tools for any Data Scientist/Data Analyst. It supports 40+ programming languages and facilitates web-based interactive programming IDE. We can put comments, headings, codes, and output in one single document. This document maintains the context to the original data source which means we can re-execute the code whenever we need it. This feature facilitates Data scientists/Data analysts to play with the code during the presentations. Also, these notebooks are very handy in sharing and can be shared easily across the teams.

What is Jupyter Lab

Jupyter Lab is the next-generation web-based tool for Jupyter notebooks. It enables tab based programming model which is highly extensible. We can arrange multiple windows … More


Update statement

In the previous chapter, we discussed the T-SQL’s Insert statement. Now, in this chapter, we are going to discuss the T-SQL’s UPDATE command. We will discuss how we can use the T-SQL’s Update command with the help of examples. We will also discuss some best practices we need to follow while using the UPDATE command in SQL Server.

What is UPDATE command

UPDATE command is used to modify existing records in a SQL table. Depending on the need, we can update all or a few selected records in a table. Also, we can update the records in a table based on the records of other tables. However, in this case, we need to join all the tables in the FROM clause of the UPDATE statement. Let’s have a look at the syntax of the UPDATE statement which updates all rows of the table:

Syntax of UPDATE command in SQL

More

Python use case – Export SQL table data to excel and CSV files – SQL Server 2017

In this post, we are going to discuss how we can export SQL Server table data to an Excel file or to a CSV file using Python’s pandas library. Prior to SQL Server 2017, we could use one of the below methods to export data from SQL Server to Excel or CSV file:

  1. Create an SSIS package to export the data from SQL Server – This option can be a good choice if we want to reuse the export process again and again. Also, if we want to put moderate/complex transformations during data export, this option can be a better choice.
  2. Use SQL Server Import/export wizard – SQL Server provides in-built data export/import wizard which can be used in case we want to export data with no/minimal transformations.
  3. OS-based copy-paste functionality – We can simply copy the query output from SQL Server using Ctrl + C option and then open
More

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 1

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