Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
0
votes
1
answer
447
views
SQL Server user running SSIS package getting EXECUTE permissions error when they have that permission
We have a user running an SSIS package from Visual Studio.
The step that is failing is it is trying to run a stored proc but the error within Visual Studio is "The EXECUTE permission was denied ...
CommunityBot
- 1
4
votes
1
answer
14k
views
DATABASE lacks a quorum of nodes for high availability
Just after setting up successfully alwayson - availability group,
testing the failover,
I get this error message:
TITLE: Microsoft SQL Server Management Studio
Failed to resume data movement in ...
CommunityBot
- 1
0
votes
0
answers
19
views
DB stuck on synchronized/recovery pending + ALT DB SET ONLINE
Im curious to understand this scenario if there was a better plan.
Index maintenance caused the log to grow, filling the disk and causing a db to go into SYNCHRONIZED/RECOVERY PENDING.
we ran ALTER ...
- 7,484
3
votes
2
answers
2k
views
Blocking on readable secondary replica
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the ...
- 90.3k
-2
votes
1
answer
350
views
Migrate data from Express Edition to Developer Edition [closed]
How can I migrate a database from SQL Server Express Edition to a newly created instance of SQL Server Developer Edition if I do not have a backup file?
I do have the .mdf (data file) and .ldf (log ...
- 90.3k
2
votes
1
answer
3k
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 ...
CommunityBot
- 1
3
votes
1
answer
832
views
Does ordinal position of columns matter for SQL Server replication
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.
The ...
CommunityBot
- 1
1
vote
1
answer
64
views
Interleaved Execution not happening in SQL 2017 DB
We have a SQL Server 2017 database that was imported from a 2008 instance long ago. The compatibility level is set to 140 (confirmed by the execution plans), but no multi-statement TVFs are ever ...
- 90.3k
2
votes
1
answer
105
views
Is there any information in Query Store that can be used to find block leaders?
I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not ...
2
votes
1
answer
1k
views
DPM transaction log backups of SQL Server partially contained databases
I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016.
I have a named instance ...
CommunityBot
- 1
1
vote
1
answer
480
views
SQLServer Linux: Error restoring backup of DB from Windows w/ full-text data file
I'm trying to move a Windows SQL Server database from Windows 10 to Linux.
For this, I'm following the instructions in
https://learn.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-...
CommunityBot
- 1
2
votes
1
answer
546
views
SQL Agent stopped recording history (SQL Server 2017)
I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server.
On 2021-06-05 something happened on this server that altered ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Using gMSA account for SQL server reporting services
I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link to check configuration.
The requirement is to have ...
CommunityBot
- 1
1
vote
2
answers
4k
views
"New request is not allowed to start because it should come with valid transaction descriptor" using linked server
I have a simple query in a stored procedure that LEFT JOINs a local temp table with several tables across a linked server (the same linked server). This query is resulting in the following error:
...
CommunityBot
- 1
3
votes
1
answer
920
views
How to install Distributed Replay (dreplay.exe) for SQL Server 2017
In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS ...
CommunityBot
- 1
0
votes
3
answers
372
views
Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from
I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue and theDate. Now assume I have a large table, called ...
- 331
2
votes
1
answer
625
views
Handling new fields when merging in data using hashbytes?
We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the ...
CommunityBot
- 1
3
votes
1
answer
373
views
How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts
I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD.
I couln'd find any valuable information so far unfortunately. ...
CommunityBot
- 1
0
votes
1
answer
328
views
Is it possible to restore a database from corrupted .mdf file?
I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this.
So, I have received some bad ...
CommunityBot
- 1
1
vote
1
answer
7k
views
SQL Server Reporting Services 2017 Can't connect and doesn't show in Configuration Manager
I have a few question about SSRS 2017 that I can't find answers to.
Can you no longer add it to the default instance? It seems to force it to an instance called SSRS.
After doing the configuration ...
- 316
1
vote
3
answers
110
views
IS NULL Filtered indexes and column choice
some filtered indexes have been created on one of our databases that filter on [EndDATE] IS NULL, so only records with a null enddate field appear in the index, which is good.
The enddate field is ...
- 14.5k
1
vote
2
answers
2k
views
Moving Databases between Instances of different Versions of SQL Server
I found out SQL Server Developer Edition is now free.
I would like to use my older databases from SQL Server Express 2016 to a new instance of SQL Server Developer 2017.
I want all the data objects:...
- 3,243
0
votes
1
answer
473
views
Insert Into Table performance questions
I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications
Procedure will accept user-defined table-...
CommunityBot
- 1
3
votes
1
answer
4k
views
SQL Server-- Calculating Optimal Number of CPU Cores
We're soon going to rebuild the SQL Server running our production ERP. Our SAN Admin issued me the following challenge:
Assume I could give you as many Intel Xeon Gold 6240 CPU @ 2.6 GHz cores as you ...
CommunityBot
- 1
0
votes
1
answer
882
views
Is it possible to add a column to a partition in a partitioned table?
I have a large partitioned table of 16 billion plus records. I am currently updating one of the columns to NULL using the partition number. The process is cumbersome in that it takes a lot of time to ...
CommunityBot
- 1
0
votes
1
answer
466
views
OPENROWSET Bulk insert Text File NULL Columns
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
CommunityBot
- 1
0
votes
1
answer
578
views
SQL Server high SQLCONNECTIONPOOL consumption
SQL Server Version : SQL Server 2017(RTM-CU21) on Linux
Runing environment : kernel 4.19,Docker 18.09
Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04
Problem ...
CommunityBot
- 1
0
votes
1
answer
83
views
If I am a sysadmin, does it matter if I have the MASTER KEY password or not?
So currently I've faced some issues restoring databases with Symmetrical keys, Master keys, SSISDB and etc and I got curious.
We didn't have the password for anything. All that was done in the past, ...
0
votes
1
answer
70
views
SQL 2017 log reuse issue
I have a SQL 2017 Enterprise server where more and more VLFs are being consumed and none are being released. Log backups are running every 15 minutes. It is in an AG but this is working correctly (...
- 35.5k
0
votes
1
answer
41
views
Proc having Key issues after moving DB from one server to another
After moving a database from one server to another, we started having an issue:
> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the ...
- 35.5k
1
vote
1
answer
5k
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 ...
CommunityBot
- 1
2
votes
1
answer
374
views
sp_addUser through sp_execute (SQL) in master db
The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions.
use master
go
ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ...
CommunityBot
- 1
1
vote
1
answer
534
views
Filestream directory exists for a partition but the corresponding partition does not exist in the database
I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Linked Server Configuration using remote user logins
I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, ...
CommunityBot
- 1
2
votes
1
answer
2k
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 ...
CommunityBot
- 1
1
vote
1
answer
719
views
SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue
I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG.
Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
CommunityBot
- 1
1
vote
2
answers
323
views
Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists."
we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase.
The issue we have is the non-sysadmin login can't query those external ...
CommunityBot
- 1
0
votes
1
answer
612
views
How to Capture Job Exit Code?
I'm essentially trying to find a better way to determine if my job has completed successfully before moving on to another call of the same job. I have multiple jobs which run through a parsing process ...
1
vote
1
answer
660
views
DBCC Check DB fails due to operating system error 665
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically ...
CommunityBot
- 1
1
vote
1
answer
495
views
Machine login for Microsoft SQL Server for Linux
-- old question --
I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account.
Now, I want to be ...
CommunityBot
- 1
3
votes
1
answer
495
views
Solution for the deletion of old Database Backups for Linux SQL Server 2017 after Backups created with Ola Hallengren scripts?
I have a problem when I want to delete older backups, I've created with Ola Halengreen scripts.
USE Maintenance
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory ='/mssql/backup/'...
CommunityBot
- 1
0
votes
1
answer
43
views
"Avg Dist From EOL/LP Request" counter is very high
We have been facing a slowness issue on our PROD DB.
While checking a few counters on sys.dm_os_performance_counters DMV, I could see one counter ("Avg Dist From EOL/LP Request") value is ...
- 90.3k
6
votes
4
answers
10k
views
Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server
I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web ...
- 662
0
votes
1
answer
2k
views
error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when import excel data to sql server
I work on SQL server 2017 I need to import data from excel 2016 to sql server 2017
I using python script to do that
I create odbc and success test with name Testserver
path G:\ImportExportExcel have ...
CommunityBot
- 1
0
votes
0
answers
31
views
MSSQL on AWS: remote user unable to establish connection via SSMS
Newly created MSSQL 2017 SE instance, using SQL authentication. I am able to connect to it with no issues via my corporate VPN. Overseas user on VPN is unable to connect, getting this error from SSMS:
...
- 143
1
vote
1
answer
1k
views
Azure AG Listener not accessible from secondary Nodes
We are building PoC SQL 2017 Availability Group up in Azure. This is part of a longer-term plan to both provide resiliency & a path to upgrade in the Spring
I have been able to stand up the AG &...
CommunityBot
- 1
1
vote
1
answer
101
views
SQL Server Express mdf file is 50+gb
I have a SQL Server Express database running on a POS register where the mdf file is 50+gb. When I run select @@version I get:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:...
- 90.3k
0
votes
2
answers
527
views
Transparent Data Encryption doesn't appear to start encrypting the DB
I have enabled TDE on a big DB of mine (mdf file:9.6GB, log file:12.6GB).
I have followed the steps from this video (amongst other sources):
https://www.youtube.com/watch?v=6EhCeLbAS_o
It has been ...
CommunityBot
- 1
0
votes
2
answers
2k
views
Kimball Data: Modeling Data as Both Fact and Dimension with View
In Data warehousing, Kimball discusses modeling an insurance policy premium as Both Dimension and Fact.
I have the following table,
create table dbo.DimAutoInsurance
(
DimAutoInsuranceId int ...
- 101