texts:
I  connected as sysdba and tried below:

SQL> startup;

ORA-01506: missing or illegal database name

SQL>

I tried multiple scenarios changing the Oracle_home and path variables but no luck.

These are my env variables:

c:\>echo %ORACLE_HOME%
C:\oraclexe\app\oracle\product\11.2.0\server\

c:\>echo %ORACLE_SID%
XE

c:\>path
PATH=C:\oraclexe\app\oracle\product\11.2.0\server;C:\oraclexe\app\oracle\product
\11.2.0\server\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Wi
ndows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\ATI Technologies\A
TI.ACE\Core-Static;C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_1
1_2;C:\Users\DINESH\AppData\Local\Programs\Python\Python37\Scripts\;C:\Users\DIN
ESH\AppData\Local\Programs\Python\Python37\

Also, I tried to restart the services manually but no luck.

The Content of initXE.ora is:

*.SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora',*.db_name='XE'

I tried:

SQL> create pfile='C:\temp\initXE.ora' from spfile;

File created.

SQL> create spfile from pfile='C:\temp\initXE.ora';

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2251776 bytes
Variable Size             159384576 bytes
Database Buffers           50331648 bytes
Redo Buffers                5189632 bytes
ORA-00205: error in identifying control file, check alert log for more info

Still getting an error.


texts:
Due to power failure, my server running Postgres database got crashed.

Now when I am trying to start the database, I am getting the below error.

< 2020-01-04 11:40:39.409 CET > LOG:  database system shutdown was interrupted; last known up at 2020-01-04 11:39:52 CET

< 2020-01-04 11:40:39.655 CET > LOG:  database system was not properly shut down; automatic recovery in progress

< 2020-01-04 11:40:39.657 CET > LOG:  redo starts at 1A2/8F237FF8

< 2020-01-04 11:40:39.658 CET > LOG:  invalid record length at 1A2/8F240298: wanted 24, got 0

< 2020-01-04 11:40:39.658 CET > LOG:  redo done at 1A2/8F240270

< 2020-01-04 11:40:39.658 CET > LOG:  last completed transaction was at log time 2020-01-03 07:21:31.432701+01

< 2020-01-04 11:40:39.685 CET > FATAL:  could not fsync file "pg_tblspc/16387/PG_9.6_201608131/48151/51110": Input/output error

< 2020-01-04 11:40:39.687 CET > LOG:  startup process (PID 33321) exited with exit code 1

< 2020-01-04 11:40:39.687 CET > LOG:  aborting startup due to startup process failure

< 2020-01-04 11:40:39.709 CET > LOG:  database system is shut down

is there any way to get it running.


texts:
I need to compare values of neighbouring rows in a table and get the number of times the value 42 of column calledstationid precedes the value 34. This is a system log table and calledstationid is not an auto-increment column.

I created this query but it has some problems:

SELECT COUNT(*) FROM (

    SELECT
        h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid,
        (
            SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
            AND sub.acctstarttime < h.acctstarttime
        ) as previous,

    h.callingstationid

    CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

    FROM radius.radacct_historical  AS h
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
    ORDER BY h.acctstarttime ASC;

) as total WHERE goal = 'yes'

First problem:

(
    SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub
    WHERE sub.callingstationid = '54:9F:13:27:31:7F'
    AND sub.acctstarttime < h.acctstarttime
) as previous,

The previous calledstationid is not returned properly. I get the correct value only in the first 2 results, thereafter it seems that MySQL stores the value of the result in memory and does not update the subquery.

Second problem:

CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

The column name previous in the CASE expression is not recognised.

Could anyone shed any light on how to do this?

http://www.sqlfiddle.com/#!2/774ff/42/1


texts: Does Oracle 10g Express Edition have enough storage?

texts:
I am going to develop a simple library database with less than 10 tables. Each table would only keep text records. The library has about 10,000 volumes of books and about 2,000 members.

Would the storage capacity of Oracle 10g Express Edition (5GB if am not mistaken) be enough for the library database for the next 20 years? (Assume volume of books increase at a rate of about 1,000 books per year and the number of members increase about 100 people every year).


texts: oracle streams - how to get last successfully applied scn for crashed apply?

texts:
I have a downstream Streams replication set up between Oracle 9i (source) and Oracle 11g (destination). The process is still in developlment stage, so the whole setup is not stable yet.

Sometimes my apply procedure at destination database fails. Say, with a error like this:

APPLY_NAME                     STATUS   RULE_SET_NAME                  ERROR_MESSAGE
------------------------------ -------- ------------------------------ ----------------------------------------
DWH_APPLY_SUN                  ABORTED  RULESET_APPLY_LCR_SUN          ORA-26808: Apply process AS0K died unexp
                                                                       ectedly.
                                                                       ORA-25455: evaluation error for rule set
                                                                       : STRMADMIN.RULESET_APPLY_LCR_SUN, evalu
                                                                       ation context: SYS.STREAMS$_EVALUATION_C
                                                                       ONTEXT
                                                                       ORA-00600: internal error code, argument
                                                                       s: [knlqany2lcr235], [22629], [ORA-22629
                                                                       : OCIAnyData is null
                                                                       ], [], [], [], [], [], [], [], [], []

I need to know the last source database SCN it was able to apply before failing.

DBA_APPLY.MAX_APPLIED_MESSAGE_NUMBER shows me something only in case apply process is stopped normally.

Any ideas?


texts: Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

texts:
SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B
ON A.Field2 = B.Field2
AND A.Field3 = B.Field3
AND A.Field4 = B.Field4
AND B.Field1 = "string1"
AND B.Field2 = "string2"
AND A.Field5 = (SELECT MAX(C.Field5) FROM Table1 C INNER JOIN Table2 D
                 ON
                 (C.Field5 < B.Field5
                 AND B.Field6 = D.Field6
                 AND C.Field2 = B.Field2
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'Y')
              OR
                 (C.Field5 < B.Field5
                 AND B.Field6 = D.Field6
                 AND C.Field2 = B.Field2
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'N')
              )

This query takes 0.2 sec in an Oracle database but 4 mins in SQL Server database.

Both the databases have the same indexes, the same records structure and the same column structure.

Can anyone tell me why?


texts:
I want to rename a column in a partitioned table.
Will oracle do this for all partitions?
Is there anything special for this change?


texts: Postgres fetch exact value when satisfied all conditions

texts:
Here are the table structure and sample data.

CREATE TABLE public.product (
                id serial NOT NULL,
                opid int4 NULL,
                opvalue int4 NULL,
                info varchar NULL,
                CONSTRAINT product_pkey PRIMARY KEY (id)
            );

INSERT INTO product (id,opid,opvalue,info) VALUES
            (1,1,1,'s1')
            ,(2,1,1,'s2')
            ,(3,2,1,'s2')
            ,(4,1,1,'s3')
            ,(6,3,2,'s3')
            ,(5,2,1,'s3')
            ;

Now I want query if info value satisfied all it's condition value then it will be returned.
Ex.If I pass opid=1 and opvalue=1 then only s1 should be returned not s2 because s2 have it's different condition. s1 should be returned when I pass (opid=1 and opvalue =1) and (opid=2 and opvalue =1)


texts: Automatic Failover At Always On High Avability Group SQL SERVER

texts:
after i read on documentation
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver15
i still confused about part protection level

if nodes within an FCI protection level = Instance & Failed-over resources = Server, instance, and database
if replica within an availability group protection level = Database & Failed-over resources=Database only

this mean if i choose or setup replica within an availability group if primary instance is down automatic failover is not running ?


texts: Simplifying UNION query that looks for duplicate names

texts:
create table sections(
  id serial,
  section_name char(255) not null,
  version_id int not null
);

create table section_versions(
 id int primary key not null,
 version_name char(255) not null
);

insert into section_versions(id, version_name)
values (1, 'default'), (2, 'version A'), (3, 'version B');

insert into sections(section_name, version_id)
values ('Toys', 1), ('Animals', 1), ('Cars', 1),
       ('Toys', 2), ('Animals', 2), ('Instruments', 2),
       ('Toys', 3);

I need to select sections based on requested section_version.version_name.

If the version_name is "default", then query just need to return all sections which have "default" version.

But if the "version A" was requested, then it should return every section that belongs to "version A", and add a missing sections from "default" version - basing on section_name.

Please see this fiddle: http://sqlfiddle.com/#!15/466e1/1/0

Here's what I came up with:

select * from sections
join section_versions on (section_versions.id = sections.version_id)
where section_versions.version_name = 'default'

and sections.section_name not in (
  select sections.section_name from sections
  join section_versions on (section_versions.id = sections.version_id)
  where section_versions.version_name = 'version A'
)

UNION

select * from sections
join section_versions on (section_versions.id = sections.version_id)
where section_versions.version_name = 'version A'
;

This is probably a naive try, so I am looking for a better solution.

It would be nice to have one query that will handle:

Selecting only "default"
Selecting a specific version
Working when no default version exists (f.i. like Instruments)


