texts: Data Migration: Unload/load of GENERATED ALWAYS TIMESTAMP

texts:
While migrating data from one Db2 database to another we need to retain the ROW CHANGE TIMESTAMP of the migrating data in the target table CFFOLIO.FOLIO

We have a ROW CHANGE TIMESTAMP attribute in the target tables. The constraint is like in the table shown below:

CREATE TABLE "CFFOLIO"."FOLIO" (
"FOLIO_ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
"FOLIO_STATE_ID" BIGINT NOT NULL,
"TIMESTAMP" TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
"ASSIGNEE" VARCHAR(50 OCTETS),
"LOCKED_BY" VARCHAR(50 OCTETS),
"LOCKED_TIMESTAMP" TIMESTAMP,
"FOLIO_PRIORITY_TYPE_ID" BIGINT NOT NULL,
"CONTEXT_ID" BIGINT NOT NULL,
"NAME" VARCHAR(100 OCTETS),
"DESCRIPTION" VARCHAR(2000 OCTETS),
"FOLIO_KEY" VARCHAR(50 OCTETS),
"SCORE" DECIMAL(6 , 2) NOT NULL,
"FRAUD_ASSESSMENT_VALUE_ID" BIGINT,
"DUE_TIMESTAMP" TIMESTAMP,
"INBASKET_ID" BIGINT NOT NULL,
"TZ_OFFSET" BIGINT,
"IS_CONFIDENTIAL" INTEGER NOT NULL DEFAULT 0
)

The data being migrated already has the TIMESTAMP values and these need to be retained in the target table.

In order to retain the TIMESTAMP of existing data,
we had to remove the constraint first:

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP DROP GENERATED;

load the data  -- we used a Java program to load the values from CSV files -- then put the constraint back, and we have a challenge here.

We are able to do step 1 and 2 but cannot find the correct syntax to put the constraint back. We tried the following:

SET INTEGRITY FOR CFFOLIO.FOLIO OFF;
ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP
  SET GENERATED ALWAYS AS (FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP);
SET INTEGRITY FOR CFFOLIO.FOLIO IMMEDIATE CHECKED FORCE GENERATED;

ALTER TABLE above gives a syntax error.

What is the correct syntax for altering the table? Is there any other way to do this?


texts: Script works but Im not sure this is the best format

texts:
I have a survey table which contains mostly integer. I want to make it readable for end user so I use select statement as below:

SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
        WHEN 3 THEN 'Three' ELSE 'Your message.' END
        ,(SELECT CASE DoYouWishToP When 0 Then 'Yes'  When 1 Then 'No' END)
        ,(SELECT CASE Housingoptions When 'rb0' Then 'Lease'  When 'rb1' then 'im lazy' when 'rb2' Then 'Rental' END)
        from tblSurvey s

Above script does work. My question is why the 2nd and 3rd SELECT statments need to be inside the parenthesis. ( ). And do you see any issues in my script?

If I Use below I get Error:

 SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
            WHEN 3 THEN 'Three' ELSE 'Your message.' END
            ,SELECT CASE DoYouWishToP When 0 Then 'Yes'  When 1 Then 'No' END


texts:
I would like to do full-text search in XML documents.

However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in the example below). Also, I don't know how to specify my own delimiters for the parser.

Below is SSCCE:

I would like to be able to select for the last component of the attribute value: attributevalue and for the parser to recognize : and / as delimiters and so yield boo1, boo2, some and attributevalue as lexemes.

DROP TABLE IF EXISTS xmldocument;
CREATE TABLE IF NOT EXISTS xmldocument (
i SERIAL NOT NULL,
content XML NOT NULL
);
ALTER TABLE xmldocument ADD PRIMARY KEY (i);

INSERT INTO xmldocument (content) VALUES
('foo')
,('foo')
;

-- matches both records as expected
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'foo';

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'attributevalue';

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo2';

 -- no match
 SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo4';

As to why I don't just use ILIKE % etc. The reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.

* update after accepting the answer*

Based on the accepted answer, the following queries all work as expected:

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('foo');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('attributevalue');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo2');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo4');


texts: Advice on a basic design, first time database design

texts:
I am taking a course to become a Java developer.

The course involves using databases but we never really design any, unfortunately.

Most of the time we get pre-made databases and we have to implement code on it to Insert, Update, Read or Delete data.

But when my final test comes, chances are I will be making something that involves a database and therefore I want to try designing a few smaller ones to get the hang of a designing since I noticed that a good database design makes a lot of difference when writing the code to work with it.

I hope these types of questions are allowed here and that it's not going to be too broad.

This is a small design I made for something a simple as clubs and members with addresses and phonenumbers.

  There will be multiple clubs.
  Each club will have multiple members (obvious)
  A member cannot be part of multiple clubs
  A member can have multiple phonenumbers and email addresses
  A member can only have one address
  An address can belong to different members (couples or siblings)

My biggest confusions:

  If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the
  same member listed twice?
  Should I put all my tables on the automatic increment of an id or would this be a bad idea? (benefits/downsides?)
  If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add
  these to the columns also? (see picture 2)
  And my probably noobiest question at all... Do I put the foreign keys of phonenumber and email in their respective table
  linking to a person_Id or should I put phoneNumber and email Id's in
  the person table?

