Strange errors with SQLBindR

I recently encountered a strange problem while trying to bind a SQL Server 2016 instance to Microsoft R Server 9.1. On running the below command:

sqlbindR /list

I got the error:

An unexpected error has occurred: Value cannot be null.
Parameter name: input

I had a hunch that this was related to an ‘orphaned’ pre-release SQL Server 2017 instance which I had previously installed and manually deleted. I found some registry keys related to that (non-existent) instance and deleted those. But SQLBindR was still erroring out:

sqlbindR /list
An unexpected error has occurred: Version string portion was too short or too long

I finally figured out that there was still an entry for the “non-existent” SQL instance at the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL. There was a value (on the right hand pane when viewed in RegEdit) for that old instance. When I deleted that value for the non-existent SQL instance, I was able to run SQLBindR  successfully!

Please note this troubleshooting tip is intended for non-production systems and for advanced users. If you have questions or concerns, or this error is happening on a production system, I strongly recommend you contact Microsoft Support.

Disclaimers Please note that this troubleshooting tip is provided as-is and neither me nor Microsoft offers any guarantees or warranties regarding the same. Using tools like RegEdit incorrectly can cause serious problems that may require you to reinstall your operating system. Use RegEdit at your own risk. This Information is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS INFORMATION IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

Sneak Preview: SQL Graph session at PASS Summit 2017

At PASS Summit 2017, Shreya Verma and I will be co-presenting a session on using the newly added Graph data (nodes / edges) processing capabilities in your applications. One of the patterns we will discuss in that session is how to leverage in-database Python scripts to detect “similar” nodes and thereby ‘infer’ edges in the graph.

Update November 4th, 2017: You can now download this graph as a PDF file: PASS 2017 Sessions. Please view with Adobe Acrobat Reader at zoom level 1600%; web browsers like Edge and Chrome do not allow you such high zoom levels.)

We have a very interesting graph processing demo which uses PASS 2017 schedule data. In this demo, we will start with each session as a node in our graph, and then use in-database Python to detect similar sessions (based on the session title, abstract and the speaker names.) We then return that data to SQL to build weighted edges in the graph, and then use an external visualization tool to draw the graph. As a sneak preview, here is a section of the graph built with this method; edges connect similar sessions, and the thicker the edge, the more similar those sessions are:

PASS Summit 2017 sessions as a graph

To know more on this theme of how Python and Graph integrate, and how we generate the above visual, come see us in our session on Nov 3 @ 3:30PM. Yes, we know that’s the last session slot of PASS Summit 2017 – you always save the best for the last 🙂

Create your own database with all PASS Summit 2017 Sessions using SQL Server 2017 ML Services (Python)

Next week is the biggest event for the SQL Server community: PASS Summit 2017! I will be presenting the Real-world SQL Server R Services session with two of our customers – APT and Financial Fabric. I will also be part of the SQL Clinic, so I’m really excited and pumped about next week!

Being a Data Nut, it suddenly occurred to me today: what if we can import all the PASS Summit 2017 session titles, abstracts and other details into a SQL Server table? And once this thought was in my mind, there was no stopping me! The wonderful thing is that with Python in SQL Server 2017 you can leverage rich libraries such as Beautiful Soup 4, URLLib3 to parse HTML and then present it as a structured table (using Pandas) which SQL can then consume.

The code below will do exactly that for you. It leverages the above mentioned Python libraries, so prior to executing the script you must install two libraries (bs4 & urllib3) using PIP. (Note that pip.exe is present under the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\scripts folder; change drive letters as appropriate for your installation.) Also note that PIP must be executed from within an administrative CMD prompt in Windows.

The other step for the below script to run correctly is that outgoing Internet access must be allowed for the SQL instance’s R / Python scripts. By default, we block such access. To edit the rule temporarily (we strongly recommend to not disable this rule on a permanent basis) use wf.msc to open the Firewall with Advanced Security console, and then locate the rule “Block network access for R local user accounts in SQL Server instance ” in the Outbound Rules section. Right click and Disable the rule for now; and DO NOT FORGET to enable it later on!

Alright, here is the code!

CREATE DATABASE PASS2017
GO

USE PASS2017
GO

DROP TABLE IF EXISTS PASS2017Sessions

CREATE TABLE PASS2017Sessions
(Abstract nvarchar(max)
		, SessionLevel int
		, Speaker1 nvarchar(100)
		, Speaker2 nvarchar(100)
		, Speaker3 nvarchar(100)
		, Title nvarchar(4000)
		, Track nvarchar(50)
		, SessionId int
		);
