Questions tagged [sql-server-2016]
Use this tag for questions specific to the 2016 version of Microsoft's SQL Server.
3,830
questions
2172
votes
33
answers
1.7m
views
How to check if a column exists in a SQL Server table
I need to add a specific column if it does not exist. I have something like the following, but it always returns false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ...
- 22.1k
309
votes
2
answers
206k
views
How do I move a table into a schema in T-SQL
I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.
- 8,790
222
votes
13
answers
224k
views
SQL Server: Filter output of sp_who2
Under SQL Server, is there an easy way to filter the output of sp_who2? Say I wanted to just show rows for a certain database, for example.
- 11.5k
110
votes
8
answers
142k
views
Is there a LastIndexOf in SQL Server?
I am trying to parse out a value from a string that involves getting the last index of a string. Currently, I am doing a horrible hack that involves reversing a string:
SELECT REVERSE(SUBSTRING(...
- 60.7k
83
votes
2
answers
6k
views
Why is 199.96 - 0 = 200 in SQL?
I have some clients getting weird bills. I was able to isolate the core problem:
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
SELECT ...
- 1,493
69
votes
3
answers
99k
views
SQL Server OPENJSON read nested json
I have some json that I would like to parse in SQL Server 2016. There is a hierarchy structure of Projects->Structures->Properties. I would like to write a query that parses the whole hierarchy but ...
- 2,393
56
votes
1
answer
51k
views
OPENJSON does not work in SQL Server?
I want to use JSON functions in SQL Server 2016, but when I try to execute OPENJSON function, I get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'openjson'.
Why it ...
- 563
54
votes
9
answers
67k
views
SQL to JSON - array of objects to array of values in SQL 2016
SQL 2016 has a new feature which converts data on SQL server to JSON. I am having difficulty in combining array of objects into array of values i.e.,
EXAMPLE -
CREATE TABLE #temp (item_id VARCHAR(...
44
votes
4
answers
44k
views
How do you OPENJSON on Arrays of Arrays
I have a JSON structure where there are Sections, consisting of multiple Renders, which consist of multiple Fields.
How do I do 1 OPENJSON call on the lowest level (Fields) to get all information ...
- 7,502
44
votes
5
answers
61k
views
Create nested JSON arrays using FOR JSON PATH
I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship.
I would like the values of the secondary table to be nested as array properties of ...
- 927
43
votes
8
answers
191k
views
SQL Replace multiple different characters in string
I need to replace multiple characters in a string. The result can't contain any '&' or any commas.
I currently have:
REPLACE(T2.[ShipToCode],'&','and')
Which converts & to and, but how do ...
- 1,139
41
votes
6
answers
12k
views
LocalDB parent instance version invalid: MSSQL13E.LOCALDB
I'm unable to add a database on a developer machine.
I'm running win 10, visual studio 2015.
I re-installed SQL server 2016 twice, last time with firewall disabled it all gave green marks in the end.
...
- 2,151
39
votes
5
answers
46k
views
Get length of json array in SQL Server 2016
You know about the new JSON_ support in SQL Server 2016 so
let's say I have this data in a row
{
"BaseBoarding": 1,
"PriceLineStrategy": "PerPersonPerNight",
"Currency": "EUR",
"...
- 14.9k
38
votes
8
answers
149k
views
SQL Select everything after character
I'd like to select everything AFTER a certain character (-) that is placed on the most right side.
Eg.
abcd-efgh-XXXX
And I'd like to select the XXXX part
Thanks!
- 1,139
34
votes
3
answers
139k
views
STRING_AGG is not a recognized built-in function name
I've downloaded and installed SQL Server 2016. When I attempted to use the STRING_AGG function I receive this error. Here is my code:
SELECT STRING_AGG(cast(FieldNumber AS VARCHAR(100)), ',')
FROM ...
- 64.3k
30
votes
5
answers
72k
views
How to search SQL column containing JSON array
I have a SQL column that has a single JSON array:
{"names":["Joe","Fred","Sue"]}
Given a search string, how can I use SQL to search for a match in the ...
- 2,418
28
votes
4
answers
107k
views
Accessing JSON Array in SQL Server 2016 using JSON_VALUE
I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code -
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData')...
- 479
27
votes
7
answers
88k
views
SQL Server : can you limit access to only one table
I think the answer is no but I'm looking to give someone access to a SQL Server database but I only really want them to have access to one table.
It's easy enough to limit someone to only access one ...
- 804
27
votes
7
answers
21k
views
SQL Server 2016 JSON: Select array of strings instead of array of objects
I am new to JSON in SQL Server and can't figure out how to return a simple array of strings:
DECLARE @T TABLE ([value] NVARCHAR(MAX))
INSERT INTO @T ([value]) VALUES ('foo')
INSERT INTO @T ([value]) ...
- 2,829
26
votes
3
answers
19k
views
How can I use System-Versioned Temporal Table with Entity Framework?
I can use temporal tables in SQL Server 2016. Entity Framework 6 unfortunately does not know this feature yet. Is there the possibility of a workaround to use the new querying options (see msdn) with ...
- 2,548
26
votes
3
answers
1k
views
NHibernate HQL Generator to support SQL Server 2016 temporal tables
I am trying to implement basic support for SQL Server 2016 temporal tables in NHibernate 4.x. The idea is to alter SQL statement from
SELECT * FROM Table t0
to
SELECT * FROM Table FOR ...
- 762
24
votes
5
answers
4k
views
How to get column-level dependencies in a view
I've made some research on the matter but don't have solution yet. What I want to get is column-level dependencies in a view. So, let's say we have a table like this
create table TEST(
first_name ...
- 111k
23
votes
7
answers
21k
views
How to force SQL Server to return empty JSON array
I'm using SQL Server 2016, which supports JSON PATH to return JSON string.
I wonder how to get just a simple empty json array, I mean [] when my query or sub-query returns null. I've tried this query:
...
- 3,482
22
votes
3
answers
65k
views
Passing JSON type as parameter to SQL Server 2016 stored procedure using ADO.Net in ASP.Net Core project
Can someone give example how to pass JSON type as parameter to SQL Server 2016 stored procedure using ADO.Net in C# ASP.Net Core Web Api project ?
I want to see example of SQL Server 2016 stored ...
- 1,233
22
votes
5
answers
15k
views
Entity Framework not working with temporal table
I'm using database first entity framework 6. After changing some of the tables in my schema to be temporal tables, I started getting the following error when attempting to insert new data:
Cannot ...
- 3,386
21
votes
3
answers
9k
views
SQL Server bug or feature? Decimal numbers conversion
I am experiencing some strange behavior in SQL Server when working with decimal and numeric data types. I have a formula that calculates a specific value (4.250), and I'm using the same rounding and ...
- 5,577
21
votes
2
answers
9k
views
Choosing a binary collation that can differentiate between 'ss' and 'ß' for nvarchar column in Sql Server
As the default SQL_Latin1_General_CP1_CI_AS collation of SQL server can't differentiate between ss and ß, I want to change the collation of a specific column in a table to ...
- 4,876
20
votes
1
answer
11k
views
TSQL RaiseError incorrect syntax, following MSDN's guidelines
MSDN states the following syntax:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
The msg_str expects a string up ...
- 6,529
20
votes
3
answers
27k
views
Get multiple rows using FOR JSON clause
Using PostgreSQL I can have multiple rows of json objects.
select (select ROW_TO_JSON(_) from (select c.name, c.age) as _) as jsonresult from employee as c
This gives me this result:
{"age":65,"...
- 1,126
20
votes
9
answers
29k
views
Database '[DatabaseName]' already exists. Choose a different database name
I'm following this guide illustrating a code-first approach with Entity Framework core. Migrations were functioning correctly until, at some point, I deleted my .mdf file. Since then, executing Update-...
- 38.2k
20
votes
5
answers
106k
views
Please create a master key in the database or open the master key in the session before performing this operation
I get the following error on secondary replicas when trying to restore an encrypted backup even though the replica has the master key (dmk), service master key, certificates and private keys restored ...
- 429
19
votes
10
answers
1k
views
Query without WHILE Loop
We have appointment table as shown below. Each appointment need to be categorized as "New" or "Followup". Any appointment (for a patient) within 30 days of first appointment (of that patient) is ...
- 22.5k
18
votes
5
answers
71k
views
The report server was unable to validate the integrity of encrypted data
The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData) .
Could not restore the encryption key, so I deleted the keys and tried to ...
- 369
17
votes
5
answers
2k
views
Conditional UNION ALL in table function
So use case is as follows - there're some parameter, based on which I want to select data from one table or another.
create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo....
- 111k
17
votes
4
answers
24k
views
Edit a dtsx through SSMS
I created and executed a dtsx with SSMS corresponding wizard:
This was to import a flat file in an existing table.
At the end I saved the "package" as a .dtsx file
Now I need to modify the column ...
- 3,211
16
votes
3
answers
81k
views
Why can I not install SQL Server Express 2016 on Windows 7 Professional 64 bit SP1?
When trying to install SQL Server 2016 Express in Windows 7 Professional x64 SP1 in VMware I get the following message.
What can I do to solve this?
- 1,681
16
votes
4
answers
12k
views
SQL Server Always Encrypted with .NET Core not compatible
I'm trying to use the Always Encrypted feature of SQL Server 2016 with .NET Core and seems like it can not be used (yet). Trying to import the Microsoft.SqlServer.Management.AlwaysEncrypted....
- 2,121
16
votes
2
answers
27k
views
Incorrect syntax near format in BULK INSERT?
I'm trying to figure out why the BULK INSERT command I'm using isn't recognizing the FORMAT and FIELDQUOTE options used in the command.
BULK INSERT dbo.tblM2016_RAW_Current_Import_File
FROM '\\x\tms\...
- 351
16
votes
3
answers
11k
views
Custom Authentication (Security Extension) with SSRS 2016
I am trying to implement custom forms authentication with SSRS 2016.
Within this article, a reference is made to the SQL Server Reporting Service Product Samples.
It appears that the latest ...
- 1,615
16
votes
3
answers
31k
views
System.OutOfMemoryException occurs frequently after Upgrading SQL Server 2016 to version 13.0.15700.28
I am running Windows 10 Pro 64 bit on a dev box that has multiple monitors, 16 gigs DDR4 RAM, 4 Ghz I7, GTX 970. I run SQL Management Studio with SQL Server 2016 Developer Edition along with VS 2015 ...
- 14.4k
15
votes
2
answers
3k
views
How can I keep SSMS Find and replace in files "Look In" from changing
In SSMS when I do a
Find and Replace in Files
(default keystroke CTRL+SHIFT+H)
Edit > Find and Replace > Replace in Files
If I set the "Look In" to "Current Document" then do a change and ...
- 33.6k
14
votes
5
answers
79k
views
Cannot be opened because it is version 852. this server supports version 782 and earlier
I am using Visual Studio 2017 and SQL Server 2014. While attaching a database file to Visual Studio, I get this error:
""
After upgrading the file I used this connection string
<...
- 185
14
votes
3
answers
7k
views
SQL Server 2016 for JSON output integer array
I'd like to get JSON with an array of integers using SQL Server 2016's For JSON feature. I'm stumped on array of integers.
Database table structures:
declare @Employees table (ID int, Name nvarchar(50)...
- 852
13
votes
15
answers
81k
views
SSIS Error: VS_NEEDSNEWMETADATA
I'm currently updating all of our ETLs using Visual Studio 2015 (made in BIDS 2008) and redeploying them to a new reporting server running on SQL Server 2016 (originally 2008R2).
While updating one ...
- 1,446
13
votes
4
answers
9k
views
SQL Server 2016 - Temporal Table - how to identify the user
Is it possible to get the information about the user/connection that modified data that is in the historical table? I read about the audit scenario where I can use temporal tables and that it's ...
- 497
13
votes
3
answers
9k
views
What is the best way to query deleted records with SQL Server 2016 temporal tables?
I'm looking at SQL Server 2016 temporal tables and can't find any efficient way to query for all historical records that are now deleted.
I prefer not to soft-delete or moving to a 'deleted items ...
- 2,057
13
votes
3
answers
1k
views
SQL over clause - dividing partition into numbered sub-partitions
I have a challenge, that I've come across at multiple occasions but never been able to find an efficient solution to. Imagine I have a large table with data regarding e.g. bank accounts and their ...
- 133
12
votes
3
answers
6k
views
Avoid schema mismatch in System-Versioned tables
Looking for a workaround for:
Error: SQL71609: System-versioned current and history tables do not have matching schemes. Mismatched column: 'XXXX'.
When trying to use SQL 2016 System-Versioned (...
- 5,114
12
votes
3
answers
110k
views
JSON text is not properly formatted. Unexpected character 'N' is found at position 0
I am new to JSON in SQL. I am getting the error "JSON text is not properly formatted. Unexpected character 'N' is found at position 0." while executing the below -
DECLARE @json1 NVARCHAR(4000)
set @...
- 417
12
votes
3
answers
6k
views
SQL Server 2016 Cannot add system versioning to relationship table
The SQL Server 2016 system versioning is cool. I am using the free Developer version. Thanks MS!
I am unable to figure out if it will give me versioning of many to many relationships. I have a User ...
- 135