(My apologies for the language in the pictures not being English, I hope this isn't too much of a problem)


texts: Passing variable as parameter to json_query function

texts:
I am getting error:

  The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it.

DECLARE @cnt INT = 1;
DECLARE @cnt_total INT = 1;
DECLARE @json NVARCHAR(MAX);
DECLARE @json1 NVARCHAR(MAX);
declare @str  VARCHAR(200);

WHILE @cnt <= 10
BEGIN
    set @str = '$.Seasons[0].Products['+convert(varchar,@cnt)+'].ProductChannels';
    set @str = char(39) +@str+ char(39);
    PRINT @STR
    select @json = json_query(jfile,@STR) from Import.tstjson;
--MORE CODE
   SET @cnt = @cnt + 1;
END

Sample data:

{
  "Seasons": [
    {
      "Season": "12321231",
      "Products": [
        {
          "ProductId": "211",
          "ProductChannels": [
            {
              "ChannelId": 1,
              "WeekQuantities": []
            }
          ],
          "ccc": 3,
          "Tttt": 4
        }
      ]
    }
  ]
}


texts: Securing Always Encrypted SQL Server from "sa"

texts:
I'm currently studying SQL Server Always Encrypted feature. How can I disable column decryption to "sa" if all they have to do is add Column Encryption Setting=enabled in the login's additional login parameters in SQL Server Management Studio?

I read articles in Google. They only said we can secure data from admins but no example how.


texts: How to create projection for proper using of UPPER function?

texts:
we've faced the situation when the operation WHERE UPPER(columnName) IN ('PRED1', 'PRED2', 'PRED3') works very bad. If I remove UPPER then it performs good. I've tried to play with projections and could not get it working properly. How can I solve that issue (maybe the problem is not in projection...)
I've tried following code:

CREATE TABLE public.ProjectionsTest (
id int primary key,
a varchar(2000)
);

INSERT INTO public.ProjectionsTest values (1,'a');
INSERT INTO public.ProjectionsTest values (2,'b');
INSERT INTO public.ProjectionsTest values (3,'c');
INSERT INTO public.ProjectionsTest values (4,'d');
INSERT INTO public.ProjectionsTest values (5,'e');

SELECT ANALYZE_STATISTICS('public.ProjectionsTest');
SELECT REFRESH('public.ProjectionsTest');

CREATE PROJECTION public.ProjectionsTest_lower_ob
(
 id,
 a
)
AS
 SELECT id,
        lower(a) a
 FROM public.ProjectionsTest
 ORDER BY lower(a), id
SEGMENTED BY hash(id) ALL NODES ;

explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id;

-- ROLLBACK
DROP PROJECTION public.ProjectionsTest_lower_ob;
DROP TABLE  public.ProjectionsTest;

And I am getting following result (the projection "public.ProjectionsTest_lower_ob" is not used):

------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id

Access Path:
+-SORT [Cost: 36, Rows: 4] (PATH ID: 1)
| Order: ProjectionsTest.a ASC, ProjectionsTest.id ASC
| +---> STORAGE ACCESS for ProjectionsTest [Cost: 35, Rows: 4] (PATH ID: 2)
| | Projection: public.ProjectionsTest_super
| | Materialize: ProjectionsTest.a, ProjectionsTest.id
| | Filter: (lower(ProjectionsTest.a) = 'a')


texts: How to create a stored procedure with columns from three different tables in SQL Server 2008?

texts: I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from first table, loan type from second table and account number, status from 3rd table.


texts: Syntax error using preparedstatements with postgresql

texts:
I have a Java application connecting to a PostgreSQL database.

Using straightforward code like:

        ps = con.prepareStatement(sql);
        if (dataTypes != null && dataTypes.size() > 0) {
            for (int i = 0; i < dataTypes.size(); ++i) {
                if (dataTypes.get(i) == DataTypes.NUMERIC)
                    ps.setLong((i + 1), Long.parseLong(values.get(i)));
                else
                    ps.setString((i + 1), values.get(i));
            }
        }
        rs = ps.executeQuery();

and with a query like

select ? from dummy

it just works fine. In the postgres logs what shows is:

2015-06-13 12:48:30 EEST [28294-3] xx LOG:  execute <unnamed>: select $1 from dummy
2015-06-13 12:48:30 EEST [28294-4] xx DETAIL:  parameters: $1 = '0'

But with a query like:

select to_char(now()+interval ? day, 'YYYYMMDD') from dummy

I get a syntax error:

Cause [org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

and in the postgres logs:

2015-06-13 13:17:39 EEST [29311-3] xxx ERROR:  syntax error at or near "$1" at character 31
2015-06-13 13:17:39 EEST [29311-4] xxx STATEMENT:  select to_char(now()+interval $1 day, 'YYYYMMDD') from dummy

but in pgadmin something like

select to_char(now()+interval '0' day, 'YYYYMMDD') from dummy

works just fine without any error.

I just can't figure out what the problem is. Why does one of the queries work but the other one doesn't?

My PostgreSQL version is 9.4


texts:
Is it possible to use LAG() to get the previous value of the current column?

The computation I need to do is the following:

value(n) = (value(n-1) + 1) * column(n)

meaning I already have a column, and I want to create a new one by multiplying the column by the previous value of the new column I am creating + 1...

First value would be 1.

Something like:

SELECT (LAG(col1) OVER (ORDER BY date) + 1) * col2 AS col1

The current cell value is defined by the previous value times another column.

If I try this I get undefined column col1 (of course since I am creating it in my query...)

In Excel it is extremely easily done with the following formula:

B2 = (B1 + 1) * A2

So, if the table has:

  date     | col2
------------------
2018-12-01 |  2
2018-12-02 |  3
2018-12-03 |  1
2018-12-04 |  4
2018-12-05 |  1.5

the result should be:

  date     | col2  | col1
-----------------------------
2018-12-01 |  2    |    1
2018-12-02 |  3    |    6           (1+1) * 3
2018-12-03 |  1    |    7           (6+1) * 1
2018-12-04 |  4    |   32           (7+1) * 4
2018-12-05 |  1.5  |   49.5        (32+1) * 1.5


texts: How to use the output of one query in other

texts:
I am using following query to get the output as JobId, JobName and Start Execution date

 SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null

But i want to use Start_Execution_Date column in another select statement, how to do that.Please suggest


texts: Postgres: Disable "similarity" column output on "SELECT SIMILARITY"?

texts:
I've tried disabling similarity column output on SELECT SIMILARITY but still cannot.

Below query will generate a column similarity:

SELECT SIMILARITY(title, 'Nation'), title
FROM gallery
WHERE title % 'Nation'
ORDER BY similarity DESC LIMIT 5;

Below query does not output similarity column but I am unable to sort:

SELECT title FROM gallery WHERE title % 'Nation';


texts:
The title is pretty explicit, I just want to disable completely redo logs on  MariaDB 5.5. This is more of a testing database that might eventually be used for a small web forum, but I don't care about being able to recover from crashes, all I care about is saving disk space.

Is there a way to completely disable redo logs? What if I set the max size to 0?


texts: Full text search with dot in search term is super slow

texts:
I have a full text catalog with about 300K records in it. We use full text search over 3 columns to search this catalog of articles.

Searching single words like 'Shower head' are almost instantly. But almost all the products also have a code in there name, like 62.13.1 or 300B.10.

People in the organization know a lot of these codes and search with these codes.
But using a . in my search query slows my query down to almost 1 minute.

I think it has something to do with word breakers but I'm not sure and my knowledge of database isn't that great. The results I'm getting back are correct thou.

This is on Windows Azure SQL btw. Locally isn't slow as well.

Query:

SELECT TOP 100
a.Id as Id,
CASE WHEN ((a.Code IS NULL) OR (( CAST(LEN(a.Code) AS int)) = 0)) THEN
a.Name ELSE CASE WHEN (a.Code IS NULL) THEN N'' ELSE a.Code END + N' - ' +
a.Name END AS Value,
a.Name as Name,
a.Code as Code,
a.[ImageUrl] AS [ImageUrl],
a.[Description] AS [Description],
... Other small properties
case when (a.TimesUsedInArticleList is null) then 0 else
a.TimesUsedInArticleList end as [Frequency]
FROM Articles a with (nolock)
LEFT JOIN MeasureUnitTypes mut on a.MeasureUnitTypeId = mut.id
LEFT JOIN UnitTypes ut on a.UnitTypeId = ut.id
LEFT JOIN PostGroups pg on a.PostGroupId = pg.Id
WHERE
a.IsDeleted = 0 AND
a.ArticleType != 2 AND
CONTAINS((a.Name,a.Code,a.Description),@Query)
ORDER BY TimesUsedInArticleList desc, IsCommon desc

Note: The query also contains a wild card (for example: 61.25*).

When not searching with the wild card the query is much faster.


texts: Is it theoretically possible to INSERT and DELETE a row using the InnoDB storage engine before any changes are written to disk?

texts: I'm debating whether to use the InnoDB or MEMORY storage engine for a high-frequency INSERT/DELETE access token table, and I want to minimize the amount of disk writes. If it's possible for InnoDB to perform a rapidly executed INSERT and DELETE of a given row before actually writing anything to disk, that would be ideal. If the INSERTs and DELETEs will always result in disk writes, I'll probably end up going with MEMORY just to spare the disk endurance.


texts:
I have a phpBB installation on a shared hosting site, and I need to be able to run Sql queries on the underlying database from my remote (home) workstation.  I am checking the MySQL website for tools and I see that there are a few, but which one does what I have just described?

I am very familiar with MS Sql Management Studio, which I use daily on my day job to work with Sql Server databases, and something that allows me to edit Sql queries and run them against the remote MySQL database where my phpBB resides is what I need.

Which tool provided by MySQL does what I need?


texts: Multilingual Dictionary Database question

texts:
Let's say someone is coding a multilingual dictionary in PHP, and he wants to let people submit both words and definitions in English and Spanish, but wants to track it so they can be filtered.  Tables thus far are as follows:

words
languages
definitions

Languages are in a separate table to allow for expanding later on.  That being said, would it be better practice to create a separate table for associating words and definitions with their respective languages and join when querying, or to add a language column directly into each table referring to the id column in languages?


texts:
is it possible to limit somehow usage of resources for pg_dump?

The problem is, whole DBase is 775GB and i have two very large tables:

pg_largeobject table is – 390GB

pg_largeobject statistics:
Index scans              778313505
Index tuples fetched    1079812292
Tuples inserted          201022148
Table size                  395 GB
Indexes size               6900 MB

hive.lob_messages – 265GB.

Index scans             194377937
Index tuples fetched    183894199
Tuples inserted          16902844
Table size  8127 MB
Toast table size     272573947904
Indexes size              3090 MB

In all cases I am executing this pg_dump command:

pg_dump -U postgres -d hive -Fc -v -f /nfs/hive_dump_10/hive_full_20191016_1945.dmp 2> /nfs/hive_dump_10/hive_full_20191016_1945.log

When i try to run pg_dump, to dump all the data it is killed by "out of memory".
My Postgre SQL server have had 20gb of RAMs and when I started a pg_dump, it kills after about ~30minutes. saying out of memory.

DB with 20GB of rams and pg_dump is killed by this *.log line:

pg_dump: reading large objects

Kern.log:

Oct 15 09:00:18 hive-psql kernel: [21504626.583951] Out of memory: Kill process 30663 (pg_dump) score 750 or sacrifice child
Oct 15 09:00:18 hive-psql kernel: [21504626.584000] Killed process 30663 (pg_dump) total-vm:19151692kB, anon-rss:16873728kB, file-rss:0kB, shmem-rss:0kB

Here is ZABBIX monitoring tool showing my three times tried a pg dump with 20GB rams:

So to cope with the problem i gave my all resources, additional +20 GB ram (40gb in total), and the it kills after ~3hours by this *.log line:

pg_dump: dumping contents of table "hive.lob_messages"

And kern.log says:

Oct 16 23:19:01 hive-psql kernel: [15614.408113] Out of memory: Kill process 2693 (pg_dump) score 840 or sacrifice child
Oct 16 23:19:01 hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB

ZABBIX window again, with 40GB in total:

I do not have any more resources for that.
So what are my options? My DB grows everyday, thank you.
I think that additional 20GB will be suffice, 60GB of rams it should be enough . But is this OK situation? What to do when my DB became like ~2TB in size and etc...

Other question, is my hive.lob_messages table is okay?
Table size and toast table size is different.


texts: Using exists in a query instead of a regular where

texts:
I saw this query that finds all the employees with more than one degree:

select id, name
from employee E
where exists (
 select *
 from academics A1, academics A2
 where  A1.emp_id = E.id and
        A2.emp_id = E.id and
        A1.discipline != A2.discipline
 )

But why is the exists necessary? Why not just do:

select id, name
from employee E, academics A1, academics A2
where A1.emp_id = E.id and
      A2.emp_id = E.id and
      A1.discipline != A2.discipline

Are the two above equivalent?


texts: Will Federated engine work with pxc cluster

texts:
I'm a bit new to MySQL specific settings and I can't find any hint/insight to this question.
I have several Federated tables that work great for their current purpose, the rest of the database is in InnoDB.
There is a plan to replace Federate in future, but that will not happen soon. In the meantime we're setting up a cluster for Mysql Percona server.
The cluster will probably be multi-master with ProxySQL.
The issue is Federate - will it work and are there things that we can do to ensure it's functionality?
Edit: Is there an alternative to Federate? Is splitting federated tables to a different db on the same server a good idea? Like a temp bucket table that just does batches of inserts?


texts: This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add  an "activity logging" table that records user actions such as deletion and user comments etc. I was thinking of creating just one table. Rather than 3 tables. The problem is with ensuring referential integrity. Should I create 3 separate columns that link to these tables? Should there be one column and I use it when needed? What is the general recommendation in such case? or should I just create 3 separate tables?


texts: XAMPP Installation - MySQL shutdown unexpectedly

texts:
I'm installing XAMPP on my machine. I'm following Derek Banas youtube tutorial, and I also tried myself, but I'm constantly running into the same issues.

I have changed my Apache ports to 1234 (main), and 8181 (SSL) and MySQL ports to (3307). Apache runs fine, but when I run MySQL from the XAMPP control panel it get this error log.

  23:11:36  [mysql]  Error: MySQL shutdown unexpectedly.

  23:11:36  [mysql]  This may be due to a blocked port, missing dependencies,

  23:11:36  [mysql]  improper privileges, a crash, or a shutdown by another method.

  23:11:36  [mysql]  Press the Logs button to view error logs and check

  23:11:36  [mysql]  the Windows Event Viewer for more clues

  23:11:36  [mysql]  If you need more help, copy and post this

  23:11:36  [mysql]  entire log window on the forums.

I have also deleted files from the MySQL folder in the xampp folder recommended in a previous stack overflow question https://stackoverflow.com/a/18162264/9182716

Also when trying to navigate to localhost phpmyadmin I'm prompted with this warning screen.

https://imgur.com/a/7iagc


texts: Log shipping - failed over to secondary, how to fail back

texts:
SQL 2012

I've been researching and testing Log Shipping as a DR solution. I got the two servers setup. Log shipping is in place and verified to be working correctly. I failed over by restoring the last translog and recovering the database. All this testing was done on VMs on my laptop. At this point everything looks good.

My question now is, how do you fail back? What if this were a true test of the DR in Production. Ok I proved it worked, the test is over, now I need to fail back. Is there a best practice?


texts:
I have records like below in my table.

I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not working:

 select route_id,fixcode,fixdescription from
 route_fixcodes group by route_id,fixcode,fixdescription
 having route_id = 12345 Order by fixcode

I want Result like below:

Table DDL:

CREATE TABLE [dbo].[route_fixcodes](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [route_id] [int] NOT NULL,
  [fixcode] [varchar](4) NOT NULL,
  [fixdescription] [varchar](32) NOT NULL,
  CONSTRAINT [PK__route_fi__3213E83FD7609D27] PRIMARY KEY CLUSTERED ([id] ASC)
)


texts: How can I load csv data into mongodb and choose the types I'd like to have created for each column?

texts:
I'm looking for a method to load csv data in mongodb and specify the types that I'd like to have created for each column?

I've been using mongoimport but I find it quite painful because there doesn't appear to be any way to specify the datatype to be used for a particular column in the csv file.

I load the data ...

mongoimport --drop -d myDatabase -c myCollection --type csv --fields name --file myFile.csv

Then I do analysis to see what types mongo actually created.

> db.artist.itunes.feed.count()
36545
> db.artist.itunes.feed.count( { 'name' : { $type : 2 } } )
36511
> db.artist.itunes.feed.count( { 'name' : { $type : 1 } } )
1
> db.artist.itunes.feed.count( { 'name' : { $type : 16 } } )
33

Then I do surgery to correct errors like so ...

db.artist.itunes.feed.find( { 'name' : { $type : 1 } } ).forEach( function (x) {
  x.name = new String(x.name); // convert field to string
  db.artist.itunes.feed.save(x);
});

This is tedious.

It would be great if there were a method that allowed me to specify at import time what type to create for each column in the csv file like so:

mongoimport --drop -d myDatabase -c myCollection --type csv --fields field1,field2 --types 2,2 --file myFile.csv

http://docs.mongodb.org/manual/reference/operator/query/type/


texts: Ensure same tenant in a Many to Many relationship

texts:
I am trying to draft a very simple data model for a multi-tenant database.

So far I have only 3 tables:

company: pretty much my tenant
account: is part of one company and zero or more groups
account_group: same as account: one company and zero or more accounts

Here is the SQL:

CREATE TABLE company (
  name TEXT NOT NULL,

  PRIMARY KEY (name)
);

CREATE TABLE account (
  email_address TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (email_address),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group (
  name TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (name, company_name),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address) REFERENCES account (email_address)
);

In this specific case, there is no constraint between the Account Company and Group Company.

After doing some search, I found this question and tried to add the following to my account_group_membership table;

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,
  account_company_name TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address, account_company_name) REFERENCES account (email_address, company_name),
  CONSTRAINT same_company CHECK (account_group_company_name == account_company_name)
);

