Category: sql-server

Auto Added by WPeMatico

SQL Services are off but can access and query from other server on the same domain. Why?

Using configuration manager, I disabled the SQL Server Services for a SQL Server 2008 R2 instance. Yet, I can still connect to the instance from its replacement server, a SQL 2019 box, and query the data! Also, the jobs which I disabled are enabled and appear to have been running, i.e. last run time column. On the SQL Server 2019 environment, I am using SSMS 18.5.1.

What is going on??? Locally, I cannot connect. Is there something else that I need to disable? I’d like to understand why this is occurring.

Go to Source
Author: LeSteelBox

Installing an older CU over a Security Patch?

According to this Microsoft link, SQL Server 12.0.6372.1 requires any system with at least SQL 2014 SP3, so my predecessor installed it over SP3. Now the version is “2014 (SP3-CU-GDR) (KB4535288) – 12.0.6372.1”.

Since that security patch only fixes a specific problem with Reporting Services, and is smaller than the CU4 package, ISTM that I should also install the CU4 patch.

But how? Do I first uninstall KB4535288, then install KB4500181 (CU4) and then reinstall KB4535288, or can I just slap KB4500181 on top of KB4535288 (which seems like it would overwrite the KB4535288 files.

Go to Source
Author: RonJohn

Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key?

Background

As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, to the use of a non-clustered primary key in combination of a clustered index with a sort-able type, my research pointed me to UUIDv6, and an implementation of it.

It is able to generate UUIDs like the below (that is sequential):

UUIDv1                               UUIDv6
------------------------------------ ------------------------------------
5714f720-1268-11e7-a24b-96d95aa38c32 1e712685-714f-6720-a23a-c90103f70be6
68f820c0-1268-11e7-a24b-96d95aa38c32 1e712686-8f82-60c0-ac07-7d6641ed230d
7ada38f0-1268-11e7-a24b-96d95aa38c32 1e712687-ada3-68f0-93f8-c1ebf8e6fc8c
8cc06fd0-1268-11e7-a24b-96d95aa38c32 1e712688-cc06-6fd0-a828-671acd892c6a
9ea6a6b0-1268-11e7-a24b-96d95aa38c32 1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which I thought SQL Server would gladly sort them for me in the clustered primary key (uniqueidentifier) column.

Little did I know how SQL Server would sort an uniqueidentifier column. Here’s the ascending sort result:

UUIDv6 
------------------------------------ 
1e712688-cc06-6fd0-a828-671acd892c6a 
1e712686-8f82-60c0-ac07-7d6641ed230d 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 
1e712685-714f-6720-a23a-c90103f70be6 
1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which is causing fragmentation as if using random UUIDs. This post explains how they were actually sorted.

The real question

Luckily, the system is still in development. Which of these options should I go for next?

  1. reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be
  2. convert the UUIDv6 to binary(16) and use that instead

Problem with option 1

The UUID standard embeds a 4-bit version field inside the ID. UUIDv6 (still non-standard) also follows that rule. The way I will reorder them is going to break this.

Problem with option 2

I’m not sure. Can hardly find anyone talking about it except this, which is going against the idea. Are there other pitfalls that I should be aware of in using the binary(16) type?

Thanks!

Go to Source
Author: oopoopoop

Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?

I routinely “scrub” the parameters of my stored procedures at the top before I run the query like this:

-- Scrub params
SET @SearchText = NULLIF(@SearchText, '')
SET @ThreadId = NULLIF(@ThreadId, 0)
SET @Hashtag = NULLIF(@Hashtag, '')

But then from this article I read the following:

If the query predicate uses a local variable, consider rewriting the
query to use a parameter instead of a local variable. The value of a
local variable is not known when the Query Optimizer creates the query
execution plan. When a query uses a parameter, the Query Optimizer
uses the cardinality estimate for the first actual parameter value
that is passed to the stored procedure.

Does it count as using a local variable if the value originated from a parameter? I’m wondering if my parameter scrubbing could affect the creation of the query execution plan.

Go to Source
Author: adam0101

Prevent THREADPOOL waits due to idle worker thread trimming

After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:

tweet

The idea is that once SQL Server has created enough threads to service the peak workload, it should not then trim worker threads (release them to the OS) after 15 minutes or so of them not being needed.

The idle worker threads will continue to use resources (e.g. memory) but there will not be the burst of THREADPOOL waits when more workers are suddenly required. Apparently this can be of assistance when using Always On Availability Groups.

What is this undocumented trace flag, and how does it work?

Go to Source
Author: Paul White 9

Is this SP safe to SQL Injection?

CREATE PROCEDURE [sp_Test] (
     @param nvarchar(Max)
) AS BEGIN

DECLARE @Output nvarchar(Max) = 
N’Select ‘ + @param

Select @output
Return

Intended Use

exec sp_test ‘5’

Returns “select 5”

Malicious Use

exec sp_test ‘5; drop database’

Returns(would be safe):
“select 5; drop database“

—-OR—-

Returns(not safe):
“select 5”
…but also actually dropping the database

MS SQL Server

Go to Source
Author: Donnie

What database and cloud service should I use for an Instagram-like app?

I am a beginner in the world of databases and I’m so confused with the amount of tools available. I am building an Instagram like app in Flutter and wanted to know what database should I study for it?

And which cloud service should I use, AWS or Azure?

Go to Source
Author: Aeden Thomas

estimate Log growth in SQL Server

I would like to estimate the log growth consumption for a database with full recovery model for a particular transaction/group of transactions which is filling up my log drive. I would like to forcast this before a transaction is run.Is there a way to find this out in SQL Server(any version)

Thanks in advance.

Go to Source
Author: kevin

SQL Server Unique Constraint on two columns with an exception

Hi all and thanks for your advice.

Expense(SupplierID(Foreign Key), DocumentID(vchar))

I understand how to add a simple unique constraint on two columns. However, if DocumentID = ‘NA’, I would like to ignore the rules of the constraint.

Some suppliers in our system do not provide an invoice id, for example. Therefore, I leave the field NULL. I would like to remove all nulls for the field ‘DocumentID’ to avoid accounting for the NULLS in my client code.

I am new to SQL Server, but I could figure out how to do this using a trigger. The reason I’m asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.

Thanks!

Go to Source
Author: Tom Schreiner

Active Directory Name Change

Our Active directory will change the accounts name for example John Doe Jdoe@abcd.com to John.Doe@abcd.com.

My question is do I need to change all the SQL Logins individually or the person can login to SQL server management studio with their old names. What also happens if the login is an owner of a database or job.

Go to Source
Author: SQL_NoExpert

How to Overcome a MYSQL Temporary Write File Failure

If you’re upgrading your website and you keep getting a “Temporary write file failure” when you run a website upgrade script and you know you did everything right, what can be done inside of your PHPmyAdmin MYSQL dashboard to make the upgrade script work? I’ve enabled necessary file permissions and the upgrade script is still saying I have a MYSQL temporary write file failure.

Anyone ever experience this before and overcame it? If so, what steps did you take to overcome it?

Go to Source
Author: DNN

Differences in the queries

How do i explain the following different queries like i am teaching a 5 year old kid?

 i) SELECT suser_sname(owner_sid) as 'Owner', state_desc,name as DatabaseName, database_id FROM sys.databases
 ii) SELECT * FROM sys.server_principals
 iii) SELECT * FROM sys.sysusers
 iv) SELECT * FROM sys.sysmembers
 v) SELECT * FROM sys.databases
 vi) SELECT * FROM SYS.SYSLOGINS

