texts: Mapping an MSSQL Login to the secondary replica in a High Availability Group

texts:
I'm trying to map a login to a user in a database on the secondary replica of a two node High Availability group.  Obviously this fails since the replica is offline.  Is there a way for me to create the login mappings to users to the offline database?  I'm using Windows domain accounts BTW, so no SID issues, just the mapping problem.

When connecting to the secondary, after a failover, the error is that "the users' default database could not be accessed." The default database is the one where on the primary I set the login to have the correct database mappings, and the correct default database. When the failover happens, the default database is correct, but the user is not mapped to the database. At all. I know these mappings are at the database level which is why this is throwing me for a loop.


texts: Running a query on the results of another query?

texts:
I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase

For example, the following query works to count the number of times/rows a each value shows up in the 'referred_link' column of the cases table, and show me only those that appear 3 or more times:

SELECT cases.referred_link, count(*)
FROM CASES
GROUP BY referred_link
HAVING COUNT(*) >= 3

The next query pulls a bunch of data from various tables and filters down on the required criteria. The catch is, I only want this 2nd query to run on the results from the first query... items which show up 3+ times on the referred_link table.

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='home_addr') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'

I tried something like this (doesn't work, of course):

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'
and names_id IN (SELECT cases.referred_link, count(*)
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)

Can anybody point me in the correct direction for how this could be accomplished?


texts:
I am a junior DBA, and I am still confused by database scope.

Now I am learning about local/global temp table and table variables. I saw an example as following:

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exe('select col1 from @T1;');

go

When I run it in SQL Server, I got below message:

  (1 row(s) affected)
  Msg 1087, Level 15, State 2, Line 1
  Must declare the table variable "@T1".

The message means the insert statement was successful while the exec failed. I am confused because:

I don't know why SQL Server considers the @T1 declaration and the insert statement as one block. My colleague told me to regard them as a stored procedure. But I thought a stored procedure should at least follow DECLARE...BEGIN...END format.
There is another example right after this one in book:

declare @T1 as table
(
     col1 int not null
);

insert into @T1(col1) values (10);
go

exec('select col1 from @T1;');
go

And it gave me same error. I don't know what the book is going to show me by using these 2 examples. Or in another word, I don't understand the use of "GO".

Can anyone help? Thanks so much.


texts: How to compare performance of two versions of a function?

texts:
I just saw this function definition:

create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
        not exists (Select * from t1 where t1.col1 = @a)
        AND @b > 0
        then 1 else 0 end
end
GO

Seeing a not exists I thought attention full table scan and tried to improve it

create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
        exists (Select * from t1 where t1.col1 = @a)
        OR @b > 0
        then 0 else 1 end
end

GO

My feeling is, that this transformation could have been done by an optimizer. It seems to be straight forward, but how can I be sure if he does?

Comment on Igor's answer:
(comparison fixed thanks to Matts comment)

This inspires me to the following:

create function dbo.f (@a int,  @b int)
returns integer
as
begin
 IF @b <= 0
   RETURN 0

 IF exists (Select * from t1 where t1.col1 = @a)
   RETURN 0

  RETURN 1
end
GO


texts: Postgres query very slow when using index

texts:
So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records.

Here's the query that I run:

SELECT  "organisations"."id", "organisations"."url"
FROM "organisations"
WHERE "organisations"."url" IS NOT NULL
AND "organisations"."domain" IS NULL
ORDER BY "organisations"."id" ASC
LIMIT 100

And here is the explain analyze

Limit  (cost=17468.48..17468.73 rows=100 width=38) (actual time=517448.061..517449.431 rows=100 loops=1)
  ->  Sort  (cost=17468.48..17482.23 rows=5501 width=38) (actual time=517448.057..517448.791 rows=100 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 38kB
        ->  Bitmap Heap Scan on organisations  (cost=193.31..17258.23 rows=5501 width=38) (actual time=93.370..516988.919 rows=1096205 loops=1)
              Recheck Cond: ((url IS NOT NULL) AND (domain IS NULL))
              Heap Blocks: exact=59205
              ->  Bitmap Index Scan on index_organisations_on_url_and_domain  (cost=0.00..191.93 rows=5501 width=0) (actual time=82.242..82.243 rows=1098765 loops=1)
Planning Time: 0.202 ms
Execution Time: 517453.118 ms

I use rails - here is the partial multi column index from the schema file:

t.index ["url", "domain"], name: "index_organisations_on_url_and_domain", where: "((url IS NOT NULL) AND (domain IS NULL))"

If it helps this is running on Postgres RDS on a small instance. There are no other connections running as I turned off all background jobs, and there are no users connected using the app at the time of running.

I'm not sure how I can get this to return faster.


texts: How to avoid Recovery Catalog's metadata being purged after 60 days?

texts:
I'm using Oracle's Recovery Catalog to track my db's backups metadata (RC ver. is 12.01.00.02 )

I need to collect historical backup sizes but then I've realized there's no metadata information about completed backups beyond 60 days. (however, there's for FAILED and incompleted backups)

e.g. I'm trying to collect this data from rc_rman_backup_job_details view. This RC's was used since Q1 2015, but only 60 days of completed backups are shown.

RMAN@SERESCATALOG> SELECT status, MIN(start_time)
      2  FROM rc_rman_backup_job_details
      3  GROUP BY status
      4  ORDER by status;

STATUS      MIN(START_TIME)
----------- --------------------
COMPLETED   03-SEP-2016 01:31:34
FAILED      18-SEP-2015 09:03:50
RUNNING     28-MAY-2015 02:39:36

What controls this retention? There's any way to change it? Any documentation?

Regards.


texts: Out of memory issues on SQL Server 2012 SP3

texts:
I've been having periodic SQL Server OOM errors, one time to the point that SQL Server shut down itself and always happened during night time, when no one's using it, and no SQL Agent job running at that time:

Here is the typical error:

  08/17/2017 19:31:17,spid100,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

  08/17/2017 19:31:17,spid100,Unknown,Error: 701 Severity: 17 State: 123.

  08/17/2017 19:31:17,spid112,Unknown,Error: 18056 Severity: 20 State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing ETW notifications etc are skipped.

Here is the server info:

10GB MIN SQL server memory
21GB MAX SQL server memory
only 4 DBs on the server
their sizes are only 1 to 2 GB each
Tempdb size never grew to more than 1GB (set to auto grow to 10GB)
Indexes are all low frag, stats updated
Version:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I checked multiple items:

DBCC memorystatus
Quick stats:

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

Output:
20 GB
Page Life Expectancy                     155932

No resource governor enabled:

select pool_id, cache_memory_kb, used_memory_kb,
       out_of_memory_count,used_memgrant_kb
  from sys.dm_resource_governor_resource_pools

Output:

pool_id | cache_memory_kb | used_memory_kb | out_of_memory_count | used_memgrant_kb
--------+-----------------+----------------+---------------------+-----------------
      1 |          295368 |         641416 |                   0 |                0

select (physical_memory_in_use_kb/1024) Memory_usedby_Sqlserver_MB,
       (locked_page_allocations_kb/1024) Locked_pages_used_Sqlserver_MB,
       (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
       process_physical_memory_low,
       process_virtual_memory_low
  from sys. dm_os_process_memory

Output:

Memory_usedby_Sqlserver_MB | Locked_pages_used_Sqlserver_MB | Total_VAS_in_MB | process_physical_memory_low | process_virtual_memory_low
----------------------------------------------------------------------------------------------------------------------------------------
                     20553 |                          20393 |       134217727 |                           0 |                          0

Server is on SP3, I know there was a memory leak issue in SP1, so ruling that out.

Anyone notice anything in DBCC result that I should focus on?


texts: SQL Server Migration using a DB Mirror (Rolling upgrade)

texts: I'm planning to do SQL Server migration of SQL Server 2012 to a new hardware and a new version of SQL Server (2016). I'm trying to find the best solution with minimal downtime. I found some suggestions on the internet that in theory sounds great. I'm talking about DB Mirroring and Rolling Upgrade. But I also have read that MS does not recommend DB Mirroring between different SQL Server versions. So now I'm confused should I or shouldn't use DB Mirroring for this. Any suggestions? Have anybody tried that method before? I'm also thinking about Log Shipping.


texts:
situation is that, i want to prepare datasets for front end chart. the raw data from table contains
LAC | FLUID_MERIT|FA_BDATE   |GRP |FARM_FK
------------------------------------------
0   |234.56      |2020-01-01 |12  |10048
1   |234.56      |2009-01-01 |13  |10048
10  |234.56      |2020-01-01 |13  |10048
0   |234.56      |2020-01-01 |13  |10049
2   |234.56      |2009-01-01 |12  |10049
3   |234.56      |2009-01-01 |12  |10048
0   |234.56      |2020-01-01 |12  |10048

so to prepare the average dataset i want is group by LAC and beside there is condition for LAC = 0 where it must be separated by month range. the query below is a sample
select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6

union

select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12

union

select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12

union

select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl
where FARM_FK = 10048 and LAC != 0)
group by LAC

union

select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12

when i use union to merge them i could not use order by in the 4th query and also it sort by LAC as a varchar data so lac 10 comes after 1 since it is a varchar as shown below
YDS           | DataSet
------------------------
0 ( 01-06 M ) | 117.78
0 ( 07-12 M ) | 465.26
0 ( 12 < M )  | NULL
1             | 292.58
10            | -62.55
2             | 321.40
3             | 278.24
4             | 308.68
5             | 267.48
6             | 229.36
7             | 165.18
8             | 105.14
9             | 65.68
TOTAL         | 149.95

is there any way to sort properly the 4th query


texts: What kind of changes does snapshot file store?

texts:
Assume that I created a database called [DBS] and inside that database I created a table called [products].
Then I inserted 4 rows into [products].
Afterwards I took a snapshot of [DBS]. As a result, sparse file Snapshot_DBS.ss was created in my drive and in its properties, the size of file was 8 MB whereas the size on disk was 128KB (see attachment).
Let's go to the interesting part.
Whenever I made either INSERT or UPDATE operations on that table the size of Snapshot_DBS.ss on disk kept growing.
In the internet I found such an explanation: A snapshot contains the original version of pages created and changes in the source since the snapshot was created.

My 1st question is, what kind of changes does snapshot file store? Is it acting like t-log (storing every single changes made to [DBS])?

The 2nd question is, the part where it states "...and changes in the source since the snapshot was created" reminds me the working principle of Differential backup where latter takes all t-logs since the full backup was taken. Is their working principle similar somehow?


texts: Log shipping - Bulk logged recovery mode doesn't log bulk operations, yet it is allowed for Log Shipping config? Why?

texts:
Log shipping is one of methods for high availability/disaster recovery. It works by transferring the transaction logs from primary to the secondary.

Log shipping needs that the database should be either in full recovery mode or bulk logged recovery mode.

My questions is: Since the Bulk logged recovery mode doesn't log the bulk operations, then why is it allowed in log shipping? Doesn't this mean it violates the principle of high availability/disaster recovery because the secondary database will be missing all the data that was added into primary via bulk operations (as these are not written to the transaction log)?


texts: Postgresql filtering by value over rolling time period

texts:
I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense.

I have got the following data set:

I need to find those customers whose amount > 2 for 4 minutes in a rolling fashion, considering the data for a day. By that I mean in the given data set, customer 1 is one such as it has got amount > 2 at 16:01,16:02,16:03,16:04 and then at 16:02,16:03,16:04, 16:05. If there is any such single occurrence, the customer needs to be selected. Customer 2 doesn't have so. Wondering what's the best approach to do this?

Any help will be much appreciated.

Regards,


texts: PostgreSQL conditional join and value distribution with materialized view

texts:
So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will need to get overridden, but some rows will. Also, the only grouping is done at the date AND pid level, and does not include creative. So if there are date AND pid combinations that have more than one creative, the spend will need to get distributed based on the proportion of impressions for that date AND pid combination. I would like to create a view that does this for me, but I'm not quite sure where to begin here or if it's even possible. I have a python script that can do the work, but I wanted to see if it was possible with just SQL. SQL Fiddle with basic schema and data

display table:

date    pid   creative  impressions  spend
1/1/16  1234  a         10           $100
1/1/16  1234  b         20           $200
1/1/16  1235  a         20           $300
1/1/16  1236  a         20           $300
1/2/16  1234  b         15           $150
1/2/16  1235  a         20           $200
1/2/16  1236  a         10           $150
1/2/16  1236  b         20           $200
1/2/16  1237  a         10           $100
1/2/16  1237  b         15           $150

spend table:

date    pid     spend
1/1/16  1234    $400
1/1/16  1235    $600
1/2/16  1235    $1,200
1/2/16  1236    $500
1/2/16  1237    $1,000

expected output:

date    pid     creative    impressions spend
1/1/16  1234    a           10          $133.33
1/1/16  1234    b           20          $266.67
1/1/16  1235    a           20          $600
1/1/16  1236    a           20          $300
1/2/16  1234    b           15          $150
1/2/16  1235    a           20          $1,200
1/2/16  1236    a           10          $166.67
1/2/16  1236    b           20          $333.33
1/2/16  1237    a           10          $400
1/2/16  1237    b           15          $600

Answer

SELECT
  D.date,
  D.pid,
  D.creative,
  D.impressions,
  COALESCE(S.spend * D.impressions / SUM(D.impressions)
  OVER (PARTITION BY D.date, D.pid), D.spend) AS "spend"
FROM display D
LEFT JOIN spend S
  ON D.date = S.date
  AND D.pid = S.pid

So I finally got around to having a good chunk of real world data to play with and this is giving me issues. This:

SELECT D.rep_date, D.placement_id, D.creative
  , COALESCE(S.media_cost * D.impressions /
       SUM(D.impressions) OVER (PARTITION BY D.rep_date, D.placement_id)
    , D.media_cost
    ) AS  "spend"
FROM dc3.display D
LEFT JOIN dc3.display_spend S ON D.rep_date = S.rep_date AND D.placement_id = S.placement_id

returns exactly half of what I would expect for spend. This:

SELECT D.rep_date, D.placement_id, D.creative
  , SUM(COALESCE(S.media_cost * D.impressions /
      (SELECT SUM(D2.impressions)
       FROM dc3.display D2
       WHERE D.rep_date = D2.rep_date AND D.placement_id = D2.placement_id)
    , D.media_cost
    )) AS  "spend"
FROM dc3.display D
LEFT JOIN dc3.display_spend S ON D.rep_date = S.rep_date AND D.placement_id = S.placement_id
GROUP BY 1, 2, 3

returns exactly what I would expect, but takes a long time to run. Any thoughts on what the issue could be?


texts: Load data local infile changing special character

texts: i am using load data local infile function to insert data from csv to database. Everything works fine. But the word CLOUTÉ is replacing with CLOUTÃ‰. I have no idea, how to prevent it.


texts: RAM Impact on Oracle database operations

texts:
We are seeing drastic change in the amount of time taken for db operations depending on ram size.

The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into a db running on a 16gb ram machine. The inserts are made through jdbc. Both the db machines were 64 bit machines and both were enterprise editions of db.

Interesting thing is that the memory consumption for the db running on 4gb machine never goes above 50-60% of the total capacity - so we do not understand why this difference. Is there any explanation for this behavior or some more pointers to look into this?


texts: How to execute SQL query from two database

texts:
I want to know how to execute sql query from two database, one database is a remote server and next one is my local database, so from both the database table I want to use JOIN query.

Is it possible to do in SQL server management studio 2016 ?

If it is possible please guide how to do so.

Thanks,
Manu.


texts: Finding all users with access to a table in SQL server

texts: Wondering if there is any script out there to extract a list of all database logins with access to a particular table, whether its given thru a view, database role, etc.


texts: Detecting running pg_dump conflicting with schema changes

texts:
I use Heroku PostgreSQL and their PGBackups for my production environment. I have found, for reasons that I'm not quite clear on, that at least some schema changes do not run correctly during the time that the backups are happening.
According to these Heroku docs, it seems like pgbackups is using pg_dump internally, though there may be additional things going on as well. The docs indicate that the output is essentially the same as this pg_dump command.
pg_dump -F c --no-acl --no-owner --quote-all-identifiers $DATABASE_URL

Attempting to run migrations during the backup window has repeatedly caused me some pain, as the migrations lock up and hang. To mitigate this, I'd like to detect when the backups are running, and avoid running the migrations by having a pre-check in the script that runs the migrations.
I can do this by checking with Heroku's CLI, and that will work for my current specifics, but I'm wondering if something less specific to Heroku might be reasonable, by watching for a running pg_dump instead.
Is there a good way to detect if pg_dump is currently running, and may block schema changes?


texts: Import issue: Missing columns values from CSV file

texts:
I have a problem with the importing of a  CSV file to my table in MySQL. The values (3) of the columns (var1, var2, dates) are not loading, as shown in the image below:

First I created the table,

CREATE TABLE table1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,
    var1 INT NOT NULL,
    var2 INT NOT NULL,
    dates DATE NOT NULL
);

After that, I imported the data to the table:

LOAD DATA LOCAL INFILE '/home/task.csv'
INTO TABLE table1
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

I am using an Ubuntu terminal.


texts:
I want to set max_allowed_packet parameter in runtime. The

SET GLOBAL max_allowed_packet = 16777216

works for new connections, but after database server restart, it has previous value. Can value of the max_allowed_packet in runtime for new connection and restart of database server will not affect this value?

Thank you.


texts: Multiple mariadb instances - how to access to the not default instances via command line?

texts:
I have a TYPO3 and a Moodle installation on one server with Debian stretch.

Moodle needs some database configurations (MariaDB 10.1.38) which are different from the ones TYPO3 needs.

I made a new configuration /etc/mysql/conf.d/mymoodle.cnf (with port 3307 and datadir /var/lib/mysql-moodle), installed the default DBs in the new datadir with

mkdir /var/lib/mysql-moodle
chown mysql:mysql /var/lib/mysql-moodle
mysql_install_db --datadir=/var/lib/mysql-moodle

and started the instance with

service mariadb@moodle start

Obviously this works:

# ps aux | grep mysql
mysql    14135  0.1  1.8 678184 74032 ?        Ssl  Jun25   1:20 /usr/sbin/mysqld --defaults-file=/etc/mysql/conf.d/mymoodle.cnf
mysql    14323  4.8  6.1 720344 241200 ?       Ssl  Jun25  55:03 /usr/sbin/mysqld
root     25475  0.0  0.0  12712   880 pts/0    S+   14:06   0:00 grep mysql

# netstat -tulpen | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      107        76817214   14323/mysqld
tcp        0      0 127.0.0.1:3307          0.0.0.0:*               LISTEN      107        76816770   14135/mysqld

That's looks good, I thought, but I didn't find an access via command line. Calling the client with

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307

leads me to the default instance:

SHOW VARIABLES LIKE '%datadir%';

gives me /var/lib/mysql instead of /var/lib/mysql-moodle.

What is the mistake?


texts: Query data that does not fulfill constraints in table in Oracle DB

texts:
In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled.

I have to know for all tables in a given schema with any of the constraints deactivated, what are the (data) rows that would not fulfil the deactivated constraints.

Question

Is there a way to easily collect all the rows that would produce an error if I were to decide to enable the constraints again?

So far, I would try to go constraint by constraint, create a query for each table and run the query. But I wonder whether there is anything that may do the work quickly and less error prone. If possible, the solution should use the information available in view USER_CONSTRAINTS or any similar one.

I have checked the constraint documentation in Oracle, in which the constraint clause may use ENABLE NOVALIDATE, which allows for having non-compliant rows of data for the existing values until they are modified.

However, I am looking for a solution that would allow me to fix the data before I re-enable the constraints.


texts:
Am trying to use backreference expression to take out three subexpressions from a search, and reposition each subexpression as follows:

 SELECT REGEXP_REPLACE('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]) ([[:alpha:]]+)','\3 \1 \2') from dual

However am getting the result as AAA 123 ppp where expecting ppp AAA 123
Where am i getting it wrong?


texts:
How can count all rows of WHERE statement:

SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%';

My output:

column1
=======
info1
info2
info3

Desired OUTPUT:

column1|number_of_rows
=====================
info1  | 2000
info2  | 1500
info3  | 1000

Can you support me How can improve it?

Thanks in advance.


texts: What does slow down an InnoDB database that is fixed by re-importing it?

texts:
I recently noted a performance degradation on a MySQL 5.6 (InnoDB) database with several million records. This particular query was taking around 20 seconds on the first run (apparently data was loaded from disk) and 4 seconds on subsequent runs (apparently data was loaded from memory):

SELECT
COUNT(DISTINCT `t3`.`node_id`) AS '__value'
FROM `hd2_ont_class_url` AS `t3`
INNER JOIN `hd2_ont_link` AS `t4` ON ((`t4`.`passive`=`t3`.`node_id`) AND (`t4`.`linktype`='tagset'))
INNER JOIN `hd2_ont_rel` AS `t5` ON ((`t5`.`passive`=`t4`.`active`) AND (`t5`.`active`='28530') AND (`t5`.`linktype`='tagup'))
WHERE
(`t3`.`deleted` = 0) AND
(MATCH (`t3`.`title`) AGAINST ('+php*' IN BOOLEAN MODE));

Same query on the same database in another environment was taking only around 0.02 seconds, so I started investigating where the hold up was coming from. Tried different versions, configs — all the same. The EXPLAIN output was also identical everywhere.

In the end of it I just dumped and re-imported back the original database. And that was it! Now the query is taking no more than 0.05 seconds!

So here are the questions:

What caused the performance degradation? How to prevent it?
Is there some sort of maintenance procedure that would fix the issue like the re-import did?

Hint: the records in the database are rotated often — every day about 20% of them are deleted and new ones are created instead.


texts:
I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database.

Thank You!


texts:
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file contains only index information). I tried to delete that file from database properties, but an error occured:

  Alter failed for Database 'xxxxxx'.  (Microsoft.SqlServer.Smo)

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

  The operating system returned error 21(failed to retrieve text for
  this error. Reason: 15105) to SQL Server during a write at offset
  0x0000002c668000 in file 'R:\DBIndexes.mdf'. Additional messages in
  the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online. (Microsoft
  SQL Server, Error: 823)

I have no problem to re-build the indxes, but I need to fix the database!!

I deleted all of the indexes that stores in that file, And tried to delete the file group, but the same error occured

*UPDATE*

When I detached the database, I couldn't re-attache it, it now saying:
Unable to open the physical file "R:\DBIndexes.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".

It was working fine before detaching it, but the small problem was in deleting that orphan file!!!


texts: Best practice design schema for implementing form approval tracking?

texts:
I'm in the middle of creating an ASP.NET MVC 5 web app using windows SQL server as my DB setup.

The app has a very simple purpose: Allow a user to submit a form to be signed by multiple people, traveling "up" the chain of command to be approved by each person. After the final approved the form travels back "down" to the requester.

Now I understand of course that within a database context, the form isn't going anywhere at all; variable are changing making the form alternately visible/invisible to different people.

But what I want to know is: what is the best way to implement this? Specifically, the best way to "track" forms that are traveling the chain.

As I have it right now, I have a model of a request form (containing info like date of request, reason for it, etc.), and that's all dandy. But I'm hung up on the BEST WAY to implement the chain. I was thinking of having two extra columns: CurrentApprover and Approved. CurrentApprover is a foreign key representing the primary key of the person that needs to approve your form next, and Approved represents whether the form has been fully approved.

This is simple, and it would work in practice. However it may limit me down the road in manipulating the pipeline or such. Maybe I could have a "manifest" table which contains all active requests and their details? I like this idea better now that I type it out.

Does anyone have a suggestion?

Also, 2 quick questions if someone would be so kind:

1) This app will be processing thousands of forms each year, and though I know tables do fine with millions of entries, I was wondering where there would be any advantage/best practice reason to have different tables for different company divisions (there are 3 distinct divisions, but the forms are the exact same and the process for approval is the exact same).

