texts: What permissions do I need if database a contains a stored procedure that inserts into a table in database b (same machine)?

texts: I have a stored procedure in database a that contains dynamic sql to perform an insert into a table in database b.  What permissions does the executing user need on database b?


texts: Duplicates when checking for two different column values based on a key that is not unique in table

texts:
I want to check to see if two orders were placed for one person within an hour of one another.
Example data
PERSONID | proc_code | TIME
123 | 1 | 4/25/2016 00:01:00
123 | 2 | 4/25/2016 00:01:00
123 | 2 | 4/25/2016 00:02:00
111 | 1 | 4/25/2016 00:01:00
111 | 1 | 4/25/2016 00:03:00
111 | 2 | 4/25/2016 00:01:00

SELECT
persons.person_id,
order_proc.proc_code,
(CASE WHEN order_proc.proc_code='1'
then case when order_proc2.proc_code='2' and order_proc.time=order_proc2.time
then 'Y'
else 'N'
end
end) as 'Y/N?'
from order_proc
inner join PERSONS on order_proc.PERSON_ID=PERSONS.PERSON_ID
inner join order_proc as order_proc2 on order_proc2.PERSON_ID=PERSONS.PERSON_ID
where order_proc.ordertime=order_proc2.ordertime

I then get duplicate rows if a person has had multiple "2" orders.
What I would like to see returned is for every proc_code=1 check to see if a 2 has been ordered at the same time as 1 was ordered (though ideally it will be within an hour of one another but I can figure that out later).
The furthest I've made it is with the above code but it isn't showing me all 1 orders when 2 wasn't ordered. The where statement above limits me from doing this because it is only matching where times are equal so I don't see any values returned as 'N'
What I'd like to see based on the above sample data:
PERSON_ID Proc_code Y/N?
123|1|Y
111|1|Y
111|1|N

Is there a way I can do this with a while loop and without duplicates? I'm guessing my duplicates are coming from the second join where I alias order_proc as order_proc2.
I am dealing with millions of rows and joining to several other tables as well to pull different types of information for the Persons and orders. I need to check a huge table (order_proc) whose foreign key is a order_procedure_id. There are several persons each with multiple orders.


texts: How can I improve a slow comparison query that have over partition and group by

texts:
I have a query (see below) that is working just fine, when there aren't that many rows, around 5k.
However, if I run the same query on a table with around 80k rows the query takes between 30 and 60 seconds to complete.  Don't ask me why it fluctuates so much...
I need help or tips on how I can improve this query so it runs faster, preferable a lot faster, but still retains the same functionality.
The [ExternalTable] in the query is a external table that are pointing on a view that i have little control over (it is a view residing on a linked server). I have full control over the [Table].
The query below is shortened a little for readability; we have 7 columns that we check against in the real query.
The purpose of the query is to find new or updated rows in the [ExternalTable] compared with the [Table] and then output the new or changed rows. This is working just fine on smaller amounts of rows but it takes a very long time when the number of rows increase.
If it makes a difference, this runs on a SQL Server in Azure.
WITH CombinedTables AS
(SELECT SourceTable,foo, bar, data, ROW_NUMBER()
OVER (PARTITION BY foo, bar, data
ORDER BY foo, bar, data) Row_Nbr
FROM (SELECT '2' AS SourceTable, foo, bar, data FROM [ExternalTable]
         UNION ALL
         SELECT '1' AS SourceTable, foo, bar, data FROM [Table] WITH (NOLOCK)
) AS CombinedTables GROUP BY foo, bar, data, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr < 2 AND CombinedTables.SourceTable = '2'

What can I do to improve the performance? Add indexes? Make it into a stored procedure?  Rewrite it somehow?
As you might have gathered, I'm a bit too inexperienced with SQL Server to solve this myself and I need some help from you smart people!
edit: added a sql fiddle
http://sqlfiddle.com/#!18/86075/1
edit 2: added actual executionplan image: http://prntscr.com/uuys04
edit 3: It might be that the query is fine and it's Azure that is limiting the query executiontime, I posted a new question about this: Tweak Azure Data IO operations to lower the DTU cost


texts: SQL Server 2014 and SQL Server 2014 Management Studio

texts:
I successfully installed SQL Server 2014, but when I look for SQL Server Management Studio it was not on the CD.

I have just downloaded SQL Server 2014 Management Studio separately and would like to get advice on any issues installing SQL Server Management Studio.

My experience with SQL Server 2005 was that the process installed both the server and the SQL Server Management Studio. Since they appear to be installed independently now. I would appreciate advice or a link to comprehensive documentation.


texts: Save postgres database from broken ubuntu 10.04 server

texts:
Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve).

I can still access the filesystem. Is there a chance to transfer the database by only moving some directories with content to an equal machine?

This is a 32 bit ubuntu 10.04 with postgres 8.4

Edit: On ubuntu 10,04 the postgresql data directory is /var/lib/postgresql/8.4/main and not /usr/local/pgsql/data


texts: how to combine multiple timestamps and keep the most recent one?

texts:
I have a table with many timestamps in it, some of which may be null.  Is there a way to combine them all and keep the most recent timestamp so I don't have to reference each timestamp individually?

For example, given this over-simplified example:

SELECT blah
FROM foo
WHERE
    ts1 >= '2018-04-17' OR
    ts2 >= '2018-04-17' OR
    ts3 >= '2018-04-17';

I suspect there must be a way to turn it into something like this:

SELECT blah, somefunc(ts1, ts2, ts3) AS ts
FROM foo
WHERE ts >= '2018-04-17';


texts: How to create recurrent weekdays as columns in a pivot?

texts:
I am newcomer to programming and databases and I would be grateful for some help on the following scenario.

I use PHP with SQL Server. I am building an employee attendance system and I would like to create a (pivot) table with months as rows and all weekdays name as columns (for a specific year). The values in cells will be the number of day(1, 2, 3...31).

The background color of the cell (already exists as table column) declares the type of employees' leave. The table has the following columns: employee_id, leave_date, leave_type, leave_type_color.

I want to achieve a result like below:

Thank you.


texts: Transactions using Isolation Level on databases with FileStream

texts:
We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting locked that makes the application to wait for the lock to be released.

I have been reading some blogs and following questions on stack overflow - one suggestion was to use READ COMMITTED SNAPSHOT. But i can't seem to use it because my database have filestream non transacted access set to full. I get this message when i try to set the snap shot isolation on:

  When the FILESTREAM database option NON_TRANSACTED_ACCESS is set to
  FULL and the READ_COMMITTED_SNAPSHOT or the ALLOW_SNAPSHOT_ISOLATION
  options are on, T-SQL and transactional read access to FILESTREAM data
  in the context of a FILETABLE is blocked.

Though it sets the isolation level, i am concerned about the file table block which is mentioned.

Can someone help me solve this problem?


texts: Improve Performance on GROUP BY - large table PostgreSQL

texts:
I have a Postgres table containing the following three columns:

origin (INT, b tree index)
destination (INT, b tree index)
minutes (SMALLINT, b tree index)

The table has ~ 1.4 billion rows. Each row contains the information about the travel time (minutes) from one place (origin) to another (destination).

The table is read-only - we might need to change it, but that will happen once a year or so...

I need to do queries like:

SELECT min(minutes)
FROM table
WHERE origin IN([long list])
AND destination IN([long list])
GROUP BY origin

The performance is really bad. Depending on the number of origins and destinations, the query takes more than 10 minutes. What we need is < 3 minutes, ideally < 10 sec. DBMS is postgreSQL 9.4, 16 G RAM.

Is there a way to improve it? Partitioning the table, optimizing indexes etc.? Or another DBMS?

Edited 2018-11-28:

Added Index on (origin, destination)

EXPLAIN ANALYSE SELECT min(minutes),origin FROM table WHERE destination = ANY('{100225471, 100236548, 10263073, 10827564, 7435519, 100272388, 10688802, 10823750, 10853634, 10681223, 100213867, 100234761, 100113775, 100229234, 100234067, 100235418, 100229220, 1000053957, 1000059198, 1000028857, 1000057809, 1000058848, 1000059188, 1000057802}') GROUP BY(origin)

"HashAggregate  (cost=228043.99..228044.04 rows=5 width=6) (actual time=10118.146..10126.308 rows=50022 loops=1)"
"  Group Key: origin"
"  ->  Index Scan using idx_destination on table  (cost=0.60..226716.69 rows=265460 width=6) (actual time=0.022..10021.269 rows=208174 loops=1)"
"        Index Cond: (destination = ANY ('{100225471,100236548,10263073,10827564,7435519,100272388,10688802,10823750,10853634,10681223,100213867,100234761,100113775,100229234,100234067,100235418,100229220,1000053957,1000059198,1000028857,1000057809,1000058848,1000 (...)"
"Planning time: 0.306 ms"
"Execution time: 10127.949 ms"


texts: Stop NLS settings in init.ora getting overridden by environment variable

texts:
We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has NLS_CALENDAR='Thai Buddha' to test a particular bug.

