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

How to check Data replicated time on subscriber server in merge replication?

I have 10 servers and all are in merge replication. when i insert data in one of all then data replicated on all servers after some time. i just want to know the time when data is replicated on any specific server. please can anyone help me?

Go to Source
Author: H.Awais

SQL Server 2016 Replication Error while Configuring Diistributor

I am trying to configure Distributor for transactional replication set up on SQL 2016 Enterprise Edition server. But I get the following error:

SQL Server could not configure ‘Server01’ as a Distributor.

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server principal ‘distributor_admin’ already exists.
Changed database context to ‘master’. (Microsoft SQL Server, Error: 15025)

Any help/direction to resolve this error would be highly appreciated.

Go to Source
Author: SQLQuest

Can I use row-level-security for application users?

For my webservice I use a PostgreSQL database which supports row-level-security to prevent users accessing rows of a table they are not supposed to read/access. I would like to use this functionality to prevent a user accessing data from another user (either due to a bug, altered webservice request, SQL injection, etc).

I understand the purpose is actually meant for a database user, but I am wondering if it is wise or common to create a shadow-user in the database for every “application” user as well. (the database is of course not directly accessed and rather through a WSGI, therefore the shadow user)

FYI: This is techically a XY-problem. Means, my actual intention is to prevent that a user requested e.g. his profile and due to an accident or corner case accidentally data from another profile gets send back. Row-Level access looked promising

Go to Source
Author: Daniel Stephens

Can I simplify this MERGE statement w.r.t. WHEN MATCHED conditions?

I’m using SQL Server (SQL Server 2016 and Azure SQL) and I have this MERGE statement which uses a rather gnarly WHEN MATCHED condition to only update rows where values are actually different.

This is done for two reasons:

  1. The table has a rowversion column that will change when an UPDATE operation is performed, even if all of the values are the same. The rowversion values are useful for reducing client-activity (the application uses rowversion for optimistic concurrency).
  2. The table is also a Temporal Table, and SQL Server’s implementation of Temporal Tables will add copies of live data to the History table whenever an UPDATE is performed, even if no values are actually changed.
CREATE PROCEDURE UpsertItems
    @groupId int,
    @items   dbo.ItemsList READONLY -- This is a table-valued parameter. The UDT Table-Type has the same design as the `dbo.Items` table.

WITH existing AS    -- Using a CTE as the MERGE target to allow *safe* use of `WHEN NOT MATCHED BY SOURCE THEN DELETE` and apparently it's good for performance.
(
    SELECT
        groupId,
        itemId,
        a,
        b,
        c,
        d,
        e,
        f,
        -- etc
    FROM
        dbo.Items
    WHERE
        groupId = @groupId
)
MERGE INTO existing WITH (HOLDLOCK) AS tgt
USING
    @items AS src ON tgt.itemId = src.itemId
WHEN MATCHED AND
(
    -- This part is painful, but unfortunately these are all NULLable columns so they need the full `x IS DISTINCT FROM y`-equivalent comparison:

    ( ( tgt.a <> src.a OR tgt.a IS NULL OR src.a IS NULL ) AND NOT ( tgt.a IS NULL AND src.a IS NULL ) )
    OR
    ( ( tgt.b <> src.b OR tgt.b IS NULL OR src.b IS NULL ) AND NOT ( tgt.b IS NULL AND src.b IS NULL ) )
    OR
    ( ( tgt.c <> src.c OR tgt.c IS NULL OR src.c IS NULL ) AND NOT ( tgt.c IS NULL AND src.c IS NULL ) )
    OR
    ( ( tgt.d <> src.d OR tgt.d IS NULL OR src.d IS NULL ) AND NOT ( tgt.d IS NULL AND src.d IS NULL ) )
    OR
    ( ( tgt.e <> src.e OR tgt.e IS NULL OR src.e IS NULL ) AND NOT ( tgt.e IS NULL AND src.e IS NULL ) )
    OR
    ( ( tgt.f <> src.f OR tgt.f IS NULL OR src.f IS NULL ) AND NOT ( tgt.f IS NULL AND src.f IS NULL ) )
    -- etc
)
THEN UPDATE SET
    tgt.a = src.a,
    tgt.b = src.b,
    tgt.c = src.c,
    tgt.d = src.d,
    tgt.e = src.e,
    tgt.f = src.f,
    -- etc
WHEN NOT MATCHED BY TARGET THEN INSERT (
    groupId,
    itemId,
    a,
    b,
    c,
    d,
    e,
    f,
    -- etc
)
VALUES (
    src.groupId,
    src.itemId,
    src.a,
    src.b,
    src.c,
    src.d,
    src.e,
    src.f,
    -- etc
)
WHEN NOT MATCHED BY SOURCE THEN DELETE

