texts: Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination

texts:
I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here

My SSIS package has a single data flow task which just gets data from a single MS SQL table and transfer it to Postgres. Here is MS SQL table

CREATE TABLE [dbo].[DailyGgrNgr](
    [Date] [DATETIME] NOT NULL,
    [PlayerId] [INT] NOT NULL,
    [GameId] [INT] NOT NULL,
    [GGR] [DECIMAL](18, 4) NOT NULL,
    [NGR] [DECIMAL](18, 4) NOT NULL,
    [BetCount] [INT] NOT NULL,
    [WinCount] [INT] NOT NULL,
    [BetAmount] [DECIMAL](18, 4) NOT NULL,
    [WinAmount] [DECIMAL](18, 4) NOT NULL
)

And this the PostgreSQl table

CREATE TABLE public."DailyGgrNgr" (
"Date" timestamp NOT NULL,
"PlayerId" int4 NOT NULL,
"GameId" int4 NOT NULL,
"GGR" numeric(18,4) NOT NULL,
"NGR" numeric(18,4) NOT NULL,
"BetCount" int4 NOT NULL,
"WinCount" int4 NOT NULL,
"BetAmount" numeric(18,4) NOT NULL,
"WinAmount" numeric(18,4) NOT NULL

);

The problem is that data sync is done row by row, instead of bulk as expected. I understand this by profiling my postgres server and there was only a single insert like this

INSERT INTO "public"."DailyGgrNgr" ( "Date","PlayerId","GameId","GGR","NGR","BetCount","WinCount","BetAmount","WinAmount") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)

SSIS Destination is configured to process rows in batches as you can see from the schreenshot below

However it still inserts data row by row. Below you can see my ODBC Data Source configs.

I believe the problem is with driver because when I use other commercial drivers they works fine. Also according to Microsoft  - "If you select Batch and the provider does not support this method, the ODBC destination automatically switches to the Row-by-row mode."

I wonder if there is a possibility to configure the driver to process the rows in batches instead of row by row or is there another free ODBC driver for postgres that allows batch processing.

My Postgres version is PostgreSQL 12.2, SQL Server 2016.


texts: How do I determine if a table exists in a SQL Server database in SQL Server 2008?


texts:
I'm not a DBA, I've only googled what MSDB does it's basically a DB of SQL Agent of its Job and History, Now I'm running out of space in my cloud server and I have 1 year worth of MSDB year 2017, Is it okay to delete this or do I keep it for backup purposes?

My MSDB is 93GB in a 250GB HDD.


texts: MySQL init script fails, but mysqld_safe works just fine?

texts:
TLDR = /etc/init.d/mysql start fails after the follwoing line (line 22), and I would like to fix it so that init starts mysqld automatically.

Problem line:

. /lib/lsb/init-functions

Background:

This happend after I attempted to install KDE on this workstation. KDE decided to uninstall mysql-server (for some strange reason), which caused all sorts of problems including corrupt tables, which I restored from a backup.

Everything is not fixed, EXCEPT the init scripts do not work.

I narrowed it down to this line by putting "echo 'ok'" after each statement as it executes from top to bottom to see the last executing line. At first, it died because /usr/bin/mysqld_safe did not exist. apt-get remove --purge [insert all mysql packages here] followed by fixing all broken dependencies,and then apt-get install mysql-server mysql-client fixed that.

This line appears to be a line used by all init scripts, and the other init scripts execute just fine. So, I have my doubts that this is the problem. However, echoing "OK" just above this statement, and then trying to echo it just below this statement show a successful "OK" followed by nothing. So, it appears the code path ends here.


texts: T-SQL script to check if index rebuild operations are online are off

texts: I wanted to see if it was possible to determine whether my indexes are rebuilt online or offline with some T-SQL. I'm currently trying to troubleshoot extra disk space used during a rebuild procedure and I thought this may me a good place to start.


texts: Is DBCC SHRINKFILE better than DBCC SHRINKDATABASE?

texts:
In the documentation to DBCC SHRINKDATABASE, Microsoft is warning to run the command because it's i/o expensive.
However, in the documentation to DBCC SHRINKFILE, there is no such warning.
Now when we use simple recovery and basically have only one database file, is it better to run DBCC SHRINKFILE or DBCC SHRINKDATABASE?


texts: How do I debug an Oracle Linux Diskspace Issue?

texts:
I have about 429GB Diskspace allocated to this VM.

I see that only 179GB is being used.

Now when I try to add a 128GB file I get an I/O error saying I do not have any space left.

I need to be able to use all of the space. I do not see any disk quota issue.

Here are some statistics. I know its probably something rudimentary; however, I am unable to find the right google search either.

Could somebody help me?

[root@Accelerant-EBS /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_accelerantebs-lv_root
                       50G   45G  2.6G  95% /
tmpfs                 2.8G  420K  2.8G   1% /dev/shm
/dev/sda1             485M  108M  352M  24% /boot
/dev/mapper/vg_accelerantebs-lv_home
                      339G  127G  195G  40% /home
[root@Accelerant-EBS /]# fdisk -l

Disk /dev/sda: 429.5 GB, 429496729600 bytes
255 heads, 63 sectors/track, 52216 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000d6093

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64       52217   418917376   8e  Linux LVM

Disk /dev/mapper/vg_accelerantebs-lv_root: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_accelerantebs-lv_swap: 6123 MB, 6123683840 bytes
255 heads, 63 sectors/track, 744 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_accelerantebs-lv_home: 369.2 GB, 369157472256 bytes
255 heads, 63 sectors/track, 44880 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@Accelerant-EBS /]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Wed Aug 28 15:00:33 2013
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_accelerantebs-lv_root /                       ext4    defaults        1 1
UUID=f5bb9829-567a-4d7f-8f7b-9dbb3297636e /boot                   ext4    defaults        1 2
/dev/mapper/vg_accelerantebs-lv_home /home                   ext4    defaults        1 2
/dev/mapper/vg_accelerantebs-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0


texts: Pivot Multiple Columns with a Cross Apply

texts:
I am trying get the following result:

At the moment, my query (and hopefully a guide to table structure) resembles:

WITH CoreGateway AS
(
SELECT      g.GatewayTitle
,           g.GatewayOrder
,           ggs.GatewayGatewayStepId
FROM        ref.Gateway             g
JOIN        ref.GatewayGatewayStep  ggs ON  ggs.GatewayId       = g.GatewayId
JOIN        ref.GatewayStep         gs  ON  gs.GatewayStepId    = ggs.GatewayStepId
WHERE       gs.GatewayStepTitle     = 'Carbon Calculated'
)
, CarbonCalc AS
(
SELECT      pg.ProjectId
,           pg.ProjectGatewayId
,           pg.GatewayGatewayStepId
,           cc.tC02
,           cc.CarbonCost
FROM        pmp.ProjectGateway      pg
LEFT JOIN   pmp.CarbonCalculated    cc  ON  cc.ProjectGatewayId = pg.ProjectGatewayId
WHERE       pg.ProjectId = 3021
)
, CarbonData AS
(
SELECT      cc.ProjectId
,           cg.GatewayTitle
,           cg.GatewayTitle + '1' AS GatewayTitle1
,           cg.GatewayOrder
,           cc.tC02
,           cc.CarbonCost
FROM        CoreGateway     cg
LEFT JOIN   CarbonCalc      cc ON cc.GatewayGatewayStepId = cg.GatewayGatewayStepId
)
SELECT      p.ProjectId
,           p.SolutionId
,           p.NeedId
,           p.ProjectCode
,           p.ProjectDescription
,           ISNULL([Gateway 1], 0.0)        AS Gateway1_tC02
,           ISNULL([Gateway 3], 0.0)        AS Gateway3_tC02
,           ISNULL([Gateway 5], 0.0)        AS Gateway5_tC02
,           ISNULL([Gateway 9], 0.0)        AS Gateway9_tC02
,           ISNULL([Gateway 11], 0.0)       AS Gateway1_CarbonCost
,           ISNULL([Gateway 31], 0.0)       AS Gateway3_CarbonCost
,           ISNULL([Gateway 51], 0.0)       AS Gateway5_CarbonCost
,           ISNULL([Gateway 91], 0.0)       AS Gateway9_CarbonCost
FROM        CarbonData  cd
PIVOT       (
            MAX(tC02) FOR GatewayTitle IN ([Gateway 1], [Gateway 3], [Gateway 5], [Gateway 9])
            ) tc02
PIVOT       (
            MAX(CarbonCost) FOR GatewayTitle1 IN ([Gateway 11], [Gateway 31], [Gateway 51], [Gateway 91])
            ) tc02
JOIN        pmp.Project p   ON p.ProjectId = tc02.ProjectId

Which yields a result of:

As we can see, not all data has been entered in either ProjectGateway or CarbonCalculated, which is fine as they are optional. And the Pivot appears to be performing a Cartesian.

What is the best approach to pivot data for the 2 fields, when columns can be optional?


texts: SQLPlus configuration problem or firewall issue

texts:
I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica.
[root@ansible-test ~]$ telnet <oracle_db_active> 1521
Trying <oracle_db_active>...
Connected to <oracle_db_active>.
Escape character is '^]'.

^CConnection closed by foreign host.

[root@ansible-test ~]$ telnet <oracle_db_replica> 1521
Trying <oracle_db_replica>...
Connected to <oracle_db_replica>.
Escape character is '^]'.

^CConnection closed by foreign host.

I'm able to telnet to the DB on port 1521.
But when I try to connect using SQLPlus I get an error as the host doesn't exist.
[root@ansible-test ~]$ echo exit |sqlplus "<user>/<pass> @(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_active>)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_replica>)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <service>)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 12)(DELAY = 5))))"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 7 17:37:23 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name: Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

