texts:
I have a problem with a massive amount of INSERTs that are blocking my SELECT operations.

Schema

I have a table like this:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

I also have this little helper procedure, that allows me to insert or update (update on conflict) with MERGE command:

CREATE PROCEDURE [InsertOrUpdateInverterData]
    @InverterID bigint, @TimeStamp datetime
    , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
    MERGE [InverterData] AS TARGET
        USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
        AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
        ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
    WHEN MATCHED THEN
        UPDATE
        SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]
    WHEN NOT MATCHED THEN
        INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB])
        VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

Usage

I now have run service instances on multiple servers that perform massive updates by calling the [InsertOrUpdateInverterData] procedure rapidly.

There is also a website that does SELECT queries on the [InverterData] table.

Problem

If I do SELECT queries on the [InverterData] table they are proceeded in different timespans, depended on the INSERT usage of my service instances.
If I pause all service instances the SELECT is lightning-fast, if the instance perform rapid insert the SELECTs get really slow or even a timeout cancel.

Attempts

I'm done some SELECTs on the [sys.dm_tran_locks] table to find locking processes, like this

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

This is the result:

S = Shared. The holding session is granted shared access to the resource.

Question

Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands?

Do I have to use some kind of transaction with defined isolation mode inside of [InsertOrUpdateInverterData]?

Update 1 (related to question from @Paul)

Base on MS-SQL server internal reporting about [InsertOrUpdateInverterData] following statistic:

Average CPU-Time: 0.12ms
Average Read processes: 5.76 per/s
Average Write processes: 0.4 per/s

Base on this it looks like the MERGE command is mostly busy with reading operations that will lock the table!(?)

Update 2 (related to question from @Paul)

The [InverterData] table as has following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Here is the (allmost) complete sp_WhoIsActive result set:

SELECT command

dd hh:mm:ss.mss: 00 00:01:01.930
session_id: 73
wait_info: (12629ms)LCK_M_S
CPU: 198
blocking_session_id: 146
reads: 99,368
writes: 0
status: suspended
open_tran_count: 0

Blocking [InsertOrUpdateInverterData] command

dd hh:mm:ss.mss: 00 00:00:00.330
session_id: 146
wait_info: NULL
CPU: 3,972
blocking_session_id: NULL
reads: 376,95
writes: 126
status: sleeping
open_tran_count: 1


texts: MySQL wrong use of index when a date interval is big

texts:
I have a query that have this statement:

SELECT DISTINCT C.id_produto
  FROM movdiario C
 INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM
 INNER JOIN estoques B ON B.id_produto=C.id_produto
                      AND B.id_empresa= 1
  LEFT JOIN ESTATISTICAS_PRODUTO E ON E.ID_PRODUTO = B.ID_PRODUTO
                                  AND E.ID_EMPRESA = B.ID_EMPRESA
 WHERE C.empresa = 1
   AND C.emissao BETWEEN ('2019-10-28') AND ('2019-12-12')
   AND C.tipo = 'V'
   AND (C.situacao IS NULL OR c.situacao = '')
   AND P.ATIVO = 'S'

When I use an interval between 2019-10-28 and 2019-12-12 the query run fast using the correct index.

But when I use the interval 2019-10-27 and 2019-12-12 the query runs slowly and I have the explanation as follow:

What's wrong?
I have indexes on fields c.empresa, c.emissao and have a composite index c.empresa,c.emissao.


texts:
I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries - however I am having a hard time deciding what to use for my specific needs, and I would like to know if there are other things I should consider when making my decision.

The code I am planning on creating involves querying a set of data, implementing some algorithms (that will become more complex over time) and insertion of the data to the database. This stored procedure will be deployed by a scheduler - most probably azure webJobs since as far as I know Azure SQL does not support db jobs.

why I think of using C#

Assuming I can load DAL libraries from my server, and leveraging OOP - I guess I can implement a more coder friendly solution to this problem - and as for performance I assume it would be better or at least not noticeable in comparison.

Why I think of using T-SQL

I don't actually believe it is a better solution, but there might be something I am overlooking that might be a good case for T-SQL, because it makes no sense to me that it would have none.

Most importantly

Is there a way I can create a T-SQL stored procedure, call a C# stored procedure with complex objects as parameters - do my algorithms and return the data to be inserted, and insert it from T-SQL? seems like the most elegant, and best performing(?) solution (assuming it's possible and there are no drawbacks to calling CLR from T-SQL).


texts: Is it okay for me to truncate the postgresql serverlog file?

texts:
I am running PostgreSQL 13.0 on RedHat, 64-bit but my serverlog file has grown so big (29 GB).
I would like to know if its safe to clear contents for this file by using > serverlog or any other command that will clear all the contents in the file.
I looked around and could not find any documentation on whether its safe to do this. Most documentation talks of changing the logging strategy or configs but I do not have that level of access to achieve the configuration changes. Sample advice - Can an active Postgres serverlog be truncated?
Current space utilization for the serverlog file
-rw-r--r--  1 postgres postgres  29G Dec 10 14:16 serverlog


texts: MySQL Select previous row that matches condition