GO

INSERT PASS2017Sessions
exec sp_execute_external_script @language = N'Python',
@script = N'
from bs4 import BeautifulSoup
import urllib3
import re
import pandas as pd

http = urllib3.PoolManager()
schedpage = http.request("GET", "http://www.pass.org/summit/2017/Sessions/Schedule.aspx")
schedpage.status
soup_schedpage = BeautifulSoup(schedpage.data, "lxml")
schedpage.close()

documents = []
processedsessions = []

sessioncells = soup_schedpage.find_all("div", class_ = "session-schedule-cell", recursive=True)
for currsess in sessioncells:
    hrefs = currsess.find_all("a")
    if (len(hrefs) >= 1):
        rowdict = {}

        # session title

        rowdict["Title"] = hrefs[0].text

        # session level
        sesslevel = currsess.find("p", id = re.compile("plcLevel"))
        if (sesslevel != None):
            rowdict["Level"] = sesslevel.text.replace("Level: ", "")
        else:
            rowdict["Level"] = None

        # session track
        allps = currsess.find_all("p")
        rowdict["Track"] = allps[len(allps) -2].text

        # get into session page itself
        if ("href" in hrefs[0].attrs):
            sessurl = hrefs[0].attrs["href"]

            # session ID
            mtch = re.search(r"sid=(d+)", sessurl)
            if (mtch is None):
                continue

            # check if this session ID was already processed
            sessionid = mtch.group(1)

            if (sessionid in processedsessions):
                continue

            processedsessions.append(sessionid)

            rowdict["sessionid"] = sessionid

            sesspage = http.request("GET", sessurl)
            soup_sesspage = BeautifulSoup(sesspage.data, "lxml")
            sesspage.close()

            # session abstract
            sessabstract = soup_sesspage.find("pre", class_ ="abstract")
            rowdict["Abstract"] = sessabstract.text
            if (len(rowdict["Abstract"]) == 0):
                continue

            # speakers
            allspeakers = soup_sesspage.find_all("a", id=re.compile("Detail.+lnkSpeaker"))

            rowdict["Speaker1"] = None
            rowdict["Speaker2"] = None
            rowdict["Speaker3"] = None

            if (len(allspeakers) >= 1):
                rowdict["Speaker1"] = allspeakers[0].text

            if (len(allspeakers) >= 2):
                rowdict["Speaker2"] = allspeakers[1].text

            if (len(allspeakers) == 3):
                rowdict["Speaker3"] = allspeakers[2].text
        else:
            continue

        documents.append(rowdict)

OutputDataSet = pd.DataFrame(documents)'
GO

select *
from PASS2017Sessions
GO

If all goes well you should see the results:

httpsmsdnshared.blob.core.windows.netmedia201710PASS2017Sessions

Isn’t that so cool! Play around with it and let me know what you think. And if you are at the Summit and interested in SQL Server ML Services, a friendly reminder to come to the Real-world SQL Server R Services session. See you later!

Disclaimer

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code. This posting is provided “AS IS” with no warranties, and confers no rights.

How to fix the ‘Class not registered (pfutil)’ error in SQL Profiler

Yesterday, on a whim I launched SQL Profiler on my laptop. I had not launched the Profiler client for a long time, especially given its deprecated status. All the more, with the recent introduction of XE Profiler within SSMS 17.3, there is even lesser motivation to use the old standalone Profiler. But still… I launched Profiler yesterday and when I tried to connect to my SQL Server 2017 instance, this is what I got:

Class not registered (pfutil)When you look at the detailed exception information provided this is what we get:

Class not registered
 (pfutil)

------------------------------
 Program Location:

at CProfilerSqlDbConnection.Open()
 at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

While Profiler is deprecated, it should certainly not throw errors like this. So I decided to take a closer look. With some debugging I figured that somehow Profiler is not able to create a OLEDB connection using the SQLNCLI11 provider. This triggered some ‘connections’ in my mind, because I remembered that I had recently had to uninstall and reinstall the same for usage with the RML Utilities for SQL Server. I figured that somehow those steps might have caused some COM registration issues with SQLNCLI11.DLL. So I executed the following command from an administrative command prompt:

regsvr32 C:\WINDOWS\system32\sqlncli11.dll

Voila! That fixed the issue. So in the rare event that you are still using Profiler and happen to run into this even more rare error, I hope the simple step to register SQLNCLI11 will help you recover quickly!