I have another server in the same network as the DB and I'm able to connect with the same string.
I don't have an sql.ora or tnsname.ora configure on any of the servers.
The only difference between the two servers is that one is RedHat and the other centos.
Do I need to open any other port to the DB o it is a configuration issue?
Thanks
(sorry for all the redacting)


texts: Is it OK to blindly add missing indexes?

texts:
I often use SSMS to test my slow stored procedures for missing indexes.  Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index.  This results in a faster query every time as far as I can tell.

Any reason why I shouldn't continue to do this?


texts: Joining 2 tables based on Dynamic criteria

texts:
Hoping you can help me. I have 2 Tables (Table PS & Table A).

What i am trying is the following ;

Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following conditions ACCOUNT_CODE__C and  I or T or C.

For example :

In the scenario below for Table A for ACCOUNT_NUMBER = '1234' the Id_Type = 'I', therefore, i want it to to search the PS table for ACCOUNT_CODE__C '1234' and against column I

If in future scenario below the for table A for ACCOUNT_NUMBER = '1234' and has Id_Type of = 'T', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column T

If in future scenario below the for table A for ACCOUNT_NUMBER  = '1234' the Id_Type = 'C', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column C

There may be a scenario where Table A ACCOUNT_NUMBER = '1234' the Id_Type could have a mixture of "I", "T" or "C".

Where it matches then provide the join. Where it doesn't match enter a new line item and populate the value a

In the expected outcome below, you can see that the following Id ("LU0336376248 & LU0430493568") from the PS table are not present in Table A. We can also see that "LU0513029156" inst in present in Table PS.

Table PS & Data

create table #PS
(
ACCOUNT_CODE__C nvarchar(80),
ID nvarchar(80),
I nvarchar(80),
T nvarchar(80),
C nvarchar(80)
);

INSERT INTO #PS
VALUES
('1234', 'a1vf1000001icrBAAQ','LU0430492750', 'TESTCAEU LX', 'L5784E347'),
('1234', 'a1vf1000001icrCAAQ','LU1162086109', 'TESTGIHA LX', ''),
('1234', 'a1vf1000001icrDAAQ','LU0336376248', 'TESTGEUD LX','L57838408'),
('1234', 'a1vf1000001icrEAAQ','LU0430493568', 'TESTGBCH LX', 'L5784E420');

Table PS Result :

select * from #ps

ACCOUNT_CODE__C ID  I   T   C
1234    a1vf1000001icrBAAQ  LU0430492750    TESTCAEU LX L5784E347
1234    a1vf1000001icrCAAQ  LU1162086109    TESTGIHA LX
1234    a1vf1000001icrDAAQ  LU0336376248    TESTGEUD LX L57838408
1234    a1vf1000001icrEAAQ  LU0430493568    TESTGBCH LX L5784E420

Table A & Data

create table #a
(
ACCOUNT_NUMBER nvarchar(80),
ID nvarchar(80),
Id_Type nvarchar(80)
);

INSERT INTO #a
VALUES
('1234', 'LU0430492750', 'I' ),
('1234', 'LU1162086109', 'I' ),
('1234', 'LU0513029156', 'I' );

Table A Result :

select * from #a

ACCOUNT_NUMBER  ID  Id_Type
1234    LU0430492750    I
1234    LU1162086109    I
1234    LU0513029156    I

Expect Outcome When tables Joined

ACCOUNT_NUMBER  ID        Table PS   Table A
1234    LU0430492750       Y           Y
1234    LU1162086109       Y           Y
1234    LU0336376248       Y           N
1234    LU0430493568       Y           N
1234    LU0513029156       N           Y

Looking forward to your help & support


texts: Disabling the recycle timeout settings in SSRS

texts:
In order to prevent periodic recycling of the SSRS service it's been suggested to disable the <RecycleTime> setting in the RS server's config file.  I believe disabling would require setting this value to 0 (i.e., <RecycleTime>0</RecycleTime>).  Is this correct?
Also, I've heard of alternate methods of scheduling an 'empty' report to run once an hour to ensure the idle time of 720 minutes is never reached.  Is it recommended to use this approach instead of setting the <RecyleTime> setting to 0?


texts: Capturing sp_executesql statement in sql profiler

texts:
After catching some data in a SQL Profiler session, I can almost always see a second entry of type RPC:Completed for each query using a sp_executesql statement. I know that this is done to create re-usable execution plan by reducing the query with parametrization.

e.g.

exec sp_executesql N'SELECT s.* FROM s WHERE s.C= @O',
@O uniqueidentifier',
@O='11112222-4444-3333-1212-5555666677'

Duration: 10 ms

Next entry:

SELECT s.* FROM s WHERE s.C= @O

Duration: 10 ms

Looking at the total sum(Duration) of the profiler events, do I have to count both of those? The query was executed once, so 10 ms was the correct result. Instead the total result is 20 ms, with I think is just WRONG and a program-error of Profiler. Do I have to filter one of them away anyhow and how?


texts: How to update two tables using subquery in postgresql?

texts:
I have two tables

main table is

Complaintid Status  Templateid
123           2
135           5      2
142           5      8
143           3
145           5

Second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                     0             0     Abc.jpg
123                     2             0     Abc.jpg
135                     5             0     Xyz.jpg
142                     5             0     Sim.jpg
143                     3             0     123.jpg
145                     2             0     Ing.jpg
145                     5             0     Ing.jpg

After updation first table

Complaintid Status  Templateid
123          2
135          1       2
142          1       8
143          3
145          5

After updation second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                  0                0     Abc.jpg
123                  2                0     Abc.jpg
135                  1                0     Xyz.jpg
142                  1                0     Sim.jpg
143                  3                0     123.jpg
145                  2                0     Ing.jpg
145                  5                0     Ing.jpg

Here is the first query

Update complaints
set status='1'
where templateid is not null

but i don't know how to add subquery in above query


texts: min/max performance versus order by limit in sqlite?

