texts:
I have 1 TB Dynamic Axapta DB on the SQL Server 2012. I have 128 gigabytes of memory.

Version : Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)   Oct 19 2012
13:38:57   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit)
on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Lock memory pages is active.

SQL Server Max memory settting : 115 GB

I am looking at the sql server on task manager and I see that the sql server is using all the memory. But looking at the Performance Counter, the server has free memory. Why sql server not using the all memory? Therefore, the highest type of wait on sql server is PAGEIOLATCH_SH

How do i fix this problem?

For the comment :

Scenario:

10:30 PM -
Database Cache Memory 30 GB -
Free Memory: 75 GB

10:40 PM -
Database Cache Memory 25 GB -
Free Memory: 80 GB

10:50 PM -
Database Cache Memory 30 GB -
Free Memory: 75 GB

I have free memory, but sql server deleted records from the cache partition. I am wrong?

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

There are a few minutes between results:

After the SP4 upgrade:


texts:
I am running Oracle 11g R1 on my local development machine.

I have been trying to run an anonymous block to test the FORALL statement in Oracle to insert 25,000 records.

I get the following error:

ORA-04031: unable to allocate 4708660 bytes of shared memory ("shared pool","DECLARE
  -- temp_rec temp%R...","PL/SQL SOURCE","PLD: Source Heap")
04031. 00000 -  "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause:    More shared memory is needed than was allocated in the shared
           pool.
*Action:   If the shared pool is out of memory, either use the
           dbms_shared_pool package to pin large packages,
           reduce your use of shared memory, or increase the amount of
           available shared memory by increasing the value of the
           INIT.ORA parameters "shared_pool_reserved_size" and
           "shared_pool_size".
           If the large pool is out of memory, increase the INIT.ORA
           parameter "large_pool_size".

I tried looking here and here but wasn't able to resolve this issue.

I have tried the following:

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET cursor_sharing = 'SIMILAR' SCOPE=BOTH;

Please help.

EDIT

The code I am using is as follows:

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team t_varchar := t_varchar();
  user_roles t_varchar := t_varchar();
  username t_varchar := t_varchar();
  sso t_varchar := t_varchar();
BEGIN
  SELECT *
  BULK COLLECT INTO biz_hierarchy, project_team, user_roles, username, sso
  FROM ... -- Query fetches 25000 records

  FORALL i IN biz_hierarchy.FIRST..biz_hierarchy.LAST
    INSERT INTO temp VALUES (biz_hierarchy(i), project_team(i),
                              user_roles(i), username(i), sso(i));

END;
/

The error returned by Oracle does not mention the line number. If I try loading 5000 records, the code block runs successfully. Fails when I try with 25000.


texts:
I have problem just like this one but in Informix.
I have table with 2 attributes ID, email like this one:

ID  email
1   my_mail1@mail.com
2   my_mail2@mail.com
2   my_mail3@mail.com
2   my_mail4@mail.com
3   my_mail5@mail.com
4   my_mail6@mail.com

I want to create a query that would return this:

ID   email
1    my_mail1@mail.com
2    my_mail2@mail.com my_mail3@mail.com my_mail4@mail.com
3    my_mail5@mail.com
4    my_mail6@mail.com


texts: How to get a sense of how busy is the server

texts:
I am not in IT infrastructure team. But I have a SQL database server. We are having this intermittent problem with Dynamics SL and everyone in the company sort of pointing the finger that the server is too busy, working too hard, ect2. I disagree with the assessment and would like to get some concrete data from the server to give to my co worker

I was thinking to capture batch request per second and presented the data.
My Question:

Do batch request per second the right approach to see how
busy/overloaded/overworked the server is?
if yes then how do I capture this?
do I have to keep SQL profiler open and save the data daily?
is there an automated way that allow me to capture some sort of metrics on how much request is hitting the server?
If batch request per second is not the right data, then what would be the right info to get? and how to get it?

thanks


texts: Speed up 180M+ rows returned by simple SELECT with PostgreSQL

texts:
I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following:

CREATE TABLE members (
    id bigserial NOT NULL,
    customer varchar(64) NOT NULL,
    community varchar(64) NOT NULL,
    member varchar(64) NOT NULL,
    has_connected bool NULL DEFAULT false,
    CONSTRAINT members_customer_community_members_key UNIQUE (customer, community, member),
    CONSTRAINT members_pkey PRIMARY KEY (id)
);

The "guilty" SELECT query is:

SELECT
    community,
    member,
    has_connected
FROM
    members
WHERE
    customer = :customer;

I have already indexed the table:

CREATE INDEX members_idx ON members USING btree (customer, community, has_connected, member);

and the query behave well for most of the customer value. However, I have a customer, let's call it 1234, which represents 80 % of the table, so the query planner prefers to scan the whole table according to the following ̀explain analyze` result:

Seq Scan on public.members  (cost=0.00..5674710.80 rows=202271234 width=55) (actual time=0.018..165612.655 rows=202279274 loops=1)
  Output: community, member, has_connected
  Filter: ((members.customer)::text = '1234'::text)
  Rows Removed by Filter: 5676072
Planning time: 0.106 ms
Execution time: 175174.714 ms

As I said earlier, my PostgreSQL is a managed instance of PostgreSQL 9.6.14 hosted on Google Cloud Platform with 10 vCPUs and 30 GB RAM. I am rather limited to the available flags , so the only PostgreSQL options tuned on this instance are:

max_connections: 1000
work_mem: 131072 KB
maintenance_work_mem: 2000000 KB

What are my options to solve this issue and to strongly reduce the query time, preferably below 30 seconds if possible ?


texts: What's the quickest way to periodically export PostgreSQL data to a local file on Windows?

texts:
I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed.

Ideally, I'd like to have the results of a few queries saved to local text files that I can then parse as needed, but if SQL dumping everything works, I'm fine with that too.

While I have experience with databases and programming, I'm not strong with either Windows or PostgreSQL.

Should I just install PostgreSQL on this Windows machine and use the command line tools that would then be found there to create a batch file? Or is there a better option?


texts: How can I query a remote server using a local function?

texts:
I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I try executing the query, I receive the error

  Msg 344, Level 16, State 1Remote function reference 'localhost\SQLDEV.db.dbo.udf_StripHTML' is not allowed, and the column name 'localhost\SQLDEV' could not be found or is ambiguous.

I tried looking for solutions, but nothing seemed remotely (haha) close to my situation as all other solutions were for executing a remote function against a local query, or trying to pass variables with OPENQUERY, etc. Is there a simple way to achieve this query with only read access to the remote, master db?

SELECT [localhost\SQLDEV].[db].[dbo].[udf_StripHTML](notes.note_text)
FROM event_log AS el
INNER JOIN dbo.progress_note as notes
on notes.event_log_id = el.event_log_id


texts:
My database backup works well or you can see the backup in the backup file. But the backup has no format of a backup file. I mean you cannot see the .bak associated with it, therefore unusable by users even though they can navigate to the backup file but unable to restore as it has no .bak.

How do I get it to show .bak, so the file is usable?


texts:
Oralce 12c  ASM
How to clean Archive logs. Oracle Instance is NOT starting since Archive disk group is full, This is my TEST DB I don't want to extend the diskgroup so instead I would like to DELETE ALL archive logs.

SQL> startup
ORACLE instance started.

Total System Global Area  738197504 bytes
Fixed Size                  2928776 bytes
Variable Size             314576760 bytes
Database Buffers          415236096 bytes
Redo Buffers                5455872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 31849
Session ID: 124 Serial number: 41214

Alert logs

> NOTE: Assigning number (1,0) to disk (/dev/DG-DISK3) SUCCESS: mounted
> group 1 (ARCH) NOTE: grp 1 disk 0: ARCH_0000 path:/dev/DG-DISK3 Wed
> Aug 24 03:50:11 2016 Unable to create archive log file '+ARCH' Wed Aug
> 24 03:50:11 2016 Errors in file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_338.trc:
> ORA-19504: failed to create file "+ARCH" ORA-17502: ksfdcre:4 Failed
> to create file +ARCH ORA-15041: diskgroup "ARCH" space exhausted ARCH:
> Error 19504 Creating archive log file to '+ARCH' Wed Aug 24 03:50:11
> 2016 Errors in file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_338.trc:
> ORA-16038: log 2 sequence# 143 cannot be archived ORA-19504: failed to
> create file "" ORA-00312: online log 2 thread 1:
> '+DATA/ASMDB1/ONLINELOG/group_2.262.917061333' USER (ospid: 338):
> terminating the instance due to error 16038 Wed Aug 24 03:50:11 2016
> System state dump requested by (instance=1, osid=338),
> summary=[abnormal instance termination]. System State dumped to trace
> file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_diag_32746_20160824035011.trc
> Wed Aug 24 03:50:11 2016 Dumping diagnostic data in
> directory=[cdmp_20160824035011], requested by (instance=1, osid=338),
> summary=[abnormal instance termination]. Wed Aug 24 03:50:12 2016
> Instance terminated by USER, pid = 338


texts: How to upgrade MySQL to a New version..?

texts:
I have installed mysql using the package management system.I am using ubuntu machine.

After that i have very well configured the server for InnoDB and other server related parameters.

I am using the version

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.49-1ubuntu8.1-log |
+-----------------------+
1 row in set (0.00 sec)

Now i want to upgrade my MySQL server to some Higher version say 5.5.
what steps should I follow to accomplish the same...?

Thanks...!


texts: Is there a way to speed up this update query using joins or any other method

texts:
I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCLN, it takes 3-4 hours.

UPDATE PCLN
SET `T1A` = ( SELECT MIN(HST.`Date`)
              FROM `HST`
              WHERE HST.`SYM`=PCLN.INS
                AND HST.`DATE` >=  PCLN.Date
                AND HST.`HP` >= PCLN.`T1`)
WHERE `BS` = 'B';

I had asked a similar question before and did see some benefits after implementing the advise that was given, however the query is still very slow and takes hours to complete. I am not very good with MySQL yet but feel that there should be some way to improve the performance of this update query.

Here is the SHOW CREATE TABLE PCLN;

CREATE TABLE `PCLN` (
`SrNo` int(6) NOT NULL AUTO_INCREMENT,
`Date` date DEFAULT NULL,
`INS` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`T1` float(8,2) DEFAULT NULL,
`T1A` date DEFAULT NULL,
......
PRIMARY KEY (`SrNo`)
) ENGINE=InnoDB AUTO_INCREMENT=16893 DEFAULT CHARSET=latin1

and SHOW CREATE TABLE HST

CREATE TABLE `HST` (
`SrNo` int(11) NOT NULL AUTO_INCREMENT,
`SYM` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL COMMENT 'YYYY-MM-DD',
`HP` float(8,2) DEFAULT NULL,
....
PRIMARY KEY (`SrNo`),
UNIQUE KEY `HST_idx_SYM_date_OP` (`SYM`,`Date`,`OP`),
UNIQUE KEY `HST_idx_SYM_date_CP` (`SYM`,`Date`,`CP`),
UNIQUE KEY `HST_idx_SYM_date_PC` (`SYM`,`Date`,`PC`),
UNIQUE KEY `HST_idx_SYM_date_LP` (`SYM`,`Date`,`LP`),
UNIQUE KEY `HST_idx_SYM_Date` (`SYM`,`Date`) USING BTREE,
UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`) USING BTREE,
KEY `HST_idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=1103583 DEFAULT CHARSET=latin1 COMMENT='HST
Data - All'

On EXPLAIN I noticed that for the index HST_idx_date, it says 1002718 Range checked for each record (index map: 0x80). What does this signify, is there a way to limit the rows so that the query is sped up. Any suggestions to speed up the query would be greatly appreciated.

Server version: 5.6.39-cll-lve - MySQL Community Server (GPL).


texts:
I want to know if there is a way to achieve the following with SQL Server 2012 Enterprise Edition?

I have Table A: on Server 1 with say 10 columns.

Table A:
| col1 | col2 | col3 | coln | col10 |

on Server 2, I need a live copy of Table A in Table B on Server 2. However I only actually need 3 of the columns replicated, as that's all I use.

Table B:
| col1 | col3 | col10 |

I mentioned "live" above, By this I mean that when I insert a new value or update a new value in Table A that change will be propagated as soon as it can to Table B.

Table B is a read only table. And Table A gets data written to it. So A will be the publisher and B the subscriber.

How can I achieve this sort of setup?  In other words do I need to use some sort of replication? or can I achieve this with Always ON High Availability.


texts:
Our SQL 2016 AlwaysOn database failed over a week ago (round about the same time as we had some high load on the system) and the secondary never came back online. For some reason my alerts were not working and today I ran DBCC SQLPERF(logspace) and realised that we were at 95% log space used. I checked the secondary database and it was in the "Reverting / In recovery" state.

The first thing I did was make sure my log backups were running correctly and that there were no open transactions. I then checked the SQL log and realised there were hundreds of the following error

  Time out occurred while waiting for buffer latch -- type 3

I struggled to find any definitive answer to what was causing this and eventually followed the advice of someone and decided to restart the secondary database.

This fixed the issue and after a while the database went from "Recovering" into "Synchronized". However, when I now run DBCC SQLPERF(logspace) it is stuck at around 5% (1.8GB) log space used and has been like that for the past few hours. I have checked and my log backups are running successfully and there are no open transactions. Even if I run it straight after a log backup it still sits at 5%.

Any suggestions on what might be causing the log file to stay at 5% used?


texts: How to Implement a Retry Feature in SSIS 2012 When a Database Connection Fails?

texts:
We have an SSIS 2012 package that supports our internal analytics reporting database by extracting data from an external database server.

Last night, for the first time, the package failed due to a database connection timeout error when attempting to connect to the external database.

It seems to me that it would make sense to enhance our SSIS package with some means of making multiple attempts to connect before failing completely.

For example, we would want the SSIS package to try 3 times, perhaps sleeping for 5 minutes before attempting a retry.  If there are 3 failures to connect, then return an error for the package.

I've searched for references on this, but I am not finding anything; it seems that it should be an obvious feature, and I must not be looking in the right places.

Here are some details:

Using SSIS 2012 packages in a local SQL Server 2012 server.
Extracting data from a remote Oracle database
Using Microsoft Connector for Oracle by Attunity
The SSIS package is called from a Scheduled Job using SQL Server Agent
The SSIS package has very simple components that consist of a truncate table step followed by a Data Flow task.
The Data Flow task uses the Oracle Data Source component connected to an OLE DB Destination (our local SQL server instance/database).
There is 1 intermediary Derived Column component, that adds some calculated data to the destination.
The package has successfully run many times in our production environment, so I already know it's not due to development/visual studio vs. SSIS catalog in SQL Server, etc.

I have looked at the Connection Manager for my external Oracle database, and I've looked at the Oracle Data Source component, but I don't see anything that looks like it would support a retry.

Should I be creating some type of retry loop using the on-error functionality?

It seems a bit like overkill, perhaps there is some property within the Oracle Source Component that I need to put in the configuration somewhere?


texts: Order the result of a comma separated string generated with STUFF

texts:
Given a schema like this:

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');

I need to generate a comma separated string ordered by Position:

'M' + Id + ' AS [' + Title + ']'

Desired result:

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]

I've tried:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

But it builds the result ordered by Id:

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

If I add ORDER BY Id to the STUFF expression:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

Produces the next error:

  Msg 145 Level 15 State 1 Line 2
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  +

I can use a subquery ordered by Position:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM (SELECT TOP 100 PERCENT Id, Position, Title FROM Foo ORDER BY Position) X
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

But I wonder if there is another way to order the result without using a subquery. There are no duplicated titles.

dbfiddle here


texts:
Let's say we have this hierarchy:

   -World
        --USA
        ---WA
        ----Seattle
        -----Downtown
        ------1st Ave
        -------945 1st ave
        ------3rd Ave
        -----South
        ----Bellevue
        ---TX
        ---MI
        --Canada
        ---BC

Now, this will not reflect real life completely, but.

At location World we have some global settings. Let's say: list of Races, list of Languages, etc. All locations underneath it can access this data.
Country level holds data about laws, currency, etc.

3.Each city can modify laws. After they modified the law, it is available to the city itself and to the regions within the city. After 3-step approval process, it can become a country level law, and all children will inherit new version of the law. Each city still can modify new version of a law to have changes.

Every City has sandboxes. This means that different organizations within city can test out editing different versions of laws without affecting City master revision.

Currently we solve this issue like this:

We have table Location with HierarchyId
Then we have table Law that has columns StateType, Location_Id, etc.
StateType can be: Master (Available to all within country), Edited (Edited for a specific location), Approved (Approved at a specific location).

Because of this we have to use Table-valued functions in our application to filter specific records that are relevant to the current location.  (Some laws can come from country level, some laws come from city level, some laws come from organization level). This makes database much slower.

We cannot use native unique constraints on our fields, because all fields must be unique only within a branch, so we have to use custom check constraints to keep data clean.

Also, insertion and editing records is much slower, since we have to promote some records to a country level, and then apply data transformation for other countries.

Currently we have around 150 tables, and we use a lot of joins.

I'm not sure what's the best way to model complex hierarchical data with versioning. (I'm thinking we need an enchanced version of source control..)


texts: SQL: How to delete Millions of Records from a Table without disturbing the server

texts:
A software of ours ran out of control and did put in millions/billions of uneccecary records in a table.
How can I delete them without disturbing the server?

This is what I have tried:
- setting transaction log to simple
- deleting them in batches in a script
- putting the virtual machine on a SSD
It still took 3 weeks before I aborted.

I don't really want to copy the data I want to keep and then delete the table.


texts: When normalising, if you have entities with the same primary key, does it mean it should be the same entity?

texts:
Crrently I have this check in data where each check in has check-in details, activities and total extra items charge(extra items are required for some activities).

In UNF, I separated them into 3 repeating groups:

CHECKIN( (customer_id, customer_name, check_in_d&t, main_tour_guide),

(activity_code, activity_name, prescribing_tour_guide, completing_tour_guide, activity_d&t,activity_cost),

(item_code, description, quantity, total_item_charge, quantity, total_extra_item_charges))

If I group total_extra_item_charges under item, I will get 3NF:

TOTALITEMCHARGES( customer_id, activity_d&t, total_extra_item_charges)

ACTIVITYSCHEDULE( customer_id, activity_d&t , activity_code, prescribing_tour_guide, completing_tour_guide)

Both of these share the same primary key, so should I group total_extra_item_charges under activity schedule instead since 1 activity only has 1 total extra charge?But something seems off?

*d&t means date and time


texts: How to select from two tables where the rows may be or may not be equal and group it if they are equal

texts:
I have two mysql tables of images ojm_pages_articles_photos, ojm_images_optimized but I want to select rows that are common to them and also rows that are NOT common to them. In case common rows are found, they should be grouped but in case they are not, the single rows should be selected. The problem in my code is that it only selects and group records that are found both in the two tables but i want records to also be selected when they are found only in table ojm_pages_articles_photos and not in the other one.

SELECT DISTINCT `ojm_pages_articles_photos`.`id_pages_articles_photos`,
                `ojm_pages_articles_photos`.`url_articles_photo`,
                `ojm_pages_articles_photos`.`id_ojm_peoples` AS `id_ojm_peoples_uploader`
FROM    `ojm_pages_articles_photos`,
        `ojm_images_optimized`
WHERE `ojm_pages_articles_photos`.`url_articles_photo` LIKE '%jpeg%'
  AND `ojm_pages_articles_photos`.`url_articles_photo`=`ojm_images_optimized`.`url_image_original`
GROUP BY    `ojm_images_optimized`.`url_image_optimized`
ORDER BY    `ojm_pages_articles_photos`.`id_pages_articles_photos` DESC,
            `ojm_images_optimized`.`optimized_is_smaller`='Oui'

I used AND  (ojm_pages_articles_photos.url_articles_photo=ojm_images_optimized.url_image_original) GROUP BY ojm_images_optimized.url_image_optimized

to select where the file is in both tables and group it but there sometimes where the files are only in table ojm_pages_articles_photos but not in the other.

How to select from those two tables where the rows may be or may not be equal and group it if they are equal ?


texts:
i am trying to simply export BACPAC file from azure db to azure blob storage
as mentioned here : https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export
but it is failed with error :

The Azure SQL Server firewall did not allow the operation to connect.
To resolve this, please select the "Allow All Azure"checkbox in the
Sql Server's configuration blade.

what's the cause of this error i am not sure, do you think i have to enable allow all azure service checkbox in azure db firewall? ( before i took backup and i was not doing this step)
also wanted to mention that both resource azure blob and azure db are in private virtual network.
also i am scared of this : https://www.fmsinc.com/microsoft-azure/sql-server/security.htm
Thanks,


texts: Tables with same schema in DB for different sources. Bad pattern?

texts:
The main purpose of the system being developed is to collect data from different sources, process and standardize it, and then add it to the DB. After information from all sources is collected it performs other work with it, like combining etc.

My design will have all the tables with exactly the same schema, just different names. Or should there be one table with additional field 'Source' for example?

Which this the right way?


texts:
I have set up a Transaction Replication, with the distribution server as subscriber itself and implemented pull subscription, to avoid load on my publisher.

When I created publisher at my source server, I got snapshot agent error saying that it cannot access the default D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL folder location. So I changed and gave a file location at Subscriber E:\ drive and it worked.

My question here is, if it is pull subscription with distribution server as subscribe:

Where should the snapshot folder be?

Because I see access issue.

Please comment if I am not clear.


texts: What is the alternative of the combination of two pattern matching conditions?

texts:
So what i want to do is to search in my table data for elements that in their name exists the word first and the word second. These two words can be seperated or attached to each others. A translation of that would be the following :

SELECT name FROM data WHERE name ILIKE '%first%' AND name ILIKE '%second%'

I searched a bit for an alternative for this query and how i can combine the condition in a regular expression but no luck.

My concern is performance problems if this query were to be performed over a large table. I already created the following index (which sped up considerably the search) :

CREATE INDEX pattern_matching_index ON data USING GIN (name gin_trgm_ops);

Is there a better way to perform this kind of search ? or is this as good as it's going to get ?


texts: How to deny connections to localhost and force to use FQDN instead

texts:
I have a server running Apache and MySQL. Connections to MySQL work fine when using localhost in the connection settings.

Now I'd like to force our developers to use a FQDN instead of localhost. This is because we're planning to move the MySQL servers from the application servers to a MySQL cluster. Then we can transparantly move things without the developers having to modify connection settings.

So I created a CNAME DNS record mysql.company.com pointing to the server that is running MySQL. Later on we can point this to the MySQL cluster.

I opened port 3306 on the server, allowed the MySQL users to connect from any host (%) and added the bind-address = mysql.company.com setting to the my.cnf (I also tried with the actual IP of the server, didn't work either). Also omitting the bind-address setting didn't work.

From the server mysql --host=mysql.company.com --user=root --password works fine.

When testing with a simple PHP script (see below) that's running on the same server, I always get a Permission denied error for the FQDN as server name. Using localhost as the server name works fine.

<?php

$servername = "mysql.company.com";
//$servername = "localhost";
$username = "tester";
$password = "my_awesome_pass";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

What am I doing wrong to achieve what I need? And how can I disable MySQL connections to localhost (maybe with the firewall?).


texts: How to get all create for index and Constraints in PostgreSQL using just SQL

texts: I want a sql to get a list of CREATE of all Index and Constraints in a table 'tablename' just using SQL.


texts: How to create a relationship between one table and multiple other entities

texts:
(PLEASE NOTE: I am aware that in a few cases there are one-to-one relationships where one-to-many should be present. It's not intentional, it's just a mistake on my side. Take no notice.)

I am designing a database for my project and I've ran into a small problem.

In the database there will be a few entities that can be in a one-to-many relationship with tables: email and phone. Here they are:

Now, I have such entities as client_rep, other_party and third_party that I want to relate one-to-many to the aforementioned tables. Since there are multiple of those, I need to create an intermediate table, in the following fashion:

That's still not that bad, but when the third party comes into play, that's what I get:

Getting nastier and nastier, right? But when I'd add more tables and more relationships, this stuff's getting, for my taste, too hideous and hard to manage.

Now, one solution I could come up with, was to add an intermediary table, like an "entity". Then, it could look something like that:

Much, much nicer. But:

Now I make each of the the entities (client_rep, other_party, third_party) dependent on entity and I'm not sure that's good.
I create a seemingly useless table containing only and ID, nothing else.
This way I can't be sure that one value of  entity isn't connected to to multiple types of other entities (e.g. client_rep.entity_id could very likely be equal to other_party.entity_id and I have no way of knowing that, except for by issuing a query, ie. from inside of my application. Still, I guess I had the same problem in the first design, where client_rep_email.email_id could be equal to other_party_email.email_id.

Another way to solve this problem would be to create entity-specific tables for phone and email.

That looks ugly at least, but this way I can be sure, that no phone number and email will be referenced by two different entities. Still, there are a few very specific and important problems with this layout:

When I want to change the length of accepted phone numbers or email addresses, I will have to edit 3 times more tables. And imagine what if added one more entity?
It's simply not right, because the same object (e.g. email address) is represented multiple times.

There is another way of doing that. Let's take a look at the client_rep table. It's obvious, that in reality it looks like this:

I could, of course, create a specific rep for each entity, like that:

This layout has other problems, like the others.

No way of being sure that client_rep.rep_id is not equal to other_party_rep.rep_id.
I have to create a entity-specific rep (ie. client_rep, other_party_rep, third_party_rep.

But there's a slight, which is that I can enter entity's rep-specific values. Still, in case of some entities (e.g. other_party), there won't ever be a rep per se. There will be only general contact information.

And I'm lost. There are to many solutions available and I can't choose the best one (although I can identify a few better than others).


texts: Does Microsoft provide paid support for SQL Express?

texts: For instance, if we run into any kind of issues with SQL Express, if we call Microsoft,  will the provide technical support?


texts:
I have a table , that has a generic set of actions for EVERY user.

However if there is a user that needs to have a unique action, that replaces an existing action,  then in my query, I want to exclude the generic action.

So for example every logged in user gets two actions OPEN or CLOSE based on the table below.

SELECT ActionID, ActionCode FROM Actions

ActionID,  ActionCode,      LoginID.
OPEN       Open contract    NULL – Generic Open Operation
CLOSE      Close Contrct    NULL  -- Generic Close Operation

A new user has a very specific OPEN case so if I add a row to the Action table

ActionID, ActionCode,    LoginID.
OPEN      Open contract   NULL  – Generic Open Operation
CLOSE     Close Contrct   NULL  - Generic Close Operation
OPEN1     Open1 contract  123   – Specific Open Operation only to user logged in as 123

So then I perform the following query

SELECT ActionID, ActionCode FROM Actions WHERE (LoginID = 123 OR LoginID is null)

I get

OPEN    Open contract   NULL
CLOSE   Close Contrct   NULL
OPEN1   Open1 contract  123

But I want

CLOSE   Close Contrct   NULL
OPEN1   Open1 contract  123


texts: Get results ordered by a specific column in a looping way

texts:
I have heavy difficulties in getting rows ordered in an special way. Two tables are part in this:

Table 1 - WORDS:

id | char | word | categoryid (<- id of a row in CATEGORY)

Table 2 - CATEGORY:

id | name

Now I like to get a list of 80 WORDS (LIMIT 0,80) randomly ordered by the categoryid. BUT before there is any new chosen row with an already chosen categoryid all other categoryids should have chosen already. It is not easy to explain, see the following example:

Possible result (CATEGORY has 5 rows with 1 < id <= 5):

id    |  char    | word    | categoryid  | name
3        f         fire      3             nouns
6        n         nice      5             adjectives
2        t         three     4             numbers
4        e         europe    1             places
5        s         spanish   2             languages
1        m         man       3             nouns
7        b         bad       5             adjectives
9        t         two       4             numbers
8        a         alaska    1             places
10       t         turkish   2             languages
...

As you can see the order of the categoryid column should loop.

The used words should be randomly chosen and therefore the list should always display different words ordered always differently but in this kind of way.

This task is way to hard for me, I cannot do it in MySQL only, but i really would like to. So if there is anyone genius, please give me advises ;-)


texts: Error 9003 Log Scan is Invalid Error When Restoring Transaction Log After SQL2016 SP2 + CU8 Upgrade

texts:
We are using SQL 2016 Enterprise Edition and have recently applied SP2 + CU8.  We were previously on SP1 + CU6.  Since the update we are getting the following error when restoring transaction logs on our DR server.

  The log scan number (20913:512:1) passed to log scan in database '' is
  not valid. This error may indicate data corruption or that the log
  file (.ldf) does not match the data file (.mdf). If this error
  occurred during replication, re-create the publication. Otherwise,
  restore from backup if the problem results in a failure during
  startup.

The log restore succeeds if NORECOVERY is used and then later logs are applied successfully with STANDBY.  But if the file that causes the error is applied with STANDBY future log restores fail when using either NORECOVERY or STANDBY.  The only fix we have found is to do a full restore.

Is there any other workaround other than a full restore?

The frequency of the error is 2 times a day on 3 different databases.  The log backups originated from an AlwaysOn secondary server.


texts: Maintenance Plan Wizard generates invalid script?

texts:
I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:

  Executing the query "EXECUTE master.dbo.xp_delete_file
  0,N'',N'',N'2016..." failed with the following error: "Error executing
  extended stored procedure: Invalid Parameter". Possible failure
  reasons: Problems with the query, "ResultSet" property not set
  correctly, parameters not set correctly, or connection not established
  correctly.

Generated T-SQL is this (according to Log File Viewer):

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

GO

Please note all single quotes are doubled.

Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?

Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:

EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

GO


texts: Count the number of occurrences of certain values of TagName column

texts:
I want to count the number of occurrences of different sensor rows in SQL, but I seem to be doing it wrong and seemingly I am not visualizing it correctly.
If I was doing this in pseudocode in a C style language, I would do it like this:
FOR i in range(taglist[i]):

    print(taglist[i], count(taglist[i]) )

I have been trying this:
SELECT COUNT(*)
FROM   (
           SELECT DISTINCT [TagName]
           FROM   [A2ALMDB].[dbo].[AlarmMaster]
           WHERE  (TagName LIKE '%Sensor%' OR GroupName LIKE'%Sensors%')
       ) a

It returns 66, but I want it to return the count of each of the distinct tagnames that are returned in SELECT ... FROM (...)a.
Can anyone help me with how I should be trying to get all the counts of my different sensor occurrences to total instead of a count of all the distinct tagnames?


texts: Error when using views on a linked server

texts:
I have this query:
SELECT
    -
FROM -.dbo.tb1
WHERE
    -IN
    (
        SELECT
            CASE
                WHEN EXISTS
                (
                    SELECT *
                    FROM -.dbo.tb2U1 WITH (NOLOCK)
                    WHERE col1 = '8614'
                )
                THEN
                (
                    SELECT TOP (1)
                        CASE WHEN ISNULL(col2,0) IN (1,2,3,6,8)
                            THEN '2'
                            ELSE '1'
                        END AS col3
                    FROM -.dbo.tb1U1 WITH (NOLOCK)
                    LEFT join -.dbo.tb3U2 WITH (NOLOCK)
                        ON U1.-= U2.-
                    WHERE
                        -= '8614'
                    ORDER by
                        -DESC
                )
                ELSE '3'
            END
    );

When I run it on -, it's ok. It returns me what I want:
col2
-------------------------------------------------- Value

But if I run it on - (we have the same database -, but with no tables, only views redirecting to - with the same names of the tables) it shows me this error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared.
Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a
context where a condition is expected, near 'THEN'.

The views to the linked server are of the form:
SELECT *
FROM -.-.dbo.tb2

If I write the query without the SELECT here:
...Where -in ( ***NO SELECT HERE*** Case When Exists
(Select * from -.dbo...

...it runs perfectly.
I just want to know why this happens. How could this be possible? It's the same query.


texts: Azure Database Link to another Azure database

texts:
How to query between two databases with the same server in SQL Database?

Currently we are aiming to link two azure databases that will update simultaneously.

Thanks!


texts: SQL Server 2012 Explicit transactions taking time with Always on Setup

texts:
We have a weird issue while we setup the AlwaysOn on Azure VM. We have explicit transactions in our stored procedures, and which are causing some delays and blocking when till the time we get the response from Secondary Server. We are in Synchronous commit mode of AlwaysOn to achieve High-Availability. But it seems we are seeing issues there as when we do the explicit commit the transactions are taking lot of time to complete and causing blockings. The transaction generaly gets stuck at "HADR_SYNC_COMMIT" wait type.

When we removed the explicit transactions from the stored procedures, the execution of the stored procedures was pretty faster and there were no blockings post that.

Anyone aware of any specifics about behaviour of AlwaysOn on Azure VM

Thank you.


texts: Problem remotely connecting to MySQL database

texts:
Good afternoon everyone.

I have 3 servers on 2 different networks, all of which run MySQL.

I can use mysql -u<user> -p -h<ip-addr> from server 1 to server 2 and back without a problem.

When I try to use that command from server 3 (which is on a differnet network) to server 1 I get the following:-

$ mysql -u<user> -p -h<hostIP>
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '<hostIP>' (146)
$

I know the same command works from server 2 to server 1 with the same password. My initial thought was that there was a network block between server 3 and server 1 but if that were the case then server 3 would not get a password prompt.

I have also looked at the users table in server 1 and the user has the same password for local connections and network connections (which is why it works from server 2 to server 1).

Is there another port other than 3306 that is used as part of the connection after that password prompt? Or, failing that, what am I missing?

Many thanks,

David


texts:
I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post.

What I don't understand is what context these sort of terms are used in. I'm thinking mainly only in DBAL or talking about design. In any case, when one thing "hasMany" of another how and where does it show up?


texts:
As an attempt to answer Difference data between adjacent rows

UPDATE Visits R
SET Duration = TIMEDIFF(
        ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID LIMIT 1),
        R.ReqTime
    )
WHERE R.Duration IS NULL

leads to error #1093 - You can't specify target table 'R' for update in FROM clause.

What to do?

MySQL 5. Here is the Visits table:

CREATE TABLE `Visits` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Session` int(10) unsigned NOT NULL COMMENT 'VisitsSessions',
  `EventKind` enum('search','subsectionList','show','seance','like','superprice','toursList','abonementsList','abonement','popular','sales','tab','mainpage','other') NOT NULL COMMENT 'Also ''purchase'' in Bravo::Visits::Query',
  `ReqTime` datetime NOT NULL,
  `Duration` int(11) DEFAULT NULL COMMENT 'считается ПОТОМ, не при INSERT',
  PRIMARY KEY (`ID`),
  KEY `User` (`Session`),
  KEY `EventKind` (`EventKind`),
  KEY `LoadedTime` (`ReqTime`),
  KEY `Duration` (`Duration`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 COMMENT='статистика для каждого отдельного юзера';


texts: How to convert strings extracted from JSON to a numeric type for aggregates?

texts:
I am struggling to extract data from this JSON object in PostgreSQL:
"temperature": {
"boiler_temp": {
  "values": [
    "1",
    "2",
    "3",
    "3",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "6",
    "6",
    "6",
    "4",
    "7",
    "7",
    "7",
    "7",
    "4",
    null,
    "4",
    "5",
    "5",
    "5",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "2",
    "2"
  ]
}
}

I am using jsonb_array_elements() to extract it but they are returned as a string like '1', '2' and so on and so forth. My goal is to try and find the "avg": 5,"max": 7,"min": 1 for this data set however because I am returning them as string I am getting a value of null and an error:
My current code:
(select MIN(x::text::int) from jsonb_array_elements((data#>>'{temperature,boiler_temp}')::jsonb->'values') AS x) as mininum_temp

And I am getting an error:

ERROR: Invalid Syntax for integer :""1""


texts: Is it possible to define a foreign key with duplication and cascading delete only if no values exist?

texts:
I have the following scenario:

A survey table with the columns survey_type and survey_version.
(survey_type, survey_version) is a primary key of the survey table.

I also have a request table. It contains requestIDs that are associated with a survey_type. Whenever a user initiates a survey for a certain requestID, they will get the most up to date survey_version.

I wish to enforce the restriction that allows creation of requests only for survey_types that actually exist. I also wish for requests to be deleted if the associated request_type is removed from the system. A FOREIGN KEY with ON DELETE CASCADE is almost a perfect solution, except that I need the cascading to happen only if all records with a certain survey_type are removed. If we are only removing an old and no longer used version of a survey_type, but records of that survey_type still exist in the survey table, then the survey request should also remain in the system.

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?


texts: How to create rolling backups for SQL Server 2008 R2

texts: I want to run daily backups for a set of databases to disk such that I always have 3 days’ worth of backups kept for each database, with the oldest backup being deleted or overwritten each day. I set up a maintenance plan and set the expiry for the backup set to 3 days. The system uses a timestamp for each file (which I like) but older files are not removed and just build up in the directory, so I'm not sure what expiry means. Is there a way to achieve what I am looking for without having to write re a separate script to clean up the old backups?


texts: Inclusion of ORDER BY on query that returns no rows drastically affects performance

texts:
Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant when ORDER BY not included. Why?

SELECT *
FROM tinytable t                          /* one narrow row */
JOIN smalltable s on t.id=s.tinyId        /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3                       /* doesn't match */
ORDER BY b.CreatedUtc          /* try with and without this ORDER BY */

I understand that I could have an index on bigtable.smallGuidId, but, I believe that would actually make it worse in this case.

Here's script to create/populate the tables for test. Curiously, it seems to matter that smalltable has an nvarchar(max) field. It also seems to matter that I'm joining on the bigtable with a guid (which I guess makes it want to use hash matching).

CREATE TABLE tinytable
  (
     id        INT PRIMARY KEY IDENTITY(1, 1),
     foreignId INT NOT NULL
  )

CREATE TABLE smalltable
  (
     id     INT PRIMARY KEY IDENTITY(1, 1),
     GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     tinyId INT NOT NULL,
     Magic  NVARCHAR(max) NOT NULL DEFAULT ''
  )

CREATE TABLE bigtable
  (
     id          INT PRIMARY KEY IDENTITY(1, 1),
     CreatedUtc  DATETIME NOT NULL DEFAULT GETUTCDATE(),
     smallGuidId UNIQUEIDENTIFIER NOT NULL
  )

INSERT tinytable
       (foreignId)
VALUES(7)

INSERT smalltable
       (tinyId)
VALUES(1)

-- make a million rows
DECLARE @i INT;

SET @i=20;

INSERT bigtable
       (smallGuidId)
SELECT GuidId
FROM   smalltable;

WHILE @i > 0
  BEGIN
      INSERT bigtable
             (smallGuidId)
      SELECT smallGuidId
      FROM   bigtable;

      SET @i=@i - 1;
  END

I've tested on SQL 2005, 2008 and 2008R2 with same results.


texts: Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

texts:
In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000).

Offset of -07:52:58?

Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, so read carefully.

SET TIME ZONE 'America/Los_Angeles' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z') ;

("2015-01-01 00:00:00-08","0001-12-31 16:07:02-07:52:58 BC","0001-01-01 00:00:00")

I am surprised by that second value: 0001-12-31 16:07:02-07:52:58 BC. I understand that we must go backwards eight hours as America/Los_Angeles is eight hours behind UTC with an offset of -08:00. But instead of -08:00 the offset is -07:52:58. Why?

No Problem Under UTC

No such issue when entering data under UTC.

SET TIME ZONE 'UTC' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z');

("2015-01-01 00:00:00+00","0001-01-01 00:00:00+00","0001-01-01 00:00:00")

No Year Zero

By the way, the date portion seems to be correct. It seems there is no year 0000, that being the pivot point between the “BC” and “AD” eras. Take the first moment of year 0001, subtract an hour, and you get the year 0001 BC – so no year zero.

SET TIME ZONE 'UTC' ;

INSERT INTO moment_  -- TIMESTAMP WITH TIME ZONE.
VALUES ( TIMESTAMP '0001-01-01 00:00:00.0Z' - INTERVAL '1 hour' ) ;

SET TIME ZONE 'UTC' ;

TABLE moment_ ;

The result is the year 0001 BC, so we jump from 0001 to 0001 BC; no year zero 0000.

"0001-12-31 23:00:00+00 BC"


texts: Is my database secure? / What does "=Tc/postgres" allow?

texts:
Given the following...

iproductiondb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                 +
               |          |          |             |             | postgres=CTc/postgres        +
               |          |          |             |             | iproduction=CTc/postgres

Are the postgres and iproduction roles the only roles that have any type of access to iproductiondb ?

=Tc/postgres is making me wonder if I need to fix something?


texts: Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point?

texts:
I am designing a database for an e-commerce company. Each product will be identified by a unique ID (SKU). We want to format the SKU as LLLL000. LLLL is the parent product, for example Iphone X.
000 is the variation product, for example color: Gold, storage: 256, status: Open Box.

Following the example, the SKU for Iphone X, color: Gold, storage: 256, status: Open Box, would be something like: LKGH177
A different Iphone X would have: LKGH178.

My questions are:

Should I consider the entire SKU as a single-column primary key that contains two distinct data points, and so violating 1NF?
Should I consider the SKU split in two columns and set a two-column composite primary key?

Database is not my main field so I hope it is clear.


texts: Storing raw results in a relational database

texts:
I have used a star-schema to build a manufacturing database. I'm receiving results of tests (eg. Temperature: 273 K) and I'm storing them in a fact table (transactions).
The problem is, some machines produce at once a JSON string with hundreds of results, and I'm only interested in a few of them. I can of course extract these few results and save them in my fact table, but I'm still interested in saving the entire JSON string somehow (just in case...).
What is the most efficient way to do that? It seems like modelling the JSONs table as a dim table (and relating it to the fact table) would be the way to go. But there will be thousands of rows in that table. It feels strange to have a dim with this many rows.
Keep in mind that the relationship between the transactions-fact table and the JSONs table would be one-to-many (one JSON can correspond to many transactions (singular results) but a single transaction can only correspond to a single JSON).
Another question is: is it efficient to store in the same table the raw data (JSONS) produced by different machines?. Or should I create separate tables for every machine?


texts: How to Get the Sum (total) of a column in a query result set

texts:
Is there a way that I can get the sum of a column in my query result set by just clicking on the column (something like in Excel) so that I don't have to copy and paste the column into Excel to the see the total sum of all the values in the column?

I'm running SQL Server 2008.


texts:
I need to do a table join to find missing translations in a translation system.

Let's say we have the following simplified table (id is the identifier of the string, the actual translation is not included in the example for simplicity's sake):

+------+-------+
| lang | id    |
+------+-------+
| en   | hello |
| en   | world |
| fr   | hello |
| de   | world |
+------+-------+

My initial thought was to do a simple left join:

select a.*, b.* from trans a left join trans b using (id) where a.lang = 'en';

I thought I would get this (so I could add where b.id is null to find the missing translation):

+------+-------+------+-------+
| lang | id    | lang | id    |
+------+-------+------+-------+
| en   | hello | en   | hello |
| en   | world | en   | world |
| en   | hello | fr   | hello |
| en   | world | NULL | NULL  |
| en   | hello | NULL | NULL  |
| en   | world | de   | NULL  |
+------+-------+------+-------+

What I actually get is this:

+------+-------+------+-------+
| lang | id    | lang | id    |
+------+-------+------+-------+
| en   | hello | en   | hello |
| en   | world | en   | world |
| en   | hello | fr   | hello |
| en   | world | de   | world |
+------+-------+------+-------+

What mistake have I made in my way of thinking, and what is the correct query to get the desired output?


texts: SQL Server 2008 R2 DB stuck on restoring

texts:
I've disabled the witness server and now side 1 is stuck in restoring state and I am unable to get it out of that state. I've tried to restore the DB and now I'm getting the following error. Any suggestions? I've made a backup of the side 2 db and before I just do a restore with that I wanted to see if any of you have come across a more graceful fix.

Thanks!

RESTORE DATABASE DBNAME WITH RECOVERY;

Error message:

  Msg 4303, Level 16, State 1, Line 1
  The roll forward start point is now at log sequence number (LSN) ###700006. Additional roll forward past LSN ###900001 is required to complete the restore sequence.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.


texts: Get the 1st record and last record of a day in PostgreSQL


texts: Is it possible to run SSIS 2012 and SSIS 2008 packages on the same server at the same time?


texts: MongoDB duplicate "_id " for an array "_id"

texts:
Consider db "test" in my standalone mongodb instance.
I created a collection called testCol and inserted a document with the following syntax :

db.testCol.insert({_id:{"age":22,"empId":1},"DOJ":"22JUL"})

which results in :

WriteResult({ "nInserted" : 1 })

and upon quering

db.testCol.find()

I get

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }

However upon inserting another document with the same _id with different positioning of the array elements with syntax :

db.testCol.insert({_id:{"empId":1,"age":22},"DOJ":"22JUL"})

which results in :

WriteResult({ "nInserted" : 1 })

and upon quering the collection using

db.testCol.find()

I get

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }
{ "_id" : { "empId" : 1, "age" : 22 }, "DOJ" : "22JUL" }

Is this a bug because to query the document with the unique _id using the syntax :

db.testCol.find({"_id.age":22,"_id.empId":1})

will return

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }
{ "_id" : { "empId" : 1, "age" : 22 }, "DOJ" : "22JUL" }

Why is this happening ? If mongodb follows a specific _id structure then shouldn't it return only 1 document from the final find command and if mongodb doesn't follow a specific structure then how does it let me insert multiple documents


texts: ms/write on tempdb data files are dead slow

texts:
I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb data files to separate drive on the HP StoreVirtual 4530 40TB SAS Storage SAN we have. The SQL Server we have is Standard 2008R2. hosted on 8 cores 80GB memory virtual machine. I would like to note that the top ten waits I have on that instance are:

CXPACKET avg:0.0011sec
WRITELOG avg:0.0149sec
PAGEIOLATCH_SH avg: 0.0080sec
IO_COMPLETION avg: 0.0103sec
PREEMPTIVE_OS_PIPEOPS avg: 5.1728sec
ASYNC_IO_COMPLETION avg: 32.6180sec
BACKUPBUFFER avg: 0.0144sec
BACKUPIO avg: 0.0113sec
ASYNC_NETWORK_IO avg: 0.0007sec
PAGEIOLATCH_EX avg: 0.0291sec

Most of the other databases data files average around 30ms per write (some of them reaches more than 100ms.

What to do? what to search?


texts:
I am tasked with implementing auditing of data changes on our database.  I am aware that there are commercial products for this, but I wanted to know which design would be better.  For any given row we will want to know what values were changed, who changed it, and when the change took place.

We have Enterprise Level data (possibly millions of rows), and we are using MS SQL Server Web (ver.13).

A suggested design used histid as the unique primary key on every table; What would normally be the unique primary key would be repeated (once for each version of the row):

-- primary key is histid.
-- the current programAssignmentId is what I am  typically searching for.

SELECT
    [histid] -- column is primary key.
    ,[isCurrent]
    ,[lastupdate] -- column is indexed.
    ,[programAssignmentId] -- column is indexed.
    ,[programid] -- foreign key, column is indexed.
    ,[entityid]  -- foreign key, column is indexed.
    ,[lastUpdatedById] -- foreign key, column is indexed.
    ,[effectiveDate]
    ,[orderno]
    ,[status]
    ,[deleted]
    ,[created]
FROM [dbo].[mem_programAssignments]
WHERE [programAssignmentId] = 'a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2'
ORDER BY [programAssignmentId], [lastupdate] DESC

Would yield the following results:

histid                                isCurrent   lastupdate                programAssignmentId                    programid
===================================   =========   =======================   ====================================   ====================================
7330075F-B076-113D-B548CDDD0BF5D4B8   1           2017-08-17 13:37:56.950   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
73291EC6-F168-E817-EB42EB46A5AFB08C   0           2017-08-17 13:37:11.670   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
56F73DE0-0137-4CBF-969AEDFE55229E3B   0           2017-04-24 18:49:11.000   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661

A competing design suggested one or more history tables that would record the changes.

SELECT
   [id]
    ,[reference_class_name]
    ,[reference_table]
    ,[reference_id]
    ,[column_name]
    ,[old_value]
    ,[new_value]
    ,[created_at]
    ,[user_id]
    ,[user_name]
    ,[message]
FROM [dbo].[history]

Would output something like:

id   reference_class_name         reference_table          reference_id                            column_name   old_value    new_value
==   =========================    ======================   =====================================   ===========   ==========   ===========
24   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    startDate     2017-01-01   2018-07-11
25   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    deductible    15000000     30000000

The reference_id in this case would be referencing the id value of the primary key of the table listed in the reference_table column (mem_programAssignments in this case).

The advantage to the second design is it would separate the historical values from the current values, but the obvious drawback would be that the history table(s) could get extremely large.

Is one design better than the other -- or I should say, what are the advantages, disadvantages, or risks with these designs?  Are there other design patterns I should consider?


texts: Incremental backups using MySQL Workbench

texts: I need to start doing regular backups of my databases, and I'd like them to be incremental; that is, only backing up the tables that have been changed. I use MySQL Workbench, and I've used the export function in the past, which works well enough, but I can't seem to find a way to make it incremental and not re-backup the entirety of every database every time. I've got about 90 schemas in total. Any help at all is much appreciated. Thanks!


texts: What is the difference between these 2 queries?

texts:
We have these 2 queries:

select jsh.jobid, max(jsh.Audit_CreatedDate)
from JobStatusHistories jsh
join Jobs j on j.JobId = jsh.JobId
where j.JobStatusId = 11
and jsh.Audit_CreatedBy <> 'Archiver'
and jsh.Audit_CreatedDate < '5/9/2015'
group by jsh.jobid

and

select jsh.jobid
from JobStatusHistories jsh
join Jobs j on j.JobId = jsh.JobId
where j.JobStatusId = 11
and jsh.Audit_CreatedBy <> 'Archiver'
group by jsh.jobid
having MAX(jsh.Audit_CreatedDate) < '5/9/2015'

The first returns 5.3 million records and the second returns 4.3 million.

The goal is to get a list of jobids where the job status is 11 and has been modified over 5 months ago (old) but they may have been modified recently by the Archiver, so we want to ignore those.

Simplified data might look like:

JobId    Audit_CreatedDate    Audit_CreatedBy
234      3/25/2015            Importer
234      9/25/2015            Archiver
456      8/25/2015            Importer
456      9/25/2015            Archiver
789      3/25/2015            Importer
789      4/25/2015            Archiver

We would expect to get back:

JobId    Audit_CreatedDate
234      3/25/2015
789      3/25/2015

Unfortunately, both queries return so many results, it's difficult to see which is doing what we want.


texts: how to update column on same row with trigger without recursion?

texts:
SQL Server 2012.

I need to update column [LastUpdated] with the current date and time whenever a record changes in my table.  Currently I have:

CREATE TRIGGER Trig_LastUpdated ON Contact AFTER UPDATE
AS
SET NOCOUNT ON

UPDATE ct
SET LastUpdated = GETDATE()
FROM Contact ct
INNER JOIN Inserted i
    ON ct.IDContact = i.IDContact

But this is recursive and I don't want that, causes deadlocks and other weirdness.  I cannot turn off recursive triggers globally.  I see that INSTEAD OF triggers are non-recursive, but if I do that do I have to check every other column in the Inserted to see if it got updated or will SQL Server handle that for me?  What's the best way to do this?


texts: Backup and truncate LDF with xp_sqlmaint

texts:
A job is backing up an LDF with the following command:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID <the id> -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog "\\server\sqlbak$" -DelBkUps 6HOURS -BkExt "TRN"'

I would like the job to truncate the LDF after backing it up.

I have looked at the xp_sqlmaint documentation and it seems that the flag -RmUnusedSpace may do the trick, but I'm not sure as it seems to only remove unused space rather than truncating.

I read that using dbcc shrinkfile('logfile' , destination size) can break the backup chain, as you cannot then backup to a specific point in time.

How can I go about truncating the LDF after the backup?


texts: Does MySQL reserve memory for MEMORY tables based on max_heap_table_size

texts:
Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater than 12GB.

If I were to assign all tables (approx 19x) to have a max_heap_table_size = 12*1024*1024*1024; -- 12GB, then would that then result in MySQL attempting to reserve 228GB of memory, or would it instead allow this however then just run into issues if the tables collectively grew too big?

To rephrase, does MySQL only reserve memory for MEMORY tables as and when it's needed, with max_heap_table_size just used as a throttle on that individual table size if it were to grow too big?


texts: FLUSH LOGS command introduces a long sleep

texts:
I am using Percona mysql 5.0-92-log on Centos 6.6 final.

I have logged in as a privileged user to mysql. If I issue command

mysql> FLUSH LOGS;

The terminal goes for a long sleep

Below is the output of processlist

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  9 | root | localhost | NULL | Sleep   |  393 |       | NULL             |
| 10 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

If I kill the process below is the error I get

mysql> FLUSH LOGS;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Same issue I face while trying to take a mysqldump by issuing command

/usr/bin/mysqldump --user=root --password='****' --host=localhost --verbose --master-data=2 --flush-logs test > $DUMPFILE
-- Connecting to localhost...

And than it hangs.

If I don't use --flush-logs option I get the dump generated properly.

Any pointers as to why it happens?


texts: Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, ...?

texts:
We have items of three types to be stored and queried according to some business logic.

create table a_table
(
   item_a   varchar2(30),
   item_b   varchar2(16),
   item_c   varchar2(2),
   -- other columns
);

And the indexes

create unique index idx_1 on a_table (item_a);
create unique index idx_2 on a_table (item_b);
create unique index idx_3 on a_table (item_c);

Data for a specific item of a specific type would be read simply as:

-- reading item_a
select ... from a_table where item_a = '...';

-- reading item_b
select ... from a_table where item_b = '...';

-- ...

If an item is of type a, it can't be of type b, so each row must have only one of item_a, item_b or item_c with a value, the others must be null. (This can be enforced in any ways, it does not matter if it impacts insertions.)

In theory the number of type of items could grow (maybe a fourth item type could be added somewhere in the future).

This fact pushed towards another solution which also avoid the ugly mutually exclusive (unchecked in the given example) columns:

create table a_table
(
   item       varchar2(30) not null,
   item_type  varchar2(10) not null,
   -- other columns
);

create unique index idx_1 on a_table (item_type, item);

-- reading item_X
select ... from a_table where item='...' and item_type='item_X';

Disregarding the obvious disadvantage of the first solution when the number of item types increases and other possible design flaws, is there any important performance difference between accessing (select of a row using indexed columns) a table as done in the first case and accessing a table as done in the second one?

The cardinality of item_a and item_b can be millions, the cardinality of item_c is surely under 200.

Notes

Assume that there will be an equal number of selects for item_a, item_b and item_c
In the first solution, consider a composite index (item_c, item_a, item_b) iff it would be better (performance-wise) than three separate indexes, but do not forget that the comparison must be done with the second solution where both indexed columns will be used in the where condition: assume the use of the best “correct” index/indexes for both cases.


texts: How to find the date a login password was last changed in SQL Server 2008?

texts:
I'm porting stored procedures from Sybase 15 to SQL Server 2008.

In Sybase this statement calcs an expiration date by adding a number of days to syslogins.pwdate (Date the password was last changed) and works fine:

SELECT @l_pwd_date = dateadd( day, @l_pwd_max_expire, pwdate)
FROM master.dbo.syslogins
WHERE name = @v_user

...but throws this error when trying to compile it in SQL Server 2008:

  Msg 207, Level 16, State 1, Line 21 Invalid column name 'pwdate'.

syslogins in SQL Server 2008 no longer includes that column pwdate.

Does anyone know where could I find equivalent column in SQL Server 2008 or a workaround to find the date a login password was last changed?


texts: SQL Server Grant CRUD to Users on One Schema

texts:
I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside that schema, and I also do not want them to have create/drop permissions for the schema itself. What is the least permissions to grant this type of access?

Currently, I set up the schema training with dbo as the owner. Then I created a role called training_modify and added the training schema as a securable with "Select", "Update", "Insert", and "Delete" explicitly granted. Finally, I will add users to that role.

Is this the right approach? Does it have any unintended consequences?


texts: Mixture of byte and character semantics in Oracle DB

texts:
Are there any issues having a mixture of byte and character semantics in a table?

E.g. Location table:

 location_type varchar2(3 byte)
 location_name varchar2(50 char)

How about a schema having a mixture of tables with byte and character semantics?

Any implication in terms of performance?


texts: I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

texts:
I would like to say that as on daily basis i got the message in my log file.

First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like

  I/O is frozen on Database (DatabaseName).No User action is required.
  However, if I/O is not resumed promptly, you could cancel the backup.

And then I/O was resumed on Database(DatabaseName) message appears like

  I/O was resumed on Database (DatabaseName). No user action is
  required.

And finally the backup has done of Database with successfully with like this message

  No User Action is Required

It is My production Audit database and having the software Environment is like:

Windows Server 2012 R2
SQL Server 2012
VMware Tools

My production Database "Recovery Model" is "Full" recovery model. And from which login ID in SQL Server, the Maintenance Plan Backup was setup.That has "sysadmin" privileges for all databases.

The Backup has setup through "Maintenance Plan" Backup at specific time schedule like 4.00 P.M. at every day. And Always Backup went through successfully at their schedule interval.

Why such kind of log message generate in at schedule backup time.
Is this kind of message will impact to my Production Database performance.
Will be any issues in (Backup/Restore) data consistency in my production database.

Any suggestion will be appreciated.


texts: Restoring Postgres from only base backup taken with wal-e

texts:
I have a postgres base backup taken via wal-e. Is it possible to restore and start the DB with just the base backup? I cannot use the restore_command in recovery.conf since the wal files are archived to deep glacier.

After copying the base backup with wal-e backup-fetch command and starting the DB, im getting the following logs:

2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  database system was interrupted; last known up at 2019-10-31 22:13:32 UTC
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  creating missing WAL directory "pg_xlog/archive_status"
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  invalid checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= FATAL:  could not locate required checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  startup process (PID 7417) exited with exit code 1
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  aborting startup due to startup process failure
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  database system is shut down

Is it possible to start the DB and bring it to consistent state with only the base backup?


texts: How to GRANT or DENY access to an object based on metadata for that object in SQL Server?

texts:
We are in the process of reviewing our database permissions among various applications and business groups on SQL Server in a Windows Active Directory domain.
We currently do not use separate schemas for access control, and permissions are assigned at the database level via roles and windows groups. We have web applications that access the databases, and internal users who access the databases directly. Everything runs on at least SQL Server 2017 Enterprise. Using schemas may be a future practice, but should be outside the scope of recommendations for this question.
To start this process we classified all database tables based on several parameters. Such as the type of data it contains and it's use within our applications. These properties were mapped to user roles. We "tagged" the databases with this extra metadata using extended properties so that we can audit our systems. So, for instance, we might flag a table as containing PHI, and being used for SYSTEM or REPORTING. Finally, "Group1" may not be able to access any tables with PHI. Or, "Group2" may have SELECT only access to SYSTEM tables.
Now, we would like to implement a method of programmatically granting and restricting access to these tables based on this metadata. That way, when a developer adds a new table, they can set the appropriate metadata, and more granular permissions will be applied without them having intimate knowledge of our roles / groups / permissions as defined by the business and admin teams.
Now, I know there are other ways to accomplish things like this. But, this is one method that makes the most sense to us right now based on development requirements and resources. i.e. Achieve the best security value with as little application re-work as possible from the development team.

Can you provide any guidance on how to do this?
Any feature within SQL server that might allow us to do this in a native way?
Or, is my only option to periodically run a job that reads the metadata and updates permissions?
Do you foresee any problems with this method?


texts: Sql Server - Best Practices for Growing Database Files

texts:
I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.

The DB files auto growth is set to 5MB and I would like to try a different approach, so I am looking for suggestions and or comments.

There is a tuning task that runs every-week on Sunday night at 1:30 AM. The task will:

Check Database Integrity
Shrink the Log File – (This is ok because logging mode is Simple)
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History

I would like to add two more steps to the weekly tuning plan:

Grow the database file by 500 MB if the used space reaches a certain threshold or total size.
Grow the log file by 250 MB (after the shrink) if the used space reaches a certain threshold of total size.

By placing the growth burden in offline hours, I hope to gain performance by reducing the number of auto-growth events during heavy loads.

I have two questions relating to auto growing files.

The best place to put the file grow steps would be prior to the current steps or after?
If I use the ALTER DATABASE|MODIFY FILE to grow the file then how can I determine if SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?


texts: Mysql datadir and binlogs: same partition or not?

texts:
On a MySQL cluster, does it make sense to store MySQL database files and binlogs in different partitions?

(This is about partitions on the same physical disk. If they were on different physical disks, the answer would be yes -- so to allow parallel reads and writes.)

One could think that by separating datadir and binlog dir on different partitions, if one gets filled up the other continues to have free space. But actually this doesn't matter as any of these two events would stop the replication or the MySQL server altogether. On the other hand, a single partition for datadir and binlogs would allow them to share the available free space and therefore give them more room for expansion.

For this reason it doesn't seem there's any particular drawback in sharing a partition, while there may be some advantages.  Is there something else to take into consideration?


texts: can we create number of fields at a time with same data type in mysql?

texts:
alter table students
 add s1,s2,s3,s4,s5,s6,s7 int;

ERROR 1064 (42000): 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 's2,s3,s4,s5,s6,s7 int' at line 2


texts: Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server

texts:
How do I correctly create a linked-server entry, from an on-prem SQL Server, to an AWS RDS SQL Server instance?  On-prem is 2008 R2, AWS-RDS is running 2012.

This is the error I get when attempting to SELECT from a table in the linked-server:

  OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK" returned message
  "Unable to complete login process due to delay in opening server connection".
  Msg 7303, Level 16, State 1, Line 30
  Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK".

I have tried following instructions here and people in the thread say they've got it working (from on-prem to AWS, not the reverse, which I don't care about), but nobody specified exactly how they did it or what work-arounds or gotchas they encountered.  Another article says the same thing -- they've "had success LINKING TO RDS" but they fail to specify how.


texts:
I have a table with records logs.
Every few minutes  I delete old logs by Service.
Each deletion can have almost 10,000 records.
The question is whether to delete all the logs with delete top, a few logs in loop or deleting them all at once will be more effective?
I have indexes and there is no cascade on the tables.


texts:
I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship.
Say I have an entity MACHINE, and I want to creat a relationship to connect it with another entity OS, call it "installs" or whatsoever. And this OS has 2 subtypes: WINDOWS and MAC (Linux and Unix also work but just for demo purpose they are not included). Not considering virtual machine or double OS, I can only choose one of these 2 subtypes of OS, how should I model this in the entity-relationship model?

Should I

Create 1 relationship between MACHINE and OS. Or

Create 2 relationship between MACHINE and WINDOWS, MACHINE and MAC. Or

Create 1 ternary relationship between MACHINE, WINDOWS and MAC.

And should I add additional attributes to the entities or the relationship?
Thanks:D


texts:
If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.

Say your app is new and you have identified columns which are good candidates for indexing (based on how you did your views e.g., WHERE / JOINS).

Do you have to immediately index those columns even before you notice performance issues? Or does it make sense to do them later as to not affect the INSERTs yet?


texts: How to group same columns from same two tables, but from two different result sets and/or conditions

texts:
I am having troubles coming up with a query for something that should be quite simple.

Initially, I had two tables of data that I generated joint result sets from; an events table, and a groups table (think communities or organisations). I would use a query to get event data, such as event name, ID, the group that hosted the event (single ID only), amongst others. Every single event and group has its own unique ID number, given by AUTO_INCREMENT. I would use the following query:

SELECT g.group_id,
       g.name,
       prev_id,
       activity_state+0,
       state,
       first_event_id,
       last_event_id,
       GROUP_CONCAT(e.event_id ORDER BY start_date_time SEPARATOR ','),
       GROUP_CONCAT(e.name ORDER BY start_date_time SEPARATOR '__'),
       GROUP_CONCAT(start_date_time ORDER BY start_date_time SEPARATOR ',') AS first_dates,
       GROUP_CONCAT(end_date_time ORDER BY start_date_time SEPARATOR ',')
FROM events e
INNER JOIN groups g ON e.group_id=g.group_id
WHERE start_date_time BETWEEN ? AND ?
GROUP BY g.name
ORDER BY first_dates

The two question marks represent two bound dates and times in a prepared statement. This query would work perfectly fine.

However, I recently updated the structure of the database to include so-called "joint events", where multiple groups host the same events together. I added a joint_event table, which simply contains an event_id column, and a group_id column. The same event IDs appear multiple times in the table (at least twice), and is used to indicate which groups hosted particular events. Joint events are also added to the event table, but are given an ID of 0 (zero).

I now need a query that is similar in functionality to the one above. The query must include the initial result set from the event table and group table join condition, also any occurrences of joint events matching the same conditions from the joint events table. They must be appropriately grouped together, so that I don't get duplicates of the same data (each group should appear in its own row, and only one row per group).

Separately, the join conditions in SQL-89 syntax would be as follows:

events.group_id = groups.group_id

(gives the bulk of results, at least 98%, plus...)

events.event_id = joint_events.event_id AND joint_events.group_id = groups.group_id

(for the joint events)

I have tried using UNION, but it doesn't appear to support grouping by the entire data set returned whole. I have tried using subqueries, but run into issues when using aliases to describe the same columns across two separate data result sets, as it usually produces a "ambiguous" column name error. Using DISTINCT didn't work, as it would either remove events that have the exact same name as each other (these need to be kept), or create an inconsistency in the grouped columns when not applied to all four columns that need to be grouped. The closest I came to was a query that resulted in the joint event data being separated from the rest, instead of being grouped into the rest; or the data was being included, but event data was being duplicated.

I cannot have NULLs anywhere, or use two completely separate queries, otherwise I would have to refactor my PHP script, and would likely make the operation inefficient using PHP to sort the data out.

Thanks in advance.


texts: Get all rows in table A even if no rows in table B in CROSS JOIN

texts:
I Have This query

SELECT *
FROM ( ( SELECT SUM(Auditoria.Importe - ISNULL(Auditoria.Pago, 0)) AS Deuda
         FROM Auditoria
           INNER JOIN
              Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
           LEFT OUTER JOIN
              Cancelaciones ON Auditoria.CUD = Cancelaciones.CUD
         WHERE (Cancelaciones.CUD IS NULL)
           AND ( CAST(Auditoria.Fecha AS date) <= CAST(@Fecha AS date) )
           AND (Auditoria.Terminal = @Term)
           AND (Auditoria.Id_Empresa = @empresa)
           AND (Articulos_Auditoria.Cortado = 0)
        ) AS Deuda
     CROSS JOIN
       ( SELECT DISTINCT Pagos_Auditoria.TipoPago,
                         SUM(Pagos_Auditoria.Cantidad) AS Cant
         FROM Pagos_Auditoria
           INNER JOIN
              Auditoria ON Pagos_Auditoria.CUD = Auditoria.CUD
           INNER JOIN
              Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
         WHERE ( ( ( CAST([Fecha] as date) ) <= CAST(@Fecha as date) )
             AND (Auditoria.Terminal = @Term)
             AND (Articulos_Auditoria.Cortado = 0)
             AND (Auditoria.Id_Empresa = @empresa)
               )
         GROUP BY Pagos_Auditoria.TipoPago
       ) AS Pagos
     )

Table A is Deudas an table B is Pagos. It works fine when rows in Table B (Pagos) are returned, but I want to see rows in table A and get 0 in fields of table B if no rows are
returned from Table B.

Specifications
 - Table A Deudas have always rows to return
 - Table B Pagos may have rows to return
 - There's no ID or Key between the tables

I tried a sub SELECT ... WHERE NOT EXIST but for the quantity of
evaluated rows is not very efficient.

What is the simplest way to do it?

Edit 1

I tried

SELECT *
FROM ((SELECT        SUM(Auditoria.Importe - ISNULL(Auditoria.Pago, 0)) AS Deuda
FROM            Auditoria INNER JOIN
                            Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD LEFT OUTER JOIN
                            Cancelaciones ON Auditoria.CUD = Cancelaciones.CUD
WHERE        (Cancelaciones.CUD IS NULL) AND (CAST(Auditoria.Fecha AS date) <= CAST(@Fecha AS date)) AND (Auditoria.Terminal = @Term) AND (Auditoria.Id_Empresa = @empresa) AND (Articulos_Auditoria.Cortado = 0)) AS Deuda

LEFT JOIN

(SELECT DISTINCT Pagos_Auditoria.TipoPago, SUM(Pagos_Auditoria.Cantidad) AS Cant
FROM            Pagos_Auditoria INNER JOIN
        Auditoria ON Pagos_Auditoria.CUD = Auditoria.CUD INNER JOIN
        Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
WHERE (((CAST([Fecha] as date))<=CAST(@Fecha as date)) AND (Auditoria.Terminal = @Term) AND (Articulos_Auditoria.Cortado = 0) AND (Auditoria.Id_Empresa = @empresa))
GROUP BY Pagos_Auditoria.TipoPago) AS Pagos ON TRUE)

but I get

  An expression of non-boolean type specified in a context where a condition is expected, near ')'.

I'm missing something?


texts: Is the key_buffer_size applicable to myisam tmp tables?

texts:
I have a database about 750GB in size. It's all innodb.

Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The tmp tables fit into memory.

My cache hit ratio (Key_reads / Key_read_requests) is 0.

When MySQL creates these tmp tables, I'm guessing it doesn't it create pseudo indexes to be used by key_buffer_size.


texts: How to disable replication when Distribution db is lost?

texts: I am in the situation that the Distribution database in SQL Server 2014 is lost and I want to disable the replication. I have found a solution how to achieve that when data files are present (also asked here). Unfortunately, I do not have data files of Distribution database. Is it possible to disable the replication when Distribution database is completely lost?


texts: When are privileges listed in \l and when not?

texts:
When are access privileges listed by \l, and when are they not?  The access privileges listed by \l can change after a grant and revoke:

$ createuser -EP my_readonly
$ psql development
development=# \l
                                           List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
...
development=# grant usage on schema public to my_readonly;
development=# grant connect on database development to my_readonly;
development=# \l
                                             List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |     Access privileges
-----------------------------+----------+----------+-------------+-------------+----------------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant               +
                             |          |          |             |             | vagrant=CTc/vagrant       +
                             |          |          |             |             | my_readonly=c/vagrant
...
development=# revoke connect on database development from my_readonly;
REVOKE
development=# revoke usage on schema public from my_readonly;
REVOKE
development=# \l
                                           List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant          +
                             |          |          |             |             | vagrant=CTc/vagrant

Why is that?  What state changed?  I believe the my_readonly user's ability to connect was unchanged through this whole psql session (because I'm guessing the PUBLIC role has connect privileges), but clearly something changed: what is that thing?

Side question: how can I explicitly ask postgres whether PUBLIC in fact does have connect privileges (they may have been revoked -- see Why can a new user select from any table?)?


texts: How to select only members who have not made any deposits this month in mysql

texts:
I am fetching data from two tables, members and fees .I would like to select only those members who have not made any deposit this month. This is what I have tried but the results are not what I am intending to get . any help will be appreciated . I am using mysql .

SELECT
    members.memberid,
    members.companyid,
    members.name,
    members.initials,
    members.surname,
    members.datejoined,
    members.usercode,
    members.address,
    members.cell,
    members.pic,
    members.idnumber, members.rank,
    members.address2,
    fees.feestype,fees.amountpaid,
    fees.companyid,fees.paiddate,
    fees.usercode
FROM
    members
    INNER JOIN fees
        ON members.memberid = fees.memberid
WHERE
        MONTH(fees.paiddate) < MONTH(CURDATE())
    AND YEAR(fees.paiddate)  < YEAR(CURDATE())


texts: TempDB on Solid State Drives on SQL Server Cluster

texts:
Can solid state drives be used for the TempDB on a SQL Server failover cluster if they are local drive on both machines? It is supported in 2012 but our cluster is a SQL 2005.  The application that it is running isn’t upgradeable to 2012.

If the SSD drives are on the same local path on both servers wouldn't it just rebuilt the TempDB when it failed over? Wouldn't the cluster recognize any drive the OS recognizes? Or are there other issues that need to be accounted for.

I realize that Microsoft won’t support it but the performance gain would be great. Our TempDB on that cluster is the heaviest used DB and we can’t change any code in the application it runs.


texts: Meaning of inverted vs non-inverted ISA triangle in EER diagram

texts: I found two different ISA triangles in various books. Like one is inverted triangle(in first figure) and another is non-inverted triangle(as in second figure). Are they give the same meanings? I am currently studying about specialization and generalization in DBMS.


texts: Best method for daily incremental copy of Postgres database

texts:
I'm trying to figure out the best plan of attack for making a nightly copy of our Heroku PostgreSQL production database to our local development environment.

The database is rather large and grows at roughly 250,000 rows per day. Ideally the solution would be an incremental method and only restore the previous days changes.

Our development environment is also Postgres running on OSX.

Any and all help is appreciated.


texts: Separating system databases from MDW SQL Server 2008 R2

texts:
We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it.

Everything works fine with the built in reports run from the instance. However, I am taking all the data from the snapshots.diskusage table to help show how our databases have grown and formulate a storage solution for the next chunk of time. For the majority our databases are uniquely named so there is no concern there running:

SELECT *
FROM [RKYV_MDW].[snapshots].[disk_usage]
WHERE database_name = 'tempdb'

The tricky part comes with the system databases, of particular annoyance is tempdb. Seeing as both instances have a tempdb and upload their stats I have double the number of data points and the graphs turn out somewhat meaningless. I can't see anything in the table bar snapshot_id that might help me seperate the two out but I am unsure where this would be unique to a particular instances tempdb.

Does anyone have any thoughts about this? My alternative is to select every other line (from a quick ganders at the data) but feel there must be a more elegant way out there.


texts: Hard and Fast rule for include columns in index

texts:
Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index
and I found that for the following query :

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

The poster suggested to make index like this:

CREATE NONCLUSTERED INDEX NC_EmpDep
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

here comes my question why can't we make index like this

CREATE NONCLUSTERED INDEX NC_EmpDep
      ON Employee( EmployeeID, DepartmentID, LastName)

or

    CREATE NONCLUSTERED INDEX NC_EmpDep
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

and what thing leads the poster to decide to keep the LastName column included. Why not other columns? and how to decide in what order we should keep the columns there?


texts: Pass parameters to a procedure with SQLcmd

texts:
I use this SQL command:

 sqlcmd -v first_date="30/09/2015" last_date="15/09/2016"

And I would like to pass that parameters to a stored procedure:

ALTER PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
AS
BEGIN

    DECLARE @F_FIRST_DATE_DT VARCHAR(50)
    DECLARE @F_LAST_DATE_DT VARCHAR(50)

    SET @F_FIRST_DATE_DT = N'$(first_date)'
    SET @F_LAST_DATE_DT = N'$(last_date)'

But it return me an error:

  Incorrect syntax near 'first_date'

What is the correct way to pass variables to stored procedure?


texts:
I have enabled Query Store on SQL 2017, and I'm seeing at query that's occurring frequently doing a SELECT * on a specific table.

I would like to narrow down where this request is coming from and see if we can find a better way than doing the SELECT *.

I have a query id, of course, from Query Store. I've also recently asked my developers to include Application Name in their connection strings, which many have done.

Is there a way (perhaps using a DMV, for example) that I can find out the Application Name associated with this query?


texts: Could we recover Mongo records from the data files only without namespace files?

texts: I am missing a namespace file from the Mongo db path. I only have data files with number extensions (e.g. report.4,report.5). Is there any way to recover the mongodb dump from the data files?


texts: How do I set up a report that shows number of clients by age group when their age is stored by date of birth in Microsoft Access

texts: I am working on setting up a new database in Microsoft Access for a food locker it will be used for client input as well as tracking client visits. One of the monthly reports they need is how many clients (including all family members on their account) have visited each month separated into the following age groups:0-3, 4-17, 18-61 and 62 and over. It seems this will have to be a multi-step process as the ages will be stored only as the date of birth, but I wasn't sure if there was something straight forward that I am missing as I am fairly new to Access.


texts: Querying a view based on a table on which I don't have SELECT permission

texts:
Suppose I have the following table (in schema 'SchemaX') :

CREATE TABLE [SchemaX].[myTable]...

And the following view, build upon the previous table but on a different schema ('SchemaY) :

CREATE VIEW [SchemaY].[myView] AS
SELECT * FROM [SchemaX].[myTable]
WHERE ...

I give permission to a certain user to read the view :

GRANT SELECT ON OBJECT::[SchemaY].[myView] TO [myUser]

But he is still not able to read it and gets the following error :

  The SELECT permission was denied on the object 'myView', schema 'SchemaY'

If I put the view in the same schema as the table, the query will work.

Can you explain why it is working when both are on the same schema and why it is not when they are on separate schemas?

EDIT: Turns out my schemas had different owners.

Found this article that explains why it doesn't work when schemas owners are not the same :
https://www.itprotoday.com/sql-server/security-through-ownership-chains