Questions tagged [sql-server-2016]

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

420 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
81 views

How to check the errorlog for important messages

I would like to receive an email every day from each of "my" SQL Servers, with information from the ErrorLog. I would like an easy way to determine if there is any important new information, that I ...
1
vote
1answer
57 views

One instance, four clients, one is slow

We have a small office setup with one Windows Server 2012 running SQL Server 2016 Express. The database is hosting files for our accountant software. This is a multi module software that runs ...
1
vote
0answers
77 views

Batching a query from a linked Oracle database in MS SQL

Working to compare and older Oracle database with a newer updated version to check that all documents have migrated correctly. 1=1 and 2=2. The database is very large ~25,000,000 rows. So I want to be ...
1
vote
1answer
63 views

is there a way to improve this query by not re calculating functions? cross apply?

when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures. this is the picture from the ...
1
vote
0answers
66 views

SQL Mirroring Message “The mirroring connection to ”address“ has timed out for database ”database"

Executing under SQL Server 2016 SP2 (SP2-CU2-GDR) (KB4458621) enterprise edition. Receiving error message "The mirroring connection to "address" has timed out for database". Do not have a witness ...
1
vote
0answers
90 views

How to find/calculate time taken for synchronization in SQL Always on AG?

I have an SQL Always On Availability Group (AG) with 4 Servers (Replica). I'm going to add a new database (about 200GB) in the AG. I want to measure the time taken for the dB synchronization (...
1
vote
1answer
1k views

Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2