2) If a form is foreign keyed to its submitter, and the user data table for the submitter contains info that is being passed up with the request form, do I still need to/ should I include those fields explicitly in the request form table, or is it sufficient/better to just query those values when needed. I feel like the latter is better, because a form has a submitter ID (employee ID per-say), and I can just use that to look up the employee's user info when I need it.

Thank you very much!


texts: Table JOIN and CREATE new table extremely slow

texts:
I'm using something like this to create a new table:

CREATE TABLE result AS
(SELECT calls.*,
      targ_with_planned_calls.*
 FROM calls
      INNER JOIN planned
              ON calls.first_id = planned.another_id);

The two tables have around 60k and 80k rows. One table has 5 columns and the other around 15. I'm using AWS RDS db.t2.medium instance. I've let this query run for as long as 10 minutes and it's still going.

Should this be taking so long? I'm wondering what the performance issue is. I would have thought it would be fairly quick.


texts: Run script on multiple schemas on PostgreSQL DB

texts:
I need to run a huge migration script on multiple schemas on PostgreSQL 9.3 server. I want output to be in a .txt format if this failed on any schema. Would you be able to help me on this?

Details:
All schemas are of similar structure and I have the list of schemas on which this script needs to be executed.


texts: Maintenance plan using external tool to execute query and stored procedures giving off no results

