Questions tagged [sql-server-2019]

SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
6
votes
1answer
218 views

Select permission denied when using linked server in a function unless inlining is off

We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production ...
1
vote
2answers
53 views

Provisioning SQL Server 2019

I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with. My build has to ...
0
votes
2answers
44 views

SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks. So with ...
2
votes
1answer
28 views

Synchronize 2 sql server 2019 database over intermittent connection (no replication)

I'm a PostgreSQL DBA learning SQL Server 2019. The setup : 1 Desktop, 1 Laptop Both running SQL Server 2019 Developer Windows 10 Home (nothing else, no active directory, nas, san, ...) a database in ...
0
votes
0answers
11 views

How to setup transaction in partitioned table with Always On?

I am currently creating a project that will use a table that will contain around 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second are to be inserted into ...
2
votes
1answer
44 views

how to mirror an azure storage account to a non-paired zone to enable fast file_snapshot backup/restore and database file access?

Wow, that title was a mouthful. Scenario My dev server is restoring a copy of production data daily, anonymizing it, and creating a dev-grade backup for my developers to restore locally. We're running ...
4
votes
1answer
118 views

Not able to restore a backup to SQL Server 2019

I am trying to a restore a SQL Server from a .bak file and always get the same error : The database was backed up on a server running version 15.00.2000. That version is incompatible with this server,...
0
votes
0answers
32 views

Strict in OPENJSON not work well

I've a table for save sub tasks data. some time we change status of sub tasks, and i need both of them id and status in @JsonStatus parameter. I wrote a stored procedure like below : Declare @...
6
votes
1answer
90 views

Incurs_seek_penalty column of sys.dm_os_volume_stats

A new column, incurs_seek_penalty, was introduced in the SQL Server 2019 sys.dm_os_volume_stats DMF. This tinyint column is not currently documented. Does anyone have information about the enumerated ...
1
vote
1answer
72 views

Cant install SQL Server 2019

I have a single node Windows 10 Pro machine, 64 bit, with 32GB Ram and an i7 processor. When I try to install the developer edition of Sql Server 2019, it fails installing the Database engine with a ...
0
votes
0answers
3 views

How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2?

Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe ...
0
votes
2answers
29 views

Windows Server 2019 and SQL Server Backups issue?

I have an issue with SQL Server that is really driving me nuts so I wanted to ask if anyone has any suggestions. I have a program that does a daily backup of the SQL Server database. This has worked ...
1
vote
2answers
33 views

SQL Server 2019 standard edition CDC with SSIS standard edition

The primary question is: Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise ...
1
vote
1answer
22 views

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I ...
0
votes
1answer
18 views

VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals' [duplicate]

Good day, We are currently facing an issue with SSIS deployment from Visual Studio 2019 to SQL Server 2019. Using the Visual Studio "Integration Services Deployment Wizard" to deploy even a ...
0
votes
0answers
6 views

Power BI Report Server keeps asking for credentials locally

I've installed PBIRS on my laptop to do some development. Each time try to connect it prompts with dialog wanting me to login. If I try and connect from a remote PC using the same domain login, it ...
2
votes
2answers
18 views

Incorrect results when combining full-text search and regular comparison

create table "Foo" ( "id" integer identity (1000, 1) not null, "name" nvarchar(100) not null ) alter table "Foo" add constraint "Foo_PK" primary key (&...
0
votes
2answers
20 views

SQL Server Basic Availability Group: config-only replica?

In SQL Server Basic Availability Groups docs, I found this interesting line: Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica. What is a ...
1
vote
1answer
51 views

SQL Server - Why am I getting TCP connections without sessions?

I enabled SSL with Forced Encryption enabled, using a self-signed certificate. Then I've set up automated monitoring to detect not secured connections (seems redundant, I know, but bear with me here), ...
20
votes
2answers
3k views

How do you enable SQL Server 2019's result set caching?

SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on: It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page. I ...
6
votes
3answers
920 views

SSRS error “something went wrong please try again later” in web portal

I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account ...
2
votes
0answers
85 views

Sql Server 2019 Query Hint Placement confusion

I'm using SQL Server 2019 (CU4) and I'm encountering some strange issues when trying to use a query hint for the legacy cardinality estimation due to a poorly performing Table Value Function. I'm not ...
4
votes
0answers
112 views

hash join hint returning error when using local variable

DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id The query above using Stackoverflow 2010 database with hash join ...
0
votes
1answer
19 views

What is causing BETWEEN to be replaced with <= and >=?

We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this. CONSTRAINT ...
0
votes
0answers
15 views

Primary Key and Unique Constraints in Partitioned Table

I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the ...
0
votes
2answers
25 views

Default trace vs auditing in SQL Server

Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment. I'm looking for some points to put forward ...
3
votes
3answers
230 views

Subscription Initialization throws Error “The option ”INLINE=ON“ is not valid for this function” which is incorrect

Scenario We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment. From: SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (...
2
votes
2answers
940 views

Replication problem in SQL Server 2019

I have installed SQL Server 2019 RC1 and cannot create a snapshot. I keep getting the same message when I run the snapshot agent: 2019-10-30 12:32:41.59 Microsoft (R) SQL Server Snapshot Agent 2019-...
0
votes
2answers
44 views

I/O is frozen on database DBName. No user action is required

We are on Windows Server 2019 on Azure VM, with SQL Server 2019 Enterprise CU4 installed. SQL Server differential backup jobs are failing intermittently. I have looked into the SQL Server error log ...
6
votes
4answers
174 views

SQL Server 2019 performance worse than 2012… am I missing something?

We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs....
1
vote
1answer
81 views

After upgrade to SQL Server 2019 some stored procedures fail

We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150). We have two different stored procedures that started failing after the upgrade, with error messages like this: Msg ...
3
votes
1answer
264 views

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Everyone, I cannot figure out what is missing in the below user-defined function: CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money) RETURNS VARCHAR(10) AS BEGIN IF (@Salary < 30000) ...
4
votes
0answers
95 views

