Questions tagged [sql-server-2016]
SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.
2,514
questions
0
votes
1answer
71 views
Is it always safe to use WITH SCHEMABINDING in a UDF?
SO I have been reading about WITH SCHEMABINDING and how it can improve the performance of queries using a scalar UDF by omitting the table spool operator from the execution plan. I think I understand ...
0
votes
0answers
47 views
Master data services(MDS) upgrade from SQL2016 to SQL2019
Team,
I have not worked on MDS before and need some advice/inputs.
We are doing an doing an in-place upgrade of MDS (master data services) from SQL2016 enterprise to SQL2019 enterprise. i would like ...
10
votes
2answers
3k views
OLE DB provider "MSOLEDBSQL" with SQL Server not supported?
I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using ...
0
votes
0answers
220 views
multisubnetfailover=yes and registerallipprovider = 0
I have a multi-subnet availability group. The application connection string specifies
multiSubnetFailover=Yes
And availability group listener network has parameter set
RegisterAllProvidersIP=0
...
0
votes
1answer
145 views
SQL Server Command Line Install - How to change the edition?
I am creating a script to install SQL Server via the command line.
I cannot see a way to choose the edition I want to install. The GUI on my install media offers Evaluation, Developer or Express:
(...
1
vote
1answer
38 views
Upgrading SQL 2016 AGs to SQL 2019 question
I am doing a rolling upgrade of my production SQL 2016 Servers to SQL 2019. I have availability groups. My current structure is 3 DR servers (secondaries), 3 HQ (on prem) secondaries and 3 HQ ...
0
votes
1answer
44 views
SQL Server 2016 compress function details - external decompression
According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox - you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find ...
2
votes
1answer
810 views
Issue patching SQL Server 2016 to SP2 CU 15
Trying to update all our SQL 2016 servers to SP2 CU 15, but am running into:
The version of SQL Server instance MSSQLSERVER does not match the version expected
by the SQL Server update. The installed ...
-1
votes
1answer
73 views
High PREEMPTIVE_OS Wait Stats [duplicate]
How to reduce PREEMPTIVE_OS_AUTHENTICATIONOPS wait type stats in SQL Server, any advice on it would be greatly appreciated. Thanks
0
votes
0answers
176 views
How to delete specific range of data from Service Broker > System Queues data table?
Due to sudden increase of my database .mdf file we found there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table.
we try this ...
0
votes
1answer
137 views
Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]
I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task.
I checked the scheduled tasks and we had ...
0
votes
1answer
42 views
SQL Server second transaction log seems to be used as well - not only after first one is full
we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance,...
0
votes
1answer
125 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 ...
0
votes
0answers
11 views
SSIS Dataflow package works with Native Client but rejects data with OLE Db connection
I've been trying to improve an SSIS ETL package to see if I could get better throughput.
I was trying to change the buffer settings as per this link, and thought i'd try using a newer driver/provider.
...
0
votes
1answer
27 views
Stored Procedure Signed by Certificate Unable to Add Members to Database Roles
I created a Stored Procedure that performs the Following tasks:
Creates database
Change owner to sa
Set recovery model
Creates users in database
Adds users to database roles.
Grants execute and view ...
0
votes
1answer
586 views
Passing a variable in OPENJSON WITH statement in SQL Server
Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason ...
4
votes
1answer
446 views
SQL Server with French_CI_AS Collation date conversion
I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++.
...
0
votes
1answer
38 views
Replication of only updates and inserts
I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports.
I know how to do a ...
1
vote
1answer
191 views
SQLServer Query Store not saving failed queries run by SP_ExecuteSQL
We are on SQL Server 2016
Query Store is activated on the database in question.
We have had some timeout problems reported on a nightly batchjob, and we have located the query in question.
The query ...
2
votes
1answer
38 views
Why when I use variables on SQL the Database Engine avoid the indexes?
I'm improving our SSIS process passing the variables through a declare and I found this:
Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following ...
-1
votes
1answer
66 views
Get records where no column is null [duplicate]
I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null.
id|f001|f002|f003|....f100
1 |null|aa | ss |sada
2 |sdad|aa | ss |sada
3 |as |aa | ss |...
0
votes
2answers
89 views
Analyse differential backup to see why it is so big
We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest.
1 or 2 days after a full backup was taken, the diff backup is bigger ...
0
votes
1answer
27 views
Join to get most specific record for tenant without PIVOT
I have a Settings table and a Tenant table.
There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'.
Account 1
---> Company 1
...
1
vote
1answer
128 views
Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?
The Backstory:
I have a table with the following structure:
CREATE TABLE WideTable1
(
BoringColumn1,
BoringColumn2,
CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
1
vote
0answers
95 views
SQL server 2016 (13.0.5026.0) Standard. High CPU ussage even without user queries
So SQL server CPU ussage is around 20% even when no users are making queries
I query the sysprocesses, and i can see some internal spid's:
select * from master.dbo.sysprocesses order by cpu desc
Any ...
-1
votes
2answers
30 views
Concatenated string gets truncated
I have a query that i am writing to convert some heap tables with primary keys into clustered tables, but the string that i'm generating truncates on objects past a certain length it seems.
SELECT
...
0
votes
0answers
25 views
Did user connect via AG Listener or directly to the server? [duplicate]
How do I tell whether a SPID connected via the AG Listener name or the server name (or its IP address)?
0
votes
2answers
80 views
Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?
I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
0
votes
1answer
51 views
Allow entering application while in transaction
We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails...
Simple we have something similar to:
BEGIN TRY
BEGIN TRANSACTION;
...
0
votes
2answers
55 views
Unique-like constraint in a complex scenario
I have a table in SQL Server 2016, as shown below. Records are inserted into the table each quarter, for all partners.
Constraint Needed: Should allow only one BatchID for a partnerID for a year-...
1
vote
2answers
62 views
Halting a multi-step Agent job without resorting to "Quit the job reporting success"?
I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures.
How can I tell Agent to successfully exit the whole job?...
0
votes
3answers
96 views
Import daily csv with varying available headers
I receive a csv daily from an external company capturing feedback for us.
Unhelpfully the csv headings vary. There could be any combination of headers.
For example:
day1: heading1, heading2, heading3, ...
1
vote
2answers
95 views
Availability Group Listener and old client software
We've got a multisegment AG Listener named SQLAGL01 which points to these two addresses:
10.143.162.32 (the primary server)
10.140.165.32 (DR server)
Because the 3rd party (which means we can't fix it,...
0
votes
0answers
1k views
RDLC report not working after upgrading from Visual Studio 2008 to Visual Studio 2019 with SQL Server 2016
After RDLC report updated with Visual Studio 2019 is not working with SQL Server 2016:
Microsoft.Reporting.WinForms.LocalProcessingException
HResult=0x80131500
Message=An error occurred during ...
0
votes
1answer
335 views
Is it possible to specify a different collation for the data vs the entities within a single database?
I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used.
The entities and their column names within that ...
3
votes
1answer
170 views
Understanding DBCC TRACEON and TRACEOFF Globally and Session-wise
For those of you that keep track of what I discuss in The Heap™ you might have been following my endeavours whilst migrating a database from SQL Server 2008 R2 to SQL Server 2016 and battling with ...
0
votes
1answer
67 views
Query optimizer triggering statistics update seems to be slowing down queries
We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
1
vote
2answers
168 views
Avoid downtime of Microsoft SQL Server during restarts
We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted.
We have been asked to look into keeping the database available ...
1
vote
0answers
106 views
High CPU on Unloaded Server -- Performance Monitor
We recently upgraded a very lightly used SQL server (running < 5% CPU 99% of the time) from 2012 to 2016. In conjunction, we moved the server from an AWS t2 to a t3 instance and installed the new ...
0
votes
2answers
784 views
Splitting a column on underscores [duplicate]
What SQL syntax would split this column into three new columns (split would be on the underscore)?
There are other columns in the table, but I would like to split these into three (underscore based).
2
votes
1answer
517 views
What Microsoft DBA certificate should I acquire?
I am thinking about getting Microsoft DBA certification, but trying to find out which exactly certificate should I study for gets a little confusing.
My company uses on premises SQL Server 2017 but ...
1
vote
1answer
28 views
SQL SERVER 2016, Restore database get 15517
I got "15517" error When I tried to restore database when the process reached 100%
I googled several hours,the recommended solution is to change the database owner , I did so
ALTER ...
-1
votes
2answers
75 views
TSQL - Sql Injection
DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One''';
CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));
INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL ;
Is it safe to have input ...
3
votes
1answer
139 views
What Method / Formula does a Nested Loops Operator use for row estimation?
The following, simple query in AdventureWorks:
SELECT *
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
Gives the following ...
1
vote
1answer
212 views
How to fix SQL Agent Job using Windows Security after password change
I have some sql agent jobs that are using Windows NT Integrated Security to run (as specified in the Maintenance Plan's Manage Connections window)...eg. Translog backups.
While logged in as this ...
0
votes
1answer
122 views
SQL Server storage - evaluate the number and types of read write operations for the existing deployment
Training for SQL Server, one of the questions in Learning material says:
Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of ...
0
votes
1answer
410 views
Generate random row order in a subquery
I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the ...
0
votes
1answer
71 views
What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist?
I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly).
I would like create a new ...
1
vote
2answers
31 views
in-place upgrade native SSRS without the SQL Server portion
We have a single server (2012R2) running SQL 2008R2 + reporting services native mode 2008R2
Can the reporting services portion be upgraded alone without the database engine?
Or can the SSRS db be ...
5
votes
1answer
290 views
DATEADD and DATEPART with milliseconds yields strange results
I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding ...