texts:
My situation is as follows:
I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers.
The stored procedures are from http://ola.hallengren.com/ and the sql queries are as follows:

Sql query to check index fragmentation.

IF EXISTS ( SELECT *
    FROM [tempdb].[dbo].[sysobjects]
    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
    [DatabaseName]                  [nvarchar] (1000) NULL,
    [ObjectName]                    [nvarchar] (1000) NULL,
    [Pagecount]                         INT,
    [indexName]                     [nvarchar] (1000) NULL,
    [avg_fragmentation_percent]     float NULL,
) ON [PRIMARY]
DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)
DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')and  state not in('6')
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlQuery = '
    USE [' + @dbname + '];

    IF EXISTS
    (
        SELECT compatibility_level
        FROM sys.databases
        WHERE
            name  = N'''+ @dbname +'''
            AND compatibility_level >= 90
    )
    BEGIN
        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
        (
            DatabaseName
            , ObjectName
            , IndexName
            , avg_fragmentation_percent
            , PageCount
        )
            SELECT db_name() as DatabaseName,
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
    END;'
    EXEC sp_executesql @sqlQuery
FETCH NEXT FROM dbcursor
INTO @dbname
END
CLOSE dbcursor
Deallocate dbcursor
-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename, avg_fragmentation_percent desc

Sql query to check database size (for baseline)

with Temp
as (select database_id, type, size * 8.0 / 1024 / 1024 size from sys.master_files)
select (select sum(CAST(size AS DECIMAL (38, 2))) from Temp where  Temp.database_id = db.database_id) DataFileSizeGB, Name
from sys.databases db
order by datafilesizeGB desc

After these 2 queries save their results in an .xlsx file, I run the following stored procedure using the basic script as found on http://ola.hallengren.com/ .

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@fragmentationmedium = 'INDEX_REORGANIZE',
@fragmentationLevel2 = '30',
@fragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = '5',
@fragmentationLow = 'NULL'

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@updatestatistics = 'all',
@FragmentationLevel1 = '5',
@FragmentationLevel2 = '30'

When I run the stored procedure in SSMS, followed by the query to check fragmentation I can see changes in the indexes
When I run the stored procedure using my third party tool, I don't see any changes in the indexes
The stored procedure takes the same amount of time using the third party tool, as it does running directly in SSMS
When I check sql profiler, I can see the stored procedure being executed when being run from VisualCron

How do I go about further troubleshooting the stored procedure being run from VisualCron?


texts: Should I create a separate user table for different web products within the same platform?

texts:
We have a web product for young professional that gives them the possibility to create their page to show their professional identity.
So a table users that has both information about the user (email, password, name) including their credentials and information about their page (premium or not, page address, theme)

Now we want to offer the possibility for recruiters to signup to our platform to browse through candidates. A recruiter can also be a user with a page but does not have to.

Now our two approaches:

A/ Create a table recruiters with name and credentials of the recruiter and a column user_id to connect with the ID of the table users if they have created a site.

Benefits : The product can be easily developed separately, by two different teams.
Inconvenient : Duplicates of the name and credentials if the recruiter is also a user.
We would need to either update both credential when one is updated or to let them have two different email/password combination, one for their user account, one for their recruiter account.

Database structure:

users
ID name email password group_id premium theme page_address

recruiters
ID name email password company_id user_id

B/ Add the recruiters to the users table with a different group_id and move all the information about the users page in another table (premium or not, page address, theme). We would also have a third table for the recruiter containing any information specific to them.

Benefits : One table with all the credentials.
Inconvenient : If we reach millions of users, any query among recruiters will have to take a tiny subset among a huge table. Also : lots of join to get the site information for every user.

Database structure:

users
ID name email password group_id

pages
user_id premium theme page_address

recruiters
user_id company_id

C/ Any other solution?

Thank you for your inputs!

Tristan


texts: Postgres - Regroup rows with at least one common attribute

texts:
I have the following table :

Letter | Figure
-------|-------
   A   |    1
   B   |    1
   B   |    2
   C   |    2
   C   |    3
   D   |    3
   E   |    4

I would like to group rows that share a common letter OR figure. Result should then be :

Letter | Figure | Groupment
-------|--------|-------
   A   |    1   |   I
   B   |    1   |   I
   B   |    2   |   I
   C   |    2   |   I
   C   |    3   |   I
   D   |    3   |   I
   E   |    4   |   II

I know I can define a postgres function and re-scan the all table for each new row, but I'm trying to find something a little bit less unoptimized.

Thanks for your help !


texts:
I am working on a strategy to replicate my MySQL binlogs to Amazon s3. I have looked into mysqlbinlog --remote-server but it seems like adding a file watcher on /var/log/mysql which triggers an aws s3sync command is a little easier.