We have set up a database and included NLS_CALENDAR='Thai Buddha' setting in init.ora. However, we still get 'GREGORIAN' for NLS_CALENDAR, since environment variables overrides the setting in init.ora.

NLS_CALENDAR value is set as follows:

NLS_SESSION_PARAMETERS: 'GREGORIAN'

NLS_INSTANCE_PARAMETERS: 'Thai Buddha'

NLS_DATABASE_PARAMETERS: 'GREGORIAN'

ALTER SESSION is not an option because then we have to modify our application code to issue an ALTER SESSION whenever a user logs in (This is not possible because we have multiple clients already deployed on various machines). Modifying the environment setting is not an option either because there are other database instances running on the same host.

I have tried ALTER SYSTEM, but it gives following oracle error:

  ORA-02096: specified initialization parameter is not modifiable with this
  option

How can we make a single database instance to ignore the environment setting? Or is there any other way we can make our instance default to 'Thai Buddha' when it comes to NLS_CALENDAR setting?


texts: I need the reason why we should not install SQL server on the domain controller

texts:
I am a system administrator for a small firm. Now we consider to rollout Windows Server 2008 R2 for the firm. We plan to implement Active Directory. We would also like to upgrade RDMS, from SQL server 2000 to SQL Server 2014.

We can afford to buy just one server due to size of our organization, so we plan to implement both of AD and SQL Server 2014 on the same machine. However, Microsoft recommends not to install SQL Server 2014 on a domain controller.

I have checked the reasons, but I don't understand what is the problem, so if someone answers my questions bellow, it's really helpful.

You cannot run SQL Server services on a domain controller under a
local service account. →If we run SQL Server on a domain controller
under a domain user account, what would happens?
After SQL Server is installed on a computer, you cannot change the
computer from a domain member to a domain controller. You must
uninstall SQL Server before you change the host computer to a domain
controller. →We plan to promote our server to a domain controller
before installing SQL server 2014 and don't plan to de-promote to
member server because it's our only server. Is our plan wrong?
After SQL Server is installed on a computer, you cannot change the
computer from a domain controller to a domain member. You must
uninstall SQL Server before you change the host computer to a domain
member. →We don't plan to de-promote to member server because it's
our only server. Is our plan wrong?
SQL Server failover cluster instances are not supported where
cluster nodes are domain controllers. →We don't plan to install
failover cluster, so we think that it's not problem. Are we wrong?
SQL Server Setup cannot create security groups or provision SQL
Server service accounts on a read-only domain controller. In this
scenario, Setup will fail. →Because We have just one server, so it
never be a read-only domain controller, so we think that it's not
problem. Are we wrong?

Actually, I guess if we clear (1), we can install SQL server 2014 on our domain controller.

Thanks for reading all of sentences. I hope someone give us answers.


texts:
I have a classstructure table:

create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));

insert into classstructure(classstructureid, classificationid, parent) values(1001, 'FLEET', null);
insert into classstructure(classstructureid, classificationid, parent) values(1002, 'LIGHTDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1004, 'MEDIUMDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1022, 'ACTIVETRANSPORTATION', null);
insert into classstructure(classstructureid, classificationid, parent) values(1023, 'FACILITYWALKWAY', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1024, 'TRAIL', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1085, 'SIDEWALK', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1091, 'SDWRAMP', 1085);
commit;

select * from classstructure;

CLASSSTRUCTUREID CLASSIFICATIONID              PARENT
---------------- ------------------------- ----------
            1001 FLEET
            1002 LIGHTDUTYVEHICLE                1001
            1004 MEDIUMDUTYVEHICLE               1001

            1022 ACTIVETRANSPORTATION
            1023 FACILITYWALKWAY                 1022
            1024 TRAIL                           1022
            1085 SIDEWALK                        1022
            1091 SDWRAMP                         1085

I would like to collapse the records down to hierarchy paths:

HIERARCHYPATH
---------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE

ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

How can I do this?


texts:
I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an unwanted color in my application.

How can I step over all geometries, identify all that have "children", and cut them out of the "parent"? I created a temporary column called newgeom, as to not clobber my real geometry (geom) for testing, and tried, to no avail:

UPDATE zones a SET newgeom = ST_Difference(b.geom, a.geom) FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;


texts:
I have a database with four tables that has a number of columns (VARCHAR) that contains one or more spaces. I would like to replace these spaces with NULL.

I can do it if I specify the table and each column

UPDATE LaserData
SET Kant = NULL
WHERE Kant= ''

But is there a way that I can use a list of tables and a list of columns and do this for all without writing a query for each?


texts: MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

texts:
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?

If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?