texts:
I know that combining min/max in one query is not performant. (See, for example, https://stackoverflow.com/questions/11515165/sqlite3-select-min-max-together-is-much-slower-than-select-them-separately). I also know that for SQL, the recommended approach is to use min/max. (See https://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) However, how do these functions compare individually against ordering and limiting in sqlite?

And to be clear, these are the queries in question:

SELECT MIN(column)
FROM table;

SELECT column
FROM table
ORDER BY column
LIMIT 1;


texts: Give execute permission to user which does not have execute permission for specific stored procedure

texts:
I have a user which just have view rights i.e. it does not have execute permission. But for a specific stored procedures I want it to have execute permission.So I followed the following process.
A user have permission to execute stored procedure and B user does not have the execute permission.

So while creating on stored procedure I wrote as.

CREATE PROCEDURE sampleSP
WITH EXECUTE AS 'A'
AS
BEGIN
      --stored procedure body
END

And I am executing stored procedure as following with the login rights of B user.

EXEC sampleSP

But it is giving me the following error.

The EXECUTE permission was denied on the object 'sampleSP', database 'SAMPLEDB', schema 'dbo'.


texts: Mirroring failed over - created 2 principal database and will not allow me to drop one of them

texts:
For some reason my mirrored servers triggered an automatic failover. I have 2 databases that both think they are the principal server, one is "Principal, Synchronized/In Recovery" and the other is "Principal, Synchronizing"

I figure this is bad. My plan is to rebuild mirroring by dropping the database on one server and restoring it again based on the backup of the other. I have removed mirroring on one of the servers. And took a backup. However, I cannot drop the database on the other server. I have tried single user mode, detaching and taking database offline.

I have tried to kill process using this database but it doesn't work. The command it is on is "KILLED/ROLLBACK" but nothing changes here.


texts: Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle?

texts:
In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.

For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER options:

  ORDER

  Specify ORDER to guarantee that sequence numbers are
  generated in order of request. This clause is useful if you are using
  the sequence numbers as timestamps. Guaranteeing order is usually not
  important for sequences used to generate primary keys.

  ORDER is necessary only to guarantee ordered generation if you are
  using Oracle Database with Real Application Clusters. If you are using
  exclusive mode, sequence numbers are always generated in order.

  NOORDER

  Specify NOORDER if you do not want to guarantee sequence
  numbers are generated in order of request. This is the default.

Does Microsoft SQL Server provide a strong ordering constraint for SEQUENCEs? Or does Microsoft not consider it important in general?


texts:
I've read that one big disadvantage of the database per tenant model in multi-tenant applications is the extra resource cost of having many (potentially thousands or tens of thousands) databases online at the same time.  What factors affect the number of tenant DB's I can have online at the same time?

Considering that the total amount of data will be (mostly) the same between the DB per tenant and single DB architecture why would a DB per tenant architecture reach the limits faster than a single DB?

I'm using MS SQL Server 2016 but I would be interested to hear answers for other DBMS's as well.


texts: How to filter table 2 before joining to table 1 so that the result still includes all of table 1, even if no match in table 2?

texts:
The following returns all rows from table 1 where post_type = dlm_download. It groups like values from table 2 by the matching column in table 1 so that each row in table 1 has a maximum of one matching 'result' from table 2. The result is that all records from table 1 are displayed in all cases, simply displaying null for download_date when nothing in table 2 matches (meaning nobody has downloaded the file yet):

SELECT p.post_title AS 'Download title', p.ID AS 'Download ID', MAX(l.download_date) AS 'Last download'
FROM wp_posts p
LEFT JOIN wp_download_log l ON p.ID = l.download_id
WHERE p.post_type = 'dlm_download'
AND p.post_status = 'publish'
GROUP BY p.ID

I want to additionally filter out records from table 2 that match a few conditions...before being matched up with table 1. But the following additions to the query, placed in between WHERE and GROUP BY, ultimately whittle down the result from 70 rows to 59, eliminating the rows from table 1 even if nothing in table 2 matches (previously shown as NULL for the download_date)

AND l.user_agent NOT LIKE '%bot%'
AND l.user_agent NOT LIKE '%crawl%'
AND l.user_agent NOT LIKE '%panscient%'
AND l.user_agent NOT LIKE '%scrapy%'
AND l.user_agent NOT LIKE '%bubing%'
AND l.user_agent NOT LIKE 'Go-http-client/1.1'
AND l.user_agent NOT LIKE '%guzzle%'
AND l.user_agent NOT LIKE '%jetty%'
AND l.user_agent NOT LIKE '%resty%'

This is hard for me to explain or wrap my head around, but I think what is happening is the 11 items that have not yet been downloaded by humans are effectively excluded from the result by the above conditions. In other words, I think the conditions effectively say to not return anything that doesn't match these, and because NULL in table 2 doesn't match, it is eliminating results where there is something in table 1 but not table 2 (and thus NULL for table 2).

How can I return ALL records from table 1, wp_posts, including data from table 2 when a record matches (and NULL If not), but pre-filter table 2 by eliminating things where the user agent excludes bot etc?


texts: Refresh postgres database on nightly basis

texts:
I've got a Postgres 9.2 database (production) which is almost 90 GB, I need to restore production database to development postgres server every day, what I think is to schedule a backup an restore script as cron job on linux, but the problem is it won't work in long term, as the database is growing too fast, so base backup and restore every night would take longer and longer as the size of database is increasing.

Is there a better way to refresh the development database on daily basis? for example using log shipping or something which only update the development database with delta changes.

PS: for some security reason, I can't use replication during business hours.


texts: Backing up MS SQL Server database creates a huge transation log

texts:
I'm hosting a SQL Server 2012 database server with about 100 databases.  I use the standard maintenance plan to backup all user databases daily and do hourly transaction log backups.

One database, which is about 80 MB, backs up fine at 6 AM but the following transaction log is typically 1-2 GB.  I've checked and the transaction log is getting properly truncated on the full backup but it immediately fills up after the backup.  I've tried switching to simple recovery, shinking the transaction log file, then re-enabling full recovery.  That all works fine and the .log file goes down to 4-5 MB.  However, within a day it's already up to 2 GB - most of which seems to happen when I perform the full backup.  The log has gotten up to 10 GB even though it gets truncated daily.

The database is only accessed through a single web application which doesn't appear to have anything hitting the database during the time where the transaction log balloons out.

I've attempted to query the transaction log itself but can't see anything obvious.

How can backing up an 80 MB database create a 2 GB transaction log? How can I find out what data is actually in the log and determine where it's coming from?


texts: ORA02063 error while connecting to remote databse

texts: I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.


texts: Database normalization: Which is the best fit?

texts:
I have 3 different types of data set:

Notification:

start_date
end_date
notification_type
country_id
state_id

Campaign:

start_date
end_date
user_notification

Transaction

email
transaction_value

All these 3 have the same types of child tables where there will be foreign keys pointing to these tables.

What type of design will be best?

3 different tables and one child table that will have 3 different columns as primary keys pointing to each different parent?
1 combined parent table allowing null values for columns which will be not needed for any particular type and a main_type column which says whether each record is a campaign, a notification or a transaction. The child table will have only one foreign key column.
3 different tables, 1 for each of the main table types with a child table for each main table that uses the main table's primary key as a foreign key.


texts: MySQL Master Slave Replication where master is not allowed to talk to slave

texts: I want to set up an Master Slave Replication between 2 different servers. The specialty is, that the Master is not allowed to talk to the slave, but the slave can talk with the master. I can not change slave to master, because the data inserted has to be on the master which can not talk (ping) with the slave. Can anyone tell me if I have to look at something when setting it up or can provide a guide which works good? Thanks in advance


texts: After upgrade to 2016, can't view SQL Server Logs

texts:
Just upgraded to SQL Server 2016 from SQL Server 2014 SP1 CU4.

This is a clustered instance

I am no longer able to use xp_readerrorlog and xp_enumerrorlog

Msg 22004, Level 16, State 1, Line 10
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 10
Error log location not found.

Found this link:
Unable to open SQL Server Logs

I can open the error logs manually via file explorer
The startup parameters for SQL Server Service indicate the right
location for the error logs.
Running repair on the instance didn't resolve the problem
Disabling Shared Memory for the instance didn't resolve the problem.
rebooting after all these issues and repairs didn't help
No aliases are being used to connect to this instance
127.0.0.1 is in the hosts file

Basically they are saying to repair the instance.

Any other options?


texts: Empty table being returned with plpgsql and EXECUTE USING

texts:
Why this function return an empty table (while running the plain query from command line returns different values):

CREATE OR REPLACE FUNCTION get_area_values (_tbl regclass, _column text, indicator_id integer) RETURNS
TABLE (indicator integer, postcode text, date date, value double precision) AS $$
        BEGIN
                execute format(
                'WITH ad AS (select distinct area, district from postcode_lookup)'
                || ' select $1 as indicator, ad.area as postcode, t.date, sum(t.%I) as value'
                || ' from %I as t'
                || ' join ad on t.postcode = ad.district group by ad.area, t.date', _column, _tbl)
                USING indicator_id;
        END
$$ LANGUAGE plpgsql;

I had even tried to use execute with || without any luck.


texts:
I've got the following layout Picture Cluster
RZ West is my first data center A and RZ Ost my second data center B. As you can see one secondary database of each cluster is available in the other data center.
Is it possible to read data out of Secondary 2 OST (B) and add it to the master database Primary WEST (A) automatically?
To understand the question this solved question could be helpful https://stackoverflow.com/questions/22988773/mongodb-multiple-masters-in-replicaset
Thank you very much in advance
Greetings,
Daniel


texts: SQL server - data successfully inserted in a table but not returned in Select Statement

texts:
SQL select query not returning records, even though the data insert operation is successful.

We have a table [dbo].[Login] in our database where data is inserted using the below stored procedure. The data is inserted by a windows service written in dot net. It has been working until last month and the records in the table were growing several 100 thousands. Recently, customer complained that the data in not reflected in the reports. Hence, we have investigated and found that, there is no insertion error from the stored procedure, but when we select the table no new records have been returned. We then tried setting the SQL transaction isolation level to Read un-Committed then we are able to see the newly inserted records from the table. We have reviewed the insertion stored procedure but didn't find anything suspicious.

CREATE PROCEDURE [dbo].[gp_Login_Insert]
(
      @ID int OUTPUT,   /*-- the automatically generated value will be returned as an output parameter*/

      @ReaderHardWareID int,
      @CardID int,
      @EmpID int,
      @UEmpID varchar(50),
      @Type varchar(15),
      @DateTime datetime,
      @CreatedByEmployeeId int,
      @CreatedDate datetime,
      @ModifiedByEmployeeId int,
      @ModifiedDate datetime,
      @version_ts timestamp
      )
      AS  /*  ** Add a row to the Login table  */

      if not exists(select 1 from Login where EmpID=@EmpID and DateTime=@DateTime)

      begin

         declare @eventCount int

            select @eventCount=count(*) from [login]
            where EmpID=@EmpID and [DateTime] between  DATEADD(dd, 0, DATEDIFF(dd, 0, @DateTime)) and @DateTime

        if(@eventCount<500)
        Begin

        BEGIN TRANSACTION;

        BEGIN TRY
            INSERT INTO [Login]
          ( [ReaderHardWareID], [CardID], [EmpID],[UEmpID],[Type], [DateTime], [CreatedByEmployeeId], [CreatedDate], [ModifiedByEmployeeId], [ModifiedDate]  )
          VALUES
          ( @ReaderHardWareID, @CardID, @EmpID,@UEmpID,@Type, @DateTime, @CreatedByEmployeeId, GETUTCDATE(), @ModifiedByEmployeeId, GETUTCDATE()  )

          SET @ID = (SELECT SCOPE_IDENTITY())

        END TRY
        BEGIN CATCH
            SELECT
                ERROR_NUMBER()

            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
        END CATCH;

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

        End

    END

Select Query on table returns the following results:

select transaction isolation level read uncommitted
select COUNT(*) from Login where CalFlag is null

returns 59341

select transaction isolation level read committed
select COUNT(*) from Login where CalFlag is null

returns 0

Expected Result: Since SQL server default transaction isolation is Read Committed it should return the correct row count 59341 in Read Committed mode. But it is not doing so.

  SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 >2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) >on Windows 10 Pro 10.0 (Build 18362: )

Thanks in advance,
Ujjal Sarmah


texts: Replication throw an error after failover mirror partner is down in SQL Server

texts:
Received an error mentioned below after failover when mirror partner is down.

Error :

  to fail over to a database which is not configured for database
  mirroring. code: 22037, text: 'Invalid connection string
  attributeCannot open database "DBName" requested by the login. The login
  failed.Login failed for user 'domain\user'.The connection attempted to
  fail over to a database which is not configured for database
  mirroring.'.

Scenario :

X is a Principal server and Y is a mirror partner of X
X is a publisher too, Z is a distributor server
Z is added as distributor in X and Y Server. X and Y added as publisher in Z server too
Configured Y as a "PublisherFailoverPartner" in replication log reader agent profile setting
Did failover of X
Thereafter Y became a principal and X became  mirror partner
Break mirror from Y to X or X server is down and unavailable

After above steps performed, replication is started to throw an error. When mirror is ON the replication is working, otherwise it raising an error mentioned.

Can you please suggest me steps to resolve it?


texts: Switching from SIMPLE to FULL recovery mode implications

texts:
I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability.  We don't need it on all of our databases, just the ones that are transaction heavy and contain data that is constantly being updated.

I've done lots of research and I fully understand what happens when you switch over to FULL recovery, especially how it pertains to the log file.

What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

