Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
5
votes
1answer
4k views

Problems caused by SQL Server 2016 Query Store

I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem. In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes. 2016-...
6
votes
2answers
16k views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
7
votes
1answer
175 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
3
votes
2answers
1k views

SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of ...
17
votes
5answers
57k views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
4
votes
1answer
5k views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
1
vote
0answers
1k views

SQL Server - can't import data-tier application from bacpac file (trying to move db + data from Server 2016 to 2014 SP1)

I'm trying to get a workable method to transfer a database schema and data from a SQL Server 2016 install to a SQL Server 2014 install on a different network. It looks like Import/Export Data-Tier ...
1
vote
2answers
3k views

Connect SQL Server 2016 SSDT with TFS

I've just upgraded the SQL client tools on my PC to SQL Server 2016. I no longer have a "Team" menu option in SSDT. Under Tools > Options > Source control there is a drop down box for Source ...
3
votes
2answers
1k views

SQL Server R Services - Could not find stored procedure 'master..xp_ScaleR_init_job'

I'm trying to run a data science experiment as explained in this tutorial. The tutorial has 5 lessons, and each lesson has a few sub-sections in it. For this tutorial, I am using a SQL Server 2016 ...
1
vote
1answer
210 views

SQL Server local instance user login [closed]

I am working on an Azure Mobile Apps backend service, which I am trying to connect to a local SQL Server database, as described here. I have followed all the steps, except I am using SQL Server 2016 ...
4
votes
1answer
1k views

Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services

Let's assume I have 3 machines with me. My first machine has SQL Server 2016 with R Services installed on it. My second machine has SQL Server 2016 without R services enabled on it. My third ...
4
votes
1answer
790 views

How can I handle unwanted, non-erroneous situations better?

When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error. Let's say, if I don't want John to be ...
1
vote
2answers
2k views

XML column performance in TSQL

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries ...
2
votes
1answer
949 views

SQL Server 2016 - Is the Microsoft R Client any different from a standalone Microsoft R Server?

Along with the integrated R services on SQL Server 2016, Microsoft is also offering enterprise level Microsoft R Server as a standalone installation. Some of the documentation on MSDN shows that the ...
1
vote
0answers
704 views

SQL Server 2016 - row level security by AD group membership

I'm wondering if it is possible to use row level security based on a users AD group membership. I would love to have something that could support users in different groups and some super users who ...
24
votes
1answer
991 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
1
vote
1answer
461 views

Can SQL Server 2016 R Services be used to connect to Hadoop data?

I've only been experimenting with SQL Server 2016 recently. So, please correct me if my assumptions are incorrect: From some research on SQL Server R Services, I see that RxHDFSConnect and ...
3
votes
1answer
598 views

Using SQL Server 2016 R Services over a standalone installation of Microsoft R Server

I've been experimenting with SQL Server 2016 and its R Services. I also have a standalone Microsoft R Server installed on my machine. What is a good use-case to use SQL Server R Services over a ...
2
votes
0answers
319 views

Can SQL Server 2016 R Services connect to SAS, SPSS, URLs etc?

The RevoScaleR package of SQL Server R Services (Microsoft R Server) has functions that allow you to connect to SAS, SPSS, HDFS and text files. My question is: Can R Services connect to SAS/SPSS to ...
9
votes
1answer
10k views

How do you install a TFS plugin for SQL Server 2016 Management Studio?

How do you install a TFS plugin for SQL Server 2016 Management Studio? The source control option is no longer in the option list so the existing workarounds no longer appear to work.
1
vote
1answer
344 views

How to change aggregate function without duplicating SQL (by using SQL)

In SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a parameter ...
5
votes
3answers
2k views

After upgrade to 2016, can't view SQL Server Logs

Just upgraded to SQL Server 2016 from SQL Server 2014 SP1 CU4. This is a clustered instance I am no longer able to use xp_readerrorlog and xp_enumerrorlog Msg 22004, Level 16, State 1, Line 10 ...
8
votes
2answers
15k views

After Upgrade to 2016, SQL Server Agent can't connect to Instance

On a clustered Windows 2012 R2 server with SQL Server 2014 Enterprise. Just upgraded an instance from 2014 SP1 CU4 to 2016 RTM and now getting this error when attempting to start the SQL Server Agent. ...
4
votes
1answer
24k views

Consume Rest Api with SSIS

I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source. Is there a non ...
4
votes
1answer
490 views

Do signed certificates from public certificate authorities add any value for always encrypted?

