Xray presents that error message when its needed Jira custom fields aren't setup.

The vendor has a knowledge base article that addresses this: https://confluence.xpand-it.com/display/ProductKB/%5BXray+Server%5D+Xray+Custom+Fields+Not+Configured

Hope this helps!

There are procedures that run fine manually https://dba.stackexchange.com/a/276427/22336 , or fails when run https://dba.stackexchange.com/a/170620/22336 , or https://dba.stackexchange.com/q/311672/22336

Mine works in all sessions but one.

this is the code I am running - it calls a stored procedure that gets the trigger definition(s) and save it in a temp table.

works fine and it is part of my automation work.

            IF OBJECT_ID('tempdb.dbo.#Jagannatha_sp_getTriggerDef') IS NOT NULL 
            DROP TABLE #Jagannatha_sp_getTriggerDef 
        CREATE TABLE #Jagannatha_sp_getTriggerDef ( 
        DB sysname not null,
        parent_name nvarchar(600) not null,
        object_id int not null,
        trigger_name sysname not null,
        is_disabled bit,
        i int not null,
        [trigger_definition] NVARCHAR(MAX) not null,
        primary key clustered (DB,trigger_name,i))

        truncate table #Jagannatha_sp_getTriggerDef 

        
        exec sp_getTriggerDef @dbname      = 'APCore', 
                              @TableName   = 'dbo.receivedLog',
                              @Drop_ONly   = 0,
                              @Radhe       = '#Jagannatha_sp_getTriggerDef'


SELECT *
      FROM #Jagannatha_sp_getTriggerDef
      order by db,i

I run it on a table that has no triggers - just to make it as simple as possible

it comes out with the warning

IF 'my_server\_DEVELOPMENT' <> @@ServerName THROW 50001, 'Wrong Server!!!',1 

all good.

but then on this particular session:

and there is nothing different in this session, that I could spot so far.

SELECT *
  FROM sys.dm_exec_sessions 
  where login_name = 'my_company\my_user'
    and  session_id = @@SPID

the second one is where it fails. all others it works fine.

what can I do to find out what is different? or even better, change the procedure so that it would work despite the difference?

Would this do? Read comments within code.

Sample data:

SQL> with cte as (
  2  select 'LINESTRING ( 1.0 2.0, 3 4)'                               as txt from dual union all
  3  select 'LINESTRING M ( 1 2 3, 4 5 6.0)'                           as txt from dual union all
  4  select 'LINESTRING ( 1 2, 3 4, 5 6)'                              as txt from dual union all
  5  select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)'               as txt from dual union all
  6  select 'LINESTRING M ( 1 2 3.0, 4 5 6)'                           as txt from dual union all
  7  select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual
  8  ),

Query begins here:

  9  temp as
 10    -- LINE is the 1st part of the string ("letters")
 11    -- SUB is the 2nd part of the string (coordinates)
 12    (select
 13      txt,
 14      substr(txt, 1, instr(txt, '(') - 1) line,
 15      substr(txt, instr(txt, '(')) sub
 16     from cte
 17    ),
 18  temp2 as
 19    -- split coordinates into rows; they are separated by a comma
 20    (select txt,
 21       sub,
 22       line,
 23       column_value as rn,
 24       regexp_substr(sub, '[^,]+', 1, column_value) val
 25     from temp cross join
 26       table(cast(multiset(select level from dual
 27                           connect by level 

Result:

TXT                                                      RESULT
-------------------------------------------------------- ----------------------------------------------------
LINESTRING ( 1.0 2.0, 3 4)                               LINESTRING ( 1.0 2.0, 3 4)
LINESTRING ( 1 2, 3 4, 5 6)                              LINESTRING ( 1 2, 3 4, 5 6)
LINESTRING M ( 1 2 3, 4 5 6.0)                           LINESTRING M ( 1 2 , 4 5 )
LINESTRING M ( 1 2 3.0, 4 5 6)                           LINESTRING M ( 1 2 , 4 5 )
LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)               LINESTRING M ( 1 2 , 4 5 , 7 8.00 )
MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0)) MULTILINESTRING M (( 1 2 , 4 5 ),( 7 8 , 10 11 ))
6 rows selected.
SQL>

SQL Server doesn't let you ALTER CONSTRAINT to alter an existing constraint to cascade on delete. However, SQL Server will happily let you create identical Foreign Key constraints. So rather than having to mess with your existing FK constraints, you can simply create some new ones, which will help you do your cleanup, then you can drop those special-purpose constraints when you're done.

Example data

Consider this very simple schema:

CREATE DATABASE [CascadeOnDelete];
GO
USE [CascadeOnDelete];
CREATE TABLE dbo.Customers (

CustomerID int,

CustomerName nvarchar(100),

CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID)

);

