SQLCLR : Create Assembly fails

While deploying SQLCLR Assembly to SQL Server 2016  or later, CREATE ASSEMBLY fails with the following error Creating [Assembly name]… Msg 10343, Level 14, State 1, Line 65 CREATE or ALTER ASSEMBLY for assembly ‘Assembly name’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1. […]

JSON support in SQL Server (read – write statistics) – Part 5

Loading Entire content of JSON file using  OPENROWSET (BULK) table-valued function, SQL Server will read the entire content of the file and insert it into SQL Server (Total Execution time = Time spent on Reading the full content of file + Writing the document into the table) In below example, Importing JSON file of size […]

JSON support in SQL Server (BULK INSERT INTO SQL SERVER) – Part 4

There are multiple ways to store JSON document into SQL Server. Such as writing the C# application to read JSON document and insert it into SQL Server, using SSIS script component with .net libraries, using PowerShell or T-SQL scripts. In this topic, I will try to cover all. Let’s start with T-SQL I have a JSON  […]

JSON support in SQL Server (path parsing) – Part 3

SQL Server support 2 modes of JSON parsing lax mode strict mode A JSON path that specifies the property to extract. If the format of the path isn’t valid, JSON_VALUE returns an error. JSON_VALUE returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression. If […]

JSON support in SQL Server – Part2

In this article, we will see how to read Object, Properties and Array values of a JSON document. Here is a simple JSON Document { “name”: “John”, “skills”: “SQL” } The above example has a simple object with two properties(name and skills). These two properties are separated by a comma. Now read the above JSON from […]

Powershell and JSON – Part 2

We have a couple of standard JSON document which requires a property value to be updated based on the system they are moving to. So we started looking for an option which tool or scripting language can the best. So, I looked to use Powershell. Here is an example of How a JSON document value […]

Powershell and JSON – Part 1

Get the List of JSON PowerShell commandlet from Get-Command Get-Command *JSON* Reading JSON Document $file=”G:\Dummy_Data\world_bank\test1.json” (Get-Content -Path $file -Raw)| ConvertFrom-Json Pass JSON as InputObject to ConvertFrom-JSON commandlet $jdata=Get-Content -Path $file -Raw $data=ConvertFrom-Json -InputObject $jdata $data Now Get the members, the properties, and methods, of objects. $jdata=Get-Content -Path $file -Raw $data=ConvertFrom-Json -InputObject $jdata $data |Get-Member Retrieve Property Value $jdata=Get-Content -Path […]

JSON support in SQL Server – Part 1

JSON stands for JavaScript Object Notation. JSON is very lightweight popular textual data format. JSON is also used to store unstructured data in log files or NoSQL database. JSON function in SQL Server enables you to combine NoSQL and relational database concept By using SQL Server built-in functions and operators, you can do the following […]

Installing mssql-cli

mssql-cli is an interactive command-line tool for querying SQL Server, install it on Windows, macOS, or Linux. To download mssqli-cli package , download and install python on the OS. The latest Python installation package can be downloaded from here Once Python is installed successfully, open command prompt with elevated permissions and run the below command pip […]

Allow Remote Connection on RHEL firewall

To allow remote connections, open the SQL Server port on the firewall on RHEL. The default SQL Server port is TCP 1433. If you are using FirewallD for your firewall, you can use the following commands: sudo firewall-cmd –zone=public –add-port=1433/tcp –permanent sudo firewall-cmd –reload  

Run SQL Server 2017 container image with Docker on REHL 7.5

To install docker on REHL 7.5 enterprise follow the below steps sudo yum install -y yum-utils sudo yum-config-manager –add-repo https://download.docker.com/linux/centos/docker-ce.repo sudo yum makecache fast To avoid error “Requires: container-selinux >= 2.9” Run following command sudo yum install -y http://mirror.centos.org/centos/7/extras/x86_64/Packages/container-selinux-2.42-1.gitad8f0f7.el7.noarch.rpm To avoid error “libtool-ltdl-2.4.2-22.el7_3.x8 FAILED” Run following command yum install http://mirror.centos.org/centos/7/os/x86_64/Packages/libtool-ltdl-2.4.2-22.el7_3.x86_64.rpm To avoid Error: Package: docker-ce-18.03.1.ce-1.el7.centos.x86_64 (docker-ce-stable) Requires: pigz […]