Right now, I plan on doing one database at a time, so I can monitor log file growth and determine the best frequency for log file backups to ensure we don't get into a runaway log file situation.  I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Are there any other considerations or advice that would be helpful.  Anything I should watch out for?  We run full index rebuilds, DBCC CHECKDB and statistics rebuild once a week too.  Are there any potential problems with those operations (maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Thanks for all your help!


texts:
i know this question might be dumb and might be duplicated. but im having a problem with my left join query. so please help Y.Y

the output im currently is something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87

but the output that i want is with null like something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87
judge5    |null
judge6    |null

scores are varchar and if table has no record match from table 1 will be automatically should be null or much better for default score of 87.

my query sample

SELECT j.judge_name,s.score
FROM `tbl_judges` j
LEFT JOIN tbl_scoring s
    on j.id = s.jud_id
where s.crit_id = 1
  and s.can_id = 11


texts: How to deal with very large transaction logs in SQL Server

texts:
Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery mode and the transaction log has never been backed up.

I intend to run the transaction log backup every 15 minutes but I am afraid that the first backup may take a very very long time (hundreds of GBs of data).

What is the right course of action here?

I'm a developer by training and not a DBA so I would appreciate a more detailed answer. Thanks in advance.


texts: getIndexes returns empty array on MongoDB collection

texts:
I'm trying creating indexes on a MongoDB collection and while they are correctly created and they work (I can see the index in the executionStats), the output of the getIndexes() function is an empty array:

> db.shopOrder.createIndex({'userId': 1});
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 2,
    "numIndexesAfter" : 3,
    "ok" : 1
}
> db.shopOrder.getIndexes();
[ ]

I'm using MongoDB 3.6, why does this happen?


texts:
I am trying to calculate max_connections. I already have a formula for it from this forum only which is

    max_connections = (Available RAM - Global Buffers) / Thread Buffers

I am not sure with the authenticity of this formula. Is there any other way around to cross check the value which I am getting from this formula and confirm on the max_connection value?


texts: Find the nearest time from another table and add column

texts:
Please help. I need to create a view of Assembly details and when they have gone through a particular machine. I have two tables. Table 1 has the main assembly data and Table 2 has the machine data.

The final query has all Table 1 fields and the Machine field of Table 2 where the BuildDate (Table 1) is = to the WaveDate (Table 2) and the Wavetime (Table 2) is less than and nearest to the BuildTime (Table 1).

Hopefully the pictures make more sense than my description :/ Any help is greatly appreciated. Many thanks


texts:
I am inserting into two tables at the same time, tss_client and tss_details.
First, I insert into tss_details, then using Details_ID from there, I insert into tss_client.

How to I get the most recent ID, from tss_details?
I'm trying LAST_INSERT_ID() but it doest work

 addDetailsQry.prepare("INSERT INTO tss_details(Username, Password, Email, Description, etc..) "
                      "VALUES (:Username, :Password, :Email, :Description, etc..");

 addClientQry.prepare("INSERT INTO tss_client(Client_Name, Website, Contact_Details_ID)"
                     "VALUES (:Client_Name, :Website, :LAST_INSERT_ID()");

addDetailsQry.bindValue(":Username", username);
addDetailsQry.bindValue(":Password", password);
addDetailsQry.bindValue(":Email", email);
addDetailsQry.bindValue(":Description", description);

addClientQry.bindValue(":Client_Name", clientName);
addClientQry.bindValue(":VAT_Number", VATNumber);
addClientQry.bindValue(":Website", website);
addClientQry.bindValue(":Contact_Details_ID", /*what would i put here*/ );

if(addDetailsQry.exec())
{
    qDebug() << "Client Details added!";

    if(addClientQry.exec())
    {
        qDebug() << "Client added!";
    }
}

The ID column in tss_details is an identity.

I'm using ODBC and SQL Server version:

  'Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3  (Build 14393: )

and  I get:

  [Microsoft][ODBC SQL Server Driver][SQL Server]'LAST_INSERT_ID' is not a recognized built-in function name.


texts: Why is postgresql still consuming space on my other drive?

texts:
I am running postgreSQL 8.4 on Ubuntu 10.0.4.

I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and turned off logging altogether by setting:

log_destination='stderr'

in my postgresql.conf file

I then stopped the current run postgresql service, and restarted it, specifying the data directory. I did that using the following steps:

sudo su - postgres
/usr/lib/postgresql/8.4/bin/pg_ctl -D /path/to/dir/on/new/drive -l logfile start &

I run the psql command line utility and run SHOW DATA_DIRECTORY to confirm that the running postgres service was indeed pointing to the right directory.

I then started my data import, but periodically run df -h to check disk usage on my machine. Although data was being stored to my new drive (as expected), disk space was being consumed at a much faster rate on my old drive.

I don't understand why that would be, since postgreSQL should (AFAIK) have no reason to write anything to my old drive.

Does anyone know why this may be happening, and how to fix this? (i.e. force PG to save data in the specified location only?)

Note:

I did NOT change the data_directory setting in my postgresql.conf. It was still pointing to my old drive - however, I had explicitly restarted the postgres service and specified a new data directory location.


texts: Meltdown Patching can be turned on as needed

texts:
Experts,

Need to know if anyone has witnessed or found such settings while patching SQL servers for Meltdown patches.

Our sys admins informed that OS patches for Meltdown can be turned ON/OFF bases on registry setting and can be enabled or disabled.

Just looking if someone know a similar way available for SQL server patches? Means you can turn it active when needed?


texts:
We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB might show because we want to fully maintain integrity of our data.

Below is .cnf file.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=9508071
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=datacv

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts: Oracle Table - game like script - need solution to find out the values from current column

texts:
I have a little "game". I have the following Oracle Table (a view more exactly), where I have the values for my columns id, cod, val, t1, tall and I need to calculate v1,v2,v3,v1a,v2a,v3a.

ID   Cod   Val   T1   Tall |  V1   V2   V3 | V1a   V2a   V3a |
--------------------------------------------------------------
1    R1    99    179  301  |  99           |                 |
2    R2    125   179  301  |       80      |       45        |
3    R2    77    179  301  |        ?      |       77        |

The rules of the "game" are:

I have my values from ID, COD, VAL, T1 and Tall and I must calculate
V1,2,3,V1a,2a,3a.
v1+v2+v3(on all three rows)=T1
v1+v2+v3+v1a+v2a+v3a=Tall
On each row, I must put part of VAL first in columns v1-2-3, until I
reach T1 (*1) and after that, I must put the remaining values from
VAL to v1a-2a-3a until I reach the value from Tall (*2)
I must put the value according to the COD, VAL with R1 as COD only
in column V1 or V1a, R2 in V2 or V2a and R3 on V3 or V3a

I'll make another example with same numbers:

ID   Cod   Val   T1   Tall |  V1   V2   V3 | V1a   V2a   V3a |
--------------------------------------------------------------
1    R1    99    179  301  |  99           |                 |
2    R2    77    179  301  |       77      |                 |
3    R2    125   179  301  |       3       |       122       |

The question is - how can I know - when I am on the row 3, when I am on the column V2 (marked with ?) that I've added 80 on V2 and I allready have 179 (T1) on V1-2-3? The same question for the second example, how can I know that I have the sum of 176 from T1 and put only 3 on V2 and the rest (of 122) on v2a?

Please, consider it like a game, but the usefulness of this "game" it's very important for I script I need. So, you can give me your "minus" if you think that this is not a "problem" for this forum but I am in a need for an answer to this. Thanks!


texts: Pausing the import of an SQL file in Postgresql?

texts:
I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is installed on and will more than likely need to be done before the import operation finishes.

Is it possible to pause the import of the database?

If not, will putting the laptop into hibernate while I relocate it cause the import to freeze or fail?


texts: PostgreSQL + why archive_command line is marked after barman restore

texts:
I use the barman tool as backup & restore for PostgreSQL

after I restored successfully the data folder under /var/lib/pgsql by the barman

we have seen that the line with archive_command  was remarked by the #BARMAN# and the line - archive_command = false added also

as the following

vi /var/lib/pgsql/data/postgresql.conf

#BARMAN# archive_command = 'rsync -a %p barman@107.14.23.6:/var/lib/barman/main-db-server/incoming/%f'
archive_command = false

this mean from this point we can't backup the data folder from the backup server
because line is marked

why barman remark the line after restore process?

can we avoid this? ( because this stops the backup process each night )

IP - 107.14.23.6 is the backup server


texts: Slecting specific query returnned data related records from another table

texts:
Product table:

productId | productName | unitPrice | supplierId
_______________________________________________
    1     | Iphone8     | 1500      |     3
    2     | Nokia 6     | 800       |     1
    3     | Samsung S9  | 1500      |     2

Supplier table:

supplierId | supplierName | country
___________________________________
     1     | Nokia Inc    | India
     2     | Samsung Inc  | Korea
     3     | Apple Inc    | USA

I’m confused, how can I write a query using above two Product and Supplier tables to list the product unitprice 1500 only and their supplier Id, name and country. Such as the table below shows.

    productId | unitPrice| supplierId |supplierName | country
    _____________________________________________
        1     |   1500   |     3      | Apple Inc   | USA
        3     |   1500   |     2      | Samsung Inc | Korea

I used join queries with supplierId on product table to get its relating data from the supplier table. But each time I ended up in error when I try to fetch '= 1500' to this query.

this is the query I tried;

select *
from Product, Supplier
where Product.supplierId = Supplier.supplierId

but it gives all the records, how can I insert "unitPrice = 1500" to this query to achieve my answer?


texts: How to draw ER diagram for recursive relation when the number of roles is more than 3?

texts:
I know for a recursive relation where there are 2 roles, for example:
Worker works for Manager
and Worker and Manager both are 2 roles of Employee, we show it this way:

But what if Employee had roles like:
1. Director
2. Manager
3. Secretary
4. Worker
and every role in the lower hierarchy works for the ones in the higher rank.

For Example:
Manager works for Director, Secretary works for Director and,   Worker Works for Director

Then again,
Secretary and Worker also Works for Manager and so on...

How to show that in the ER DIAGRAM?


texts: Restore Database in SQL Server 2014 with different name

texts:
I am using SQL Server 2014.
I have a database but as the user deleted some records, I need to create another database with a different name
and then transfer the data from the restored database to the current one.

I know I need to rename the restored database.

From my understanding, I also need to go to the Files page and then click on Relocate all files to folder.

In terms of renaming, do I need to rename both the Data file folder and the Log file folder?

Any assistance would help clarify


texts: Can we use FILESTREAM in case of SQL Server cluster

texts:
I saw this point on Technet article about RBS.

  The local FILESTREAM provider is supported only when it is used on
  local hard disk drives or an attached Internet Small Computer System
  Interface (iSCSI) device. You cannot use the local RBS FILESTREAM
  provider on remote storage devices such as network attached storage
  (NAS).

Does it mean we cannot use FILESTREAM in case of SQL Failover Cluster because Failover Cluster only works on NAS? But the thing is NAS is made available locally when setting Failover Cluster so may be FILESTREAM will work but I am not sure about it.


texts: Granting Permissions for a limited period of time

texts: Is it possible to grant permissions to a user for just a certain period of time? For example, I ran into a situation the other day where I wanted to grant a user the ability to modify data in a table for only a couple hours. I haven't found a native way to do this, but thought I'd ask anyway. This would be specifically for Azure SQL Database.


texts: Updating an indexed column performance impact in SQL server

texts:
I am trying to understand is there any performance impact if I am trying to update a column which is the key column of a non-clustered index in SQL server 2008? For e,g

I have a column IsActive in the table TestTable. I have created a non clustered index on column IsActive. Now I am trying to update the value of the column IsActive through an update query like

UPDATE TestTable SET IsActive= 1 WHERE IsActive = 0

I need to understand will this perform better or not having the index on it will be better. I am asking his as reindexing takes place when index vales are updated. So will this reindexing have a impact on the query performance? Note : The table has excess of 100 million rows.


texts: Always Encrypted - Lock request time out period exceeded

texts: I'm trying to encrypt a column and am getting the following error: Set-SqlColumnEncryption : Lock request time out period exceeded. Are there parameters to specify the lock timeout? What could be locking it? The table has about 10k records. It works fine with empty tables or tables with hundreds of records.


texts: Can I get an equivalent query for this Not in Query

texts:
SELECT ID FROM dbo.SomeTable
            WHERE ID not in
            (
                SELECT MIN(ID)
                FROM dbo.SomeTable
                GROUP BY SystemNo
            )

As this query is taking very long (more than a minute) to get executed. I guess "IN" is the culprit for this. I would like a query which yield same result but with most efficient time.


texts: Extracting date from file group name and converting to date fails when filtering

texts:
In sys.sysfielgroups I have a list of filegroups with dates in them, and I'm trying to extract these dates.  Here's an example list of the file groups:

  Event_Header_Daily_LeftNull_FG

  Event_Header_Daily_2018_08_07_FG

  Event_Header_Daily_2018_08_08_FG

  Event_Header_Daily_2018_08_09_FG

  Event_Header_Daily_2018_08_10_FG  2018-08-10

This is the query that I'm trying to run.  As you can see, I'm trying to get any file group older than today's date:

SELECT *
FROM
(

    SELECT
        groupname,
        CAST(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) AS dt
    FROM sys.sysfilegroups
    WHERE groupname LIKE 'Event_Header_Daily_%'
        AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
) a
WHERE dt < GETDATE()

If i run the subselect without filtering the results, the query runs just fine.  However, when running the whole query I get the 7th, 8th, and 9th and the query errors out, stating:

  Conversion failed when converting date and/or time from character
  string.

As far as I can tell this should just work, but it doesn't and I'm at a loss as to why.


texts: Using timestamp plus integer as primary key

texts:
In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is:

Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between 0 and 100?

Why would this be preferred to using an Integer Auto_Incremented field? (Unless it wouldn't, then I want to know that too.)

I would love reasons why this is a really bad design practice. Casting everything in order to get back that integer value is also a total pain in the ass.

I basically stand once chance at getting my small team of developers access to help improve our applications' database, which it needs badly-To avoid situations like primary key Varchar(50) fields with left-padded zero's, or to just start improving the non-normalized database, or comma-separated lists in one field.


texts: Querying for a specific language and if not found use default one instead

texts:
I currently store all language data (including default language) in my database. Generally a simple query making the assumption the language exists is as simple as doing:

SELECT a.artifact_id,
       a.base_atk,
       a.base_hp,
       a.max_atk,
       a.max_hp,
       b.name,
       c."class",
       b.lore_description,
       b.skill_description
FROM GameArtifact a
JOIN GameArtifactNameDesc b
    ON a.artifact_id = b.artifact_id AND b.locale = 'en-US'
JOIN GameClassTranslation c
    ON c.identifier = a."exclusive" AND c.locale = 'en-US';

And a result from that said query:

-[ RECORD 1 ]-----+-------------------------------------------------------------------
artifact_id       | abyssal-crown
base_atk          | 15
base_hp           | 54
max_atk           | 195
max_hp            | 702
name              | Abyssal Crown
class             | Mage
lore_description  | May those ruled by the crown walk the road of corruption forever. +
                  | - Witch Reinwood, hung high up on the stake
skill_description | 12.0% (24.0%) chance to stun the enemy for 1 turn after attacking.

The issue is, what if the language (which is set on the application) is not found? How can I set it to use a default language (let's say: es-AR) and query for that language? Or what if there's translation for that language in one table but not in both?

I thought of two approaches:

Approach #1

Doing the query presented above and if it doesn't return anything, query again using the default language which I can be certain there's data of in the database.

This doesn't sound convincing and nice to do.

Approach #2

Cut off the short tables and do their translations on the application. Which in this case GameClassTranslation, which only contains 49 rows, 7 languages so 7 rows per language but which might expand in the future.

This also doesn't sound convincing from the point of keeping everything in one place rather than split everywhere.

Is there any better solution for this?


texts:
I need to create a materialized view (MV) with auto refresh every hour. After I create it, a lot of redo logs are generated (10GB per hour). I think the solution is to recreate the MV in NOLOGGING mode.

Are there some problems with my DG database and with a second DG database in read only mode?

Can my RMAN backups restore fail, or do I need only to recreate the MV after a data recovery?


texts: ReFS vs NTFS - which is better for SQL Server 2019 on Windows Server 2019

texts: We want to install SQL Server 2019 on Windows Server 2019; currently Admin has formatted Nimble Storage with ReFS. Is it really better than NFTS? I have seen other posts wherein people have mentioned about downside of ReFS. Any suggestions \ help is appreciated.


texts:
For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral".

This question is about taking advantage of such type of storage in installations of the Oracle database. Which breaks down into:

What is a way to keep a tablespace's datafiles on ephemeral storage
and have Oracle create those on boot (and possibly run some scripts to create/populate a few tables) and be ok when they are lost.
What would be the implications for backups (there should be no backup of the ephemeral data).
Any other possible consideration for tables and other object on top of it

possible optimizations: e.g. disable logging.
what is lost with a reboot (data or data+metadata)

The TEMP tablespace is a perfect candidate for this optimization and in fact for MS SQLServer's equivalent there are on the web howtos for doing just this.

Let's consider a data warehouse, as the reference use case, with a recurring job that imports many gigabytes of data (from CSVs or datapump) into a staging schema "STG" and a subsequent ETL process that saves the results on the production schema.

This workload would benefit a lot from having very fast read and write access on the staging schema, easily tolerating the volatility of its data.


texts: SQL Server FILELISTONLY command providing incorrect logical database name

texts:
When using RESTORE FILELISTONLY to restore a database using code the logical_name for the files is coming back as 423.

However when I do a manual restore of the database the logical file name appears to be 3497.

I'm running SQL Server 2008 R2.  Has anyone run into anything like this before?  How do I repair this?


texts: Include parent node ID in hierarchical recordset of child rows

texts:
I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a specified parent, but I would like to also include the parent id on each row.

WITH level_1 (category_id, category_name) AS (
  SELECT
   category_id,
   category_name
  FROM category
  WHERE
   parent_category_id = 1
  UNION ALL
  SELECT
    c.category_id,
    c.category_name
  FROM category AS c CROSS JOIN level_1
  WHERE
    c.parent_category_id = level_1.category_id
)
select * from level_1

The query as written returns the following:

category_id | category_name
         64 | Cows
         6  | Pigs
         11 | Holstein
         23 | Bantu

What I want though is below because I want to group on those parent categories but I won't necessarily know what they are except that they have a parent_category_id = 1.

 root_id | category_id |  category_name
      64 |          64 |  Cows
       6 |           6 |  Pigs
      64 |          11 |  Holstein
       6 |          23 |  Bantu

Category table is like below.

   category_id         |  unique row identifier
   category_name       |  varchar
   category_parent_id  |  foreign key to category table


texts: Replicating schema changes between production and development databases

texts:
Not too sure if this would be a good and proper way to go about things, so I apologize if I'm kind of far off.

Currently running Microsoft SQL Server 2005, and in this we have a database for a production website, and now just made a copy of it for a development version of the website. What my question is, what would be the proper way to "replicate" or "mirror" table structure changes and things of that nature from the development database to the production one, when we do our upgrade? Is there a simple way to do a migration like this?


texts: Index fillfactor and index pages in PostgreSQL

texts:
The postgres documentation says index fillfactor affect on "index pages" and says "for heavily updated tables a smaller fillfactor is better". I'm confused that its about update an index value or any value in the row?
Is table fillfactor override the index fillfactors of the table (whether index fillfactor defined or not (default 90 for btree index))?

Fillfactor in PostgreSQL documentation:
Table fillfactor:

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

Index fillfactor:

The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.


texts: Better jsonb schema for querying in postgres

texts:
I have a JSON with the following structure:

uid | item
1   |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}]
2   |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":0}]
3   |[{"id":1, "m":"qa", "s":1},{"id":4, "msg":"ks"},{"id":5, "m":"test"}]