I have tested this with mysqlslap and it seems to work, But I was wondering if anyone had insight as to whether this was a bad practice. Perhaps the files can get corrupted this way?


texts: Locks while stream reading and deleting/inserting involving the same tables?

texts:
I have a question regarding a potential block/lock situation:

I am reading data using a select command, which is then modified externally and uploaded to a temporary table. A stored procedure will then use MERGE INTO in order to apply the changes to the original table. When system resources are getting short a flush happens - resulting in the data reader still being open while the SP is executed. Reading and SP executions run on their own separate connections.

Is it probable that this behavior will cause locks and therefore rather lengthy execution times (over 5 hours having around 100k entries, 5k being altered)?

This is the most relevant part of the SP:

[Some deletions in OrderAssociations depending on user settings]

MERGE INTO OrderAssociations a
USING #OrderAssociations b
    ON a.OrderId = b.OrderId
    AND a.CustomerId = b.CustomerId
WHEN NOT MATCHED THEN
    INSERT
    (
        OrderId,
        CustomerId,
        ShopId,
        Sequence
    )
    VALUES
    (
        b.OrderId,
        b.CustomerId,
        b.ShopId,
        (
           SELECT
              ISNULL(MAX(Sequence), 0) + b.Sequence
           FROM OrderAssociations c
           WHERE c.CustomerId = b.CustomerId
        )
    )
    OUTPUT INSERTED.CustomerId INTO @InvolvedCustomerIds;

The selection command is as follows:

SELECT
   a.OrderNo,
   '[Deleted by ERP]' AS PathPart1,
   CONVERT(VARCHAR, GETDATE(), 102) AS PathPart2,
   ISNULL(lcn.Name, 'No Name') AS PathPart3
FROM OrderAssociations ca
LEFT JOIN LocalizedCustomers lcn
   ON ca.CustomerId = lcn.CustomerId
INNER JOIN Orders a
   ON a.OrderId = ca.OrderId
WHERE ca.ShopId = '548F9654-5369-41ED-B1E9-80BB04477DBE'
AND lcn.LanguageId = 1
ORDER BY lcn.Name


texts: Exponential log file growth on quiet database with log backups

texts:
I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon.
Despite being a testing database with virtually no activity, the size of the log files (and transaction log backups) are growing exponentially:

Date
Size

March 2:
200MB

March 3:
560MB

March 4:
963MB

March 5:
1.5GB

March 6:
2.5GB

March 7:
5.9GB

For comparison, the nigh-identical production version of the same database has transaction log backups (same schedule) under 100MB.
The autogrow is set to 10%, so each new VLF winds up 10% larger than the last, but it's a mystery to me why new VLFs are created several times a day at all!  There are no open transactions, and when I checked DBCC LOGINFO last night, there were numerous large VLFs with status 0, so log backup is truncating fine.  They were all status 2 this morning.
Doing periodic LOGINFO & LOGSPACE checks this morning, I saw the log expand to add a VLF, despite having virtually nothing in the last one:

The previous size would have been ~6369MB, so that VLF had about 25/700MB in use.
Can something cause SQL Server to move onto a new VLF before the active one is remotely full?
EDIT:
Trace shows a 90 minute shrink and grow cycle

Action
duration
start
end

Log File Auto Grow
1473000
2021-03-08 10:50:14.970
2021-03-08 10:50:16.443

Log File Auto Shrink
1000
2021-03-08 10:50:13.763
2021-03-08 10:50:13.763

Log File Auto Grow
1116000
2021-03-08 09:19:36.813
2021-03-08 09:19:37.930

Log File Auto Shrink
1000
2021-03-08 09:19:35.827
2021-03-08 09:19:35.827

Log File Auto Grow
1090000
2021-03-08 06:48:33.760
2021-03-08 06:48:34.850

Log File Auto Shrink
1000
2021-03-08 06:48:32.207
2021-03-08 06:48:32.207

Log File Auto Shrink
1000
2021-03-08 05:17:54.147
2021-03-08 05:17:54.147


texts: Recommendation for storage of series of time series

texts:
Just a few words describing the data:
In my application, there are acceleration measurements (for example at 25kHz) for the exemplary duration of one second. These measurements get repeated in not necessery äquidistant time steps for that measurement point. (Maybe each five or ten minutes). This is a kind of interrupted permanent monitoring, somehow two-periodic:

short time period of measurement is 25.000 Hz (the resolution of the measurements)
long time periodic (not in a strong sense, might differ) every 5 minutes

There are 20 or more of those points.

As dealing with time series, the first idea might be the usage of a time series db. On the other hand, for me it seems as if the main purpose of time series db is storage of scalar values. Of course, my measurements are scalar values. But I'm not sure if it would be a good idea to store every scalar value as a (time/value/measpos_id)-triple - leading to an enormous number of entries. I think single of those entries would never be evaluated.
Another idea could be the storage of the measurement vector (all values from that second) together with the starting time and the measpos_id. But howto do that? Taking all values as a blob? Not every system is capable of dealing with vectors - and maybe they differ in length. Are there concepts in timeseries-db for such problems, which I'm not aware of?
Further for evaluation (extraction) I think maybe the exctraction of the complete vector would be the most used case.
Please feel free to ask, if my description is incomplete or some more details could help in finding a good solution.
What are your recommendations? NoSQL or relational SQL? Further ideas? Every hint is welcome. Thanks in advance.

additions:

A rough idea for the volume is steady growing in size of about 1 TB a year
Giving a sample is not that easy - I'll try to describe:
Think of 1 column with 25000 float values for each measurement (each minute rougly and for each measurement position), timestamped each of these columns (at begin).
Usage for big data evaluation (means testing many kind of algorithms); windowing data, fft (spectral analysis), comparison, aggregation like energetic sum, value of max amplitude, pos (freq) of max amplitude, many more
purpose (focus) of evaluation: wear detection for condition monitoring of for example rolling devices (gears, generator sets, turbines, shafts, bearings)
evaluation would (from todays view) focus on each seperate column and maybe compare to others - but not combine (stack) columns together.
data size example: 25.000 float values in each column for 20 measured engines each 5 minutes (12 per hours) results in 6e6 floats each hour or 5.25e10 floats each year.


texts: Security implications of restoring a backup from an unknown source?

texts:
Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the source should be trusted.

Question 1: What are the potential implications of restoring a backup that could well be malicious?

Question 2: What can you do to protect your server/the data in other databases from the impact of restoring a potentially-malicious backup? RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation?


texts: Try to run Python programm from command line. ImportError: No module named mysql.connector

texts:
I'm new in programming. So I have a question about this error.
I'm trying to run programm from command line in PyCharm, and get this Error.

root@kali:~# python /root/PycharmProjects/lesson/ProL3/ner.py
Traceback (most recent call last):
  File "/root/PycharmProjects/lesson/ProL3/ner.py", line 2, in
    import mysql.connector as sql
ImportError: No module named mysql.connector

Prog:

import mysql.connector as sql
from mysql.connector import Error
import argparse

connection = sql.connect(
    host="localhost",
    password="mroot",
    user="myro"
)
mycursor = connection.cursor()

parser = argparse.ArgumentParser(description='Database management', usage='Script option:')
parser.add_argument('-d', '--data', help="Create Databases")
args = parser.parse_args()

class Data:
    def __init__(self, database=''):
        self.database = database
        self.create_database()

    def create_database(self):
        try:
            mycursor.execute(self.database)
            print("The database was created!")
        except Error as e:
            print(e)

What should I do?


texts:
I have two replicas in availability group. Also I have additional instance instance3, with dummy databases that are not in availability group.
As time has passed, some new things started to be stored on that dummy database and now I have to add it to Availability group.

I followed user guide as described here:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
but when I get to specify new database, in wizard, that I want to add to availability group, I don't see that database in the list.

Did I miss some step in the middle or I am doing it completely wrong?

Additional note is that there is a database with same name but from another instance in availability group. Will that maybe create problem? I guess there can't be two DB with same name and now is little late to change name of DB.


texts: When is it better to create STATISTICS instead of creating an Index?

texts:
I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to find any guidance or "best practices" information regarding when to create them: what situations benefit more from a manually created STATISTICS object than from an Index. I have seen manually created filtered statistics helping queries on partitioned tables (because the statistics created for the indexes cover the whole table and are not per partition--brillaint!), but surely there must be other scenarios that would benefit from a statistics object while not needing the detail of an index, nor worth the cost of maintaining the index or increasing the chances of blocking / dead-locks.

@JonathanFite, in a comment, mentioned a distinction between indexes and statistics:

  Indexes will help SQL find the data faster by creating lookups that are sorted differently than the table itself. Statistics help SQL determine how much memory/effort is going to be required to satisfy the query.

That is great info, mostly because it helps me clarify my question:

How does knowing this (or any other technical info on the whats and hows related to the behaviors and nature of STATISTICS) help determine when to choose CREATE STATISTICS over CREATE INDEX, especially when creating an Index will create the related STATISTICS object? What scenario would be better served by having only the STATISTICS info and not having the Index?

It would be super-duper helpful, if possible, to have a working example of a scenario where the STATISTICS object is a better fit than an INDEX.

Since I am a visual learner / thinker, I thought it might help to see the differences between STATISTICS and INDEXes, side-by-side, as a possible means of helping determine when STATISTICS are the better choice.

Thingy           PROs                             CONs
-------          ----------                       -------------------
INDEX            * Can help sorts.                * Takes up space.
                 * Contains data (can             * Needs to be maintained (extra I/O).
                   "cover" a query).              * More chances for blocking / dead-locks.

STATISTICS       * Takes up very little space.    * Cannot help sorts.
                 * Lighter maintenance / won't    * Cannot "cover" queries.
                   slow down DML operations.
                 * Does not increase chances
                   of blocking / dead-locks.

The following are some resources that I found while looking for this, one that even asks this same question, but it was not answered:

SQL Server Index vs Statistic

SQL Server Statistics Questions We Were Too Shy to Ask

