Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
0 answers
31 views

Workaround for a view with joined data on encrypted column

I've got a view that currently has this within: SELECT * FROM customers_1 LEFT JOIN customers_2 on customers_1.last_four_of_ssn = customers_2.last_four_of_ssn AND customers_1....
2 votes
2 answers
13k views

"Untrusted Domain" error when connecting to SQL Server localhost and specifying port

I have two SQL Server 2016 instances running on localhost, and I can connect to both with no issues (localhost and localhost\test) using windows authentication. However, if I specify the port ...
1 vote
1 answer
2k views

Optimizing single-row DELETEs from a table with many FOREIGN KEY references

In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it. If I delete one ...
1 vote
1 answer
38 views

Unique constraint for single structure that simulates relations between tables

Imagine I have several tables in SQL Server 2016: Cat Cat carrier Cat transit box Cat car Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
9 votes
1 answer
646 views

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

I have the following tables in my database. LastNames Table CREATE TABLE [dbo].[LastNames]( [LastNameID] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](50) NOT NULL ) ON [PRIMARY] GO ...
1 vote
1 answer
42 views

Index not being used for date predicate

I'm doing abit of self learning on indexes & performance in a SQL Server. I'm using stackoverflow's database as my sample DB for this activities. For my testing, I'm using the Users table & ...
3 votes
1 answer
383 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
1 vote
2 answers
1k views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
1 vote
1 answer
4k views

The OLE DB provider "MSDASQL" for linked server "(null)" reported an error

I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
0 votes
1 answer
1k views

What is the best way to replace a SQL Server database with an updated one?

I have a SQL Server instance with 3 databases in it (SQL Server 2016 Web on Win Server 2012 r2). All databases have relations with each other. It is live and is feeding some web services. I need to ...
0 votes
0 answers
57 views

In SQL Server database mail is not working

Database mail is not working: Below Error while sending a test mail Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641) Checked ...
1 vote
1 answer
5k views

How to check who has permission to execute a stored procedure?

I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple ...
0 votes
1 answer
530 views

Adding another Secondary Log Shipping location

I've seen a few variations of this question, but no clear cut answers to my situation. I have an existing log shipping setup, and want to add a secondary server. Here is how I understand what will ...
2 votes
3 answers
2k views

SQL Server 2016 - Configuration Manager WMI Problem

Background: Windows Server 2016 Core SQL Server 2016 Enterprise (obviously 64-bit) Windows Failover Cluster SQL Failover Cluster I am attempting to connect to SQL Server Configuration Manager via my ...
0 votes
0 answers
35 views

Data Loss and data redeundancy when forced failed over to secondary replica on DR Data Center in SQL Server 2016

We are using SQL Server 2016 Enterprise edition and planning to have 2 datacenters primary and the remote Disaster recovery(DR). We have 2 replicas (primary replica and secondary replica) in the ...
2 votes
1 answer
3k views

Always On Availability Groups connection with secondary database established for primary database [closed]

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? though it's showing No user ...
0 votes
2 answers
794 views

DatabaseBackup backs up the same database twice?

I use the Ola Hallengren DatabaseBackup procedure to run backups. I have two jobs configured so the backups can run parallel as I have one large database that takes 45 minutes to back up and many ...
1 vote
1 answer
767 views

How does one export/import registered servers with passwords?

I have a pretty extensive collection of registered servers on SQL Server Management Studio 2016 on my box. However, I got a new box and want to move the entire collection there. So I right-clicked ...
0 votes
1 answer
1k views

Backup and restore of large SQL Server database with least downtime

I am trying to migrate a SQL Server 2016 database to a SQL Server 2019 server. The database is huge at about 500 GB. It takes about 3 hours to create a Full compressed Backup of about 300 GB and 2.5 ...
0 votes
0 answers
19 views

Reduce primary DATAFILE size for an SQL Server Database 2016

In an AG always on configuration (primary) I have a database with 10TB datafile, 50% of the datafile is a free space. Then, I want to reduce this DATAFILE from 10TB to 8TB. How to proceed for that ...
user avatar
1 vote
2 answers
2k views

Copy a database to a new schema in an existing database

Just curious if there is a way to create a new schema that is based on an existing database. Assuming the existing database has dbo schema only. I will be taking an entire database and moving it to an ...
1 vote
2 answers
4k views