Can backups done through external clients cause memory pressure on SQL Server?

Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%,...
1
vote
1answer
35 views

Which system catalog view has the create_date of a database which name was modified?

In the Stack Exchange Data Explorer each Sunday all databases are dropped and recreated one by one with this stored procedure. For each database the script starts with (paraphrased) for example Dba....
4
votes
1answer
628 views

Performance of Polybase

We've been experimenting with Polybase on a SQL Server 2019 (CU2) box, using SQL Server external data sources and the performance has not been good - in most cases a 1400% increase. In each case all ...
2
votes
1answer
46 views

How to control Segmentation min/max data_id on a non-clustered ColumnStore index

Given a simple row-based table without a PK but with a row-based clustered index like so: create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value]) Then I wish to ...
0
votes
0answers
37 views

HA AlwaysOn and SMB File Share

I am planning on taking our current single 2014 SQL server and rebuilding it to a 2019 SQL Server HA AlwaysOn Cluster. The issue is, the listener IP and hostname has to stay the same and many years ...
0
votes
1answer
57 views

SQL Server 2019 Always On using Distributed Network Name

We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When ...
0
votes
0answers
13 views

Windows Server 2019 non-owner node cannot see Cluster Name or IP

I am trying to build a SQL Server AlwaysOn Availability Group with 2 nodes in Azure. I have created 2 SQL Server VMs and Managed to create a Cluster with both Nodes as members of that cluster.  I ...
1
vote
0answers
20 views

mssql suddenly cannot access physical file

SQL server 2019 on Centos 7 x86_64. It was running fine for quite long time, then got broken. I was not doing anything special, neither update or restart or whatever. At some point the database went ...
0
votes
1answer
22 views

Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'. I have made a query that'll return the records matching and containing a passed String using ...
2
votes
2answers
59 views

dbcc checkdb runs for interminably long time, 50% of the time

I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database. The integrity check part ...
0
votes
1answer
21 views

Cost of indexed view versus filtered index?

I have met the following table design for recording references in non-normalized way: RecordGUID uniqueidentifier DestinationEntity uniqueidentifier DestinationRecordGUID ...
0
votes
0answers
53 views

SQL Polybase - Connect to MySQL ODBC - Issue with decimal data type

hope someone has a clue about this... I'm using SQL 2019 Polybase to connect to a MySQL 8 Server using an ODBC (MySQL 8.0.20 ODBC Driver). CREATE EXTERNAL DATA SOURCE eds_test1 WITH ( LOCATION = '...
1
vote
0answers
53 views

Why are column store indexes disabled automatically when a database is restored?

I have recently done a server migration where we backed up and restored all the databases from a SQL Server 2012 version to SQL Server 2019. However the restored databases appear to all have their ...
2
votes
1answer
34 views

Cannot find DReplay.exe for SQL Server 2019

I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller - Client, however I cannot find the DReplay.exe. I thought the ...
1
vote
1answer
139 views

SQL2019 CU4 PWAIT_EXTENSIBILITY_CLEANUP_TASK

I'm running SQL2019 CU4 Linux and seeing a high number of PWAIT_EXTENSIBILITY_CLEANUP_TASK. Can anyone share their insight on what this wait type means and how we can reduce it? Select * from sys....
0
votes
1answer
29 views

views restored from backup in a different server referring to linked servers

I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B. There are (many) view in X.A that uses 4-level naming in their definition: SELECT ... FROM Y.B.SCHEMA.TABLE From this I infer that X ...
3
votes
2answers
117 views

Restoring MDF without LDF file

I am trying to attach a database just from the MDF file, cause the server crashed and could never find the LDF. I am on a 2019 MS SQL Server. I am following the instructions from this page. I have ...
34
votes
1answer
2k views

SQL Server 2019 executes unreachable code

Consider the following repro example (fiddle): CREATE FUNCTION dbo.Repro (@myYear int) RETURNS datetime AS BEGIN IF @myYear <> 1990 BEGIN RETURN NULL END DECLARE @...