Unfortunately this doesn't work since the company_name column of the account table is not unique.

I cannot:

Make the column company_name unique since multiple accounts can be from the company.
Use email_address and company_name as primary key for an account. The email_address should be unique in the whole table. Also account will mostly be queried by email_address and from time to time by company_name.
See any other solution actually....

So is there any simple way to do this (simple as in easy to understand and maintain) ?
Alternatively, does having this problem, mean that there is something wrong going on in the design ?


texts: Enabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways

texts:
First of all just want to say , I am completely new to Transparent Data Encryption.

Can I get some guidance on things to keep in mind while I am enabling TDE on Critical PRODUCTION Servers. I have read many articles about TDE : It looks like pretty straight forward process but in my case I do have few concerns as below:

Currently we are with AOAG 2014 SQL Server 2014 SP2 GDR On
Windows Server 2012 R2

My Databases are FileStream enabled
I am not clear picture as how to test the performance of TempDB performance as it is forever encrypted once I enable TDE. Kindly help me how can I test the TDE TempDB performance.
I am using Third party tool for FileStream enabled Databases backup and my backup files are around 700 GB size. Once I enabled TDE what will happen if I am planning to restore them to a 1. Same environment with same Certificate and same DMK (PROD --> PROD) . 2. PROD --> STG environment where PROD is with diff certificate and STG is with different Certificate.
Is there a way I can test the end-end Server level testing post enabling the TDE. How does the CPU , RAM , Disk IO , Network above . I read according to MS only there will be 5-10% more resources but no great performance impact when compared non TDE Enabled Databases.
Please share some insights on things I should definitely keep in mind before enabling TDE .
If there are any Testing cases I can do it to measure performance after TDE enablement please do share me some insights.
Backup compression and TDE does not go hand in hand i.e. You wont be able to leverage backup compression when you enable TDE. This means your VLDB backups will be HUGE ! Does this mean my existing Backup files is going to increase more and more ?? I am using third party tool , does this points affect my third party tool backups too ?? Please advice.

Regards


texts: Return Results From Select Statement and Store Count In Variable

texts:
I am wanting to execute my select statement, but also store the returned row count into a variable.  This is how my DDL is set-up, how can I store the row count from my Select statement in my variable@returnedrows

Declare @returnedrows int;

Insert Into pinkpurplegreen (field1, field2, field3, field4, field5)
Select field1, field2, field3, field4, field5
FROM blue

Select * FROM pinkpurplegreen


texts: Restrict record to be update or deleted according to dates inside row for SQL Server 2005

texts:
I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at  the subscriber that still existed at the publisher.

I now know that if someone updates, or deletes some records at the publisher, which do not exist on my subscriber, then my replication is going to crash.

I know  that I could also filter data at the distributor, but this would mean reinitialising my subscribers, and that would mean transferring almost 40GB over a slow link.

So, whilst I am testing my subscribed database I'd love to restrict users to only updating or deleting records at the publisher side. My source table has a date field and I want to restrict updates or deletes according to date.

Is it better to use a trigger or a constraint for this?

How can I avoid as much record locking as possible?


texts: insert/update query running slow to specifi tables

texts:
I am monitoring MySQL client connections and queries run time. Found some insert/update queries to specific tables are slow than expected.

I know the tables which are slow are big in size > 600K, i hope for RDBMS not big deal buffer pool size at 2G, max connections 200.
Table Open Cache is at 99% efficiency and  InnoDB buffer pool size is 87% used
what are the ways to optimize / tune performance.


texts: PostgreSQL alternative to SQL Server’s `try_cast` function

texts:
Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.

This makes it possible to then use a CASE expression or a coalesce to fall back on. For example:

SELECT coalesce(try_cast(data as int),0);

The question is, does PostgreSQL have something similar?

The question is asked to fill in some gaps in my knowledge, but there’s also the general principle that some prefer a less dramatic reaction to some user errors. Returning a null is more easily taken in one's stride in SQL than an error. For example SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. In my experience, user errors are sometimes better handled if there is a plan B.


texts: Cross-database foreign keys vs. database design best practice

texts:
MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

Let's say there are two databases, A and B, that in general store different information for different purposes and with different means of uploading data, however some part of the data may appear in both databases. Now let's assume that one wants to make sure that everything out of this small intersection that ends up in B has been previously uploaded to A. Is a foreign key a good idea in this case?


texts: SQL server indexing foreign keys, covering indexes included columns

texts:
I have pulled a list of FK's that do not have indexes on them.

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created ?

for example mytable below is a 20 column table with one varchar(255) and the rest ints, datetime or smallints.

it is missing indexes on

AdvertiserId
Dirty
MasterAdGroupId

AdvertiserID missing a foreign key index, but [advertiserID] is part of an index with [dirty]&[error] which are smallints. Should this have its own dedicated index ?

Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys ?

    index_keys
    -------------
    [AdvertiserAdGroupId]
    [AdvertiserHierarchyId]
    [AdvertiserHierarchyId], [AdvertiserAdGroupId], [MasterAdGroupId]
    [AdvertiserAdGroupCode]
    [AdvertiserAdGroupId], [MasterAdGroupId], [AdvertiserId]
    [AdvertiserId], [Dirty], [Error]
    [MasterAdGroupId], [Deleted], [AdvertiserAdGroupId]
    [AdvertiserHierarchyId], [Error], [AdvertiserAdGroupId], [MasterAdGroupId], [Deleted]
    [Error], [AdvertiserHierarchyId], [Dirty], [AdvertiserAdGroupId]
    [MasterAdGroupId], [AdvertiserAdGroupId], [AdvertiserHierarchyId]
    [AdvertiserId], [AdvertiserAdGroupCode], [LastSyncedDate], [CreatedDate]
    [AdvertiserId], [Deleted], [Paused], [AdvertiserAdGroupCode]


texts:
I have an application where the data repository is moving to Oracle 11g.  This means we need the new Oracle 11g client on the application server to support the retrieval of their data.

Is it possible to have both the Oracle 10 and Oracle 11 clients running on the same server?

If not, can the Oracle 11 client support retrieval of data from an Oracle 10 database?


texts: How did you split strings before string_split()

texts:
I have some strings I need to split on a SQL Server 2014 instance.

I would normally do it something like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

select value from string_split(@bk, '~')

and I can also do it like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

;with hizizzle as (
select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity)

select flippity, left(floppity, charindex('~',floppity)-1) as floppity,
right(floppity, len(floppity) - charindex('~', floppity)) as flooo
from hizizzle

...but there must be a better way, right?

Question: How did you do string splits in the ancient past before 2014?


texts: Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow

texts:
There is a lot of rows in table "logs" (the Request A FROM).

Request A is quick and Request B is quick.

Put together, Request A WHERE IN Request B is very long. Is that normal?

Request A is very quick:

--request A
SELECT "rid", max("createdAt") as "createdAt"
FROM "logs"
WHERE "rid" IN (17,71,196,187,111,86,108,81,54,184,245,27,118,100,175,136,130,67,45)
GROUP BY "rid";

Request B is very quick:

--request B
SELECT "dr"."rid"
FROM (
  SELECT *
  FROM (
    SELECT "eid", "tid", count(*) over (partition by "eid", "tid") count, id as "rid"
    FROM rs
    WHERE "deletedAt" is NULL
  ) "nr"
WHERE count > 1
) "dr"
INNER JOIN teams ON teams.id = "dr"."tid"
INNER JOIN projects ON projects.id = teams."pid"
ORDER BY "eid", "tid"

The result of B is the same list of numbers than in request A WHERE IN.

Replacing request A WHERE IN by request B, it becomes very slow.

Postgres: 9.5.6

Query plan for Request A:

Query plan or Request B:

Query plan for Requast A WHEREIN B:


texts: Broken tempdb location and can't recover

texts:
Made a mistake and mistyped an alter database command for tempdb.

Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using:

net start msqsqlserver /f /t3608

But then I can't actually connect to the instance at all using either sqlcmd or ssms.


texts: Populate main table with random employees (shuffled) from employee table based on department

texts:
I have a task which I have been working on but am struggling finding the end result.

Task Description
Check each row in Main table

If column a = ‘x’ then assign one of the 4 ‘x’ employees from employee table picked randomly to that row
If column a = ‘y’ then get one of the 4 ‘y’ Standards officers from
employee table picked randomly to that row

Employee Table

Id  Dept Employee
1   x   x1
2   x   x2
3   x   x3
4   x   x4
5   y   y1
6   y   y2
7   y   y3
8   y   y4

Main table (DATA) contains many columns and I need end column (Employee) to be populated by a Employee based on Dept and shuffled in employee table to randomize also.

Thank you


texts: SQL Server transaction log backups: test whether tail log follows last known log backup

texts:
We are using SQL Server with full recovery mode.  Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the current tail log.  (Without actually restoring these backups; the purpose here is to test the consistency of the backups.)

I already know how to do this for the existing backups: using RESTORE HEADERONLY I get the FirstLSN and LastLSN of every file, which can be compared for consecutive files, in order to determine whether they are compatible.

However, I don't know how to check whether the tail log follows the last log backup.

If I had the FirstLSN of the tail log, I could compare it to the LastLSN of the last log backup.  But how can I obtain the FirstLSN of the tail log?

I need a solution that works from SQL Server 2005 upwards (ideally using t-sql). So far, I have searched Google to no avail.  Btw. I first posted this on stackoverflow; but migrated it here since it was flagged off-topic there.

EDIT

I tried the two provided solutions on a small example (SQL Server 2005, 9.0.5057):

BACKUP DATABASE TestDb TO DISK = 'C:\temp\backup test\Full.bak'

-- fire some update queries

BACKUP LOG TestDb TO DISK =  'C:\temp\backup test\Log1.bak'

-- fire both queries from the provided answers:
-- Martin Smith's answer yields: 838886656088920652852608
-- Shawn Melton's answer yields: 46000000267600001

RESTORE HEADERONLY FROM DISK = 'C:\temp\backup test\Log1.bak'
-- yields: 46000000267600001

So it appears the first one is off by several orders of magnitude.

I then did the same test on SQL 2008 SP1 (10.00.2531), where both queries yielded the correct answer.


texts: query generate the information one column in two columns in one query

texts:
I have a question related with a query in sql.  I have the next table:

id        name
1         name1
2         country1
3         name2
4         country2

I need to call the results so:

id         name      id   country
1          name1      2    country1
3          name2      4    country2

Thanks for your help in advance.


texts: Install SQL Server 2017 over 2016 Local DB and 2012 native client

texts:
I have downloaded Microsoft SQL Sever 2017 and would like to install it. But just to be sure I checked installed applications and I found that there is:

Microsoft SQL Server 2012 native client
Microsoft SQL Server 2016 LocalDB

installed already. ( I noticed that because there was a Microsoft SQL Server folder in Program Files)

Can I just proceed with the installation?
I don't recall having installed SQL server previously so I am not sure why those two are installed in the first place

Also, I would like to have SQL Server management studio installed. Is this a separated install?


texts: Is it possible to exclude rows from a select based on conditions of another row

texts:
My data set is quite simple;

Ref          Value      Voucher No
------------ ---------- ----------
M019922      -154.80    100081968
M019922A     154.80     100081968
M019922B     -154.80    100081991

These represent three lines in a sales ledger.  The ref without a suffix is the original transaction.  The ref with the A suffix is a reversal of the original transaction (it doesn't matter why, just that it was reversed).  The ref with suffix B is the new, correct transaction.

So I have three scenarios to cover, and for simplicity I would like to use just one query, but if that's not possible so be it

Scenario 1

There is only a single transaction with no suffix.  This means it's just an invoice we have processed, so I want to return the first row.

Scenario 2

There are two rows, one with no suffix and one with an A suffix.  In this case I am not interested in either row, as we have just reversed the transaction.

Scenario 3

As above, I have three rows.  In this case, I am only interested in the row with the B suffix.

There must be something I can do using the Voucher No, excluding the duplicates, bit I can't see what the answer is.

I don't think this is possible, but I am happy to be proved wrong.

Before you ask, I have interrogated the rest of the database to see if I can find a flag that tells me a transaction has been reversed out.  This is a very old system......(I have the original invoice from 1984)


texts: Single disk for Data, System, User, Temp and Backup when installing Fail Over Cluster?

texts:
I have a Windows cluster with two nodes. I am trying to make Fail Over Cluster using SQL Server 2012.

On both nodes there is 2 TB storage available which I can access as:

C:\Storage_For_Cluster\Volume1

So basically a 2 TB SAN is mapped on both nodes as above.

My questions is, can I make Fail Over Cluster with one disk only and all data will be saved in it? Or it is better to use a separate drive for each. If you take a look at the screenshot below, you will get an idea as to what I am talking about. As you can see we are using two drives Z and X for different options.

Since in this case I only have one drive, can I use this for all these options?

Secondly MSDTC is not installed yet (which I think is also required to make Fail Over Cluster) so I wanted to know shall I install MSDTC too on same drive as mentioned above?


texts:
I'm going to build a social network app. Each user will have photos, videos, details, posts etc.

My question is when a new user registers, should I generate many new tables for that user or have one table for all users which may be contain billions of rows?

And which is best database for social network system: MySQL or CassandraDB?

Please help me!


texts: How do I disable escaping when using COPY FROM in PostgreSQL?

texts:
I have a large tab delimited file that I want to read into a table in PostgreSQL 9.5.  It contains double-quotes and backslashes that I want to treat as regular characters.

I think COPY FROM is the way to go, but I can't figure out how to disable escaping.

Here is a sample of the data (it's from Google's ngram dataset):

aX13_X  2006    8   5
aX13_X  2007    4   3
aX13_X  2008    2   1
a\  1852    1   1
a\  1935    1   1
a\  1937    2   2
ACT1V1T1ES  2003    15  11
ACT1V1T1ES  2004    63  6
ACT1V1T1ES  2005    1   1
ACT1V1T1ES  2006    5   4
ACT1V1T1ES  2008    4   3
ACTION="    1995    3   3
ACTION="    1996    6   5
ACTION="    1997    9   7
ACTION="    1998    19  11
ACTION="    1999    11  5

And the table:

CREATE TABLE onegram (
    id SERIAL,
    ngram character text,
    year integer NOT NULL,
    match_count integer NOT NULL,
    volume_count integer NOT NULL
);

If I try without modifiers, the backslash escapes the tab:

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv';
ERROR:  missing data for column "volume_count"
CONTEXT:  COPY onegram, line 4: "a\     1852    1       1"

So I switch to CSV, and then the double quote characters quote the tabs:

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv' WITH CSV DELIMITER E'\t';
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY onegram, line 17: "ACTION="      1999    11      5
"

Using CSV lets me use the DELIMITER keyword.  It works if I pick a delimiter that doesn't occur in the sample (space, in this case):

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv' WITH CSV DELIMITER E'\t' QUOTE E' ';
COPY 16

But I want to be able to include any character (except for tab and newline).  So, how can I disable QUOTE?  Or what can I use instead of COPY FROM?

Edit: For slightly arbitrary reasons, ideally I'd like an option that does not involve pre-processing the data.


texts:
From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command:
Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
 FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2:
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date
 FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me?
Thanks,


texts: Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016

texts:
1) I am trying to encrypt some varchar columns which are not identity / primary using the always encrypted feature of SQL Server 2016,
but getting an error code 71501,71561 for unresolved and / or ambiguous reference.

How do I resolve this ?

2) Is there a way to nominate a column as primary key for all tables at once ,which do not have a Primary Key .

Thanks


texts: ERROR 1297: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER when importing data

texts:
I have the cluster built by one MGM Node and two data/sql nodes

When importing data to an NDB node the importation crashes returning the following:

ndb_2# mysql -u root -p db < data.sql
Enter password:
ERROR 1297 (HY000) at line 214: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER

Monitoring the other NDB node, I could see the data is being well inserted, but at 'n' records, the first node return the ERROR.

The ndb_2_out.log & ndb_3_out.log write:

WARNING  -- ACK wo/ gcp record (gci: 60138/0) ref: 0fa20004 from: 0fa20004

The ndb_2_error.log & ndb_3_error.log don't write anything.

Before the crash, both nodes have the same records.

I google it but I can't find anything related.


texts: OpsCenter missing in Datastax Cassandra for Windows

texts:
Following this guide within the Cassandra installer I should get OpsCenter too.
But this screenshot https://www.datastax.com/wp-content/uploads/2012/01/dsc-win4.jpg
doesn't match the current release (v3.9.0) installer, not even (v3.0.9).

In both installer I do not get OpsCenter, how can I get it?


texts: practical effect of not validating FKs in Postgres?