Statistics. Are multicolumn histograms possible?

** To be clear, I do not have an answer for this and am actually looking to get feedback from hopefully a few people to provide what appears to be oddly missing information out here in the interwebs.


texts:
I have a big table with 315M rows. with these columns & Index :

[key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int)

PK & Clustered-Index: key_ , Non-Clustered-Index on: time_ + quality_

Actually the main select that we use here is as bellow. This select gives good result, in 2Secs, but when I change the range & make it wider (like 36 hours), it responds in 6Minutes!
SELECT TOP (1000)
     ,[time_]
     ,[value_]
     ,[quality_]
    FROM [DataHubRev2].[dbo].[tb_HPGR_120MI01ME01_Power]
    WHERE quality_=192 AND time_ >' 2020-03-27 19:00:01'AND time_ <' 2020-03-28 07:00:00'
                           --- 12 hours: Good Result
--- WHERE quality_=192 AND time_ >' 2020-03-27 07:00:01'AND time_ <' 2020-03-28 19:00:00'
--- 36 hours: Bad Result

It is clear that by using this select & where, SQL-Engine should go for NonClusteredIndex (at least I think so). When I look at the 'Actual Execution Plan' the first one goes directly to the NonClustIndex Seek, but the second one goes for ClusteredIndex Scan & the readed rows tells 315M (the entire table)!!
Interesting part is that when I remove  ',[value_]' from the select or add the Hint to force NonClustIndex use (With (INDEX = [IX_TimeQuality_Index])) response time & exec-plan is solved. But I need a more deep solution & actually why this is happening.
Also tell me that: making my table as Heap & not having any Clustered Index on ID which I don't use on my selects is a good practice or not?
Edit:
These are the Actual Execution Plans, you could see that the second one scans all the table (Number of rows read).

Good Response Time : https://ibb.co/JFjXXwJ
Bad Response Time:    https://ibb.co/WGgrDh2


texts: PostgreSQL Query and/or Session CPU Usage

texts: Is there any way in PostgreSQL to know how much the cpu cost was per a query and/or by session. It doesn't have to be the exact system cpu usage more of a relative cost like we get with disk I/O. For Windows or Linux.


texts: How to recover a lost password in MySQL 5.7.11? (for Windows)

texts:
I am running MySQL 5.7.11 on Windows 10

I am unable to login to root through the command line client since I lost my password.

How do I change/reset the old password?

Please help, and thanks in advance.


texts: How can I enforce data integrity in this model with a M:2 relationship?

texts:
This model describes a part of my database (somewhat simplified):

A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 mouse. "Experimental animal" is the specific animal from a given line which is used for something in an experiment. Each animal can have tumor entities, and the researcher can do interventions on it (e.g. give the animal chemotherapy). So far, pretty straightforward.

The part where I ran into problems was when I had to add a transplantation. A transplantation is always performed on exactly two animals within the same tumor model, one being the donor and the other the acceptor. There can be multiple transplantations within a tumor model. As it is a M:2 and not a M:N relationship, I took a shortcut and did not make a junction table out of the "performed on" relationship, opting instead for two foreign keys from the Experimental animal table to be referenced in the Transplantation table.

My problem is that now, I am able to create a transplantation between two animals which are not part of the same model. (By the way, using a junction table instead of two IDs in Transplantation would not have removed the problem). I tried many ways to model the whole thing and I am quite sure that there is no way to enforce this on the ERD model level. I know that we can enforce this well within the business logic, but our department has a "our user has a one-time wishes to do something, we will quickly throw together a script/Access frontend/whatever to do it" policy, where the business logic gets completely bypassed.

What are my options for ensuring that I never get a Transplantation row with two IDs pointing to experimental animals from two different models? Keep in mind that as an application developer, I do not have experience with databases beyond creating my own schema and doing CRUD operations in SQL. I am not sure if I can somehow put together a constraint which can do what I want, or maybe set a trigger which prevents updates and inserts with the wrong combination of IDs, or if there is something else entirely I didn't think of. Ideas about which way leads to the cleanest solution and a rough description of what exactly I would need to implement are welcome.


texts: I have a bunch of SQL databases. How should I put these into Azure?

texts:
I have a bunch of on-premise SQL databases that contain data related to HR. One database could be pure employee data, such as staff records, leaver records or recruitment records. Another database could be from a external provider of services, which would tell me what benefits my employees are utilising. A final example could be salary review data, where data is generated by the business each year by line managers submitting employee performance scores.
I'm currently exploring Azure as I want a cloud solution for my data. I need to store all of these datasets in a central location, with some standard governance applied. I also need to have this data available to applications such as Power BI for BI requirements and I also need machine learning capabilities for data science projects.
I currently use SSIS to get my data in SQL Server, however my research has suggested using Azure Data Factory, Azure Data Lake and Azure Synapse to manage my data.
I don't really know where to start, any guidance would be great! Thank you.


texts:
Suppose my students fill every year a survey, containing some questions (called fields) which answer is real-valued (called value). Every student may answer the survey, at most once a year.

A simple example for the survey:

in 2012: Quality of the lecture (0-100)
in 2013: Quality of the lecture (0-100) and Difficulty level (0-100)

I am faced with a dilemma : use composite keys or check constraint. Which solution is a best practice ?

No composite key :
I create the following table structure but I need a constraint to make sure that field.year is the same as survey.year

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   value float,
   primary key (surveyID, fieldID),
   foreign key(studentID),
   foreign key(fieldID)
)

With composite key :
With the following table structure, I have the drawback to use the tuple (surveyID, year) for each join I make.

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
   primary key (surveyID, year)
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
   primary key(fieldID)
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year int,
   value float,
   primary key (surveyID, year, fieldID),
   foreign key(studentID, year),
   foreign key(fieldID, year)
)


texts:
Suppose I have a database with a large number of tables.

Is there any command that I can type in PL/SQL which display only the field names of a given table?
Given any table is there any command that I can type that would show all the relations that it has with other tables?

For example , I have a table "TAB_1" , can I type a command that would show all the tables related to and for any table "TAB_2" that it is related to it would show which field in "TAB_1" corresponds to which field in "TAB_2 " .


texts:
I want to read data from each cell of database. If the corresponding value is encountered then I have increment a variable count for that corresponding variable.

Consider the following table

Ques1   Ques2   Ques3   Ques4   Ques5
   1      2       3       1        3
   3      4       1       1        2
   2      2       2       1        2
   1      1       3       4        1
   3      1       4       1        1

So from the above table data, I want to extract the count of each rating. For example the
count of 1 in the above table is 11 and the count of 2 is 6 and the count of 3 is 5 and so
on.. So I have to get the following table as my output

Count1  Count2  Count3  Count4
  11       6       5      3

I have some basic knowledge for incrementing count for that variable but I don't know how to navigate from one cell to another and check its value.


texts: SQL Server - What taks/queries should I need to execute when there is a lack of space in TempDB?

texts:
I am having some troubles loading data to my fact table. Every time that I want to insert, I'm getting the following issue:

  Could not allocate space for object 'dbo.SORT temporary run storage:  140739614015488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

My question is: Which practices should I apply in order to understand why this error or to know if tempDB is close to full or not? There exists any queries that normally tell me some information about this?


texts: Get dates of the MSSQL Database Transaction Log records

texts:
I am wondering if there is a way not just to get the transaction log (e.g. using fn_dblog), but also get the date for the start of execution for each of the transactions recorded?

It's okay to get any amount of extra uninteresting data as long as I get the date or timestamp of the transaction execution.


texts: Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB

texts:
Everyone is warning against shrinking a database, e.g.
SQLAuthority

But will it be a valid option in my case? The database was originally more than 1 GB,
but with a table removed from it (moved to another database), the database is now 100 MB when I do a backup of it, as opposed to the MDF being more than 1 GB still.

What if I shrink the DB, then rebuild all the indexes? Is all of this still taboo?


texts: Best approach to find missing logins and missing jobs between principal and mirror server

texts:
I am doing a manual comparison of the logins and jobs between Principal and Secondary server, when doing database failover from P to S. I copy both the logins and jobs from one server to excel and compare them to those on the other.

Could someone please suggest a way to automate at least part of this check?

Note - I am doing this before doing a database failover as a pre-check, just to ensure that the secondary isn't missing anything as compared to the principal, so as to avoid a break after mirroring database failover.


texts: Azure SQL DB and SQL MI Security Concerns

texts:
I have 7 different SQL Managed Instances, which were newly migrated over from SQL VM. I also have 7 different Azure SQL Databases, which were also newly created.
I want to make sure that I completely secure all my MI and Azure SQL Databases to eliminate security vulnerabilities and also to avoid any SQL injection attacks.
I have already implemented the following:
Public Endpoint is enabled and I have setup NSG rules to configure the IP addresses which have inbound access to the SQL MI instances.
I was also doing some reading into some other best practices to secure the SQL Managed Instance and Azure SQL Databases and below are some of the things that I found:

Implement Row level security
SQL Database Auditing
Always Encrypted feature
Dynamic Data Masking
Data Discovery and Classification

I would appreciate if you can advise if these are worth looking into and should they be implemented in the environments based on your experience.
Also does implementing any of these best practices degrade performance.


texts: Reconciling AR Payments against invoices

texts:
I have a situation I have been working on for quite some time. We sometimes give our customers extended terms and allow them to make payments toward a larger invoice. Those payments are expressed with a notation on the invoice as three separate payments with three due dates, however, in the database table it's still a single invoice with a single due date. I'm required to age the payments even though they don't technically exist anywhere.

Here's where I'm at thus far.  I have created a view which takes the invoices with extended terms and calculates what the payment amount and due date should be for each extended terms invoice payment.:

alter view V_ArExtTermsInv as
Select i.Customer, i.Invoice as 'Invoice',
CAST(i.InvoiceDate  as date) as 'InvoiceDate',
i.SalesOrder as 'SalesOrder',
i.InvoiceBal1 as 'Bal',
i.CurrencyValue as 'Total',
t.TermsCode, t.DueDays,
tt.ExtendedTermsCode1 as 'Ext1',
tt.ExtendedTermsPct1 as 'ExtPct1',
CAST(DATEADD(day, t.DueDays, i.InvoiceDate) as date) as 'PmtDt1',
round(i.CurrencyValue*(tt.ExtendedTermsPct1/100),2) as 'Pmt1',
tt.ExtendedTermsCode2 as 'Ext2',
tt.ExtendedTermsPct2 as 'ExtPct2',
CAST(DATEADD(day, t.DueDays+30, i.InvoiceDate) as date) as 'PmtDt2',
round(i.CurrencyValue*(tt.ExtendedTermsPct2/100),2) as 'Pmt2',
tt.ExtendedTermsCode3 as 'Ext3',
tt.ExtendedTermsPct3 as 'ExtPct3',
CAST(DATEADD(day, t.DueDays+60, i.InvoiceDate) as date) as 'PmtDt3',
round(i.CurrencyValue*(tt.ExtendedTermsPct3/100),2) as 'Pmt3',
tt.ExtendedTermsCode4 as 'Ext4',
tt.ExtendedTermsPct4 as 'ExtPct4',
CAST(DATEADD(day, t.DueDays+90, i.InvoiceDate) as date) as 'PmtDt4',
i.CurrencyValue*(tt.ExtendedTermsPct4/100) as 'Pmt4',
tt.ExtendedTermsCode5 as 'Ext5',
tt.ExtendedTermsPct5 as 'ExtPct5',
CAST(DATEADD(day, t.DueDays+120, i.InvoiceDate) as date) as 'PmtDt5',
round(i.CurrencyValue*(tt.ExtendedTermsPct5/100),2) as 'Pmt5',
tt.ExtendedTermsCode6 as 'Ext6',
tt.ExtendedTermsPct6 as 'ExtPct6',
CAST(DATEADD(day, t.DueDays+150, i.InvoiceDate) as date) as 'PmtDt6',
round(i.CurrencyValue*(tt.ExtendedTermsPct6/100),2) as 'Pmt6',
DATEDIFF(day, i.InvoiceDate, CURRENT_TIMESTAMP) as 'DaysFromInv'
from ArInvoice as i
left outer join ArCustomer as c on i.Customer=c.Customer
left outer join ArTrnSummary as s on i.Invoice=s.Invoice and
i.DocumentType=s.DocumentType
left outer join TblArTerms as t on i.TermsCode=t.TermsCode
left outer join [TblArTerms+] as tt on t.TermsCode=tt.TermsCode
where i.InvoiceBal1<>0 and i.MonthInvBalZero=0 and i.YearInvBalZero=0 and
t.TermsCode='81'

The Invoice, Date (3/10/2019), BalDue, and InvTotal are the only values here carried in our current database.  As it stands, the invoice appears to be past due.

I need to somehow show the balance due here of Pmt1 and Pmt2 as 0 so that I can age this report correctly. As a side note, customers don't always pay the payments in the exact amounts. They might pay all of Pmt1 and half of Pmt2. My logic thus far is that I don't know what they are paying technically.  I just want to take the TotalPaid and apply it from Pmt1 to Pmt3 setting Pmt1 to 0, Pmt2 to 0, and Pmt3 to 0 or a negative number if they overpay in total. Is this too complicated to accomplish?


texts: PostgreSQL - pg_dump fails (out of memory) - queries to a table fail for memory

texts:
Version: PostgreSQL 9.2.1 - 32-Bit Windows

We have a database which is fairly small (~1.4GB) that is unable to be backed up.  It seems to fail on the same table (attachment_data - has about 3500 rows) during pg_dump and giving the following error:

pg_dump: dumping contents of table "public.attachment_data"
pg_dump: Dumping the contents of table "attachment_data" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  out of memory
DETAIL:  Failed on request of size 536870912.
pg_dump: The command was: COPY public.attachment_data (file_data, id) TO stdout;

Not knowing if this was a database issue or an issue with the postgresql instance itself, I installed the same version of postgre on another server, copied the data folder over, and have been working off that data and replicated the same problem.

Thinking it was a bad row or two in the table based on this post for a similar issue, I've been doing selects on top X rows to try and discover bad ones:

select * from attachment_data limit 200

I thought I was getting somewhere - I failed on a couple rows and deleted them.  Then I would go back and do the selects again, but it fails inconsistently.  For example, sometimes a select limit 2000 will work, and other times it will generate the following:

ERROR:  out of memory for query result

I should mention I cannot do a select * from attachment_data if I don't limit it - it will also fail for out of memory.

I've also made some changes to postgresql.conf file on memory and shared_buffers, to see if this would have any impact.  I've increased it in steps, restarting each time, up to 1512MB with no change.

I also increased max_locks_per_transactions to 256 based on another thread with no luck.

Any suggestions on things to try?


texts: Is there a way to open an encrypted Oracle Database wallet through weblogic JDBC drivers?

texts:
I encrypted a column in an Oracle 12c database using the TDE method, The said column was being used in my application, The column becomes unavailable due to the Database wallet being closed.

To open the wallet I use the command 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;'

If I open the Database encryption wallet the column becomes available and the application works fine.

As the application is being deployed using weblogic servers, I was wondering if it is possible to open the wallet with the help of JDBC driver, or is there some way to pass the wallet password so as to open the wallet on connection to the database.


texts: MySQL Queries taking too long to execute

texts:
I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% CPU. and 6.5 %MEM (results obtained by running top in the CLI). Also to note, it runs on the same server as Apache2 Web Server.

Here's what I get on the Mem line:
Mem:   8141292k total,  6938244k used,  1203048k free,   211396k buffers

When I run: show processlist; I get something like this in return:

2098812 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Horns/thumbs/Halloween 2013 Horns (Original).png'
2098813 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Witch Hat/thumbs/Halloween 2013 Witch Hat (Origina
2098814 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Blouse/thumbs/Halloween 2013 Blouse (Original).png
2098818 | admin | localhost       | phpb | Query   |   11 | Sending data | SELECT * FROM items WHERE parent = 210162 OR auto = 210162

Some queries are taking an excess of 10 seconds to execute, this is not the top of the list, but somewhere in the middle just to give kind of a perspective of how many queries are stacking up in this list. I feel that it may have something to do with my Query Cash configurations. Here are the configurations show from running the SHOW STATUS LIKE 'Qc%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 434      |
| Qcache_free_memory      | 2037880  |
| Qcache_hits             | 62580686 |
| Qcache_inserts          | 10865474 |
| Qcache_lowmem_prunes    | 4157011  |
| Qcache_not_cached       | 3140518  |
| Qcache_queries_in_cache | 1260     |
| Qcache_total_blocks     | 4440     |
+-------------------------+----------+

I noticed that the Qcache_lowmem_prunes seem a bit high, is this normal?

I've been searching around StackOverflow, but I couldn't find anything that would solve my problem. Any help with this would be greatly appreciated, thank you!


texts: Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

texts:
At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields.

I have a web interface that interacts with the sql scheduling system.

I've created a test job that started on March 16th 2011 at 9:45am.
I've set it up to happen every 4 weeks on a Wednesday.

From today, this should put a next run date and time on January 18th at 9:45 but the Next Run Date/Time value for my job is saying January 4th.

The values in the job properties look good.
The start date says March 16th 2011. The frequency values are all correct. But it seems SQL is failing to land this occurrence properly.

Here is some relevant info for the job in question from the sp_help_jobschedule proc

freq_type   8
freq_interval   8
freq_subday_type    1
freq_subday_interval    1
freq_relative_interval  0
freq_recurrence_factor  4
active_start_date   20110316
active_end_date 99991231
active_start_time   154500
active_end_time 55959
date_created    2011-12-29 20:37:05.737
schedule_description    Every 4 week(s) on Wednesday at 154500
next_run_date   20120104
next_run_time   154500

if the start date is March 16 2011 at 9:45, it doesn't make sense that the next run date would fall on January 4th 2012 based on the properties of the job.

I guess my question is, how much can I rely on this Next Run Date data?


texts:
Will having data compression enabled decrease the amount of memory used by an in memory OLTP table?

If yes I assume the CPU workload will increase.


texts: Find Max() updated time for a group of users

texts:
Database is MariaDB 10.3.25
I have 2 columns that are relevant:
UserID and LastUpdate
UserID consists of user@domain
LastUpdate is a date field.
Here is my issue - I have the current query:
select a.UserID,
substring_index(a.UserID, '@', -1),
max(a.lastupdate)
from MyTable a
group by a.UserID
having max(a.lastupdate) < '2020-03-31'

This shows all the Users that haven't updated in just over a year and the domain. However there is the following scenario that I want to account for:
UserID LastUpdate
A@A.com 2020-08-16
B@A.com 2019-05-16
C@A.com 2021-05-05
With the current query, B@A.com will be captured, and therefore domain A.com will be captured as not in use, but the user C@A.com is still active - I want to do a Max(a.lastupdate) but have it grouped by  substring_index(a.UserID, '@', -1)
but also spit out all the users for that domain.
I'm sure the answer is starring me in the face...


texts: Do I need MySQL Group Replication or a Single Server is enough for Click Counting, etc?

texts:
I'm planning to create a system which tracks visitors clicks into the database. I'm expecting around 1M inserts/day into the Database.

On the backend, I'll have an analytics system which will analyze all the data that's been collected over the days/weeks/months/years.

My question is: is it a practical approach to have 2 different MySQL Servers + 1 Web server? MySQL Server A would insert the clicks into it's DB and it would be connected to MySQL Server B by group replication, so whenever I create reports, etc on MySQL Server B, it doesn't load Server A heavily.

These 2 Database servers would then be connected to the Web Server which would handle all the click requests and displaying the backend reports also.

Is it a practical solution, or is it better to have one bigger server to handle all the MySQL data? Or have multiple MySQL servers that are load balancing each other? Anything else perhaps?


texts: Why is my database stuck in the Restoring state?

texts:
I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the beginning of these tests.

The restoration code looks like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY
ALTER DATABASE {0} SET MULTI_USER

After this, the tests connect to the database and do whatever they need to do.

The problem is that, during the tests debug, if eventually I need to hit the stop button to cancel the tests, the database is being left in the Restoring state forever. It's strange because it only happens when I stop the debug session. If I have 20 tests and all of them restores the snapshot prior to the test, I'll get no error during these test executions.

Do you have any suggestions of what might be causing this?

EDIT

Complementing the @usr's response, to recover the database from the inconsistent state at the beginning of the tests, it's necessary to add the REPLACE option to the restore statement.

It will work if it's like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY, REPLACE
ALTER DATABASE {0} SET MULTI_USER


texts: Wich solutions is better for translate some data?

texts:
I've a little mobile app who need some translation. Currently, I just want to translate my app in one language, I don't think I'll need more. So I'm facing a problem, should I translate stuff in database, or in translations files on client side ?
The user will be able to select his language, but first i need to determine it with the locale variable for the registration (because he must select his country at registration).
First I tought about creating one translation table for each table who need it.

But it seems to not be the good solution if I need to add more language, I'll have to alter all translations tables.
So, my second solution is to create a language table, keep the translation table for each table but as one-to-many relationship.

I think this solution is better, but I'm not sure about performance. I know it's a little app and the question does not really arise, but we never know.
Now, I'm stuck because It means that every time I need translation, I have to query the database.
There are tons of contries, and I'll like to have auto completion. Fruits will be displayed in several pages and used in others tables.
So I thought about keep translations on client side within json files.
For exemple translations/fr_FR.json
"fruits": [
  {
    "id": 1,
    "original": "banana",
    "translation": "banane"
  },
  {
    "id": 2,
    "original": "apple",
    "translation": "pomme"
  }
],
  "countries": [
  {
    "id": "ES",
    "original": "spain",
    "translation": "espagne"
  }
]

Everytime I need translation, I can use a function to get the translation from the file.
It means more calculations, and user will have to update his app if translation has changed.
Wich solution is better ?


texts: Distinct 3 columns and 4th column with sum

texts:
how can I select in a Oracle DB all columns but some of them DISTINCT.
I have columns A,B,C that I want distinct and column D that I want the sum of the rows.

A   B   C   D
1   2   3   10
1   2   3   20
1   2   4   1000

And I want the result

A   B   C   D
1   2   3   30
1   2   4   1000

Thanks!


texts: I am looking for an automated solution to scrub sensitive data from my prod environment to my DEV and DEVINT environments so that I don't have to write lots of code to get this done. Does anyone know if Data Quality Services and a data cleansing step in SSIS can do the trick for me? Or, does anyone else have any suggestions to scrub my data without having to write TSQL to do it?


texts:
I have a stored procedure that among other things builds and executes a large number of Dynamic SQL statements. (Don't hate me, I didn't write it.)

I would like to retrieve the actual execution plan for the overall procedure so that I can analyze it in SQL Sentry plan explorer.

Turning on Actual execution plan when I execute the PROC in a query window just crashes Management studio when I try to look at it and save it off.

If I turn on SHOW STATISTICS XML that just results in each individual statements plan xml as a separate result. That doesn't help me because I need the entire plan as a whole.

My problem is I would like to get a .sqlplan file saved off of the server and save it to my local machine where I can analyze it in Plan Explorer. The reason is that I cannot connect directly to the server running Plan Explorer. RDP through a proxy is my only connection method, so I can only analyze .sqlplan files that have been saved and that I have retrieved to my local machine for analysis.

When I retrieve the estimated plan for the Procedure I can save off the returned graphical plan as a .sqlplan file that has a single root node and a single Batch Sequence node. I can then bring that file to my local computer and open it in Plan Explorer and the software can see all of the different statements that make up the batch.

If I turn on XML Statistics, each statements plan is returned as it's own standalone plan with it's own root node. I can't put that in a single file because that's not valid xml.

Any ideas?

Thanks,
Bill


texts: Altering nullability of column in SQL Server

texts:
Say I have the following table:

CREATE TABLE test(test_id int not null identity primary key,
 field1 int not null,
 field2 int
);
CREATE INDEX IDX_test_field1 ON test(field1);
CREATE INDEX IDX_test_field2 ON test(field2);

Now ALTER TABLE test ALTER COLUMN field1 int works, and field1 allows null.

Nevertheless, I cannot change ALTER TABLE test ALTER COLUMN field2 int not null because of

  ALTER TABLE ALTER COLUMN field2 failed because one or more objects
  access this column.

Also, I cannot change field1 back to not null.

However, I can add and then drop check constraint as many times as I want :

ALTER TABLE test  ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL);
DROP CONSTRAINT CHK_NN_field2` without any problems.

Is it well defined behaviour? Could someone explain why that happens or point me to documentation?

I'm using SQL Server 2008 R2 if that matters.

Thank you.


texts: Oracle copy DB from one windows server to another

texts:
I am not an Oracle DBA but looking for the best approach to copy/clone an Oracle database from one windows server to another, here is my configuration and my requirements:

Configuration
Server 1 (Source)
Oracle DB Version: 10.2.0.4
OS: Windows Server 2008 Standard
Server2 (Destination)
Oracle DB Version: 11.2.0.3
OS:  Windows Server 2008 Standard
On the destination server, Oracle is installed but no DB is created

Requirements
1.  It is preferable that the source DB does not shutdown during the process, I switched to archivelog mode for this purpose
2.  The Source database size is around 200Gb and there is no enough space on the Source server to make an RMAN backup then copy the files to the destination server, I am looking for a way to back up the DB directly on the remote server (destination) but I am not able to do it through RMAN for Enterprise Manager


texts: Getting View of One to Many where Column Names are repeated with Indexed field

texts:
I have 4 tables joined with an inner join.
2 of these joined tables are 1 to Many relationships. Consider the following example return where t1 is table 1, t2 is table 2 and so forth. table 1 has a 1 to many relationship with table 2 and like wise with table3.

I would like to get a result set back like

t1.Column1,  `t2.ItemDescItemOrdinalColumn1,t2.ItemDescItemOrdinalColumn2,
t3.ItemDescItemOrdinalColumn1, t3.ItemDescItemOrdinalColumn2

