texts:
Should I use SSIS or stored procs to copy data from a number of database tables on one server to database tables on a different server?

Each table's structure is identical to the destination server except for an identifier added to the destination table. There is no real data transformation.
The process would get called from a user application.

My thoughts on using SSIS is that each copy process can run simultaneously. Can I do this with stored procs?

Any suggestions?

Thanks.


texts: find total money of parent node of tree according to a particular pattern

texts:
actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly.

My tbltestingtree structure is ass follow:

        Id         ParentId        IsLeft           IsRight
         1          Null             Null             Null
         2           1                1               Null
         3           1               Null               1
         4           2                1               Null
         5           2               Null               1
         6           3                1               Null
         7           3               Null               1
         8           4                1               Null
         9           4               Null               1
         10          5                1               Null

the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.

My problem is to find the query which can return the total income of any given  node.

According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.

According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has  no pairs

According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)

one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)

I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$,  but the query has not been able to consider the first condition that is the 2:1 condition

        declare @ParentId as int
         set @ParentId=1

       create table #temp_table_name
        (
          ParentId varchar(30) null,
         )

      ;with Child as
       (
    select id,ParentId from tblTestingTree where id=@ParentId
    union all
    Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree
    inner join Child
    on tblTestingTree.ParentId=Child.Id
       )

  insert into #temp_table_name
  select c.ParentId  from tblTestingTree T join Child c
  on c.Id=t.Id
  WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
  group by c.ParentId
  having COUNT(c.ParentId)>1

  select COUNT(*)*500 as totalmoney from #temp_table_name

  drop table #temp_table_name

Please help


texts: How to restrict data pump logon to localhost

texts:
I have a user in Oracle 12c database that will be used to run full data pump export and import. I would like to restrict logons from that user to localhost.

I have tried to create logon trigger:

create or replace trigger logon_trigger
  after logon on database
declare
  host varchar2(50);
begin
  host := trim(lower(sys_context('USERENV', 'HOST')));
  if lower(user) = '<data pump user>' then
    if host <> '<hostname>' then
      raise_application_error(-20000, 'Login not allowed');
    end if;
  end if;
end;
/

I can see from the audit trail that this trigger has fired (ORA-20000: Login not allowed) but the user is still allowed to logon from remote host.

This restriction works for normal users but not for the data pump user. I suspect this has something to do with the roles exp_full_database and imp_full_database.

Is there some way to make this trigger work or should I use local OS authentication?


texts: SQL Server Login to access tables of three different database from single database

texts:
I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which this user doesn't have access and I cannot give access to those databases.

How can I create such a user with single database access but can run query on other two database through this stored procedure only?

So far what I have done:

Created login and mapped to Database1
Right click on the stored procedure in Database1 and given permission to this user to execute this stored procedure
This stored procedure is running a query to get data from two other databases (Database2 and Database3)

e.g.

Select value1, value2, value3
from Database1.[dbo].table1
inner join Database2.[dbo].table2 on ....
inner join Database3.[dbo].table3 on ....


texts: MYSQL: Creating a table with same columns on remote machine

texts:
I am trying to create a table on my local machine which has the same description as some other table on a remote machine. I just want to create the table with same columns, don't worry about the row data.

The table has around 150 columns, so its very tedious to write the CREATE TABLE command. Is there an elegant way of doing this?


texts: Postgresql - Streaming replication - STONITH

texts:
Current situation:

PostgreSQL, streaming replication
Master failed, I created the
trigger file. Slave is promoted to new master
After the old master
restarted successfully, I have to create base backup and change
config to change old master to new slave.
(https://wiki.postgresql.org/wiki/Streaming_Replication: How to
use/13).

My problem: the data amount is very large (sometimes 100GB), therefore the base backup takes a long time. How to turn the old master to a new slave without basebackup?  (For example, with administrator command like pg_switch_xlog().)


texts: Can migration from MS Access to SQL Server or ... make database faster?

texts:
A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn't because of their database design rather than MS Access.

I think that their database is smaller than 1GB large and it will remain always smaller than 10GB. Let me be pessimist 100GB! I'm afraid that the migration doesn't accelerate their query execution time and they would remain unsatisfied. I'd like to know if their slow interaction could be because of Access or I should  contact consult them to continue working with access and just change the designs, optimize network and the programs?

In which case should I think migrations towards non relational databases like MongoDB?


texts: Partitioning a really large table, or splitting in multiple tables?

texts:
We had a MySQL server old enough to not have partitioning enabled, so we decided to take our largest tables and move all the old rows to another table. In case they were needed at some point, they'd be moved to the "recent table", to make its usage faster. Overall, it would mean we'd have:

table_old: holding about 25Gb
table_recent: holding about 1Gb

We would keep this proportion forever, trying to balance the amount of old data vs new data and keeping only data used last week onwards in the recent one.

This proved to be a little bit difficult to develop in a clean way, but it's working well now. By just editing the "recent date" threshold, we can balance how much data the recent table will hold. At some point, old rows would go to the old table, and if some old data was needed, it would be moved around beforehand.

Now, we moved to MySQL 5.7, and partitioning is a thing. It seems cleaner at a developer side, but I don't know if it would be as efficient as what we had now, meaning we could update one row's last_used_time field and pop it to the main table.

Would MySQL still move those fields between partitions, if my partition is based on a varying field like this?
Would partitioning the table make the system run faster, or keeping the data in different tables is better?


texts: Is DBSNMP used only by Oracle EM by default?

texts:
I have Oracle database and I need to install agent for Oracle EM. It requires credentials for DBSNMP user, but it was lost. Is it safe to change password via SYS user to provide it to agent?
Is there any other systems in standard Oracle distribution that connects via DBSNMP user? I'm afraid this user can be used somewhere else.
If it matters database version is 12c, agent and OEM are 13c.


texts: PostgreSQL: Which hardware (number of cores, RAM, storage type, storage size) and what number of hosts are needed?

texts:
There is a Log DB. As I understand, there are two constraints which influence the answer to my question: space needed to store the database, number of reads-writes/sec needed.

There are 72 M records inserted per day, which is equivalent to 2000 records generated per second during 10 active hours. There is no limitation on number of client threads that need to do inserts. It can be hundreds of clients.
We need to store 180 days of this information like a sliding window - 13 000 M records in total, 30 TB (2500 bytes per record).
When a new day starts, data for the oldest day shall be removed;

In parallel some subsystem periodically queries this DB with 500 reads/sec, there is no limitation on number of threads for that, it can be tens of threads:

select * from T_LOG_RECORD
where
    ((F_EXTERNAL_ID="92" AND F_SCOPE="CONT") OR F_GROUP_TAG="100000164")
    AND F_RECORD_TIME < "2016-12-12 23:59:59"
    AND "2016-11-12 00:00:00" < F_RECORD_TIME AND F_STATUS = 0

Here is the draft DDL (Oracle syntax) of the single table in this DB to understand the idea, indexes are skipped:

CREATE TABLE T_LOG_RECORD (
    T_LOG_RECORD NUMBER(20) NOT NULL,
    F_NOTIFICATION_ID VARCHAR(45) NOT NULL,
    T_OPERATOR_ID NUMBER(20) NULL,
    T_LANGUAGE_ID NUMBER(2),
    F_RECORD_TIME TIMESTAMP NOT NULL,
    F_ADDRESS_TYPE NUMBER(2) NULL,
    F_ADDRESS_VALUE VARCHAR(45) NULL,
    F_EXTERNAL_ID VARCHAR(45) NULL,
    F_SCOPE VARCHAR(45) NULL,
    F_SUBJECT VARCHAR(250) NULL,
    F_TEMPLATE_ID NUMBER(20) NULL,
    F_MESSAGE_TEMPLATE CLOB NULL,
    F_MESSAGE CLOB NULL,
    F_NOTIFICATION_PARAMS CLOB NULL,
    F_TRIGGERED_TIME TIMESTAMP(0) NULL,
    F_EXPIRED_TIME TIMESTAMP(0) NULL,
    F_GROUP_TAG VARCHAR(20) NULL,
    F_STATUS NUMBER(2) NOT NULL,
    F_DISCARD_REASON NUMBER(2) NULL,
    F_PRIORITY NUMBER(2) NULL,
    F_ERROR_CODE VARCHAR(45) NULL,
    F_DELIVERY_TIME TIMESTAMP(8) NULL
)
PARTITION BY RANGE (F_RECORD_TIME)
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (PARTITION p1 VALUES LESS THAN (TO_DATE('08-01-2017', 'DD-MM-YYYY')))
    TABLESPACE SOME_TABLESPACE
;

I assume that commodity server hardware will be used. We will implement sharding on application level, with active-standby replication with 1 redundant copy.

My current assumption is that PostgreSQL on a single host (8 cores, 64 GB RAM, 2 x 4 TB SSD without any RAID) will be able to handle 10 K inserts/sec/host.
So we will need 8 hosts/VMs with such parameters. The calculation is the following:
30 TB * 2 (replication factor) / 8 TB (total disk size on host) = 8 hosts.
So there will be 4 host-shards and 4 hosts-replicas. Inserts will be done on host-shards, selects will be done on all hosts (shards and replicas).

Is my calculation correct?


texts:
Let us say that we have to write a query for:

  Find the distinct names of all students with percent>90 in courseno=107

given three relations:

Students (rolno:integer,sname:string)
Courses (courseno:integer,cname:string)
Registration (rollno:integer,courseno:integer,percent:real)

According to an exam solution,the query solution is:

SELECT DISTINCT S.name
FROM Students as S,
     Registration as R
WHERE R.rollno=s.rollno
  AND R.courseno=107
  AND R.percent>90

