Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
682
questions
0
votes
2answers
19 views
Multi-instance 2-node SQL Cluster - do instances need their own disks?
I currently have a single default instance of SQL2017 Standard on a 2-node cluster.
Against my preference, I need to setup a second instance on the cluster (the new database requires some users to ...
0
votes
1answer
27 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 ...
1
vote
1answer
52 views
Need advice on how to migrate from existing database mirroring to availability groups
Need some practical/general guidance on moving from database mirroring to AOAG
Current setup - 2 standalone servers with database mirroring configured as async.
Server A: Windows 2016 running SQL ...
1
vote
1answer
28 views
Where Database Alerts & Operators Exist In SQL Server?
My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
8
votes
1answer
302 views
Is it possible to restore a SQL Server Linux Backup on SQL Server Windows
I am running SQL Server in docker on a mac. I have taken a SQL Server backup from windows and restored it in the mac SQL Server docker instance. Now I want to take a backup from the SQL Server docker ...
0
votes
1answer
14 views
Basic Availability Group in SQL Server on Linux [closed]
I am trying to find a document for implementing Basic Availability Group (Automatic Failover) in SQL Server 2017 on Linux, but not able to find any.
In Microsoft Docs, some configuration only ...
0
votes
0answers
11 views
gMSA for running a sql agent job
I've had the question if it's possible to add a gMSA-account to the SQL-logins in order to let it perform a certain SQL Agent Job.
I know it's possible to add a gMSA as a login but it will probably ...
0
votes
2answers
22 views
How do you configure a 4-node AlwaysOn cluster to have two listeners?
Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/...
1
vote
1answer
26 views
Best-practice use of Resource Governor in SQL Server 2017 [closed]
I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload. The OLTP system requires sub-second (in the milliseconds) response time while the reporting ...
1
vote
0answers
32 views
SSIS 2017 - Parse flat file with multiple columns and headers on multiple lines
I receive a daily CSV file that contains 600+ company employee positions, each of which are formatted as follows:
Position,Description
SUP1015,Shipping Supervisor Day
Work UOM:,Hours,Active:,Yes,...
1
vote
1answer
31 views
Speed— Restoring from Azure Blob to local database
We're being encouraged to move our database backups to Azure blobs, which we access through the Microsoft Azure Storage Explorer. We're running SQL Server 2017.
Our backup times are fine-- 20 minutes ...
1
vote
0answers
36 views
Issue: Data synchronization state of availability database is not healthy
I have a sqlserver 2017 cluster (sql server 14.0.3026.27) with two secondary nodes set with Availability Mode = Synchronous commit. The server has 8 databases that are replicated on the secondary ...
0
votes
1answer
29 views
High Availability with Transactional Replication
We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional ...
-2
votes
1answer
27 views
Function to convert time difference between two dates into timer format
Does anyone have a function that converts date difference between two dates into timer format ?
For example:
select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35')
The result should ...
0
votes
1answer
53 views
SQL count(*) grouped by date range
I have a table as follows:
DROP TABLE IF EXISTS #PersonnelRecord;
CREATE TABLE #PersonnelRecord
(
person_id int NOT NULL,
company_id int NOT NULL,
entry_date date NOT NULL,
exit_date ...
2
votes
1answer
71 views
Why is this frequent SQL not in the SQL Server procedure cache? [closed]
I frequently query the SQL Server procedure cache (dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan) to get execution stats and execution plans.
We have a new application (with a new ...
0
votes
0answers
24 views
Transactional replication in sql server 2017 but job not running
I have created transactional replication and here is my script
Distribution script:
use master
exec sp_adddistributor @distributor = N'servername', @password = N''
GO
exec sp_adddistributiondb @...
3
votes
2answers
42 views
Find order that has deliveries in multiple weeks
I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it. Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the ...
0
votes
1answer
55 views
User info retrieval takes too long - ASP.NET Membership
I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try ...
0
votes
1answer
35 views
Distributed Always-On without Domain
We have a requirement to configure a distributed availability group from Data center1 to Data Center2 using SQL Server 2017 Enterprise Edition.
DC1 (two nodes WSFC setup): SQLDB1, SQLDB2
DC2 (two ...
5
votes
1answer
106 views
SUM(Amount) different value then sum of lines - SQL Server 2017 (14.0.3045.24) [closed]
I am using following Query to get the SUM of field Amount of all lines in my filter:
SELECT SUM(Amount)
FROM dbo.[CompanyName$Detailed Cust_ Ledg_ Entry]
WHERE [Customer No_] = 'XYZ'
The result is
...
2
votes
0answers
32 views
Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?
I want to add a computed field that extracts a value from another xml column.
The function looks like this:
create or alter function extractValue(@val xml) returns varchar(14)
as
begin
return @val....
0
votes
0answers
4 views
In AWS how to migrate “Microsoft Windows Server 2016” instance to “Microsoft Windows Server 2016 with SQL Server 2017 Standard” AMI
Currently I have "Microsoft Windows Server 2016" instance running in AWS where I have installed evaluation version of SQL server 2017. Now I want to move this server along with SQL database to "...
0
votes
0answers
21 views
Safely unistall SQL 2014 without impacting SQL 2017 install [duplicate]
As the title says, we have two instances of SQL installed on a server - SQL 2014 and SQL 2017 with Server 2019 as the OS. I attempted to perform an upgrade of 2014 to 2017 several months ago, but we ...
3
votes
1answer
65 views
Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?
The official Microsoft documentation for Always Encrypted on SQL Server 2017 states:
Deterministic encryption always generates the same encrypted value for any given plain text value.
Using ...
0
votes
0answers
6 views
SQL Server 2017 on RHEL Linux with IDM for AD authentication
We are using Red Hat IDM to authenticate AD users to SQL Server. Users can login successfully to SQL Server with SSMS and SQL CMD with AD Authentication. But running some elevated commands from SSMS ...
0
votes
1answer
100 views
SQL Server 2017 password complexity rule
SQL and Windows version: Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) Jan 23 2020 21:00:04 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows ...
0
votes
1answer
56 views
MS SQL - CLR Stored Procedure runs as deleted user
I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP.
My user is connected and works ...
1
vote
0answers
55 views
How to hide SQL Server connection string and SQL login password from Developers
We are using Microsoft Azure App Services to host some of our Web Apps.
Web Apps connect to on-premises SQL Server over hybrid connections.
When new Web App needs access to the on-prem SQL Server, a ...
1
vote
0answers
21 views
Error 17807, Severity: 020 - error source identification
I have setup an alert to notify me on high severity errors. Irregularly, from time to time an error occurs:
"Error 17807, Severity: 020, Description: Event '31', which was received from the client, ...
8
votes
1answer
147 views
Improve the poor performance of the sys.dm_db_stats_properties DMV
We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
1
vote
1answer
17 views
ALTER USER statement modifies users on off-air node
I had previously asked a question about Orphaned Users here and was directed to this link in an effort to fix it, but this didn't seem to answer my questions, so I am re-asking after some further ...
0
votes
0answers
31 views
MSSQL Express: linked server from Access database on network path
I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.
I have trouble to set the appropriate network UNC path and other settings for the linked server.
My ...
1
vote
1answer
11 views
MS Access linked table displays short text instead of Decimal data type
I have linked tables in MS Access databases (various versions, 2007-2019) which are connected to SQL server 2017 via Sql server 2017 drivers (eg, msodbcsql_17.2.0.1_x64). Fields that are of data type ...
1
vote
1answer
34 views
Column types analysis of columns used in joins
Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?
I inherited a database that hasn'...
0
votes
0answers
22 views
How to force SQL Server to write partial query set to table during select
I'm trying to run a select query to extract a data set from a linked server, to be stored on SQL server.
It seems that SQL server is storing the rows of the partial data set in memory and writing it ...
1
vote
0answers
22 views
DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always
Schema Summary
A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed.
This looks like ...
0
votes
0answers
20 views
SSIS - Setup OLEDB Connection to SQL Server Always On Listener
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).
I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
3
votes
1answer
52 views
Why are queries getting dropped from query store?
I see sometimes that queries are dropped from the query store. But I don't know why. The query store disk-space is not full and the query is far from the stale query treshold.
What I experience is ...
0
votes
1answer
57 views
SQL Server:Piecemeal Restore on Standard Edition 2017/2019?
I am currently fiddling with piecemeal restore.
I have some demo code from Jes Borland from her great presentation "Minimize Data Loss with Advanced Restore Methods" (take a look at https://www....
3
votes
0answers
73 views
Turn off Error AND Usage Reporting SQL Server
Unfortunately, SQL Server usage internet bandwidth and us activity in SQL, for R&D. You can find in your server, this service is called: SQL Server error and usage.
This service is active without ...
1
vote
1answer
41 views
Lock addresses and page addresses don't match
Today, I discovered the command DBCC PAGE(). I understand that it displays information about the data stored at the page level.
(This question is about SQL Server 2017 - v14.0)
So this is what I do :...
4
votes
2answers
423 views
Running out of disk space, start saving new data to different disk
I'm currently at around 7 billion rows on one table and approaching the max disk space for the entire database.
I need to start saving new data on the one large table to a new disk.
I've looked at ...
0
votes
2answers
40 views
Datediff SECOND with DATETIME type and rounding returns questionable results
I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017).
The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie ...
1
vote
0answers
41 views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
0
votes
0answers
17 views
Fixed Orphaned Users on 2nd Node, but 1st Node Users Permissions Changed [duplicate]
For context: My environment consists of two Windows Server '19 boxes with SQL 2017 running in an AlwaysOn High Availability & WSFC configuration.
I have run into an issue where several login ...
0
votes
1answer
94 views
SQL Server - Add signature to stored procedure by certificate does not work
I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2
To avoid granting calling login/user permissions to do table updates, I try to use code signing
...
2
votes
0answers
85 views
Why is enabling db_chaining bad from a security perspective? [closed]
I have two databases, Database1 & Database2 and two applications that access both databases using an account called AppUser. There are hundreds of views in Database1 that access tables in ...
0
votes
1answer
55 views
Trigger on indexed view
I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level.
This is the plan that a normal insert generates (note the clustered index ...
-2
votes
1answer
50 views
Can't create availability group , big error message
hello I am setting up my "high availability group" and I have a little problem, I have this error message .
maybe I have to validate my cluster first? I already tried to validate but I also receive ...