`

Where ItemDesc can be a literal, ItemOrdinal is an index number that is stored in the table for the group.

T1, Id, Description;
T2, Id, T1.Id, ItemOrdinal, ItemDesc, other columns;
T3, Id, T1.Id, ItemOrdinal, ItemDesc, other columns;

I am trying to model after an XML file to import it into a database and also model that in a view.

<Inventory>
<Site>

<Item1Amt>37.00</Item1Amt>
<Item1Desc>Molded Plastic Handle</Item1Desc>
<Item1Qty>2.0</Item1Qty>

<Item2Amt>34.00</Item2Amt>
<Item2Desc>Chrome plated Knob</Item2Desc>
<Item2Qty>8.0</Item2Qty>

</site>
</Inventory>

SQL FIDDLE :

Expected output :
Columns Named
[t1.Description] , Item[ItemOrdinal]ColumnNameQTY,Item[ItemOrdinal]ColumnNameAMT

I hope by adding the xml I am trying to import and the sql fiddle table will help me. Maybe I am wrong in my attempt or design - but I am trying to flatten out the result set in a view.

EDIT:
Added the Columns in DbFiddle to look more like the xml I have. To make it more readable I am showing Item1Amt,Item1Desc,Item1Qty; . but this row would repeat to Item 6 - I am doing a view here because an older system will need to look at the data. And that is how the older db system was designed.

Tbl_1.Description, Item1Amt,   Item1Desc    , Item1Qty,
           M_5888,   3.0   , M Number 5888_1,       12,

For better readability this is a Continuation of Row Result :

Item2Amt,  Item2Desc     , Item2Qty,
    51.0, M Number 5888_2,      5

DbFiddle Link


texts: During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.


texts:
We are changing one of our application user logins from being dbowner to a more granular permission scheme--finally.

Is there a way from within SQL Server 2016 that I can track permission violations? That is, this new user cannot SELECT from table dbo.TableName so that I can review it and determine if it is appropriate or not.

I know I can trap errors from the program side of things and log them, but I would rather not rely on that if the database can log that for me.


texts:
I have the following problem:

I created two tables:

CREATE TABLE DimServers
(
    [ID_DimServer] INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
    [ServerName] VARCHAR(13) NOT NULL,
    [Domain] VARCHAR(10) NOT NULL,
    [PIT] VARCHAR(4),
    [PhysicalMemoryMB] INT,
    [AvailablePhysicalMemoryMB] INT,
    [MemoryInUseMB] INT,
    [CPU] INT,
    [HyperthreadRatio] INT,
    [SystemUpTime] VARCHAR(50),
    [WindowsVersion] VARCHAR(50) NOT NULL
)

CREATE TABLE DimDrives
(
    [ID_DimDrive] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [DimServer_ID] INT FOREIGN KEY REFERENCES DimServers(ID_DimServer),
    [Drive] VARCHAR(1) NOT NULL)
)

After that I do an INSert with data from an another server:

INSERT INTO DimServers
(
    [ServerName],
    [Domain],
    [PIT],
    [PhysicalMemoryMB],
    [AvailablePhysicalMemoryMB],
    [MemoryInUseMB],
    [CPU],
    [HyperthreadRatio],
    [SystemUpTime],
    [WindowsVersion]
)
SELECT
     sq.[ComputerNamePhysicalNetBIOS],
     sq.[Domain],
     sq.[PIT],
     sq.[TotalPhysicalMemoryMB],
     sq.[AvailPhysicalMemoryMB],
     sq.[MemInUseMB],
     sq.[cpu_count],
     sq.[hyperthread_ratio],
     sq.[SystemUpTime],
     sq.[WindowsVersion]
FROM [x].[y].[dbo].[sqlsysinfo] as sq

Now I don't know how to do it, if it has a foreign key.


texts: Inserts/updates are extremely slow with Merge Replication in SQL Server 2008

texts:
I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it takes many hours (over 12 hours in some cases) to insert or update a record. Honestly that is just unacceptable.

As an example I am trying to insert one record into a table (ComputerUsers) that links a laptop to a user. You only have two columns (one is call ComputerID and one is called UserID). There is a Computers table that stores the ComputerIDs and the Computername. There also is a Users table that stored the UserID and username.

All I am trying is insert one record into the ComputerUsers table. Last time I ran it it took over 13 hours. This table is published and it is part of the Dynamic Filter.

I have tried everything I have read online. I created two separate indexes for the UserID and ComputerID columns since they are both included in separate joins in the Filter. There is also a clustered primary key on the Userid and ComputerID columns since the combination of the two are unique (A computer can be assigned to two users or vica versa). I have also have tried rebuilding the indexes and even created some suggested indexes on the merge replication tables.

I am at my wit's end. What am I missing? I find it hard to believe this is normal for Replication because I doubt anyone would use it if it was. Thanks.


texts: I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to distribute the load. I get "server closed the connection unexpectedly" intermittently when connecting through HA Proxy. If I connect directly to one of the PG Bouncer instances I do not receive these errors. Has anyone setup something similar to this?


texts: How to upgrade a SQL 2000 Database to SQL 2005 - need confirmation?

texts:
I need to confirm the process for upgrading a standard SQL 2000 database to SQL 2005. As I understand it, there are at least 2 methods for upgrading. Back\Restore and Detach\Attach DB.  In my case, I need to leave the original SQL 2000 Database online, so I believe it is best to do a Back\Restore.
Can someone confirm the process below and shed some light on my questions?

On the SQL 2000 Server

Take a Full Backup of the Database
Databases->[Database]->All Tasks->Backup Database..., selecting 'Full', and saving the file (.bak)
Move the backup file (.bak) to the new SQL 2005 server

On the New SQL 2005 Server

Copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server.
Rename files if desired to something more standard
Create a New SQL 2005 Database.
Perform a Restore of the backup file (.bak)
Select “Options” tab. This will have the mdf and ldf locations that were in the backup file. Change these to legitimate directories on your new Server.
Change the Compatibility to “SQL Server 2005 (90)”
Questions:

What performs the Upgrade?
Is the SQL 2000 Database automatically UPGRADED during the restore, by the SQL 2005 Engine?
Do I need to copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server?
Do I need to create a New SQL 2005 database?

Thanks in advance for all of your help.
Jorge


texts:
I have a Microsoft SQL Server 2008 R2 database.

I have some applications in different programming languages. Some of them are legacy and I would not like to modify them.

Is there a way to log at server level all query errors, regardless of the application causing it?

I would like to know, for each error, the query causing it, the error type, and ideally loginame and hostname.


texts: How can we establish a real time synchronization from SQL server to PostgreSQL Database? We have tried to automate the jobs in BI but that does not serve the purpose as we need real time data from source database. Do I need to use third party tools or would SQL Server Management Studio be sufficient to achieve this?


texts: How can I manage an exception generated by Postgis?

texts:
I am running the following query on Postgres 10.3/Postgis 2.4:

create table table_c as (
    select A.*, B.area_name, B.id
    from table_a A
    left join table_b B
    on ST_Contains(A.geom, B.geom)
);

that fails with a cryptic:

ERROR:  GEOSContains: TopologyException: side location conflict at -2.0889721121124643 53.528652265475735

due to Postgis (and relative used libraries).

Is there a way to skip exceptional cases and keeping the query running until finished?


texts: Ms sql MERGE INTO locks whole table for updates

texts:
I have a table for statistic values, it holds millions of records, which is defined like this:

CREATE TABLE [dbo].[Statistic]
(
    [Id] [INT] IDENTITY(1, 1) NOT NULL
  , [EntityId] [INT] NULL
  , [EntityTypeId] [UNIQUEIDENTIFIER] NOT NULL
  , [ValueTypeId] [UNIQUEIDENTIFIER] NOT NULL
  , [Value] [DECIMAL](19, 5) NOT NULL
  , [Date] [DATETIME2](7) NULL
  , [AggregateTypeId] [INT] NOT NULL
  , [JsonData] [NVARCHAR](MAX) NULL
  , [WeekDay] AS (DATEDIFF(DAY, CONVERT([DATETIME], '19000101', (112)), [Date]) % (7) + (1)) PERSISTED
  , CONSTRAINT [PK_Statistic]
        PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [IX_Statistic_EntityId_EntityTypeId_ValueTypeId_AggregateTypeId_Date]
ON [dbo].[Statistic] (
                         [EntityId] ASC
                       , [EntityTypeId] ASC
                       , [ValueTypeId] ASC
                       , [AggregateTypeId] ASC
                       , [Date] ASC
                     );

CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[Statistic] ([Date] ASC);

CREATE NONCLUSTERED INDEX [IX_EntityId]
ON [dbo].[Statistic] ([EntityId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_EntityType_Agg_Date]
ON [dbo].[Statistic] ([EntityTypeId] ASC, [AggregateTypeId] ASC, [Date] ASC)
INCLUDE ([Id], [EntityId], [ValueTypeId]);

CREATE NONCLUSTERED INDEX [IX_Statistic_ValueTypeId]
ON [dbo].[Statistic] ([ValueTypeId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_WeekDay]
ON [dbo].[Statistic] ([AggregateTypeId] ASC, [WeekDay] ASC, [Date] ASC)
INCLUDE ([Id]);

ALTER TABLE [dbo].[Statistic]
ADD CONSTRAINT [PK_Statistic]
    PRIMARY KEY NONCLUSTERED ([Id] ASC);

During updates with merge, sql server locks the whole table instead of pages/rows, @inTbl is a key/value datatable passed as parameter

MERGE INTO Statistic AS stat
USING
    (SELECT inTbl.EntityId, inTbl.Value FROM @p0 AS inTbl) AS src
ON src.EntityId = stat.EntityId
   AND stat.EntityTypeId = @p1
   AND stat.ValueTypeId = @p2
   AND stat.Date IS NULL
   AND stat.AggregateTypeId = @p3
WHEN MATCHED THEN
    UPDATE SET stat.Value = src.value
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EntityTypeId, ValueTypeId, Date, AggregateTypeId, EntityId, Value)
    VALUES
    (@p4, @p5, @p6, @p7, src.entityId, src.value);

So, I have 2 problems:
1) the merge sometimes takes forever to finish

2) updates like this wait for merge to finish:

UPDATE [dbo].[Statistic]
SET [Value] = @p0, [JsonData] = @p1
WHERE [EntityTypeId] = @p2
      AND [ValueTypeId] = @p3
      AND [Date] = @p4
      AND [EntityId] = @p5
      AND [AggregateTypeId] = @p6;

I have plans/locks files for the queries, but they are rather big, so here they are

before index rebuid: https://www.brentozar.com/pastetheplan/?id=S19EgxYIB

after index rebuild: https://www.brentozar.com/pastetheplan/?id=SyjexxtLH

What can be the problem? This happens occasionally and may sometimes go away after clustered index rebuild.

The clustered index goes fragmeted to 90+% in a day or so. How can I prevent this fragmentation?


texts:
To view "recent expensive queries" I use Activity Monitor. Some of the queries there are with parameters.

Is there a way to know the values?


texts: Group by multiple column acquired from join

texts:
this is part of a query result :

all three columns are user_ids acquired from multiple tables join .
as you see each row has only one user_id and two other user_ids are necessarily null .
I'm going to group this rows by user_id regardless of the column it is on.

so i need to have all user_ids in one column (instead of three) to make me enable to perform group by on it. the ultimate result should be like this:


texts: How to generate missing values for each employee for each day

texts:
CREATE TABLE `EmpWorkCount` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `day` int(10) unsigned NOT NUL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `daycount` int(10) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Current Values in the table :

    emp_Id    |       day            | daycount  |
       1      |        0             |  10       |
       1      |        1             |  11       |
       2      |        1             |  13       |
       2      |        0             |  12       |

.

CREATE TABLE `Emp` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Emp Table looks like :

emp_Id    |       created_At            | updated_At            |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   3      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |

I need to run a query where I need a count of EmpWorkCount with different day values (0, 1, 2, 3) for each employee

    Select emp_Id, day_list.day,
    IF(daycount = NULL, 0, daycount)  as daycount from (
        SELECT 0 day
           UNION ALL
           SELECT 1
           UNION ALL
           SELECT 2
           UNION ALL
           SELECT 3   )day_list
      LEFT JOIN
      select emp_Id, day, daycount from EmpWorkCount
   )

(my query is incorrect, but I don't know how I multiply these 4 values from day for each employee

this gives output for the emp_Id, day values present in the table e.g.

emp_Id    |       day            | daycount    |
   1      |        0             |  10         |
   1      |        1             |  11         |
   2      |        1             |  13         |
   2      |        0             |  12         |
   NULL   |        2             |  0          |
   NULL   |        3             |  0          |

but if the database does not contain let's say emp_Id : 1 with day values = 2 or 3 these records don't appear with a count 0.

Output that I look for :

    emp_Id    |       day            | daycount    |
       1      |        0             |  10         |
       1      |        1             |  11         |
       1      |        2             |  0          |
       1      |        3             |  0          |
       2      |        1             |  13         |
       2      |        0             |  12         |
       2      |        2             |  0          |
       2      |        3             |  0          |
       3      |        0             |  0          |
       3      |        1             |  0          |
       3      |        2             |  0          |
       3      |        3             |  0          |

How do I go about this one?

In my query I have not joined with Employee Table --> but the output should even contain Employees which do not have a entry in EmpWorkCount Table.


texts:
There is a relational database with the following Users table

User
Id
Name
Age
Parents
Friends
Colleagues
...

And there are tables Parents, Friends, Colleauges, etc. Due to specifics of database this part of User info is actually a single document that is always used as whole piece

Parents
Friends
Colleagues
...

Are there any advantages to remove all this tables and use the following Users table instead

User
Id
Name
Age
People

Where people are document with a special structure, for example, JSON or XML? This database is not truely relational. Will it be faster, easier to develop and maintain, etc.?