I think that if two students(say there are two students having same name so its name would be eliminated) so I am worried about use of DISTINCT here.


texts: help in connecting the sql server 2017 developer edition

texts:
I have installed the sql server 2017 for non production environment in my PC.
It has installed successfully.
when connect using sqlcmd it was throwing an error login failed for login sa. it didn't have the asked for the creating user or anythings...
please find the command used to connect

sqlcmd -S servername -U sa

Update:

I can't even log in using windows authentication.

Update2:

I have tried to access sqlcmd from command prompt i got the following error.

  Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [2]. . Sqlcmd:
  Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout
  expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..


texts:
I appreciate if someone could give me some help.
I would like to deal with time addition based on the previous column using mysql. Here is what I have.

create temporary table test(id integer,
                            realtime datetime,
                            realminute float(15));

insert into test(id, realtime, realminute) values
("1","2018-12-12 03:03:03","96");

insert into test(id, realminute) values
("2","15"),
("3","10"),
("4","30"),
("5","30"),
("6","15");

Then I have this.

id|          realtime                      | realminute
1|           2018-12-12 03:03:03           |  96
2|                                         |  15
3|                                         |  10
4|                                         |  30
5|                                         |  30
6|                                         |  15

The following is what I would like to have. Note that 2018-12-12 03:03:03 plus 96 minutes would be 2018-12-12 04:39:03. This would be the realtime in the second column. And 2018-12-12 04:39:03 plus 15 minutes would be 2018-12-12 04:54:03 and this would be the real time in the third column and so on.

id|          realtime  | realminute
1| 2018-12-12 03:03:03 |  96
2| 2018-12-12 04:39:03 |  15
3| 2018-12-12 04:54:03 |  10
4| 2018-12-12 05:04:03 |  30
5| 2018-12-12 05:34:03 |  30
6| 2018-12-12 06:04:03 |  15

How would I calculate these realtime values?


texts: Reset the numbering at the tabs of the Microsoft SQL Server Management Studio

texts:
I notice there are some numbering on the tab of each new query (such as the number 52, 56, 57 and 58 as shown in the following pictures)

What does these numbers means and is it possible to reset to the first original values?


texts:
I am very new to mysql-cluster.I downloaded the binaries and was trying to configure it on my local machine.

Can anybody suggest how to install it on single machine with the 1 management node,2 data nodes,1 sql node.

Also what are the importance of each type of node?

I need the basics steps to install the mysql-cluster.As I have installed it by seeing some website and stuck in between i am getting an error as

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message                                                                         |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB                                       |
+-------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I also have gone through the manual but no results.

Anybody can help me out in this?


texts:
MSSQL 2005 Server Workgroup.

I have a database that is serving as a "Warehouse" for another, older, slower cobol database/application.

I've recently moved the warehouse from one SQL Server instance to a new instance, to keep the warehousing I'm doing separate from other production databases.

The issue I'm having has me perplexed. The table I have, as a copy of the older database, is has, for the purpose of this question, 4 fields: ID, LName, FName, MI, FullName - all NOT NULL. On the report server I have a calculated field FullName: LastName, FName MI:

FullName = LName + ', ' + FName + ' ' + MI;

What's happening is the names are not joining consistently, and it has me completely baffled.

DECLARE @ID int;
SET @ID = 1;
SELECT ID, 'LName' FName, LName                                   Field FROM TABLE WHERE ID IN (@ID) UNION
SELECT ID, 'FName' FName, FName                                   Field FROM TABLE  WHERE ID IN (@ID) UNION
SELECT ID, 'MI'    FName, MI                                      Field FROM TABLE  WHERE ID IN (@ID) UNION
SELECT ID, 'LFM'   FName, LName + ', ' + FName + ' ' + MI Field FROM TABLE  WHERE ID IN (@ID)

This outputs the expected:

1   FName   Jack
1   MI  T
1   LName   Frost
1   LFM Frost, Jack T

But if I switch it to a different ID I get:

2   FName   Joe
2   MI  E
2   LName   Schmoe
2   LFM Schmoe

It's not an issue with nulls (Three fields have data). Trim, coalesce, different combinations of name (LFM, MFL, LF, MF, etc) end up the same: Either the person has a full name... or the person has the first one listed.

To my knowledge I did not have this issue in the other isntance. New instance is on the same server.

Original Server Instance: 2005 9.0.3042 Serv\SQL2005
New Server Instance:      2005 9.0.1399 Serv\DataWarehouse

Note: Query against "original" database works as expected. Query against a report built upon this warehouse works as expected.

So Original Database > Data Warehouse > Report. Original works fine. Report works fine. Data warehouse doesn't. Warehouse and Report are in the same instance, but different databases.


texts: Which datatype is used to store video files

texts:
I am working on a web application using JSP.

I want to ask which datatype(with syntax) is used to store video files in Oracle database.


texts: Will transactional replication break if there is a heavy update/delete query run on the publisher DB

texts:
For these specifications:

Publisher: (prod OLTP DB server) SQL Server 2005, database size : 220 GB also acts as the distributor
Subscriber (Prod reporting DB server): SQL Server 2005, DB gets replicated and is currently in sync.

Now, as per the yearly activity there would  be a script run on OLTP publisher DB to delete more than 3516009 records.

Please suggest if this breaks transaction replication? Or is there a way I can prevent one because setting that up is tedious?

Also, something similar was done 2 years back and we only have the error as

  The process could not execute 'sp_replcmds' on 'xxxxxx'." "Status: 2,
  code: 0, text: 'Timeout expired'" "A time out occurred while waiting
  for memory resources to execute the query." "Agent 'xxx' is retrying
  after an error. 0 retries attempted. See agent job history in the Jobs
  folder for more details." "The step was cancelled (stopped) as the
  result of a stop job request""

And the fix that was done at that time was to increase the query time-out for log reader agent and was set from default of 1800 to a max 65333 secs.

Since the DB has grown over last 2 years we are wondering, if above fix will still be good or should we take any measures?

Also, in case replication breaks, what can be the fastest way to bring it back because setting the whole thing up (especially SQL Server 2005) will bring a big outage on reporting server?


texts: How to get the daily amount of committed transactions on a given MSSQL database?

texts:
The number of transactions for the current connection can be obtained with @@TRANCOUNT.

How can you get a daily counter of the globally committed transactions on a given database?


texts: MongoDB remove() function deletes documents but retains indexes

texts:
I have came across in one of my readings that remove() function will delete the documents without any changes to the existing indexes.

for instance: "db.users.remove() - This will remove all of the documents in the users collection. This doesn’t actually remove the collection, and any indexes created on it will still exist."

I would like to understand why the indexes are retained when the actual referenced data is removed / deleted in that specific collection? is there any specific use case?

Thank you


texts: Optimal way to ignore duplicate inserts?

texts:
Background

This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:

  -- Ignores duplicates.
  INSERT INTO
    db_table (tbl_column_1, tbl_column_2)
  VALUES (
    SELECT
      unnseted_column,
      param_association
    FROM
      unnest( param_array_ids ) AS unnested_column
  );

The code is unencumbered by checks for existing values. (In this particular situation, the user does not care about errors from inserting duplicates -- the insertion should "just work".) Adding code in this situation to explicitly test for duplicates imparts complications.

Problem

In PostgreSQL, I have found a few ways to ignore duplicate inserts.

Ignore Duplicates #1

Create a transaction that catches unique constraint violations, taking no action:

  BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
  EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
  END;

Ignore Duplicates #2

