Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server-2016]

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

2
votes
1answer
23 views

Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
0answers
13 views

SQL Server LDF is is keep growing on secondary Always on

I have configured ALways-On (Async mode) on SQL server 2017, recently LDF file has been growing on secondary (80 GB), but on the primary database it is less than 50 MB, also I have set up full back ...
7
votes
3answers
215 views

Access to the path 'c:\some\path' is denied for MSSQL CLR

I think this is a permissions problem, but I'm having trouble locating it. I have a group of CLRs on one server (SQL Server 2016) and they work as they should. All are marked UNSAFE and they do ...
0
votes
2answers
24 views

Query Joining on Column headings

I have Survey results given to me in a massive table. (300+ columns) A list of questions is provided in a table, and the Survey results in another. Questions. +-----+-----+-------------------------...
0
votes
1answer
26 views

Prevent read access on primary AG replica, but allow it on the readable secondary

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
0
votes
1answer
8 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
1
vote
1answer
16 views

SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?

I am in the process of creating a data warehouse using SQL Server for my company. I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process. Now ...
0
votes
0answers
5 views

VS 2019 - Schema Compare - “The certificate chain was issued by an authority that is not trusted” when TrustServerCertificate=True

I'm trying to do a schema compare in VS 2019 with Active Directory Password Authentication. The account I'm using works in SSMS, but fails in VS with the following error, even when ...
0
votes
0answers
14 views

Undone the table partition for a particular year

I have implemented partitioning for a particular table, in such a way that user can partition min 1 and max upto 52, to a particular table for a particular year. Now, after performing partitioning, I ...
0
votes
0answers
18 views

[SSISDB].[internal].[cleanup_server_retention_window] Foreign key Violation

I have setup a job to cleanup SSISDB with predefined storeprocedure "[SSISDB].[internal].[cleanup_server_retention_window]" but sometimes i get following error. After that i have to do manual cleanup ...
4
votes
3answers
685 views

How to determine if a hyphen (-) exists inside a column

In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-): CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS CONTAINS '-' ...
1
vote
0answers
8 views

SQL Server 2016 query Azure AD

I need to query our (Azure) AD to get a user username based on on an email address I am being passed and then write it to a table ideally via an SP. From my brief bit of reading, Azure AD supports ...
-3
votes
0answers
54 views

Can anyone help me with this hideous query plan? [on hold]

Here's the plan https://www.brentozar.com/pastetheplan/?id=SJxeroW6N This is our number one throughput SP, I'm just trying to determine what I can do to improve it.
2
votes
3answers
38 views

SQL Server Index Maintenance-Conditional Rebuilds

I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure ...
0
votes
0answers
25 views

How to test a master key regeneration?

Following up after a situation on an important LIVE server - where I have the master key backed up, but the decryption password for that master key backup file is missing. I can't open the master ...
2
votes
2answers
203 views

'Select @VAR =' and 'Set @VAR =' behaving unexpectedly

Working on a database i didn't setup myself i was digging through some stored procedures to find out what they were doing. In here i found numerous variables being set through a SELECT @var =, I ...
0
votes
0answers
6 views

Disabling SSRS Scale out feature during migration to non-enterprise edition

Server A is SQL Server 2008 R2 Enterprise which has SSRS installed. I have migrated across the reporting database, reporting tempdb, etc. On Server B is SQL Server 2017 standard edition with SSRS. I ...
2
votes
2answers
19 views

How to detect whether a key is present in a JSON object using TSQL?

This is an interesting one. Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d. Imagine this object is passed to a stored procedure and every key is ...
0
votes
0answers
16 views

How to fix ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER error

I'm getting below error while executing PS script Error Code: ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER More Information: The SQL provider cannot run because of a missing dependency. Please make sure ...
0
votes
1answer
39 views

Having a backup of the master key, how can I restore a database that was encrypted by master key?

while setting up AlwaysOn - Availability Group on a test system, I generated all the restores from live and restored them to this new server. then I backup all these databases to a network share to ...
1
vote
1answer
28 views

Snapshot on Always On Availability Group in suspect mode

Our configuration is 2 SQL Servers 2016, enterprise edition, 13.0.5026.0, running on Windows Server 2012 R2. Created availability group with one database in it. Database has 3 data files in it, all ...
5
votes
0answers
55 views

CMEMTHREAD waits during Index Optimisation in Availability Group

In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation: Troubleshoot: Changes on the Primary Replica are not ...
1
vote
0answers
24 views

Searching for and parsing out specific text parts within the values of a column

I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values. Sometimes the relevant column will contain a person's name, and sometimes it will ...
2
votes
0answers
23 views

Paralleled SSIS package flatlining at 20% CPU

I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter ...
2
votes
1answer
92 views

