Questions tagged [sql-server-2016-sp1]

0

votes
0

answer
3

Views

Insert Excel Data into 2016 SQL database using JavaScript, Vue.js and a .net web api

I have a Vue.js Web page that has a drop zone for the end user to drop a Excel Template that could have over 1000 rows. When the End User drops a file a Vue.js DataTable is created to display the data on the screen. At that point they can edit or change data that is in the selected row. Once they...
user1314159
1

votes
1

answer
55

Views

Optimize JSON query processing in SQL Server 2016

I have a JSON column in a table that I'm parsing out to create a view. I have 120 columns in the JSON string and it produces about 1750 rows of data. Problem is, the query takes 17 seconds to run and it's a basic query like the one below (with 120 columns being selected): SELECT JSON_VALUE(json, '$....
jdids
1

votes
2

answer
58

Views

Extracting JSON values from a SQL Server NVARCHAR column

I'm trying to extract the values, but the always return NULL. DECLARE @TestTable TABLE ( JSON_Column NVARCHAR(MAX) ) INSERT INTO @TestTable VALUES ( (SELECT 111111 AS idTest1, 222222 AS idTest2 FOR JSON PATH) ); SELECT JSON_Column, JSON_VALUE(JSON_Column,'$.idTest1') AS idTest1, JSON_VALUE(JSON_Col...
Mark
12

votes
1

answer
130

Views

Do Clustered Columnstore Indexes Affect SSAS Query End User Performance

Do Clustered Columnstore Indexes affect SSAS Performance querying for the end-user, and how could to fix this? Read an article below here how Sort order affects SSAS query customer user performance. Is there any way to resolve this issue? Would rebuilding SSAS indexes/aggregations work? Already kn...
Joe Smith
1

votes
0

answer
46

Views

Searching using SQL Server Full Text Index support 'or not' operation

Is there a way to support Boolean expression like (x and y) or (not z) in full text index search? I am using SQL Server 2016. I have read this article: CONTAINS (Transact-SQL) I see that 'or not' is not supported. Any help?
Ali Alnader
1

votes
1

answer
26

Views

Calculate total when lower level table is amended in SQL Server

Is there any clever trick for calculating totals when down stream tables are updated? What I mean is... imagine you have 3 tables... 1) Orders - keyed on order id 2) OrderLines - keyed on order id and lineno, with part code and quantity 3) Parts - keyed on Partid, with cost and a STATUS flag Now I...
Jaffa Brown
1

votes
1

answer
1.8k

Views

SQL Server connection timeout error in nodejs mssql

Always getting following error after couple of SQL statement execution. I am using var sql = require('mssql'); (node:12240) [DEP0064] DeprecationWarning: tls.createSecurePair() is deprecated. Please use tls.Socket instead. warning.js:18 (node:12240) UnhandledPromiseRejectionWarning: Unhandled promi...
Furqan Misarwala
1

votes
0

answer
351

Views

Why a System-Versioned temporal table cannot be merged?

I have this error which I don't understand quite well. I have the following System-Versioned temporal table: CREATE TABLE [dbo].[Dim_Table] ( columnA int IDENTITY, -- columnB nvarchar(10) NOT NULL, columnC nvarchar(10) NULL, SysStartTime datetime2 G...
SNR
1

votes
1

answer
92

Views

Query equivalence with DISTINCT

Let us have a simple table order(id: int, category: int, order_date: int) created using the following script IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL DROP TABLE dbo.orders SELECT TOP 1000000 NEWID() id, ABS(CHECKSUM(NEWID())) % 100 category, ABS(CHECKSUM(NEWID())) % 10000 order_date INTO order...
Radim Bača
1

votes
1

answer
151

Views

Full-text search stemming not returning consistent results in different languages

I have an Sql Server 2016 database with full text indexes defined on 4 columns, each configured for a different language: Dutch, English, German & French. I used the wizard to setup the full-text index. I am using CONTAINSTABLE with FORMSOF and for each language I would expect executing a query with...
redrobot
1

votes
0

answer
185

Views

Embedded images sent via sp_send_dbmail are not working in gmail or Outlook 2013 for OSX

I'm having a problem where emails sent through Sql Server are not properly being embedded in some mail clients - notably gmail and Outlook 2013 on OSX. (Outlook 2013 on PC is fine) Here's a sample call that will demonstrate the problem in my environment: declare @body varchar(4000) set @body = ' Emb...
Joe Zack
1

votes
0

answer
87

Views

Error OData Source SQL Server 2016

I'm using SQL Server 2016 with SharePoint 2013. I'm trying to access to SharePoint List. I use the OData Source in my Package. The connection works succefully and I can preview (with the Preview button) data from the list. But, when I try to execute it got an error : Error: La connexion « Connexi...
Sara
1

votes
1

answer
65

Views

entity framework (MSSQL 2016) + xUnit test - tries to update “GENERATE ALWAYS” column SysStartTime