Create a rule to ignore duplicates on a given table:

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO db_table
   WHERE (EXISTS ( SELECT 1
           FROM db_table
          WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;

Questions

My questions are mostly academic:

What method is most efficient?
What method is most maintainable, and why?
What is the standard way to ignore insert duplication errors with PostgreSQL?
Is there a technically more efficient way to ignore duplicate inserts; if so, what is it?

Thank you!


texts:
I need to be able to restore around 6TB of data from one MS SQL 2008 instance into another SQL Instance. DBA's and backup folk are estimating between 36 and 48 hours for the restore. I can't afford this amount of down time on the target system.
Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?
This way the only down time is getting the drives mounted and switching data file location.


texts: Need for recursive lookup, stuck in query design

texts:
In PostgreSQL 9.4, here is my table:

with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;

In a table that stores pairs of user id, i.e. two integer columns (user id and partner), we are requested to find this kind of relations:

User m and user n in the same record
(both cases userid = m && partneruserid = n and  userid = n && partneruserid = m)
indirect partnership with a third X partner in common (in either two columns)
more indirect partnership with a third and a fourth X and Y partner in the chain which are partners between them: m .. x .. y .. n
So on and so forth until there are no more relationships or until the nest level is 5, meaning relation with five steps like:

  users m .. x .. y .. z .. a .. b .. n (always containing the cases in either two columns).

In this table, if we ask for the relationship between users 1 and 3, the function should return:

Approach 1

first, the obvious row 2: 1-3 (single record, no hops)
Result set so far: [1,3]
and the not so obvious row 6: 3-1 (idem)
Result set so far:

[1,3]
[3,1]

then if we follow the paths from user 1 (which are users 2, 3 and 6) and are looking for a partner one hop from user 1, we would then reach rows in which partners of user 1 (user 2 and user 6) are in the record as partners of user 3. These rows could have been [2, 3], [3, 2], [6, 3] and [3, 6] if they existed, but only row 10 does, being the result set so far:

[1,3] [3,1] [6,3]

the next step would be search for rows that have user 2 and user 6 (first hop) partnered with partners of user 3 (1, 5 and 6) (this is the second hop), reaching: row 4 (1..row1..2..row5..6..row10..3), row 5 (1..row1..2..row4..5..row8..3) and row 9 (1..row1..2..row9..6..row10..3).
Result set so far:

[1,3]
[3,1]
[6,3]
[2,5]
[2,6]
[6,2]

then with these last rows added to the result set, we would need to find all users that are partners of user 3 of user 5 that are partners of user 3. But as users 2 and 6 take us back to the first iteration, only remains user 5 for exploring his partnerships with a partner of user 3 (1, 5 and 6), being reached no more rows, hence no more hops or indirect partnerships can be found.
The result set is still the same as the previous step.
here would be nice to have the quantity of partnerships are repeated allowing us to reduce the result set to:

[1,3] qty: 2
[6,3] qty: 1
[2,5] qty: 1
[2,6] qty: 2

That would be the desired result. I hope not have made a mistake trying to make sense. As I posted previously, any advice that points me in the right direction, would be much appreciated!

Approach 2 (cannot imagine how)

Get the direct partners of user 1 (2, 3 and 6)
Get the direct partners of user 3 (1, 5 and 6)
Make a tree of each, and look for points in common.


texts: PostgreSQL: raise error if planner needs to do an sequence scan

texts:
I want to check if our code does use only indexed columns.

I think it does, but I want to be sure.

How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan?

It is ok, if PostgreSQL wants to do an sequence scan, but an index exists.

Then I run our software tests to see if an exception gets raised.


texts: Database Design for Paper Submission System

texts:
I am learner of Database design, I want to design a database for a journal paper submission system in which one paper can be submitted by one or more author. I have initially two tables Author and Paper

Table Author has attributes like,

AuthorID
Name
Contact
Email
URL

Table Paper has attributes like

PaperID
Title
Abstract
Keywords

I need a third table submission where i can preserve association between paper and authors/author. Can any one help me in this regard to find possible association.


texts: Full text search partial search with 'in:*' not working

texts:
select to_tsvector('english', 'JAVA') @@ to_tsquery('english','ja:*');

select to_tsvector('english', 'india') @@ to_tsquery('english','in:*');

The first one is returning true while the second is returning false. How to address this?


texts: Query to check if job already belongs to jobserver in MSSQL

texts:
I am working with a legacy migration script that was setup to add a job (With a number of steps) to a MSSQL server. I am trying to build in some semblance of re-runable without it completely blowing up.

However I am now down to this line :

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

If the job already belongs to the local server, then this line will error. It's very feasible that this might be run several times.

Is there a query that I could run to check whether a job already belongs to local (Or any other servername?)


texts: Creating a Relational Products / Package Database

texts:
To prefix this, I am stuck using Microsoft Access, or at least relating to it, so my capabilities are a bit limited. I also do not want to use an EAV model. I could put the new tables into SQL Server, but it would require software driven integrity, and that makes me a bit nervous.

Some of the business rules I have to accommodate for:

Products must have multiple price levels.
Products can be stand alone and/or part of a bundle of products.
A "product" can also be a service. Such as education.
Bundles themselves are not a tangible product, they only contain products and will have a $0.00 price.
Products can also be part of a package of products. The products may have a different price inside of the package.
Packages can also have bundles of products in them. These packages can also have their own special prices for both the individual products and the products inside of bundles.
Packages have several "Steps" that require a specific product or bundle. The user must pick an item defined for that step. Think about designing your own vehicle and the "Package" is a truck. The "steps" with variable products would be things like what color body do you want for your truck or what shape body of the truck do you want. The products, respectfully, would be things like blue and green or small, medium and large. Each option is a different product. Stuff like that, that defines the package.

Here is an idea I had:

The problems with this model are:

I cannot have multiple prices for bundles in this model. (E.G., we sell a bundle to resller A for price X and resller B for price Y).
Packages cannot override bundle prices in this model.
Querying seems a bit difficult, as it is reciprocal.

I looked at Magento's ERD, and would much prefer a relational model database. I do not want to use EAV, as I feel that would be overkill for what we need anyway, and seems like a real headache to deal with.

Do you have any ideas or suggestions?

Edit:

This model is my most current concept. Each product will have options such as a red, blue and green pencil. The unit cost for that item will be in tblProductOptions and the adjustment to that price will be in tblPrices.


texts: What is the easiest way to backup PostgreSQL and send backups to Google Drive?

texts: I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.


texts:
I've successfully installed Oracle 19c with this article.
Now I need to restore the database from existing backups. I've first restored the control file, then I started to restore the database:
rman> restore database;
...
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15374: invalid cluster configuration
...

Then I tried to create the diskgroup, and it failed:
SQL> create diskgroup DATA external redundancy disk '/dev/loop1' force;
create diskgroup DATA external redundancy disk '/dev/loop1' force
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type

Is there a work around? I can either ignore the diskgroup or find a way to create one, I just don't how to fix it.


texts: Filter Table by a Historical 'Last Day of the Month'

texts:
I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month.  For example:

Table:

DwDate            Balance
20181231           $10
20190131           $80
20190228           $75
20190331           $50

99% of the time I just need the MAX(DwDate), which would be:

@MaxDate = (SELECT MAX(DwDate) FROM dbo.TABLE)

However, occasionally I need a formula that would allow me to go back 1 or 2 months:

@MaxDate - 2 Months


texts: MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT

texts:
The documentation states that without the GLOBAL or SESSION keyword, the isolation level will be valid for the next transaction and will be reverted to the session default after the transaction was commited.

With autocommit enabled, what is the scope of an transaction isolation level for a SELECT statement?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM mytable WHERE id = 1;

Is a SELECT statement a complete transaction, meaning the isolation level will be reverted to session default after the SELECT was executed, or does a SELECT somehow not represent its own transaction, meaning that the isolation level will not be restored after a SELECT statement?


texts: Apply the select privilege to all users in mysql

texts:
I have 30 plus MySQL users their permissions vary from one another.

I want to GRANT SELECT privilege to all of the 30 users. Is there any one command that I can use that can grant select to all 30 users in one shot!


texts:
I am working on a relational database design for a photo organising app, and as I have mentioned before, am very new to databases. I need to include exposure, aperture value and focal length. The values I have been provided are:
Exposure: typically represented in seconds, using, e.g. 1/1000 for fast exposure and 20 for very slow;
Aperture: ranging from e.g. 1.1 up to values such as 45;
Focal Length (mm): ranges from about 8 to 1200.
Would you recommend representing these as enums with a range of values, or rather, for example varchar for exposure, text for aperture and int for focal length (I am using MySQL)?
I still am not 100% clear on why you would use which datatypes.
Thanks again for your insight and advice.


texts: Do I risk losing the benefits of indexing if I have an index on every column?

texts:
I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of the columns, e.g.

select * from table where C > 43 and H is not null;
select * from table where A is null and F < 10 and F > 1 and X > 2;

For performance I plan to create an index on every column of the table. Some feeling in my stomach tells me to ask the experts first: Is it good design for the above described use case to create an index on every column?

UPDATE:
I have to speculate about real use cases. I can't measure the exact queries yet. This is in design phase.

The server is well equipped with RAM and SSD storage, so queries are already "fast" now, and I can feel the effect of caching when I fire similar queries in sequence.

The columns are of types double, integer, timestamp and geometry (which explicitly gets a 'gist' index).

The queries will include from 1 to 10 columns. Usually ~6. Results will usually be <20k rows. Queries on a column will never relate to another column.

Thanks for all the explanations. What I will do:
 * select 1/4th of the columns that I think will be most used and create indexes.
 * wait for more testing/usage and start measuring/analysing the queries and use-cases then.

Thank you


texts: Not using foreign key constraints in real practice. Is it OK?

texts:
Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables.

According to my senior, in real practice, those are very time consuming obstacles when we are dealing with urgent issues. He says when we need to use INSERT/UPDATE/DELETE statements immediately, the constraints block those statements to be executed and writing statements while keeping the constraints is time consuming too. I even heard many other companies are doing this.

While I kinda understand the struggles, I'm not sure if this is a good way since it's quite opposite to my understanding to DB. This company is my first job too, so I don't know how other companies dealing with this.

In practical way, what is your opinion about this? Can this be justified? Are there better ways? How other companies are doing about this matters?

UPDATE: It seems it is a quite common approach for South Korean companies. I asked a few other seniors who worked for other companies, and most of them are saying they all doing like this. And even one of them worked for a financial company! Interesting...


texts: What's the difference between a temp table and table variable in SQL Server?

texts:
This seems to be an area with quite a few myths and conflicting views.

So what is the difference between a table variable and a local temporary table in SQL Server?


texts: Basic information splitting and table design in a database about people, accounts and transactions

texts:
I am having trouble deciding which design would be considered a better practice.

Say, for example, I have two tables:

TABLE Person
pid int primary key
name varchar
age int
bank_account varchar

TABLE Transactions
tid int primary key
date datetime
amount float
from int references person
to int references person

This is just an example and not using any particular syntax.

Now, my Person table will have multiple entries that have all the same information aside from the bank_account, since a person can have multiple different accounts they make transactions from or receive to. Would it be better to do it like this, or would it be better to create a separate account table that holds all the different account numbers with a reference to the person that owns it, and then changing the references in the Transactions table to reference the new account table?

I know that duplicating information in person is in accordance with 1NF, but it doesn't seem like good design to me. Any insight would be appreciated.


texts: Why am I seeing these mongodb 3.6.5 server log messages in huge amounts?

texts:
Could someone please shed some light on these mongodb 3.6.5 server log messages on the primary node of a 3-member replicaset? rs.status() says all the nodes are up and running but seeing a ton of (I)nformational logs.

  2019-01-16T00:07:24.819+0000 I ACCESS   [conn95] Unauthorized: not
  authorized on admin to execute command { replSetGetStatus: "1", $db:
  "admin", $clusterTime...


texts: Delete from table where two of the three keys in another select

texts:
I have tableA which has 4 columns:

IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC).

I'm required to delete the records where the count of (IDA, IDB) is above 1 and the comment is "temp". I've come up with the following SQL statement:

DELETE FROM tableA WHERE IDA, IDB IN
    (SELECT IDA, IDB FROM tableA  GROUP BY IDA, IDB HAVING COUNT(*) > 1)
 AND comment = 'temp'

But I get the following error:
"An expression of non-boolean type specified in a context where a condition is expected, near ','. [...]"

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

I'm rather new and appreciate any help/suggestions.


texts: Postgres Installed in Linux (RHEL) and trying to access / connect using pgadmin (Windows)

texts:
I've installed and configured postgresql in Linux successfully,

1. postgres user created
2. new db created successfully
3. I'm trying to connect using pgadmin III (Windows)
4. Following details are adding in pgadmin window

Host: DEMO
Host: XXXX
Port: 5432
Maintenance DB: postgres
Username: postgres
password: G0!mf17.

5. Clicked on OK

It's tried to connect and displayed message connecting to database.... Failed.

I'm not sure why it's failed and what needs to configure in linux system.

I'm new to linux, If anyone able to guide me solve this issue it will be greatful.

Please let me know if you need more details.


texts: In SQL Server, how do you create a default constraint for a column using the result or return value from a Stored procedure?

texts:
In SQL Server, can you create a default constraint for a column using the result or return value from a Stored procedure, and if so how?

So I am thinking of something along the lines of this, but with valid SQL.

ALTER TABLE [dbo].[MyTable]
    ADD  CONSTRAINT [DF_MyTable_MyColumn]
    DEFAULT (EXEC [dbo].[GetNextNumber]) FOR [MyColumn]
GO

My reason for this is I have a table which has the sole responsibility of providing the next number to use, and a table which the default value must use this next number every time a new record is entered.


texts: Replicating redis foreign table via postgresql synchronous replication

texts:
I have synchronous replication between db server, DB1 and DB2. Tested, working.
I have a redis server (RD1), integrated with DB1 (master) via redis_fdw, no issues here. I was expecting foreign redis table to by synched between DB1 and DB2 after sync replication but it does not. I can see the table on DB2 (slave) when I type "\d" in slave psql but when I want to select records, I get: "ERROR:  could not access file "$libdir/redis_fdw": No such file or directory". The question is, how can I get redis updates from postgresql slave via postgresql sync. It is possible? I can integrate the slave DB with redis server but that would break the DB sync.
Thanks.


texts: What happens at low level when I add a column to a table in PostgreSQL?

texts:
I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this query.

Doesn't relational databases store row data pointers together so it will have to lookup each row to add a pointer of the added column to the end of that row block on disk? This is my current understanding.


texts: How do I terminate a running SELECT query without restarting MySQL?

texts:
I have a MySQL select query that is not getting killed. Please help!

And we have load avg building up on this server.

 | 64446 | root    | localhost | dast-india | Killed  | 3758 | Sorting result | select * from dastnumbers_0 order by addeddate desc limit 25 |

I tried killing and unlocking the tables as usual it works for selects. But now its not responding for that too. Please help.


texts: Is there a reason to use extremely abbreviated table names?

texts:
We're using a database setup from a vendor's application that has horrifically hard to read database table names, and no documentation on what is stored where. I can see why one might want to obfuscate their table structure in a proprietary app, but one of the selling points of this application (Enterprise Resource Planning) was it's customizability.

Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise.

To the best of my knowledge the length of the table name won't affect performance noticeably, correct? The database is very complex (hundreds of tables) so sorting makes sense, but I can't imagine why AccountsPayableTransactions isn't preferable to aptrx....


texts: How do I change the default external text editor in psql?

texts: I want to change the external editor in psql. But the command select-editor does not work. How can I change it? How do I run the command?


texts: Opening a stored procedure file multiple times in sql server management studio

texts:
In Sql Server Management Studio, there is strange situation that made me lose a full day work. It is the problem of opening database objects multiple times. When I try to edit a table by right clicking from the object explorer, it is opening the edit screen for the first time, at second time it is re-opening the previously opened but not in a new edit screen. Despite that, if I try to select from that table by right clicking from the object explorer, it is opening the new query with the select statement multiple times while I retry this process. It is also doing exactly the same thing for the stored procedures and functions. I lost my changes in a stored procedure because of opening multiple times.In Visual Studio it is not possible to re-open a file in new editor multiple times by default. Are there any option to reconfigure sql server management studio to not open an object which is already opened in editor?

Note: I know this is my problem to remember which objects I have already opened, but I am asking for help, if there is an option that I am not aware.

Important EDIT:
I need to clarify that I can understand the SQL programming and I am not a newbie. I just don't used to SSMS and I don't know the all features of that tool. In my question's Note Section, I marked that it is my responsibility to keep records of scripts. I am only asking for if there is an option to prevent opening same script multiple times. If your answer is not more than suggesting to using third-party tool etc. please keep it for yourself.


texts: How do I parse Address into Street Number, Street Name, Type, Unit Number?

texts:
How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this?

2280 MEYERS AVE
ALDERGROVE AVE & S VINEWOOD ST
SIMPSON WAY & N HALE AVE
412 E WASHINGTON AVE #3
AVOCADO AVE & LEMON ST
572 N TULIP ST
1030 HAWAII PL
500 N MIDWAY DR A-E, ADMIN


texts: Calculate value differences of rows before and after bounded by multiple referenced rows

texts:
Supposed I have a user travelling table like this:

 id |  start_time  |  end_time  |  location  |
----+--------------+------------+------------+
  1 |      1       |     2      |    Loc_A   |
  1 |      3       |     4      |    Loc_B   |
  1 |      5       |     6      |    Loc_A   |
  1 |      7       |     8      |    Loc_C   |
  1 |      9       |    10      |    Loc_A   |
  1 |     13       |    15      |    Loc_B   |
  1 |     16       |    17      |    Loc_C   |
  2 |      1       |     3      |    Loc_D   |

Supposedly we are using "Loc_B" as the reference point - the point that the user must pass. We will have 2 nodes @ "Loc_B". Left tree contains all the rows before the current reference row, right tree contains all the rows after the current reference row (i.e Loc_B @ row 2) till the next reference row (i.e Loc_B @ row 6).

For the 1st node (row number 2):

left tree [ Loc_A ]
right tree [ Loc_A, Loc_C, Loc_A ].

For the 2nd node (row number 2),

left tree [ Loc_A, Loc_C, Loc_A ]
right tree [ Loc_C ].

Time diff is calculated as follow: if row is in left tree, then reference row's start time minus the inspecting row's end time. The sign of value will be minus. If row is in the right tree, then diff is equal to the inspecting row's start time minus the reference row's end time.

Time diff value for 1st node will be:

left_tree [ -1],
right_tree [ +1, +3, +5].

Time diff value for 2nd node will be:

left_tree [ -7, -5, -3 ],
right_tree [ +1 ].

Then the final diff value column, we have to compare 2 consecutive nodes with each other in pair and compare the node[0]'s right tree with node[1] 's left tree. Taking the absolute minimum value and append the sign based on its original value (or whether it is in left or right tree)

For user id - 2, diff will be NIL since he didn't pass through the reference point (Loc_B)

 id |  start_time  |  end_time  |  location  |  diff  |
----+--------------+------------+------------+---------
  1 |      1       |     2      |    Loc_A   |   -1
  1 |      3       |     4      |    Loc_B   |   N/A
  1 |      5       |     6      |    Loc_A   |   +1
  1 |      7       |     8      |    Loc_C   |   +3
  1 |      9       |    10      |    Loc_A   |   -3
  1 |     13       |    15      |    Loc_B   |   N/A
  1 |     16       |    17      |    Loc_C   |   +1
  2 |      1       |     3      |    Loc_D   |   NIL

Other note: left_tree and right_tree can be empty in case of Loc_B is the first or last record (partition by id).

May I know whether it is possible to do this?


texts:
We have an OLAP SSAS cube with budget data on the fact table, and budget cathegory (sales, costs, salaries, ...) on a dimension. We have a bunch of formulas for our users but we are not able to create a MAT (moving annual total) on an already calculated measure.

create MEMBER CURRENTCUBE.[Measures].[Sales budget]
as ([Dim budget].[Level_2].[Sales],[Measures].[Budget]);
-- Budget accounting only the values from the fact table with value "Sales" on the appropiate field of the cathegory

create MEMBER CURRENTCUBE.[Measures].[Sales budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].
[Sales budget]);
-- A MAT over a calculated measure doesn't work, we obtain a #VALUE! error

create MEMBER CURRENTCUBE.[Measures].[Budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].[Budget]);
-- A MAT over an original measure works as expected.

