Tag / SQL Server 2016

by Arvind Shyamsundar

Unable to launch runtime for ‘R’ script: Check this first

This post is a quickie, hoping that the tip will come in handy to others who are facing the same issue.

Note: the steps below were written for SQL Server 2016 pre-release versions. They are NOT valid for RTM and subsequent releases.

Today I was trying to enable the R Services (a.k.a. Advanced Analytics) feature in SQL Server 2016, which brings the world of R into SQL Server. I thought that I had diligently followed instructions on installing and configuring the integration. Next, I tried to execute the basic script below (sort of like a ‘loopback’ test for this feature to check if the configuration is okay:

sp_execute_external_script @language =N’R’,@script =N’OutputDataSet <- InputDataSet’, @input_data_1 =N’SELECT 1 as Col’ WITH RESULT SETS ((col int not null));

Unfortunately it kept returning the error messages below:

Msg 39021, Level 16, State 1, Line 1
Unable to launch runtime for ‘R’ script. Please check the configuration of the ‘R’ runtime.
Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
Msg 11536, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

I went back to the instructions and realized I had forgotten one step which is to download an updated version of the post-installation configuration script from the Microsoft Download Center. Clicking on the previous link actually gives us an updated registerRext.exe. Running the registerRext.exe /uninstall followed by registerRext.exe /install with this updated version fixed my problem!

Do note that the errors mentioned above could have been caused by other issues, but at a minimum please ensure that you are using the updated registerRext.exe.

by Arvind Shyamsundar

What’s new in the Server 2016 CTP 3.3 ScriptDom Parser

We just made SQL Server 2016 CTP 3.3 publicly available a few days ago, and it has some new features which are also reflected in the managed T-SQL parser (Microsoft.SqlServer.TransactSql.ScriptDom). As readers of this blog may know, I am a big fan of this parser and to see it keeping pace with the latest preview release of the SQL Server engine feels great!

Here are the main changes you can leverage if you are using the SQL Server 2016 CTP 3.3 ScriptDom parser:

  • The ScriptDom parser is up to date with the new support for delayed compression of delta rowgroups in Columnstore indexes: the new COMPRESSION_DELAY option in ALTER INDEX helps if you are using non-clustered indexes in a real-time analytics scenario. See this article for more details.
  • The RSA_3072, RSA_4096 algorithms are now added as algorithms inside the ScriptDom parser. These are typically used in the CREATE ASYMMETRIC KEY syntax.

I also wanted to inform anyone who is planning to upgrade their previous installation of SQL Server 2016 preview (CTP 3.2 typically) to the release notes, which contain a very important step (if you are using FILESTREAM) you must take BEFORE upgrading to CTP 3.3. So please take a minute and check the release notes prior to upgrading!

Hope this helps!

by Arvind Shyamsundar

Inline Index Definition in SQL Server 2016 CTP2

Firstly, I am sure you are as excited as I am about the next version of SQL Server, and you can try the public preview (a.k.a. Community Technology Preview 2 – CTP2) by visiting the evaluation page and install it NOW! Today morning, at the start of a  new week and I found something new in SQL 2016 CTP2 which has not been widely discussed as yet: inline index definition enhancements.

As a regulatory disclaimer though I must mention that the information here may be subject to potential change in future releases beyond CTP2.

 

Current State

SQL Server 2014 introduced T-SQL grammar support allowing us to declare index definitions inline with the CREATE TABLE itself. While this was mainly driven by the semantics of the in-memory OLTP tables, the elegance of defining indexes along with the table is compelling. However there were some drawbacks:

  • Inline definition of columnstore indexes was not allowed.
  • Inline indexes could not have a filter predicate associated.

I’m glad to see that both of these have scenarios have been addressed in SQL Server 2016 CTP2. Here are some examples of the new syntax which work in SQL Server 2016 CTP2:

Inline Filtered Index

create table t1
(
    c1 int,
    index IX1 (c1) where c1 > 0
)
go

Inline Columnstore Index

— inline clustered columnstore index definition
create table t2
(
    c1 int,
    c2 int,
    c3 nvarchar(500),
    index CCSI1 clustered columnstore
)

— inline nonclustered columnstore index definition
create table t3
(
    c1 int,
    c2 int,
    c3 nvarchar(500),
    index NCCSI1 nonclustered columnstore (c1, c2)
)

The CREATE TABLE help in SQL 2016 CTP2 Books Online has been updated to reflect these enhancements.

I’ll be posting other such nuggets on an ongoing basis, so stay tuned!