I am having a strange problem: I have a quite simple Login(string user, string pass) method for user login which needs to save last login date of that user: using (MyDbContext dbContext = new MyDbContext()) { var dbUser = dbContext.IdentityUserLogins.SingleOrDefault(u => u.LoginProvider == login &&...
XzajoX
1

votes
0

answer
46

Views

SQL Server Temp Table Creation For Stored Procedure that Returns Multiple Tables

I've been struggling with this all afternoon, so I'm hoping someone here can help me. I've seen several threads similar to this, but none of provided me with an answer to my exact situation. I'm using SQL Server 2016 and I have a stored procedure (I didn't write it and can't modify it) that returns...
sccrbrg
1

votes
0

answer
179

Views

Entity Framework C# - DbGeometry POLYGON doesn't work

i have a problem with WKT and DbGeometry working together. When i use POINT as geometry like this example it works good. DbGeometry myGeometry1 = DbGeometry.FromText('POINT (30 10)'); DbGeometry myGeometry2 = DbGeometry.PointFromText('POINT (30 10)', 0); if i use POLYGON or any other geometry instea...
Digitarca Lab3
1

votes
0

answer
69

Views

Azure Virtual Machine SQL Server 2016 R service Error :Unable to launch runtime for 'R' script

I am using R services in SQL Server 2016 SP1 with machine learning Server 9.2.1 in an Azure Virtual Machine (I have installed both SQL Server and Machine learning server in Virtual Machine). Everything was going fine. After changing few network configuration of Virtual Machine it shows error in R s...
mzhasan
1

votes
0

answer
378

Views

SQL Server - parse parent-child structured JSON with unknown depth in SQL

I'm going to ask my last question which has left unanswered, how can I list all the objects in following JSON string having parent-child structure? declare @json nvarchar(max) = ' { 'topparent': [{ 'id': '1' , 'parent': '0' , 'child': [{}] }, { 'id': '2' , 'parent': '0' , 'child': [{}] }, { 'id': '3...
Brian SP
1

votes
1

answer
302

Views

SQL Server Cannot Build Execution Plan

I'm using Microsoft SQL Server 2016 Enterprise SP1: Core-based Licensing (64-bit) Version 13.0.4411.0 and I have an extremely odd problem I don't know how to solve. I have an (admittedly fairly large) query that joins together over 100 tables. I wrote the original a while back and have run the que...
JoZ
1

votes
1

answer
419

Views

MSSQL 2016 Package execution failed | SSIS error: Index was out of range

I've made a copy job with Copy Database Wizard using SMO(leave DB online). The DB is to be copied to the same SQL instance. This fails with The package execution failed. The step failed. If I execute the SSIS package it has created, the following occurs: Event Name: OnError Message: Index was out...
Vaskemaskina
1

votes
0

answer
325

Views

VDI_CLIENT_OTHER wait type in SQL Server 2016

After direct seeding on an on-prem SQL Server 2016, I still have 8 sessions (8 CPUs) permanently waiting on the VDI_CLIENT_OTHER wait type, for two days. It seems to be the result of direct seeding, as mentioned in this anwser. Azure SQL high wait time on 'VDI_CLIENT_OTHER' And Indeed direct seeding...
rudi bruchez
1

votes
0

answer
99

Views

SQL Server : check user/password is valid without trying connect

We have built a vb.net (2015) application that requires the user to logon using name and password. The app only requires a valid login since the active connection does the work using a hidden connection with very specific rights, however we must validate the real user As far as I am aware, the only...
William Slaney
1

votes
1

answer
120

Views

Query Store does not record any data

I have enabled the Query Store for two of my databases (acceptance and production), which are both running on the same instance of SQL Server 2016 Standard Edition. The Query Store records query history on the acceptance database, but on the production database it does not record any data. The two d...
Cătălin Mihai
1

votes
1

answer
596

Views

Not possible to update value of encrypted column

I have used Always Encrypted to encrypt values in a column. My connection string looks like this (everything is working fine and the client is able to decrypt the values): Driver={ODBC Driver 13 for SQL Server};Server=10.10.10.1\INST2;Database=test;Trusted_Connection=yes;ColumnEncryption=Enabled; Th...
gotqn
1

votes
1

answer
1.1k

Views

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set

I'm trying to run a simple R code in SQL Server 2016: EXEC sp_execute_external_script @language =N'R', @script=N'OutputDataSet
AskMe
1

votes
2

answer
41

Views

Trim and Replace functions fail to remove spacing with INTO statement

I'm trying to remove some leading/trailing spaces from a column in a database using the LTRIM and RTRIM function in MSSQL. When I simply select the data, the trim function works correctly: SELECT LTRIM(RTRIM(mycolumn)) FROM mytable However, when I write the same query to a table with an INTO stateme...
galaxie
1

votes
0

answer
38

Views

How to mask always encrypted value in the application layer?

I have an always-encrypted column using SQL Server 2016 SP1 Standard Edition and and the ODBC driver in combination with ADO. Everything is working fine, but when a user has no access to the encryption keys, the encrypted value is returned. This is not very user friendly and I want to return not acc...
gotqn
1

votes
2

answer
34

Views

T-SQL - Average % of Matching Characters

I have data coming from two different systems which I input into two different tables in SQL Server 2016. These tables are related with a Key. Table1 Key Name ------------------------ 1 Jim Parsons 2 Steven Woz 3 Billy Jean 4 Carol Henderson 5 Sara Hauder Table2...
aGuy
1

votes
0

answer
279

Views

UPDATE query on field that has dynamic data mask

I have this situation: A table in SQL Server 2016 has Dynamic Data masks configured on one column. I read one whole row into a front-end and display the values, including the masked field that returns value 'xxxx' To update the table, the application reads the values from the user front-end and gen...
David Ruiz
1

votes
1

answer
229

Views

Clustered index update, how changing the index only at different column value?

I've table, example as Id int primary key(clustered index) Name varchar(255)not null (non-clustered index) .. .. Other columns I execute stored procedur as update table1 set table1.Name=isnull(@Name,table1.Name) ,( updating other columns) where [email protected]; many times Name equals @Name; In execution pla...
KreminT
1

votes
1

answer
43

Views

JSON Query return result with escaped character

From the following query I am getting result as : 'Units': '[{\'Name\':\'Unit25\'}]', Query is as follow query which reproducing this result, I have simplified the query actual query is used but based selection is this: SELECT ( SELECT * FROM ( SELECT JSON_QUERY(COALESCE((SELECT [Name] F...
Furqan Misarwala
1

votes
1

answer
79

Views

SSRS 2016 - Tablix in subgroup fails upon deployment

SQL Server 2016, SSRS: I have an expandable group that works correctly in Report Builder or Visual Studio SSDT, but does not display when run on the server. My charter was to create a report that didn't rely on pop-ups or new windows to display detail data. I've managed to embed that detail data bel...
Barry Seymour
1

votes
1

answer
231

Views

T-SQL/CLR function for deterministic encryption

I have a table with User Agents Strings table with the following structure: UserAgentStringID INT UserAgentStringValue VARBINARY(8000) The [UserAgentStringValue] field is encrypted with symmetric key. The previous version of the table structure was: UserAgentStringID INT UserAgentStringValue NVARCHA...
gotqn
1

votes
0

answer
90

Views

T-Sql update and avoid conflict

I'm trying to migrate a Tomcat app from using Postgres 9.5 to SQL Server 2016 and I've got a problem statement I can't seem to duplicate. It's basically an upsert but one of the complications is the request supplies arguments to do the update, but when there is conflict I need to use some of the exi...
tubbs_uk
1

votes
1

answer
181

Views

All Values in json response including numeric values need to be in double quotes

Iam trying to retrieve the data from the table in json format with below query in SQL Server , Select * from TableA for json path, root( 'table 1') Iam getting json response as , { 'TableA':[ { 'Id':34562, 'A Id':2, 'Name':'ZDR' } ] } Where Field 'ID' & 'A Id' are declared as 'int'in the datab...
Sakthignanavel
1

votes
1

answer
341

Views

Is it possible to upgrade and migrate TFS 2013 with SQL server 2016 to TFS 2017

We upgraded SQL 2012 to 2016 recently and now we are upgrading our TFS 2013 to TFS 2017. Due to the requirements of TFS 2013 indicating that TFS 2013 update 2 is compatible only with SQL server 2012 SP 1 or SQL server 2014 according to docs. My question is: Will I be able to update TFS 2013 directly...
Rohan
1

votes
2

answer
34

Views

Use database with missing file

Issue Good news: Lucky me, its not production, but its a big database (TBs) used for performance testing of scripts and stuff Bad news: While performance testing, i experimented with In-Memory tables, creating FileGroup and file in it. However, by my mistake, the file was created on a SSD drive that...
Vladislav Zalesak
1

votes
6

answer
102

Views

Where clause with three options

I'm using SQL Server 2016 Dev and I have a WHERE clause with 3 options. I solved 2 and I need help on the third one. Please see the code. I can't use a CTE or any other code because it's part of a legacy code and can't be modified except the case on the where clause. Requirements When DepartmentID...
Oded Dror
1

votes
0

answer
31

Views

Dynamically Create Always Encrypted Table in SQL Server using c#

Is there any way to dynamically create an always encrypted table in C# code, just like we how we create a table dynamically in c# code?
Hyder Ahmed
1

votes
0

answer
469

Views

SQL Server Error Log

Application: sqlsqm.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info: System.IO.FileLoadException at Microsoft.SqlServer.Sqm.Launcher.Main(System.String[]). This always happen every day in the log file. I don't know what is the...
vannak
1

votes
0

answer
46

Views

StateChange event issue in SQL Server 2016 and .net 4.7 ado.net

I am facing an issue with SQL Server 2016, we have an application in windows forms to block multiple logins with the same username. The logic that we implemented to prevent this is: the first time a user logs in to the application a DB connection will be opened and the connection will not be closed...
arun

View additional questions