(I'm guessing the answers are yes and yes :).


texts: How to gracefully handle a source database restore from backup in a data warehouse

texts:
We are facing a challenging situation with our data warehouse source databases.

Frequently these sources databases will be restored from backups. There is a high turnover of data entry persons who use the system and they make many mistakes. So the business will just restore from a backup and start over.

But at this point, the data in the data warehouse will have already been processed and needs to be corrected. There could be thousands of rows of fact data which is no longer valid. Is there an appropriate design pattern to handle this scenario?

For example, would you need a way to rebuild the data warehouse from scratch? Would you attempt to restore a backup of the data warehouse and then build ETLs to synchronize? Would you delete data from your fact tables and then re-insert?


texts: How inaccurate is sys.dm_db_partition_stats?

texts:
I am looking at ways to count rows in several tables on several databases.  In this particular case I am looking for unused and abandoned items.

Several sources source1, source2 recommend using sys.dm_db_partition_stats everything agrees that the count is not perfectly accurate.  MSDN describes row_count as

  approximate number of rows in the partition.

I understand if the value is always changing, it might be off by a bit. There are some good arguments on why this small discrepancy really doesn't matter.  But if the value is near zero or none, will it be accurate?

If I use sys.dm_db_partition_stats and I am getting no rows found, do I need to run a second query using COUNT (or something else) to make sure the table is really empty or is the inaccuracy limited to minor fluctuations over minor periods of time?

Clarify
I don't own the data, so I am ethically prohibited from looking at it. If sys.dm_db_partition_stats tells me a table is empty I can't just go open it and see if it really is empty. I need to know if I can depend on the results returned from it.  As pointed out in answers, more information than just this will be required, before deleting.  The table may be used as a temp table, maybe it gets rows written to it and removed, and just always happens to be empty when I look at it.


texts: SELECT is using a non-PK Index instead of the PK

texts:
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.

When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:

My question is: Why is it not just using the PK index instead?

Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.


texts: Why do I receive the error message "'' must appear in the GROUP BY clause or be used in an aggregate function"?

texts:
Where am I going wrong?  Why am I getting that message when I add the MAX function?

The query involved:

WITH
   references_cve AS (
      SELECT vulnerability_id, reference
      FROM dim_vulnerability_reference
      WHERE dim_vulnerability_reference.source = 'NVD'
   )
SELECT DISTINCT ON (
      da.ip_address,
      favi.port,
      dv.severity,dp.name, dv.title,
      dv.description,
      fix,
      dv.nexpose_id,
      cve.reference,
      da.host_name,
      dos.description,
      da.last_assessed_for_vulnerabilities,
      davbs.solution_id
   )
   da.ip_address AS IPAddress,
   CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END AS Port,
   dv.severity AS Severity,
   dp.name AS Protocol,
   dv.title AS VulnerabilityName,
   htmlToText(dv.description) AS Summary,
   htmlToText(dv.description) AS Description,
   htmlToText(fix) as Solution,
   'https://www.rapid7.com/db/vulnerabilities/' || dv.nexpose_id as SeeAlso,
   dv.nexpose_id AS NexposeID,
   ''||null as PluginOutput,
   cve.reference as CVE,
   ''||null as APPorOS,
   ''||null as BU,
   da.host_name as DEVICENAME,
   date(da.last_assessed_for_vulnerabilities) as LastScanDate,
   dos.description as DeviceOperatingSystem,
   MAX(sr.solution_id),
   davbs.vulnerability_id,
   dv.vulnerability_id

FROM fact_asset_vulnerability_instance favi
   LEFT JOIN dim_asset da USING (asset_id)
   LEFT JOIN dim_vulnerability dv USING (vulnerability_id)
   LEFT JOIN dim_site_asset dsa USING (asset_id)
   LEFT JOIN dim_site ds USING (site_id)
   LEFT JOIN dim_vulnerability_status dvs USING (status_id)
   LEFT JOIN dim_protocol dp USING (protocol_id)
   LEFT JOIN dim_service dsvc USING (service_id)
   LEFT JOIN dim_operating_system dos USING (operating_system_id)
   LEFT JOIN dim_asset_vulnerability_best_solution davbs ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
   LEFT JOIN dim_solution sr on davbs.solution_id = sr.solution_id
   LEFT JOIN references_cve cve on favi.vulnerability_id = cve.vulnerability_id

ORDER BY da.ip_address

The resulting message:

  Error: column "da.ip_address" must appear in the GROUP BY clause or be used in an aggregate function
  Character:456


texts: MariaDB does not start after restoring incremental backups with mariabackup

texts:
OS: CentOS 7

MariaDB: mariadb  Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

I am creating a full backup every 7 days on a replication slave, and an incremental backup every hour on top of that.

This script is used to create backups: run-mariabackup.sh

To restore the backup I am using this process.

However, after restoring the backup MariaDB does not start.

systemd: Starting MariaDB 10.4.12 database server...
mysqld: [Note] /usr/sbin/mysqld (mysqld 10.4.12-MariaDB) starting as process 11363 ...
mysqld: [ERROR] mysqld: File '/var/lib/mysql/aria_log_control' not found (Errcode: 13 "Permission denied")
mysqld: [ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
mysqld: [ERROR] Plugin 'Aria' init function returned error.
mysqld: [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
mysqld: [Note] InnoDB: Using Linux native AIO
mysqld: [ERROR] InnoDB: Operating system error number 13 in a file operation.
mysqld: [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
mysqld: [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
mysqld: [ERROR] Plugin 'InnoDB' init function returned error.
mysqld: [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mysqld: [Note] Plugin 'FEEDBACK' is disabled.
mysqld: [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
mysqld: [ERROR] Failed to initialize plugins.
mysqld: [ERROR] Aborting
systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE

Of course I have already done chown -R mysql:mysql /var/lib/mysql prior to that, so this error puzzled me. After this error I tried chmod -R 777 /var/lib/mysql to see if there is some permission that was missing, but starting MariaDB again provided me with the same error.

Here are the exact steps I took to restore the backup:

cd /home/mysqlbackup/
for i in $(find . -name backup.stream.gz | grep '2020-03-12_12-08-44' | xargs dirname); do
        mkdir -p $i/backup;
        zcat $i/backup.stream.gz | mbstream -x -C $i/backup/;
done
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_13-24-20/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_13-54-25/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_14-00-02/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_15-00-01/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_16-00-01/backup/
systemctl stop mariadb
mv /var/lib/mysql/ /var/lib/mysql_bak/
mariabackup --copy-back --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --datadir /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mariadb


texts:
As the title said. I found it inconvenient when accidentally rerun a copy (time-consuming query) to '\tmp\foo.csv'. I tried setting set -o noclobber in .bashrc but it did not help.

How can I prevent copy from overwriting existing file?

I am using Postgres 10. The doc seems not giving any info.


texts: How can I connect to SQL server by just using local ip address

texts: Currently I am connecting to SQL server using 192.168.1.157\MyInstance. I would like to connect to SQL server by just using 192.168.1.157 i.e if I type IP addressin SSMS it should connect properly using 1433 port number. How can I do so?


texts:
In my scenario I have 2 databases I would like to add into my AG. I have initiated it via automatic seeding, but then deleted one DB from the AG pool. As a result I expected it will stop the seeding of that DB into the replica and just start seeding the second one. Unfortunately (for me) seeding process of the first one is still running. Therefore my question is - how can I stop it?
My test instance is SQL Server 2017 Enterprise CU22.


texts: Database Role based access control design for survey app

texts:
I'm going to design the RBAC for a survey app. Each survey has the same role as below and only 1 manager, 1 leader and multiple participants.
Role_ID|Role_Name  |
-------|-----------|
      1|admin      |
      2|manager    |
      3|leader     |
      4|participant|

Each user can have multiple roles and this user can choose to be the participant role to join one survey or not when he is the manager or leader of a survey. To make the user only can action the survey he owns, for example, UserA is assigned to be the leader of SurveyA, so he can only has the capability, edit_survey, on SurveyA but not another surveyB which he's not assigned to, how should I design the database?
I generated two options, can someone check which one is better or there is another better solution?
Option1
I put the role manager and leader column as the FK of User_ID from User table on Survey table as it's one to one relationship and create a new participant table for participants.

Option2
I create a new user_role_in_survey table to store manager and leader role and this table replaces old user_role table. Survey_ID is the FK of Survey table and participant table for participants.


texts:
First table

 Complaint_id   Status  Reopen
    127493          5   1
    127494          5   0

  Second table

Parent_id   Currentstatus   Reopen  datetime    usertype
127493  0   0   2019-04-08 12:40:31.209103+05:30    c
127493  5   1   2019-04-08 12:40:31.209103+05:30    c
127493  0   0   2019-04-08 12:36:22.662995+05:30    c
127493  5   1   2019-04-08 12:36:22.662995+05:30    c
127493  0   0   2019-04-08 12:34:25.459444+05:30    c
127493  5   1   2019-04-08 12:34:25.459444+05:30    c
127493  5   0   2019-04-08 12:29:58.581771+05:30    E
127493  5   0   2019-05-08 12:29:58.581771+05:30    E

  Output

complaintid currenstatus usertype
127493        0            c

I am trying using below query

SELECT
 currentstatus
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      secondtable
  ) AS derived
WHERE Datetime = MaxDatetime

but i don't know how to connect with first table basically i want the records form first table is where reopen=1

select * from firsttable where reopen=1

and fetch the current status and usertype from the second table according to max datetime


texts: add mobile prefix in existing mobile field in mysql

texts:
I have mysql table 'USERS' which have a column name 'MOBILE" which contains customer mobile numbers in following format

333123456
333123457

I want to add 092 in starting of every number so that it should be like this

092333123456
092333123457

there are hundreds of mobile records I have to update, so is tehre any query I can run to accomplish this task?


texts: postgresql: permission denied for sequence _i_seq

texts:
I have a schema of a table in PostgreSQL that looks like:

CREATE TABLE IF NOT EXISTS  data.pmacct (

    i                       BIGSERIAL PRIMARY KEY,

And there are users:

A: superuser privileges
B: read/write privileges
C: read privileges

And I have made the following modifications in regards to the privileges (as user A)

CREATE SCHEMA data ;
ALTER DATABASE ip_spotlight SET search_path TO data ;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA data FROM PUBLIC ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO C ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO B ;

as user postgres

GRANT USAGE ON SCHEMA data TO netops ;
GRANT USAGE ON SCHEMA data TO netopsapp ;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO C ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA data TO C ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data TO C ;
GRANT ALL ON ALL TABLES IN SCHEMA data TO B ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA data TO B ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA data TO B ;

But I am getting the following error message as user B

  ERROR:  permission denied for sequence pmacct_i_seq

Could you please enlighten me what I have done wrong and user B is not able to do an INSERT statement into the data.pmacct table ?


texts: Insert triggers to eliminate sensitive information

texts:
A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control.  One column in this insert will have sensitive information that I do not want in my database.

If I use an INSTEAD OF INSERT trigger to blank out this column, will the sensitive information still end up in the transaction log?


texts: How to set weights on a Postgres TSVECTOR column?

texts:
Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to me). Here's what my table looks like:

CREATE TABLE documents
(
   id serial NOT NULL,
   name character varying(255),
   raw_data text,
   user_id integer,
   active boolean,
   created_at timestamp with time zone,
   updated_at timestamp with time zone,
   search tsvector
)

And here's the query I run to set the tsvector column (search):

UPDATE documents SET search = (setweight(to_tsvector(name), 'A') || setweight(to_tsvector(raw_data), 'B'));

Now, whenever I run a select query, like so:

SELECT name FROM documents WHERE search @@ to_tsquery('some & tokens');

The only results I ever get are if I type in the exact name of the data so for instance, I'd have a hello document so if I use the hello token, I get the result; however, if I type in anything from the raw_data column, nothing shows up. So, for example, I have a row where raw_data contains the sentence "Exhibit B, more information attached". Searching using any of those words comes up with nothing.

I'm trying to do the same thing locally, and it seems to work. The only difference is that I'm running on a windows machine and production is on linux and that the production table is UTF8. Could that make a difference?


texts:
I have faced syntax error during creating MySQL Trigger.

Create Trigger Count_McNum
before insert on tension
for each row
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000)
then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End

  1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'declare rowcount int' at line 4


texts: Will Peer to Peer replication still work with RPC out disabled?

texts:
PCI Compliance is failing due to "Microsoft SQL Server Database Link Crawling Command Execution"

There is no solution available however they recommend a Workaround:

  Disable RPC_Out and xp_cmdshell for this issue.

We currently use Peer to Peer transactional replication, and the RPC Out is a server option on linked servers...

Would peer-to-peer replication still work if that is disabled as per the 'workaround' ?


texts: How to check total allotted space inside a HDFS 'group'

texts:
Our DBA has created a schema for our team in HDFS/HIVE. Not sure if 'schema' is the right word, they call it a 'group'.
Anyway, we can only write to the data lake inside this schema, whether it is parquet files or hive tables.
Is there a way to check what is the max space allocated to our group , knowing only the schema name?
I dont want to accidentally load too much data.
Thank you.


texts: How to Split string into multiple values using best approach

texts:
We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a column?

Query:

Select *
from
(
        SELECT TblTwo.ID Desc_ID,
           (CASE WHEN TblTwo.Description_Type = 'Default' THEN coalesce(tblOne.Code + ' ','') ELSE '' END) + TblTwo.[Description] AS String
        FROM TableOne tblOne
        INNER JOIN TableTwo TblTwo ON tblOne.CodeID = TblTwo.CodeID
        WHERE TblTwo.Description_Type IN ('Default','Custom')
)ResultOne   ---- Return almost 20000 rows and take 1 sec to execute
CROSS APPLY dbo.udf_TokenString(String) ---- return almost 194379 rows and take 3 mins to execute

Result of drived table ResultOne :
Desc_ID     String
1           HCD.39877
2           HCD.39777 Personal test history of documents
4           CD.397 Unspecified document text
5           N.7 This is end of file

Script of function:

CREATE FUNCTION [dbo].udf_TokenString (@string VARCHAR(MAX))
RETURNS
@Results TABLE
(
    Item VARCHAR(255),
    location INT,
    Sequence INT IDENTITY PRIMARY KEY
)
AS
BEGIN

    DECLARE @Len INT, @Start INT, @end INT, @Cursor INT,@length INT

    SELECT @Cursor=1, @len=LEN(@string)

    WHILE @cursor<@len
       BEGIN

       SELECT @start=PATINDEX('%[^.A-Za-z0-9][A-Za-z0-9%]%',
                       ' '+SUBSTRING (@string,@cursor,50)
                       )-1

       IF @start<0 break

       SELECT @length=PATINDEX('%[^.A-Z''a-z0-9-%]%',SUBSTRING (@string,@cursor+@start+1,50)+' ')
       INSERT INTO @results(item, location)
           SELECT  SUBSTRING(@string,@cursor+@start,@length), @cursor+@start
       SELECT @Cursor=@Cursor+@Start+@length+1

       END
    RETURN

END

GO


texts:
I have an issue with my database Oracle 11gR2.

When I try to connect to my database via SQL Developer, it says that my account is locked after too many missed connections due to a wrong password but when I use the query

SELECT username, account_status FROM dba_users

on SQLPlus, the account is OPEN. I tried to unlock/lock/unlock the account without success.

This problem comes after the expiry of passwords. I've changed them via SQLPlus (with the command ALTER USER <username> IDENTIFIED BY <password>) but when I wanted to connect with my new password on SQL Developer, I had the error message

  ORA-01017 Invalid Username/Password.

(and if I put the old password, I had the error message "expired password".)

I would like to know how to unlock the locked account (even though I see it open).


texts: How do I determine when scaling is necessary or helpful?

texts:
I have a MySQL Server that I use for the back-end storage of an online game. The game writes to the database to persist character information such as world location, health status, ability stats, etc .. This data is written every few seconds to the database for each character and vehicle. I have been noticing that as more and more users join the game that queries I write via the database seem slower. Also, as I make the online world more complex, via content added via the database, the game tends to slow.

I am looking for the bottleneck and I am wondering how I go about determining if scaling my database (vertically or horizontally) will make a difference. Knowing the database is MySQL how do I go about seeing if my single database server is the bottleneck and if scaling it would be helpful? Thank you.


texts:
I am try to run bulk insert command on SQL Server 2008 but I am having issues with the security.
after researching via internet, the problem has something to do with account delegation and impersonation.

However all solutions seems to point to an Active Directory setup which my setup is not.

My setup is SQL Server 2008 Express on Windows Server 2008 Standard configured as a workgroup.

Despite a SQL Server user account, assigned all the user mappings and server roles and ensuring security set to SQL, I am getting this error:

  Msg 4861, Level 16, State 1, Line 1
  Cannot bulk load because the file "\server_name\file_name.txt" could not be opened. Operating system error code 5 (Access is denied.).

So, how does one setup SQL Server account delegation and impersonation on a workgroup environment?

Thanks in advance


texts: Can I Show Multiple "WHERE" Filtered Results In A Single Query/Output Table?

texts:
I am using Postgres 9.2.4 on Red Hat 6.
Tables

---------------------------------------
|             Table 1                 |
---------------------------------------
| WorkflowID | DataSet | CreationTime |
---------------------------------------

------------------------------------
|             Table 2              |
------------------------------------
| WorkflowID | Data | CreationTime |
------------------------------------

Description
I'd like to be able to create a query which, for a list of records in Table 1, aggregates the WorkflowID COUNT/SUM(LENGTH(data)) from Table2.  That's easy enough with:
SELECT b.WorkflowID,
       COUNT(b.*),
       SUM(LENGTH(b.data)/1024.)::numeric(10,2) AS size_kb
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID
ORDER BY size_kb DESC;

Now, I'd like to compare these WorkflowID contributions before-and-after a given CreationTime and have it in a single query and output table.  Something like:

----------------------------------------------------------------------------
| WorkflowID | count_before | size_before_kb | count_after | size_after_kb |
----------------------------------------------------------------------------

My Problem
To my less-than-ideal understanding of SQL's mysteries, this looks like some kind of multi-WHERE clause type of query.  Does that even exist, or would I have to run multiple queries and then merge the output?


texts: Use large set of data in multiple databases

texts:
I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter.

Say you have a large set "X" of data that consists of multiple tables. For the sake of argument, let's say that "X" consists of all entries from an elaborate phone book.

You then create various databases for different applications, and each application takes a subset of "X" to build upon. It has no access to data of "X" outside the subset.

But if one application changes a tuple in its subset, this change should be propagated to the central store of "X" and thus be available to any other application accessing the same tuple.

To stay with the example of the phone book, application 1 might work with a subset of all people who are parents. Application 2 might work with a subset of all people under 30. These two subsets might intersect. So if application 1 changes the phone number of a parent under 30, this changed phone number should be pushed back to "X". And because application 2 uses the same tuple, it should have access to the updated version.

How do you solve this in practice? I'm using PostgreSQL myself but maybe there is a more universal solution to this question.


texts: MySQL Replication - transactions missing in M->S1->S2 setup

texts:
I have MySQL chained replication setup as Master->Slave1->Slave2.

Events are replicating perfectly fine from M->S1 and also recorded into the Relay & Binary logs on S1 but then S2 doesn't receive these events.

SHOW SLAVE STATUS on S2 is showing both IO & SQL threads running, Exec_Master_Log_Pos is constantly changing but Relay_Log_Space & Relay_Log_Pos stay static.

Master is MySQL 5.5, S1 & S2 are both MySQL 5.6.40.

Interesting that if any updates are done directly on S1 then they replicate across to S2 correctly.

I have checked disk space isn't full on any of the servers.

Have also looked and put a comment on Mysql replication is enabled but stuck at "waiting for binlog to be updated"

On S1 I have:

mysql> show global variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| init_slave                   |                       |
| log_slave_updates            | ON                    |
| log_slow_slave_statements    | OFF                   |
| rpl_stop_slave_timeout       | 31536000              |
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 3600                  |
| slave_parallel_workers       | 0                     |
| slave_pending_jobs_size_max  | 16777216              |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
| sql_slave_skip_counter       | 0                     |
+------------------------------+-----------------------+

On M:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=05ac0062-7af8-11e5-9af2-9c8e991000d2

On S1:
 - SELECT @@global.server_id=4
 - SELECT @@global.server_uuid=062ad58e-59ed-11e8-a7ea-000d3a32adb2

On S2:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=184b1c69-7868-11e8-aeab-000d3a321333


texts: How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

texts:
I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc...

These are stored in a table with a Hirarchy-ID to sort on, like this:

HIERARCHY | SETTING | OPTION | PREFERENCE | COLOR |
----------+---------+--------+------------+-------+
100       | NULL    | 1      | NULL       | NULL  |
10        | NULL    | 2      | square     |       |
1         | 0       | 3      | rounded    | green |

The result I'm after is for each column, the first not-null value (and it would be great if I can also squash a NULLIF(COLOR, '') in as well). In this example the result would be:

SETTING | OPTION | PREFERENCE | COLOR |
--------+--------+------------+-------+
0       | 1      | square     | green |

Currently I'm doing a separate query for each column but I'm hoping that this can be achieved in a more elegant way?

I'm working with MS SQL Server (and need to support version 2005).


texts: How to call a function in regex_replace with matches?

texts:
I'd like to use some processing function instead of replacement text in regex_replace function. Is there possible to use a function with matches as arguments, like:
SELECT REGEXP_REPLACE( description, '\[([is]?:?)(\d+)\]', CONCAT_WS(' ', \1, \2), 'gi' )
  FROM some_table
  WHERE id = 123;

Of course, backreferences \1 and \2 do not work here, but is there some way to use variables instead of them?
Background: description may contain more than one string with a pattern like [12345] or [i:12345] or [s:12345] and I'd like to process such matches into advanced strings, so I thought the best way is to write a custom function and call it with matches of regex_replace. I just did not found in the docs, how could I use matches as variable/arguments. Is this possible or what is a better way to accomplish my goal?
EDIT I show description field before and after:
Lorem ipsum dolor sit amet [123], consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et [234] magna aliqua.

should be after replacements like:
Lorem ipsum dolor sit amet "one-two-three", consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et "two-three-four" magna aliqua.


texts:
When I used this query to see the current primary key seed of my PostgreSQL 13 table:
select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id'))

it told me:

SQL Error [55000]:
ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

The id column is defined like this:
ALTER TABLE rss_sub_source
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY
        (START WITH 1233);

Table definition:
CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    --  ... many more columns
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

How to see the current auto-increment seed for the primary key?


texts: Mysql Cluster Ndb Restore Foreign key constraint violated

texts:
I am trying to restore mysql cluster backup on slave cluster.

When i run ndb_restore command it gives me following error:

$ /usr/local/mysql/bin/ndb_restore -n 6 -b 15 -r ./6

Opening file './6/BACKUP-15.6.log'
File size 1210464 bytes
execute failed: 255: Foreign key constraint violated: No parent row found

NDBT_ProgramExit: 1 - Failed

How can i skip foreign key errors ?


texts: What is an appropriate datatype for an RFID, and is there a standard definition for the format?

texts:
I have a database that tracks people based on RFID tags embedded in their shoes.  This data arrives in a VARCHAR(MAX) column with other data mixed in, in no particular order.  So far, all the RFID values are formatted like the following:

AnnnnnnnnAnnAAAA  (where A = 0-9  and n = A-Z)

I am writing a report to extract RFIDs from these messages and store them in their own table.  As long as the RFIDs follow this same pattern, I can find them. If the RFID format changes, my extraction process will fail.

Is there a standard format for RFID tags?  What datatype would be best suited to store RFID tags?

I am going with VARCHAR(20) for now.


texts: Query that checks for subset to mysql query

texts:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)

I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.


texts: How to restrict SSMS to list the database of a particular user (Windows 2012 + Plesk)

texts:
We are using windows 2012 R2 server with Plesk and provide shared hosting for our customers.

Version Details: MSSQL server 2012 and SSMS 11.0.2100.60

In SSMS if we login with particular user, it is displaying all the database instead of particular user's database. Displaying all DBs to all users, it is a security issue. (though, they cant access the DB, Just displays the name).

How can I restrict it, to display only the DB which belongs to that specific user.


texts:
I have a query:

SELECT *
FROM OPENQUERY
(
    oracle,
    'SELECT *
    FROM [some_table]'
)

It runs for about 7 hours. I only need to run this query once to import data into a new system when we change system.

I want to know is there some table in SQL Server 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?


texts: Data Backup and Restoring the same if requried in SQL SERVER 2008

texts:
In Multi-tenancy , Shared Database Shared Schema.
How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction.

Thanks!!!

NB: Database : Microsoft SQL SERVER 2008


texts:
For example, say I have something like

CREATE TABLE COMPANY(id int not null primary key, ...);

CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null,
 CONSTRAINT FK_DEP_COMPANY_ID FOREIGN KEY(company_id) REFERENCES COMPANY(id),...);

CREATE TABLE EMPLOYEE(id int not null primary key, department_id int not null,
  username varchar(30) NOT NULL, ...,
 CONSTRAINT FK_EMPLOYEE_DEP_ID FOREIGN KEY(department_id) REFERENCES DEPARTMENT(id));

I have to implement uniqueness of EMPLOYEE.username within one company.

I see 2 ways.
  1. Using AFTER statement level trigger on EMPLOYEE table (something similar to http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936 , case1)
  2. Just adding company_id to EMPLOYEE

Neither of them looks ideal to me, and I wonder what is the proper way to implement uniqueness in such case.

Thanks.


texts: Which characters are allowed in MySQL/MariaDB user account names?

texts:
It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section includes information on character encodings and the maximum name length, but it doesn't state whether the characters that can be used in an account name are restricted (or perhaps I missed that part?).

So, are there any special charachters in MySQL/MariaDB that cannot be used in user account names? For example, are control characters (like a line break) or wildcard characters (like the asterisk) illegal characters?


texts: What is the quickest way to restore a DB

texts:
Lately I have been finding myself restoring a DB to my local instance of SQL-server a lot more. I wanted to speed up the process by writing a script and then just executing it in order to restore over some X DB on my local. However, every time that I run this script I get the error saying the DB is still being used. Now this could happen because I have a query open, or because a web application is still using it, but instead of me going to track down what is using my db and stopping it. I would much rather have my script take care of this since this is not going to be affecting anyone but me.

Is there a better way to do this than querying for the processes that are using the DB and killing them? Currently I was thinking of doing the following

 select
 spid,status,loginame=SUBSTRING(loginame,1,12),
 hostname=SUBSTRING(hostname,1,12),
 dbname = SUBSTRING(db_NAME(dbid),1,10)
 from sys.sysprocesses where DBid = 17

and then running the results back through a dynamic sql with the KILL command.


texts: MariaDB Galera Cluster - Cert Deps Distance issue

texts: I have mariaDB Galera 10.2 Cluster running with 3 nodes; in google cloud VMs with 8 cpu ; 15Gigs Ram / node; My slave_thread setting = 24 ; currently there is no much write happening and local_receive_q_avg= 0.019481 but my Cert_deps_distance= 54 in two nodes and 0 in one node ? What does that mean ?


texts: SQL Server 2000 - How to find unused indexes

texts:
In SQL Server 2000 - how can we find unused indexes, so that we can drop them.]

I have scripts for 2008 but don't know how to do it in SQL Server 2000.

Please help.

Regards


texts: mysql directory grow to 246G after one query, which failed due to table is full

texts:
I was trying to run the following statement with the hope to create a join of two existing tables.

create table CRS_PAIR
select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, concat_ws(',', a.YTG, b.YTG) as YTG
from CRS_TESTING a, CRS_TRAINING b
where a.TESTING_C=b.TRAINING_C;

Currently the size of these two tables are:

mysql> SELECT table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" FROM information_schema.tables WHERE table_schema = "crs";
+----------------+---------------+
| table_name     | size in megs  |
+----------------+---------------+
| CRS_TESTING    |         36.59 |
| CRS_TRAINING   |        202.92 |
+----------------+---------------+

After a little over a day, The query finished and I got the following result.

140330  2:53:50 [ERROR] /usr/sbin/mysqld: The table 'CRS_PAIR' is full
140330  2:53:54  InnoDB: ERROR: the age of the last checkpoint is 9434006,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

It turned out that the size of /var/lib/mysql has grown to 246GB in disk space, and the disk run out of space. However, for some reason, the CRS_PAIR table does not show up in the shell. Even when I try to get the size of all databases.

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / (1024 * 1024) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| crs                |            1426.4531 |
| information_schema |               0.0088 |
| mysql              |               0.6453 |
| performance_schema |               0.0000 |
+--------------------+----------------------+
4 rows in set (0.74 sec)

This is the show tables command.

mysql> show tables;
+----------------+
| Tables_in_crs  |
+----------------+
| CRS_TESTING    |
| CRS_TRAINING   |
 some other tables
+----------------+
9 rows in set (0.00 sec)

CRS_PAIR is not there.

May I ask if anyone can help me figure out where this mysterious table went to so that I can clean up my disk space?


texts: Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

texts:
I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that?

I am creating an index in a table with 10000 registers, and in a column with 15 chars.  It is taking 10 minutes using the traditional CREATE INDEX.

I know it is very difficulty to answer that, but there is a way to estimate?


texts: How to store daily records in SQL Server 2017 database?

texts:
So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries.

Example:

and so on...

Now, as the application will grow, the nummber of users will also increase, thus, making millions of records. I have to fetch monthly, yearly quarterly data from this table, which will take too much time to fetch whatever query I will use. So, I want to ask what can I do to speed up the process of fetching the data fastly?
Also, can anyone please tell me what better design can be given to this table?

NOTE: I am a beginner in Database work and has very little and limited knowledge.


texts: SQL agent job Error while migrating from 2008 R2 to SQL 2014 for openrowset query

texts:
I need to insert metadata into temp table. It was working fine in 2008 R2. Below is my query.

Insert INTO #Temp
(SELECT * FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=YES;', 'set fmtonly off; EXEC msdb.dbo.sp_help_job') )

Getting this error and unable to resolve.

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure invokes an extended stored procedure.


texts: Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant

texts:
This is my first time installing Oracle 11g R2 on Linux. I followed all the instructions in the Oracle documentation, but in the installation process, the Net Configuration Manager is failed. I continued the process, and after it finished, I couldn't connect to sqlplus.

tnsnames.ora

# tnsnames.ora Network Configuration File: /Oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localhost)
    )

listener.ora

# listener.ora Network Configuration File: /Oracle/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /Oracle/u01/app/oracle

Output of lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAR-2013 02:35:48

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

I used ./netca to reconfigure the listener, but the program told me that the port is in use. I deleted it and added a new one, but the same warning that port is in use. When I kill the process that uses this port (1521). and reconfigure the listener, the same message appears that the port is used.

When I run lsnrctl status, I get:

  Services Summary...
  Service "orcl.localhost" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
  Service "orclXDB.localhost" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...

But when I use sqlplus and enter user-name SYSTEM and its password, I get:

  ERROR: ORA-12541: TNS:no listener


texts: Trigger function using current row and current table name as variables

texts:
Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB.
They only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a trigger that calls a trigger function named trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND timetype = 'start'
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

ELSIF NEW.timetype = 'lap' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap')
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := v_timeidx + 0.001;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND (timetype = start OR timetype = lap)';
    END IF;

ELSIF NEW.timetype = 'resume' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume')
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := v_timeidx + 0.001;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND timetype = start';
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql;

Trigger definition:

CREATE TRIGGER trfn_tbl_log_a
  BEFORE INSERT ON tbl_log_a
  FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a();

So I have to create 26 trigger functions, one for each tbl_log_%letter%
all of them are exactly the same except for the used table name (tbl_log_a in the example).

Is there a way to write one generic trigger function, with dynamic SQL maybe and parameterize the table name?

My trigger function uses several table columns:

timeidx
fnname
timetype
stmtserial

... and many more that I didn't add for size, but all in the example list all the kinds.


texts:
I'm new to PostgreSQL and I wanted to use or run a specific file on the SQL-Shell on windows but I`m getting some errors of what I think is not making sense from my knowledge.
I ran the following...
\i 'C: \Users\Username\Documents\person.sql' : No such file or directory

I don`t know why this is not functioning with the quotes and by default this file exists, which I downloaded from a website!
Through my experimentation, I ran the following again without quotes with a different error
\i C: \Users\Username\Documents\person.sql : Permission denied

I don't know why it says at first it doesn't exist but later getting no permission over the file. My question is what is the problem with the following executions and is something wrong with my running?
I looked at this site and saw common problems but it couldn't help me further!
Thanks!


texts: Does this computed CHECKSUM() index design make sense?

texts:
I have come across what looks to me like a slightly odd pattern in a SQL Server 2005 database I'm taking care of, and was wondering whether it's just me, or whether it really is odd.

There are a number of tables with uniqueidentifier primary keys, which also have a computed column which is the CHECKSUM of that key, e.g.

[CustomerGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[CustomerHash]  AS (CHECKSUM([CustomerGuid])) PERSISTED,

Then, there are indexes which contain both of these fields, e.g.

CREATE NONCLUSTERED INDEX [IX_Customer_CustomerHashAndGuid] ON [dbo].[Customer]
(
    [CustomerHash] ASC,
    [CustomerGuid] ASC
)

This pattern also pops up with Guids that are not primary keys - e.g., an Order table with CustomerGuid and CustomerHash for each order, and an index on those two columns for looking up orders by customer.

Surely, the whole point of a checksum is that you create an index just on the checksum, so a SELECT will retrieve the records that match the checksum, and then compare the underlying value as a safety check? Doesn't putting the underlying value in the index waste a bunch of space for no real gain?


texts: What subquery do I need to calculate this report?

texts:
I have two tables: tickets and comments. I need to run a report that shows me which ticket is "neglected" meaning that there has not been a comment in X amount of time.

If I was to do this in PHP, I would load ticket, then do a separate query for each one, see what the most recent timestamp was (max(comment_ts)), and keep the values of the "old tickets" in an array, then display them from there. But, it seems I should be able to do this as a single query via MySQL and it would be much faster, so I am trying to create a view for this.

Assume this is the basic table structure:

-- MySQL Script generated by MySQL Workbench
-- 07/18/14 17:29:39
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`tickets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`tickets` (
  `ticket_id` INT NOT NULL,
  `ticket_description` TEXT NULL,
  PRIMARY KEY (`ticket_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`comments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
  `comments_id` INT NOT NULL,
  `comments_comment` VARCHAR(45) NULL,
  `comments_timestamp` INT NULL,
  PRIMARY KEY (`comments_id`),
  CONSTRAINT `comments_bug`
    FOREIGN KEY (`comments_id`)
    REFERENCES `mydb`.`tickets` (`ticket_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


texts: Exclude certain results from in condition in sql

texts:
I have two tables
Table 1
Id data1  data1
1  'ff'   'ffe'
2  'r'    '3'
3  'df'   'er'

Table 2
Id   WFID  data1   data2
1    12    'd'     'e'
1    13    '3'     '4f'
1    15    'e'     'dd'
2    12    'f'     'ee'
3    17    'd'     'f'

Now what I need is to join the two tables and get the data when only WFID is 12 or 17 for the record and 12 or 17 only. In this case Id number 1 has 12 but it also has 12 and 15 so it's excluded. Id number 2 and 3 are included because they have just 12 or 17.
I used the below query but it is also giving me data with id 1. Can someone please tell me what I can use to fix this.
select * from Table1
where Exists (select distinct ID from Table2 where
    Table1.ID = Table2.ID and
    WFID in (12,17))

Thanks


texts: High Availability Options For SQL Server

texts:
I'm looking to upgrade my single server SQL Server 2008 environment to a platform with much greater redundancy. The current system was put in 5 years ago when the company started and so was built down to as low a cost as possible. However, we're now well established with a large client base and as such the replacement system needs to be much more redundant.

We have a few sites around the country (as we're part of a larger group with multiple sites), so have access to backup offices etc and remote sites to host servers.

I'd like suggestions about how best to achieve having our two or three key databases available throughout hardware failure or lack of access to site. I'm thinking of some type of cluster with a pair of local servers, then one off-site server with a copy of the DBs on. The DBs themselves are actually very small, so hardware can be pretty low spec and I can get away with a few physical boxes. However, I'm conscious that SQL Enterprise 2016 is VERY expensive as we'd have to licence by core.

I'm open to all suggestions/options etc. We're a pure MS house, so we've no other OS platforms. I'm reading conflicting reports regarding DB mirroring vs clustering etc.

Thanks


texts: Combine the same column from multiple rows into one row

texts:
I am joining two tables HR and UserDefined based on the Employee_ID:

HR Table

First Name  Last Name   Employee_ID
----------  ---------   -----------
Joe         Smith       456654

User Defined Table

Area    ud_1_text   ud_2_text   Employee_ID
----    ---------   ----------  -----------
1       ABCD1234    31/08/2018  456654
2       69090       23/05/2022  456654

Now I know that if Area equals 1 then it is a "Drivers Licence" and if Area equals 2 that it is a "First Aid Certification".

I would like to be able to return the following in one line per Employee_ID:

Employee_ID First Name  Last name   D/L Number  Expiry      First Aid Expiry
----------- ----------  ---------   ----------  ----------  --------- ----------
456654      Joe         Smith       ABCD1234    31/08/2018  69090     23/05/2022

Any ideas on how to do this?
Microsoft SQL Server query please.


texts: Searching a text with space in mySql match against Query

texts:
I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result.

My MySql Query is:

SELECT count(id) as total
    , MIN(product_price) as min_price
    , MAX(product_price) as max_price
FROM `products_details`
WHERE subcat_id = 1026
    AND MATCH(alternate_name, product_desc, keywords)
    AGAINST ('+moto +g +plus +(>mobile <mobiles)'; IN BOOLEAN MODE)

I have problem with:

  AGAINST ('+moto +g +plus +(>mobile <mobiles)'

I want to search for string moto g plus mobile,using the above query. I want to add space before and after the g. Example, moto "SPACE"g"SPACE".

I tried with the 2 following solns, its not working.

[[:space:]]

  AGAINST ('+moto +[[:space:]]g[[:space:]] +plus +(>mobile <mobiles)'

and

  AGAINST ('+moto +\\ g\\  +plus +(>mobile <mobiles)'  ==> I have a space character (Ascii 32) after the \ in this query

Both of them are not working.

Can you please let me know what should be there?

Edit: The value of ft_min_word_len is set 1.


texts: Where is the documentation for version 1.0 of PostgreSQL wire protocol

texts:
The current documentation refers only to version 3 of the wire protocol.
However, there is the following note in the documentation:

For descriptions of the earlier protocol versions, see previous releases of the PostgreSQL documentation

However, I can't find anything about previous versions of the wire protocol.


texts: PostgreSQL's PGP_SYM_ENCRYPT log security

texts:
In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with LIKE %keyword%, BETWEEN etc, which I can not achieve if I use programming language such as Java or PHP to encrypt the data then insert it into the database.

However, I found an issue. Once I execute queries with PGP_SYM_ENCRYPT new line in the log file is written. Interestingly, the log is including the password in PLAIN!

Of course, IMO, this is unacceptable. I want to secure my password, in the same time I also want to log any change (INSERT, UPDATE, DELETE) into my database.

Is there any way to hide this password information from the log file?


texts: The operating system returned error 21 (The device is not ready.)

texts:
Every time I reboot Windows, for some databases I get this error:

  The operating system returned error 21(The device is not ready.)

I checked the disk with chkdsk /r - no bad sectors.
I executed DBCC CHECKDB with no errors:

*(CHECKDB found 0 allocation errors and 0 consistency errors in database)*

If I restart SQL Server the errors disappear.

Windows 10 and SQL Server 2016 Express.


texts: How to verify an oracle database license

texts:
We have bought an oracle 11g database license the supplier gave us a paper with oracle logo and said that is the license, we were expecting our company name to appear on it, but it doesn't.

My question is :

Is there anyway to verify if that paper is really a license,
what does an oracle license look like.

Thanks


texts: MySQL Dynamic Result Query with column when values are on same column

texts:
I want to create a dynamic value based on election result. I've three table.

vote table
Candidate table
Candidate position table.

Here the candidate table and position table is dynamic. its means I haven't any control on it.
I want to make a view like this:

Here Ballton num come from vote table, president, general secretary and treasurer is come from candidate position table and against each ballot no all column will be populate.
Here are the rest of the table structure:
Vote:

Candidate:

Candidate position:


texts: SQL Server Cross Domain Transcational Replication

texts: Has anyone got any documentation for doing/implementing Cross Domain SQL Server 2012/2008R2/2008 transactional replication document or steps (with screenshot or without screenshot)?


texts: SQL Server: SSMS "Save Results As" creates CSV that Excel mangles

texts:
Using the Save Results As option, shown below.
CSV looks good!

But when Excel opens/imports it:

Long strings are mangled as numbers (if the data is largely numeric)
Dates are not detected properly
My users beef about all this

How do I get a query to a clean excel file (a real xls/xlsx) without dancing? (We like everything to actually in the SQL file itself... I wish we could do "SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS ...")


texts: How can I search for all points with a specific range (radius) of another point?

texts:
The syntax I use to query for polygons in range of other Points

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1

But, to query points of interests, I use a radius and I'm using this.

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Is there a better way to write this? Can the queries be improved?


texts: sql script to export table's column data to another table's column

texts:
Let's say , I have two table with same schema but different data .
Table_A and Table_B .

Table_A
--------
ID(p_key)     Number(p_key)         Column3     Column4
-----------------------------------------------------
ID1                1                  AAA         BBB
ID1                2                  CCC         DDD
ID2                1                  EEE         FFF
ID2                2                  GGG         HHH

-

Table_B
--------
ID(p_key)     Number(p_key)         Column3     Column4
-----------------------------------------------------
ID1                1                  AAA_1       BBB_1
ID1                2                  CCC_1       DDD_1
ID2                1                  EEE_1       FFF_1
ID2                2                  GGG_1       HHH_1

I want to export(overwrite) Table_B column3 data to Table_A column3 , where ID and Number Columns data are equal .
After executing of script , Table_A's data should be ,

 Table_A
    --------
    ID(p_key)     Number(p_key)         Column3     Column4
    -----------------------------------------------------
    ID1                1                  AAA_1        BBB
    ID1                2                  CCC_1        DDD
    ID2                1                  EEE_1        FFF
    ID2                2                  GGG_1        HHH

How can I make this using sql script only ?
I use MS SQL-Server 2008 R2 .


texts: MongoDB ReplicaSet reconfig when primary and majority don't exist

texts:
http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

Is there anyway to reconfigure a replica set in the event the primary as well as a majority of the servers are down ? For example if data center 1 goes does, a procedure can be run to reconfigure and set a new primary with the remaining servers?

I keep getting this "Command '$eval' failed: not master (response: { "note" : "from execCommand", "ok" : 0.0, "errmsg" : "not master" })"

We only have 2 data centers. (I we had 3 i realize this would solve my problem, but we don't.
)
I was thinking about running a reconfigure on a 5 member replica set every time the fail over happens and changing the host of the arbiter from one data center to another so that the active data center will always have the arbiter, but I can't do that if 3 of the 5 members of the replica set aren't available...because not new primary will ever be set.

Any suggestions?


texts: How to flatten and pivot a normalised hierarchy table

texts:
I have an OrgChart table that looks something like this:

PositionCode   PositionName   ParentCode  Level
AA0001         CEO                        0
AA0002         CFO            AA0001      1
AA0003         CIO            AA0002      2
AA0004         SnrMgr         AA0002      2
AA0005         JnrMgr         AA0004      3
AA0006         ItMgr          AA0003      3

etc
So, the ItMgr reports to the CIO, who reports to the CFO, who reports to the CEO.

I need to convert this to a flattened structure like this (for a BI solution): (CurCode is the position we're focusing on for that row, followed by the complete org hierarchy above that position).

CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3Name
AA0001   AA0001   CEO      na       na       na       na       na       na
AA0002   AA0001   CEO      AA0002   CFO      na       na       na       na
AA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       na
AA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       na
AA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgr
AA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr

How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns.
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.


texts:
I have an Amazon RDS (postgres) database and an client asked me to have access to certain data stored in this database preferably in the form of a comma separated file. If the data changes, the client would like his report to represent the new state of the data.

What would be the text book way of providing this?

I was thinking about creating a view and granting them access to only this view.


texts:
I have 2 tables (tblA and tblB) joined by ReportID key. This key is a Primary Clustered key on tblA, and is a FK on tblB.
I have created a Non-clustered index on UserID and UserDate in tblA.

I have a query where I want to return a number of rows from tblB based off a ReportID selection from tblA.

My issue is the Execution Plan runs a Clustered Index SCAN on tblB. How do I get it to perform a seek? Or how can I improve the query execution?

Thanks!

SELECT
     b.[Asset]
    ,b.[Investment]
    ,b.[Currency]
    ,b.[Address]
    ,b.[Email]
    ,b.[Region]
    ,b.[Country]
    ,b.[Sector]
FROM
    tblB b
INNER JOIN
    tblA a
    ON a.ReportID = b.ReportID
WHERE a.UserID = @UserID
    AND a.UserDate = @Period;


texts: Splitting a csv column from select query into multiple columns

texts:
I have a stored procedure

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code,
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX([ShipWorks].[dbo].[OrderItem].[Location])
         ELSE MAX([Picklist].[dbo].[ItemData].[InventoryLocation])
    END AS Location,
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity,
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem]
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID]
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [ShipWorks].[dbo] [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

that returns this:

Code              Location                                  Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AISLE                                   1           M
GM22660003-EA      B1K2                                      1           M

I want to split the Location column into multiple columns .

Code               loc1  loc2  loc3  loc4  loc5 loc6        Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AI                                      1           M
GM22660003-EA      B1K2                                      1           M

I found this answer but that was for postgresql. Also this, but it returns a table with multiple rows and you can select the top 1 but I want all the results and I'm not sure how to use the tvf Split(string, ',')in the stored procedure to get the result set I want?

This select statement results in a error:
'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code,
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
         ELSE MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
    END AS Location,
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity,
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem]
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID]
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location


texts: How to store user defined scoring formulas?

texts:
First post, so apologize if I have not chosen the best spot for it:

If I had table of values, let's say corresponding to a question number and its corresponding answer, what is the correct way to plan a database to allow users to store formulas to calculate a score based on said answers?

Example:

Let's say I have a table of answer given to a series of questions:

Question_and_answer_table

ID   Answer
 1       3
 2       5
 3       7
 4       9

Then I have 3 different users, User1, User2, User 3.

If I want each user to be able to define how they want to weigh the answers to determine a final score

  ex: User1: A1*3-A2*2+A3*7*A4=SCORE

what's the best way to handle storing a formula for each user?

Thank you for your time and help!

Cheers


texts: MongoDB Too many open file while reSync member

texts:
I get the problem in many past days. I have MongoDB cluster with 3 shards and 3 replicas.

    1   2   3
A   O   S   S
B   S   P   S
C   P   O   P

P - Primary state
S - Secoundary state
O - Other state

Alphabets are replica machine and numberic are shards.

I try to resync all data around 2TB in mongod machine with other state (A1 and C2). But I got error while resync data in primary that mongod service going down because Too many open file

2019-03-16T16:35:22.351+0000 E -        [conn28204] cannot open /dev/urandom Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system

I already try to fixed ulimit as mongodb recommendation.

> ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 241518
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 64000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 241518
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and set in /etc/security/limits.conf like this

*            soft    nofile      64000
*            hard    nofile      64000
root         soft    nofile      64000
root         hard    nofile      64000

but all of this is not fix my problem. I still get mongod service down because Too many open file. I stuck for 3 days. Anyone have any ideas or solutions?


texts: MySQL updating table with values selected from same table in one go

texts:
I want to update quite a few values in a table. Said values already exist in another table.

I'm selecting said values using the following query:

SELECT c.last_purchase,
       so.customer_id,
       so.modified_at
  FROM customer c
 INNER JOIN sale_order so  ON c.customer_id = so.customer_id
                          AND c.country = so.country
 WHERE so.created_at > c.last_purchase

So far so good. But my sql skills aren't too great(hey, I'm being honest here).

The correct values are so.modified_at. I want to update c.last_purchase to match so.modified_at.

Originally I was thinking of using a WHEN THEN combination. Or perhaps some sort of stored procedure.

I could this by using a bash script or any other programming language that knows how to connect to a database. But I'd like, if possible, to do this strictly using MySQL. If not possible I'll have to settle for a bash script(I already wrote one).

Honestly, I have no idea where to start.

Any pointing in the right direction is more than appreciated.


texts: SQL DB Permissions so anyone can modify the DB

texts:
I'm teaching a class on SQL Server 2014, and want students to be able to restore a Db I give them and then be able to take ownership and modify tables, and also to view the database diagram. Apparently, the diagram part is problematic.

The students will have a local Windows administrative account on the lab PC. If I make the guest role a db_owner will that allow students to take ownership? If not, what strategy can I adopt to give them full permission to, among other things, the database diagram?


texts: Recovering MySQL data from files in windows

texts:
I had a server fail (disk failure, old server; no raid) at the weekend and am unable to recover the MySQL databases from the backup.

After exhausting all the usual recovery options, I've performed a disk recovery and have managed to retrieve the contents of the MySQL data folder located at c:\programdata\MySQL Server 5.5\data.

The folders corresponding to each of the databases are there and are populated with .frm and .myi/.myd files... these seem quite small in size.... between 10 and 20k each. In the root of the data folder there are various files...

ib_logfile0
ib_logfile1
ib_logfile0_000
ib_logfile1_000
ibdata1 ibdata1_000
WIN-8KT3MJ6T2PS.err
WIN-8KT3MJ6T2PS.pid
WIN-8KT3MJ6T2PS_000.err
WIN-8KT3MJ6T2PS_000.pid

So far I have tried installing a new clean server with 2008 R2 (the original host OS) and MySQL 5.5, then stopping the service and replacing the contents of the data directory with that of my recovered data, but when I try this, the MySQL service will no longer start, telling me The description for Event ID 100 from source MySQL cannot be found....

Is there any other way in which I can recover the databases from these files?


texts: Sub query to return same number of records and join

texts:
i have this query

SELECT DISTINCT (a.AssetID) AS [AR BarCode],au.[Asset_barcode] AS [Audit BarCode],a.AssetRegisterID, a.Description AS [Asset Description],
      a.LocationRoomID,g.[RoomNo] AS [AR RoomBC],
      h.Description AS [AR Building]
FROM dbo.AssetRegister AS a LEFT OUTER JOIN
     dbo.LocationRoom AS g ON g.LocationRoomID = a.LocationRoomID LEFT OUTERJOIN
     dbo.Audit AS au ON au.Asset_barcode = a.AssetID LEFT OUTER JOIN
     dbo.Building AS h ON h.BuildingID = g.BuildingID
ORDER BY a.AssetRegisterID ASC, g.[RoomNo] ASC

Which works and return the 29771 records i need but when i add au.[Room_barcode] i get 37633 records which i dont know what is happening.

I want to compare AssetID's from 2 tables dbo.AssetRegister and dbo.Audit. Also the RoomBarcodes. Because Assets in the dbo.AssetRegister is the base data and after the audit process assets were found in different rooms.

Please help


texts:
Environment

Two Nodes A & B

A - Site/Subnet 1

B - Site/Subnet 2

File Share - at Site 3

We have an AG listener that has two IP addresses spanning the two subnets (1 up, the other down depending who is primary)

A - hosts both the primary replica and "Core Cluster Resources"

B - hosts the secondary replica

AG Failover = Automatic Failover

Scenario

Node A is a rebooted

As A is rebooting, I am logged into Node B desktop. In node B, I have the Failover cluster manager open, and the fileshare wintess status is down. This does not make sense, maybe has something to do with Node A owning the "Core Cluster Resources"??????? I would have expected that the file share witness would still show "online"

During this time the AlwaysOn Role in Failover cluster manager is offline, and can not get started since the windows cluster is not online.

Finally after node A completes its reboot cycle, the file share witness is visbile in Failover cluster manager, and the AlwaysOn Role is online. In addition, Node A still owns the "Core Cluster Resources"

I would have expected that the reboot of Node A would have triggered the "Core Cluster Resources" to be owned by Node B (within a few minutes), and the AlwaysOn Role to be brought online by the cluster manager.

I have other alwayson environments (automatic failover, and fileshare witness) that are not multi-subnet, and a reboot of a server hosting a primary replica triggers the cluster manager to bring up the AG on the other node automatically. This behavior is not happening with in a multi subnet environment. Ideas????

My concern is losing the node hosting the primary replica, and the services not being brought up automatically at the second node (Windows and AlwaysON) in this multi-subnet configuration.

Any help would be appreciated.


texts: importing csv file with ragged right format and " text qualifier

texts: I'm importing csv files in ssis that are in a ragged right format, I've been able to create the columns perfectly fine but for some of the data rows it has included a " text qualifier into the data in the first column which makes the character count of each column out sync and pushes some characters over to the next columns, the " text qualifier is also found in the last column of the same row by itself instead of the value being blank.  These " text qualifiers are also not found in the original csv file, the flat file connection manager also does not let you use the text qualifier option when working with ragged right so i can't take them out that way.


texts:
Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)?  My testing shows that it seems to be surprisingly stupid.

I have the following query:

SELECT COUNT(userId), AVG(age),
 STDDEV(age), MIN(age), MAX(age),
 date_range_start, date_range_end
FROM users
WHERE
(date_range_start >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011'))
GROUP BY date_range_start, date_range_end;

It takes 27 seconds.
Now I remove the STDDEV, MIN and MAX aggregations the same query takes only 12 seconds.
OK, I can see STDDEV slowing things down as it requires 2 passes.
So I try AVG + MIN and MAX -- I get 21s.

How is this even possible?  How can adding calculation of min and max to the calculation of AVG slow things down by the factor of 2 almost?  Considering that out of the 12 seconds that it takes with AVG only 10 are spent on the full table scan?  So adding min/max calculation changes the group by step from 2 seconds to 10?

The explain plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     12 |00:00:24.61 |     369K|    369K|   |   |      |
|   1 |  HASH GROUP BY     |              |      1 |     12 |     12 |00:00:24.61 |     369K|    369K|   762K|   762K|   11M (0)|
|*  2 |   TABLE ACCESS FULL| USER         |      1 |     29M|     29M|00:00:09.34 |     369K|    369K|   |   |      |
-------------------------------------------------------------------------------------------------------------------------------------


texts: Partial access MongoDB databases without replication. Is that possible?

texts:
Is there any possibility to deploy a structure in case if when one or two mongod(s) in sharded cluster will be unavailable, so we'll be able to read and write partial results in\from remaining shards? But in the current case I don't want to deploy Replicaset structure. Just for the clarification, our sharded structure must be without replicasets.
Thank you.


texts:
I've been taught that in RDBMS any DDL always causes an implicit commit, ending any active transaction at the time the DDL is issued.

Now I found this page on the PostgreSQL wiki that states that PostgreSQL is able to perform DDL statements in a transaction, making them atomic and rollback-able.

But does that change anything with respect to the implicit commit about the DDL?
Is it still executed when the DDL is issued?


texts: Merge multiple rows containing different values into one row

texts:
I have a database with the structure:
Region    | OpenServicesID | DFC
K00001    | 1400           | 4
K00002    | 1300           | 3
K00001    | 1200           | 4
K00001    | 1100           | 4
K00002    | 1000           | 4

I have to present the data table in the following format:
Region | OpenServicesID | DFC | OpenServicesID | DFC | OpenServicesID|DFC
K00001 | 1400           | 4   | 1200           | 4   | 1100          | 4
K00002 | 1300           | 3   | 1000           | 4   | Null          |Null


texts: Convert a DATETIME value using string Timezone Info?

texts:
I have a column which has date, time, and timezone info like this:

Sat, 20 Aug 2016 03:50:04 CDT
Sun, 17 Sep 2017 16:51:46 CEST
Mon, 09 Oct 2017 06:43:27 CET
Thu, 28 Sep 2017 14:05:23 IST
Thu, 05 Oct 2017 08:59:01 IDT
Wed, 28 Jun 2017 17:10:22 CEST
Mon, 18 Sep 2017 18:32:52 GMT
Sat, 27 Aug 2016 07:06:22 MEST
Sat, 06 Aug 2016 13:17:09 UTC
Sat, 16 Jul 2016 23:53:02 PDT

I want to normalize these all to UTC.

I can strip off the leading 5 chars, and trailing timezone string, and they cast to datetime just fine... but my question is:

Does SQL Server have any built in function where I can pass in the datetime, the source timezone string e.g. PDT and a target timezone string e.g. UTC and it will handle the conversion?

I am thinking of something similar to what Oracle has:

FROM_TZ(YOUR_TIMESTAMP, 'UTC') AT TIME ZONE 'America/Dawson_Creek'

I am hoping to be able to do it without having to maintain a timezone offsets table to map the string values to an offset. I also don't want to hard-code the offsets in some big case-statement.

I am on sql server 2014, so I can't use the new select * from sys.time_zone_info for timezone info that comes in 2016.

Any thoughts?


texts: How do I login to SQL Server via group membership in another domain

texts:
When attempting to connect to SQL Server (2012), I get a login failed error message. The error log on the server reports:

Logon        Error: 18456, Severity: 14, State: 5.
Logon        Login failed for user 'MYDOMAIN\mswart'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.---.--]

However, I expected to be able to login based on my membership to a group MYDOMAIN\MYTEAM

I'm able to login when MYDOMAIN\mswart is created as a login.

The SQL Server machine has joined domain OTHERDOMAIN and the SQL Server service account is also a member of OTHERDOMAIN.

Maybe I'm not looking at the right thing. I'm looking for troubleshooting steps I can take to narrow down the issue so I can understand why I can't log in.

Aaron Bertrand's post Troubleshooting Error 18456 was almost useful... in the comments, it looks like a Gary Mazzone experienced the same issue.