All instances upgrade from Command Prompt

I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation Install SQL Server 2016 from the Command Prompt regarding this issue. ...
0 votes
1 answer
424 views

SSRS2016 external parameters for mobile report

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's ...
1 vote
2 answers
341 views

How can I see the plan for ONE statement in a stored procedure?

I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes. Now I've wrapped it in a stored procedure (with no parameters), the performance ...
1 vote
2 answers
616 views

I am looking for a query to determine when the snapshot has been finished applying on the subscriber

Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber? I have a transnational pull replication set up for reporting purposes and it also ...
1 vote
1 answer
392 views

Running Excel addin for MDS2019 vs MDS2016

We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to ...
1 vote
2 answers
2k views

split SQL FULL Backups on ola hallengren maintanance solution

I am using Ola Hallengren backup solution in all our environment, I have a very big Backup file which is taking 10+ hours to complete, so I am thinking to split SQL Server Full backup files. anyone ...
0 votes
0 answers
47 views

Intermittent Error On SQLServer Hourly Database Backup Maintenance Plan

Some of our SQLServer machines' hourly maintenance plan with Back Up Database Tasks are failing intermittently, around once or twice per week. The next hourly execution runs without any issue. Each ...
0 votes
2 answers
49 views

Trying to renumber a field in order grouping by another

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful ...
2 votes
1 answer
1k 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
1 answer
36 views

snapshot to apply just the schema changes - not the data

I am restoring databases to each side of my transactional and merge replications. Basically the publication and the subscriber are the same database initially. I still need to run the snapshot agent ...
1 vote
1 answer
49 views

ApplicationIntent = ReadOnly and Writes queries

I'm confused about behavior of connection string that specifies ApplicationIntent = ReadOnly and write queries in SQL Server 2016 Enterprise Edition. Let's say, Availability Group has a primary - X1 ...
0 votes
2 answers
672 views

What is the best way to measure INDEX impact on production?

We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
3 votes
1 answer
5k 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 ...
28 votes
3 answers
17k views

SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC ...
0 votes
1 answer
2k views

SQL to check if a certain time duration occurs within a particular period of the day

I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time (Few hours) up to a number of days in duration. I'm trying to establish a way ...
5 votes
1 answer
622 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 (...
3 votes
1 answer
472 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...
1 vote
1 answer
434 views

Is there a way to specify which schema the database task "Export Data-tier Application" exports for the other entities besides the tables?

I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object ...
-1 votes
1 answer
1k views

Maintain Foreign Keys using Import/Export Wizard when creating a SSIS package

I´m trying to create a SSIS package in SQL Server Management Studio using the Import/Export Wizard. I´m trying to copy the tables from an existing db to another one, but I´m getting errors because of ...
19 votes
1 answer
6k views

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
0 votes
1 answer
2k views

SQL Full-text Filter Daemon Launcher failing to start with error "Access is Denied"

When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails. In the event log we get the following ...
0 votes
1 answer
55 views

Extra `CASE WHEN` Removes Text from Dynamically Created String

I am trying to generate a CREATE TABLE statement based on a given table in my SQL-SERVER database using the INFORMATION_SCHEMA tables, but when I try and concatenate the DEFAULT portion of a field ...
2 votes
2 answers
681 views

SSIS upsert from CSV File Source

I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns 1 x CSV Source (loop through multiple CSVs same structure 100 + columns) 1 x Staging Table Destination ...
0 votes
1 answer
295 views

Is there any option to enable Window NT logs in SQL Server Logs

I'm trying to view SQL Server logs on a specific server and is missing Windows NT section in same. Though other server are showing that section. In the screenshot shared below, I highlighted section ...
1 vote
1 answer
7k views

Linked server error- The table either does not exist or the current user does not have permissions on that table

There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
1 vote
0 answers
37 views

DST Adjustments to Ola Hallengren's backup jobs not working as intended [closed]

I have been using Ola Hallengren's backup/maintenance scripts for a few years but one thing that usually plagued me once a year was our backup location going near capacity after spring forward DST. I ...
3 votes
0 answers
172 views

Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?

We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
0 votes
2 answers
47 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
0 votes
0 answers
66 views

SQL Server Database Audit used with a trigger to monitor CREATE TABLE events

Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations. Problem is that changes to ...

1
2 3 4 5
56