I want to query this table based on id and get rows based on matched object values. For e.g. for id=3 I want:

uid | id | m  | s
 1  | 3  | of | 1
 2  | 3  | gh | 0

This table has over 500M rows so I would need an index. Using GIN(item jsonb_path_ops) with item @> '[{"id": 3}]' works, but I don't know how to get the exact json object from array that matched.

I can turn the JSON structure to:

{
    "1": { "m":"123", "s": 1 },
    "2": { "m":"on", "s":2 },
    "3": { "m":"of", "s": 1 }
}

and use GIN(item) and check for key exists with ? and then try to fetch the key value. But the index size might increase which might be an overkill given that I only want to search based on id.

Maybe I can use a B-Tree index but not sure how. What should be my JSON structure for most efficient querying? and what index should be used so that I get only the matched object from the array?

Some facts:

Cardinality of id <<< uid. (id = ~100,000, uid = ~500,000,000)
Normalizing this turns this table to 500M*10,000 rows which is extremely slow.
Each JSON array will have upto 50 objects only.


texts: How to update a column with values from the same newly inserted row in a trigger?

texts:
Using SQL Server 2014, I have this:

Table1
Tab1_id        int identity(1,1) primary key
Tab1_val1      smallint
Tab1_val2      smallint
Tab1_valInfo   varchar(10) -- not yet implemented, will be used only for presentation

 Tab1_id |   Tab1_val1 |   Tab1_val2 | Tab1_valInfo
---------|-------------|-------------|--------------
       1 |          25 |          19 | 0025-0019
       2 |           0 |           5 | 0000-0005
       3 |          12 |           3 | 0012-0003

Now with my pretty short SQL experience, what I'm trying is:

From an application where the only input values required for inserting data are Tab1_val1 and Tab1_val2, then, create a trigger which updates Tab1_valInfo column.

create trigger utr_ValInfo on Table1
after insert as
begin

    declare @size int = 4;

    declare @valInfo varchar(10) =
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val1 */), @size) from inserted
        + '-' +
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val2 */), @size) from inserted;

    update Table1 set Tab1_valInfo = @valInfo where /* this is the last inserted row*/;

end;

A single insert may involve multiple rows.

I hope what I'm trying to achieve is clear.


texts:
I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined below) but still got these errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

Steps to reproduce:

Install a fresh, vanilla Ubuntu 14.04 distro (I'm using Vagrant with this Vagrant box).
Install PostgreSQL 9.3, configure to allow local connections as PostgreSQL user "postgres" from any Linux user.
Create a test database. I'm just doing:

vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres
psql (9.3.5)
Type "help" for help.

postgres=# create database mydb;
CREATE DATABASE
postgres=# \q
vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb
psql (9.3.5)
Type "help" for help.

mydb=# create table data(entry bigint);
CREATE TABLE
mydb=# insert into data values(1);
INSERT 0 1
mydb=# insert into data values(2);
INSERT 0 1
mydb=# insert into data values(3);
INSERT 0 1
mydb=# \q

Create a backup of the database like so:

PGPASSWORD="postgres" pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump
Delete some rows out of the data table in mydb so we will be able to tell if we restored the data successfully.
Restore the database with:

PGPASSWORD="postgres" pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump

The data is restored, but the pg_restore command in step 6 exits with status 1 and shows the following output:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

WARNING: errors ignored on restore: 1

I cannot just ignore this because I am running this command programmatically and need to use the exit status to determine if the restore failed or not. Initially, I wondered if this problem was because I put my database in public (the default schema). I reasoned that public would be created as a result of the --create option by pg_restore before the data was restored (which could conceivably try to create that schema as well since that is where my table is), but when I tried the above steps with my table in a different schema, the results were the same and the error messages were identical.

Am I doing something wrong? Why am I seeing this error?


texts: SQL Server: insert french characters without using them in a query

texts:
I have a query inserting a special character into a table, for instance:

INSERT drinks VALUES( 'Café' )

I would like to do the same thing, in the most portable way possible, using only ASCII in my query, for instance:

INSERT drinks VALUES( 'Caf' + <something> )

Is it possible?


texts: How to select from a table without including repeated column values?

texts:
In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do what I was trying to do.

Instead of filtering the table during import, I now want to create a view on the table that returns only records where the price has changed.

Here's the original scenario rephrased to suite this question:

We have a table of historical prices for items. The table contains rows where the same price is recorded for multiple dates. I want to create a view on this data which only shows price changes over time, so if a price changes from A to B I want to see it, but if it "changes" from B to B then I don't want to see it.

Example: if the price yesterday was $1, and the price today is $1, and there were no other price changes, then the price today can be inferred from the price yesterday so I only need the record from yesterday.

Example (http://sqlfiddle.com/#!3/c95ff/1):

Table data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-24T00:24:00  1234     1     1.00 -- redundant, implied by record 1
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-27T00:27:00  1234     1     2.00 -- redundant, implied by record 4
2013-04-28T00:28:00  1234     1     1.00 -- not redundant, price changed back to 1.00

Expected view data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-28T00:28:00  1234     1     1.00

My initial attempt used ROW_NUMBER:

SELECT
    Effective,
    Product,
    Kind,
    Price
FROM
(
    SELECT
        History.*,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                Product,
                Kind,
                Price
            ORDER BY
                Effective ASC
        ) AS RowNumber
    FROM History
) H
WHERE RowNumber = 1
ORDER BY Effective

Which returned:

Effective               Product  Kind  Price
2013-04-23T00:23:00     1234     1     1.00
                                             -- not 2013-04-24, good
2013-04-25T00:25:00     1234     1     1.50
2013-04-26T00:26:00     1234     1     2.00
                                             -- not 2013-04-27, good
                                             -- not 2013-04-28, bad

I tried searching for a similar question/answer but it's hard to work out how to phrase the search, an example is worth a lot of words.

Any suggestions appreciated. Thanks


texts: ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

texts:
Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and others will be a full load.
To note, we have about 225 source databases all the with the same schema.
From what I've seen, building a simple data flow in SSIS with a OLE DB source and OLE DB destination requires the columns and data types to be determined at design time. This means that I will eventually end up with over 200 data flows just for the extraction alone.
From a maintainability perspective, this strikes me as a big problem. If I needed to make some kind of sweeping change to the extraction code, I would have to modify 200 different data flows.
An an alternative option, I wrote a small script which reads the source databases, table names and columns I want to extract from a set of metadata tables. The code runs in multiple loops and uses dynamic SQL to extract from the source tables via a linked server and OPENQUERY.
Based on my tests, this is still not as fast as using an SSIS data flow with an OLEDB source and destination. So I am wondering what kind of alternatives I have. Thoughts so far include:

Using EZAPI to programatically generate SSIS packages with
simple data flow. The tables and columns to extract would come from
the same metadata tables mentioned earlier.
Purchase 3rd party software (dynamic data flow component)

What is the best way to approach this? When it comes to .NET programming I'm a beginner, so the time required to ramp up just with the basics is also a concern.


texts: What is the term for a “wide”, non-normalized view/query?

texts:
A well-designed relational DB schema tends to have a relatively large count of relatively small, normalized tables — a cluster of snowflakes, I think, describes the situation.

In order to access the relational data, queries are necessary that join these separate tables into relatively large, non-normalized but very views (or virtual tables)

What is the accepted term for such a “wide” view/query -- a practical, "virtual table" resulting from multiple, useful joins of small, normalized tables?

I have given considerable thought over the years to database design; I just don't have much recent hands on experience so am not necessarily au courant with the terminology.


texts: Retrieving data from inner join using LIMIT and OFFSET

texts:
These are my two tables:
table1

qid[PK]     |gid[PK]  |abcd     | xyz  |    date
---------------+---------+---------+------+------------
 00001      |     qwe |      54 |    a | 1994-11-29
 00002      |     asd |       0 |    s | 1994-11-29
 00003      |     azx |      50 | 0.25 | 1994-11-27

table2

qid[PK]     | gid[PK] | user[PK]
------------+---------+--------
 00001      |     qwe | shreya
 00001      |     qwe | nagma
 00001      |     qwe | koena
 00001      |     qwe | paoli
 00002      |     asd | anushka
 00002      |     asd | angelina
 00003      |     azx | jolie
 00003      |     azx | scarlett
 00003      |     azx | sharon
 00003      |     azx | jeniffer

As you can see for each qid and gid of table1 there can be any number of rows in table2.

My requirement :
I want to retrieve all the users for the first 10 values of qid and gid from the offset.

My Query :

    select  * from table1 q inner join table2 a on q.qid=a.qid
 and q.gid=a.gid  order by q.date desc limit 10 offset ?

But this query will retrieve  10 rows from offset from the inner join, But I want all rows from table 2 for the 10 rows[offset] from table1.

How can this be achieved?


texts:
I have the following xml document

<XML_Articles>
  <Articles>
    <ID>12</ID>
    <ReferenceNum>123</ReferenceNum>
    <ArName>الهدف</ArName>
    <EnName>GOAL</EnName>
    <PublicationDate>2016-11-15</PublicationDate>
    <TypeID>15</TypeID>
    <CreatedBy>2</CreatedBy>
    <CreatedOn>2016-11-15</CreatedOn>
  </Articles>
</XML_Articles>

insert them into table by

INSERT INTO dbo.Articles
(ReferenceNum,ArName,EnName,PublicationDate,PublisherID,TypeID,CreatedBy,CreatedOn)
SELECT * FROM OPENROWSET(BULK 'C:\Users\dana_\Desktop\DataBase\Assignments\Assignement 6\Articles.xml',SINGLE_BLOB) AS x

But I get an error:

  The select list for the INSERT statement contains fewer items than the
  insert list. The number of SELECT values must match the number of
  INSERT columns.


texts: Should I use additional id values as unique values that only applies in junction tables in MySQL?

texts:
I have created three tables called person, metadata_image and person_image.

person table contains info about people.
person_id
person_name
person_bio

metadata_image table stores the images.
image_id
image_url
image_type
image_size
date_uploaded
importance (if importance equals to 1, I use that image as cover picture)

person_image has foreign keys from person and image tables.
person_id
metadata_image_id

Additional image :

Should I add a third column like person_image ID as a unique and auto increment value or is my structure okay as it is?


texts: Does SQL Server Date Correlation Optimization only work on DateTime fields, or will a Date work, too?

texts:
I ask because this is an older feature that I just discovered recently. The documentation mentions DateTime a few times but doesn't explicitly say that other date types are compatible or not.

I have a Data Warehouse that I believe could benefit from this, but 90% of the dates that matter are Date types, not DateTime. I am on SQL 2014, but information on any version would be helpful.


texts: Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries?

texts:
I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and found some ideas, but anytime I do it, I barely note any changes in the performance. So, does it affect in any way? How?

Thanks!


texts: DB2 zOS increasing DSSIZE vs adding partition?

texts:
I'm trying to load data into a DB2 table. The tablespace has 3 partitions with about 60M,60M,70M rows. The 3rd partition is failing to load. It gives me a warning that loading this partition's dataset will exceed the maximum size of # pages.

The DSSIZE is currently set to 4G for this table space. To increase to 8G, I would need to change the storage groups to SMS.

The data is currently partitioned by a numeric range.

P1 00-33
P2 33-66
P3 66-100

Would adding another partition and reorginize the data be a better solution?

P1 00-25
P2 25-50
P3 50-75
P4 75-100

Are there any other suggestions that would help load this data?


texts: How to count the amount of values grouped by some column?

texts:
I need to create a view based on data from another table and grouped by some column value. Here is a source table:

CREATE TABLE source
AS
  SELECT *
  FROM ( VALUES
    ( 6, 1000 ),
    ( 6, 2000 ),
    ( 7, 5000 ),
    ( 7, 6000 )
  ) AS t(client_id, score);

and the desired view is:

 client_id | score
----+---------+---
 6         | 3000
 7         | 11000

As far as I know, it's convenient to use window functions in this case. But what way will be most effective for grouping by client_id field?

Thank you.


texts: Finding changes for table-valued function definition on SQL Server

texts: I had a Table-Valued Function in SQL Server and someone from my colleagues has altered it unexpectedly. How can I restore previous version easily? Is there any way to find the previous version?


texts: MariaDB Maxscale REST API can't show result

texts:
hi i have already setup maxadmin on maxscale can access from rest api this is my configuration

    .....
[CLI_Service]
type=service
router=cli

[CLI_Listener]
type=listener
service=CLI_Service
protocol=maxscaled
address=192.168.101.107
socket=default

[MaxAdmin_Inet]
type=listener
service=CLI_Service
protocol=HTTPD
address=192.168.101.107
port=8010

but when i test the url like this

curl --include --basic --user "admin:mariadb" http://192.168.101.107:8010/v1/servers

the result is like this

HTTP/1.1 200 OK
Date: Wed, 13 May 2020 11:33:15 GMT
Server: MaxScale(c) v.1.0.0
Connection: close
WWW-Authenticate: Basic realm="MaxInfo"
Content-Type: application/json

Commands must consist of at least two words. Type help for a list of commands

whether i miss about the configuration ?


texts: Execution plan push sort operator to avoid too many PK lookups

texts:
For a query like this:
select top 40 ColumnNotInIdexCausingPKLookup from table
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex

Any way to push up a sort/filter operator, so the PK lookup isn't that heavy (40 records vs 4.797)?  All columns that are being used in the filter/sort are in the index "included column" list so there is enough information in the index to not have 4797 PK lookups
+- 20K reads

If i change the index so the columns are in de index fields instead of the "include" fields then the execution plan does filter first, using the index itself. However, that only works with that specific set of orderby/filterby. Which changes if the user filters/sorts on another field.
+- 700 reads

SQL Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)   Nov  2 2020 19:19:59   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 18363: )
Steps to reproduce
create database ExecutionPlanTestDB
go
use ExecutionPlanTestDB