We recently upgraded from SQL 2016 SP1 build 4466 to SP2 build 5201 (CU2 GDR) on an AG. On one server an analyst was deploying SSIS using Visual Studio Enterprise 2015 (version 14.0.25431.01 Update 3 ...
1
vote
0answers
61 views

Table row count throws an exception

I have a table named backupfile, this is a copy of sys.backupfile that I am inserting into from all of the servers on in my network. I was trying to see how big the table has gotten, and when I go to ...
1
vote
0answers
346 views

Error when restore database using dialog - SQL Server 2016

I'm trying to restore backups for a DB, but every time I select the files, like this: As soon as I click on "Options" or on "Script" (not even to execute the actual restore!), I get the following ...
1
vote
0answers
75 views

Creating 'temporary' memory optimised tables with only read/write roles

I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue. The issue is related to permissions, as users of the application are only ...
1
vote
0answers
350 views

SQL Server 2016 CDC (change data capture) missed data issue

I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains ...
1
vote
0answers
434 views

How to create encryption certificate using PowerShell in Windows Server 2012 R2?

I'm implementing the Always Encrypted feature in SQL Server 2016. I have tried to create the certificate using PowerShell in Windows 10 environment as per the below article. https://docs.microsoft....
1
vote
0answers
119 views

Log Shipping Job Failed

I have a transaction log shipping configured. Job activity monitor shows that both Copy and Restore steps have failed, however, I see that backup files are in fact copied across to the secondary ...
1
vote
0answers
81 views

Adding IF Exist to truncate statement while looping through DBs

Please, I am trying to add if exists statement to the code below such that it only print truncate statements on all DBs where the table= dbo.log only exist. (SQL Server 2016) IF (EXISTS (SELECT * ...
1
vote
0answers
106 views

KPI's don't transfer on RS.exe SSRS Server Migration

I'm attempting to do a server migration with SSRS 2016 to Power BI (March 2018 release). I have been successful at getting my Data Sources, Data Sets, Folders, Reports, settings and subscriptions to ...
1
vote
0answers
112 views

sp_detach_schedule throws an error because of @delete_unused_schedule = 1, but there are jobs with that schedule

We are using SSISDB and SQL agent jobs to run them using SQL Server 2016. My developers have SQLAgentReaderRole, and are able to create their own jobs and schedules. We have a framework for triggering ...
1
vote
0answers
275 views

Is it possible to work around the need to have a sever level permission with ownership chaining or the sort?

I want to give everyone access to a very limited portion of the dynamic management views. That is, I want everyone in my database to be able to select from this view: create view dbo.KnowYourself as ...
1
vote
0answers
762 views

Unable to access an SQL Server 2016 named instance locally via sqlcmd when only using @@SERVERNAME value

I have a server with two instances of SQL Server 2016 SP1 CU7 running, one is the default instance (on a custom port, 9433) and the second is a named instance (on another custom port, 9434). I've ...
1
vote
0answers
538 views

SQL Transactional Replication - some tables frozen, but others working fine

Background We are "republishing" 20 SQL Server 2012 source databases, using SQL 2016 as the republisher and subscribers, with SQL 2017 acting as the single distributor for both "hops" (Hop 1 being ...
1
vote
0answers
50 views

SQL Server 2016 - ReportServer - sp_BlitzCache

Does it ever make sense to try to tune queries in the ReportServer database behind SSRS? Diagnostic tools such as sp_BlitzCache indicate possible issues, but I've a tendency not to futz with that ...
1
vote
0answers
569 views

How do I get MS Access 2016 to work with SQL Server 2016 where DATETIME columns are involved?

I'm working in an environment that has a lot of apps that are a MS Access 2016 front-end with a SQL Server 2016 back-end. Symptoms: When viewing data from a table where a DATETIME column is part of ...
1
vote
0answers
488 views

Trying to view database properties and getting a cannot impersonate `guest` error

One of my DBAs is trying to look at the database properties of a database by right clicking and selecting properties and is getting the following error: Cannot show requested dialog. (SqlMgmt) An ...
1
vote
0answers
124 views

bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...
1
vote
0answers
2k views

Exporting to Excel from SQL Server - BCP Using a Stored Procedure Headers not shown

I am running a Stored procedure to export data to an Excel sheet. Issue is that The column headers are being displayed at the bottom or they are missing at times. What am I missing here ? SQL Server ...
1
vote
0answers
790 views

Error while importing flat file (CSV) in SSMS 17.5

I'm not sure if this is a bug or not, but when trying to import a CSV file (with no headers and text qualified (")) using the new "Import Flat File" in SSMS, I get this error: "A column named foo ...
1
vote
0answers
976 views

CHANGETABLE query too slow

I've got a table with the following DDL: CREATE TABLE dbo.SubscriberProduct (SubscriberId INT NOT NULL , ProductId INT NOT NULL , PRIMARY KEY (SubscriberId, ProductId)); GO I've then enabled ...
1
vote
0answers
1k views

SQL Server 2012 to 2016 Performance Issue

I've recently upgraded my SQL Server 2012 to 2016 (Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit)...
1
vote
0answers
352 views

Reading decimal mark comma with an external table

SQL-server 2016, SQL server management studio (SSMS). Creating external tables with transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot? When using float and ...
1
vote
0answers
89 views

Is it possible to CREATE CERTIFICATE using certificate from the Windows Certification Store?

Additional Information With SQL Server 2016 SP1 we have the availability to use Always Encrypted functionality with the Standard Edition of the server. One is able to create a certificate in the ...
1
vote
0answers
4k views

SSIS execution timeout expired

Recently I started to get this error from time to time on my nightly SSIS jobs: Message Executed as user: "my user name". Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for ...
1
vote
0answers
403 views

HowTo: [Output File Cleanup] workaround for UNC targets

Hallengren job [Output File Cleanup] silently fails when the output file directory is a network file share. Question: Is there a better, cleaner way to resolve the issue detailed below when the job ...
1
vote
0answers
215 views

why the queries run by a specific user are slow?

Here you can see how to save the contents of the default trace into a table. I have been saving the contents of the default trance into a table in my DBA database. The table is called [DBA].[dbo].[...
1
vote
0answers
198 views

INFORMATION_SCHEMA.VIEW_TABLE_USAGE is inaccurate?

Good morning All, Trying to find dependencies on specific tables. Used SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE... The above query appeared to confirm my assumption; ...
1
vote
0answers
34 views

Availability Groups - When Does Synchronous Mode Actually Restart

Given the scenario where a connection for a synchronous replica of an AG is broken, 10 seconds (by default) elapse and the replica goes into asynchronous mode. This allows database clients to keep ...
1
vote
0answers
616 views

Inserting into an table with identity column from stored procedure works even though it shouldn't

We have this piece of (anonymyzed code) create table #SomeTmpTable ( [RowNumber] integer identity, [Value] integer, primary key (RowNumber) ) insert into #SomeTmpTable (...
1
vote
1answer
435 views

Generic log-table for CRUD operations that support both single and composite Primary Keys

I'm trying to come up with a decent table design for a table that will log CRUD operations for a small application. There won't be a lot of traffic, which could justify a single table for all events. ...
1
vote
0answers
174 views

Backup and Restore SQL Server VLDB

We are planning to configure AlwaysOn on SQL Server 2016 (Windows 2016).This is Azure Machine The DB size is around 15TB. Which is the best method to take backup from Primary and restore in Secondary ...
1
vote
0answers
290 views

Patching SSRS on Always On Availability Groups

I have SSRS 2016 installed on Always On Availability Groups. I have patched my secondary servers with SQL Server patches. When I failover SSRS will not connect to the databases, it has an error ...
1
vote
0answers
35 views

Buffer Pool in SQL Server FileStream

One of the best feature of FileStream is : The SQL Server buffer pool is not used; therefore, this memory is available for query processing. I have encountered an issue that If I create a stored ...
1
vote
0answers
127 views

SQL server 2016 SP1 CU4 issues, upgraded from SQL Server 2016 RTM CU1

Mirrored Servers (primary and secondary) running fine on SQL Server 2016 RTM CU1.. After the upgrade to SQL Server 2016 SP1 CU4 both servers OS became unresponsive after 23-25 hours, bizarrely SQL ...
1
vote
0answers
101 views

are there possible draw backs when setting 'dbo' to users who have default schema as null?

as part of a bigger operation I have been putting together this script below that basically generates a create user script for each user of the current database. When the user has a default_schema it ...
1
vote
0answers
28 views

User-defined Policy Evaluates to True when Evaluated but False when Invoked by Test-SqlAvailabilityReplica

I created a PBM condition and policy in SSMS. The policy runs a T-SQL script using the ExecuteSql function against the Server facet to determine whether all databases that are expected to be a member ...
1
vote
0answers
6k views

Fault Finding SSIS “Unexpected Termination” - SQL Server 2016

I have been getting an "Unexpected Termination" error from time to time on a bunch of SSIS jobs that I am running on a SQL Server 2016 instance (Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0....
1
vote
0answers
79 views

listing the permissions for a database object - and take into consideration it can be a synonym

I have a script that gives me all the permissions assigned to a database object: ------------------------------------------------- -- permissions to database object -- marcelo miorelli v. 20170401 ---...
1
vote
0answers
157 views

Negative values for Wait Time per Core per Sec in sp_BlitzFirst

I'm a developer in a firm that doesn't want to spend money on a DBA so i'm on my own with keeping the SQL servers healty. I checked some of Brent's videos and decided to check how hard the SQL servers ...
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
0answers
789 views

SSRS reports broke after upgrading from 2014 to 2016

One of my team members upgraded the SSRS and SQL servers from 2014 to 2016. Now, rather than doing an upgrade i.e. use existing databases - they uninstalled 2014 and did a fresh install of 2016 for ...
1
vote
0answers
531 views

SQL Server 2016 partitioning with columnstore

Environment is SQL 2016/SP1/CU1 Enterprise on Windows 2012 R2 Standard, and I'm running tests on using paritioning in combination with clustered columnstore. For one large table (300 million rows), ...
1
vote
0answers
365 views

R Services on SQL Server on Amazon RDS

Can R Services be run on a SQLServer provisioned via Amazon RDS?
1
vote
0answers
235 views

Sync data from tables in two SQL Servers

In a following app arhicecture, local C# desktop app with SQL Server Express, which need to work offline sometimes and a central web app with SQL Server Enterprise, with db per local app. I need to ...

1 2 3
4
5
9