If I create Always Encrypted columns in Microsoft SQL Server from the SSMS gui, it makes a self signed certificate. Is there any value in making a CSR and paying a certificate authority (or using ...
2
votes
1answer
589 views

SQL Server 2016 Audit Options

Often law or organization policies require an application to track changes to certain table rows in a database. The changed data is often also viewable through some application page. Some common ...
3
votes
1answer
10k views

Can't install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2

I am attempting to install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2. I have removed all other versions of SQL Server, and removed VS 2015 Community and all it's ...
3
votes
1answer
21k views

Can't uninstall Microsoft SQL Server 2016 RC3

Microsoft SQL Server 2016 RC3 was installed on our server (not by me) Windows Web Server 2008 R2 64bit. Visual Studio 2015 Community was also installed. When I went to work on the server I could not ...
41
votes
2answers
20k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
9
votes
1answer
1k views

Do temporal tables log changes when there are none?

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema: create table blah ( foo int identity(1,1), baa int ) and I ...
2
votes
1answer
1k views

How can I import data from a NetCDF file into SQL Server?

I'm being asked to investigate the feasibility of importing data from a NetCDF file into SQL server. The file could be upwards of 300-400Mb and provided 4 times a day. However I'm only interested in ...
2
votes
1answer
562 views

Store info in a column as JSON

I'm learning SQL Server 2016, I learned how to extract JSON from tables. Now I need to store JSON in a column. My JSON was: {"endereco":"R. Nome da Rua, 000", "cep":"00000-000"} I`m using nvarchar ...
3
votes
1answer
266 views

Extract Multidimensional JSON

I have some tables: teste // id, descricao teste2 // id, descricao relacao // id, idTeste, idTeste2 With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to ...
2
votes
2answers
8k views

How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?

On SQL Server 2016, how do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server? I don't mind there being another layer in there, such as the MSDASQL provider, but I do want ...
2
votes
2answers
594 views

SQL Server 2016 deprecation of DML keyword ROWGUIDCOL

I just found out that the DML keyword ROWGUIDCOL is deprecated, replacement is $rowguid; and that DML IDENTITYCOL keyword is deprecated, new name is $identity. I don't understand why a non-variable (...
1
vote
1answer
524 views

AlwaysEncrypted Conversion Fails

I'm trying to encrypt a column with AlwaysEncrypted and I get the following error. Could not deploy package. Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it ...
1
vote
1answer
2k views

SQL Server 2016 Plugin's not working [closed]

Recently I installed SQL Server 2016 on my PC. Here is the detailed information of my server : Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64) Mar 10 2016 16:49:45 Copyright (c) ...
8
votes
1answer
1k views

Set statistics I/O for nested loops

Consider the below query: CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key @pagesize AS BIGINT = 25 AS SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM ...
1
vote
1answer
1k views

Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction

I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically: Is the following usage of @@ROWCOUNT in conjunction ...
2
votes
0answers
927 views

Anyone else had problems uninstalling SQL Server 2016 RC0?

We installed RC0 the day it was released and, for needs of space and project transitioning, needed to reclaim the space. As I couldn't find an uninstall option in the installation UI, I resorted to ...
4
votes
1answer
5k views

Trouble setting up mirroring on SQL Server 2016

I've setup a test SQL Server 2016 server. I've installed 2 instances, restored a backup on the primary. Then restored a backup on the secondary with NO RECOVERY, then restored the transactional log ...
2
votes
0answers
856 views

Unexpected Memory Pressure - RESOURCE_SEMAPHORE wait

Update I've done some further investigation into this issue and could still do with some help understanding what's going on. After restarting the instance, I monitored memory allocations over time, ...
4
votes
2answers
3k views

Msg 39011, SQL Server was unable to communicate with the LaunchPad service

We have just installed SQL Server Enterprise 2016 CTP 3.3 on a server that already had SQL 2012 Standard installed. We installed SQL2016 as a Named Instance and have been trying to get the R ...
8
votes
3answers
24k views

Data warehouse server. How do you calculate RAM/CPU specifications?

I am trying to write a spec for a data warehouse server for our planned data warehouse upgrade. As we run virtual servers on VMWare hosts we have the ability to add or remove resources as necessary. ...
4
votes
1answer
662 views

Can SQL Server 2016 validate JSON on insert?

Currently SQL Server 2016 CTP 3 and later releases support NVARCHAR(max) fields with JSON content. However since the type NVARCHAR(max) is totally free-form, there is no constraint on these fields. ...
3
votes
1answer
930 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` -...
3
votes
1answer
640 views

Having hard time understanding expressions in below query plan

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause which returned below query plan Rows Executes StmtText StmtId NodeId ...
4
votes
1answer
3k views

How to enable query store on the SSMS object browser with Sql Azure?

I am following this guide: Monitoring Performance By Using the Query Store I am using SSMS 2016 CTP3.2 and Sql Azure V12 I have enabled the query store on the database properties. I can use the ...
4
votes
2answers
424 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...
11
votes
1answer
60k views

SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782

While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would ...