Sunday, May 21, 2017

Is it JSON or not?

The following is formatted in JSON

{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}etc..
{  "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

This is a valid JSON according to RFC 4627.


However when you use this in SQL Server to look at the JSON using the OpenJSON, for example, you will get this error:


The reason for this error:
Msg 103, Level 15, State 4, Line 2
The identifier that starts with "wclass"...
lies in the fact that SQL Server string starts with a single quote and therefore you need to provide this declaration:

declare @json nvarchar(Max)
set @json=
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

When you do this the error goes away as shown:


The character count in @json is also important as you see in this SQL query:


The answer is that RFC 4627 validation requires a string to start with a double quote("), but the SQL Server's JSON validation requires the JSON to begin with a single quote(') as we saw in this post.

How do you query the OpenJSON function with a SELECT statement?

OpenJSON converts an array of objects in a variable in JSON Format to a rowset
that can be queried with standard SQL Select statement.

Here is an example:

We are going to look at a JSON list of my first batch of students who took my course shown here. 

{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

This is the result of running OpenJSON using the above:

Now you can run a SELECT query with a with clause on the rows returned by OpenJSON as shown here:

The first member "wclass" has nulls for the selected columns. It exists because it actually was in the original XML that got converted to JSON.
Here are my more recent JSON related articles:

JSON validation in SQL Server:

Nested JSON using SQL Server 2012:

Retrieve JSON formatted data from SQL Anywhere 17

Friday, May 19, 2017

What are geometry primitives in GeoJSON?

In Eucledian geometry we were taught about the concepts of 'Point' and straight line being the shortest distance between two points.

GeoJSON is an Open Standard format based on JavaScript Object Notation for representing simple geographical (geometrical?)features.

In GeoJson Point, Line and Polygon are the Geometric Primitives:

{ "type": "Point",
    "coordinates": [30, 10]
{ "type": "LineString",
    "coordinates": [
        [30, 10], [10, 30], [40, 40]
{ "type": "Polygon",
    "coordinates": [
        [[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]]
This post is based on GeoJSON on Wikipedia.

Tuesday, May 16, 2017

What is Azure Cosmos DB?

This is how Azure Cosmos DB is described by Microsoft, "Azure Cosmos DB is Microsoft's globally distributed, multi-model database. With the click of a button, Azure Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure's geographic regions. It offers throughput, latency, availability, and consistency guarantees with comprehensive service level agreements (SLAs), something no other database service can offer"

If you are looking for Global Distribution, horizontal scaling, guaranteed latency, high availability, comprehensive SLA's and unrivalled Data Model and API that no RDBMS, no no-sql database can provide there is nothing out there to match Azure Cosmos DB.

Azure Cosmos DB Engine supports multiple database systems and it is based on the following:

 Atom: Consisting of a small set of primitive types (String, Bol, number)
 Records: Structs
 Sequence: Arrays of atoms, records and sequences

Azure Cosmos DB engine currently supports the following:

Document DB SQL
Mongo DB
Azure Table Storage
Gremlin Graphh Query API
It will support other API's in the future.

For developers looking for a globally distributed database system Azure Cosmos DB is the answer.

Sunday, May 14, 2017

How do you build an ODBC Connection String with Report Builder 3?

ODBC source is one of the many sources that you can connect to in Reporting
Services using the Report Builder.

Here are the steps. It is assumed you have SQL Server 2016 SP1 installed; Report Builder is also
installed on the same machine. For these steps it is assumed that the SQL Server is up and running
and you are the owner of the computer who installed the SQL Server.

1. Launch Report Builder with Administrative Privileges.
2. Right click Data Sources and Click Add DataSource... to bring up the Data Source properties window.
3.  Provide a name for the data source, DS_ODBC
4. Choose Use a connection embedded in my report
5. Select connection type: click handle and choose ODBC at the bottom of list
6. Click Build... to bring up the Connection properties window
7. In the Data source specification section choose Use connection string:
8. Click the Build... button that gets activated to display Select Data Source window
9. Click Machine Data Source
10. In Machine Data Source tabbed page, click New... to display Create New Data Source
11. Accept the default, User Data Source and click Next
12. In the Select a driver for which you want to set up a data source, scroll down and
    select SQL Server (SQLSRV32.dll); click Next to display the next window
13  Click Finish. Create a New Data Source to SQL Server is displayed
14. Provide a name of your choice (ODBC_DSN); Description:(ODBC for Report Builder); Server
    use handle to locate: Hodentek9\OHANA. Click Next
15. Accept the default in the displayed page: with Windows NT authentication using the
    Network login id. Click Next
16. Change the default database by clicking handle to Northwind. accept all other
    defaults. Click Next
17. Accept all defaults on the displayed page. Click Finish
18. In the ODBC Microsoft SQL Server Setup page test the connection and make sure it works. Click OK
19. ODBC_DSN enters the Machine Data Source tabbed page. Click OK
20. SQL Server Login window gets displayed. Enter creator owner's credentials
21. Copy the connection string from the connection properties window.
Dsn=ODBC_DSN;description=ODBC for Report Builder;trusted_connection=Yes;app=Microsoft SQL Server;wsid=HODENTEK9;network=DBNMPNTW
22. Test Connection to make sure it works
23. Click OK
The Connection string enters the Data Source Properties window as shown.

Thursday, May 11, 2017

How do you create a Report Model in SQL Server Reporting Services 2016 (Native Mode)?

You cannot as it is discontinued.

RS Web Portal is the new name for Report Manager of earlier versions.

The following two features related to Report Model are discontinued  in SQL Server 2016.

Feature                                                                Replacement or workaround
Upload report models through the web portal     This can still be done through the SOAP API.
Manage report models through the web portal    This can still be done through the SOAP API.

Note: Whereas HTML4 is supported, the future SQL Server Reporting Services will be using HTML5.

Wednesday, May 10, 2017

How do you fix the rsErrorOpeningConnection error in Reporting Services 2016?

The error, rsErrorOpeningConnection has been asked many times in almost
all versions of SQL Server Reporting Services starting from SSRS 2008. Various
reasons have been given. The error message hints at what may be wrong.

In the present case, a simple report with embedded data source  was created in Report Builder 3 by the creator owner who is also the local administrator. This report was saved to the
Report Server (also gets uploaded to the Web Portal front-end).

The report is processed in the Report Builder without any error.

If the Where_2 report is double clicked this is the message displayed after correct authentication
by the local administrator.

The Data Source is shown in the following and the report gets correctly displayed in
the Report Builder and it can be saved to the Report Server as seen above.

The rsErrorOpeningConnection arises because of the credentials to the Data Source is not
properly configured (what is shown is the default). Review the credentials for the data source:

When you make the proper entries for the report as shown here:

The error goes away.

 The SQL Server 2016 SP1, the Report Builder 3 are both on a Dell Laptop with Windows 10 Pro. The Reporting Services configuration is for Native Mode managed by the laptop owner.