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
0
votes
1answer
29 views

SSIS: Extract SQL Data and Split across Flat files based on row count

I have created a simple SSIS package that queries a table and extracts data to a flat CSV file. In production this extract could be millions of rows and I want to split the flat file destinations into ...
0
votes
2answers
1k views

Is there any Cross-platform SQL editors that allow joins from different databases?

Initially tried dbeaver but after configuring it, I realized that you can't perform joins from different data bases. Also tried TeamSQL which apparently does what I need but looks to be designed ...
-1
votes
0answers
22 views

How to add a SUBTREE COST collumn on this query?

I could create mixing a lot of other queries, a query that will list the most costly queries running on a server. SELECT TOP 50 SUBSTRING(qt.TEXT,(qs.statement_start_offset/2)+1, ((CASE ...
-1
votes
1answer
308 views

How can I compare row values as column name to the another table and Get data (Sql Server-2017)? [closed]

CREATE TABLE Table1( ID int NOT NULL, PTA_1 int NOT NULL, PTA_2 int NOT NULL, PTA_3 int NOT NULL, PTA_4 int NOT NULL, PTA_5 int NOT NULL, DTA_1 int NOT NULL, DTA_2 int NOT NULL, DTA_3 int NOT NULL, ...
2
votes
1answer
133 views

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes....
5
votes
1answer
501 views

Reinitialize Table Values in SQL SSDT Unit Testing

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
0
votes
0answers
36 views

Restore sql server DB between Cumulative updates

Respected Sir, Please I want to know - Can we restore one database on version- 13.0.5888.11(5001092 Cumulative update 17 (CU17) for SQL Server 2016 Service Pack 2 Latest CU) To 13.0.5103.6 (4583460 ...
0
votes
0answers
28 views

SQL Server Replication - accidentally unselected table from snapshot: how do I add it back in again?

Using SQL Server merge replication, one of the tables was accidentally unselected (check box unticked). How do I add it back in to the snapshot again? It was unfortunately a large table with plenty ...
-3
votes
1answer
45 views

Expose SQL server externally [closed]

I currently have a sql server database and I want to be able to expose this over the internet so that someone external to my network can access the tables/data. How best can I achieve this and what ...
25
votes
3answers
8k views

How to disable “Completion time:…” in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
0
votes
1answer
448 views

Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this ...
0
votes
2answers
35 views

changing SSMS settings via Powershell

in order to enable the dark theme to SSMS (not available on the picture below as you see): following this webpage: SQL SERVER Management Studio 18 - Enable Dark Theme - SQL Authority with Pinal Dave ...
3
votes
1answer
3k views

SQL Server 2016 Linked Server OPENQUERY Hangs

I've been trying to figure out this linked server problem for about a week now with no luck. The set up I have is a linked server from an instance of SQL Server 2016 to an instance of SQL Server 2016 ...
1
vote
1answer
73 views

Ola hallengren SQL Server Index and Statistics Maintenance solution

We are using the Ola hallengren SQL Server Index and Statistics Maintenance solution from the past 6 months in our production system. Script is only used for Update Statistics not Index maintenance. ...
4
votes
2answers
4k views

Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature. This is the command we're using: INSERT INTO ...
1
vote
1answer
50 views

Distribute Guests over Rooms Evenly

I have a table of packages: create table Package ( PackageID int, Guests int, Rooms int ); insert into Package(PackageId, Guests, Rooms) values (1, 1, 1), (2, 2, 1), (3, 2, 2), (5, 3, 2),...
1
vote
2answers
816 views

What configurations do I need to fix the “unable to begin a distributed transaction” error when trying to run a remote procedure?

This is the error message I'm receiving: Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1] The operation could not be performed because OLE DB provider "SQLNCLI11" ...
5
votes
1answer
349 views

Online index rebuild higher fragmentation on intermediate level

I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on ...
0
votes
1answer
57 views

How to restrict Users to see only their databases where has access? [duplicate]

I need to restrict the list of database for our users in a SQL Instance In order to move forward this I revoked the view any database permission to the user desired. But I figured out that only ...
5
votes
2answers
514 views

Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?

I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting: "Something in your plan is forcing a serial query. Further investigation is needed if ...
0
votes
1answer
106 views

Affinity side effects / TF 8002- how to measure

Environment: We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0. We have multiple SE instances on one physical server. We use processor ...
3
votes
1answer
103 views

Determine minimal permissions required for a given SQL script or session

For security reasons I need to determine minimal permissions required for a given script or session without having to manually audit activity then work it out by hand. I'm assuming this can be done ...
1
vote
2answers
82 views

Why does the SQL Server build number sequence not correlate with the release date sequence?

I'm looking at the Microsoft SQL Server 2016 Builds (see that section heading). The list of builds is given in build number order. That is, at the top of the list is the "highest" build ...
1
vote
1answer
29 views

Automatically expiring records in a temporal table?

I have a need to store some results that are only valid for a rolling 5 day window. An application will insert or update records and the results should be viewable for the 5 day window. However, there ...
0
votes
1answer
76 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
2
votes
2answers
263 views

If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

I am reading mixed messages about server level object syncing between replicas in an Availability Group. As I understand it, server-level objects must be kept in sync (aside: then why isn't it part ...
2
votes
2answers
126 views

SQL server AlwaysOn alternate option for readintent=true

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly" Now my question is , if some ...
0
votes
1answer
139 views

Will a Transactional Replication break the LSN chain in my backups

I need to create a fairly up to date read only copy of parts of a production database for reporting purposes. I did look at log shipping to keep the copy up to date but this will break the LSN and ...
0
votes
1answer
30 views

How to Ensure all Log backups are restored on secondary server in log shipping

I have some concerns regarding the restore job on secondary server in log shipping in Microsoft SQL Server 2016 for below scenario : I have two servers, primary and secondary. The backup, copy and ...
0
votes
1answer
517 views

SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it)

I am a beginner SQL DBA and I experienced a weird thing in SQL server. Symptom: Session is in Running state (not runnable, not suspended). CPU time is zero, or if it is not, after a while it ...
3
votes
2answers
49 views

Question Regarding ISDATE Function and DATENAME Functions

We have had an issue this morning with a report failing due to the following error: "Conversion failed when converting date and/or time from character string" The issue is that in the ...
-1
votes
1answer
60 views

SQL service stop date

is there a Powershell command or through windows to know since when my SQL service is stopped Thanks
0
votes
1answer
28 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15
11
votes
1answer
13k views

STRING_SPLIT with a multiple-character separator?

SQL Server 2016 introduced STRING_SPLIT which is very fast and an excellent replacement for any homemade implementation people would roll before 2016. Unfortunately, STRING_SPLIT only supports a ...
1
vote
0answers
21 views

Can't delete secondary mirror DB stuck in (Mirror, Disconnected / In Recovery) [duplicate]

i'm facing an issue with a corrupted secondary database on SQL Server 2016. After a massive outage we were able to restore most of the databases immediately without issues. Two databases were in the &...
2
votes
2answers
278 views

SQL Server 2016 - write output file to mapped Samba share (CentOS 7)

I have a stored proc that's going through the tables, getting data, and write the result into a csv file (mainly to provide up to date data for FTP customers). When writing it to the local D: drive, ...
0
votes
0answers
45 views

Super complex Schema will not migrate to PaaS

Using a Dedicated 2016 datacentre SQL Server 2 vcpus, 14 GiB memory for testing. We have currently found a migration issue with the database Migration SQL 2016 to Azure PaaS. The transfer of schema ...
4
votes
3answers
236 views

How can we restrict tempdb usage for specific users or queries on SQL Server?

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run ...
0
votes
2answers
138 views

Performing Sum in Case Statement

I am trying to get a sum inside a case statement, but I am getting the error Cannot perform an aggregate function on an expression containing an aggregate or subquery This is my query syntax ...
1
vote
1answer
9k views

Non-yielding Scheduler Error with dump caused by heavy queries on SQL 2016

After bringing our databases to new servers with new hardware and new os (2012 server) and new SQL Server version (2016 sp1) we face the following issue: During nightly reporting database update runs,...
1
vote
2answers
1k views

SQL Server Internal Memory Error

I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics. Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (...
0
votes
1answer
164 views

PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2. Is there a way to keep that from happening? SELECT s....
5
votes
3answers
437 views

Resource Pool Stats Counter Reporting Zero

I utilize the resource governor on my 2016 SQL Server. In prior 2016 SQL Server versions, this query: select * from master.sys.dm_os_performance_counters opc where opc.object_name = 'SQLServer:...
0
votes
1answer
25 views

SQL job sending email even if not configured to send notifications

I am using this version of SQL Server. Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
0
votes
0answers
16 views

SQL Mirroring Replace Certificates

I have inherited two SQL 2016 servers with two mirrored databases. They have been built with certificates as they are in a workgroup, the certs expire in six months. I am trying to understand the ...
0
votes
2answers
61 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
1
vote
1answer
1k views

SQL Server Connection Timeout Issue

I have a application, which runs for the entire hour, every hour of every day. It basically has 400ish 'projects' to execute within each hour, and loops over the scripts, which execute stored ...
1
vote
1answer
230 views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
2
votes
1answer
529 views

Secondary Database Replica in Reverting/InRecovery (Long time)

Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time Hi, One of my AG secondary replica has been in reverting/in recovery for ...
0
votes
1answer
57 views

String aggregation using STUFF

Let's say I have table #DOC like this: polNum boxNo batchNum 111111 null qwe1234 111111 ff/55 rto1235 111111 ee/59 yhn1238 111111 ww/55 rto1235 222222 dd/58 jkl1234 222222 null fgh1234 333333 ...

1
2 3 4 5
50