create table ExecutionPlanTest (
    id int not null identity(1,1) primary key,
    ColumnInIndex bigint,
    ColumnInIndex_Include bigint,
    ColumnNotInIndex bigint,
)

insert into ExecutionPlanTest(ColumnInIndex,ColumnInIndex_Include,ColumnNotInIndex)
select
    top 10000000
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id
    from sys.objects o1
inner join sys.objects o2 on 1=1 and o2.object_id<100
inner join sys.objects o3 on 1=1 and o3.object_id<100
inner join sys.objects o4 on 1=1 and o4.object_id<100
where o1.object_id<100

create index IX_EPT_ColumnInIndex on ExecutionPlanTest(ColumnInIndex) include(ColumnInIndex_Include)

-- execute with execution plan from here
select top 5 ColumnNotInIndex from ExecutionPlanTest
where ColumnInIndex=307 --should have +-2k results
order by ColumnInIndex_Include

Query plan: https://www.brentozar.com/pastetheplan/?id=S1odVbOgu
One way to solve this seems to be changing it to this structure. Although I would rather not convert all the queries like this. It seems to be only logical that sql server first sorts/filters/tops the data in the index itself before sending it further in the execution plan?
select ColumnNotInIdexCausingPKLookup  from table where tableID in (
    select top 40 tableid from table
    where ColumnIncludedInIndex=A
    order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
)

This results in the better plan (+-700 reads now instead of 20k)


texts: Can I delegate SQL Agent rights without granting sysadmin rights?

texts:
I'd like to give a user the ability to administer SQL Agent jobs.  According to the doc page, I can do this by granting the "appropriate" roles on msdb.  I've granted all three SQLAgent% roles, but the user gets this error when (as a test) trying to rename a job:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
(Microsoft SQL Server, Error: 14525)

This question is similar, but no answer there addresses SQL Agent.  Is the sysadmin role the only solution, in MS SQL 2K8?


texts: Why pt-duplicate-key-checker suggests removing a composite index?

texts:
Here's a snippet from the output of the Percona Tool pt-duplicate-key-checker which searches for redundant indexes:

# Key myidx ends with a prefix of the clustered index
# Key definitions:
#   KEY `myidx` (`bar`,`foo`)
#   PRIMARY KEY (`foo`),
# Column types:
#         `bar` mediumint(8) unsigned not null default '0'
#         `foo` mediumint(8) unsigned not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `mydb`.`mytable` DROP INDEX `myidx`, ADD INDEX `myidx` (`bar`);

Why does the tool suggest this? Can't the original composite index be useful?

As far as I understand, an index on bar would be worth of deletion given a PK (bar,foo), but it is not the case here.


texts: Get data excluding time intervals in another table

texts:
I have two tables, Message and BlockUser.

Table Message:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

Table BlockUser:

BlockStartDate       | BlockEndDate
2017-09-21 13:00:00  | 2017-09-22 13:00:00
2017-09-24 13:00:00  | 2017-09-24 14:00:00

Now I want rows from Message excluding those matching the intervals defined in BlockUser. So for my example the result should be:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
5  | M3      | 2017-09-23 13:00:00
7  | M3      | 2017-09-25 13:00:00

I tried using this query but it didn't work:

WITH CTE AS (
         SELECT BlockStartDate,BlockEndDate FROM BlockUser
         )
SELECT * FROM Message
WHERE DateCreated NOT BETWEEN
(SELECT BlockStartDate FROM CTE)
AND
(SELECT BlockEndDate  FROM CTE)

It gives me this error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.


texts: What does "Drop Connections" and "Update Statistics" in the "Detach Database" dialog do?

texts:
As a testing phase I have installed the evaluation version of SQL Server 2014 Enterprise Edition.

When I detach a database through SQL Server Management Studio, I see two options:

Drop Connections
Update Statistics

Both options will detach the database from the SQL Server Instance.  What is the difference between the two options?


texts: MySQL boolean field search vs indexed varchar performance

texts:
Will a select filtering on a boolean field be faster than a select filtering on an indexed varchar(255) field?
Specifically:
MySQL 5.7
In this case, records of a certain table should never be deleted.  Instead they should be marked as inactive if necessary.
A boolean 'is_active' field could be added to that table, or an existing 'status' indexed varchar field could be used (conceptually overloaded) to also store the value 'inactive' if necessary.  The varchar is specified as 255 max length, but realistically it is much smaller.
In this case, 90+% of the rows will be considered active, so the boolean is_active would be 0, or alternately the status would have some value other than 'inactive'; and most searches would be for is_active = 1 (ignoring inactive rows).
I have read that there is little value in indexing boolean fields, so whether the boolean option were indexed or not probably wouldn't be a factor (but if it is, please enlighten me!)


texts:
I'm trying to get the rowcount for each table by using the query below:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
where   [Tables].name   like '%ixf_%'
     or [Tables].name   like '%itx_%'
     or [Tables].name   like '%wk_%'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
ORDER BY [TotalRowCount] desc ,[TableName];

The problem cames up when I run it in a particular enviroment, i'm getting no rows, but when I tried in another server, I'm able to retrieve rows.