texts:
I need to add a foreign key definition to a very large table in Postgres.  (Let's assume for the moment that I'm positive that every FK value is actually valid.)
I know that I can specify NOT VALID in order to prevent Postgres from taking aggressive locks on the table while validating the foreign key.  Then I can go back "at leisure" and run VALIDATE CONSTRAINT as a cleanup.
What if I never bothered going back to run VALIDATE CONSTRAINT?  Is there any practical effect of not doing so?


texts: I can not Ola Hallengren database backup script to Data Domain make works

texts:
Ola Hallengren has a nice SQL Maintenance scripts that I installed to our dev SQL server. The backup script works very well to a local drive, however it does not work to Data Domain. At all.

Here is the script:

EXECUTE dbo.DatabaseBackup
 @Databases = 'USER_DATABASES',
 @BackupType = 'FULL',
 @CheckSum = 'Y',
 @BackupSoftware = 'DATA_DOMAIN_BOOST',
 @DataDomainBoostHost = 'ourdatadomainhost',
 @DataDomainBoostUser = 'ouruser',
 @DataDomainBoostDevicePath = '/ourdevicepath',
 @DataDomainBoostLockboxPath = 'C:\Program Files\DPSAPPS\common\lockbox',
 @CleanupTime = 240

Here is the output (truncated):

  Date and time: 2019-04-16 14:52:39 Command: DECLARE @ReturnCode int
  EXECUTE @ReturnCode = [master].dbo.emc_run_backup ' -c DEVSQLSERVER -l
  full -y +10d -k -S 1 -a "NSR_DFA_SI_DD_HOST=ourdatadomainhost" -a
  "NSR_DFA_SI_DD_USER=ouruser" -a
  "NSR_DFA_SI_DEVICE_PATH=/ourdevicepath" -a
  "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox"
  -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" "MSSQL:1_DB"' IF @ReturnCode <> 0 RAISERROR('Error performing Data Domain Boost
  backup.', 16, 1)

  Msg 10314, Level 16, State 11, Line 1 An error occurred in the
  Microsoft .NET Framework while trying to load assembly id 65536. The
  server may be running out of resources, or the assembly may not be
  trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query
  again, or check documentation to see how to solve the assembly trust
  issues. For more information about this error:

  System.IO.FileLoadException: Could not load file or assembly
  'ddbmasqlclrlib, Version=4.7.0.0, Culture=neutral,
  PublicKeyToken=c0f9ccf99c6c0e66' or one of its dependencies. An error
  relating to security occurred. (Exception from HRESULT: 0x8013150A)

I tried many things, including

ALTER DATABASE 1_DB SET TRUSTWORTHY ON
Change 1_DB owner to sa
Running MS SQL Server Management Studio in administrator mode

I can not figure out what I can do more to solve this problem.
Any idea?


texts: track down which application / procedure / function modified the value of a column in a specific table

texts:
in our enterprise application, value in one column of a table is being modified at client side. This happen usually once in a week. i have verified in all stored procedures and other modules, value in that column is just inserted first time (when some request is generated through web app) but never updated again. so it seems quite strange. now i have created a temp table and a trigger to track the record when that column is updated, but my problem is i don't know how to track the stored Procedure or any other part of db / or application which is modifying that value.

I have used HOST_NAME(), APP_NAME(), SUSER_NAME() in my trigger. these functions just return the machine IP, and db name "sa", and ".Net SQL client" things, but i need to know the actual SP / Trigger / function name which modify the value.
i cant use CONTEXT_INFO() since i don't know the actual issue point.

Any help from you experts, will be highly appreciated.


texts: Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data

texts:
I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new demograph e.g. M18+ and F18+ = A18+ which works perfectly however when trying to create a script that will create a demograph range e.g. A18-24, i am encountering some issues i cannot resolve.

My first script that adds two demographs together is as follows:

create table #SurveyTemp
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, Time, Aud)
select   Com, Survey, 'A18+', Wk, Time, sum(Aud)
from     table_survey
where    survey = 'LO2017'
and      demograph like 'F18+'
or       surveyid = 'LO2017'
and      demograph like 'M18+'
group by Com, survey, Wk, Time:

insert into table_survey
select temp.Com, temp.Survey, temp.Demo, temp.Wk, temp.Time, temp.Aud
from   #SurveyTemp temp

drop table #SurveyTemp

This script works fine. It adds two demographs together and inserts new rows of data, creating a sum of the audience for each Com, Survey, Wk, and then Time. See the sample data set below where the addition of M18+ and F18+ has created a new record called A18+

Let me start with a sample data set:
(Disclaimer: the live database has MANY rows for each demograph)

com | surveyid | demo | wk | time | audience
-------------------------
 1 | LO2017    | A18+ | 1  | 300  |   4.7
 1 | LO2017    | F18+ | 1  | 300  |   1.9
 1 | LO2017    | M18+ | 1  | 300  |   2.8
 1 | LO2017    | A25+ | 1  | 300  |   2.3
 1 | LO2017    | A18+ | 2  | 100  |   3.7
 1 | LO2017    | F18+ | 2  | 100  |   1.9
 1 | LO2017    | M18+ | 2  | 100  |   2.8
 1 | LO2017    | A25+ | 2  | 100  |   4.3

What I need to write now is a script that creates a range. Notice how in the table above there is a A18+ and a A25+. Please bear in mind that in my live table there will be MULTIPLE rows for all demographs.

In this example, I will be using the above table. So, I need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In the script below, I am trying to create a range called 'A1824' but I am either getting a "subquery returned more than 1 row" error OR it is inserting the wrong data into the temp table where the sum in AudOne and AudTwo is the SAME for every single Wk and Time.

create table #SurveyTemp
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8),
    AudOne decimal(18,8),
    AudTwo decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
          (select sum(aud) from table_survey where demo = 'A18+'),
          (select sum(aud) from table_survey where demo = 'A25+')
 from     table_survey
 where    surveyid = 'LO2017'
 group by Com, survey, Wk, Time,
)

update #SurveyTemp
set Aud = AudOne - AudTwo;

insert into table_survey
select temp.Com, temp.survey, temp.demo, temp.Wk, temp.Time, temp.aud
from   #SurveyTemp temp

drop table #SurveyTemp

It is this second script that i need to get working and have spent hours trying to resolve without much luck.
Please let me know if i can provide any more information to help you help me!

Edit:
My expected results would be something like the following:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 300  |   2.4

But there would be multiple rows where there are multiple 'wk' and 'time' entries. E.g.:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 100  |   2.4
 1  | LO2017   | A1824 | 1  | 200  |   3.7
 1  | LO2017   | A1824 | 2  | 100  |   2.1
 1  | LO2017   | A1824 | 2  | 200  |   6.2


texts:
We are building a real time banking platform and app.

Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power outages and lose our data (I'm talking about MongoDB).
Our secondary concern is schema - like functionality for our app tenant users.

We also would like to escape schema updating hell on large databases. Custom schemas for our tenants is optional but very desirable.

We are very heavy AJAX frond end and our backend is full nodejs. We do a lot of JSON data processing.

Should we go with MySQL? Is there any noSQL solution (maybe CouchDB?) for our case?

Thank you for taking your time with this question!


texts:
I got the following query and result currently:

mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)  GROUP BY songname ORDER BY count DESC LIMIT 5;
+--------------------------------------+-------+
| songname                             | count |
+--------------------------------------+-------+
| Meghan Trainor - All About That Bass |    67 |
| The Cars - Drive                     |    55 |
| Train - Drops Of Jupiter             |    54 |
| C.W. McCall - Convoy                 |    52 |
| The Script - Superheroes             |    48 |
+--------------------------------------+-------+

What I want to do is add a second, temporary field to the query that shows which position it is after the sort.
So the result would look like this:

+-----+--------------------------------------+-------+
| Pos | songname                             | count |
+-----+--------------------------------------+-------+
| 1   | Meghan Trainor - All About That Bass |    67 |
| 2   | The Cars - Drive                     |    55 |
| 3   | Train - Drops Of Jupiter             |    54 |
| 4   | C.W. McCall - Convoy                 |    52 |
| 5   | The Script - Superheroes             |    48 |
+-----+--------------------------------------+-------+


texts: does SELECT with implicit transaction prevent VersionStore cleanup?

texts:
The JDBC driver usually sets the implicit transaction on in every query executed by the client.
I found many SELECT statements in suspended state with implicit transaction set to ON using Version Store for 48 hour.
These selects statements didn't take up much tempdb space, but I was wondering if this might have blocked version cleanup even though the UPDATE / DELETE / INSERT statements that generated the version store rows had already been committed. (many databases use read committed snapshot isolation level)
After I killed the sessions the version store space became free.
I found the select statement using the version store with the following query:
SELECT
       db_name(spu.database_id) as database_name,
       ast.session_id,
       ast.transaction_id,
       ast.transaction_sequence_num,
       ast.elapsed_time_seconds,
       b.program_name,
       b.open_tran,
       b.status,
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb,
       loginame,
       last_request_start_time,
       last_request_end_time,
       cmd,
       lastwaittype
   FROM sys.dm_tran_active_snapshot_database_transactions ast
      JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
      JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
      JOIN sys.sysprocesses b on ast.session_id = b.spid
   ORDER BY elapsed_time_seconds DESC


texts: How do I CAST a date in a subquery? Currently getting an error: Conversion failed when converting date and/or time from character string

texts:
I have a column called Source in my table called TPR. The Source column is of datatype varchar and contains text like January 2020 and Q3 Completed. When I run a query that only extracts the date-like fields to CAST as dates like so:
SELECT CAST(Source AS DATE) AS Source
FROM TPR
WHERE Source NOT LIKE 'Q3 Completed`

I get the appropriate results. When I place the query above in a subquery to act as a filter like so:
SELECT CaseNumber
FROM TPR
WHERE Source =
     SELECT CAST(Source AS DATE) AS Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed`

I get the following error:
Conversion failed when converting date and/or time from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.

I've also tried the following but it too failed:
SELECT CaseNumber
FROM TPR
WHERE CAST(Source AS DATE) =
     SELECT CAST(Source AS DATE) AS Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed`

I don't understand why this is failing.


texts: SQL server 2012 how to apply a check constraint on multiple columns