texts:
I am using MySQL 5.7 and I have 2 tables where relevant data looks like this:
SQL was exported using mysql workbench, let me know if it doesn't work or if it doesn't make sense:
CREATE DATABASE  IF NOT EXISTS `blog`
USE `blog`;

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` bit(1) NOT NULL DEFAULT b'0',
  `date` date NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_article_1_idx` (`category_id`),
  CONSTRAINT `fk_article_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

LOCK TABLES `article` WRITE;

INSERT INTO `article` VALUES (1,_binary '','2020-02-01',2),(2,_binary '','2020-02-01',2),(3,_binary '','2020-02-02',2),(4,_binary '\0','2020-02-05',2),(5,_binary '','2020-02-06',2),(6,_binary '\0','2020-02-08',2),(7,_binary '\0','2020-02-11',2),(8,_binary '','2020-02-13',2),(9,_binary '','2020-02-16',3),(10,_binary '','2020-02-29',2),(11,_binary '','2020-03-01',2),(12,_binary '\0','2020-02-29',2);

UNLOCK TABLES;

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

LOCK TABLES `category` WRITE;

INSERT INTO `category` VALUES (1,'category_1'),(2,'category_2'),(3,'category_3'),(4,'category_4');

UNLOCK TABLES;

What I want to accomplish is get the articles(or even better a list of article pairs[updated-not_updated] that fit in a certain category that were not updated, but the direct previous(by this I mean previous date) article belonging to the same category was updated. If the dates are equal then additional ordering is by id.
For the examples sake I guess I'm interested in retrieving the articles 4(and 3), 6(and 5) and 12(and 10) since these are the ones that are not updated but the article posted right before them that was in the same category was updated.
This doesn't have to be happening all in one query(although that would be the preferred way). I'm using java with Spring native queries if it matters.
I did think of getting all articles that were not updated and then check each record to see if it fits my criteria but it doesn't seem this is the right approach since I can get to maybe a couple of articles that were not updated and making the check could hurt performance and processing times. Any way I can do this directly from MySQL with a native query?
I hope my question is well formed, please ask for more details if they are needed. Column names have been changed but the structure is correct(in case you are wondering why they weird criteria for select)
Thanks in advance.
Reason for edit: provide create and insert startements(exported with mysql workbench)


texts: How to make Indexed Views work for SQLCLR?

texts:
In SQL Server, the indexed view is a hellscape of limitations.  But I need one.  I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search on substrings of that prettified version.  So I need a full-text index on a persisted calculated column or a materialized view.

However, the formatter depends on data stored in multiple tables.

So this works fine for a view, but not for a persisted computed column because those can't query from multiple tables.

My SQLCLR method is precise and deterministic, so it should be eligible for use in indexed views, but indexed views can't have SQLCLR in their index keys.

I could re-implement my formatter using the T-SQL FORMAT function... but FORMAT is also incompatible with indexed views.

Can indexed views do anything?  Ever?

I'm open to the "roll your own materialized view with triggers" approach if anybody can refer me to a good document on best practices for doing that.  Last time I tried, it spiraled out of control and there was tremendous duplication between the code for insert and update and the initialization.

Is there some way I'm missing to get performant text searches on calculated data on a few million rows, without using a materialized/indexed view or a persisted calculated column?

The formatter does not perform data access. However, the data I need to feed into the formatter for it to be useful will be coming from multiple tables (with a nice regular join), hence I can't solve this problem with persisted computed columns. I can't key the SQLCLR column, so I can't use it in a fulltext index.

I thought this would be simple. Persisted columns and indexed views are designed to perform calculations on write, and to properly implement the observer pattern so that changes to their dependencies are reflected on the calculated value.


texts:
I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!

Consider the following table:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
3  |     1 |   10 |     2 | 200
4  |     1 |   11 |     2 | 210
5  |     1 |   12 |     2 | 220
6  |     1 |   13 |     2 | 230

The idea is that we are mapping References from an unknown number of systems. This is not a "From and To" scenario, but a two-way mapping.

We are looking for a solution that satisfied all of the below statements:

A new row cannot be inserted where the combination of TypeA, RefA and TypeB already exists (could be achieved by unique constraint)
A new row cannot be inserted where the combination of TypeB, RefB and TypeA already exists (again, could be achieved by unique constraint)
The pairs of columns must be checked both ways around

To clarify, the following rows defined below would all be invalid:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
?  |     1 |   10 |     2 | 201   <-- Type 1 and Ref 10 already exists
?  |     1 |   20 |     2 | 210   <-- Type 2 and Ref 210 already exists
?  |     1 |   12 |     2 | 220   <-- This row already exists exactly like this
?  |     2 |  220 |     1 |  21   <-- Type 2 and Ref 220 already exists (in the B columns)
?  |     2 |  300 |     1 |  13   <-- Type 1 and Ref 13 already exists (in the A columns)

What is the best way to approach this?

Please let me know if anything is unclear in the above!


texts: SQL Server service won't start due to certificate issues

texts:
Problem

We are running a SQL Server 2014 SP3 CU1 on Win 2016. After a each reboot of the host the SQL Server service won't start automatically. It always results in the following error messages.

The service is configured to start automatically.

SQL Server log:

  Error: 17190, Severity: 16, State: 1.
  Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.

  Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

  Error: 17182, Severity: 16, State: 1.
  TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support.

  TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors.

  Error: 17826, Severity: 18, State: 3.

  Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

  Error: 17120, Severity: 16, State: 1.

  SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Workaround

I could solve the issue temporarily in the following way:

connect via RDP to the host and login with the service account name
open the SQL Server Configuration Manager
start the SQL Server service

Unfortunately I can't start the service when I login with my personal login to the host. It results in the same error mentioned above.

Ideas

We already tried to delete the profile of the SQL service account. It doesn't solved the issue.

We also thought about space problems, but the host has more than 20% (> 50GB) free space available for each used drive.

What could be the issue with the certificate? How and what I have to check?


texts: How to take undo datafile offline ,which is in recovery mode?

texts:
I am trying to take undo datafile in offline mode using following command

alter database datafile '<datafile path>' offline;

this datafile is in recovery mode.  Oracle shows message as database successfully altered.

But after executing this command when I select entries in v$tablespace. The file that I just made offline remains in this table.

Can somebody please tell how to take undo datafile  offline.

OS: RHEL
Oracle version: 11g
Datafile status: Recovery


texts: SQL Server CLR method failing due to TLS 1.0 shutdown

texts:
I have an old CLR assembly that was using .Net 2 and when our TLS 1.0 connections were shut down for security reasons, the calls I was making out of the assembly (rest posts to https end point) we being returned with

  The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Authentication failed because the remote party has closed the transport stream.

We figured we needed to upgrade the assembly to use .Net 4 (specifically 4.5.2) to get it to use TLS 1.1 which is still active.  I upgraded the project and assembly but am still getting that message.

Is there something specific I need to do in SQL Server or in OS to force the TLS 1.1 connection?


texts: The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

texts:
We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed  full text filter service. So we also added that feature to the install. Ever since we did that we have been getting the error that it stopped abnormally every minute filling up our error log. Here is the full error message.

  The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

Is there any recommendations to avoid this or any best practices with regard to Full text service running on alwayson clusters?


texts: Static service not working when using SCAN?

texts:
I know the correctly way to configure service is creating a resource in with srvctl add service command, but I've seen a behavior after configure a static listener in a RAC environment. It's not possible to connect in the static service using the SCAN, just with the vip/public IP. This behavior is correctly? or probably a bad configuration in my environment?
Usually a just configure a static listener in a RAC when using data guard broker, which broker uses the vip.


texts: Calculating difference between consecutive timestamps. Error when i reach the hour mark

texts:
I am using MySQL

I have a table that contains among other things an id a time in the format date:time (YYYYhhmmss) and a readingvalue. The difference between each reading value is 300 seconds but this may vary slightly so i need to calculate the difference between each timestamp and then add the difference in readingvalue for that allotted time.

Below is what i have tried and this works until i get to the a timestamp of 10:55:00 to 11:00:00. The difference in seconds this gives is not 300 but 2700. Does anyone know how to fix this. Thank you

SELECT
    a.id as id1,
    b.id as id2,
    a.rt1 as rt1,
    b.rt1 as rt2,
    a.avg_readingvalue as arv1,
    b.avg_readingvalue as arv2,
    time_to_sec(b.rt1-a.rt1) as rtdiff,
    b.avg_readingvalue+a.avg_readingvalue as arv21
FROM table1 a
INNER JOIN table1 b
    ON b.id=(a.id+1)
ORDER BY by a.id


texts: Issue moving database between servers (cannot find .frm file even though it's there)

texts:
I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great.

The name of my database is roster and it has one table called users. I copied the 'roster' folder which contains the following files:

db.opt
users.frm
users.MYD
users.MYI

I put the 'roster' folder in the mysql directory. I run the following:

<?php
$db = mysql_connect("localhost","username","password") or die ('Fail message1');
mysql_select_db("roster") or die("Fail message2");
?>

No error. Awesome. But when I run the following:

$query = "SELECT last_name, first_name, u_name, skype_id, primary_location FROM users";

$result = mysql_query($query)or die('query error'.mysql_error());

Then I receive the following error:

query errorCan't find file: './roster/users.frm' (errno: 13)

Furthermore, when I login to the server and login to mysql. I see the database and table:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phones_db          |
| roster             |
+--------------------+
4 rows in set (0.00 sec)

mysql> use roster;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+------------------+
| Tables_in_roster |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from users;
ERROR 1017 (HY000): Can't find file: './roster/users.frm' (errno: 13)
mysql>

I checked the 'roster' folder and it contains the four files mentioned at the beginning of this post. The file is where it supposed to be, right? Did I move the database wrong? What gives? Any ideas?


texts: Created databases and inserted data on MySQL slaves in Master-Slave replication - how to fix?

texts:
I got MySQL Master-Slave replication running on 1 master with 2 slaves. SHOW MASTER STATUS / SHOW SLAVE STATUS says they are in sync, but databases have been created on the slaves and filled with data which of course cannot be seen on the other slaves/master, because no replication from Slave to Master happened.

Is there an easy way to fix this situation? Two outcomes would be acceptable for me, but I'm not sure what is the best way to achieve this (sorry, DBA noob):

No replication at all, but no data lost on each server in its current state (replicated data stays in its current state, new data on the slave stays there, too)
Converting Master-Slave to Master-Master-Replication so every server has the same data.


texts:
I have created a simple CLR functions for compressing/decompressing NVARCHAR columns:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBinary Compress( string str ){
    if( str == null ){return new SqlBinary();}

    if( String.IsNullOrEmpty( str ) ){str = " ";}

    byte[] bytes = Encoding.Unicode.GetBytes( str );
    using( MemoryStream msi = new MemoryStream( bytes ) ){
        using( MemoryStream mso = new MemoryStream() ){
            using( GZipStream gs = new GZipStream( mso, CompressionMode.Compress ) ){
                msi.CopyTo( gs );
            }
            return new SqlBinary( mso.ToArray() );
        }
    }
}

The compression ratio I get is about 4 or if I have 1024 KB of not compressed data I will get 256 KB of compressed data. I am aware that the ratio depends on the data itself and its size, but I want to get a better ratio.

As I am using SQL Server 2012 and .net 4.0, is there a change that the compressions are not giving the expected ratio because of issues like this?

And is there an alternative class I can use in the SQL CLR function? There are such alternatives but are not supported currently.


texts:
I can't seem to get around this problem. I am trying to query into a SQL table I have and limit the results.

I have an org_id. Also a field named address_id. The table has a "primary_address" column which denotes which row has the address_id I want. IF there are more than one address_id associated with an org, it will be marked primary. If there is only one address associated with the org, there will not be a designated primary.

Here is a hypothetical example:

org_id  AddressID   Primary
============================
1       1           1
1       2           0
2       3           NULL
3       4           NULL

How can I pull a distinct set of org_ids along with the correct address_id, while still getting the ones that don't have a primary?

The major problem that I have is that I can't just MAX() or MIN() because there are several other attached fields, too, that are strings or address tables.

The steps I have tried:

1) Create a CTE with an address count, then only pull the orgs that have 1 row count OR marked as primary. This pulled a lot of duplicates.

2) Pull only the ones marked primary and inner join them to the table. This did not pull all the records.

Any help would be appreciated, even if you just point me to a page that explains some steps I could take.


texts: Restoring MySQL database data from ibdata1 and ib_logfile

texts:
I am trying to restore a MySQL database. I have the following:

ibdata1

ib_logfile0

ib_logfile1

.frm files restored as I have a backup of the structure.

Is it possible to restore the data for the database having the above? The server runs on CentOS.


texts: Galera multi-nodes Master-Master issue with READ right after INSERT

texts:
I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.

Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.

It's worth noticing that:

Both requests were made asynchronously.
The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)

What am I dealing with here, guys?


texts: Database size not reduced after compressing tables

texts: Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?


texts:
I added a calculated field to my table, and only want to update NULL rows (or "new" rows).  Something is going awry, and every row in my table is being updated with the current DateTime when 1 row is inserted.  This is the Create Table syntax, what must I do so that only the "new" rows are updated with the current DateTime

CREATE TABLE [dbo].[SE](
[dbID] [int] IDENTITY(1,1) NOT NULL,
[uqID] [varchar](31) NOT NULL,
[POD] [varchar](255) NULL,
[EC] [varchar](255) NULL,
[S1] [varchar](35) NULL,
[S2] [varchar](35) NULL,
[S3] [varchar](35) NULL,
[S4] [varchar](35) NULL,
[S5] [varchar](35) NULL,
[S6] [varchar](35) NULL,
[S7] [varchar](35) NULL,
[PSD] [varchar](50) NULL,
[DS] [datetime] NULL,
[CN] [varchar](max) NULL,
[autodate]  AS (getdate()),
PRIMARY KEY CLUSTERED
(
[dbID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


texts: What is the best way to handle a join / group_concat query in MySQL

texts:
I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.
I have 3 tables: tasks, assignees, and users
---tasks----
id, task, completed

---assignees----
user_id, task_id

---users---
id, name

I need to select tasks with concatenated user names of assignees, like so
tasks.task  |   assigned_names.names
---------------------------------------
"My task"   |   "Joe Schmoe, Bob Mcbart, Sally McGuire"

Here is what I have currently
SELECT tasks.task, assigned_names.names

FROM tasks

LEFT JOIN (
   SELECT
      GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
      assignees.task_id
      FROM assignees
      LEFT JOIN users ON users.id = assignees.user_id
      GROUP BY assignees.task_id

) assigned_names ON assigned_names.task_id = tasks.id

WHERE tasks.completed IS NULL

My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea.
Is there a better strategy?
Also, I would be happy to rephrase my question title if someone could suggest better language.
Thanks!


texts: When were OLEDB based linked servers added to SQL Server?

texts: I guess what I'm asking is in which version of SQL Server were linked servers added to SQL Server?


texts: Data Warehouse -- Generating natural keys to be used in WHERE clauses

texts:
I need to repeatedly retrieve a group of records from a data warehouse fact table based on multiple where conditions.

Not

WHERE (user_d=1 OR user_id=2) AND (branch_id=1 OR branch_id=2)

but

WHERE (user_id=1 AND branch_id=1) OR (branch_id=2 AND branch_id=2)

I was thinking of doing something like this:

... WHERE CONCAT(user_id,branch_id) IN ('11','22 )

and then creating a new column, turning the concat into a natural key which will be created on insert:

WHERE new_key IN ('11','22')

This will allow:

SELECT queries to avoid JOINS. I have input of user_id and branch_id, and can generate that in the application.
I can put a unique constraint on the key field, and then can use an UPSERT.

Is generating a natural key like this strictly for easier lookups bad practice?

CLARIFICATION

I want to use an IN clause because I will have a lot of combined values to lookup.

I won't have IDs to build the new_key or look it up, it will be values.

I think I answered my own question. It's bad practice because it's redundant data.

The CONCAT will be a better choice off a JOIN. To optimize, instead of storing values in the database, it's better to add a key-value store in front like redis.


texts: Migrating database from Oracle to SQL Server

texts:
I need to migrate an Oracle database in which there is more than one table. I created new SSMA project and grant all privileges to my Oracle user, but still an error occurs:

  User does not have required permissions: SELECT ANY DICTIONARY.

Anybody have idea how to solve this problem?


texts: How to change a script task version in SSIS?

texts:
I added a script task to an SSIS project in VS2015. When I deployed to SQL Server 2016 I got an error message that the version 15.0 script is not supported.

Where does this version 15 come from? Reading other, similar questions on Stack Overflow I see that you can set the target version of the project to SQL Server 2012 - which I did (the eventual deployment target is SQL Server 2012).

I also tried deleting and recreating the script task. And in the information of the script it says it's using V10 of C#.

How can I resolve this?

  Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task ""ST_a1ad9dc5972c42b68c12a13155f10b6d"" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
         at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)"

I have also opened the project in SSDT 2012 and rebuilt with a different name. Same error. It seems that there must be a reference that wasn't deleted or something.

None of the solutions on this question (https://stackoverflow.com/questions/34893267/ssis-script-task-vs15-not-work-when-deploy-on-sql-server-2014) worked.

Looking at the XML in the package where the script is, I can easily find that task, and there is no reference to version 15 anywhere.

=========== EDIT

After copying the project to the machine that hosts the database, opening VS2015 and deploying from there, the package executes.

And then when going back to my machine and building there, it doesn't.

Is this a bug? Or am I doing something stupid by expecting the build to produce the same deployment wizard as using the wizard from VS...

I have SQL Server 2016 (13.0.4411.0), ssisdb has the schema version (13.0.1601.5).

I am using an integration services package created in visual studio 2015. The script component has the path: C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax It won't let me execute the package through the integration services catalog (due to the message experienced by Zach). However it appears that it will let me execute it through the file system (using SQL Agent). Unsure if this is working, will update this once the package has completed.


texts: How to start indexing a new data warehouse

texts:
I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds.

I plan to use an index tuning tool (probably sp_BlitzIndex), but how do I start? Do I start off with no indexes and then let sp_BlitzIndex tell me what indexes to use after a while? Or do I start with the obvious indexes and build on that with sp_BlitzIndex?

I was planning on adding a clustered index on every primary key and a non-clustered index on every foreign key -- and then let sp_BlitzIndex suggest the less obvious choices. But maybe I should leave it all up to sp_BlitzIndex.


texts: Would a date clustered index speed up queries with date ranges? (SQL Server)

texts:
I'm not the expert on indexes really - I'm trying to work my way through a few materials now.

Anyway, let's say I have a table with 7 million records spanning 3 years. However the most common query will pull say the last 3 months -- using an integer field (unix epoch time).

This 3 month query usually takes about 18 seconds --- but many other queries may be built atop it, so I was trying to speed it up. I created a clustered index on the unix_epoch_time int field that would eventually be in a 'where' statement filter.

However, this doesn't appear to have sped up the query time at all. Does this mean this clustered index is a spurious waste? Or accounts for something like 1% of the query time? I thought it would be much faster.


texts: Should I use table hints with a process-keyed table?

texts:
I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table locks) when using these types of tables.  Here's some background:

I'm developing a batch process that inserts the rows resulting from a stored procedure call into a table.  I have no control over the stored procedure.  The table is keyed by process id (@@spid) because it needs to be read by several stored procedures that follow and should not conflict with other batches running at the same time.  This shouldn't be too big a problem because it is a low volume server, e.g. not too many of these batches will be running simultaneously.  Still, I wouldn't want to create table locks if it can be avoided as I imagine blocking may cause unexpected failures or delays.

Any advice?


texts: Challenge. Split database 500 records in two smaller datasets, randomly

texts:
I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone knows how to do that using an sql query?
ps. I use mysql.

In addition I find this ...
select ...
from ...
where ...
ORDER BY rand() LIMIT 150 but this gives me only the first dataset

Thanks in advance


texts: Bulk-Insert Select performance over individual Inserts

texts:
I am using sequelize as ORM for my nodejs application.

The scenario is, I have to insert a Poll and its Options and get their objects.

Approach 1:

1 - Insert Poll:

INSERT INTO `polls` (`id`,`start_at`,`expire_at`,`options_count`) VALUES (DEFAULT,1487945658,1487945965,3);

2 - Insert Options (bulk insert):

INSERT INTO `poll_options` (`id`,`poll_id`,`text`) VALUES (NULL,{id},'red'),(NULL,{id},'green'),(NULL,{id},'blue');

3 - Select Options (I have to select because I don't get id of inserted Option due to bulk insertion):

SELECT `id`, `poll_id`, `text` FROM `poll_options` WHERE `poll_id` = {id};

Approach 2:

1 - Insert Poll

2 - Insert each Option one by one (this way I can get inserted id as well, so no need of explicit SELECT at the end)

Question:

Which approach would be better?

Both tables have referential integrity enabled and using innodb engine.


texts: Safest place to download SQL Server 2005 Service Pack 2 Installer

texts:
I need to obtain Microsoft SQL Server 2005 Service Pack 2 as part of a rollback plan for an upgrade to SP3. All links on the Microsoft website seem to be broken. This will be installed on a very safety sensitive system and I would prefer to obtain the installer from the official Microsoft website. I am able to obtain a download link on MSDN, but it says "No product key is required" so I'm assuming it wont even give me an option to enter a license key when I install it.

Where is the safest place to obtain a Microsoft SQL Server 2005 SP2 installer (iso or exe)?


texts:
When executing the following code on the SCOTT schema I receive a compilation_error.
What am I doing wrong here? I doesn't even tell me what the error is.

SQL> create materialized view dept_emp_mv as (
  2      select dname, job, hiredate, count(*) nbr
  3                  from emp e
  4                  join dept d on e.deptno = d.deptno
  5                  group by dname,hiredate,job);
Materialized view created.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
FRESH
SQL>
SQL> insert into emp values (8003, 'TEST', 'TEST', 7902, to_date('20131127','YYYYMMDD'), 2500, null, 20);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
NEEDS_COMPILE
SQL>
SQL> execute DBMS_SNAPSHOT.REFRESH('DEPT_EMP_MV','C', parallelism => 1);
PL/SQL procedure successfully completed.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
COMPILATION_ERROR

Any help is appreciated.

Update

With Oracle 12c this issue seems to be resolved.


texts: Understanding Simple MongoDB Replication Works

texts:
I've been using MongoDB (3.2.9) replication for years and barely know the detail how it works. My Stack is Rails 4.2.5 with Mongoid 5.1.3. I've 3 database servers, let's call them A (Primary), B (Secondary) & C (Secondary). My Mongoid configuration as follow:

production:
    clients:
      default:
        database: db_production
        hosts:
          - A:27017
          - B:27017
          - C:27017
        options:
          pool_size: 10
          timeout: 60
          replica_set: my_replset
    options:
      raise_not_found_error: false

What I thought for years, server A is only for write and B+C for read only operation. But when I check on the doc lately (https://docs.mongodb.com/v3.2/replication/), it show that server A is doing write & read while B & C get the copy from A.

So my questions is: How to make sure if B & C serving a 'read' operation to any request? I'm afraid all the requests hitting the server A while B & C are exists just for backup failover.

thanks for any feedback


texts:
I want to populate a grid as data is made available.

If you run a really large sql query in SQL Management Studio the results window starts to populate with rows as they are made ready (I effectively want to mimick this), however at the momoent when I run SQlExecDirect() I have to wait untill all rows have been calculated.

The following code is an example from Microsoft about Asynchronously doing an SQLExecDirect() Asynchronously

SQLHSTMT  hstmt1;
SQLRETURN rc;

// Specify that the statement is to be executed asynchronously.
SQLSetStmtAttr(hstmt1, SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON, 0);

// Execute a SELECT statement asynchronously.
while ((rc=SQLExecDirect(hstmt1,"SELECT * FROM Orders",SQL_NTS))==SQL_STILL_EXECUTING) {
   // While the statement is still executing, do something else.
   // Do not use hstmt1, because it is being used asynchronously.
}

// When the statement has finished executing, retrieve the results.

Does anyone know if it is possible or how it is possible to Asynchronously call SQLFetch() Asynchronously and retrieve each row as it is made available?


texts: Setting up Log shipping without enabling LS copy job

texts:
We have a need where we need to set up LS for the servers where we use NAS for backups.

As NAS is shared between Primary and Secondary there will not be a need to use LS COpy job as backups will be duplicated and LS might not work

How can i set up with just LS backup and LS restore configured and make LS a success?


texts: Deleting duplicate records when using text datatype

texts:
I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.

Normally I would use the following statement to remove duplicates:

DELETE
FROM emails
WHERE email_id NOT IN (
SELECT MAX(email_id) FROM emails
GROUP BY record_id, from_add, to_add, subject, body)

However, body is datatype text, so it cannot be compared/grouped. It gives the following error message on the select query:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

What is the best way to identify duplicates with the text datatype? Do I have to convert the column to varchar first?


texts: Where to look for Agent history in Replication?

texts: In SQL server 2008R2 replication configuration, "agent history cleanup distribution" job by default runs every 10 minutes and deletes any historical data older the 48hrs. If I increase the retention parameter for > 48hrs to access that historical data, where will that data be saved by default ?


texts: Query to check the timestamp of a table accessed recently

texts:
I would like to list out the timestamp of tables which are accessed from my application recently. I would like to know at what time they were accessed so that I can pull which part of application accesses which table?

The query

select * from all_tab_statistics;

provides only the timestamp of  deleted,updated,truncated details but not the timestamp of read details.

I am a normal user and not DBA. Hence I do not have access to all system tables.


texts: Currently all connections getting "Timeout expired"

texts:
I have an application which connects to SQL Server. Currently I am getting,

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

When I ran,

SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
     DB_NAME(dbid) ='MyDb'
GROUP BY
    dbid, loginame

DBName  NumberOfConnections LoginName
MyDb    10                     sa
MyDb    109                   MyUser

The status of all process is sleeping and cms is AWAITING COMMAND

Here is my code,

private async Task<object> ExecuteAsync<T>(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func<IDataReader, T> callback = null)
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();
    using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
    {
        using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
        {
            command.Parameters.AddRange(parameters);
            await connection.OpenAsync().ConfigureAwait(false);
            command.CommandTimeout = _settings.CommandTimeout;
            var transaction = connection.BeginTransaction(isolationLevel);
            command.Transaction = transaction;
            try
            {
                object result;
                switch (executionType)
                {
                    case ExecutionType.Reader:
                        var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
                        using (reader)
                        {
                            var list = new List<T>();
                            while (reader.Read())
                            {
                                if (callback != null)
                                {
                                    var item = callback(reader);
                                    if (item != null)
                                    {
                                        list.Add(item);
                                    }
                                }
                            }
                            result = list;
                        }
                        break;
                    case ExecutionType.NonQuery:
                        result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                        break;
                    default:
                        result = await command.ExecuteScalarAsync().ConfigureAwait(false);
                        break;
                }
                transaction.Commit();
                stopwatch.Stop();
                var elapsed = stopwatch.Elapsed;
                if (elapsed.Seconds > 2)
                {
                    _logger.Log(string.Format("{0} took {1} time", command.CommandText, elapsed));// only log if it tooks more than 2 seconds
                }
                return result;
            }
            catch (Exception exception)
            {
                _logger.Log(exception);
                transaction.Rollback();
                throw;
            }
        }
    }
}


texts:
Doing a pie chart in crystal. Items are

Inquiries
Referrals
Followups

A referral has to have an inquiry, A followup has to have a referral.

I want to show pie chart of referrals to inquiries so the slice would be the referred count, the rest of the pie would be the non-referred inquires.

Table A:

customer001 INQ 1/1/2013
customer001 INQ 9/30/2013.
customer001 REF 10/15/2013.
customer001 FOL 10/30/2013.

This customer has 4 records. First off I will ignore the followup record in my crystal reports record filter.

That leaves:

customer001 INQ 1/1/2013
customer001 INQ 9/30/2013
customer001 REF 10/15/2013

Now when I run my chart the pie will show 2 INQ's and 1 REF in the pie chart.
But I want it to be a 50/50 pie, because the INQ record that is most recent belongs to the REF record of 10/15.

I need a SQL statement that removes the 9/30/2013 record out of a view.
I have written code (Top(1) MAX of the date field in a view that gives me this output with the most recent INQ record inserted to the end of all records. Now I need a second statement that strips the duplicate record out.

ID---------Code--Date----INQcode---INQdate
customer001 INQ 1/1/2013 INQ 9/30/2013
customer001 INQ 9/30/2013 INQ 9/30/2013
customer001 REF 10/15/2013 INQ 9/30/2013.

So in my second unwritten view I need to write out only the records where code<> INQcode AND Date<>INQdate


texts:
I have my code running online shared hosting provider. Recently I am  seeing too many deadlock exception is being thrown. As you know that shared hosting uses the same database server to host all the databases, I am thinking does other database is running in same server blocking the resource that required for my database query to run. It sounds like stupid. But I have little knowledge on this matter. We are using MS-SQL server2005 and application written in .net framework 2.0. I am not sure about the Windows Server running.

I hope I had enough information here. Please let me know if you need more information.

Does deadlock occurs between two or more databases?


texts:
Below is the error message  I am getting

waiting for server to start..../bin/sh: line 0: exec: 9999: not found

postgres@ip-172-31-37-228:~/data>  pg_ctl start -D /var/lib/pgsql/data -p 9999
waiting for server to start..../bin/sh: line 0: exec: 9999: not found
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I tried changing port number sill not working


texts: MySQL Job failed to start after adding "log = /var/log/mysql/mysql.log" to my.cnf

texts:
I'm trying to add "log = /var/log/mysql/mysql.log" to my /etc/mysql/my.cnf file. But after sudo service mysql restart I have Job failed to start.

If I delete line "log = /var/log/mysql/mysql.log" all work fine.

Set permissions like sudo chown -R mysql:mysql /var/log/mysql

What it can be?


texts: MariaDB, SEQUENCE engine, dynamic upper bound

texts:
I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema:

create table main (
  id integer primary key,
  -- ... other columns --
);

I've learned about MariaDB STORAGE engine for generating sequences but on the surface that's good only for generating static sequences, where you know the range at the time of writing the query, e.g.

MariaDB [skynetdb]> select * from seq_1_to_3;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.001 sec)

But how do I generate sequence whose upper bound is the result of select max(id) from main? It seems that MariaDB doesn't accept meta-queries where I would construct a name of a table to query and then query it in a single query, e.g. something like this:

-- INVALID, FOR ILLUSTRATION ONLY
select * from concat('seq_1_to_', select max(id) from main)

Or is it possible? How? (Note I'm aware that I can do one additional roundtrip and construct the sequence table name on the client, but I want to avoid it because I don't have a feature rich client, only mysql command)

The following recursive common table expression (CTE) works

MariaDB [skynetdb]> with recursive nums(n) as (select 1 union all select n+1 from nums where n <= (select max(id) from main)) select * from nums;

but it's too slow. It seems it constructs whole nums table in memory first.


texts:
When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules?

Example: What happens if backup is not ready, will the copy job still run. Similarly, what if the copy is not compete - will the restore job still run? Will SQL Server dynamically handle this or will it skip one round?


texts: Replace space (" ") with no space ("") in one column

texts:
I have a table like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     | Denpasar
 2 | Aceh     | Banda Aceh
 3 | Sumatera | Asahan

This table has many rows. The problem is I want to replace the space before the text in column Kota for all rows like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     |Denpasar
 2 | Aceh     |Banda Aceh
 3 | Sumatera |Asahan

I searched Google, the function replace in MySQL only affects one row:

SELECT REPLACE(string_column, 'search', 'replace') as Kota

Can someone fix my problem?


texts: PostgresSQL - cumulative sum grouped by values

texts:
Have this query returning right values where c.tiporifiuto = 0
WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno
         , SUM(c.peso) as totale
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3
      AND c.dataora >= '2020-06-01'
      AND c.dataora <= '2020-06-12'
      AND c.tiporifiuto = 0
    GROUP BY giorno
    ORDER BY giorno ASC
) SELECT giorno, SUM(totale) OVER (ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  FROM data

returning
       giorno        |   sum
---------------------+---------
2020-06-01 00:00:00 |   24.09
2020-06-02 00:00:00 |   48.05
2020-06-03 00:00:00 |   88.96
2020-06-04 00:00:00 |  183.78

Need same query but grouping ALSO for tiporifiuto, expected results should be
       giorno        |   sum   |  tiporifiuto
---------------------+------------------------
2020-06-01 00:00:00 |   24.09  |  0
2020-06-01 00:00:00 |   36.09  |  2
2020-06-01 00:00:00 |   13.09  |  3
2020-06-02 00:00:00 |   48.05  |  0
2020-06-02 00:00:00 |   72.18  |  2
2020-06-02 00:00:00 |   26.18  |  3
2020-06-03 00:00:00 |   xx.05  |  0
2020-06-03 00:00:00 |   xx.96  |  2
2020-06-03 00:00:00 |   xx.78  |  3

help pls
UPDATE
Source data is a materialized view
CREATE MATERIALIZED VIEW public.conferimenti_001_1d
TABLESPACE pg_default
AS
   SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
   sum(c.peso) AS peso,
   c.tiporifiuto,
   c.idcomune
   FROM conferimenti c
   GROUP BY (time_bucket('1 day'::interval, c.dataora)), c.idcomune,
   c.tiporifiuto
WITH DATA;

SOLVED WITH
WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno, SUM(c.peso) as totale, c.tiporifiuto
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 AND c.dataora >= '2020-06-01' AND c.dataora <= '2020-06-12'
    GROUP BY giorno, c.tiporifiuto
    ORDER BY giorno ASC, c.tiporifiuto ASC
)
SELECT giorno, SUM(totale) OVER (PARTITION BY tiporifiuto ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as totale, tiporifiuto
FROM data


texts: Best practices for sql server log shipping method

texts:
We have about 20 databases, with no DR config yet.
Most of them are SQL Server 2016 with Failover cluster Availability group
What's the best method for a log shipping?

Log shipping directly between DC site to DR site
Log shipping through a fileshare server

Imo, 2 is much more risks in case of file share server are accessed by someone else.


texts:
I've gone ahead and configured the Report Server after SQL Server installation, and as part of that I've ended up with:
1) a report server database and
2) a link (I think in 2016 they call it Web Portal), where reports are to be deployed I guess.

I realized this was incorrect, I should not have configured/installed Report Server to begin with. I need to remove REPORT SERVER without removing Reporting Services. To be specific, I need the link (the Web Portal, or the link Report Server puts up) REMOVED and the Report Server database removed also.

The ONLY instructions I get are for removing Reporting Services, here and here , all that's talked about is removing Reporting Services. I just need the Report SERVER (DBs) and Report Server LINK to be removed. How do I accomplish that?

I'm using SQL Server 2016. Thank you very much in advance.


texts:
I have a table where I want to update "time 2" row, in the user's last "time 1" entry. I was try this, but not function.

UPDATE `temp` SET `time2`=NOW() WHERE user = 'kraso' (SELECT max(time1) FROM 'temp')


texts:
I run this:

$ postgres -D foobar

It just hangs.  There is no error message.  I newly created this database with the initdb command.  What should I do?


texts: Is there an effective way to create extended event data that includes stacktraces in SQL Server on a local db instance?

texts:
I read Paul Randal's article on getting the symbols for SQL Server. I have a theory that I could get the extended events to read the symbols from the symbol server catch directory by setting the _NT_SYMBOL_PATH environment variable. The only problem is I can't create the right conditions for any data to show up when I create an extended events session with sqlos.spinlock_backoff. I tried the script in this pdf.

I tried HammerDB and I tried this script with adventure works. I don't get any spinlocks. I tried setting max server memory at 256 megs (my default setting on my laptop) and I tried it at 2 gigs. The Extended event session was running, but no data showed up.


texts: What does N'[Uncategorized (Local)] mean?

texts:
The following code is seen in several Q&A code snippets here (and elsewhere),  I am trying to translate the section N'[Uncategorized (Local)]
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
       @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

Examples

SQL Job in idle state
Copy files using script to a network share

I have looked at Microsoft EXECUTE (Transact-SQL)

'name'
Is a valid user or login name. name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.
name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

and

[N] 'command_string'
Is a constant string that contains the command to be passed through to the linked server. If the N is included, the string is interpreted as nvarchar data type.

And I am guessing the section N'[Uncategorized (Local)] goes and gets the name of the sysadmin and passes it to execute the job as the sysadmin, but I am not finding a good reference that indicates how all three segments are working together.


texts: I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This means that we have an ever growing database. With regular intervals our customers do searches for different things, for example a certain material used in a car. These queries have started to take really long time to execute and we dont really know how to fix them. So my question is there anything we can do to optimize this query? I'll provide the EXPLAIN and then the query itself.


texts: Oracle: Does enabling a PK rebuild its index?

texts:
Scenario:

Table MY_TABLE has primary key constraint PK_MY_TABLE
Table MY_TABLE has also an index called IDX_PK_MY_TABLE which is enforces the uniqueness for constraint PK_MY_TABLE.

If I disable constraint PK_MY_TABLE and then enabled it back, does index IDX_PK_MY_TABLE get rebuilt?


texts:
I am confused about schema owners in Postgres.

When I create a schema with the owner defined by the AUTHORIZATION keyword, owner can create objects in that schema (no explicit privileges are shown on that schema in pgAdmin4).

But when I alter the schema owner to another role, this role cannot create objects in that schema (until I explicitly grant USAGE and CREATE).

I suppose, that schema owner has implicit privileges to do anything with schema. What is the right way to change schema ownerschip?


texts: How to extract multiple key value pairs into distinct columns

texts:
I have a database that is outside my control, and am pulling data from it using Power BI for reporting. The database is a list of pages on a website, where each page is a new row, and the columns all have metadata regarding the page (created by, created by, and so on). Except not all of the metadata is stored in distinct columns, there is a "Primary" and "Secondary" column that stores mulitple key value pairs. For example:

PageName | CreatedBy | Primary | Secondary
page1    | Joe       | [owner:frank,topic:meals] | [topic:drinks]
page2    | Dale      | [owner:joe, topic:drinks, topic:meals] | [topic:appetizers]

The metadata is even more complex than above with several other keys that can potentially occur in either Primary or Secondary column. But the root problem is, how can I extract these key value pairs using Power BI so that my final table has each key as a unique column with all instances of its values across Primary / Secondary as a list, similar to this:

PageName | CreatedBy | Owner | Topic |
page1    | Joe       | frank | meals |
page2    | Dale      | joe   | drinks, meals, appetizers |


texts: In Oracle, how can I give one user privileges to alter the schema of another user?

texts:
I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that is used by a lot of my users.

I don't want to give Dogbert the password to the account to the Wally schema. So I've created a temporary account for Dogbert.

How can I give Dogbert's account permission to alter the structure of Wally's schema? I know I can certainly give Dogbert insert/update/delete/select but that doesn't affect the structure.


texts:
How can I kill session with infinite looped plperl function?

Both pg_terminate_backend and pg_cancel_backend had no effect. kill -1 pid did nothing as well and kill -9 pid crashed server.


texts: SQL Server job - powershell Where does "log to table" save logs

texts:
After selecting the "log to table" option and running the job, when I come back to this screen and select View, I can see the output of the powershell script in a text file.

The only job step is a call to a powershell script that takes a string as input and prints it after performing a toUpper

However, where are these logs being stored?


texts: Deadlock graph: Show SQL statement containing the phrase "password"

texts: We need to debug a MS SQL Server 2014 deadlock situation. We got our deadlock graph, but the statement seems to contain the string "password", so the SQL is obfuscated ("*password---..."). Is there a way to turn this behaviour off and get the actual statement?


texts:
I’m trying to set up backup strategy for a production postgreSQL DB.
It will have large amount of data and it should be running 24 X 7.
Could you recommend some backup & recovery strategies that can meet the following criteria?

Large amount data (about over 200GB).
Hot backup (online backup)
Minimum Impact on the DB performance.
Minimum restore time.
Allow PITR(Point-In-Time Recovery)

Can we execute backup with above criteria on slave server of replication?
If you know backup strategy using storage snapshot, Could you let me know?


texts:
Currently my backup strategy invloves :-

1)I have one master server and two slaves (Data approximately 700 GBs and tables are of mixed innodb and myisam).

2)I take weekly and monthly backup from slave through mysqldump.

3)In case of slave failure ,will copy data from another slave.

My question is  should i change this backup strategy ??
Should i use percona xtrabackup and if yes why ??


texts: Insert into table if column  "Text" for each project

texts:
I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per project. I have tested this and works fine. Now I need one more trigger make an insert check check if project exists, then don't trigger, but in addition this insert query need to check if Periodcheck is equal to "Later" for each project, if a row like that doesn't exists, then it should add a new row in my fact table. Tried a few different things, but can't seem to put it all together without creating duplicate rows for project and Periodcheck.
dimtable table looks like this (lets pretend project 2 is just added):

ProjectID
Status

1
A

2
A

Factable like this where project 1 is correct, but project 2 is missing the last row wiht Periodcheck = 'Later':

ProjectID
Status
Period
Periodcheck

1
A
202106
NULL

1
A
202107
NULL

1
A
202108
NULL

1
A
202109
NULL

1
A
202110
NULL

1
A
202111
NULL

1
A
202112
NULL

1
A
202201
NULL

1
A
202202
NULL

1
A
202203
NULL

1
A
202204
NULL

1
A
202205
NULL

1
A
205001
Later

2
A
202106
NULL

2
A
202107
NULL

2
A
202108
NULL

2
A
202109
NULL

2
A
202110
NULL

2
A
202111
NULL

2
A
202112
NULL

2
A
202201
NULL

2
A
202202
NULL

2
A
202203
NULL

2
A
202204
NULL

2
A
202205
NULL

This query below is supposed to only add one row with Periodcheck = 'Later' for projet 2, since that row does not exist in my facttable.
CREATE TRIGGER project_insert
ON dimtable
AFTER INSERT
AS
BEGIN
Insert into facttable (ProjectID, Status, Period, Periodcheck)
SELECT
   ProjectID
 , Status
 , Period = '205001'
 , Periodcheck = 'Later'
FROM dimtable d
  WHERE EXISTS(Select ProjectID FROM facttable f
        WHERE d.ProjectID = f.ProjectID)
  and
   NOT EXISTS(SELECT Periodcheck FROM
        facttable f
      WHERE t2.Periodcheck = 'Later')
END

End goal is to add this row to only projects that does not already have this row in my facttable. Any suggestions on how to change the query to do this?
It runs, but I have tested by adding a project to dimtable and this trigger does not add my desired row.
Thank you so much!


texts: Remove accidental duplicates in PK table, cascade changes to FK table

texts:
Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants.  There is a PK-FK relationship between plants.id and samples.tree, and a constraint on table plants that every genus-species combination must be unique.  But some, of the entries in table plants are actually just misspelled versions of other records in the same table.  So:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
2   |  Acer    | rubra
3   |  Quercus | albaa
.....

Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 2    | 16
4      | 2    | 34
5      | 3    | 20
...

Table: plant_name_mapping

oldgenus | oldspecies | newgenus | newspecies
-----------------------------------------------
Acer     | rubra      | Acer     | rubrum
Quercus  | albaa      | Quercus  | alba
...

I want to update table plants based on table plant_name_mapping.  If the update creates a new name that wasn't in plants already, great.  However, if the update creates a name that is already in the table plants, I want to delete that duplicate record, and than change the foreign key in the table plants to match the name that was originally there all along.  So the output should look like:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
3   |  Quercus | alba
.....

Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 1    | 16
4      | 1    | 34
5      | 3    | 20
...

I'm working with a postgresSQL-9.3 database.  Obviously I have many more records than this example.  Let me know what kind of update statement or stored procedure could do this.  I'm having trouble describing what I want to do, but I think the examples illustrate it pretty clearly.  Thanks for the help.


texts:
My company is changing from a distributed Access Database model to using a centralized SQL Database.  The datatables were designed such that all of the tables have a modified date.  In discussion it was suggested that since we will be creating a trigger on each table to handle the modified date perhaps we should have the trigger also log some information to an audit table.

Is this the best way to setup auditing so that we can track who is changing information or is there a better way?  Links to articles on the subject are welcome.

For the audit I'm looking at capturing the table name, column name, date modified, row id and the username of the person making the change.  Is there any information I'm not thinking of that I should be capturing that might help me avoid future pit falls?


texts: Copying my Oracle 10g Express database to another PC

texts:
I have Oracle 10g Express. How can I make a copy of my database and application?

I don't want to make a back up, I want to move my DB and application to another PC.


texts: I am working on a project that involves importing data via Apex's data workshop. Two of my columns contain dates in the format YYYY-MM-DD HH:MI:SS. Can anyone tell me how to get Apex to import these columns correctly.


texts: MySQL - When Command column in Processlist shows "Prepare" or "execute"

texts:
Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist"

Any example which shows this would be helpful.


texts: Unable to save a stored procedure which contains a temporary table creation and update query

texts:
I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query.
Here is my query:
CREATE OR REPLACE PROCEDURE account_report_v2()
LANGUAGE SQL
AS $$
    BEGIN;

        CREATE TEMP TABLE report_data on commit drop AS (select * from tests);

        update report_data set
            df_name = dfn,
            dl_name = dln
        From
        (
            select  id  ,first_name dfn,last_name dln from df_info
        )AS subquery
        where (report_data.provider_id :: varchar(50)) = (subquery.id :: varchar(50));

    COMMIT;
$$;

CALL account_report_v2();

When I try to save the procedure, postgres gives the following error:

relation "report_data" does not exist.

Here is how it looks like in pgAdmin 4:

Please advise


texts: Pre-existing shared memory block (key 5432001, ID 32768) is still in use

texts: I have been seeing this error lately that is causing the server to crash all of a sudden. I have seen elsewhere that the solution is to delete the postmaster.pid and postmaster.opts files and restart the server, but no one has explained why this event happens, and how to stop it from happening again.


texts: Performance impact of having gaps in Identity Column in SQL Server 2005

texts:
We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there are now gaps in the primary key column.

Is there any impact to the performance of the clustered index on these tables as a result of these "missing" identities?

From my understanding as long as the index b-tree is refreshed then there shouldn't be any problems, but I can't find anything specific to confirm this.

Thanks


texts: Live update index in production database

texts:
I have read Perform Index Operations Online provided by MSFT but I couldn't find any detail information for my questions.

My questions are:

What is the impact when I update (create/drop) non-clustered index in the live database?
If the database cannot be shut down, is there any specific way to reduce the risk and impact?


texts: Set replica set member host names on Secondary

texts:
I have a replica set rset1 that consists of a primary, secondary, and a backup server.

I recently just changed their DNS names in Route53--the only name that didn't change was the Primary's, however since it can't connect to the secondaries, it stepped-down from being Primary.

I've been trying write a query to use in an update of local.system.replset, where local is the database and system.replset the collection; I can't seem to get anywhere with it, though.

Here's what system.replset looks like:

{ "_id" : "rset1",
  "version" : 7,
  "members" : [
    { "_id" : 0,
      "host" : "mongo-rs1-p.foo-bar.com:27017",
      "priority" : 3
    },
    { "_id" : 1,
      "host" : "mongo-rs1-s1.foo-bar.com:27017",
      "priority" : 2
    },
    { "_id" : 2,
      "host" : "mongo-rs1-s0.foo-bar.com:27017"
    }
  ]
}

I need to change members to this:

 [
   { "_id": 0,
     "host": "mongo-rs-p.foo-bar.com:27017",
     "priority": 3
   },
   { "_id": 1,
     "host": "mongo-rs1-s.foo-bar.com:27017",
     "priority": 2
   },
   { "_id": 2,
     "host": "mongo-rs1-b.foo-bar.com:27017"
   }
]

I've tried using:

cfg = rs.conf()
cfg.members[1].host = "mongo-rs1-s.foo-bar.com:27017"
cfg.members[2].host = "mongo-rs1-b.foo-bar.com:27017"
rs.reconfig(cfg)

But since the primary is no longer primary, I can't get this to run successfully, and running db.getMongo().setSlaveOk() didn't help with using rs.reconfig()

As a last resort (I've had this problem before) I can run rm -rf /data; mkdir -p /data to remove mongo's data directory (after killing the mongod process) and then I can start the mongod server again and rebuild the replica set from scratch, but I'd rather not do that.

So how can I update the members of the replica set without deleting the database on a primary that  stepped down?


texts: Backup databases based on wildcard on database name

texts:
We have a MS SQL 2016 databse server with a number of databases that need backing up.  We are using a maintenance plan to backup all databases at the moment but running low on space on one of our drives.

We have the following databases:

foo1
foo2
..
..
foo10
fred01
fred02

How would we go about swapping out the T-SQL in the maintenance plan task to only backup the databases foo* and not the fred databases?

I could select the databases manually but over time new foo databases are added and I want to ensure that these are automatically backed up without needing to update the maintenance plan.

Thanks


texts: Lock time not involved in MySQL slow query selection?

texts:
I wish to analyze some read-write contentions problems on a very huge table where writes are very fast, less than 1ms in most case. The point is that the contention makes those write queries sometimes wait for the lock for a long time, i.e. hundreds of milliseconds. But the query itself remains very fast so it's not selected as a "slow query", which threshold is actually 10ms.

The only way to gather the information I need is to put à 0ms threshold so I can capture all queries, but it's not sustainable for more than 10mn on our production environment, where it would produce about 200MB of logs per minute.

In fact I can't understand why MySQL don't include the lock time, added to the execution time, for the slow query log selection process. At least, I wish there were an option for that.

Am I missing something ?


texts: Can I replace these left joins with a union or some other optimization?

texts:
I'm trying to optimize a query that returns the membership of objects in two groups.  Group "a" is a one-to-one link table (ie. object_id is a unique foreign key in group_a) and group "b" is one-to-many (object_id can have multiple entries in group_b).  Here is a simplified version of the query:

-- show objects and their membership w/ LEFT JOIN
SELECT DISTINCT o.name,
    a.object_id IS NOT NULL as in_group_a,
    b.object_id IS NOT NULL as in_group_b
FROM object o
LEFT JOIN group_a a ON (a.object_id = o.object_id)
LEFT JOIN group_b b ON (b.object_id = o.object_id)
WHERE a.object_id IS NOT NULL
OR b.object_id IS NOT NULL;

It returns something like this:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

Note that Barney is in both groups but only one row is returned; this is critical.  I tried to rewrite this query with a union:

-- show objects and their membership w/ UNION
SELECT o.name, tmp.in_group_a, tmp.in_group_b
FROM ((
    SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
    FROM group_a a
) UNION (
    SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
    FROM group_b b
)) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id);

This query is 10 times faster but I get multiple rows for objects in both groups:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          0 |
| Barney |          0 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

I'm guessing the original query is slow because it starts with the object table, which is large and most entries are not in either group.

Is it possible to make the union query return the results I want, and if not is there another way to optimize this query?

FYI, here are my simplified example tables and data:

CREATE TABLE object (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (object_id)
);

CREATE TABLE group_a (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

CREATE TABLE group_b (
    group_b_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    description VARCHAR(64) NOT NULL,
    UNIQUE KEY (group_b_id),
    KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

INSERT INTO object VALUES (1, "Fred"), (2, "Barney"), (3, "Wilma"), (4, "Betty");
INSERT INTO group_a VALUES (1), (2);
INSERT INTO group_b VALUES (1, 2, "a"), (2, 4, "a"), (3, 4, "b"), (4, 4, "c");

Thanks!


texts: How to identify cross-database queries in SQL Server

texts:
In preparation for a migration project we are trying to identify any queries that select data across databases.

This can be of the form:

SELECT fieldA, fieldB
FROM dbo.TableA a
JOIN DatabaseB.dbo.TableB b ON b.ID = a.ID

or

DECLARE @resultFromDatabaseB VARCHAR(50)

SELECT @resultFromDatabaseB = b.ID
FROM DatabaseB.dbo.TableB

SELECT fieldA, fieldB
FROM dbo.TableA a
WHERE a.ID = @resultFromDatabaseB

or any other variation (including INSERTS/UPDATES/etc...)

Unfortunately the majority of queries are not stored procedures so I cannot do static analysis of the SQL text, my current plan calls for recording the cached query text and doing static analysis on them.

Are there any alternatives to this approach?  ie: use of extended events/trace/audit?


texts: Will PostgreSQL server work if I install the service in mounted partition

texts:
I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).

I am facing problem with memory since my root partition having only 10 GB. I have verified the data folder (/opt/postgresql/9.1/data/), and noticed few things.

/opt/postgresql/9.1/data/base directory having more data
pg_largeobject table is having more data

So, I planned to do following activity,

Take the full backup of existing database(s) - custom format (*.sql)
Uninstall the postgresql from the root directory, using uninstall.sh (which is available the postgresql path)
Install the new postgresql in mounted path (/newly/mounted/remote/path/postgresql/)

If I do above steps, will postgresql service start and work without any issue?
(if no issue, I will restore the database backup)

Any suggestion?

(using postgresql-9.1)


texts:
I want to create a trigger such that whenever a user is inserted in
TABLE A(userid,type) then his "userid" along with some predefined value, depending in his "type", is inserted in TABLE B(userid,value)

For example in TABLE A if following value is inserted:

userid  |  Type

23      |  admin

then, depending on condition if Type=admin value=1 OR if Type=user value=2, the following insertions are made:

TABLE B
userid  |  Value

23      |  1

Is this possible?


texts: SQL Server created a new errorlog file but instance and agent were not restarted

texts:
One of our jobs failed but the job history had already expired by the time we investigated. We could, however, see that a new log file was created around the time it failed.

There was no instance restart (checked os_sys_info) or agent restart (looked in sys.processes) and nobody using it knows how to cycle the log.

Must it have been a corruption of some kind?


texts: Copying mysql data from one db to another with a where clause for a subset of tables

texts:
I'd like to be able to move mysql data from one db to another. I only want to move records where account_id=1234 except for tables like account which does not have an account_id column.

Is there any possible way to do this?


texts: How to select multiple columns but only group by one?

texts:
I have a problem with group by, I want to select multiple columns but group by only one column. The query below is what I tried, but it gave me an error.

SELECT Rls.RoleName,Pro.[FirstName],Pro.[LastName],Count(UR.[RoleId]) as [Count]
from [b.website-sitecore-core].[dbo].[aspnet_UsersInRoles] UR
inner join [b.website-professional-au].[dbo].[Profile]  Pro
on UR.UserId = Pro.Id
inner join [b.website-sitecore-core].[dbo].[aspnet_Roles] Rls
on Rls.RoleId = UR.RoleId
inner join [b.website-professional-au].[dbo].[Gender] Gn
on gn.Id = pro.GenderId
GROUP BY Rls.RoleName;


texts: Duration of adding versus dropping a column

texts:
I have a table containing more than 50 million rows.

When I add a column:

ALTER TABLE my_table ADD my_column NUMBER;

It's very fast.

Just after adding the column, I drop it.

ALTER TABLE my_table DROP COLUMN my_column;

It takes 3 minutes!

Why is adding a column so fast, when removing it without populating it is slow?


texts: Is it ok to let users add new columns and tables to your database?

texts:
I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can then go in and, through an interface, add entries to the database. The point is to create a management system for information within the company. As in, the administrator could create a "Employ" database to store workers with the correct columns and such.

I have it in my head that I should never let users create new tables (which is what a "database" would be) or even columns. I created two tables to store the information that defines what a database/table should be from the users perspective, and two more tables to store the entries and data for those entries.

I feel a bit like I'm reinventing the wheel. Is it ok just to let users add new tables.


texts: Sql Server - Build a json array of integer from an aggregate

texts:
I want to use an aggregate to build a simple single-dimensional JSON array of scalar values, like the LuckyNumbers array in the example below:
[{
    "id": 1,
    "name": "Josian",
    "LuckyNumbers": [581, 777]
}, {
    "id": 2,
    "name": "Paul",
    "LuckyNumbers": [123551, 5, 646464, 1345, 75, 76]
}, {
    "id": 3,
    "name": "Seasonique",
    "LuckyNumbers": [1]
}]

With Postgresql 12, you can do it using native aggregate functions ( fiddle )
--Postgresql 12 schema
create table person (   Id int primary key,   name varchar(20) );
insert into person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');
create table LuckyNumbers (PersonId int references person(Id), LuckyNumber int);
insert into LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1);

--Query to JSON
select
  array_to_json(array_agg(row_to_json(r))) "PersonsNumbers"
from (
    select
      p.id,p.name,json_agg(ln.LuckyNumber) "LuckyNumbers"
    from person as p
      inner join
        LuckyNumbers as ln
        on
          p.id=ln.PersonId
    group by p.id,p.name
  )
  r

This will output the desired result, exact as above.
The SqlServer2019 example below however requires the use of string_agg which is a string function and then manually concatenating array brackets. ( fiddle )
--Sqlserver 2019 schema creation
create table person (   Id int primary key,   name nvarchar(20) );
insert person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');
create table LuckyNumbers (PersonId int foreign key references person(Id), LuckyNumber int);
insert LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1);

--Query to JSON
SELECT p.id
    ,p.NAME
    ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
FROM person p
INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
GROUP BY p.id
    ,p.NAME
FOR json path

--JSON to Query : OPENJSON and out apply allow you to read back the array !
DECLARE @JsonOutput NVARCHAR(max) = (
        SELECT p.id
            ,p.NAME
            ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
        FROM person p
        INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
        GROUP BY p.id
            ,p.NAME
        FOR json path
        );

SELECT id
    ,NAME
    ,luckynumber
FROM openjson(@JsonOutput) WITH (
        id INT 'strict $.id'
        ,NAME NVARCHAR(50) '$.name'
        ,LuckyNumbers NVARCHAR(MAX) '$.LuckyNumbers' AS JSON
        )
OUTER APPLY OPENJSON(LuckyNumbers) WITH (LuckyNumber NVARCHAR(8) '$');

Is there a better way to do this with Sqlserver ? Did I miss a native aggregation function similar to Postgre's json_agg ?


texts: Multi-homed SQL Server with High Availability Groups

texts:
We have two servers (SQL-ATL01, SQL-ATL02) that make up a Failover Cluster, each running as part of a  SQL Server High Availability Group (HAG). Each server has two network cards. One is a 10Gbit card that is directly connected to the other server and is used for Synchronizing the HAG on a 192.168.99.x subnet. The other is a 1Gbit card that is used to connect the DB servers to a switch to communicate with the application servers on a 10.0.0.x subnet. The Listener is pointed to the  192.168.99.x subnet.

We want to add a third server (SQL-NYC01) in another physical location to the cluster and run it as an Async replica part of the HAG, but the VPN only routes traffic on the subnet on the 1Gbit network.

Is there any way to set up the Failover Cluster and High Availability Group to tell it:

Send synchronous replica traffic for SQL-ATL01 <--> SQL-ATL02 over 192.168.99.x
Send asynchronous replica traffic for (SQL-ATL01, SQL-ATL02) <--> SQL-NYC01 over 10.0.0.x

Or do we have to have all replica traffic going in and out on the same IP address/subnet?


texts: How to write a query for picking 20 items from six tables with two conditions

texts:
I have six tables.

candidate,candidate_joborder,company,extra_field,joborder,user

I want to pick up 20 items from these tables with query those items are as follows.

name from company table.
title,city from joborder table.
status from candidate_joborder.
candidate_id,first_name,phone_cell,city,source,notes,current_employer,date_created,email1,desierd_pay,current_pay from candidate table.
user_name from user table.

Where as extra_fields i want to pick up

total_experiance,relevent_experiance,notice_period,education

These are user entered data i am picking up using this query.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id

And the where condition is this.

WHERE entered_by = $user_id AND date_created LIKE '$date%'

In these tables.

extra_field.data_item_id = candidate.candidate_id
candidate.entered_by = user.user_id
candidate_joborder.candidate_id=candidate.candidate_id
joborder.companycompany_id=company.company_id
joborder.entered_by=user.user_id
company.company_id=joborder.company_id

And the fiddle is here.
https://www.db-fiddle.com/f/gWr5g2222yAE8Fh1Gc6Awf/5


texts: How to check who has permission to execute a stored procedure?

texts:
I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple stored procedures on DATABASE_B. Most of those procedures are in the dbo schema, but one procedure is in another schema, say SCHEMA_X. That procedure reads from various tables in dbo and then truncates a table in SCHEMA_X before repopulating that table. That table and that procedure are the only two items in SCHEMA_X.
Now - all the above is applicable to both environments.
If I go to SSMS > relevant server > DATABASE_B > Programmability > Stored Procedures > SCHEMA_X.stored-proc-name > right-click > Properties > Permissions
Then nothing is listed. If I click to view schema permissions, again nothing is listed. This is again in both environments, but in one environment a client application presents an error saying it does not have permission to execute the stored proc. I tried brute force by granting the client's user group execute permissions on that schema. That seemed to take things further as the next error related to being unable to see the table, or not having permissions to access the table - which I could probably resolve by granting further permissions around insert/delete/update on that schema.
But my point is that initially both environments presented in the same way - at least in that spot that I checked (permissions against the stored procedure). So how should I debug this issue to determine why a client program in one environment is able to execute the stored proc (and effect the truncate/insert into the table) while the other reports it does not have permission? Is there no single comprehensive query that lists for you all permissions within a database?


texts: MySQL / InnoDB: Does daily optimization improve performance?

texts:
Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, and it looks promising.

PS: As I learned, OPTIMIZE TABLE liberates overhead. However, I double checked the overhead column in mysql and it is all empty for my innodb tables ... So, I guess this may not be necessary.


texts: Design best practices for last tracking with indexed view

texts:
My tables structure is below :

  TbDoc (ID int , ...)
  TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)

I want to have indexed view to get last TbDocActions columns for each TbDoc record.
result of this view must be such as below :

  DocID , col1, col2, ...

For get this result with view I can use below query:

Select Z.DocID, X.*
From (Select DocID, Max(ID) as MaxActionID
      From TbDocActions
      Group By DocID
     )Z
inner join TbDocActions X ON X.ID = Z.MaxActionID

but I want indexed view to have better performance. and in indexed view I can't use Max() aggregate function.


texts: many Servers of SQL server 2012 needs mirroring

texts:
I have two SQL server 2012 instances and I want to configure mirroring between the 2 servers with database.

My questions are:

How can it automatically change host of one if the other one was dropped ?

Is the performance of mirroring is acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?


texts: Mirrior DB cannot be recovered after switching off the partner off on the primary

texts:
Primary db (ldf) log is huge transaction is getting killed/rolled back.
we want to drop the mirroring  and recover the mirrored db.
we set the partner off both on primary n secondary. db could not recovered saying
mirrorin still there.

how to solve this.


texts:
I try to select distinct combinations of rows (author_id, recipient_id), preserving following order and limit. Like on Facebook you have a list of recent conversations with given users. For example I'm a user with id=665 and I need a list of 20 recent conversations, 20 last distinct people a user with id=665 has talked. My current code:

select * from (
  select distinct on (author_id, recipient_id) * from private_message where author_id=665 or recipient_id=665
) p_m
order by id desc
limit 20;

In this case everything is ok besides that I want to keep bigger id (more recent), so message with id=10 needs to be drop, because it's duplicate of id=18.

So the expected result is:


texts: How to save database connection information when using `psql`?

texts:
I am learning to use psql to work with a postgres database on linux.
I can connect using a long connection string, for example by running:
psql postgresql://myusername:mypassword@localhost:5433/dbname
However, I would like to save the details of my connection string somewhere, to simplify the command to connect to the database.
I've added the parameters to ~/.pgpass, and I've also changed the environment variables PGDATABSE, PGHOST, PGPORT, and PGUSER.  However, I'm not sure how to tell psql to source connection information from either ~/.pgpass or the environment variables.  When I just type psql I get an error, and I can tell from the details of the error that it is trying to connect using default parameters, like port 5432 instead of my specific port.
So how to I tell psql to use the parameters in ~/.pgpass or the environment variables?  Or more broadly, how can I save my connection information to simplify the command to connect in the future?


texts: Postgres Minor Version Upgrade from 11.4 to 11.7

texts:
I am new to PostgreSQL database. I want to upgrade the PostgreSQL version from 11.4 to 11.7. what are the steps involved can anybody tell. It will be helpful if anyone can share SOP.

Thanks


texts: Designing a friendships database structure: Should I use a multivalued column?

texts:
Say I have a table called User_FriendList, which has the following characteristics:

CREATE TABLE User_FriendList (
    ID ...,
    User_ID...,
    FriendList_IDs...,
    CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID)
);

And let us suppose that said table holds the following data:

 +----+---------+---------------------------+
 | ID | User_ID | Friendlist_IDs            |
 +----+---------+---------------------------+
 | 1  | 102     | 2:15:66:35:26:17:         |
 +----+---------+---------------------------+
 | 2  | 114     | 1:12:63:33:24:16:102      |
 +----+---------+---------------------------+
 | 3  | 117     | 6:24:52:61:23:90:97:118   |
 +----+---------+---------------------------+

Note: The “:” (colon) is the delimiter when exploding in PHP into an array.

Questions

So:

Is this a convenient way to “store” the IDs of a FriendList?
Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?


texts: SQL join on col1 if present else use col2

texts:
There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.
What I want is either join on T1.col1 = T2.col1 or T1.col1 = T2.col2 if col1 is NULL in T2.
I have already tried these:
select * from T2 left join T1
on T1.col1 = coalesce(T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 in (T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 = T2.col1
or T1.col1 = T2.col2)

Which result in the ETL job never ending.
Additional info:

Redshift DB
No indexes because columnar DB
T2.col1 and T2.col2 will be ditto. T2.col1 comes from upstream, whereas T2.col2 is derived from the ETL. Until upstream quality improves, we'll need fallback on col2.


texts: List all database containing table and value

texts:
in one of our SQL Servers we have several databases attached and most of them contain a little table, let's say dbo.DBType and that table will always contain 1 record with two columns, first column tells me if the DB is of type A or B (only A and B are allowed).

is there a way I can query the master db of SQL Server and retrieve a list of all databases of type A or type B ?

I know I could connect inside a loop to each of them and check the containewd DBType table but was thinking


texts: How do I normalize a database with lots of many-to-many relationships?

texts:
I am building a database that is based around a business project, in this case called a "Program." Each Program will have multiple Reports, Clients, and Partners.

My first thought was to place each Program, Report, Client, and Partner into their own table and then use a linking table for the Report, Client, and Partner tables to the Program table's primary key.

Can I link all of them with a single table by just making repeat entries? I am not sure if this even makes sense, I am kind of lost as to how one descirbes an ERD in a post like this.

ProgramTable

ProgramID

ReportTable

ReportID

ClientTable

ClientID

PartnerTable

PartnerID

LinkTable

ProgramID (FK)
ReportID
ClientID
PartnerID

My concern is that because a Program may have multiple Reports, Clients and Partners, that I cannot do this in one link table and istead need 3 link tables, one for each. Any help would be great... sorry if I am not to clear on this.


texts:
I'm new in DB world, so if you can help me it would be great.

My problem:

I have filtered some data and got a table with data, but need to filter them again. Picture might help you. For me, it is pretty complicated. (I made this tables / picture just to illustrate my problem, I'm not allowed to post real data because they are business secret)

I have data in Table 1 (HAVE), and need to get data in table 2 (WANT). Need to take data from first table, but result is based on first appearing in table 1 based on name. When I  "find" new "name" (in real life there are some other data, but it is not matter because it is also string), take that row and put it in new table. Then second name, third name ... it depends how many different names are in first table.


texts: Cross-talk with temp tables with SSRS over a shared data source?

texts:
I have a data source that is setup to store credentials on the report server and to use them when running reports. Given the following factors, I'm concerned about reports that use temporary tables in the SQL query text (not in a store procedure):

Due to SQL Server's connection pooling, reports that use this data source will all share a single connection (assuming they are being run near simultaneously).
Temporary tables are scoped on a per-connection basis.

For example, if User A runs the report with one set of parameters, the temp table will be populated with one set of data. If User B runs the same report at the same time with different parameters, the query will populate the temp table with a different set of data, thus possibly mangling User A's report.

Will the data in the temp table be shared across instances of a report and cause incorrect results?