Any idea what are we doing wrong? How can we calculate this MAT?


texts: Select first row (grouping) + add aggregate function

texts:
First have a look at this question on StackOverflow.

I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.

How can I combine the answer using DISTINCT with an aggregation...

I've tried:

SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
ORDER BY name, zonedistrict_id, zonestyle_id;

Which screams:

  column "zones.gid" must appear in the GROUP BY clause or be used in an aggregate function

Which is strange, because if I remove ST_Union(geom) as geom,, the query works. But it's absent of the union'd geometry.


texts:
When I run the following querys in MySQL:

SELECT count(*) FROM Inventory AS i WHERE i.lastModified > 1389920146804

or

SELECT i._id FROM Inventory AS i WHERE i.lastModified > 1389920146804;

The EXPLANE command will show that the index on lastModified is used.

But when I run

SELECT * FROM Inventory AS i WHERE i.lastModified > 1389920146804;

or any other query with more then two rows we got a full table scan.

Why? How can I avoid a full table scan when selecting more then one row?


texts: Why availability group resource goes offline in WSFC?

texts:
Sometimes I find the AG Cluster role in offline state which results the AG in resolving state. It doesn't allow the application to access the databases during this time. And then after sometime it comes to online state. Why is that happening?
If one of the cluster networks is down, then the cluster role goes to offline state?