texts:
I would like to ask if it is possible to apply a check constraint on multiple columns using one statement. I mean instead of adding a constraint for each column.

Scenario:
I have a table named 'Buyer' and contains these colums: BuyerId, BuyerName, BuyerSurname, BuyerEmail & BuyerAddress.

I need to apply a no empty string check constraints on BuyerName, BuyerSurname & BuyerEmail columns only.

Can this be done at one go instead of one constraint for each column?

Thanks for your help in advance

Kevin


texts: Php code to update the profit column in database in every 1 hour

texts:
I'm currently working web based hyip software but the problem I'm facing there is that I need a php code that will be increasing the user profit in every 1 hour but I still don't get it.
In that application, I have a table called ph_list whereby all deposit made by the user will be stored.
In that table, I have a column called the tracking_id(which is unique), Amount, Percentage...

But after making deposit, I want the amount donated by the user should be increasing in every 1 hour , which will be in percentage column....

Please I need someone to shield more light on this please!!


texts: Pgpool-II: sequences advanced by 1 after PITR recovery

texts:
I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4
In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes enabled in pgpool have old serial values.
Here is my commands:

postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'create schema sch'
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'create table sch.mytable (id serial, name text);'
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c '\d sch.mytable'
                             Table "sch.mytable"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('sch.mytable_id_seq'::regclass)
 name   | text    |
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c "insert into sch.mytable (name) values ('usera')"
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'select * from sch.mytable'
 id | name
----+-------
  1 | usera
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 2

# Online recovery via PITR (http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery)
# Recover the first standby node: standby_1
postgres@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 1 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4

# Recover the second node: standby_2
postgres@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 2 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 5
postgres@server01:~> psql -h standby_2 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6

What am I doing wrong?

I've tried to set this options without success:

black_function_list = 'lastval,currval,nextval,setval'
insert_lock = true

Resolved

Got it! Script pgpool_recvery_pitr:

psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND
datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind =
'S'" $i
fi
done

After recovery all sequences in database advance by 1.

There is no information in official docs about it:
They forgot about other enabled nodes in pgpool. This statement must be run for every enabled node in pgpool to keep all sequence points on all nodes equal.


texts: Oracle: spool data to different files depending on where condition (loop)

texts:
I am trying to get my way around something like this

declare
  filename varchar2(100);
begin
  for condition in (select fieldN from mytable) loop
    filename := 'data_for_value'||condition.fieldN||'txt';
    spool filename
    select some_data from some_table where fieldN = condition.fieldN;
    spool off
  end loop;
end;

In order to export different sections of data from a table onto different files but I just cannot get it right, I have read this and tried to do something similar in consequence:

set serveroutput on
set pagesize 0
set numwidth 2
set trimspool on

DECLARE
  filename varchar2(100);
BEGIN
  for field2 in (select field2 from blau2) loop
    filename := 'COST_TRAN_BASE '||field2.field2||'.txt';

    spool "run_query.sql" REPLACE

    select distinct 'spool "' || filename || '.txt"' || chr(10) ||
              'select field1, field3 from blau2 where field2 = ' || field2.field2|| ';' || chr(10) ||
              'spool off' cmd
    from blau2;
    spool off
    @"run_query.sql"
  end loop;
END;