texts: user security inheritance in SQL Server 2016

texts:
I'd like to build a bunch of new databases (several dozen) in SQL Server 2016.  However, I'd like all users to access a single database (called SECURITY for now), and apply security to that one database, where each user would be given access to a single schema for their department.  Does SQL Server 2016 allow inheritance of security, so you don't have to build those users in each database, and apply the same security in each?  Some of these schemas will just have views in which the definitions that read schemas and tables in other databases.

A few of these schemas in DEV_SECURITY will allow read/write and drop/create on views, tables, and stored procedures, for SECURITY, but also one other database.

Does this require me to build out these users, and their security in all databases, or can I simplify by just building them in SECURITY?


texts: MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

texts:
I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time.

My simplified tables look like this:

Items
------------------
|key  | title    |
------------------
|1    | Sproket 1|
|2    | Sproket 2|
------------------

And

Prices
----------------------------------------
|item_key | price | datetime           |
----------------------------------------
|1        | 2.99  | 2014-11-25 02:05:56|
|1        | 1.99  | 2014-12-13 02:05:56|
|2        | 9.99  | 2014-10-25 02:05:56|
|2        | 8.99  | 2014-11-13 02:05:56|
----------------------------------------

The Items table contains a single row for each item.  The Prices table contains a single row for each price change.  The script runs daily and checks for the current prices, if the current price is different than the most recent item price recorded in Prices than a new entry is made in Prices.

My question is, How do I calculate the average price of an item over 180 days?

I can't use AVG because there is not a price entry each day for each item.


texts: Oracle XMLAGG issue with combining cells that have comma separated values in cells

texts:
Have a situation where I'm trying to use xmlagg to get:

     | ID | name
row1 | 1  | apple
row2 | 1  | apple,pear,banana
row3 | 1  | apple,banana
row4 | 1  | pear,banana,orange
row5 | 2  | orange
row6 | 2  | orange,pear
row7 | 2  | pear, banana

to:

1 | apple,pear,banana,orange
2 | orange,pear,banana

Is this possible using xmlagg?
Thanks


texts: postgresql random function returns duplicate results when order by random used in query

texts:
I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column:

postgres=# SELECT random() as sortOrder, random() as col2;
     sortorder     |       col2
-------------------+-------------------
 0.607938482426107 | 0.121234225574881
(1 row)

But if I add an ORDER BY clause, as shown below, then random returns the same random number each time for every row.

postgres=# SELECT random() as sortOrder, random() as col2
               FROM generate_series(0,1)
               ORDER BY sortOrder;
     sortorder     |       col2
-------------------+-------------------
 0.100375576410443 | 0.100375576410443
 0.170669795479625 | 0.170669795479625
(2 rows)

I'm assuming there is a technical reason for this, but is it possible in this last query to get a different random value for each column?

Edit:
I may have oversimplified in my original question. The actual query I was attempting was more like the following:

SELECT column1, random(), column2, random()
               FROM table1
               ORDER BY random();

So, having an answer that can be combined with a table query is also important.


texts: INSERT don't work with NOT NULL but UPDATE does, why?

texts:
I have this table definition:

CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brands_id` int(11) DEFAULT NULL,
  `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci NOT NULL,
  `thumbnail_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `media_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `media_code` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `inactive` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `titleHtml` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `descriptionHtml` longtext COLLATE utf8_unicode_ci NOT NULL,
  `useHtml` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_6A2CA10CBF396750` (`id`),
  KEY `IDX_6A2CA10CE9EEC0C7` (`brands_id`),
  CONSTRAINT `FK_6A2CA10CE9EEC0C7` FOREIGN KEY (`brands_id`) REFERENCES `brands` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

Each time I try to INSERT a new record I get an error due to NOT NULL constraints, that is OK, now if I try to UPDATE the same record and I leave title and/or description or any other required field empty the UPDATE is executed and the column is set to blank, why? How I can avoid this? I am using MariaDB 5.5.46.

UPDATE

As requested by moderator below is the INSERT and UPDATE queries I have tried:

INSERT INTO media (media_type, title, titleHtml, description, descriptionHtml, thumbnail_url, media_url, media_code, inactive, useHtml, createdAt, updatedAt, brands_id) VALUES (null,"Media Title Test",null,null,null,"https://someurl",null,null,false,false,"2015-10-22 22:07:54","2015-10-22 22:07:54",null)

The INSERT above fails because the CONSTRAINTS in the DB

UPDATE media SET description = null, updatedAt = "2015-10-22 22:12:18" WHERE id = 13

The UPDATE above pass and this is the problem because description column isn't allowed to have NULL values


texts: Mongodb throwing an error when I run the query to record last position from oplog

texts:
I am getting an error when I run the query in the secondary member to record the last position from the oplog.

below is the query.

db.oplog.rs.find().sort({$natural:-1}).limit(1).next().ts

and the error I am getting is

2019-05-20T12:49:26.566+0000 E QUERY    [thread1] Error: error: {
        "operationTime" : Timestamp(1558355938, 1),
        "ok" : 0,
        "errmsg" : "not master and slaveOk=false",
        "code" : 13435,
        "codeName" : "NotMasterNoSlaveOk",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1558356558, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCommandCursor@src/mongo/shell/query.js:725:1
DBQuery.prototype._exec@src/mongo/shell/query.js:117:28
DBQuery.prototype.next@src/mongo/shell/query.js:307:5
@(shell):1:1

can someone help me to get out of this issue?


texts: SSIS ETL or CDC - real time DWH reporting

texts:
I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future)  from OLTP system to a Reporting database residing on a different server.

The request is for real time data sync between the two sources.

Should I continue and test it with just regular SSIS -> Staging -> merge route OR should I look into implementing CDC. Will that be faster than plain old ETL considering the number of tables involved?


texts: MAX function and group by usage - postgresql

texts:
I am building a ranking algorithm using IMDB's formula:

https://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268

The formula for calculating the Top Rated 250 Titles gives a true Bayesian estimate:

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:

* R = average for the movie (mean) = (Rating)
* v = number of votes for the movie = (votes)
* m = minimum votes required to be listed in the Top 250 (currently 1300)
* C = the mean vote across the whole report (currently 6.8)
for the Top 250, only votes from regular voters are considered.

The problem I am having is that I am not sure how to get the AVG and MAX from the whole result set. SQLFiddle here

I am guessing it has to do with the GROUP BY clause.

Also, how can I set "business 4" ranking to 0 instead of null?


texts: dbvisualizer: number of rows limited by maxrows

texts:
I'm running a simple query on an Oracle database table like

select * from tableA

and DBVisualizer cuts the query short at 1000 rows.  How do I remove this limit?
Message is "number of rows limited by maxrows".


texts:
We have an accounting program with an SQL Server database and would like to start a Ukrainian version. I am not sure which collation to use: Ukrainian or Cyrillic_General?

With a non-technical Ukrainian, we compared the collation charts for Cyrillic_general and Ukrainian and found only minor differences towards the end of the table. That person thinks Cyrillic_general should do fine.

Also, SQL Server 2012 no longer lists the Ukrainian collation. Is this a reason for concern?

Any advice which to choose?


texts:
I would like to know the differences between SSRS 2016 and Report Builder 2016
which one can be use for implementing AD security


texts: Should I add a column or 1-1 table if property is usualy null

texts:
There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%).
We thought it's better to add a new table which will be 1-1, than to add a new column which will have Nulls 99% of the time.

Is a new table the preffered way?


texts: How to filter a character followed by numbers string pattern in PostgreSQL?

texts:
Back in SQL Server, I use the command below to get the matching string pattern of #1234 from the column called Number
SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-9][0-9]'

Now in Postgres, I have tried using the command below, but no luck:
SELECT m.Number
FROM Boats AS m
WHERE m.Number ILIKE '#[0-9][0-9][0-9][0-9]'

Any idea what pattern expression should I use to get the correct filter?


texts: Convert String date format in Jalali to Gregorian

texts:
Hello I have The data table in oracle SQL 12c with 2 col one is thetime , and another is theh the data stored in cols are varchar and format like this :
THETIME | THEH
14000207| 1245
14000207| 1230
14000207| 1215
...

which the 14000207 tell is 1400(yyyy) , 02(MM) and 07(dd) and in THEH 12(HH) and 45(mm) I need to convert it to date format with time for better selecting the data but it's a jalali date and Before do that I need converting to Gregorian , so how Can I do that?
ps :
the 1400-02-07 equal to 2021-04-27


texts:
The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366.

Later I dumped table with mysqldump, droped table, then created again and imported data. Now Avg_row_length is 471.

| Name       | Engine | Rows     | Avg_row_length | Data_length | Index_length | Data_free  |
+------------------------------+--------+---------+------------+----------+----------------+-

          before
| calls_old  | InnoDB |  1185666 |           7366 |  8733835264 |   4875419648 | 5329911808 |

         after

| calls_old  | InnoDB |  1277654 |            471 |   601882624 |    464175104 |    4194304

What causes this behavior?


texts: Postgresql partitioned table timestamptz constraint problem

texts:
The table reports is partitioned table by day like reports_20170414,reports_20170415

Constraint SQL is defined as follow

CHECK (
    rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time zone
    AND
    rpt_datetime < '2017-04-15 00:00:00+00'::timestamp with time zone
)

Lets consider two type of query

SELECT SUM(rpt_unique_clicks)
    FROM reports WHERE rpt_datetime >= '2017-04-14 00:00:00';

Above query runs within sub-seconds and everything is fine.

SELECT SUM(rpt_unique_clicks)
    FROM reports WHERE rpt_datetime >=
 date_trunc('day', current_timestamp);

On contrary, above query runs at least 15 seconds.

SELECT date_trunc('day', CURRENT_TIMESTAMP), '2017-04-14 00:00:00';

returns

2017-04-14 00:00:00 +00:00 | 2017-04-14 00:00:00

When I examine why the latter runs long (using explain analyze), I finished the result that it visits and scans every table (~500), but the former visits only reports_20170414 so there is problem with constraint checks.

I want to query for today, without using prepared statements as the latter query does. Why date_trunc('day', CURRENT_TIMESTAMP) is not equivalent to  2017-04-14 00:00:00 ?


texts: Problem joining log-shipped secondaries in STANDBY to AlwaysOn group

texts:
I have a configuration where one database is log shipping to three different servers hosted in a disaster recovery site.  These three disaster recovery servers are joined to the same AlwaysOn availability group (AG).

In the event of failover, we recover the database on the server acting as the primary replica of the AG and then add the database to the AG using the 'Join only' synchronization option.  Since the databases on the secondary replicas are already in a non-recovered state, the operation succeeds and we end up with a database synchronized across the AG.  This is 100% great.

New problem:  Our monitoring software does not like it when databases are not in a readable state.  So while we are in our primary site and are log shipping to our NORECOVERY secondaries at the disaster recovery site, our monitoring software opens high-priority tickets because it thinks the secondary databases are down (because it can't read them).

Making the secondaries readable by switching them from NORECOVERY to STANDBY solves this issue, but creates a new one.  When we failover to the disaster recovery site and try to add the database to the AG (as outlined above), it fails because the databases on the secondary replicas need to be in NORECOVERY in order to successfully join the AG.

If we switch these databases from STANDBY to NORECOVERY before attempting to add the database to the AG, we receive a message saying the databases on the secondary replicas are not restored far enough in order to be joined to the AG and the join fails.  If at this point, we take a transaction log backup of the database on the primary replica and apply it to the secondaries with NORECOVERY, we can re-initiate the join procedure successfully.

It would seem that changing the secondaries from STANDBY to NORECOVERY is causing the engine to determine the databases are no longer in sync but I can't for the life of me figure out why.  Anyone have any ideas?  The only thing I can think of is that the act of recovering the primary database itself was enough to bring them out of sync, but if this were true, shouldn't it also be the case when we simply leave the secondaries in NORECOVERY to begin with (like our original plan)?


texts: Installing SQL Server Express in parallel of Standard

texts:
On a server I have a SQL Server 2008 r2 standard performing very bad.

To make a quick & dirty test I would like to try to install express edition.

This server is not administrated by me, it is a server of a customer.

A stupid query is not running on that standard SQL Server, it takes 45 minutes to execute, while on my laptop it takes 2 seconds with the same db.

So I suspect either SQL Server is corrupted or windows is corrupted (other ideas?).

This is why I would like to try to install express (to bypass "SQL Server is corrupted").

Is it possible to install express when standard is there?


texts: How to get data from different table having same column?

texts:
I have a database which stores historical data in different tables with same column names. Is there any way I can query all tables and get result

Example

2016_01_01----Fields-f1,f2,f3,f4----Values-1,2,3,4
2016_02_01----Fields-f1,f2,f3,f4----Values-5,6,7,8
2016_12_01----Fields-f1-f2-f3-f4----Values-9,10,11,12

Result should be

f1   f2    f4
1    2     4
5    6     8
9    10    12


texts: How to count the rows from 1 table but from two different column

texts:
SELECT distinct Major, COUNT(Major) AS MajorCount FROM
[College Management System].[dbo].[ApplicantCourseDetails]
group by Major

SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM
[College Management System].[dbo].[ApplicantCourseDetails]
group by MinorI

Result should be like this.

╔═══════════╦════════════╦════════════╦════════════╗
║   Major   ║ MajorCount ║   MinorI   ║ MinorCount ║
╠═══════════╬════════════╬════════════╬════════════╣
║ Botany    ║ 1          ║            ║            ║
║ Chemistry ║ 2          ║ Chemistry  ║ 2          ║
║ English   ║ 2          ║ English    ║ 2          ║
║ History   ║ 2          ║ History    ║ 1          ║
║           ║            ║ Philosophy ║ 1          ║
║           ║            ║ Zoology    ║ 1          ║
╚═══════════╩════════════╩════════════╩════════════╝


texts: How to map single-products to categories in my scenario?

texts:
I have the tables depicted in the following diagram:

Let's say I have only two products in my database at moment, one that has variants, while the other does not have them.

Now my question is: how do I map my Product WITHOUT variants to a CATEGORIES in the PRODUCTS_TO_CATEGORIES_MAPPING table?


texts:
In our environment we have some servers that are in an Always On Availability Group, and some that are standalone.

We normally backup to a network share, but we have recently observed that as the databases are growing bigger the time taken is getting longer, which slows down the whole network.

Ola hallengren's script is being used with compression and also splitting the backup files. I am only performing daily "full" backups. The backups are going to the network share EMC isilon drive.

I am never comfortable with EMC DD Boost. The only alternative is to do a local backup and then copy to the same network share.

Is there an efficient way other than the above?


texts: I have a general question about SQL Server 2008 table(s) design.  We currently have a table that is over 600GB and grows at about 3GB a day.  This table has the appropriate indecies but is becoming a major hangup when running queries and just because of its size.  The question is should I split the table into multiple tables by year and month (this would fit how other departments split their large data sets up) or should we leverage the partitioning that is built into SQL Server.  It appears that using the partitioning would require less code changes.  From what I read when partitioning you still just query one table and the server handles how to get the data.  If we went the multiple table route, we would have to handle pulling data from multiple tables.


texts:
I am not a guru of SQL Server, I'm just studying it, and I have the following question:

Should I use UPDATE STATISTICS option?

I want to know something from your experience is it good practice and it is better to do it on a regular basis or it is better don't even touch it?


texts: How do I query the running pg_hba configuration?

texts:
I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql shells on both nodes)

For the non-replication connection, I would connect psql using a connstring like 'host=$MASTER_IP port=5432 dbname=$DATABASE user=$DBUSER password=$DBPASSWORD'

Context:
I am writing a script to automate the setup of replication between servers, and configuration of the servers is managed through different systems/repositories (legacy reasons). Therefore, I want to test if settings are all right at each step.


texts:
I pretend to show a personalized message at login in MariaDB ver. 10.1.37 , in accordance with the data in one table.

My idea was to use a stored procedure as shown in fromdual.com but i can't find the my.cnf in my disk (I'm using Xampp), so i created the file and added SET GLOBAL init_connect="CALL audit.login_trigger()"; but now I have this error:

  error: Found option without preceding group in config file:
  C:\xampp\mysql\my.cnf at line: 1 Fatal error in defaults handling.
  Program aborted

Q1)What preceding group should I type?
In this pdf from fromdual there is no details on this topic.

Q2) is there any other method? I have look in to this question but I need to check the tables to create the message

This same question has been made in server fault, but I think that this site is more adequate.


texts: PostgreSQL Automatic Failover (Trigger File Creation)?

texts:
Is there a way to detect if the master PostgreSQL (9.3) server becomes unresponsive, and if so, create the trigger file (that is specified in recovery.conf) that initiates the failover process?

Or more specifically, is there some kind of ping or keepalive command that you can use from the slave to see if the master is responding?  If so, this can probably be implemented with a shell script rather easily.  I have not yet found a command that does something similar to this.  Any help is appreciated, thank you.


texts:
Being a newbie to database migration, can I script all the database jobs one by one and run them against a new server to create these jobs?

If not, why do we use these SQL job scripts?

Thanks in advance.


texts: Postgresql - get relation name from relid or RelOptInfo data structure

texts:
I want to get the relation name for a given relid or RelOptInfo data structure.

For some experiment I need to fetch the relation name while processing a given query inside postgresql. is there some way of doing this?


texts:
I have a mariaDB with a table that is corrupted. It's useless data to me, so I've tried to drop the schema but it won't drop. Instead, it gives me this error when trying to drop it:

"Error Code: 1805. Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted."

I've read on here that I should try to run a mysql_upgrade to fix this issue. But this also has failed when I point to the MariaDB bin directory and run this command line:

C:\Program Files\MariaDB 10.2\bin>mysql_upgrade --force -h localhost -uroot -p -
P3307

This is the results I get from it:

    Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_executed                                OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1408 (HY000) at line 545: Event Scheduler: An error occurred when initiali
zing system tables. Disabling the Event Scheduler.
ERROR 1805 (HY000) at line 572: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 574: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 582: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 584: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
FATAL ERROR: Upgrade failed

Any advice on how to resolve this?


texts: SQL Server 2005 to 2012 Replication, cannot access replicated data

texts:
I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes several minutes after that I got error. It looks like table is locked.
My Observations

Replication monitor shows everything is OK.
Distribution service is running on MS SQL Server 2005.
I see replication files created on network share.

Why can I not access the table ?


texts: Error Referencing wp_users as Foreign Key

texts:
I'm fairly new to SQL. I'm trying to create a FK for one of my tables (time_sheet) to one of the Wordpress tables (wp_users). But I'm hitting an error and I'm not entirely sure why that is.

This is my SQL statement to create the table time_sheet:

Create table Time_Sheets
(
 id bigint AUTO_INCREMENT NOT NULL,
    project_id varchar(255) not null,
    username varchar(250) not null,
    task varchar(255) not null,
    description varchar(255) not null,
    hours int(10) not null,
    time_date varchar(255) not null,
    PRIMARY KEY (id),
    FOREIGN KEY (project_id) REFERENCES Time_Sheet_Projects(project_id),
    FOREIGN KEY (task) REFERENCES Task_List(name),
    FOREIGN KEY (username) REFERENCES wp_users(display_name),
    CHECK (hours > 0)
)

Here's an image of the Wordpress database schema:

When I take out this line FOREIGN KEY (username) REFERENCES wp_users(display_name), the query run perfectly fine.

I've check the name/column name and they are correct. I'm not sure why this isn't working.

show create table wp_users

CREATE TABLE `wp_users` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_status` int(11) NOT NULL DEFAULT '0',
 `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`ID`),
 KEY `user_login_key` (`user_login`),
 KEY `user_nicename` (`user_nicename`),
 KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Edit: So it turned out the database engine being different was the problem. After switching wp_users to InnoDB, everything is working nicely.


texts: Change the row position of a record without messing anything else MySQL?

texts:
Hi I would like to ask if there is a smart way to change the record position of a table in mysql. My problem is this: I have a table with statistics data and when I recover all the details in a table called "details" I get the following result:

id | name
 1 | Residents
 2 | In age from 0 to 6
 3 | In age from 6 to 14
 4 | In age from 14 to 30
 5 | In age from 30 to 66
 6 | Total school population
 7 | Students in kindergarten
 8 | Students in high school
 9 | ...
10 | ...
11 | 66 years and over

I would like the 11th record to be right after the 5th, thus right after "In age from 30 to 66". Is there a smart way to do that?

Thanks!


texts: Can you create an unrecoverable SQL Agent back log?

texts:
I have created and am using an SQL Agent for the first time. I have searched google but either the info isnot there or I am not getting the keywords correct (SQL agent + back log/callstack overflow etc !).

My agent reads in and does some processing on a (variable) volume of data. I observed an error recently where i deadlocked a database i think due to an issue with the SQL agent and it started me thnking about the following: If I set the schedule frequency to be shorter than the duration that it took to compete the action that is performed by the agent, such that the 2nd action is scheduled while the first is still processing ...and so on. Will this generate an unrecoverable situation by accumulating jobs that it will never have time to process or if the agent starts while a previous job is still running does it just abandon the attempt and check again at the next scheduled interval.

Obviously one wouldnt want to do the above on purpose but it just got me thinking about whether i need to handle for the above possibility.

Cheers


texts: Selecting default value query returns blank

texts:
I am trying to select the default value if a row do not have a value with this snippet
1
SELECT IFNULL((SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1) ,'lorem ipsum');

When i run the query,it returns a blank and so is when i
2
SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1

Why is my first query returning the text i want as default?.


texts: Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4?

texts:
I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8?

You can see this in the MariaDB test suite, and and the MySQL test suite; or, by using SHOW CREATE TABLE mysql.db;


texts: MS Access : Navigation pane is suppressed

texts: I received a MS Access form client. It is in access 2007 format. Initially Navigation Pane was not available. Only form was visible. Then I choose options->Current database->checked Display Navigation Pane check box. It is visible now. But It is suppressed and not able to resize or access it as shown in below screenshot. I checked the code by pressing Alt+F11. I could not find script related to navigation pane. Please advise.


texts: How can I write a window function using something like row_number to increment only on discrete values?

texts:
Say I want to do something like:

select
     my_date,
     row_number() over(partition by my_date order by my_date asc)
from
     my_table

I want the row numbers to come out as:

2018-10-01,1
2018-10-01,1
2018-10-02,2
2018-10-03,3

Not quite sure how to do this. With the current window function, it does:

2018-10-01,1
2018-10-01,2
2018-10-02,1
2018-10-03,1


texts: MySQL Cluster -- Storing table data on data nodes

texts:
Possible Duplicate:
  MySQL Cluster — Storing table data on data nodes

I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm and run SHOW at the prompt and everything seems to look OK, with the following headers.

[ndbd(NDB)] 2 node(s)
[ndb_mgmd(MGM)] 1 node(s)
[mysqld(API)]   1 node(s)

When I run mysql -uroot on the mysql server and create a database and populate a table with data I notice that the database directory and table files are being created in the local datadir, not the datadir on my Cluster's data nodes.

I have set /var/lib/mysql-cluster/config.ini to point at the data nodes and their datadir. The /etc/my.cnf file looks like this on all 3 servers. I have hidden the DNS, both are the same

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

Why is the mysql table data files being stored locally? Shouldn't the data files (.ndb and .frm files) be on the data nodes only?

Thanks!


texts: Do I need to RUNSTATS after a REORG in DB2?

texts:
The REORG documentation says:

  BUILD

Builds indexes. Updates index statistics.

But I have always heard REORG/RUNSTATS as if they should be paired.  Can anyone verify that "Updates index statistics" achieves the same thing as RUNSTATS?


texts: How can I best combine 3 queries into 1?

texts:
I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two queries against each result):

post_id from wp_postmeta
brandLogoName from brand

Here are the tables and their relevant columns:

merchant - brandIdentifier, locationIdentifier, locationName, postalCode
wp_postmeta - post_id, meta_key, meta_value
brand - brandIdentifier, brandLogoName

Currently I run the following and load it into an array:

SELECT `brandIdentifier`,`locationIdentifier`,`locationName` FROM merchant WHERE postalCode = '90210';
GROUP BY `locationName` LIMIT 0, 5

Which returns:

brandIdentifier  locationIdentifier locationName
11111111         666666             ABC
22222222         777777             DEF
33333333         888888             GHI
44444444         999999             JKL
55555555         000000             MNO

Then for each line in the array I run the following (using the first result as an example):

SELECT post_id FROM wp_postmeta WHERE meta_key = 'locationIdentifier' AND meta_value = '666666' LIMIT 1;

Which returns:

post_id
123456

Then for each line in the array I run the following (using the first result as an example):

SELECT brandLogoName FROM brand WHERE brandIdentifier = '11111111' LIMIT 1;

Which returns:

brandLogoName
file.png

I know joins are required. I just don't know how to join to a result set. Can anyone point me in the right direction on writing this query? Thanks.


texts: Make a database inaccessible through "sa" user but should be accessible through another user

texts:
I have a database BugTracker which is accessible from "sa" user.

I have just created new user AdminBugCatcher. I have also mapped "BugTracker" database with this user. But this database should not be accessible through "sa" user.

How can we achieve the same ?

I have already tried to unlink this database from "sa" user but getting an error "Drop failed for the User "dbo" ".


texts: Query to list all the databases that have a specific user

texts:
I need to form a query that lists all the databases that include the words 'PRD' that have a single specific user name 'user_1'
So far I have this query:
select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
and name = 'user_1'
order by username;

The above query will require me to do "use database name" for each database. How can I include all the databases that contain the word 'PRD' in it?
Any help is appreciated.
EDIT:
How can I combine the above query with the query below?:
SELECT name FROM master.sys.databases
where name like '%PRD%'


texts: How to uninstall pgAdmin4 on Ubuntu 16.04?

texts:
I successfully installed pgAdmin4 with

pip install pgadmin4-2.0-py2.py3-none-any.whl

But afterward, pip list doesn't show anything related to pgAdmin, so I don't know what argument to pass to pip uninstall <package_name>.

If i do pip uninstall pgadmin4, i get:

  Cannot uninstall requirement pgadmin4, not installed

Should I just delete the virtual environment directory where I installed pgAdmin?


texts: group several columns together in materialized view

texts:
For statistical reasons, I need to group several ids in a single column of a materialized view.
This should form a list of ids from other tables with an info. Further processing will be done on this view. The goal is to save seconds of processing by first generating this view instead of querying all the tables (about 10) one by one.

But I don't understand if I can create a new column name with view or only select one in existing table.


texts: MySQL creating a concat_WS delimiter function, one part not working

texts:
I have a function that looks like this:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len INT UNSIGNED;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len <= 0 END REPEAT;
   return erg;
END;

I use the function like this:

WHEN CHARACTER_LENGTH(mac) = 12
    THEN
        CONCAT(CONCAT_UNTIL(UPPER(mac), ':', 2), ',')
END AS fullMac

So the field "mac" looks like this:

555FFFEE1001
555FFFEE1004
555FFFEE1005
555FFFEE1006
555FFFEE1007
555FFFEE1008
555FFFEE1009
555FFFEE1019

So I expect to see this:

55:FF:FE:E1:00:01,
55:FF:FE:E1:00:04,
55:FF:FE:E1:00:05,
55:FF:FE:E1:00:06,
55:FF:FE:E1:00:07,
55:FF:FE:E1:00:08,
55:FF:FE:E1:00:09,
55:FF:FE:E1:01:19,

But instead when I run the function I get this:

55:FF:FE:E1:00:1,
55:FF:FE:E1:00:4,
55:FF:FE:E1:00:5,
55:FF:FE:E1:00:6,
55:FF:FE:E1:00:7,
55:FF:FE:E1:00:8,
55:FF:FE:E1:00:9,
55:FF:FE:E1:01:9,

I've been messing around with all sorts of different fields, but haven't had success.  What is my logical error and can someone help me with the correct function call?


texts: MS Sql Server Management Studio gives error on start

texts:
When i try to connect my sa account on sql server management studio it gives me this error:

*ps: the error message is turkish but you will see english version in 3rd image

Then i clicked on "Get Help For" thing as its shown above. It showed me this:

So i clicked to "Yes" button. Then it opened web page below:

*ps: error message is the same in first image but its english.

After that, i tried to start sql server process in services windows regarding 3rd image. But it gave me this error message:

It means "Windows couldn't start SQL SERVER service on local computer. For more information see event logs. If this is not a Microsoft service, call provider and give this special error code: 3417"

I wanted to look system event logs from C:\Windows\System32\winevt\Logs directory but there was 84 different .evtx files, I couldn't know what to do. How can i fix this?

*ps: I'm also not little bit confused about this problem's scope. I also thought to post this on superuser and/or stackoverflow but this place looked more convenient to me. So, if i'm mistaken please warn me.

edit:

I opened Event Log application in Control Panel\Administrative Tools according to Jon Siegel's suggestions and filtered all event logs from MSSQLSERVER source. There are 2 different error event.Below you can see event properties and link for event log online help documents.

Error 1:

Event Log Online Help Document for Error 1: Link

Error 2:

Event Log Online Help Document for Error 2: Link 2


texts: Accessing impact of deleting ibtmp1 file in MYSQL

texts:
There is a file called ibtmp1 and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please share the command or the process. Please note that I am using Windows system and MySQL Server version is 5.6

thank you.


texts: What does the GO statement do in SQL Server?

texts:
The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly.
I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use it?
And what is the difference between semicolons ; and GO at the end of a query?


texts: Does it make sense to store a couple of Boolean values as array?

texts:
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent Postgres features (e.g. gin indexes) from being used?


texts: mysql select query to select from single table with multiple WHERE clauses

texts:
i am new to database querying so apologies for this rudimentary query question. thank you in advance.

table called "neuron"
columns in "neuron" called "neuron_id", "neuron_name" + others (not relevant here)

neuron_id is unique identifier for each neuron_name;
say there are 5 entries, thus
neuron_id = 1,2,3,4,5 and corresponding
neuron_name = a,b,c,d,e

i would like to generate a list that contains neuron_id for select neuron_name. the following query does not work for me.

select neuron_id, neuron_name
from neuron
where neuron_name = "a" or "c" or "e"

i expect the following

1,a
3,c
5,e

but the above query only spits out

1,a


texts: Reusing covering index for fields in FROM, from another query's index

texts:
For the following query I have a covering index as so:

SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent;

ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id);

Now I have another query that I would like to add a covering index for:

SELECT user_id FROM foo WHERE bar_id=:bar_id AND date_sent=:date_sent;

Does the previous covering index also cover this new query? When I run EXPLAIN for the new query the following output is returned:

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | ref  | bar_id        | bar_id | 4       | const |    5 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+

I see that the key mentioned is bar_id, but that seems to me to show that this is not a covering index. Furthermore, the Extra field does not show the expected Using index output that would assure me that a covering index is in use.


texts: Restart the active node services without failover

texts: On a 2 node Windows Server Failover cluster configuration, how do I restart the SQL Server services without failing over to the other node?


texts: How to update a SET statement so I can use the MIN() function?

texts:
I have an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use the MIN() function on a few columns. When I tried to add the MIN() function to the statement as it exists I received the error

  An aggregate may not appear in the set list of an UPDATE statement."

Here is my existing code for the UPDATE statement:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE
                        WHEN  ((CONVERT(int, DTE.StrtTm) + 800) > 2400) AND (CONVERT(INT, DTE.StartBreak) < 1600) THEN ((CONVERT(INT, DTE.StrtTm) + 800) - 2400)
                        ELSE (CONVERT(INT, DTE.StrtTm) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND DTE.StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, DTE.StartBreak) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

Example inputs:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   NULL
 1/1/11    1     1200   1430

Goal results:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   1430

I am not able to change the table schema so please don't come in here tell me I should redesign the entire then so it is setup properly, I don't have that luxury.

Code I tried that produced the error:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE
                        WHEN  ((CONVERT(int, MIN(DTE.StrtTm)) + 800) > 2400) AND (CONVERT(INT, DTE.MIN(StartBreak)) < 1600) THEN ((CONVERT(INT, MIN(DTE.StrtTm)) + 800) - 2400)
                        ELSE (CONVERT(INT, MIN(DTE.StrtTm)) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND MIN(DTE.StartBreak) IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, MIN(DTE.StartBreak)) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);


texts:
SELECT ndo.sku ParentSKU, visitsWhenSKUWasOnline.s
FROM
Temp.NumberOfDaysOnline ndo
JOIN
(
    SELECT SUM(gasessiondata.sessions) as s
    FROM gasessiondata
    WHERE
    gasessiondata.date >= ndo.FromDate
    AND
    gasessiondata.date <= ndo.ToDate
  ) as visitsWhenSKUWasOnline
  ON 1=1

I can't use ndo.fromDate and ndo.ToDate inside of the subquery since hive does not support correlated sub queries. What could be an alternative for something like this?

Edit:

down vote
favorite
I have a table like this:

date          val

2016-1-1       8
2016-2-1       10
2016-1-2       30
2016-1-3       30

Now, I have two dates coming in from a different table, let's call them `fromdate` and todate

If fromdate was 1, 2016-1-1 and Todate was 2016-1-2, I need 8+30 = 38 as the final value

I need to check

SUM(val) of all those values dates corresponding to which are between the fromdate and twodate.


texts:
Mysql Dump Taking longer time

I have 188 databases, which I am individually dumping in a loop using a shell script as below

 echo "$(date  +%b-%d-%y) Dumping Started at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
for db in $databases; do
        echo "START : Dumping $db start at $(date +%r)" >> $DEST/details.logs
        if [[ "$db" != "information_schema" && "$db" != "performance_schema" ]]; then
                if ( mysqldump -u $USER -p$PASSWORD --databases $db > $DEST/$DATE.$db.sql) then
                        gzip $DEST/$DATE.$db.sql
                        echo "SUCCESS : Dumping $db" >> $DEST/details.logs
                        echo "FINISH : Dumping $db finished at $(date +%r)" >> $DEST/details.logs
                else
                        echo "FAILED : Dumping $db" | tee -a $DEST/general.logs $DEST/details.logs
                fi
        fi
done
echo "$(date  +%b-%d-%y) Dumping Finished at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs

if [ -d "$oldBackup" ]; then
        rm -rf $oldBackup
        echo "Backup for $fiveDaysAgo removed successfully at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
else
        echo "Backup for $fiveDaysAgo not found." | tee -a $DEST/general.logs $DEST/details.logs
fi
echo "Backup Size" $(du -ch $DEST | grep total) >> $DEST/general.logs

The total size of all the databases is nearly 150GB When I restart mysql it takes nearly 2-3 hours to complete all the backups for a couple of days and then it keeps on increasing. Over a period of a week it reaches 7-8 hours and then 15 -20 Hours.

I have made sure that there is no process running while the backup is going for the first 4 hours.

Below is my sys config : OS : Centos 5.6 Mysql : 5.6 RAM : 64GB(8 cores)

Below my SQL config(my.cnf)

[mysqld]
#innodb_force_recovery=6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/lib/mysql/tmp
user=mysql

interactive_timeout=15
wait_timeout=40

symbolic-links=0
local-infile=0
port=33060
skip-name-resolve

sql_mode=NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=1

innodb_buffer_pool_size=32G
innodb_log_file_size=10G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_checksum_algorithm=crc32

innodb_buffer_pool_instances=8
innodb_thread_concurrency=16
innodb_lock_wait_timeout=180

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_io_capacity=2000
innodb_io_capacity_max=3000
innodb_lru_scan_depth=2000

#Binary log/replication
#log-bin
#sync_binlog=1
#sync_relay_log=1
#relay-log-info-repository=TABLE
#master-info-repository=TABLE
#expire_logs_days=6
#binlog_format=ROW
#transaction-isolation=READ-COMMITTED
#innodb_autoinc_lock_mode = 2
join_buffer_size=6M
sort_buffer_size=6M
read_buffer_size=6M
read_rnd_buffer_size=1M

key_buffer_size=512M
query_cache_type=0
query_cache_size=0
#query_cache_limit=1M

tmp_table_size=256M
max_heap_table_size=256M
table_open_cache_instances=16
table_open_cache=200000
table_definition_cache=100000
open_files_limit=1024000

thread_cache_size=200
max_connections=400
max_user_connections=350
max_connect_errors=99

connect_timeout=20
max_allowed_packet=128M

slow_query_log=1
long_query_time=5
slow_query_log_file=/var/log/mysql-slow.log

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

[mysqldump]
quick
max_allowed_packet=1G

Thank you in advance!!


texts: postgres removing user from role membership

texts:
How do I drop a role member? My role name tester is member of role postgres (which I do not want). How do I remove this membership?

 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+------------
 tester    |                                                | {postgres}
 postgres  | Superuser, Create role, Create DB, Replication | {}


texts: Blocked Process Report - which process does lockmode apply to?

texts:
I am analyzing a blocked process report and my blocking-process XML is as follows (some sections redacted for simplicity)

<blocking-process>
    <process status="running"
            spid="123"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2019-11-22T03:00:00.347"
            lastbatchcompleted="2019-11-22T03:00:00.347"
            lastattention="1900-01-01T00:00:00.347"
            clientapp="MyAppName"
            hostname="MyServer"
            hostpid="6596"
            loginname="MyLogin2"
            isolationlevel="read committed (2)"
            xactid="184723528"
            currentdb="6"
            currentdbname="MyDB"
            Timeout="4294967295"
            clientoption1="536870944"
            clientoption2="128056">
        <executionStack>

        </executionStack>
        <inputbuf>

        </inputbuf>
    </process>
</blocking-process>

my blocked-process XML is as follows

<blocked-process>
    <process id="process89e4c7f848"taskpriority="0"
                logused="256"
                waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"
            waittime="13730"
            ownerId="184723742"
            transactionname="user_transaction"
            lasttranstarted="2019-11-22T03:00:09.190"
            XDES="0x89d0786e80"
            lockMode="X"
            schedulerid="33"
            kpid="8176"
            status="suspended"
            spid="112"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2019-11-22T03:00:09.207"
            lastbatchcompleted="2019-11-22T03:00:09.207"
            lastattention="1900-01-01T00:00:00.207"
            clientapp=".Net SqlClient Data Provider"
            hostname="MyServer"
            hostpid="1092"
            loginname="MyLogin"
            isolationlevel="read committed (2)"
            xactid="184723742"
            currentdb="6"
            currentdbname="MyDB"
            lockTimeout="4294967295"
            clientoption1="671088672"
            clientoption2="128056">
        <executionStack>

        </executionStack>
        <inputbuf>

        </inputbuf>
          </process>
</blocked-process>

I can find the database and object affected in the blocking-process node:

waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"

However, what I don't know is what lock the blocking process held. I can see lockMode="X"in the blocked process node so my question ism does lockMode="X" relate to the lock that the blocking process was holding or the lock that the blocked process was trying to get?


texts: Compare the tablespace concept between Postgres and Oracle

texts:
When I create a table, it is placed in a default tablespace such as "USERS", unless I specify a user created tablespace, say, MyTableSpace.

For example,

create table test_tab(

name varchar2(100),
photo BLOB,
text   CLOB
)
tablespace "USERS"   --default
lob ("photo")  tablespace "MyTableSpace"   --user created
lob ("text")  tablespace "MyTableSpace"    --user created

Now, convert the above table to the counterpart in Postgres.  Where could those columns be placed?

A default tablespace?  User create tablespace?  Or some proprietary logical structure in Postgres?


texts:
Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure.

I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz:

How to Delete From Large Tables in Batches Without Growing the Transaction Log by Greg M Lucas.


texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the database is backed up?

Scenario:

Auto backup job in SQL Server takes a backup of database XYZ and also of it's transaction log. When this backup has completed SQL Server will reset the transaction log (log file size will be increased to starting point)

Thank you.


texts: GRANT permissions to all tables, views, procedures in SQL Server 2000

texts:
I was wondering if there was a fairly efficient way to use T-SQL with SQL Server 2000 syntax to GRANT SELECT, INSERT, UPDATE, DELETE, on all TABLES and VIEWS for a particular database while excluding 2 or 3 of the over 100 objects. I would also like to be able to grant EXEC privileges on all Stored Procedures.

Currently I'm using the below code to change them one by one.  Doing this for about 100 tables and 100 views is taking far too long and will take even longer through the GUI (Unless I'm doing that wrong too).

use [DATABASE_NAME]
GO
GRANT DELETE ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT INSERT ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT SELECT ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT UPDATE ON [dbo].[table_name]TO [user_name]
GO

How can I use T-SQL to cycle through ALL of the User Tables and Views to grant certain priveleges while excluding a couple objects?


texts: DATE - Normalization, a multiple ranged query, multiple inserts and multiple update

texts:
I have a booking system, airbnb style.

TABLES -> prices, house, available_date

A house has available days and not available days, but sometimes the days have different prices. A weekend is more expensive for example.

What are my options for storing the days: available, not available? If a house can have up to 366 information days....

First alternative was to build columns with the information startSaleDate, endSaleDate. What in my opinion may be a choice of low performance of search. Because, when a day in the range is sale, we have to reprogram the house row, and have a possibility to be available a day on, a day off, generating 366 rows for 1 house!

Are there other alternatives? Like normalization of dates, or algorithms?

I'm not really finding anything that might help me with the mentioned problem...


texts: List of role permissions including other roles - how can I find out the roles that belong to other roles?

texts:
I can see the permissions of a role in a database using the scripts in
List all permissions for a given role?

However, It does not show me when the role belongs to a another role.
For instance, on the picture below you can see that db_integrationEngine has the permissions of other roles too.
It is db_datareader and db_datawriter.

However when I run the script below, I can't see this.

here is the script I am using:

SELECT DISTINCT rp.name,
                    ObjectType = rp.type_desc,
                    PermissionType = pm.class_desc,
                    pm.permission_name,
                    pm.state_desc,
                    ObjectType = CASE
                                   WHEN obj.type_desc IS NULL
                                         OR obj.type_desc = 'SYSTEM_TABLE' THEN
                                   pm.class_desc
                                   ELSE obj.type_desc
                                 END,
                    [ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
    FROM   sys.database_principals rp
           INNER JOIN sys.database_permissions pm
                   ON pm.grantee_principal_id = rp.principal_id
           LEFT JOIN sys.schemas ss
                  ON pm.major_id = ss.schema_id
           LEFT JOIN sys.objects obj
                  ON pm.[major_id] = obj.[object_id]
    WHERE  rp.type_desc = 'DATABASE_ROLE'
           AND pm.class_desc <> 'DATABASE'
           AND rp.name <> 'PUBLIC'
    ORDER  BY rp.name,
              rp.type_desc,
              pm.class_desc

The script above, gives me the result on the picture below:

Scripts to add and remove one role to another
sp_addrolemember [db_datareader], [db_IntegrationEngine]
go
sp_addrolemember [db_datawriter], [db_IntegrationEngine]
go

sp_droprolemember [db_datareader], [db_IntegrationEngine]
go
sp_droprolemember [db_datawriter], [db_IntegrationEngine]
go

Ideally I am thinking about a recursive CTE that would go through each role and gets its permissions and when find another role inside it, do the same.
Just so far my attempts to generate such scripts were not successful, any help?
It does not need to be a recursive CTE as long as I can see the roles that are contained inside another roles.


texts: Could you please describe how SQL Server 2014 do cardinality estimation of Join predicate?

texts: I found the information about CE of single filter predicate, multiple filter predicated but I have not found anything about JOIN predicate.


texts: Is it possible to change the default style used by CAST/CONVERT when converting DATETIME?

texts:
When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For example:

SELECT CAST(GETDATE() AS VARCHAR)
-- May  8 2015  9:13AM

Is there a way to change the default style used? For example, I would like to use style 126 so that the results look more like this:

SELECT CAST(GETDATE() AS VARCHAR)
-- 2015-05-08T09:14:19.437

If possible, I would like to only change the default style for the current process or statement; however, I am also willing to change it at the database or schema level.

I should note: I am aware of CONVERT and the ability to manually specify the style. However, in this particular situation, I am unable to use CONVERT. I am CASTing a SQL_VARIANT parameter to a scalar-valued function as VARCHAR.


texts: Force table to raise non-fatal error when queried

texts: I need to setup a table in MySQL that when queried with a simple query (SELECT * FROM $table), it returns the data but also raises a non-fatal error. Doesn't matter what kind of error, just as long as it is non-fatal, so that the query results are successfully returned. Ideas?


texts: Postgres search query contains stopwords

texts:
I'm trying to debug a search query that contains a stopword, which is also a lexeme in the document text.

The query is 'up' and the document is 'UPS Store'. I would like to be able to show the user the document, when the user enters 'up', but instead I get this message:

NOTICE:  text-search query contains only stop words or doesn't contain lexemes, ignored

How can I fix this issue?


texts: Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

texts:
I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

Update / Solution

Thanks to Jack's answer below, I have been able to circumvent the issue, since it seems like we're really hitting the bug he mentioned. When I first drop all indexes on the MV, I do not get the error message and am able to recreate the MV as intended (it is not showing up in USER_OBJECTS anymore after having been dropped).

This is how I am doing it now (snipped for brevity):

SET serveroutput ON
SET echo ON

DECLARE
  CURSOR mv_indexes
  IS
    SELECT 'DROP INDEX '
      || index_name AS stmt
    FROM user_indexes
    WHERE table_name = 'MV_NAME'
    AND table_owner  = 'USER';
BEGIN
  FOR ix IN mv_indexes
  LOOP
    dbms_output.put_line('Executing: ' || ix.stmt);
    EXECUTE immediate ix.stmt;
  END LOOP;
END;
/

DROP materialized VIEW MV_NAME;

SELECT * FROM user_objects WHERE object_name = 'MV_NAME';

CREATE materialized VIEW MV_NAME ..

CREATE INDEX ix_someindex ON MV_NAME (..);
CREATE INDEX..


texts: Java application not using Transparent Application failover in Oracle database

texts:
I have a customer that have a RAC environment (2 nodes, database version is 11gR2).
Clusterware version:
[grid@server2 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]
[grid@server2 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]

Their aplication not use correctly the TAF (transparent application failover)
After some research I've tested using swingbanch and I've found the same behavior in my LAB environment:
INST_ID USERNAME   SERVER    SERVICE_NAME         PROGRAM              STATUS   FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- --------- -------------------- -------------------- -------- ------------- ---------- ---
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          sqlplus.exe          INACTIVE SELECT        BASIC      NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO

The only session using the TAF above is a session created manually from sqlplus (failover_type column). I'm using the same tnsnames alias in all sessions.
Why is this happening. Is there some requirement in the application side as well for it work correctly?


texts: Trying to run a procedure, error says result consist more than one line

texts:
There is an officecode and employeeNumber in the employees table, which references city in office table.

I want to create a stored procedure setRelocationFee to set the relocation fee for a given employee. If the employee’s office is in San Francisco, the relocation fee is $10000; if the employee’s office is in Boston, the relocation fee is $8000; if the employee’s office is in London, the relocation fee is $20000; if the employee works in other offices, the relocation fee is $15000.

I used these codes to test the procedure, and it gives me error saying result consist more than one line:

set @empID = 1501;  call setRelocationFee(@empID, @Rfee);   select @empID, @Rfee;

DELIMITER |

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20)) BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation
    FROM employees LEFT JOIN offices ON
    employeeNumber = empID;

    CASE relocation
    WHEN 'San Francisco' THEN SET Rfee = '$10000';
    WHEN 'Boston' THEN SET Rfee = '$8000';
    WHEN 'London' THEN SET Rfee = '$20000';

    ELSE
    SET Rfee = '$15000';
    END CASE;  END |


texts: Check the Oracle 11g R2 installation type

texts:
How can I check if the installation of Oracle Database is done with the Administrator Option Type?

There are four installation types while installing the Oracle 11g R2 as listed below:

Instant Client
Administrator
Runtime
Custom

How can I check to see if the installation was completed with the Administrator option?


texts: running many CREATE VIEW and GRANT SELECT statements in one execution

texts: Is there a way to run multiple CREATE VIEW commands and/or multiple GRANT SELECT commands by hitting F5 in SQL Server Management Studio?  If so, how can I do this?


texts: How to select info about indexes with specified columns from all databases in the instance using TSQL?

texts:
This query selects all the indexes - its names, fragmentation, and some other parameters from the current database.

select
    idx.[name] as [index_name],
    sc.[name] as [schema_name],
    obj.[name] as [table_name],
    ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;

Now I need to select all the indexes with specified columns from all databases. I can use USE [dbname] when I execute the statement manually, but I need to put it in a cursor, so I cannot write USE [dbname] in a cursor.

How can I substitute the database selection in the ...CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), ... part of my statement?

Help will be much appreciated to show how can I select all database indexes with a single query.

Something like this:

declare cr_index cursor
for
exec(@dynamicUSE)
select
    idx.[name] as [index_name],
    sc.[name] as [schema_name],
    obj.[name] as [table_name],
    ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;


texts: Perl DBI takes 60 seconds to connect to an Oracle database, every single time

texts:
I feel like I must be doing something wrong with my SQL connections.  I've generally ignored it, but I'm finally going to try and get to the bottom of this.

my $dsn = "DBI:Oracle:host=$host;sid=$sid";

my $dbh = DBI->connect($dsn,$dbuser,$dbpass,{ RaiseError => 1,AutoCommit => 0 }) or die("Unable to connect: $DBI::errstr");

It takes 60 seconds any time I run DBI->connect.  Rather large queries might take a bit of time, network latency may induce some delay, but all of that is on top of the 60 seconds it takes to connect.  I can literally run DBI->connect and then immediately close it without actually running a query, and it will take 60 seconds.  I can query this database using different tools with no issue, but any time I run a DBI->connect against it, it takes 60 seconds.  Most of my queries come back with a time of 61.2 seconds, or 63.4 seconds, or 65.3 seconds.  No matter what, it's 60 seconds from the time the script hits DBI->connect to the time it hits the next line.  I've even timestamped it on the line before and the line after.  the only thing between the timestamps is DBI->connect.

What am I doing wrong?


texts: can you setup replication on mirrored SQL database

texts:
Just moved to SQL2017 Ent. Currently, do transactional replication for 1.5TB DB. the replicated database is used for data analytics, mainly Power BI reports. Need near real-time data. When I have issues, initializing the DB is a pain due to size, I initialize from a backup but it takes too long and it is a manual process. was wondering if mirroring is set up, can I replicate the mirrored database, meaning can I set up full transactional replication on the mirror. This way I could initialize from a snapshot on the replicated DB instead of from a backup.
is the mirror only for failover? Can I make the mirror an active read-only copy?


texts:
I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table.

To be clear, my statistics are the following (approx.):

NAME        | TYPE  | SIZE
----------------------------
MYTABLE     | TABLE | 21 GiB
PK_MYTABLE  | INDEX | 20 GiB

MYTABLE has four columns, and three of them composes the primary key.  So the sizes should be correct.

But why do Oracle stores this so inefficiently?  (a separated structure for the primary key)
Do Oracle has something similar to MS SQL's clustered index, that makes the index part of the related table?
Is there something I can do to avoid this?


texts: Does a weak entity always have a weak relationship and total participation in ER diagram?

texts:
I've found in some videos that say a weak entity has a total participation and weak relationship in er diagram. In other videos it isn't. I couldn't find a clear explanation for this. Please can someone explain this to me.

For example, this states that the weak relationship exists when there is no primary key of the parent entity attached on the weak entity. Is it true?


texts:
If I want to use streaming replication between two Postgresql databases on different parts of the internet, is there a way to secure or encrypt the connection between the two? Or are there reasons other than security to not do that?

I'd like to prevent eavesdropping via a man-in-the-middle attack.


texts: Joining ~ 40 normalized tables for 200 columns to display detailed user profile. Can this be optimized?

texts:
A profile can have many profile types. That profile can have many fields, which are all tied to lookup tables. Some fields can have many selections.

profile -> profile_date -> one relationship status, one zodiac, many languages, many countries traveled, many cuisines, many activities, ...

profile -> profile_network -> one company, one school, ...

profile -> ...

A lookup table for the fields looks like this:

id  type
1   ♈ ARIES
2   ♉ TAURUS
3   ♊ GEMINI
4   ♋ CANCER
5   ♌ LEO
6   ♍ VIRGO
7   ♎ LIBRA
8   ♏ SCORPIO
9   ♐ SAGITTARIUS
10  ♑ CAPRICORN
11  ♒ AQUARIUS
12  ♓ PISCES

If the field is a single selection, we join the lookup table and we're done.

-> profile_date (zodiac_type_id) -> zodiac_type.id

If the field is a multiple selection, there's a join table in between for the many-to-many.

-> profile_date -> (profile_date_id) profile_date_languages (language_type_id) -> language_type.id

In addition, I am joining other things like this to get all of their photos:

profile -> personas -> photos

In addition, the query also filters for things like persons who like each other, etc.

The query seems to start performing poorly with just a low number of profiles, like less than 10. If a single person has an obnoxious profile where they check all the multiple selections e.g. speak every language, been to every country, it grinds the database to crippled performance for everyone if their mega profile ends up being fetched.

That's the idea. You can see more of the fields here:

Sample query:
https://pastebin.com/wug86Wrr

Sample query plan:
http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

Users will always be filtering by at least profile.birth_date (age), profile.radius (geolocation). Paid users can filter by any field. :)

PostgreSQL 9.6

Some ideas I had were:

Optimize this query (I am not sure what can be done, am mostly wondering if I'm just doing something really stupid/unreasonable)
Right now I am joining many tables per card, so the initial load is slower, but the data is all there if the user decides to explore and things look instant to them. I could instead fetch the minimum preview for the cards (join few tables per card), then only join the full profile (1 card filtered by id, many/the rest of the joins) if the user clicks on the preview to see the detail view (but I feel this is not the best user experience because the user has to now wait for the load every time they click a profile which is more noticeable). I am not sure this would even help because users can also filter by these fields, so I believe I would need to join them beforehand.
Use ElasticSearch (I feel like I wouldn't need this until many more profiles?) I've done something similar to this with Algolia and it handled it like a champ, but I imagine this can just be done with PostgreSQL.


texts: Error during migration run: "no PostgreSQL user name specified in startup packet"

texts:
When I go docker-compose up, I get this error:
yarn run v1.15.2
$ yarn migrate:run && yarn start
$ yarn typeorm migration:run --config ./lib/src/ormconfig
$ /usr/src/app/node_modules/.bin/typeorm migration:run --config ./lib/src/ormconfig
Error during migration run:
{ error: no PostgreSQL user name specified in startup packet
at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:606:11)
at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:403:19)
at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:123:22)
at Socket.emit (events.js:193:13)
at addChunk (_stream_readable.js:295:12)
at readableAddChunk (_stream_readable.js:276:11)
at Socket.Readable.push (_stream_readable.js:231:10)
at TCP.onStreamRead (internal/stream_base_commons.js:154:17)
name: 'error',
length: 121,
severity: 'FATAL',
code: '28000',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'postmaster.c',
line: '2217',
routine: 'ProcessStartupPacket' }
error Command failed with exit code 1.

Whereas I have already specified the allegedly missing user name in my docker-compose.yml file.
Where else should I ass the user name? Or is the error referencing something completely different?

db: postgres:9.6


texts: Difference between is_published and is_merge_published in Sql Server?

texts:
What is the difference between the columns is_published and is_merge_published in Sql Server  ?

I can't find docs describing it !

SELECT [name],[is_published],[is_merge_published] FROM sys.databases ;


texts: AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

texts:
I'm running a CRON script which checks the database for work and executes anything that needs to be done.  It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU machine which, until recently, has been plentiful to keep it happy (normally plugging along under 20%).
This weekend we updated customers to the latest version of the code and implemented some tooling, and since then we've started seeing these huge waits:

Further I'm seeing that about half of our busiest queries are EXPLAIN statements, somewhere illustrated here:

Nowhere in our code base is an "EXPLAIN" performed (though we are using AWS RDS performance insights, ProxySQL and New Relic for monitoring).  I did notice that in the past week our number of DB connections was previously baselined around 10 and is now closer to 90.

Any ideas on where I should be digging to find the cause of these waits and explain statements?  And if they could justify the large number of open connections?


texts: How to modify a Postgres tablespace location

texts:
I am using a workstation that has 3 different hard drives. Some time ago I got a large database to reverse engineer that would not fit in the system drive. Therefore I created a new table-space in one of the the other hard drives, that at the time was mounted on the /media/Work1 folder.

Everything was working fine, until today at startup the system decided to mount the drive on the /media/Work folder. Naturally, Postgres can no longer access the databases in that table-space.

How can I change the location of a table-space in Postgres? I am aware of the pg_tablespace_location function, but not of somthing to modify it.


texts:
When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems?  I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so I'm sure I'll be getting a few answers here.  I migrated about 157 million rows to a SQL Server database, and this query is taking forever.  But in my source Netezza database, it takes seconds.

For example:

Netezza 6:

SELECT COUNT(*) FROM DATABASENAME..MYTABLE

Oracle 11g:

SELECT COUNT(*) FROM MYTABLE

SQL Server 2012:

SELECT COUNT(*) FROM DATABASENAME.[dbo].[MYTABLE]


texts: Why am I seeing `ERROR 1290 (HY000)` when altering my table?

texts:
When I run the following statements on my test_date table, I get error ERROR 1290 (HY000):

desc test_date;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| gmt_create | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
1 row in set (0.00 sec)

alter table test_date modify gmt_create datetime not null default current_timestamp;

ERROR 1290 (HY000): The MySQL server is running with the
--log_bin_use_old_datetime_format option so it cannot execute this statement

I don't know what is the problem.Looking for your help.


texts: SQL Server 2014 - remote login to AOAG named instance, listener port does not work

texts:
Your help is greatly appreciated.
I spent a few hours trying to troubleshoot this and before I go to the firewall/network possibilities, i would like to check if my setup has any errors

I am unable to remote connect from my desktop using the VNN and port# to the named instance on non default port in the AOAG

Local connection tests - successful
a) servername\instancename
b)servername, port#
c)server ip\instancename
d)serverip, port#
e)aoag listener\instancename
f)aoaglistener, port#
g)aoag listenerip\instancename
h)aoaglistenerip, port#

Remote connection from desktop - successful if using server and instance names.
But it fails if I try remote connection with VIP/VNN
e)aoag listener\instancename
f)aoaglistener, port#
g)aoag listenerip\instancename
h)aoaglistenerip, port#

I would like to use the separate AOAGlistener and port#s in the connection string.

I have validated -

firewall rules work as tested from remote desktop using server ip and instancename/port#
username password works
using static ip and port#
tcp is enabled and active/enabled are yes for each ip
remote connectio is enabled.

Error information

Cannot connect to aoag_lsnr, 1551.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider)

Server Name: aoag_lsnr, 1551
Error Number: 64
Severity: 20
State: 0

Thank you for your help.


texts: Query to send mail to multiple users with their corresponding tickets

texts:
I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority.

For example:

I need to send mail from SQL Server using sp_send_dbmail to John with all the tickets assigned to him in a single mail. This is a huge table so I want send mails to users with their assigned tickets in single mail instead of sending one mail per ticket. Any help would be appreciated.


texts: Issue with getting data from XML file by using SSIS

texts:
I'm getting the data from XML file on the internet like below:

<?xml version="1.0" encoding="ISO-8859-1"?>
-<exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
-<dailyrates id="2016-07-22">
<currency rate="505.47" desc="Australian dollars" code="AUD"/>
<currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
<currency rate="205.85" desc="Brazilian real" code="BRL"/>
<currency rate="515.31" desc="Canadian dollars" code="CAD"/>
<currency rate="684.99" desc="Swiss francs" code="CHF"/>
<currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
</dailyrates>
</exchangerates>

XSD file looks like:

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="exchangerates">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="dailyrates">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="currency">
                <xs:complexType>
                  <xs:attribute name="code" type="xs:string" use="optional" />
                  <xs:attribute name="desc" type="xs:string" use="optional" />
                  <xs:attribute name="rate" type="xs:string" use="optional" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="id" type="xs:date" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="type" type="xs:string" use="optional" />
      <xs:attribute name="author" type="xs:string" use="optional" />
      <xs:attribute name="refcur" type="xs:string" use="optional" />
      <xs:attribute name="refamt" type="xs:unsignedByte" use="optional" />
    </xs:complexType>
  </xs:element>
</xs:schema>

But in SSIS (from XML Source Task) I get the data like below. Last column shows me "1" instead of "date" (2016-07-22 in this case). I need this date to be in every row  of the "dailyrates_Id" column. Any ideas how to solve this ?

code    desc    rate    dailyrates_Id
AUD Australian dollars  505.47  1
BGN Bulgarian lev   380.36  1
BRL Brazilian real  205.85  1
CAD Canadian dollars    515.31  1

Metadata of XML Source Task:


texts: Archiving tables with Access frontend and linked SQL Server

texts:
I'm working on a project where the user has an Access 2013 MDB database with forms, reports, and queries. We're converting it to use linked tables to SQL Server 2008 R2. All the linked tables seem to be working well.

The user needs the ability to archive some tables annually, and be able to read the data. These are not particularly large tables (the whole MDB is 27MB). Currently, the users make a copy of the MDB file every year and use that as their archive. They need the old data for comparison with the current information.

How do you archive linked tables via Access? It's not feasible to copy the entire SQL Server schema every year. Maybe if we just back up the tables and link again? What's the recommended approach to this? Thanks.