Understanding the differences in the storage space used by two tables

I have two tables in which, more or less, one contains a subset of data of the other one. There is some data I delete from the first table, which is inserted in the second one as historical data. ...
3
votes
1answer
126 views

Why isn't space deallocated after deleting all rows from a clustered table?

Today one of our queries has gone crazy. It was hanging for more than an hour before I killed the process. UPDATE tc SET tc.Status = 0 FROM #RC_Task_Calc AS tc CROSS APPLY ( SELECT TOP ...
1
vote
1answer
26 views

Create a partition for a temporal table based on a period

To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension. For my Employee dimension, I created a temporal table. Now I want to split data into ...
4
votes
1answer
222 views

Why does JSON_QUERY within CASE statement fail to remove escape characters?

It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement. Example code below. declare @data ...
2
votes
1answer
18 views

SSIS Merge Join: Delete does not work

I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere). Merge Join: Inserting and Updating works fine,...
0
votes
1answer
41 views

spliting a row based on max values

I have a table with 3 fields and one is a Qty. If the Qty is > than a set amount I would like to split the row into a second row and/or third row Row Order Product qty 1 123 AAA ...
2
votes
1answer
11 views

Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated ...
4
votes
1answer
81 views
+50

Tracking errors from prepared SQL statements

I use extended events to store database errors, like this: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_hostname, ...
2
votes
2answers
54 views

How to change disk for “There is insufficient free space on disk volume”?

Restore of database 'Configurations_6.2.2.25' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient....
2
votes
1answer
34 views

DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format

DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format Thought to get the experts opinion. Below options to convert / cast didn't work: CONVERT(VARCHAR(19),CONVERT(DATETIME, wfm.date,3)...
2
votes
1answer
49 views

What types of locks are held on global temporary tables?

I am wondering if: the isolation level is Read Committed data is inserted or deleted or read are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
6
votes
1answer
86 views

Issue with variable length string between compatibility level 80 and 100

I was getting tons of issues related with a specific scenario on my current enterprise. We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to ...
0
votes
0answers
17 views

Connect google cloud storage bucket in MSSQL(2016)

I host a SQL Server Enterprise 2016 + Windows Server 2016 DataCenter on google compute engine. I'm going to use .bak file on my google cloud storage (GCS) bucket, to restore database on other folder ...
1
vote
1answer
33 views

Using SUBSTRING and RTRIM Together

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into ...
1
vote
1answer
48 views

Always On Availability Question mark

In my production environment I have two database Servers know as DBN1 and DBN2. I have made a cluster between two and DBN1 is my primary database server. Now I used Always on availability feature on ...
3
votes
2answers
190 views

Max worker thread exceeded after manual AG failover

We have two SQL Server 2016 SP2 CU6 Enterprise running on physical hardware (32 cores) with one AG that contains 5 db's. The secondary is not readable. Today we did a planned manual failover, so we ...
1
vote
2answers
38 views

SQL Server 2016 Always On with Availability Groups: add instance on both servers

I have a WSFC with SQL Server 2016 Enterprise Edition and 4 Availability Groups. Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM. Currently I have one instance on each, they serve 4 ...
1
vote
1answer
31 views

Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts ...
1
vote
1answer
37 views

Altering endpoint of existing AG

Looking for some advice here: I currently have 3 SQL servers in a single AG with a single network card on each server. During times of heavy network usage, we are sometimes seeing failovers to a ...
1
vote
0answers
21 views

How to handle/separate big database with blobs

We have a specialized business software (transportation) running on SQL Server. The software company decided to store all sorts of files (invoices, scans, pdfs, emails, pictures, ...) that have to be ...
3
votes
1answer
103 views

Trying to repair query store, but sp_query_store_consistency_check does not exist

The query store on our SQL Server 2016 Standard (SP2 CU6) database has gone into ERROR state and we are now trying to get it back online. All of the articles I've seen regarding troubleshooting QS (e....
12
votes
2answers
460 views

Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50. In the mornings, when trying to ...
1
vote
1answer
70 views

SQL Server Metric Report Information For Management [closed]

We have a quarterly metric meeting and I report the metrics for all of our production SQL Servers. The people that are looking at these reports are mostly management (some technical, but others are ...
18
votes
1answer
1k views

Why does this derived table improve performance?

I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs. An example input might be the following. declare @json nvarchar(max)= N'[[40.7592024,-73....
2
votes
2answers
131 views

How to replace multiple parts of a string with data from multiple rows?

Here's a fiddle to show what I'm after. Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in ...
12
votes
1answer
142 views

SQL Extended Events Session for deadlock detection

Is there a way to increase the size of the <inputbuf> element in the deadlock XML captured by the deadlock extended events session? We want to see the complete query to help pinpoint the issue ...