But to no avail. I get the feeling that I am not allowed to use the spool command where I am trying to (I recently started working in the Oracle environment and the line between SQL*Plus and PL/SQL is still a bit blurry (you can kill me now for my ignorance :') ))

Am I just struggling against the impossible? D:

Edit:

with the initial help of Balazs Papp and my follow up investigations I came up with this system:
Using this file:

basic_script.txt
-----------------
SET PAGESIZE 0
SET TRIMSPOOL ON

spool RUN_QUERY.sql REPLACE

select distinct 'set heading off' || chr(10) ||
        'spool "BLAU2 ' || field2 || '.txt"' || chr(10) ||
                'select field1||chr(9)|| field3 from blau2 where field2 = ' ||
field2 || ';' || chr(10) ||
                'spool off'
    from blau2;

spool off
-----------

And this other file:

basic_script_condition.sql:
-----------
set feedback off
set trimspool on
set recsep off
set serveroutput on

col some_column new_value script_name
select decode((select count(*) from (select distinct FIELD2 from BLAU2)),1,'exit.sql','RUN_QUERY.sql') some_column from dual;
@&script_name
-----------

Calling them from command line in a simple .bat program:

basic_launcher.bat:
-------------
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT.txt"
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT_CONDITION.sql"
-------------

A script for exporting the different sections of data in the BLAU2 table is generated and executed only if there is data to export (if there is no data to export, basic_script would generate a .sql file with no rows selected as its content, possibly repeated N times, and the execution would fail, hence the basic_script_condition.sql controlling this behavior).


texts: What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology?

texts: What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology or is there anything similar in MySQL that can act as AE?


texts: Optimisation Newbie: How much of a 'sin' is redundancy?

texts:
I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.

I have 2 SQL tables, as such:

VOTES
vote_id (PK)
question_id (FK)
user_id (FK)
option_id (FK) <The option the user voted for>

The 'questions' table looks like this:

QUESTIONS
question_id (PK)
option_1 (FK --> Options)
option_2 (FK --> Options)

The problem I have is that I very often have to retrieve the sum of all option_1 votes (or option_2 votes) for a question. This is currently done by selecting count where question_id = [@question.id] and option_id = [@question.option_1.id]"

I'm guessing it'd be a heckuva lot quicker to just add option_1_votes and option_2_votes columns to the "questions" table and increment them each time a vote is added. But ultimately, that's redundant data.

So, as someone who's pretty clueless re: database design princpiples, what's the rule of thumb here? Would a top DBA just add in the columns, or try some other solution?

Cheers...


texts: Problems with foreign keys in a mysql database

texts:
I am trying to link both these tables with a foreign key. But I keep getting error message "ERROR 1452: Cannot add or update a child row: a foreign key constraint fails " the foreign key is pwalk_id to walk_id

Walk Table
walk_id INT(11) PK NN AI
walk_title NN walk_dur INT(11)
walk_spoint VARCHAR(20)
walk_fpoint VARCHAR(20)
walk_desc TEXT NN

Programme Table
prog_id INT(11) PK NN
pwalk_id INT(11) NN
prog_date DATE NN
prog_stime VARCHAR (9) NN
prog_max INT(11)

Edit:

CREATE TABLE `Walk` (
  `walk_id` int(11) NOT NULL AUTO_INCREMENT,
  `walk_title` varchar(15) NOT NULL,
  `walk_dur` int(11) NOT NULL,
  `walk_spoint` varchar(20) NOT NULL,
  `walk_fpoint` varchar(20) NOT NULL,
  `walk_desc` text NOT NULL,
  PRIMARY KEY (`walk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Programme` (
  `prog_id` int(11) NOT NULL,
  `pwalk_id` int(11) NOT NULL,
  `prog_date` date NOT NULL,
  `prog_stime` varchar(9) NOT NULL,
  `prog_max` int(11) NOT NULL,
  PRIMARY KEY (`prog_id`),
  KEY `Progwalk_idx` (`pwalk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


texts: After insert trigger only using single inserted virtual table row

texts:
I have a trigger that updates multiple columns in a table for rows inserted into another table.

A record inserted into the fuelTransactionInfo column, updates a column in fuelTransactions, based on the "info code", finding the correct row using fuelTransactionId. I'm just denormalizing some data for easier reporting.

The rows can be inserted into the fuelTransactionInfo column with multiple values. E.g.,

INSERT INTO fuelTransactionInfo
(...cols)
VALUES
(...values),
(...values),
(...values);

My issue is that only the first set of values is being used in the trigger. I understand that triggers are fired on the statement, not the rows, and I believe the trigger code accounts for that. However, it's simply not working.

Here is my trigger code:

ALTER TRIGGER [dbo].[fuelTransactionInfoDev_DenormalizeFuelTransactions]
   ON  [ERPDev].[dbo].[fuelTransactionInfo]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE t SET
        t.employeeNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'DRID' THEN
                inserted.value
            ELSE t.employeeNumber
        END,
        t.unitNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'UNIT' THEN
                inserted.value
            ELSE t.unitNumber
        END,
        t.contractId = CASE
            WHEN fuelInfoCodes.efsCode = 'CNTN' THEN
                (SELECT c.id
                 FROM pinContract p
                 JOIN contracts c
                    ON c.contractNumber = p.ContractNumber
                 WHERE pin = inserted.value)
            ELSE t.contractId
        END,
        t.contract = CASE
            WHEN fuelInfoCodes.efsCode = 'CNTN' THEN
                (SELECT c.contractNumber
                 FROM pinContract p
                 JOIN contracts c
                    ON c.contractNumber = p.ContractNumber
                 WHERE pin = inserted.value)
            ELSE t.contract
        END,
        t.driverName = CASE
            WHEN fuelInfoCodes.efsCode = 'NAME' THEN
                inserted.value
            ELSE t.driverName
        END,
        t.subFleet = CASE
            WHEN fuelInfoCodes.efsCode = 'SSUB' THEN
                inserted.value
            ELSE t.subFleet
        END
    FROM fuelTransactions t
    JOIN inserted
        ON inserted.fuelTransactionid = t.id
    JOIN fuelInfoCodes
        ON fuelInfoCodes.id = inserted.fuelInfoCodeId;
END

I've done a little bit of testing by adding the following statement to the trigger:

SELECT * FROM INSERTED INTO insertedTest

I see all the expected rows in the insertedTest table. I've tested the UPDATE statement by converting it into an equivalent select (based on the insertedTest table, instead of "inserted"), and all the joins are good.

Only the column in the 'UNIT' case is actually updated in fuelTransactions, which happens to be the first row in every single insert firing the trigger.

What am I doing wrong? Let me know if you need more info.


texts: Do synonyms affect remote query performance

texts:
I've got a task where I need to migrate an existing database to a new server.

The database in question has stored procedures that query another database on the same server, this second database is not going to be moved.

I would love to use a synonym to point to the other database on the linked server as this will make it really easy to set up and maintain, however I want to know if the synonym is likely to cause performance problems with the execution plan, or if it's only the cross server querying that is the problem.


texts: Update many rows in a table with a single statement?

texts:
What is the easiest way to update many rows in a table? I have a csv file that looks like this:

|primary_key |value|
|          1 |  xyz|
|          2 |  abc|
|          3 |  def|
...

Rows with these primary keys already exist in the target table

I would like to update the target table with these values. Is there syntax so that I can write something like:

update mytable set value = ('xyz', 'abc', 'def') where primary key = (1,2,3);

Looking through the MySQL Update Reference, this site (MySQL - csv update), SO (update multiple rows, multiple db updates, update multiple rows), I suspect that the answer is "no", but I'd like to confirm that this is true.


texts: How to automatically grant permissions to application created databases in SQL Server 2008 R2?

texts: So I have an application that automatically creates a new database after the database it is currently in reaches a fixed number of rows. I need to be able to automatically assign a user within the installation the db_datareader role for the automatically created database. I'm kind of at a loss here, any help is greatly appreciated.


texts: Can we Configure Change Data Capture in another Database?

texts:
I want to apply Change Data Capture(CDC) to database DB1's tables and want to log everything in DB2.

So, could it be possible to capture table DB1's changes to DB2?


texts: How can we use transaction in mongodb standalone connection?

texts:
I want to use transaction in mongodb but its told to replicaset can we perform transaction query with standalone mongodb if yes please share how to we can because when I try its give error

This MongoDB deployment does not support retryable writes. Please add retryWrites=false to your connection string

how to we perform translation without retryable writes.

Thanks


texts: MS Access database returns multiple entries for same person with more than one child

texts:
I know this is a very basic question, but I've watched about six Access database videos and still can't find the answer.  I'm a volunteer creating a membership database for a church in MS Access.  The children are in a table separate from their parents.  When I query the two tables for the data which I will then use in a Word mail merge, if a person has more than one child, that person will have more than one entry in the query results.

How do I return just one entry for a parent with more than one child?
Is it a Grouping function?

This is a basic question, for say a customer database, where a customer has more than one order, but I cannot find the answer.

Thank you.


texts:
I'm trying to do a copy database by scripting a backup and restore.  I have the backup procedure working correctly but I keep getting the error

  Conflicting file relocations have been specified for file. Only a
  single WITH MOVE clause should be specified for any logical file name.

when I run the procedure to do the restore.

Here is the procedure code.  Can anyone spot what my error is?

ALTER PROCEDURE spDBRestore (@BackupName NVARCHAR(50), @RestoreName NVARCHAR(50))
AS
BEGIN
DECLARE @BackupPath NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TemplateCopy\'+@BackupName+'.BAK',
@NewDBFileName NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@RestoreName+'.mdf',
@NewLFFileName NVARCHAR(4000) = 'L:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@RestoreName+'_1.ldf'
select @BackupName, @RestoreName, @NewDBFileName, @newlffilename
RESTORE DATABASE @RestoreName
FROM DISK = @BackupPath
WITH FILE = 1,
MOVE @BackupName TO @NewDBFileName,
MOVE @BackupName TO @NewLFFileName,
NOUNLOAD,
REPLACE;

END


texts: Error in SSMS when attempting to restore a database

texts:
We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the restore dialogue box doesn’t load):

I presume SSMS is loading backup history from msdb or something in the background and running out of space - does this imply i have excessive backup history in my system databases? If so, what is the best way to maintain it?

I have maintenance plans cleaning up the trn and .bak files on disk but i dont think i maintain any of the logs in the system databases.


texts:
I've got a table like:
╔═══╦════════════════════╦═════════════╗
║ID ║ type (enum)        ║ updated_at  ║
╠═══╬════════════════════╬═════════════╣
║ 1 ║ friend_request     ║ <date_time> ║
║ 2 ║ new_article        ║ <date_time> ║
║ 3 ║ article_read       ║ <date_time> ║
║ 4 ║ article_invitation ║ <date_time> ║
╚═══╩════════════════════╩═════════════╝

I would like to order the retrieved results by the value of the enum type.
Records with value friend_request and article_invitation for the type column should come first, then the rest. Everything together should be ordered by updated_at DESC.
Now, I know I can chain multiple order by properties, so f.e.
order by type desc, updated_at desc

will order for me by type and the result of that by the updated_at values. But how do I specify for certain values of the type column? Is that even possible?


texts: Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or configuration file someplace that stores the information for logging into the DB.  Other than the basic precaution of making sure that my database's privileges are appropriately restricted for remote requests, what options are available that I could implement in my own projects to protect this information?


texts: Selecting ALL records when condition is met for ALL records only

texts:
Sorry if this has been asked before. I couldn't find any examples.
I am trying to pull a student's course work for a semester, only if they have received a grade of 'NA' in ALL their courses. Right now, my code is only pulling any student who has a grade of 'NA' in any course. I need for them to have 'NA' in ALL courses, not just 1 or 2 courses.
My data:

Name
Course
Grade

student1
en101
NA

student1
ma101
B

student1
py102
A

student2
en101
NA

student2
ma205
NA

student2
en206
NA

student3
ma101
NA

I am trying to pull ALL rows for a student, ONLY if they have a grade = 'NA' in all their courses.
Results should be:

Name
Course
Grade

student2
en101
NA

student2
ma205
NA

student2
en206
NA

student3
ma101
NA

my code is pulling every row that has a grade of 'NA', even if the other rows don't meet the condition. I need to pull ALL rows for that record, only if it meets the condition for every row.
Seems easy in my mind...can't seem to make it work.
thanx


texts: How are block nested loop joins optimised for disk reads?

texts:
I'm trying to understand how the number of disk page reads for a block nested loop join is calculated (depending on the query) and how optimisations are applied to read in as few pages as possible.
If I have two relations (primary keys in bold)

recording(rid,made, budget)
uses(rid, name, opus, royalty)

We have buffer space enough to store 25 pages.
And an example query
SELECT recording.rid
       recording.budget
       uses.opus
       uses.royalty
FROM recording
     JOIN uses USING (rid)
WHERE recording.made = 1978

My first question is as pages, which contain records, are stored on disk, would the optimiser read in the pages which contain the columns we are selecting rather than reading all the columns in? Can the optimiser read in specific columns of a row in a page or does it have to read the entire page in first and then project out the columns it wants? If it's the former, we can read more rows in per page and hence less disk reads right?
Read in

Pages(uses(rid,name,opus,royalty))

or

Pages(uses(opus,royalty))

My second question is, if we do the block nested loop join

uses BNLJ recording

Since filtering is done on recording.made = 1978 and uses is used as the outer loop, when reading in a page of recording, does the optimiser filter out pages which are not made = 1978 first then do the join? i.e check if the page it has read into memory is a page which contains made = 1978? If so, how does the query planner calculate the number of disk page reads done? Because for every 23 pages of uses that is read in, 1 page of recording is read in so it has to store the filtered page somewhere?


texts: How to shut down clustered SQL Server 2008 R2 with NOWAIT?

texts:
Is it possible to shutdown with nowait on a clustered instance? I want to delay recovery to when the instance starts, and I want to shut down as quickly as possible without issuing checkpoints to my clustered instances. If I issue a SHUTDOWN WITH NOWAIT, SQL Server sends a SERVICE_CONTROL_STOP message which causes it to go into a Failed state. The cluster service then starts it up, which is not what I want.

Ideas?


texts:
I have a db2 LUW 10.5 database.

I want to create a trigger that saves a copy of a record into an audit table whenever, a record gets updated and inserted.

I came up with follwing syntax:

CREATE TRIGGER TR_TRANSACTION_MESSAGE_CREATE_HIST
AFTER INSERT OR UPDATE ON TRANSACTION_MESSAGE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO ....
END@

I get following error message when creating the trigger:

  An unexpected token "OR UPDATE" was found following "".  Expected
  tokens may include:  "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53

However, if I create it as an insert only trigger i can create the trigger succeesful. The same is true when creating an update only trigger.

Any idea what is wrong with the syntax?


texts: Resetting MySQL Workbench 6.3 CE password

texts: I'm a rookie using MySql Workbench 6.3 CE on Windows 10. Before I could even try to open my first data table to begin learning SQL, I've managed to forget my password. Can someone help me re-set it step-by-step? I've looked at other posts instructing me to use command line. I've tried to do that, and so far, I've had no luck. Any advice would be appreciated!


texts:
Here's a simplified recreation of what I have:

CREATE TABLE "DocumentStore" (
  "ID" int NOT NULL,
  "Content" jsonb NOT NULL,
  CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);

INSERT INTO "DocumentStore"
VALUES (1, '{ "ID": 1, "Group": 0, "RecordNumber": 0, "When": 1490280300 }');

INSERT INTO "DocumentStore"
VALUES (2, '{ "ID": 2, "Group": 0, "RecordNumber": 1, "When": 1490280400 }');

INSERT INTO "DocumentStore"
VALUES (3, '{ "ID": 3, "Group": 0, "RecordNumber": 2147483647, "When": 1490280500 }');

INSERT INTO "DocumentStore"
VALUES (4, '{ "ID": 4, "Group": 1, "RecordNumber": 0, "When": 1490280600 }');

INSERT INTO "DocumentStore"
VALUES (5, '{ "ID": 5, "Group": 1, "RecordNumber": 2147483647, "When": 1490280700 }');

INSERT INTO "DocumentStore"
VALUES (6, '{ "ID": 6, "Group": 2, "RecordNumber": 0, "When": 1490280900 }');

INSERT INTO "DocumentStore"
VALUES (7, '{ "ID": 7, "Group": 2, "RecordNumber": 1, "When": 1490281000 }');

I'm looking to break things down by Group and get a result set that looks something like this:

+-------+------------+------------+-------+
| Group |    Start   |    Stop    | Count |
+-------+------------+------------+-------+
|   0   | 1490280300 | 1490280500 |   3   |
+-------+------------+------------+-------+
|   1   | 1490280600 | 1490280800 |   2   |
+-------+------------+------------+-------+
|   2   | 1490280900 |    NULL    |   2   |
+-------+------------+------------+-------+

Start and Stop are Unix timestamps. I just grabbed ones from this morning and rounded out the numbers for simplicity.

A group will always have a "RecordNumber" = 0 entry so if there's a group, there's a start time. A group that doesn't have an entry with "RecordNumber" = 2147483647 should not have an end time.

I'm trying to summarize every Group by looking up it's Start time by finding the jsonb document for that Group that has a RecordNumber of 0 and using it's When value, and look up it's Stop time by finding the jsonb document for that Group that has a RecordNumber of 2147483647 and use it's When value, and finally a count of how many documents are in that Group.

I started with this:

SELECT
    "Content"->>'Group' AS "Group",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 0)::int AS "Start",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 2147483647)::int AS "End",
    COUNT("Content") AS "DetailCount"
        FROM "DocumentStore"
        GROUP BY "Group";

It works if all the documents in the table belong to the same Group. As soon as there are 2 groups my subqueries become a problem: more than one row returned by a subquery used as an expression. I tried adding WHERE "Content"->>'Group' = "Group" to the subqueries but it seems I can't do that (column "Group" does not exist).

I'm thinking this might be a chance to join a table with itself but I'm not sure where to begin with that.


texts: What's "referenced_table_name" in CREATE CONSTRAINT TRIGGER

texts:
I'm creating a deferred constraint trigger to make sure a parent has at least one child. There's this optional parameter referenced_table_name in the trigger definition, but its description gives no clue of its use.

https://www.postgresql.org/docs/9.5/sql-createtrigger.html

  referenced_table_name

  The (possibly schema-qualified) name of another table referenced by
  the constraint. This option is used for foreign-key constraints and is
  not recommended for general use. This can only be specified for
  constraint triggers.

I don't see how its presence affects the behavior of a trigger:

create table aaa_parent ( id int );
create table aaa_child ( parent_id int );

CREATE OR REPLACE FUNCTION ck_aaa_child__no_empty()
RETURNS TRIGGER
AS $$
DECLARE
BEGIN
    RAISE NOTICE 'trigger';
    if (TG_OP = 'DELETE' or old.parent_id != new.parent_id or (old.parent_id is not null and new.parent_id is null) ) then
        RAISE NOTICE 'differs';
        if exists(
            select * from aaa_parent where id = old.parent_id and not exists ( select * from aaa_child where aaa_child.parent_id = aaa_parent.id )
        ) then
            RAISE exception 'need at least one child for aaa_parent: %', old.parent_id;
        end if;
    end if;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SET search_path FROM CURRENT;

DROP TRIGGER IF EXISTS ck_aaa_child__no_empty on aaa_child;

CREATE CONSTRAINT TRIGGER ck_aaa_child__no_empty
        AFTER update OR delete
        ON aaa_child
        -- FROM aaa_parent
        INITIALLY DEFERRED
        FOR EACH ROW
        EXECUTE PROCEDURE ck_aaa_child__no_empty ()
;

delete from aaa_parent;
delete from aaa_child;

insert into aaa_parent values (9);
insert into aaa_child values (9);
update aaa_child set parent_id = 8;


texts:
In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user can have multiple roles, and obviously multiple users can have the same role. Now I wish to find all users who do NOT have the specific role (private_account), and I wish to list them as (user_id, new-inserted-role-id) for further processing.

SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)

However this complains that the table r does not exist.

ERROR:  missing FROM-clause entry for table "ur"

How can I do this?

So for clarity: if private_account would have id 2 and I a user 1 which does not have this ID the return would look like:

USER | role
1    | 2
2    | 2

The role reported is independent on what the user actually has. I could do this manually and do a select like below. However this has the huge disadvantage that I have to manually update the sql if I move to a different database (since the ID might not correspond to the actual role name).

SELECT u.id, 2
...

The further processing is basically using the return from the select as INSERT INTO:

INSERT INTO public."userroles"
SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)


texts: Is someone trying to hack into my SQL Server Database?

texts:
I was asked by a friend, who isn't technical at all, to help her with her application.

I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot more than that. Needless to say I've learned a lot.

I've stumbled upon the Server Logs in SSMS. To my surprise there are TONS of failed login attempts. The login attempts are primarily using the username 'sa' but there are others, for example: 'KHB', 'wwo' 'IIS' 'hpv7' 'N200user' 'kisadmin'. These usernames make no sense. I am the only one who should be logging into this database. The error is either.

Error: 18456, Severity:14, State: 8 or  Error: 18456, Severity:14, State: 5

State 8 only happens when the login attempt is for the username 'sa'.

The majority of these attempts are happening between 1am-4am.

This seems real fishy.

Any suggestions on what I should do?


texts: Database design - People and Organisations

texts:
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.

I really want to create an optimal schema for this.

I have these considerations.

A Person can have one or more Contacts (eg. phone, email)
A Person can have one or more Addresses
An Organisation can have one or more Contacts (eg. phone, email)
An Organisation can have one or more Addresses
An Organisation can have one or more Persons related to it.

I would like the App to scale decently, so the schema should fit this choice.

I am trying to achieve something like the following.

SELECT * FROM Customers + a few joins.

1. Person | NULL | John Doe | Primary Organisation Contact | Primary Address

2. Organisation | Acme Ltd | Jane Doe | Primary Organisation Contact | Primary Address

How should I create an optimal schema for relating the above?

I have attached a rough Visual Schema hierarchy - I know im way off!! and Im sure im making errors.

Is it possible to get a married set of results depending on whether its a Person or Organisation?

Joining a Person and Contact/Address to Customer is simple, but How do you join Organisation's primary Contact/Address ?

Is there any easier way to achieve my spider-diagram looking Schema.??


texts: How to set a NOT NULL column into NULL in PostgreSQL?

texts:
I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow NULL. I've tried

alter table app_client_users alter column client_id int  NULL ;

and

alter table app_client_users alter column client_id   NULL ;

which is not working. How can I fix it?


texts: Can SQL Server use a spatial index and another index at the same time and be efficient?

texts:
Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below:

  CREATE TABLE [dbo].[DigitalTerrain](
  [DigitalTerrainID] [uniqueidentifier] NOT NULL,
  [Longitude] [float] NULL,
  [Latitude] [float] NULL,
  [ZCoordinate] [float] NULL,
  [Tile] [nvarchar](10) NULL,
  [TerrainModelID] [uniqueidentifier] NULL,
  [Location] [geography] NULL,
  CONSTRAINT [PK__DigitalTerrainID] PRIMARY KEY CLUSTERED(DigitalTerrainID))

I have a spatial index, Location_IX and also an index on the TerrainModelID column. In a query like the below is having the two datasets in the same table going to cause the spatial index issues/slowdowns?

SELECT
    Location,
    ZCoordinate,
    TerrainModelID
FROM TerrainData.dbo.DigitalTerrain WITH(INDEX(Location_IX) NOLOCK)
WHERE @line.STDistance(DigitalTerrain.Location) < (@margin) AND TerrainModelID = @TerModID

Is it still able to filter out the terrain model dataset we want and use the spatial index efficiently? Or should we differ the design or query here? Should this have been split into two tables, one for each terrain dataset?


texts:
I'm trying to import a csv file circa 30GB. I have a MySql server running remotely on a vm. When i try to import the csv file I receive the following error

  is too long 1059

I'm using MySql Workbench to import the csv. I used the import tool and let it create the tables from the csv file.

I assume the error is because the data is too big for the column's datatype? Can I change it and or can it auto truncate it? What can I possibly do for this issue?

EDit:

Full error log:

Traceback (most recent call last):
  File "D:\Program Files\mysqlworkbench\workbench\wizard_progress_page_widget.py", line 197, in thread_work
    self.func()
  File "D:\Program Files\mysqlworkbench\modules\sqlide_power_import_wizard.py", line 132, in start_import
    retval = self.module.start(self.stop)
  File "D:\Program Files\mysqlworkbench\modules\sqlide_power_import_export_be.py", line 300, in start
    ret = self.start_import()
  File "D:\Program Files\mysqlworkbench\modules\sqlide_power_import_export_be.py", line 438, in start_import
    if not self.prepare_new_table():
  File "D:\Program Files\mysqlworkbench\modules\sqlide_power_import_export_be.py", line 266, in prepare_new_table
    self._editor.executeManagementCommand(""" CREATE TABLE %s (%s)""" % (self._table_w_prefix, ", ".join(["`%s` %s" % (col['name'], col["type"]) for col in self._mapping if col['active'] is True])), 1)
DBError: ('Identifier name \'//www.somesite.com/embed/44bc40f3bc04f65b7a35" frameborder="0" height="481" width="608" scrolling="no\' is too long', 1059)
ERROR: Import data file: ('Identifier name \'//www.somesite.com/embed/44bc40f3bc04f65b7a35" frameborder="0" height="481" width="608" scrolling="no\' is too long', 1059)
Failed


texts: Powershell script while executing mysqldump script

texts:
I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect
Here is the mysqldump command on my ps1 file.
$command = [string]::format("`"{0}`" -u {1} -p{2} -h {3} --default-character-set=utf8mb4 --quick --master-data=2 --single-transaction --routines --databases test1 --add-drop-database --result-file=`"{5}`" ",
        $mysqlDumpLocation,
        $databaseUsername,
        $databasePassword,
        $databaseIp,
        $database.DatabaseName,
        $saveFilePath);

But when I try to remove the {2} which is the DB password, then execute the ps1 file. When it prompt me for the password, it goes through.


texts: How can I import table records in HeidiSql?

texts:
Im using HeidiSQL and I have imported database from another one.
All table structures are imported but not records.
I want to import all records for all tables.
I have tried to import records for one table using import csv option but its inserting only one record and through error.


texts:
I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query

declare global temporary table T (reference integer, val integer) on commit preserve rows;
insert into session.T values(1, 10);
insert into session.T values(2, 20);
select first_value(val) over (order by reference) from session.T;

I am given the error

NO AUTHORIZED FIRST_VALUE NAMED  HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.18.60. 2) [Error Code: -514, SQL State: 26501]  THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE.. I am aware of the listing of error codes. However, these descriptions do not help me to understand the problem.