Go to Source
Author: teo93

SQL Server maintenance plan DTS_W_MAXIMUMERRORCOUNTREACHED

I have an SQL server running on 11.0.5058.0.
I created a backup Maintenance Plan but I’m getting the following error:

Executed as user: MyDomaindbaAcct. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:45:20 AM Error: 2020-07-06 11:45:21.57 Code: 0xC00291EC Source: {39010E7A-E7B3-462C-A418-5AD339537AC2} Execute SQL Task Description: Failed to acquire connection “Local server connection”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2020-07-06 11:45:21.57 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2020-07-06 11:45:21.76 Code: 0xC0024104 Source: Back Up Database (Transaction Log) Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Error: 2020-07-06 11:45:21.76 Code: 0xC0024104 Source: {34EEAB12-81DB-4631-9850-2E01D2D2E0F6} Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Warning: 2020-07-06 11:45:21.77 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:45:20 AM Finished: 11:45:21 AM Elapsed: 1.437 seconds. The package execution failed. The step failed.

I raised the Maximum error Count to 10 for the subplan (Sequence) and set the TransactionOption to required.

The SQL Server and Agent services are running on MyDomaindbaAcct. I know not best practice, but trying to figure what’s going on here.

Go to Source
Author: Rick

Derive Date Spans from Start and End Dates in SQL Server table

I am using SQL Server 2016

I have a table that contains 1 row per month that a patient is assigned to a particular Provider.

A patient can be assigned to multiple providers during the year.

How can I derive date spans (startdate & enddate) to represent the time a patient was assigned to each provider.

My table looks like this:

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20191201  | 20191231 | 
| 1904176  | 12345         | 20191101  | 20191201 |
| 1904176  | 12345         | 20191001  | 20191101 |
| 1904176  | 12345         | 20190901  | 20191001 | 
| 1904176  | 12345         | 20190801  | 20190901 |
| 1904176  | 12345         | 20190701  | 20190801 |
| 1904176  | 12345         | 20190601  | 20190701 |
| 1904176  | 12345         | 20190501  | 20190601 |
| 1904176  | 12345         | 20190401  | 20190501 |
| 1904176  | 12345         | 20190301  | 20190401 |
| 1904176  | 12345         | 20190201  | 20190301 |
| 1922157  | 12345         | 20190101  | 20190201 |
| 1922157  | 56789         | 20190101  | 20190201 |
+----------+---------------+------------+-----------+

In this case, patient 12345 was assigned to 2 different providers. One for 2 months, January and then December and the other for the rest of the year (10 months) February through November. Patient 56789 was only assigned to 1 provider (1922157) for 1 month (in December).

I’m trying to make it so my output looks like the below table but I am running into issues I think because the patient is assigned to the same pcp during 2 different times of the year. I tried using the lag function but I only get the correct results for some cases but not all such as this particular case.

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20190101  | 20190201  | 
| 1904176  | 12345         | 20190201  | 20191201  | 
| 1922157  | 12345         | 20191201  | 20191231  | 
| 1922157  | 56789         | 20191201  | 20191231  |
+----------+---------------+------------+-----------+

Go to Source
Author: Juan Velez

Secondary replica database file location in Availability Group

I inherit a setup of two nodes (primary & secondary replica) Availability Group.

On my secondary replica node, the SQL server instance default files are stored at:

enter image description here

My default primary replica node files are as follow:

enter image description here

When I look at the synchronizing database’s properties on my secondary replica, I see its database files as follow

enter image description here

They are primary replica node directories. Is this how it works in Availability Group?

That is when you look at the file group of your secondary replica database, they show the paths of the primary replica database even though the data file and log file are stored at a different local directory?

Go to Source
Author: Fylix