I have this query also to match the collate in case of any collation issue:

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default = b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

And no rows are returned either.


texts: Sum of points and rank according to points with join three tables

texts:
I am creating a game where I am calculating the user points on the basis of user activities. Everything working fine I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation PointsUp (contains up points) and pointsdown (contains down points)

select Sum(PointsUp-PointsDown) as points
from table1 t1
  join table2 t2 on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
Group By t1.Id
order by points desc

Everything is fine but the problem come when I have to rank the users with same points then I have to consider the registeration date of user who register first will come up on ranking. The registeration date is in another table table3 i have tried to join third table but the sum is behaving odd showing wrong sum calculation

Can anyone help, please?


texts: UPDATE statement not consistent when including NULL fields

texts:
So I want to append Field B to C, then copy field A to B and set field A to "Awaiting update".

I have written the following code

UPDATE Notes

SET

/* append previous month to notes */
Notes=Notes + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(LastMonth as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))

The problem i've found is that if any of the fields in the row are blank the update statement no longer works as expected.

What is the correct method for going about an update like this?


texts:
because of GDPR we need a system that monitores all activities that administrators does on an SQL Server.
First goal is to audit sysadmin activities (selects too), but if possible activities of DBowners too.

I think the ideal solution would be, that a DAM (Database activity monitoring) gets the activities directly from SQL Server Memory and write it to an syslog-server.
And this ideal solution must recognize if a sysadmin with local admin rights trys to stop or manipulate the auditing.

One not so ideal solution is to audit sysamins trough sniffing (ergo separation of dutys because this is a network administrator work...), but because normaly a DBA is local admin too and they logins often with RDP sessions..., so thats not a perfect solution.

An other solution is maybe that DBAs cannot be more local admins on their SQL - Server and the solution writes in the security log and a local service gets the infos from security log and write to the syslog server.

But maybe i think to complicated and it exists easyer solutions...

We need it for SQL 2014 or higher. If SQL 2014 is to old than SQL 2017 or higher version.

What do you consider?

Kind regards


texts: Database design : hotel room and its detail

texts:
Right now i'm creating DB schema for a hotel. I stumbled upon on my hotel rooms table design. The requirement of hotel rooms would have data like : columns like room_type, room_size, room_number.
room_type can have values like economic, standard, deluxe
room_size can have values like single bed, double bed
My question is, what the best way of creating this tables? Right now i'm thinking of two ways of doing it, which is :
Type 1
Basically i create these tables :
rooms_master (room_no, room_type_code, room_size_code)
rooms_type (room_type_code, room_type_description)
rooms_size (room_size_code, room_size_description)

with rooms_type and rooms_size act as master reference table for rooms_master.
rooms_master will have FK (room_type_code, room_size_code) to PK in table rooms_type/size

or this one :
Type 2
Basically i combine two reference table (rooms_type and rooms_size) into one general table
rooms_master (room_no, room_type_code, room_size_code)
general_table (key, code, description) with column "key" and "code" act as primary keys

sample data would be like this :
|Room_No|Room_Type_Code|Room_Size_Code |
----------------------------------------
|100    |1             |1              |
|101    |2             |2              |

|key      |code|description|
----------------------------
|ROOM_TYPE|1   |Standard   |
|ROOM_TYPE|2   |Deluxe     |
|ROOM_SIZE|1   |Single Bed |
|ROOM_SIZE|2   |Double Bed |

I'm weighing the pro and cons for those two ways.
Type 1 :
Pro :
1. rooms_master will have a strong relationship with rooms_type and rooms_size
2. easier to query data
Cons :
1. too many reference table like this (or is it okay to have much tables like this ?
   because sometimes i felt overwhelmed by a lot of reference tables like these)

Type 2
Pro :
1. A lot less table, because i can just put all reference data to a general table
   (not only rooms_type and rooms_size but others tables which only act as reference data)
Cons :
1. Harder to query, but it can be done (by query room size to general table,
   the room type and then join these two queries).
2. Doesn't have a strong relationship between rooms_master and general table.
3. Prone to error, if the values in column "key" in general tables is maintained wrong.

Right now i'm thinking toward to try using type 2, because much data in there, such as room type and room size aren't going to change (there will be no addition in near future).
So, please give me some insight of what ways should i chooes or are there any better ways of doing this ?


texts: How do I optimize query where searching for 'is not null' on either of two linked tables

texts:
I have a query like this:

SELECT * FROM my_first_table a   -- > 1 million records
INNER JOIN my_second_table b     -- > 1 million records
    on a.id = b.a_id
LEFT OUTER JOIN my_third_table c -- > 1 million records
    on a.id = c.a_id
WHERE   b.my_column_x IS NOT NULL
OR      c.my_column_y IS NOT NULL
AND     b.start_date  > isnull(@startdate, '17530101')
AND     b.end_date    < isnull(@enddate, '99991231')

my_column_x and my_column_y are both indexed, however as I'm doing an OR IS NOT NULL across both of these columns, neither index is being used, and the query is extremely slow.

I'm also doing a date range check. b.start_date and b.end_date are both datetime columns, and are indexed. The query accepts inputs where both the start and end dates can be null or a specified datetime.

How can I optimize the query? I'm thinking there must be a way I can do some kind of separate sub-selects for IS NOT NULL on my_second_table.my_column_x and my_third_table.my_column_y then combine those sub-selects in order to be able to join in the way I'm trying to do in the original query, so that the indexes on b.my_column_x and b.my_column_y are used.


texts: How to setup sync between MongoDB and C# .NET Application

texts:
What is the best practice to synchronize a MongoDB remote database with a C# .NET App that would allow queries to be responded to within the local file system and update itself when there is a change in the master database which is on a remote web server.

Some methods I came across are syncing MongoDB to a local JSON file using Node.js.

Please suggest a solution.


texts:
I am storing some data intro a mysql database like this:

ID     |   DATE      |  INFO
-----------------------------
1      | 1389384821  |  blabla
2      | 1389384821  |  blabla2
3      | 1389384821  |  blabla3

DATE is a timestamp generated before adding it into database using time() function in PHP

I want to get all data from this table that was added in the last 24 hours, or 48 hours, 30 days ...

I tried

SELECT
info
FROM table
WHERE date > (LOCALTIMESTAMP() - 86400)

Instead of LOCALTIMESTAMP(), I also tried CURDATE().

Where is my mistake?

I am not an expert in SQL, so this might sound a silly question.

WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400) ?? Is this a good solution?


texts: mysql Quiz leaderboard filter by points, time taken

texts:
I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1
---------------------------------------------------------------
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,15 2015|  1        |
| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs
---------------------------------------------------------------
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,13 2015|  3        |
| 3        |  2       | 0.5          | May,12 2015|  2        |


texts: Overcome SubQuery Returned More Than 1 Result

texts:
I found this syntax already answered to a diff question, but I am getting the error of

  More than 1 result returned

what do I need to alter in my syntax in order to defeat this error?

Declare @ONE Table
(
    employee varchar(250)
    ,eod int
)

Declare @Two Table
(
    employee varchar(250)
    ,amtpaid decimal(10,2)
)

Insert Into @One (employee, eod) VALUES
('Frank',   '1122')
,('Frank',  '1188')
,('Bill',   '1100')
,('Larry', '1199')
,('Bill',   '2211')
,('Jerry',  '3311')
,('Jerry',  '3322')
,('Stan',   '3388')
,('Stan',   '3300')
,('Larry',  '3377')

INSERT INTO @Two (employee, amtpaid) VALUES
('Frank', '11.11'),('Bill', '10.10')
,('Larry', '44.44'),('Jerry', '20.32')
,('Stan', '100.33')

SELECT a.employee
    ,[EOD] = b.eod
    ,[Paid] = CASE
        WHEN b.eod = (
                SELECT min(b.eod)
                FROM @Two
                WHERE b.employee=a.employee
                )
            THEN COALESCE((
                        SELECT TOP 1 SUM(ISNULL(amtpaid, 0))
                        FROM @Two b
                        WHERE a.employee = b.employee
                        ), 0)
        ELSE 0
        END
FROM @One b
INNER JOIN @Two a
ON a.employee = b.employee
GROUP BY a.employee, b.eod, b.employee
ORDER BY a.employee ASC

EDIT
My desired output is like below - amtpaid showing for only 1 employeename not all of them

Employee    EOD Paid
Bill    1100    10.10
Bill    2211    0.00
Frank   1122    11.11
Frank   1188    0.00
Jerry   3311    20.32
Jerry   3322    0.00
Larry   1199    44.44
Larry   3377    0.00
Stan    3388    0.00
Stan    3300    100.33


texts: pg_basebackup fails with " too many connections for role "replication""

texts:
I am trying to set up a standby server and keep getting this error. My primary server has more than enough connections to handle the load:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
max_connections=100
checkpoint_segments = 8
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

This is the command that fails on the standby server:

pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vPw --xlog-method=stream

I don't understand why this happens.


texts: Cast as INT(11) instead of DECIMAL 11,0 in a view

texts:
I have a view with a column defined as:

IFNULL(priority.id, 2) priority_id, ...

where priority.id has type INT(11), however the resulting view column has type:

DECIMAL(11,0)

How do I get the resulting column to retain the INT(11) type?


texts: Stopping SQL Server Log Reader Agent In 2012

texts:
We implement Transactional Replication with 2 publishing servers, 4 publications and 1 subscribing server. I need to shut down our distribution server for several hours. When restarted, will the Log Reader be able to pick back up where it stopped reading the logs before the shutdown?

My thought is that, because transaction log backups will continue on the publishers and clear the logs, the log reader will not have anything to grab.

I believe that my only reliable option is going to be reinitializing each publication and starting over.

Is my understanding of this correct or am I over complicating things?

Thanks!