INSERT dbo.Customers (CustomerID, CustomerName)

VALUES (1,'Grandpa Joe'),(2,'Grandma Josephine'),

(3,'Grandpa George'),(4,'Grandma Gerorgina'),

(5,'Charlie Bucket');
CREATE TABLE dbo.Orders(

OrderID int,

CustomerID int

CONSTRAINT FK_Orders_CustomerID

REFERENCES Customers(CustomerID),

OrderDetails nvarchar(100),

CONSTRAINT PK_Parents PRIMARY KEY CLUSTERED (OrderID)

);

INSERT dbo.Orders (OrderID, CustomerID, OrderDetails)

VALUES (1,1,'Wonka bar'),

(2,5,'Wonka bar'),

(3,5,'Wonka Scrumdiddlyumptious Bar');
SELECT * FROM Customers;

SELECT * FROM Orders;

If you try to delete Grampa Joe, you'll get an error because Grampa Joe has an order:

DELETE c
FROM dbo.Customers AS c
WHERE CustomerID = 1;
/*

Msg 547, Level 16, State 0, Line 33

The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_CustomerID". The conflict occurred in database "CascadeOnDelete", table "dbo.Orders", column 'CustomerID'.

*/

Now that we have that set up, we can script out the rest of what we need:

The solution

First, we can use some dynamic SQL to generate the script for every FK in your database. I'm appending the names with _CASCADEDELETES to identify them. I'm also creating these WITH NOCHECK and ON DELETE CASCADE regardless of the original FK definition. You would take the output of this script, review it, and run it to duplicate every FK in your database with these additions:

--adapted from https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/
SELECT N'ALTER TABLE ' 
       + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + N' WITH NOCHECK'
       + N'    ADD CONSTRAINT ' + QUOTENAME(fk.name + N'_CASCADEDELETES') 
       + N'    FOREIGN KEY (' 
            + STUFF((SELECT ',' + QUOTENAME(c.name)
                    FROM sys.columns AS c 
                    JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.parent_column_id = c.column_id
                        AND fkc.parent_object_id = c.object_id
                    WHERE fkc.constraint_object_id = fk.object_id
                    ORDER BY fkc.constraint_column_id 
                    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
        + N') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
        + N'(' 
            + STUFF((SELECT ',' + QUOTENAME(c.name)
                     FROM sys.columns AS c 
                     JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.referenced_column_id = c.column_id
                        AND fkc.referenced_object_id = c.[object_id]
                    WHERE fkc.constraint_object_id = fk.[object_id]
                    ORDER BY fkc.constraint_column_id 
                    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') 
        + N')'
        + N' ON DELETE CASCADE;'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

In our example database, we only have one FK, so the output produces a new FK for that one FK:

ALTER TABLE [dbo].[Orders] WITH NOCHECK
    ADD CONSTRAINT [FK_Orders_CustomerID_CASCADEDELETES] FOREIGN KEY ([CustomerID]) 
        REFERENCES [dbo].[Customers]([CustomerID]) 
        ON DELETE CASCADE;

After creating that FK, we can simply run our original delete again, to delete Grampa Joe from the Customers table, and it will cascade & delete the Orders as well:

DELETE c
FROM dbo.Customers AS c
WHERE CustomerID = 1;
SELECT *

FROM dbo.Orders

And we're done! The last step is to simply drop the duplicate FKs we created. We can use another script to generate the DROP commands, just like we did with the creation. We can simply review & run the output from this script to drop FKs based on the appended _CASCADE DELETES we added to the name:

--adapted from https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/
SELECT N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE fk.name LIKE '%CASCADEDELETES';

The benefit of this is that you don't have to worry about getting the original FKs back to exactly how they were when you started, since you never have to touch them. This can be particularly beneficial if all your FKs are fully trusted, and you want to avoid potential blocking or runtime issues of recreating all the FKs WITH CHECK to ensure they are trusted at the end.

The perfect index for this query is a two-column index, with the column that is compared with = first:

CREATE INDEX ON trade (symbol, open_time);

If that is still slow, the EXPLAIN (ANALYZE, BUFFERS) output for the query should tell us why.

I have two queries that return the same data, only the calculating field with the case is moved. I am using the Access Client Solutions "run query" to execute the query.

If I run this one

Select storenum,
       businessdate,
       kitchensenttime,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount
from irislib.tblorder
where not(kitchensenttime='01/01/0001'
          or parkcartime='01/01/0001')
    and parkcartime>kitchensenttime
    and businessdate='05/18/2022'

No rows are returned.

While this one

Select storenum,
       businessdate,
       kitchensenttime,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds
from irislib.tblorder
where not(kitchensenttime='01/01/0001'
          or parkcartime='01/01/0001')
    and parkcartime>kitchensenttime
    and businessdate='05/18/2022'

Will return rows. If I try the "broken" query with just the storenum and businessdate with just the disastrouscount calculation will it return rows.

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary.

This will also automatically enable the snapshot isolation level on the secondary which will result in additional load on the secondary server's tempdb.

Is there any chance this will put any load on the primary server's tempdb?

Assuming the SQL AG is in sync commit mode. Suppose the secondary becomes unresponsive or has secondary has network issues, then upon the session timeout expiry the AG will automatically get converted into async mode.

So, why is SQL AG sync commit mode classified as no data-loss HA solution even when the session timeout can make the mode async?

I have a use case where a large number of clients are reporting their operational states every minute using an HTTP connection to a php script.

For each client, I have a row in a mysql table with unique clientID.

PHP script first checks if the client is valid, and update the timestamp so I know when was the last time they connected.(A heartbeat of sense)

INSERT into clientStatus (cID,..,timeStamp) VALUES (123,....,time())
ON DUPLICATE KEY UPDATE ......,timeStamp=time();

I use INSERT/ UPDATE to handle any new clients joining, but I can handle it separately if it helps to have a better flow on the larger scale. Each HTTP request creates a write operation (as above) to the DB.

Is there a way to aggregate all such queries (15-20 thousand requests per minute), and save it somewhere, combine them and run it as a single INSERT/ UPDATE query? Or is this line of thinking not the right approach?

At this point, the AWS RDS IOPS is being a bottleneck, and that's what caused me to explore this angle.

You can use NOT EXISTS:

SELECT b.site,b.date
FROM table_b b  
WHERE NOT EXISTS (SELECT 1 
                  FROM table_a a  
                  WHERE a.site=b.site  
                  AND b.date = a.date
                  );

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7fed48443f4790e1b0076aeff4930aa9

Or you could use LEFT JOIN,

SELECT b.site,b.date
FROM table_b b
LEFT JOIN table_a a  ON  b.site=a.site and b.date = a.date
WHERE a.site IS NULL;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=d2d62fc4a1d4650cfcff49fda611342a

As @mustaccio helpfully pointed out in the comments you have too many columns returned in your subselect.

You don't have to specify the count(*) to use it in the having clause.

You can edit your subselect to something like:

DELETE FROM ODS.YARDI_PROPERTY WHERE HMY IN 
(select hmy
from ODS.VW_YARDI_DLR_PROPERTY_INFORMATION
group by hmy having count(*)>1 );

That will still find values of HMY that occur more than once in the table, but will return only one column, HMY, so won't cause an error in your DELETE statement.