OUTPUT
    $action AS [Action],

    inserted.groupId AS Ins_groupId,
    deleted .groupId AS Del_groupId,
    inserted.itemId  AS Ins_itemId,
    deleted .itemId  AS Del_itemId,
    inserted.a       AS Ins_a,
    deleted .a       AS Del_a,
    inserted.b       AS Ins_b,
    deleted .b       AS Del_b,
    inserted.c       AS Ins_c,
    deleted .c       AS Del_c,
    inserted.d       AS Ins_d,
    deleted .d       AS Del_d,
    inserted.e       AS Ins_e,
    deleted .e       AS Del_e,
    inserted.f       AS Ins_f,
    deleted .f       AS Del_f,
    -- etc
;

As you can see, this is quit the pain to maintain!

I already use tools like T4 to automate generating the repetitive parts of this query, but the sheer… scale and pain of this MERGE statement makes me feel like I’m doing something very wrong (because software is meant to light-the-way via the Pit of Success, so if one is encountering difficulties trying to do the right thing you’re probably doing it wrong), but I can’t think or see a better way of accomplishing this (BULK INSERT notwithstanding, but for the purposes of this question that’s not a possibility).

I know this statement can be simplified in other RDBMS which support x IS DISTINCT FROM y (which replaces the horrible-but-necessary NULL-safe checks in the WHEN MATCHED AND, but SQL Server still doesn’t support it.

Another pain is the lack of DRY in SQL in general – and the difficulty of implementing a DRY database in SQL Server (e.g. there’s no support for Deferred Constraints or Table Inheritance so you can’t implement the Subclass Table Pattern, which means needless repetition of data-design in multiple tables and weaker constraints) – but that’s another topic. I’m just frustrated with how backward SQL programming seems today compared to the many time-saving and keypress-saving features in modern languages like Kotlin and TypeScript.

Fantasy time:

I’d love to be able to do something like this, and not have to do with any gotchas (like how MERGE is unsafe by default without an explicit HOLDLOCK – that’s crazy!):

MERGE INTO
    dbo.Items AS tgt
WHERE
    tgt.groupId = @groupId
FROM
    @items AS src
ON
    tgt.itemId = src.itemId
WHEN MATCHED AND DIFFERENT THEN UPDATE ( automap )
WHEN NOT MATCHED BY TARGET THEN INSERT ( automap )
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT ALL;

(Where automap would be an amazing feature in SQL Server that automatically maps source and destination columns by name and throws a compile-time error if it cannot automatically map columns to each other), and OUTPUT ALL would output all $action, inserted, and deleted values with different column names – using the same column names but with inserted and deleted values in adjacent rows).

Go to Source
Author: Dai

Best approach for extending a foreign relationship for an existing table

I’m not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don’t uniquely identify a row:

+--------+------+------+------+------+
| RateId | col1 | col2 | col3 | Rate |
+--------+------+------+------+------+

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+
| MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId |
+------------+------+------+------+----------------+------------+

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+
| RateId | MaterialTypeId | UnitTypeId |
+--------+----------------+------------+

Is that the correct approach?

Go to Source
Author: David Clarke

Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it’s setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

enter image description here

Versus

enter image description here

Go to Source
Author: TugboatCaptain

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 CONTAINS.

Select TOP 5 * from ProductCodes
WHERE CONTAINS(Value,'"pepper*"')

It does return the records contains ‘Pepper’ e.g. Pepper and Bayleaf, Peppermint, Dr. Pepper etc.
I also have an exact record that only contains the exact string, ‘Pepper’ which is displayed in the mid of the results. I only need to select the TOP 5 of the records and also the exacting match of passed string.

I also tried to use the CONTAINSTABLE

SELECT Value, RANK FROM
ProductCodes PC
INNER JOIN
CONTAINSTABLE(ProductCodes,Value,N'pepper') AS KEY_TBL
ON PC.Value = KEY_TBL.[KEY]
ORDER BY RANK DESC

I tried using a WHERE rank = 1000 which I read somewhere that’s the rank of the closest match but as I figured out the rank of the record ‘Pepper’ is 128 and phrases that have more than 1 word match have a higher rank.

I am fairly new to using FREETEXT and Semantics so I am still learning on it. How can I take the exact match and ‘partial’ match in my TOP 5? TIA

edit:

I have added a UNION ALL query where it first selects the equal record and combining it with my CONTAINS query.

SELECT TOP 5 * FROM (
 SELECT TOP 5 * From ProductCodes 
 WHERE Value = 'pepper'
 UNION ALL 
 Select TOP 5 * from ProductCodes
 WHERE CONTAINS(Value,'"pepper*"')
) as Value

Now, I can get the exacting match and partial match but wonder how it’ll make my query slower.

Go to Source
Author: bish-cuit

My question is in a select statement how do you return the RID along with the table data?

I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from the table. In the event that a developer does not use a clustered index on a table I want to be able to show them the RID (Row Identification number) SQL Server uses to find the record. So my question is in a select statement how do you return the RID?
I know that you can use the following to find where SQL Server stores the data in the data file.

SELECT TOP(100) sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot], *
FROM  [dbo].[Badges] ;
SELECT   TOP(100) *
FROM   [dbo].[Badges]
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%);

My question is in a select statement how do you return the RID along with the table data?

Go to Source
Author: Andy Eggers