texts: Select multiple rows from one row based on column values

texts:
I have a product table where the value of two columns can imply either one or two actual product results.  If column X = 1 AND Y = 0 return one result, if X = 0 and Y = 1 return one result, if X = 1 AND Y = 1 then return two results.

I want to write a query that will generate two row results for a single row based on the above rule. How can this be done with a SQL query?  Is a UNION the only way?

[EDIT based on comment]

TABLE: PRODUCT

ProductId | ABR | UBR
1 | 1 | 1
2 | 1 | 0
3 | 0 | 1
4 | 1 | 1
5 | 1 | 1

I want a SELECT statement that will generate 8 results from this set.  Basically one result for each instance of either ABR or UBR = 1.

So I would like my result to be:

ProductId | Edition
1 | ABR
1 | UBR
2 | ABR
3 | UBR
4 | ABR
4 | UBR
5 | ABR
5 | UBR

I know I can achieve this using a UNION but I was looking for something more elegant.


texts: Relational Design - Multiple tables into one foreign key column?

texts:
Take a table that tracks meat purchases.  It has a "meat_id" foreign key column to indicate what type of meat the purchase was.

But, the different types of meat are unique in some way (such as USDA grading), so I'm thinking they should be stored in different tables.

I currently don't have enough rep to post the ERD I drew out, but I hope these DDLs will be enough (I've simplified them for brevity):

CREATE TABLE meat_purchase
(
    id                  INTEGER
  , purchase_details    VARCHAR(4000) -- actually multiple columns, but details are irrelevant
  , meat_id             INTEGER
);

CREATE TABLE beef_meats
(
    id                  INTEGER
  , usda_beef_grade_id  INTEGER
        FOREIGN KEY REFERENCES usda_beef_grades
  , desc    VARCHAR(4000)
);

CREATE TABLE pork_meats
(
    id      INTEGER
  , desc    VARCHAR(4000)
);

CREATE TABLE poultry_meats
(
    id      INTEGER
  , bird_id
        FOREIGN KEY REFERENCES birds
  , desc    VARCHAR(4000)
);

-- and so on for the different types of meat...

I'm wondering how to relate the "meat_purchases" table to the meats tables.

Standard SQL and RDBMS agnostic answers only, please.


texts: Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?

texts:
(First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts).

Is a junction table with say 4 foreign keys (4 tables), a good idea for tables that are only to be Read as a Lookup and not changed regularly, and which contain static data and would only be changed by an admin and not a 'user'?

I'll give a simple example.

Say we have four tables each has only two fields - an ID number field  (Primary Key) and a 'Name' (text) field: tblCarModelName, tblCarType, tblManufacturer, tblColor.

And then we have a junction table (tblCars) which is designed as follows:

Car_ID (PK)
CarModelName_IDFK (FK)
CarType_IDFK (FK)
Manufacturer_IDFK (FK)
Color_IDFK (FK)

The table could look like (please note the IDFKs will be integers not strings as illustrated here):

Car_ID, CarModelName_IDFK, CarType_IDFK, Manufacturer_IDFK, Color_IDFK
1,Hilux,Truck,Toyota,White
2,SLK300,Sedan,Mercedes,Blue
3,SLK500,Sedan,Mercedes,Silver
4,Prius,Coupe,Toyota,White
....

So the table ends up looking like a spreadsheet. But why would I want to do this?

Makes data entry and table maintenance easy.
I can still query the table for a particular say 'Manufacturer'
because of the foreign key reference.
I can paste data structured in the same way from Excel directly into
the table.
And also, more importantly, many-to-many relationships are
handled in this example.
If I offer this table the exact same way in a user interface form, I
know when the user chooses from this list, say ID = 3 and that they
have chosen "SLK500,Sedan,Mercedes,Silver" from the list.

Is this design ideal? Feedback is appreciated.

What is this model called anyway?


texts: Can I see the status of a "repair by sorting" in mysql?

texts:
I am running an alter statement on a very large table.

show processlist shows the state as "Repair by sorting".

Is there a way to see the status, or get an estimate of how much work has been completed/remaining?

Thanks.


texts: Query with large number of or'ed predicates on the same column?

texts:
At one place we need to query a table to determine whether any records exists that are in a subset of the same table.

The columns that are queried are varchar columns containing file-paths, and we need to determine whether there are any duplicates for a set of rows.

Say, this is the table:

--------------------------------
| Id  | Path              | ....
--------------------------------
|   0 | file1.txt       |
|   1 | file2.txt       |
|   2 | file3.txt       |
|   3 | file1.txt       |
|   4 | file4.txt       |
--------------------------------

only that in reality there are millions of paths.

Now, at one point we have already collected the data from a number of rows for the table (say, Id 0 and Id 2 => file1.txt and file3.txt) and we now need to find out whether there are any duplicate entries in the table. (The data is just in an string array on the client side.)

Due to the way the application is built at the moment, there will be a loop in the application, essentially executing

for all f in (file1.txt, file3.txt)
    select count(*) from Table where Path = f;

