Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
2
votes
1answer
13 views
Can I control which algorithm SQL Server uses to hash symmetric key passphrases
I will start by describing the issue / cause before asking my question:
If you are using a symmetric key in SQL to encrypt data.
This can be set up as follows:
CREATE MASTER KEY ENCRYPTION BY ...
1
vote
1answer
14 views
Dynamically changing tables in datasource in SSRS
I have an SSRS report that I have set up to query two tables, let's call them ABC_Audit and ABC_AuditDetail. I have now added XYZ_Audit and XYZ_AuditDetail to the same database, and want to be able to ...
1
vote
1answer
33 views
Indexing for a table with many large subsets
In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is ...
0
votes
0answers
24 views
Add more partitions to partitioned table [duplicate]
How would I create more partitions to an existing partitioned table? At the moment, there's partitions set for every day for the remainder of 2018, and now I need to add more partitions for everyday ...
-1
votes
2answers
44 views
What permissions should be added to public users sql [closed]
I have a SQL Server and am looking at making it public.
What I mean by this is: I can create a DB and give access to myself and friends etc.
I need to create a login which can only view databases it ...
5
votes
1answer
74 views
Performance gain with MultiSubnetFailover for single subnet
On BOL I read following about MultiSubnetFailover=True:
The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to ...
3
votes
3answers
54 views
Why is the Read-Only Routing not functioning in this Availability Group?
First off, my apologies if this has already been answered elsewhere. I have not been able to find any articles or threads describing my exact situation, but no one's Google-fu is perfect.
With that ...
7
votes
2answers
261 views
Parameter sniffing = On with Parameterization = forced. Which takes precedence?
These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans
If you have Parameterization = forced, ...
1
vote
1answer
30 views
Storing Routes and Locations in Graph using SQL Server 2017
I'm very new to Graph database, so please ignore my mistakes. My scenario described in following picture:
I have different locations connected to each other with a route between them. Then I have ...
0
votes
1answer
27 views
How to best manage a queue of external processes
My scenario is a web app that creates jobs that need to be processes by an R script. We are currently have the web app execute a SQL stored proc that executes the R script. However, we quickly ...
0
votes
0answers
25 views
Log shipping between Azure VM and On Premise Sql server
I would like to create Logshipping between Azure VM server (SQL 2017 Developer edition) and On premise SQL server (SQL 2014 standard edition) . These are Dev servers. Because Azure and our domain is ...
-5
votes
0answers
38 views
Creating Master Slave Relationship on SQL Server 2017 (v14.x) using only SQL Server Management Studio [closed]
I have run into an issue where I have a database that I need to replicate on another server (readonly). I've never created a Master, Slave relationship. I don't have remote access to either server, is ...
1
vote
0answers
27 views
Error while trying to create a database for SSRS
I have installed SQL Server Reporting Services 2017, and my database engine is developer edition 2017. Now, while trying to configure SSRS from its Configuration Manager I get this error
I have found ...
2
votes
0answers
25 views
How can I upgrade from Developer edition to Standard SQL Server 2017
I had accidentally installed the wrong edition of SQL Server on our Live environment.
How can I upgrade from Developer edition to Standard edition?
Thank you .
3
votes
0answers
76 views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
1
vote
1answer
41 views
Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
0
votes
0answers
25 views
Getting Hash Match in query
We noticed recently that a production SQL server was creating a downstream application performance issue. We narrowed the issue down to a specific query:
SELECT ExistingSiteFlag = ' ', Invtid =...
0
votes
1answer
22 views
Oracle dbms_xplan.Display() equivalent to SQL Server 2017
I am converting some sql from Oracle to SQL Server 2017.
One of SQL code is having dbms_xplan.Display() inside the script.
I want to know what is the equivalent of dbms_xplan.Display() in SQL Server ...
0
votes
1answer
67 views
Transposing repeated rows of datetime into columns in SQL 17
I am working with a dataset of inpatient data in SQL version 17. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, ...
0
votes
0answers
27 views
many MSQL_XP waits
I've run the script from Paul Randal: please tell me where it hurts, and the top wait type (by 32 %) is MSQL_XP.
When running sp_whoisactive, I've seen calls to xp_sqljdbc_xa_commit, ...
1
vote
0answers
27 views
SQL Server 2017 Express Edition firewall install issue
Im currently trying to install this in the SQL server 2017 setup, but I get a warning from windows firewall stating: The Windows Firewall is enabled. Make sure the appropriate ports are open to enable ...
4
votes
1answer
63 views
Why is my database restore being aborted?
I'm trying to restore a SQL Server 2016 database into a SQL Server 2017 docker instance (microsoft/mssql-server-linux:latest). The process seems to be:
Create docker container: docker run -e ‘...
1
vote
1answer
35 views
Stored procedure body missing
I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure. As usual, I right-clicked it in SSMS, clicked Script stored procedure as... ...
1
vote
0answers
18 views
Unlink Windows login from database user in SQL Server contained database
I'm migrating databases from SQL Server 2012 to Server Server 2017, and switching SOME of them to partially contained. I've done SQL Server backups and restores to actually move the databases, and ...
0
votes
1answer
105 views
How to log ALL queries made by users in SQL Server 2017 Standard? [duplicate]
Is there a possibility to do this? All queries and actions performed in any database need to be logged, because of in house compliance.
I need information (and/or source) if we need MS SQL 2017 ...
7
votes
4answers
130 views
Adding a ROW_NUMBER() with no column to ORDER BY?
So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order.
Let's say my source data is:
SELECT value
FROM STRING_SPLIT('one,two,...
2
votes
0answers
48 views
Is this “idiosyncratic” method to manage constraints declarations beneficial?
I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this.
At my company, we have a large .sql file that holds all of our schema ...
6
votes
1answer
410 views
“Warnings: Operation caused residual I/O” versus key lookups
I've seen this warning in SQL Server 2017 execution plans:
Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.
...
1
vote
1answer
29 views
AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?
Please help to to research the possibility of below:
We have availability group that consists of two replicas:
replica 1: SQL Server 2017 on Windows Server 2016
replica 2: SQL Server 2017 on ...
1
vote
0answers
48 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 ...
5
votes
1answer
74 views
Managed backup large database to Azure
Got a bit of an issue here that I need some help to solve.
Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage ...
1
vote
1answer
51 views
MS SQL 2017 Batch insert performance degradation
This one's a bit strange so bear with me:
I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection ...
1
vote
1answer
30 views
Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log'
Query:
DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
WITH FORMAT;
GO
-- Truncate the log by ...
3
votes
1answer
54 views
Unable to use plan guide with sp_execute
I have an application that's querying a 10 million rows table.
The query is filtering by an integer column which is indexed. This column contains only NULLs and the query always looks for non-NULL ...
0
votes
0answers
28 views
Executing stored proc in a SQL Agent Job fails on secondaries within a High Availability group with a sys.fn_hadr_is_primary_replica guard
I have a High Availability cluster of two servers. I have the same job setup on both servers within the cluster. The job has a single TSQL step, which is executed against master. The TSQL is as the ...
16
votes
2answers
450 views
SQL Server 2017 with 500 databases - Frequent AG disconnects since CU9
Hi everyone and thanks in advance for your help.
We are experiencing challenges with SQL Server 2017 Availability Groups.
Background
Company is a retail B2B back-end software. About 500 single ...
0
votes
0answers
21 views
Sharding at SQL Server level [duplicate]
I would like to get some advice about how to implement sharding at SQL Server Level (on-premise), basically, the architecture is something as shown below:
Stand Alone.
Writer: SSIS, Linked Servers
...
0
votes
1answer
50 views
when to use a TCP dynamic port and when TCP Port?
I have a new server that is configured using a dynamic port as you can see below:
The application that connects to this server uses the following connection string, but I am getting the following ...
0
votes
1answer
163 views
Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?
We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.
I just recently received approval to start setting up new environments ...
3
votes
2answers
157 views
New SQL Server with slow Index Spool (Eager Spool) and old server used index
So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems.
I got the answer to try updating ...
1
vote
0answers
79 views
Error: CImageHelper::Init () Failed Load of symbol file with path
Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment.
Getting error:
CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL ...
2
votes
1answer
58 views
Upgrade to SQL Server 2017 Fails
Windows Server 2016 - Fully Patched
Sql Server 2016 SP2 CU3 -> Upgrade to 2017
In place upgrade fails with error message:
Wait on the Database Engine recovery handle failed. Check the SQL server ...
0
votes
1answer
28 views
Update Sql server, not user databases
I have one machine we've been fighting to do an in place upgrade of sql server from 2016 sp2 cu3 to 2017. It keeps failing, but thats another topic. My question is, is there a way I can run the ...
0
votes
1answer
84 views
Transfer SQL Server 2017 Standard license to a new server [closed]
We recently purchased a license for SQL Server 2017 Standard (16 cores) in behalf of a client from an online reseller (not directly from Microsoft), and installed it without problem: we downloaded and ...
2
votes
1answer
231 views
Is Locked Pages In Memory Still Recommend in SQL Server 2017?
I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server ...
1
vote
0answers
38 views
How to import .dbf files (with more than 256 columns) to SQL Server?
I would need once a month to import 12 .dbf files to SQL Server 2017. Files have dBase III version. They have a similar structure. They have no PK and no unique index and no combination of columns can ...
1
vote
1answer
98 views
Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? [duplicate]
In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement,
create table #localtemptable
(tempid int,tempname varchar(20) )
After creating #table,...
1
vote
1answer
21 views
Updating column in table a from top 1 in column b with foreign key relationship
I've got two tables:
Table A:
PlayerProfileId (PK),
Email,
HighScore,
Name,
UserName,
Date
1 g@s.com 37 Gustav MegaBoi 2018-09-14 09:44:07.4505232
Table B:
ScoreId (PK),
Points,
Date,
PlayerId (...
2
votes
3answers
193 views
Display one row over another if there are two
Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.
If I sort the list by ID and Site.ID the first matching result is the value we want ...
1
vote
2answers
46 views
Running remote stored procedure with DDL locally
Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.
Database: SQL Server 2017 + Azure
The goal is to build a ...