Given that the current production DB has about 14M records and the typical query size is about 100-2000 different paths the performance of this is horrible. (Yes, it's actually running above query 2000 times
We have thought about re-engineering this, but we're a bit stumped at how to lay this out properly.

Given 2000 strings, I very much doubt it would be a good idea to run

select count(*) from Table where Path in (str1, ... str1999);

What are alternatives, generally speaking? This has to run both against Oracle(10+) and SQLServer(2008+).

Edit:

The problem here is that I have n "records" outside of the DB and need to query the DB for each of those records.

As long as n is sufficiently small, we can just dynamically build an OR'ed query and be done with it. Although, of course dynamically built queries do have there own set of problems. (So the "dumb" solution of just running the same query n times may have its merits after all.)

The comments suggest to put the n records into a (temporary) table, but then the question becomes how to efficiently put the n records into that table (or table-valued parameter) in the first place?


texts:
I have a bash script to run migrations on our MySQL databases.

Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line?

Occasionally we've had schema changes that require some data manipulation to migrate that into the new structure and maintain referential integrity.

I'd love to do something like: (assume all vars are set accordingly)

mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "START TRANSACTION;"`
for i in $( ls $MIGRATIONS_FOLDER/migration-* ); do

    # check database for record of this file
    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e
            "SELECT id,filename,ran FROM tbl_migrations WHERE filename='$i'"
            --batch --raw | wc -l`

    # if there is a record in tbl_migrations for this file, skip it
    if (($result > 1)); then
        continue
    fi;

    echo "Importing $i";

    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME < $i`

    if [[summatz screwed up]]
    then
        mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "ROLLBACK;"`
        exit
    fi

    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e
            "INSERT INTO tbl_migrations (filename, ran) VALUES ('$i', 1)"

    if [ "$i" = "$END" ]; then
        echo "Ending migration at user specified file $i"
        break
    fi

done
mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "COMMIT;"`


texts: Mysql Fulltext search doesn't return any results

texts:
I have an issue with a full text search in a MySQL table.

MATCH AGAINST returns no results even I if have 7 records containing the words I'm looking for. What can I do to make it return the rows?

SELECT *
FROM site_plugin_products_cache_texts
WHERE MATCH(item_text) AGAINST ('+your +name' IN BOOLEAN MODE);

  No rows.

SELECT *
FROM site_plugin_products_cache_texts
WHERE item_text LIKE'%your name%'

  7 rows (0.071 s)

http://sqlfiddle.com/#!9/70c3fa/1/0

Thanks.


texts: Wrapping query in IF EXISTS makes it very slow

texts:
I have the below query :

select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)

The above query completes in three seconds.

If the above query returns any value, we want the stored procedure to EXIT,so I rewrote it like below:

If Exists(
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
)
Begin
Raiserror('Source missing',16,1)
Return
End

However this is taking 10 minutes.

I can rewrite the above query like below, which also completes in less than 3 seconds:

  select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source
if @@rowcount >0
Begin
Raiserror('Source missing',16,1)
Return
End

The issue with above rewrite is that the above query is part of bigger stored procedure and it returns multiple result sets. In C#,we iterate through each result set and do some processing.

The above returns an empty result set, so if I go with this approach, I have to change my C# and do deployment again.

So my question is,

  why does using just IF EXISTS changes the plan to take so much time?

Below are the details which may help you and let me know if you need any details:

Create table and statistics script to get same plan as mine
Slow Execution plan
Fast Execution plan

Slow plan using Brentozar Paste the plan
Fast Plan using Brentozar Paste the plan

Note: Both queries are the same (using parameters), the only difference is EXISTS (I might have made some mistakes while anonymizing though).

The table creation scripts are below:

http://pastebin.com/CgSHeqXc  -- small table stats
http://pastebin.com/GUu9KfpS  -- big table stats


texts: Does this database table violate normalization rules?

texts:
I’m working with Access 2010.

I’m developing a database for a public art program – we paint large scale murals on walls.
The database tracks blank walls in the city as potential sites for murals.  It includes information about the building itself and the surrounding property such as the lot the wall faces.

My main question is about the WallsMaster table.  As you can see there are about 30 fields… and about 10-15 more that I’m thinking of adding resulting in 45 fields, maybe even more.

As far as database performance is concerned:

is it better to separate these out into multiple tables or keep them all in one?
Should I break up WallsMaster and have another table that cover my general categories within this table…. maybe called Damage and one called Obstruction and one called FacesLot etc… and then set up FK relationships between them and WallsMaster?

I am  thinking about normalization rules… just not sure if these qualify as groups of related/repeating data as far as 1NF is concerned.  My understanding is that is more about not having a table with something like AuthorName, Book1, Book2, Book3, etc.

Here’s a rough schema of my database:

Table: WallsMaster
WallID (PK)
StreetAddress
City
State
Zip
BldgName
Occupied_or_Vacant
Faces_Direction (NSEW)
Residential_or_Commercial
Historical_Property (Yes/No)
Visible_to_traffic (Yes/No)
Faces_Parking (Yes/No)
Faces_Fenced_Lot (Yes/No)
Faces_Abandoned_Lot (Yes/No)
Faces_Garden (Yes/No)
Faces_ParkPlayground (Yes/No)
Faces_street (Yes/No)
Wall_Surface (Lookup: Brick, Stucco, etc)
Damage_Water (Yes/No)
Damage_Crumbling (Yes/No)
Damage_Graffiti (Yes/No)
Damage_Other (Yes/No)
Obstruction_Trees (Yes/No)
Obstruction_Powerlines (Yes/No)
Obstruction_Other (Yes/No)
Number_Stories
Height
Width
Image (Link)
GoogleStreetView (Link)
Notes (memo field)

Table: WallContacts
ContactID (PK)
WallID (FK)  many:many.  i.e., one wall can have many contacts (owner, manager, tenant) and one contact can be affiliated with many properties (i.e., one owner owns several walls)
FirstName
LastName
Address
City
State
Zip
ContactType (Lookup: Owner, Manager, Neighbor, etc.)
Phone
Email

Table: WallInteraction  (Catalogs each time our staff talks to someone affiliated with that wall or conducts an inspection of the property, etc)
InteractionID(PK)
WallID (FK)
StaffName
Date
InteractionStatus
Notes
(this may expand to include more fields as we work with this more)

Thank you!!


texts:
I have a question, about a question, that is part of the 70-462 Microsoft exam.

I had this question today, and I'm not completely sure of the correct answer. While I did pass the exam, I would like to understand the correct solution given this situation.

If I recall the question correctly, you administer a 2012 SQL Server, the requirements are to offload up to the minute reporting, for an OLTP database, to another server, and also be able to add indexes to the secondary database.

The answers for the question were along the lines of:

Two servers, different data center. AG group with synch commit. Active secondary.
Two servers, same subnet. AG group with synch commit.
Two servers, log shipping every 10 minutes.
A couple diffrent answers with a-synch commit.
SQL Server that includes application database configured to perform transactional replication.
SQL Server that includes application database configured to perform snapshot replication.
Two servers in a windows failover cluster. SQL Server configured as clustered instance.

Initially I ruled out the a-synch commit options (latency), the synch commit AG group in different data centers (latency?), log shipping (delay), and snapshot replciation options.

This left me with the synch commit AG group on the same subnet, or SQL server with transactional replication.

Based on https://msdn.microsoft.com/en-us/library/ff878253.aspx, I read that if you want indexing on your active secondaries, you have to create them in your primary. I interpreted the question as requiring to be able to create indexes on the seconday, that do not exist on the primary (covering indexes for reporting, or what not).

The other answer, refers to SQL Server singularly, which doesn't sound like we're adding "another server" to offload the work to.

Have I misinterpreted the question/requirements? What should the correct answer/solution be?


texts:
I have a table containing the following data, using Postgres 9.6:

log_id | sequence | made_at (timestamp)
206480    1            1
206480    1            2
206480    2            3
206480    3            4
206480    1            5
206480    2            6
206480    4            7
206480    5            8
206480    1            9
206480    2           10
206481    1           11
206481    2           12
206481    3           13
206481    4           14

I have to group and aggregate on the ID so I get an array of possible sequences. In the end I want the data to look like this:

log_id | sequence
206480  {1,1,2,3}
206480  {1,2,4,5}
206480  {1,2}
206481  {1,2,3,4}

I want a new row (with the sequences) when:

the log_id changes; or
the next sequence number is lower than the current sequence number.

There is another column which specifies the ordering (a timestamp), but it's in another table (I join them and use that timestamp). I left it out to make things easier, but we can assume the column is called made_at.


texts: IP address to use with FCI, Always On AG and Always On (AG) FCI

texts:
My understanding is that HA can be achieved using either of the below and my concern was which IP address should client applications use when connecting to SQL Server:

Failover Clustering: For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL Server installation media we install SQL Server on a node and add more nodes to the cluster. If primary fails then another node will be brought online and will continue to service request. Am I right to assume that the clients should use the above cluster IP address to access SQL server? And that the Cluster IP address will decide where to direct the request to?
Always on Availability groups: For this setup the windows cluster and assign DNS/IP to this cluster. Then we configure the AG on primary SQL Server and add more instances to the AG. We configure the Listener and give it a DNS/IP address. I know that client apps should use the Listener IP address and that if primary fails then the requests will to auto directed to the secondary by the listener. So what is the purpose of having the cluster DNS/IP address also?
Combine the above - to get Always on Clustering (Clustering+AG): For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL installation media we install SQL server on a node and add more nodes to the cluster. Then we install a separate new SQL Server instance, add this to the same or separate Windows cluster. Then create an AG on one of the clustered nodes and add the other clustered node and also the newly created separate SQL Server instance into the AG. Here also I assume that client apps should be connecting to the Listener IP address and not the cluster IP address?


texts: Optimization for 100 GB Real-time info table

texts:
I have a table of device information feed which is filled by data from remote device units.

There will be like 1000 units each sending an update per minute which will be stored as a row in that table. So each minute there will be like 1000 new rows added so 60,000 rows per hour and 1 440 000 rows per day. And I need to store minimum 3 weeks data.

As I am new to DB design therefore I am out of ideas to how to maintain such big flow of data.

There will be INSERT queries mostly on this table and SELECT queries, no UPDATEs or other queries will not be needed.

Table strucure: There will be 28 columns mostly FLOAT and TINYINT(4) and 1 DATE type. No other types.
The table is in 3rd normal form and each row will be unique per device unit. So, columns can't be less then this.

I am using MySQL.

I have other tables but they are not so huge so maintainable.
Looking for expert suggestions to how to maintain this database.


texts: DBMS Query Preprocessor Checks Semantics or Syntax?

texts:
I encountered the following statement when reading about the constituents of a query processor in a DBMS:

  ... (b) A query preprocessor, which performs semantic checks on the query (e.g., making sure all relations mentioned by the query actually exist), ...

Page 10, 1.2.5 The Query Processor, Database Systems: The Complete Book (2nd Edition) by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom.

I have experience with computer science, but I have only just started studying DBMS and database design, so I am unsure if I am interpreting this statement incorrectly.

My understanding of semantic and syntactic errors in programming are as follows:

1) Syntax errors. These are invalid code the compiler doesn't understand. The compiler will detect them, because it can't compile them.

2) Semantic errors. These are valid code the compiler understands, but they do not do what you, the programmer, intended. There is no way for the compiler to detect them.

Since the compiler (query compiler) cannot detect semantic errors, is the aforementioned statement incorrect? Should it instead say that the query preprocessor performs syntactic checks on the query?

I would greatly appreciate it if people could please take the time to clarify this.


texts: postgresql: Invalid data directory | Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory

texts:
This error is showing after running this command: sudo systemctl status postgresql@10-main.service
postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor preset: enabled)
   Active: failed (Result: protocol) since Tue 2020-12-01 14:21:40 UTC; 2s ago
  Process: 1603 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (code=exited, sta

Dec 01 14:21:40 ubuntu-s-1vcpu-2gb-sgp1-01 systemd[1]: Starting PostgreSQL Cluster 10-main...
Dec 01 14:21:40 ubuntu-s-1vcpu-2gb-sgp1-01 postgresql@10-main[1603]: Error: Invalid data directory
Dec 01 14:21:40 ubuntu-s-1vcpu-2gb-sgp1-01 systemd[1]: postgresql@10-main.service: Can't open PID file /va
Dec 01 14:21:40 ubuntu-s-1vcpu-2gb-sgp1-01 systemd[1]: postgresql@10-main.service: Failed with result 'pro
Dec 01 14:21:40 ubuntu-s-1vcpu-2gb-sgp1-01 systemd[1]: Failed to start PostgreSQL Cluster 10-main.

I have tried all solutions out there. I can't even take backup by pg_dump. Getting the following error while running pg_dump database_name > database_backup.bak:
pg_dump: [archiver (db)] connection to database "database_name" failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Here is the log of permissions of /var/lib/postgresql/10/main:
-rwx------ 1 postgres postgres    3 Apr 28  2020 PG_VERSION
drwx------ 7 postgres postgres 4096 Nov  9 23:36 base
drwx------ 2 postgres postgres 4096 Nov 29 19:28 global
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_commit_ts
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_dynshmem
drwx------ 4 postgres postgres 4096 Nov 29 20:18 pg_logical
drwx------ 4 postgres postgres 4096 Apr 28  2020 pg_multixact
drwx------ 2 postgres postgres 4096 Nov 29 19:27 pg_notify
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_replslot
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_serial
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_snapshots
drwx------ 2 postgres postgres 4096 Nov 29 20:18 pg_stat
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Sep  2 00:07 pg_subtrans
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_tblspc
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_twophase
drwx------ 3 postgres postgres 4096 Nov 28 01:33 pg_wal
drwx------ 2 postgres postgres 4096 Apr 28  2020 pg_xact
-rwx------ 1 postgres postgres   88 Apr 28  2020 postgresql.auto.conf
-rwx------ 1 postgres postgres   95 Nov 29 19:27 postmaster.opts

Here is the log after running sudo -u postgres psql this command:
 psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Please Assist me to fix this. Any kind of help will be appreciated. Thanks in advance.


texts: MySQL Performance Gains By Splitting Tables?

texts:
I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which would be better.

1.Have one table

user_product

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

The user_product table stores details of any items that a user has in their basket and items the user has purchased. For purchased products a foreign key to a sales_order_id is added, for basket items no sales_order_id is added

2.Have two tables

user_basket

id (PK) | user_id (FK) | product_id (FK)

user_purchased

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

Fairly self explanatory. Items that are pending in a users basket in the first, items they have purchased in the second with a foreign key to a sales order.

The first question I have is are both solutions 3NF? Im 99% sure they both are but if one isn't then I can eliminate it.

The second question is does one solution perform better than the other? I would prefer to go with solution 1, but worried that a lot of queries will be made to that one table. Would it be better to separate out to two tables like solution 2 or does it not really make a difference as the same number of queries will be made to the overall database irrespective of whether its two tables or one.


texts: best db and file system for high volume, high qps

texts:
I'm looking at setting up a DB for a very high volume non-profit situation that also would benefit from high speed updates and searches.

Each transaction record will be relatively small (basic customer information plus 10-12 extra fields) but must be updated/modified literally thousands of times via different updates, reports and screens.  (But I don't need records of each update except in recovery.)

Is MYSQL best for this?  What file system would be most efficient?

Thanks.

G


texts: PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

texts:
Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.

Can we do

pg_start_backup(),
rsync ${PGDATA}/ master to slave,
pg_stop_backup()

... while the master postgresql is still under full load?
(Or will pg_start_backup() lead to

table locks,
I/O blocks,
inconsistencies,
fire alarm,
slow db response

In other words, will pg_start_backup() affect our application?


texts: SELECT COUNT(*) returns no data SQL Server 2012

texts:
I have a query, that as far as I know has failed exactly one time. It's a simple select count(*) from one table, no joins. But at least this once, executing that query resulted in no data read from SqlDataReader. Not even null, just nothing. First call to Read returns false. No exception was raised.

Has any one ever heard of that before? Any scenarios you can think of that would cause it?

I'm not even sure what to ask for to look at beyond SQL server logs. It's not something we can duplicate. I am assuming I'll have to chalk it up to a fluke and move on if/until it becomes a chronic problem. Here's a similar query:

SELECT ISNULL(COUNT(*),0)
FROM dbo.TRANSACTIONS T
WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
AND T.ParentId = @ParentId
AND (T.TransactionStatus != 4)

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

UPDATE: Here's the code executing this.

using (SqlConnection conn = /*connection created and opened here*/)
{
    SqlCommand cmd = new SqlCommand("usp_GetChildTransactions");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@transactionId", SqlDbType.BigInt).Value = transactionId > 0 ? transactionId : (object)DBNull.Value;
    cmd.Parameters.Add("@parentId", SqlDbType.Int).Value = parentId;

    using (SqlDataReader rdr = conn.ExecuteReader(cmd))
    {
        rdr.Read();
        itemCount = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); //exception thrown here because there is no data
    }
}

Update 2: I was hoping to not do this, but maybe the exception handling is part of the problem. Here is the create procedure for this, and also another stored procedure being called in the exception handler. Sorry for the length:

CREATE PROCEDURE dbo.usp_GetChildTransactions (
    @transactionId              bigint,
    @parentId                   int,
AS
SET NOCOUNT ON

BEGIN TRY

    SELECT ISNULL(COUNT(*),0)
    FROM dbo.TRANSACTIONS T
    WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
    AND T.ParentId = @ParentId
    AND (T.TransactionStatus != 4)

END TRY
BEGIN CATCH
    EXEC usp_RethrowException
END CATCH

CREATE PROCEDURE dbo.usp_RethrowException
AS

SET NOCOUNT ON

DECLARE @ErrorMessage    NVARCHAR(4000),
       @ErrorNumber     INT,
       @ErrorSeverity   INT,
       @ErrorState      INT,
       @ErrorLine       INT,
       @ErrorProcedure  NVARCHAR(200)

SELECT @ErrorNumber = ERROR_NUMBER(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorLine = ERROR_LINE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

if (@ErrorState = 0) set @ErrorState = 1

SELECT @ErrorMessage = ERROR_MESSAGE()

IF (LEFT(@ErrorMessage, 15) = N'( Error Number=')
BEGIN

    RAISERROR
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState)
END
ELSE
BEGIN
    -- First time - Create the message with all error information in a standard string format
    SELECT @ErrorMessage = N'( Error Number=%d, Severity=%d, State=%d, Procedure=%s, Line=%d )' + CHAR(13) + @ErrorMessage
    RAISERROR
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState,
           @ErrorNumber,
           @ErrorSeverity,
           @ErrorState,
           @ErrorProcedure,
           @ErrorLine)
END

P.S. I did edit this a little, changing names of the procedures, the name of the table only, and removed comments only.


texts: How to access an Oracle database from linux

texts:
We have a legacy Oracle database hosting all of the information for a now shutdown website, we need to keep the data on the DB available for a period of time due to GDPR etc. We have had some requests from previous users to have there information deleted.
The problem...
This database was last in use 5-6 years ago (for legal reasons we need to maintain access for 10 years), none of the staff still with the company have DB accounts capabale of performing delete actions, we can only read the data.
We have full root access to the linux server hosting the DB but otherwise we only have DB accounts capable of reading information. Old accounts which are able to perform deletes etc exist on the DB but the passwords for these are lost in time.
Is there any way by which we can create a new database user with suitable access? or to some how trigger a pwd reset of an old DB User (given our limited access to the DB itself)?
Edited for clarity:
oracle linux 6.5,
oracle db 12.1.0.2
After using su - oracle (and ensuring the machine is powered on) i can now run sqlplus!
is there something i need to do to get sqlplus to run using the os creds? I found some online info around this, which suggested some setting in the sqlnet.ora file, but the only instance of this file on the server is the sample file which i presume is not the correct one to be updating e.g.
/u01/app/oracle/product/12.1.0.2/network/admin/samples/sqlnet.ora

I tried:
sqlplus / as sysdba

which gives me the SQL prompt, but if i attempt to run:
conn /

Then it responds with invalid username/pwd - i assume conn is meant to connect you to an actual db (not sure, found it in some blog)
final edit:
fixed my final issue, needed to include the schema name when connecting! thanks all.


texts: How to store list of unknown size in database

texts:
I have trouble with database design and don't know how to overcome this. The problem is with list of cities between two chosen city. User chooses start and finish city, and OPTIONALLY, user can write which cities between he will pass, so it can be 0,1,...N. How can I store this in my database ? I already have table with list of States and another table with list of cities.

I thought a bit, and if there is no better option, I will have row in which I will store IDs of cities in format "ID1,ID2,ID3,..." and in software I will separate IDs, but I think this is not normal.


texts: after update trigger sends data to log table and sends tag_id .I need tag Name?

texts:
I Have 4 tables:

Documents, document_tag, doc_document_tag and log.

In document: doc_id, Doc_name are saved
In Doc_tag: Tag_id,Tag_name are saved
In doc_document_tag multiple tags are save against one or more
documents.

for every table logs are generated.

My question is that in my case in doc_document_tag  only tag_id is saved and I need tag_name


texts: In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

texts:
One way to determine the executing stored procedure is to use "dynamic management" methods, like so:

SELECT
    sqlText.Text, req.*
FROM
    sys.dm_exec_requests req
OUTER APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

However, this only displays the text of the stored procedure's create statement. e.g.:

CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id

Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters.

Is there a way to do that?  (In this question Aaron Bertrand mentions DBCC InputBuffer, but I don't think that's appropriate for this problem.)


texts: MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

texts:
Let's assume a data model like this:

Books could have multiple authors as well as authors could have written multiple books.
If a book and its authors are selected by the following statement
(this piece of code is generated by access 2016):

SELECT book.book_id, book.title, book.year_of_publication,
       book.pagecount, author.firstname, author.lastname
FROM book
INNER JOIN (author INNER JOIN link_author_book ON author.auth_id =
            link_author_book.author)
            ON book.book_id = link_author_book.book
            WHERE (((book.title)="Cassandra: The Definitive Guide, 2nd
            Edition"));

the result will be this:

This is perfectly fine and all as expected. Somethimes allthough its a little bit unpractical. In my case I would like to have the different authors not listed in rows but instead in columns.
It should look like this:

My question is: is it possible to build something like this in MS Access 2016 and how can I do that? It is not neccessary that this is done in SQL oder VBA or so, I just want to get an output looking like this :)

Thx in advance

PS: If this is a duplicated question I am sorry but at the moment i don't even know how to call/describe my problem so it is a little bit tricky to google for a solution...


texts: Any advantage of having a second index with included columns on a primary key?

texts:
I'm reviewing our database and noticed that on a particular table we have the following indexes:

Table:

Col1    INT IDENTITY(1,1) Primary Key
Col2    INT
...
about 15 more columns
....
ColN   VARCHAR(50)
....
another 10 more columns

In addition to the standard primary key clustered index on Col1, we also have the following index:

create nonclustered index [iTable-Col1] ON [dbo].[Table1]
(
    Col1
)
include ( ColN )

We regularly search by Col1 and want to only retrieve ColN. This index does get used as it is the smallest index that contains all of the data required to satisfy the query.

My question is, does this index add any benefit to SQL Server? Would we be better off dropping it and just doing a search on the clustered index?

My only thought is that this index is much smaller than the clustered index (25mb vs 300mb) which may make searching and/or caching faster.

Server is SQL Server 2012 if that makes any difference.


texts: Broken NTFS Permissions for MSSQL$SQLEXPRESS

texts:
I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created.
D:\SQL
D:\SQL\Data
D:\SQL\Backup
etc..

I kept the permissions on the Data folder as they were, but I removed anything I thought I didn't need from the others.
Now I can't backup databases. If I try selecting the backup location the dialog doesn't show anything in the d:\SQL folder and if I put the path in myself it tells me this:-

Cannot access the specified path or file on the server. Verify that you have the necessary security privilages and that the path and file exists.
...

It looks like folder permission problem. Another server configured in a similar way has a bunch of "allow" permissions for an account called "MSSQL$SQLEXPRESS", (which I know I deleted), But I can't re-add these permissions because there is no such group, user or built-in security principle on the server.
How do I add permissions for this MSSQL$SQLEXPRESS account?


texts: Difference between star schema and data cube?

texts:
I am involved in a new project, where I have to create data cube from the existing relational database system.

I understood that, the existing system is not properly designed, I am not sure where to start.

My question are:

What is difference between Star Schema and data cube?
Where I have to start? From star schema or directly data cube?
Is data cube generated from star schema?

I have little experience in relational data modeling, this question may seems too basic, I tried to figure out from few resources, still not clear. Please give your advice and suggestions?

If I missed, something very important related with this question, please share your thoughts on that too.


texts:
Consider the following, in PostgreSQL:

drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;

I would like results2 to contain the value blue, indicating that the maximum value for the integer columns occurs in column blue. This can be achieved by running an UPDATE or a function at a later time.

How do I insert the column name as a value? As an example, the row above would be updated such that it contains:

'red', 1, 2, 3, 'blue'

There will not be NULL values for the integer columns and one of the integers will always be the largest.

(See also here.)


texts: How to systematically export all tables from a DB2 database schema to csv

texts: Is there a way using a DB2 database to systematically export all tables from a schema to separate csv files ? What I mean is to export all the tables in a schema without specifying table names. Maybe somehow getting all table names in a schema, then applying the export on those


texts: Can a MySql stored procedure return multiple resultsets each containing different columns?

texts:
I have made a stored procedure that returns multiple resultsets from multiple tables. Therefore each resultset is made out of different columns. These resultsets are retrieved by an ProcedureOutputs object from the Hibernate framework. The first resultset is retrieved with no problems, however Hibernate tries to map the resultsets after that using the column set from the first resultset. This results in the error

2020-04-26 21:28:55.112 ERROR 22164 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'firstColumnFromFirstResultSet' not found.

firstColumnFromFirstResultSet is an example here.

My question is: Is there any way to let Hibernate know that each resultset is unique and that it shouldn't try to map the second resultset based on the columns of the first resultset?

Thank you


texts: SQL MATCH AGAINST incomplete string with space

texts:
I have MySQL database of places, and I want to use the MATCH (...) AGAINST (...) function to find places with two-part names, such as New York. This is a problem because the match against syntax will recognize the second part of the name as an 'OR' condition.

SELECT * FROM places WHERE MATCH (...) AGAINST ('New York')

Will match against columns containing the word "New" or the word "York".

How can I specify the place as one string to match against, or is there a way to escape spaces in the query?

If there is no solution, then I accept workarounds to the problem if I only get results from my database that contain the two-part name.

EDIT: I'm using this for a program where a user types and I query my database for suggestions to predict what the user could be typing, so I need to match against a string that is probably incomplete.


texts: Can Oracle 11g Express Edition Work on Parallels

texts: I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?


texts: How can I check for stored procedure consistency across multiples servers

texts:
I've got a stored procedure on 10 distinct servers, how can I check across all servers for stored procedure consistency?

I'm currently pulling the definition for each server manually, and comparing it in notepad++ compare tool.

It's quite time consuming, and surely I'm not the first one to have this problem.


texts:
I have to design the following sentences for a company that sells different vehicles:
"The database must keep track of everyone involved(customer and staff) in the sales for vehicles. The sale price, profit and commission from that profit also needs to be recorded."
I came across many ER diagrams to model such a scenario which involves a ternary relationship between Vehicles, Staff and Customer as can be seen  here.(ER1)
And another one where the Sales in actually an Entity? like this.(ER2)
Initially, I was drawing my diagram like this(3) but as it says, the diagram is not accurate and I'm not sure why.
What exactly is the difference between the concept that these two diagrams(ER1 and ER2) explain? It would be a lot of help if someone could explain the difference between the two models and which one is better suited for what I want to record in the database.
If any clarification is needed please let me know.


texts: SQL Server 2012 Fails to Install with 0x80004005 - The system cannot find the file specified

texts:
I'm trying to install SQL Server 2012 Express SP4 with Advance Tools on Win Server 2012 R2 Essentials. It keeps failing in the SqlEngineConfigAction_install_startup_Cpu64 action. It fails with error code 0x80004005, the The system cannot find the file specified.

From analysis of the Detail log it seems to fail on

(01) 2019-09-20 16:26:58 SQLEngine: : SQLWriter: Starting Writer Service.
(01) 2019-09-20 16:26:58 Slp: Sco: Attempting to start service SQLWriter
(01) 2019-09-20 16:26:58 Slp: Sco: Attempting to open SC Manager
(01) 2019-09-20 16:26:58 Slp: Sco: Attempting to open service handle for service SQLWriter
(01) 2019-09-20 16:26:58 Slp: Sco: Attempting to close service handle for service SQLWriter
(01) 2019-09-20 16:26:58 Slp: Sco: Attempting to close SC Manager
(01) 2019-09-20 16:26:58 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing Startup and scenario Startup.
(01) 2019-09-20 16:26:58 Slp: The system cannot find the file specified.

I cannot seem to find reference to the actual file it is failing on or why it is failing, The Installer has been run as Administrator and also I've tried extracting the installer and running the setup program from an Administrator command prompt.

It fails in the same place each time. I am having to use the NT AUTHORITY\SYSTEM for the database service account as the known issue of "Could not find database engine startup handle"

The server is acting as a DC, we have the same version of SQL installed on other machines that are acting as a DC

Can anyone help me please with resolving this issue.
Full Detail Log is here Detail.txt

The machine also has a older install of SQL Server 2005 Compact Edition that cannot be removed. I don't know if this is causing the problem. I have tried both the 64Bit and 32Bit install of SQL they both fail at the same point.

Thanks.


texts: PostgreSQL - is it possible to use a plpgsql function in a bash script?

texts:
Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up).

Problem : Since between the partial-backup and the restore may pass some time, and certain records could be deleted, I need to perform an Upsert(Update if record exist, Insert if record doesn't exists). This operation in Mysql would have be done with REPLACE, but in PostgreSQL it doesn't exist. After some research I've found a good substitute of it(correct me if i'm wrong) in this function :

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
        RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The problem with this is that I need to make dynamic the input parameters of the function, and also the UPDATE and the INSERT statements, in order to adapt it for every record and every table i'm going to upsert.

Question :

is it possible to integrate plpgsql functions in bash scripts in order to makes them more dynamic?


texts:
I want to generate the next enroll_no.  It is incremented by group for each institute_id and every new institute has to start enroll_no at 1. When an institute has multiple branches then the problem happens.

If we fetch MAX(enroll_no) and then increment 1 and insert and at the same time branch 2 fetches the same MAX(enroll_no), then after incrementing they both send same enroll_no to the server.  This creates a duplicate enroll_no for the institute_id.

I need them to be exclusive and possibly use a lock there but i don't know how to go about that.


texts:
We migrated our databases from SQLServer 2012 to SQLServer 2019. Our ETL's are build in Visual Studio and are set up from a master package. The masterpackage calls on different packages, which are not deployed in SSIS. One of the packages calls a stored procedure. This stored procedure call on different stored procedures. On the old server, this SP step took 4 hours. On the new server, this step takes 7 hours. What could we do to speed up this proces? Does the compatibility level of the database affect this proces? And would it help if we deploy the package in SSIS? We are open for any suggestions.
Things we already tried:

Rebuilding indexes and updating statistics
Improving certain queries
Creating 8 files instead of 1 in the tempdb (old sever has one)

Thank you for your help.
Esmee


texts: Long term storage of sp_server_diagnostics results

texts: Is the output from sp_server_diagnostics stored anywhere by default please ?


texts: Fail plan on a release to production with differential backup

texts:
I am currently looking into a fail plan when we do a release to our production server.

The context
Currently we have one single production database (also production and testing but they are not important to my question) with MS SQL SERVER 2012. No mirroring and no fail over clusters.
We make a full back up every night around 1 am.

The issue
When we release a new version of our web application we need to update the production database with SQL update scripts. (changes to the scheme, tables, constraints etc...). We want to keep the down time as short as possible.

When we update our production database with our scripts, and something goes wrong there, we want to revert the failed changes.

My plan

DB becomes inaccessible to the web application.
I first make a differential backup from the production database.
We run our cleanup and update scripts
Something goes wrong here
Restore the production database by restoring the differential backup

But all documentation states that I should first restore the full backup and then the differential backup.

My Question
Is it not sufficient enough to use my differential back up when something went wrong with the cleanup and update scripts ? Or do I really have to restore first the full back up and then the differential backup ? Because in this case, I won't win much time if something goes wrong.

Extra
We want change the database setup in the future to avoid this manual approach but for now I am hooked to this context and approach.


texts: Migrate azure sql database to Azure SQL Database Managed Instance

texts:
We need to migrate one of the database into MI from Azure Sql. Has anyone done it before.Looking for any documenatation/Guidance on this as not much is available. Will be testing the below method soon.

One way would be to import the bacpac to blob and then use to restore in the MI. Any suggestions?