texts: no pg_hba.conf entry for host "[local]", user "pgadmin", database "postgres", SSL off on OSX

texts:
local  all  postgres    trust
host  all  all  0.0.0.0/0  trust
hostnossl    all          all            0.0.0.0/0  trust

I would think either the second or the third would match these criteria.

I've restarted my service on brew with brew service restart postgres@9.6

Am I missing some setting here?


texts: Constraining inserts/updates for summary tables with complex validation logic

texts:
I am designing some MySQL tables and am running into an issue that I believe can be solved by a uniqueness constraint, but not sure exactly how. My actual use case is complicated and confusing, so I've simplified it down to a meaningful analogy involving schools, classrooms and test grades (but note: this is not homework - I'm a developer trying to solve a real-world problem here).

Some domain entities and business logic for a backstory:

There are Students
Students take Classes
In the Classes you get Tests which are graded
Each Quarter students have to sign up for a particular CourseLoad, which is a pre-determined sets of classes to take. A CourseLoad is a particular set of Classes that are all taken at the same time (e.g. the Core Math CourseLoad might consist of: MATH 205, MATH 210, and MATH 326, etc.)

Here are my tables as they currently stand, and although I'm 100% open to refactoring them, I am pretty happy with the given design (also please note: please don't linger on naming conventions here, its the design which is most important!):

students
========
student_id : INTEGER PRIMARY KEY AUTO INCREMENT (hereafter 'IPKAI')
student_name : VARCHAR(100)
student_dob : DATETIME

classes
=======
class_id : IPKAI
class_name : VARCHAR(50)    // Ex: 'CHEM 101'

courseloads
===========
courseload_id : IPKAI
courseload_name : VARCHAR(50)   // Ex: 'Core Math Courseload', 'General Ed Courseload', etc.

// Classes and Courseloads are many-to-many: The same Class can appear inside multiple
// Courseloads, and the same Courseload can consist of many Classes
classes_to_courseloads
======================
classes_to_courseload_id : IPKAI
class_id : FK INT (classes)
courseload_id : FK INT (courseloads)

quarters
========
quarter_id : IPKAI
student_id : FK INT (students)
courseload_id : FK INT (courseloads)

tests
=====
test_id : IPKAI
student_id : FK INT (students)
quarter_id : FK INT (quarters)
class_id : FK INT (classes)
test_date : DATETIME
test_grade : INT

So finally, my problem:

A Test occurs inside a given Quarter (reporting period). A Test also belongs to a Class, which itself belongs to 1+ CourseLoads. A given Quarter also has a CourseLoad assigned to it (since the student must select which CourseLoad they are going to tackle that particular quarter).

I'm worried about validation here. Let's take a living, breathing example to make this understandable:

Given a Quarter with a quarter_id=14...
A Student with student_id=508 signs up to take a 'Core Math CourseLoad' (courseload_id=5)
The Core Math CourseLoad consists of 3 Classes:

MATH 205 (class_id=8); and
MATH 210 (class_id=18); and
MATH 326 (class_id=92)

At some point during that quarter, the student takes a mid-term for MATH 210, which is test_id=38321 and the student receives a grade of 94

Given all this info, the tests record for this mid-term would look like:

test_id     student_id    quarter_id      class_id    test_date   test_grade
============================================================================
38321       508           14              18          2015-07-23  94

What I need to validate is: That it becomes impossible for the student to have a test record during a given quarter for a class that is not apart of that quarter's courseload. In other words:

test_id     student_id    quarter_id      class_id    test_date   test_grade
============================================================================
38326       508           14              21          2015-08-19  85

...should be an invalid entry and should violate some constraint or raise an error if we try to insert that as a new tests record. Because, class_id=21 is not a part of the Core Math CourseLoad.

I am looking for an elegant solution here. Again, I think this can be solved by introducing a UK to the tests table, but would consider any solution (even heavy table refactoring) if it means solving the problem correctly.


texts: Does it matter to convert integer primary key into equivalent string?

texts:
In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability.
I want to encode it as base36 or base64, like using char(n). In theory the number of bytes should be the same as before, so it does not take more space. Is that correct?
Are there other concerns? For example sorting, collation?


texts: Cluster network name resource 'XYZ' cannot be brought online

texts:
I have given wrong name for listener while configuring the always on in SQL Server management studio.

One option I can see is deleting and re-creating the listener,  But is it right way of doing it? will it have any side affects?

https://msdn.microsoft.com/en-us/library/hh231526.aspx

Updated - After deleting and recreating the listener I am getting following warning.

  Cluster network name resource 'abc_xyz' cannot be brought
  online. Ensure that the network adapters for dependent IP address
  resources have access to at least one DNS server. Alternatively,
  enable NetBIOS for dependent IP addresses


texts: Can I migrate 2 tables (with one table foreign key referencing the other) to in-memory OLTP

texts:
SQL 2016 - I have 2 tables, Foo & Bar - Bar references Foo. I'd like to turn Foo into an in-memory optimized table, however as Bar references this, I can't do it.

I also can't make Bar in-memory due to its dependence on Foo.

Seems like I need to do this to both tables at the same time - is this possible without recreating both tables from scratch?


texts: Automatically partition a table in mysql (pymysql) everyday for new data

texts:
Im still figuring out partitioning in SQL.

Is it possible to automatically partition a table everyday for new data? For example, on Monday, I have one partition. On Tuesday, automatically create a new partition, insert that day's data into the new partition and so on... Is reorganize partition the right way to go?


texts: CMEMTHREAD waits during Index Optimisation in Availability Group

texts:
In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation:

Troubleshoot: Changes on the Primary Replica are not Reflected on the Secondary Replica

In my experience, the issues that I see most often appear to be:

  A long-running transaction on the primary replica prevents the updates from being read on the secondary replica.

and

  The redo thread on the secondary replica is blocked from making data definition language (DDL) changes by a long-running read-only query. The redo thread must be unblocked before it can make further updates available for read workload.

I can observe this by looking at the Extended Events session "AlwayOn Health":

When an application issues read-only queries to the secondary replica, if a heavily logged operation (like Index Optimisation) is running on the primary, sync lag becomes pronounced and I see a huge backlog in uncommitted log records on the secondary, as is described in the above MS docs.

The question I have is why I see CMEMTHREAD waits on the secondary replica when Index Reorganisation is taking place on the primary:

Is this normal/expected behaviour or something else?

Whilst there is some read activity on the secondary replica, those queries are most often <1 second in runtime, with the occasional <10 second query. CPU usage around 5%.

Output of @@VERSION: Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) -
13.0.4522.0 (X64)   Jul 17 2018 22:41:29   Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

CMEMTHREAD wait is only observed on the secondary replica
The replica that shows this wait (and the lag) is an actively queried synchronous replica

Update:
I just spotted this WAIT occurring again during index optimisation. I killed the index job and that then obviously stopped the sync lag from increasing, however the CMEMTHREAD wait continued and redo seemed quite slow. I also noticed occasioinal PARALLEL_REDO_FLOW_CONTROL waits on the redo thread, so I simply executed DBCC TRACEON (3459, -1) and suddenly redo speed increased and the backlog started to clear extremely quickly.

You can see I stopped index optimisation at 1:20pm and applied the trace flag at 1:45pm. Note that the SQLSentryOne wait graph is in UTC whilst the latter graph is in BST.

Update

I have just observed this exact behaviour again on an ASYNC replica with nothing running on it. The same trace flag resolved this again. I'm surprised as I had thought this was caused by read-only queries causing contention on SYNC replicas with heavily-logged operations (like index maintenance) occuring on the primary. On this occasion, we have index maintenance on the primary, a SYNC replica with no redo issue but an ASYNC replica with this issue. Here you can see the WAITstats showing CMEMTHREAD and the point when the trace flag was enabled, the CMEMTHREAD wait is gone and the redo contention is resolved.


texts: SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014

texts:
I have 2 databases that contain nearly identical data.

The first has compatibility level 100 and is restored on a sql2012 instance.
The second has compatibility level 110 and is restored on a sql2014 instance.

I am running the same query on both databases.

On the first database the query finishes in approximately 20-30 seconds.

I tried running the same query on the second, and it did not finish after 50 hours. If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

I have tried rebuilding statistics for all tables affected by the query, and i also checked fragmentation (the first database actually looks to have far more fragmentation)
Why is there such a difference in performance, and what can I do to fix this?

This is the actual query plan for the first database:

https://www.brentozar.com/pastetheplan/?id=rJUN72gQB

This is the estimated query plan for the second database:

https://www.brentozar.com/pastetheplan/?id=SysNr2gQr

The full query looks like this:

https://justpaste.it/53b9c


texts:
I have installed SQL Server 2014 Express Edition ( 12.0.2000.8 x64 ) on a Windows server 2012 machine.

Because of the POODLE vulnerability, our IT department is going to disable TLS 1.0 and all other previous security protocols on this server.

Where can I find a newer version of SQL server Express Edition that can support OR work with TLS 1.2?


texts: Warning after upgrading from dockerized postgres 9.3.10 to Postgres 9.5.2

texts:
I've just finished an upgrade on an Ubuntu box with a dockerized Postgres container. The upgrade appears to have gone off great. But when log in to the docker container/server I get a warning message:

  psql (9.3.10, server 9.5.2)
  WARNING: psql major version 9.3, server major version 9.5.
           Some psql features might not work.

How I did the upgrade was to spin up a new docker container with 9.5.2. Once the container was up I piped a pg_dumpall from the old server to the new server using pg_dumpall -h localhost -U postgres -p 5432 | psql -h localhost -U postgres -p 5532. After the dump was complete I swapped the data dir: mv /data/postgres/data data.old
mv /data/postgres/data9.5.2 data. These are the mapped volumes from the docker container. Then I stopped and removed the 9.3 container and started up the new 9.5 one with the right ports and docker name mapped. I was able to start the server and log in and everything appears to be working but the warning is giving me cause for concern.

Any ideas what I could do differently and how to resolve this warning? I have several other databases that I'll need to do this on.


texts: Set Field To Have Default Value Of Another

texts:
I am trying to create a trigger using the below syntax, however SSMS keeps barking at me with the below error.

  Msg 208, Level 16, State 4, Procedure test1, Line 1
  Invalid object name 'data123'.

And here is my syntax

CREATE TRIGGER test1
ON data123
AFTER UPDATE
AS
begin
  UPDATE data123
  SET saledate = saledate1
  FROM data123
  INNER JOIN INSERTED i ON data123.id = i.id
  AND i.saledate1 is not null;
end
go

Edit
For clarity - what I want to achieve is to set the field saledate = saledate1 where saledate1 is not null.

If there are better ways to achieve this then a trigger I am open for suggestions :)

EDIT #2
I also tried this syntax

ALTER TABLE data123 ADD CONSTRAINT cst123 DEFAULT saledate1 FOR saledate;

But I get an error of

  Msg 128, Level 15, State 1, Line 1
  The name 'saledate1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

saledate is a field that is used for multiple SSRS etc and when the database was created etc for data123 the field was improperly named to saledate1.  Not viable to try to rename it now as multiple elements are already tied to it.  So in order for data123 to be included in SSRS I need the field saledate to hold the value from saledate1

EDIT #3
Here is sample DDL to hopefully clarify what my problem is and my desired output.

Create Table test1
(
  uqID int IDENTITY(1,1) PRIMARY KEY,
  itemsold varchar(400),
  saledate1 date,
  saledate date
)
Insert Into test1 (itemsold, saledate1) Values
('tree', '03/01/2016')
,('flower', '02/11/2016')
,('chair', '02/25/2016')

Select * from test1

So if you run the above syntax this is your output

uqID    itemsold    saledate1   saledate
 1      tree        3/1/2016
 2      flower      2/11/2016
 3      chair       2/25/2016

My desired output (which is why I originally thought a trigger) is this

uqID    itemsold    saledate1   saledate
1       tree        3/1/2016    3/1/2016
2       flower      2/11/2016   2/11/2016
3       chair       2/25/2016   2/25/2016


texts: Incorrect definition of table mysql.event after migrating database to mariadb

texts:
I did a mysqldump to dump all databases into a .sql file, then installed a mariadb server. There are no errors after the installation. But after restoring the databases, I started to observe the following two errors:

  [ERROR] Incorrect definition of table mysql.event: expected column
  'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
  found type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
  [ERROR] Event Scheduler: An error occurred when initializing system
  tables. Disabling the Event Scheduler.

It looks like something within the mysql.event table got overwritten. I did not create any events, so I wasn't expecting anything to be affected here. What should be done to eliminate this error?


texts: Oracle Top CPU Consuming SQL is "select longdbcs from javasnm$ where short = :1"

texts:
For the Oracle 12c database I've been administrating, I usually carry out the monitoring using Oracle Enterprise Manager Cloud Control 13c. From time to time, the database suffers from performance issues that are felt by end users.

When I check the performance details of the database in EMCC following the menu in Database Home page Performance -> Top Activity, I see some application specific SQLs are problematic. In addition to problematic SQLs, I also usually see the following SQL listed as the top consumer:

select longdbcs from javasnm$ where short = :1

I know that the 3rd party application running on top of this Oracle database is Java based, and it might not have the most optimized code in terms of Oracle calls.

Would anyone have any idea what this SQL would be about and how to figure out if it is really needed or something that could have been disabled? (at Oracle level or with a slight change in application level)

I searched the internet for relevant information but was not able to find anything clear / comprehensible enough for me. For anyone who would have any points, suggestions and answers are welcome. Thanks in advance.


texts: unreachable SQL Server for a group of computers

texts:
I'm deploying SQL Server 2008 Express in my company. I already installed, did a application, tested, gave permission to user.

All desktop users run the program without any problem. But the notebook users can't establish a connection with SQL Server.

Facts:

Notebook has permission in the company's network (already access network drive)
Notebook pings the computer with SQL Server.
I gave permission for the company's groups and explicit for notebook users
Firewall and Anti-virus is turned off in SQL Server computer.

Any idea?

  System.Data.SqlClient.SqlException (0x80131904): A network-related or
  instance-specific error occurred while establishing a connection to
  SQL Server. The server was not found or was not accessible. Verify
  that the instance name is correct and that SQL Server is configured to
  allow remote connections. (provider: SQL Network Interfaces, error: 26
  - Error Locating Server/Instance Specified)

EDIT: The problem was that the notebooks wasn't in the same domain.


texts: What database should I use locally if I'm going to use Aurora Serverless?

texts:
I'm planning to use Aurora Serverless in QA/PROD and then a docker container for running a compatible engine locally. Should I use MariaDB 10.0 or MySQL 5.6?

Database need is low, we're not doing anything crazy. We're developing a Java (Spring Boot, Hibernate) and choose to go with a relational database. We choose to go with a MySQL driver because that's the DB we're familiar with, but not a DBA and so therefore we're scratching the surface.

We want to have locally running version of the database rather than being dependent on a connection back up to AWS while working. We're small enough where we don't have a direct connect and managing the DB security of people being able to work anywhere/anytime isn't something we feel we can keep up with.

Also, if this is a bad idea please speak up - thanks!


texts: Restrict postgresql's public schema's tables visibility in tableua

texts:
My requirement is to display only one particular table or materialised view when user give db_host, db_port, db_name, db_username, and db_password to tableau.

Currently tableau is showing all the tables present default public schema. So I tried to create another schema and another role and keeping that perticular table or materilised view in that new schema.

CREATE SCHEMA alok_schema;
CREATE ROLE alok LOGIN PASSWORD 'alok';
GRANT USAGE ON SCHEMA alok_schema TO alok;
CREATE MATERIALIZED VIEW alok_schema.mview_alok as SELECT name, location from sometable;
GRANT SELECT ON alok_schema.mview_alok TO alok;

I revoked access for this new user on public schema

REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM alok;

Now user alok is not able to see tables present in public schema and he can only see table/view present in alok_schema when he try

psql -h localhost -U alok db_name

But this behaviour is not reflecting in tableau even for new user alok. In tableau postgresql user alok is still seeing all the tables present in public schema because tableau list up tables names by querying pg_catalog.pg_namespace which is system catalog and I guess its not allowed to add row level security policy

create policy restrict_rows_for_alok on pg_catalog.pg_namespace to alok using (nspname = alok_schema);
ERROR:  permission denied: "pg_namespace" is a system catalog

So either I am seeing all the tables or none of the tables in tableau.

Is there any way I can restrict public schema's table listing in tableau? I want only mview_alok to be visible in tableau which is present in schema alok_schema.


texts: Compare Validate TSQL Data Warehouse to SSAS

texts:
How do I compare Data in T-SQL DataWarehouse to SSAS Cube?

I would like to conduct this from SSMS (SQL Server Studio Management Studio).

Example

Select CustomerName from dbo.DimCustomer where CustomerId = 5

SHOULD EQUAL

SELECT [Customer].[CustomerName].Members ON ROWS FROM [DimCustomer] where [Customer].[CustomerId].[5]

https://xzwang.wordpress.com/2013/06/17/validatingcubesvsrelational/

Following blog recommends OPENROWSET. Is there any other method to validate data in same environment rather than OPENROWSET in SSMS? Otherwise, team will utilize this or may research Powershell or C#.

select cast("[Customer].[Customer].[Customer].[Email Address]" as nvarchar(50)) EmailAddress,
"[Measures].[Internet Sales Amount]" SalesAmount
from openrowset('MSOLAP', 'datasource=localhost;Initial Catalog=AdventureWorksDW2012Multidimensional-EE;',
' SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS ) }
DIMENSION PROPERTIES [Customer].[Customer].[Email Address] ON ROWS FROM [Adventure Works] ')


texts: SQL Server Management Studio 2014 - Show same .sql file in multiple tab groups

texts: In Notepad++, I can look at the same file in two side-by-side tab groups. In that software, this feature is called "Clone to Over View". Is it possible to do this in SQL Sever Management Studio 2014?


texts: SQL Server Express 2012 Database Size Limit

texts:
I checked the forum there are other similar topics but my scenario is different.
for myDatabase under database properties -> General tab it shows:

Size : 25285.31 MB
Space Available: 3989.13 MB

And on disk

myDB.mdf = 10.0 GB (10,745,806,848 bytes)
myDB_log.ldf = 14.6 GB (15,767,764,992 bytes)

As per my understanding only 10GB is allowed in Expres edition but here it seems more. What is the space available parameter from DB properties.


texts:
Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application.
I don't know yet what the issues are but the solution they propose is to shrink the database (because there is a lot of free space). By my knowledge this can not result in a performance improvement but apparenty they had success with this method when applying at other clients.
The way I think the shrinking happens is by moving pages from the back to the front of the file and like so compacting the database and causing massive fragmentation.

Is it possible that range scans take advantage of compacting the database?
Or are there some other edge cases that can benefit from shrinking a database?


texts:
In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.

In monitoring SQL task:

state = SUSPENDED
, comment = ALTER INDEX
, APPLICATION name = Microsoft SQL server Management Studio
, Query Wait = LCK_M_SCH_M
, Head Blocker = object lock
lock Partition = 15
objid=585105175
subresource=FULL
dbid=5
id=lockaa7aae200
mode=Sch-S
associatedObjectId=585105175 1386

Thanks in advance for any helpful info.


texts: Discover available HD storage from inside SQL Server

texts:
I have a SQL Server 2012 instance hosted in a VPS. I can't login on VPS's Windows directly. I know that there are multiple instances installed on that VPS.

I have a query that shows me each DB's file name, that file's size, used and unused space. By their file paths, I can see they aren't all on the same partition (IDK why the infrastructure admin created multiple partitions...).

I need to see each partition's size and free space. Is there a way to find that from a query in SQL Server without having to login on Windows?


texts: MySQL - MySqlDump uses \n instead of null

texts:
Using Windows with MySQL 5.1.

I am running a script that uses mysqldump to backup a database.

The mysqldump syntax used is:

mysqldump --user=root --port=3306 --password=topsecret some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

This creates a text file and when I open it all NULL fields show as \n

Is there a setting for mysqldump that would export NULL fields as NULL?


texts: Should I enable db_writer in public facing website

texts:
I've come across an issue when using Entity Framework and trying to delete an object.

The SQL-Server DB that it's trying to delete from, and the Login it is using, has a role membership of public only.  If I check the db_datawriter box, the data is deleted as expected.

I'm completely new to SQL-Server admin, would enabling this option pose any security risk to our website?


texts:
I am importing a csv file into an Oracle database using an external table.

The file encoding is UTF-8. The separator is actually ;.

The target table definition is identical to the external table definition.
The NLS_LENGTH_SEMANTICS is defined as CHAR.

Target character fields are defined as COMESFROMTOWN VARCHAR2(50), (example).

Reading the external table and loading into the target table works fine for the majority of them. But records with COMESFROMTOWN length of 49 to 50 characters including a ',' (comma) are rejected because of length: ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

I tried resizing the external table fields, but the issue also happens when loading the target from the external table. Resizing the target is not an option.

Can you explain why:

The comma being the 2c UTF-8 character, coded on 1 byte, it raises an
error?
NLS_LENGTH_SEMANTICS being defined as CHAR, it does not uses
the requested number of bytes for the string?

How can I workaround this ?

Thanks.


texts: How to total additional results from alias column results in Oracle SQL?

texts:
I'm attempting to display the same number of invoices that have the same number of days between two specific dates (e.g. issuance date and payment date).  I can ascertain a segment of the aforementioned with the following query:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days" FROM customer.invoices;

which displays the number days between those specified column-dates, but to clarify, I would like to create an additional column displaying how many instances occur between the number of days calculated from the above, preferably as another alias column- say "Number of Instances".  I suspect the solution might involve the ROLLUP operator or perhaps the CUBE operator but I'm unable to effect.  Here is my last unsuccessful attempt:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
     COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices;

I'm also open to alternatives and I'd appreciate any insight with respect syntax and structure as well.  Thank you!

UPDATE

As requested, I've included a below screen-shot of the first query cited; so, in addition to the column below, I would like to create another alias column as "Number of Instances" counting the number of instances.  Thank you.


texts: SSMS clean output window with sql instruction

texts:
How can i clean output window in SSMS using t-sql syntax? Is it possible ?
Do you know any workarounds ?


texts:
I'm working on a small project (CRM) using Python / Django and I'm using, each user have his own Schema, I would like to know how can I share some data between users ( ex: share user contact list between two users)
I was thinking to create a table in the public schema but I don't think is the right solution since I'm isolating data
Scenario
You are the user (Tenant 1), and I'm working with you in your team (Tenant 2) how you can share with me a contact record from your contact table


texts: Count between dates and operate between rows - SQL

texts:
I try to count elements between dates and operate between rows agnostically. As an example, suppose the month starts from day 2 until day 2 of next month. The table has fields {timestamp, status} and the status field has 3 different types. I do not know how to make it count between dates. Also, I would like to have the percentage of statuses.
Thank you for the help sincerely.


texts:
I'm currently developing SSIS packages which include transferring data between two servers. The servers are in the same local LAN, but I'm remote to them.

Is there any way that I can develop the packages on my local workstation and get the advantages of the visual debugger, etc. while having the actual data processing only happen on my remote SSIS server, saving me from having the data transferred over the WAN and back?


texts: SQL Server for Linux 2019 - Windows Authentication not working

texts:
Hoping to pick the brains of those more knowledgeable than me. I've been trying to set up a SQL Server 2019 instance on Linux; specifically on AWS using AMI amzn2-x86_64-SQL_2019_Standard-2019.11.12

I've followed the steps to connect the instance to our domain, and am able to successfully login using my domain credentials. sssd.conf below:

[sssd]
domains = [domain name]
config_file_version = 2
services = nss, pam

[domain/[domain name]
ad_server = [FQDN of Domain Controller]
ad_domain = [domain name]
krb5_realm = [DOMAIN NAME]
realmd_tags = manages-system joined-with-adcli
cache_credentials = True
id_provider = ad
krb5_store_password_if_offline = True
default_shell = /bin/bash
ldap_id_mapping = True
use_fully_qualified_names = False
fallback_homedir = /home/%u
access_provider = ad

I've followed all the steps on the MSDN documentation on how to set up Windows Authentiation:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-linux-ver15

But whenever I try to login to the SQL Server using my domain credentials I receive the following error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication..

And in the mssql-server logs (using systemctl status mssql-server -l) I see the following:

Apr 22 04:47:04 [SERVER NAME.DOMAIN NAME] sqlservr[3243]: 2020-04-22 04:47:04.24 Logon       Error: 17806, Severity: 20, State: 14.
Apr 22 04:47:04 [SERVER NAME.DOMAIN NAME] sqlservr[3243]: 2020-04-22 04:47:04.24 Logon       SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure.
The Local Security Authority cannot be contacted   [CLIENT: [IP ADDRESS]]
Apr 22 04:47:04 [SERVER NAME.DOMAIN NAME] sqlservr[3243]: 2020-04-22 04:47:04.25 Logon       Error: 18452, Severity: 14, State: 1.
Apr 22 04:47:04 [SERVER NAME.DOMAIN NAME] sqlservr[3243]: 2020-04-22 04:47:04.25 Logon       Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: [IP ADDRESS]]

The error would suggest an issue contacting the Local Security Authority, so I've double checked my krb5.conf file and don't see any obvious issues:

# Configuration snippets may be placed in this directory as well
includedir /etc/krb5.conf.d/

includedir /var/lib/sss/pubconf/krb5.include.d/
[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 dns_lookup_realm = false
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 rdns = false
 pkinit_anchors = /etc/pki/tls/certs/ca-bundle.crt
# default_realm = EXAMPLE.COM
 default_ccache_name = KEYRING:persistent:%{uid}
 udp_preference_limit=0

 default_realm = [DOMAIN NAME]
[realms]
# EXAMPLE.COM = {
#  kdc = kerberos.example.com
#  admin_server = kerberos.example.com
# }

 [DOMAIN NAME] = {
 kdc = [Domain Controller 1 FQDN]:88
 kdc = [Domain Controller 2 FQDN]:88
 kdc = [Domain Controller 3 FQDN]:88
}

[domain_realm]
# .example.com = EXAMPLE.COM
# example.com = EXAMPLE.COM
 [DOMAIN NAME] = [DOMAIN NAME]
 .[DOMAIN NAME] = [DOMAIN NAME]

And the fact that I can login using my AD credentials to me suggests there's no overarching issue contacting the domain controllers.

Any tips or pointers would be greatly appreciated on where to go next with this!


texts:
Is it possible to tell when databases, schemas and tables were created in Postgres?

I've serached through PgAdmin and tried the command line interface with no joy. Also can't find any reference to this functionality anywhere online. Maybe I'm missing something obvious!


texts:
I'm trying to get cumulative sum in one query. It works fine in SQL, PRESTO etc. but not in HIVE, which throws error msg.
create table test(store varchar(10), item int, quantity int)
insert into test
select 'depot',101,1
union select 'depot',101,2
union select 'depot',101,5
union select 'depot',102,1
union select 'depot',102,3

store
item
revenue

depot
101
1

depot
101
2

depot
101
5

depot
102
1

depot
102
3

select store, item,
sum(sum(revenue)) over (partition by store order by item)
from test
group by store, item

Expected output:

store
item
quantity

depot
101
8

depot
102
12

Error :

[Code: 40000, SQL State: 42000]  Error while compiling statement:
FAILED: SemanticException Failed to breakup Windowing invocations into
Groups. At least 1 group must only depend on input columns. Also check
for circular dependencies. Underlying error:
org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:24
Expression not in GROUP BY key 'revenue'

How to fix ?


texts: WHERE attribute having two relation lines in the same SQL

texts:
I have a database designed with tree tables:

checklist (id, name)
tag (id, name)
checklist_tag (checklist_id - FK, tag_id - FK)

Running this SQL:

SELECT cc.id as checklist_id, cc.name as checklist_name, ct.name as tag_name
FROM checklist cc INNER JOIN checklist_tag cct ON cc.id = cct.checklistId
INNER JOIN tag ct ON cct.tagId = ct.id
WHERE ct.name IN ('tag 1', 'tag 2')

I get this result:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2
3               checklist 3     tag 2
4               checklist 4     tag 2

But I need to select the checklists having the tags 1 and 2. Something like this:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2

Is there anyway to do that with a single select?


texts: Disk queue length very high in SQL Server 2016

texts:
I have two Servers A and B with the same settings, but on Server A, the query runs in 6 minutes and on Server B the same query runs in 15 minutes.
what I noticed is that on Server B is that disk queue length very high.

While on Server A, the same query does not have a large number of disk queue length.

What could be different for the same query to run in less time on Server A.
Could it be some problems with the Discs?
Thanks.


texts: Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?

texts:
Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index?

I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes.

When I try creating both a Clustered Columnstore and Nonclustered Columnstore
I receive error:

Msg 35339, Level 16, State 1, Line 7
Multiple columnstore indexes are not supported.


texts: Audit Trail trigger can't detect if dates are the same

texts:
I know this is a common problem with SQL language triggers, but I just cannot seem to get this right.

Basically I'm trying to write an "Audit Trail" trigger for all of my fields. I'm using an IF UPDATE()... branching scheme which, as I've discovered on other SO posts, is not a reliable function.

My problem is centered around a date field that may be NULL. I'm simply trying to write a trigger that will only write to my log table if the date field has changed (either in value or to/from NULL)

Right now my code in this branch is as follows, and will always fire no matter if the dates didn't change (but other fields did):

IF UPDATE(DueDate)
BEGIN
    DECLARE @insertedDate date
    DECLARE @deletedDate date

    SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
    FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate

    IF (@insertedDate = @deletedDate)
    BEGIN
        return
    END

    SET @IsModified = 1
    SET @LogText += ('Due: ')
    SELECT @TempText = '"' + ISNULL(convert(nvarchar(255), i.DueDate, 107), '[deleted]') + '" '
    FROM inserted i
    SET @LogText += @TempText
    SET @TempText = ''
    SELECT @TempText = '(was: "' + ISNULL(convert(nvarchar(255), d.DueDate, 107),'[not set]') + '"), '
    FROM deleted d
    SET @LogText += @TempText
    SET @TempText = ''

END

Which will incorrectly produce (instead of not running at all):

Due: "Mar 04, 2020" (was: "Mar 04, 2020"),

Both @Temptext and @LogText are of type nvarchar(MAX); and @LogText is a running string which contains the final log line to be written (and will concat through the whole procedure with +=)

Is there anything I'm missing with how I'm constructing these conditionals? I've tried several iterations and function methods but this one is just not making sense to me...


texts:
I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.

I use inverted LIKE clause on field digits (which contains prefixes in form +48%, +49%, +1%, +1232% and so on).

Therefore I can't use normal index on that field.

I managed to get substantial improvement by using IOT on gvcode and cgi field (which are part (first two cols) of primary key).
I also looked at some oracle text indexes but can't find one that will match longer input with shorter prefix in the table.

Is there any other way to perform such search that is faster than this approach.

Here is the query which gives a list of all matched prefixes (I sort it afterwards on digits length).

  select  t.gvcode,  t.digits
                from NUMBERS t
                    where
                        t.gvcode=ZONE_SET_CODE
                        and t.cgi=cgi_f
                       and ( PHONE_NR like t.digits)
                         order by length(digits) desc


texts:
I would like to add an additional row that will display the Total order count for all the different Customers in the Customer Dimension. Below is the query that I have been trying to modify.

SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS
    ,NON EMPTY {([Date].[Year].[Year].ALLMEMBERS
       * [Date].[Quarter].[Quarter].ALLMEMBERS
       * [Order].[Customer].[Customer].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION
    ,MEMBER_UNIQUE_NAME ON ROWS FROM [Order] CELL PROPERTIES VALUE

Currently it is displaying as :

                                               | Order Count
Calendar 2012   |Quarter 1, 2012    |D         |31

What i need is below:

                                               | Order Count
Calendar 2012   |Quarter 1, 2012    |D         |31

Total                                          |122011


texts:
I am trying to read flat file with some data and I need to convert some columns to time type in the format of HH:MM:SS.
Below is the example how they are stored in the file (on the left) and how I want them to see in the database (on the right).

846 ---> 08:46:00
1323 ---> 13:23:00

I am performing following transformations:

if NA value appears, I am converting it to NULL (with Derived column)

then I am concatenating it to string in format of HH:MM:SS (using Derived Column)
LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"

then I am converting it to the type DT_DBTIME2 with scale of 0 (using Data Conversion):

And this last step makes the package falling. Here is error message:

[Data Conversion [20]] Error: Data conversion failed while converting
column "ArrTime" (166) to column "Copy of ArrTime" (27).  The
conversion returned status value 2 and status text "The value could
not be converted because of a potential loss of data.".

I am not very experienced in SSIS, but I really cannot see any reason for such an error.
When I tried to perform it without conversion, everything had gone fine.
Can anybody help me with that?
Greetings,
Chris.


texts: What is the proper procedure for fixing materialized view replication?

texts:
Oracle 10 Standard:

I have a number of materialized views built on pre-built tables.
These are fast refreshed from a remote database over a link from one master db.
They've generally been working fine, but sometimes the view needs re-creating when a database server is bounced.

Comparing the remote (replicated) table with the master table now shows there are a few records missing in the replicated table.  Re-creating the materialized view doesn't fix this (or manually refreshing it).  There are no errors shown with the jobs that fast refresh these.

What is the procedure to fix this?  Do I need to drop & regenerate the materialized view log on the master table?

Additional information:

Ok, dropping and recreating the materialized view log did not fix this.

I suppose I can just copy the records manually, but I thought there'd be a more generic way to fix.  If complete refresh is the answer, I understand that it truncates the table first which is something I'd really like to avoid if possible.

As requested, here is the script that creates the materialized view:

CREATE MATERIALIZED VIEW AUTO_EVENT_DEFN
ON PREBUILT TABLE
USING INDEX
TABLESPACE DATA1
PCTFREE 10
INITRANS 2
STORAGE
(
  INITIAL 81920
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
REFRESH
NEXT SYSDATE + 1/(24*60)
FAST
WITH PRIMARY KEY
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
select   aed_rid, name, description, max_check_freq_sec, max_result_freq_sec, header, footer, subject, html_format,
report_group, trigger_event, max_lines, html_base_size, ins_or_update, active, valid, defn_type, cust_req, last_modified,
modified_by, sum_cols, report_delay_secs, process_queue, sql_string, report_security, results_type, multi_loc
from auto_event_defn@kitlive;


texts: Implementing a one to zero or one relationship in SQL

texts:
Let us say that I am designing a database for a scenario where there exists a one-to-zero-or-one (1-0..1) relationship. For example:

There is a set of Users, and some Users may also be Customers.

Thus, I created the two corresponding tables, users and customers, but…

…What is the best way to represent and implement this situation in a given SQL platform? I have considered two possible solutions:

In the users table, add the customer column which may be either a FOREIGN KEY
reference to customers or a NULL mark.
In the customers table, include a user column (set with a UNIQUE constraint) which points to the users table.

I have already asked a similar question in some forums, but the answer
was basically “whatever you need”, “whatever you think convenient”. I
do not like this kind of answer. I want a serious piece of DB theory
instead, a well founded answer. Where can I read about 1-0..1
relationships?


texts: SQL Server DB Restore Unable to browse directory

texts:
I have a problem with SQL Server 2008.
When I try to restore a backup (located in the default location). This error appears.

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

I've tried to google about this error but nothing has been resolved, I've also tried to assign the folder Full Access to Everyone and the error still appears.

The SQL Server services (Engine and Agent) are running under the NetworkService system account.

However in the folder browser window I can browse through the C:\ drive

Edit:
The server is standalone, I have only one with Windows Server 2008 x86 as a domain controller.


texts: Increase in IO Wait after adding indexes with postgres

texts:
I've recently been put in charge of a system that has been fairly untended for a period of time.

The majority of activity is via queued worker jobs based off of data feeds from other systems/data sources.

Via the slow query log I identified a number of slow queries using the PostgreSQL logs and added indexes to address these. This started around 7am or so.

Would the addition of these indexes have contributed to the large increase in IO/Wait?
What steps can I take to reduce this?


texts: Is there a name for this type of data structure?

texts:
I'm working with a database that has a structure that few of my colleagues have encountered. The database is a fairly standard Oracle SQL database, but it has a number of tables called xxx_param, which take columns away from the main tables and let you add additional "columns" without altering the data structure in any way.

For example:

Table: Premises

Id         | Zip_Code    | Address
0123       | 90210       | 1 Acacia Avenue

Table: Premise_Param

Premise_ID | Name        | Value
0123       | DoorColor   | Green
0123       | WindowCount | 4

So in order to find the door color of a specific premise, you'd write a query like:

Select * from Premises
Left join
(select * from Premise_Param
where Name = "DoorColor") doorColor on doorColor.Premise_ID = Premise.ID

So my question is, is there a name for  design of database set up this way?


texts: Un-attaching SPL 2008 BD not willing to Attach

texts:
I have a 5 year old SQL Server 2008 database.  There are actually 3 databases

NL_Dev
NL_Test
NL_Prod

Each has a log file. We have been using the development database, but all three seam to be linked together. The .MDF files are all about 3.2GB, but the dev log file is 450GB.

I was trying to shrink the files, but only had 10GB's of space left on the array.

I had a backup of NL_Dev and didn't think it would make any difference to delete the test log, so I stopped SQL Server and deleted the test log File. It didn't make any size difference, but now I can't access the data in NL_Dev.

I tried un-attaching the DB, but now I can't attach it since the file is missing.

I am not a DBA, just a small business owner that has worked with Access and SQL Server for 20 years, and I've run out of rope to hang myself. Please Help

Gordon Mills
Newlife Electronics


texts: Why mysql-noinstall5.6.x.zip includes ibdata file?

texts:
English is not my main language, please feel free to correct me or ask for clarification or a different explanation.
I've been messing around with MySQL for Windows and trying to understand how it works. Right now I'm working with MySQL 5.5 and 5.6 using the no-install.zip version and following the steps explained in the manual to get them both to work.
Any versión(¿subversion?) of MySQL 5.5.x (i.e., 5.5.12, 5.5.62) includes the data folder and only three folders inside it (mysql, performance_schema and test). I decompressed the zip, generated a my.ini file and ran the server. It created the ibdata and log-files and everything worked fine.
As for MySQL 5.6.x, it also includes the data folder with the same three folders as MySQL 5.5 but it already has the ibdata and log files (I downloaded different subversions just to make sure, and the files are present in all of them). So I followed the same process as for MySQL 5.5: I decompressed the zip, generated a my.ini file and ran the server, everything worked as expected.
Just for the knowledge's sake I deleted everything to make a clean start. Decompressed again the MySQL 5.6 zip file but this time I deleted the default ibdata and log-files and ran the server and it worked... aparently. It created the ibdata and log-files. But it lost some information from the mysql database. For example, I can no longer check the innodb_index_stats table using a select nor a describe query.
Now, my questions:

Why does the MySQL 5.6 zip file includes default ibdata and log-files?
What does those default files contain?
I'm fully aware that deleting the ibdata file erases the information and it's the reason the server is missing some data. But, why when they are created from scratch they miss this information? Isn't the server supposed to create them with full default information just as MySQL 5.5 does?

I already tried to answer those question but I couldn't find anything useful. I slightly read the first MySQL 5.6 release notes and randomly others between the first and last but didn't find any explanation on why it does include default files.
I also searched in Google about ibdata file but the answers are more in the sort of how to recover/backup/dump information from damaged ibdata or how to shrink it.
So, why? :)


texts: Using to_base64 function and regexp_replace with backreferences in MariaDB


texts: Applying data changes from one database to another

texts:
I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production.
Currently I work as follows:

Set all the parameters needed on development database (this might take months), the user validates everything and then I have to apply everything again (which might take some more months). I can't just import the development database into production, because all the data from production will be lost.
Is there something that can be done to avoid this "reworking"?

I tried Database Copy and Data Diff tools available on SQL Developer, but didn't work, since it has a lot of problems with dependencies when inserting new data on the tables..

The point is to keep the new/changed data made by the users in production while I work on the development database. Then import what I did without losing what the users did meanwhile.

Thanks in advance.


texts: How to return a boolean response for a unique and new entry

texts:
I am looking for an SQL query to identify if there are any new classs in my table:

CREATE TABLE `Features` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_username` varchar(64) NOT NULL DEFAULT '',
  `class` int(11) DEFAULT NULL,
  `new` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Edge cases

Here class 3 has no previously new=0 entries. So the SQL should return 1.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     3 |   1 |

Here class 2 has already a new=0. So the SQL statement should return 0.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     2 |   1 |

Here there are no new classes. So the SQL statement should return 0.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     3 |   0 |

Here all classes are new. So the SQL statement should return 1.

| class | new |
|-------|-----|
|     1 |   1 |
|     2 |   1 |
|     3 |   1 |

This query should run on a single account_username.


texts: how to view colmn names of joined tables

texts:
Is there a way to make this query:

SELECT * FROM food
JOIN conditions ON food.id = conditions.id
JOIN flavors ON food.id = flavors.id
JOIN symptoms ON food.id = symptoms.id
JOIN tags ON food.id = tags.id
WHERE 1

show column names instead of 0-infinity

To be a little more clear, I need the column names from the tables (flavors,conditions,symptoms, and tags) to show up instead of a numerical value.

The above query gives me this but far longer:

 9 => string '15.012820512821' (length=15)
 10 => null
 11 => null
 12 => string '17.846153846154' (length=15)
 13 => null
 14 => null
 15 => string '5.3846153846154' (length=15)
 16 => null
 17 => null

and the column names those numbers represent:

 0 =>
    array (size=1)
      0 => string 'id' (length=2)
  1 =>
    array (size=1)
      0 => string 'name' (length=4)
  2 =>
    array (size=1)
      0 => string 'addadhd' (length=7)
  3 =>
    array (size=1)
      0 => string 'alzheimers' (length=10)

In phpmyadmin I can see that  9 => string '15.012820512821' corresponds to addadhd, but 9 != 2, so I can't just match those values. If I had the column name instead of 9, I could easily match the value. I'm very frustrated.


texts: MariaDB: Insert records (list of tuples) into table if not exists

texts:
Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1;

INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1;

Generally speaking, as rule of thumb the less queries the better which is why I want to rewrite the sql queries into one query. This should make it even faster when having millions of datasets as the WHERE clause would only iterate through the table once, hence this should be possible:
WHERE col1 in ('1','3') AND col2 in ('2','4')

But when I try multiple select statements in a single query like:
SELECT * FROM
(SELECT '1','2') AS tmp1,
(SELECT '3','4') AS tmp2

SQL throws this error:
#1136 - Column count doesn't match value count at row 1
The problem is with SELECT * FROM (SELECT '1','2') AS tmp.
How can I use several tuples (1, 2) (3, 4) in the subquery for it meet the WHERE condition?
Is there a shorter and faster way to achieve this?
Any feedback is highly appreciated!
Please ignore the data types; the numbers should be formatted as int not as varchar.

UPDATE
When executing the following:
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '7','8') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='7' AND col2='8'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '9','9') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='9' AND col2='9'
) LIMIT 1)) t1;

SQL throws: Duplicate column name '9'


texts: db_owner does not have access to Windows folders

texts:
This is MSSQL Server 2008 R2 running on Windows Server 2008 Enterprise. Service Instance is running as 'Network Service'.

I have two logins, UserA and UserB. UserA is a sysadmin. UserB is a db_owner for database 'Demo'.

I login as UserA and can take a backup of Demo, no questions asked.

I login as UserB and try to take a backup of Demo. When clicking the 'Add' button to add a destination, and then the 'browse' button, I get the following error message:

G:\Backups

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

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

My question is:

After reading that folder access is based on the User Account that the SQL instance is running under (Network Service), why can the db_owner not browse to the specified folder?

Is there something I am missing?

Thanks


texts: Move data with schema change between MariaDB servers

texts:
I am tasked with the challenge to move data between two MariaDB servers. The source database is a MariaDB 5.x, and the destination database is a MariaDB 10.x. The problem is that I need to add a column to the target.

We are splitting a monolith service into microservices. The new database needs an additional column (found in a separate table in the source database).

Is the below the way to go or are there any nicer way of moving the data?

SELECT CONCAT("insert into table4(id,level,name,levelt2) VALUES(",   quote(table1.id),   ",",    quote(table1.level),   ",",    quote(table2.name),   ",",    quote(table2.level),    ");") as q
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

cat query.sql|mysql --skip-column-names --raw > table4.sql


texts: MySQL temporary column in SELECT statement

texts:
I'd like to do some calculations in a MySQL query.
I've created some temporary columns which I'd like to use for the calculation.

SQL fiddle for currently working (but discouraged) SQL query:
http://sqlfiddle.com/#!2/5dc99/14

According to the MySQL docs 9.4 User-Defined variables(http://dev.mysql.com/doc/refman/5.0/en/user-variables.html): "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement."

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  @redemption_price := product.redemption_price AS redemption_price,
  @customer_price_increment := (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE
      @redemption_price * (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  @general_price_increment := (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE
      @redemption_price * (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  @sale_price := @redemption_price + (
    CASE
      @customer_price_increment
    WHEN 0 THEN
      @general_price_increment
    ELSE
      @customer_price_increment
    END
  ) AS sale_price
FROM
  product

I'd like to add HAVING sale_price > 250 to only show products with a product price higher than $250.

Adding HAVING sale_price > 250 doesn't work, because of the usage of the user defined variables (see http://sqlfiddle.com/#!2/5dc99/19).

I've removed the user defined variables as much as I could, but I'm not getting it to work without all the user defined variables.
http://sqlfiddle.com/#!2/5dc99/24 shows results, because he HAVING clause isin't there.
http://sqlfiddle.com/#!2/5dc99/23 doesn't show any results while it should, because there are products that costs more than $250.

Simply removing all the user defined variables doesn't solve the problem, because the temporary columns cannot be reused (http://sqlfiddle.com/#!2/5dc99/25). Unknown column 'general_price_increment' in 'field list'.

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  product.redemption_price AS redemption_price,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE
      (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE
      (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  redemption_price + (
    CASE
      customer_price_increment
    WHEN 0 THEN
      general_price_increment
    ELSE
      customer_price_increment
    END
  ) AS sale_price
FROM
  product
HAVING
  sale_price > 250

What can I do to make this work?


texts: Map Oracle roles to Active Directory groups?

texts: Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?


texts: Move Commonly-Repeated Fields into a new Table

texts:
I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields.

In this case, we have customers, offices, warehouses etc. that have address fields like Address1, Address2, ... Postcode. So, I was strongly considering making a new Addresses table with all these fields in, and an ID, and linking to that with foreign keys for all of the different types that contain addresses. The same thing could be done for groups of telephone/fax numbers too.

To illustrate this point, here's the current situation in an abstract form...

Customers        Offices
- CustomerID     - OfficeID
- FirstName      - OfficeName
- Surname        - Telephone
- Address1       - Address1
- Address2       - Address2
- Address3       - Address3
- Address4       - Address4
- Postcode       - Postcode

...and after the change...

Customers        Offices         Addresses
- CustomerID     - OfficeID      - AddressID
- FirstName      - OfficeName    - Address1
- Surname        - Telephone     - Address3
- AddressID      - AddressID     - Address3
                                 - Address4
                                 - Postcode

The objective here isn't really normalization (I'm not intending to eliminate duplicate addresses here, even though it may be possible) but to help keep similar subtypes of data consistent. It also means that in the model code, I can treat all addresses in the same with with one class, whatever they belong to. I hope that the overhead in joining these tables wouldn't be that high, especially since I don't always need the addresses for all use cases.

Your opinions/experiences please!


texts: Is STRICT_TRANS_TABLES required for replication? Particularly, I need to know about it for Percona Cluster.


texts: Performance gain with MultiSubnetFailover for single subnet

texts:
On BOL I read following about MultiSubnetFailover=True:

  The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to preconfigure new clients to support future spanning of subnets without any need for future client connection string changes and also optimizes failover performance for single subnet failovers.

As I understand the working of MultiSubnetFailover, with this option set the client driver sets up a socket for each IP address associated with the listener. They are all checked in parallel to speed up the process to find the online IP and the first one that response will be the one used for the connection. Here I see a performance gain.

But where is the performance gain in a single subnet? There is only IP associated with the listener.


texts: MariaDB (RDS) going temporarily offline (with memory drop)

texts:
We have an issue with a database of ours on RDS running MariaDB 10.4.8 on a t3-large instance type.
For the past week or so, it's been sporadically inaccessible with no errors I can find. During the minute or two that it's down I can't run 'show process list' it just hangs. It fixes itself and is back working within a few minutes. When it comes back it looks like it has a lot of connections and queries to catch up on.
Changed params from default:
ft_min_word_len 1
innodb_buffer_pool_size {DBInstanceClassMemory*17/20}
innodb_ft_enable_stopword   1
innodb_ft_min_token_size    1
log_bin_trust_function_creators 1
max_statement_time  30
sql_mode    NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Freeable Memory:

Swap space:

Performance Insights:

If there any way to find out what's causing it. Is it not enough free memory? What's a normal threshold to be running to?


texts: How Can I get multi-Dimensional array by value

texts:
In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this:

[{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}]

I want to get when id is equal to 1. I can do this by following query:

SELECT * FROM mydb AS f WHERE f.user_tag #>> '{0,id}' = '1'

But, I have to provide 0 as a key. I want to search all id columns and return matched table rows. If I already know 0, why would i search in first place.

Any idea will be appreciated.

Note: SELECT * FROM mydb AS f WHERE f.user_tag ->> 'id' = '1' is not worked.


texts:
I was running some data prep queries when I noticed that one of them sometimes ran much longer than expected. The query should have finished in seconds but sometimes would take minutes to run. Here is the query:

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL, ID2 INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (20000000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3;

Here's an actual execution plan for when the query runs quickly. I can't find anything out of the ordinary in the plan.

However, there is something wrong in the query plan when the query takes longer than expected. The final (reading from right to left) nested loop operator generates around 13.3 billion rows. I would expect only expect it to generate around 20 million rows because of the row goal introduced by TOP. I've observed between one to three threads spin out of control and it seems like this is directly responsible for the long runtime.

Why does my parallel query sometimes process billions of extra rows? What can I do about it?

I am on SQL Server 2016 SP1. I've been unable to reproduce the issue with a compatibility level of 120.

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3  (Build 14393: )


texts: How to convert UPDATE operation into INSERT using triggers

texts:
Given I have a table User with column status (active/inactive)

these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified.
and for attaching to newer entities I only need the latest updated ones.

So when an UPDATE is performed, I want to make the existing row inactive,
and INSERT the updated values as a new row.

I am using PostgreSQL


texts: Recommended page file size for SQL 2008R2 on Windows 2008R2

texts:
This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 bit Windows 2008 and Windows 2008R2. This no doubt works fine for general purpose servers. I'm wondering what the guidance is for SQL Server 2008R2 running on Windows 2008/R2 64 bit?

I'm presuming we want as little in memory data to be hitting the page file otherwise SQL could be hitting the disk twice for data. Does SQL Server even allow data in memory to hit the page file? I've hunted through SQL Server 2008 R2 Books Online for guidance but haven't yet found any mention of page file use.

Here's a potential usage scenario: Given a physical server with 64GB of RAM, is a pagefile necessary for the entire 64GB of RAM? Should we gear it up for 96GB of pagefile? That does seem a bit excessive for a single file. I know conventional wisdom has been that Windows couples pagefile to memory in an attempt to make swapping out apps easier on RAM, but is that true? Will a less than 64GB pagefile hinder performance here?


texts: A lot of ad-hoc queries impact on server

texts:
We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc Workloads' so the query cache doesn't get bloated.
I've asked the supplier to adjust his queries but he doesn't want to change them.

My question is, what impact can a lot of ad-hoc queries have on my server.
My first thought is a lot of compiles.


texts:
I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency.

Now, I have this table:

category
--------------------------
category_id   name         parent
1           Phillips       (null)
2           Barnes          1
3           Moore           2
4           de Mohrenschildt   3
5           Oswald          9
6           Hunt            2
7           Burnham         6
8           Buckley         7
9           Paine           4
10          Sturgis         6
11          Hunt            3

I have this query:

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Phillips'
ORDER BY lev2

that returns:

lev1        lev2    lev3    lev4         lev5      lev6
Phillips    Barnes  Hunt    Sturgis      (null)    (null)
Phillips    Barnes  Moore   de Mohr'ldt  Paine     Oswald
Phillips    Barnes  Hunt    Burnham      Buckley   (null)

and, for a new "node":

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Moore'
ORDER BY lev2;

which returns

lev1    lev2        lev3    lev4    lev5    lev6
Moore   Hunt        (null)  (null)  (null)  (null)
Moore   de Mohr'dt  Paine   Oswald  (null)  (null)

Which, as you can see, is awfully close to this, in form, with some html:

Phillips supervised:

Barnes supervised:

Moore supervised

de Mohrenschildt knew:

Oswald ...

Sturgis knew

Hunt knew

Moore worked for...

And can hopefully mature into this one day (what's below the tabs in the image)...:

I have a couple of questions about this table and query.

1) as can be seen in the name "Hunt", the problem I may have is that when someone is assigned to more than one parent, I'm going to have to enter a new record for him. And as there are going to be, I'm guessing 5 or 600 unique persons, to repeat many of them seems to me to be unnecessary. Can someone see a better way to handle this issue, or is it even an issue? I just don't like redundancy...

2) along these lines, if you look at the image, you'll see that some of the names are in RED, denoting an AJAX dropdown paragraph of pertinent information regarding that immediate relationship. So "Hunt" may have a paragraph or two under this particular branch, and another on another branch somewhere else. I'm thinking maybe the need to repeat "Hunt" wherever necessary in the table would allow for calling another set of data to fill the AJAX dropdown. Am I asking for trouble with what I have here so far...?

3) since there will be so many branches starting with different names, is this query efficient enough if I have to run the query perhaps 40 or 50 times on one, (of many) pages, especially if it's calling some extra data as mentioned above?

I like this "schema," if that's the right word, because it seems to be really extensible. This database has the potential to grow indefinitely.

I really appreciate anyone's help with this. I'm so happy I've finally found something that's working for me (I never even knew what an Hierarchical DB is until last night, and that that's what I was looking for...)


texts: HAPROXY for Postgres Failover Master/Replica

texts:
I have successfully set up a postgres master-replica with repmgr for automatic failover which can be found over here https://github.com/mgonzaga1990/pg-rpi-failover
Now what I'm planning to do next is to set up HAProxy However, I can't find resources that could teach me how to properly set up load balancing together with repmgr.
My Scenario is
When master failed. One of the replicas/slave/standby servers will become the master.
Now if that happened should I always change the configuration of HAProxy do we have a way to make it automatic as well?? where HAProxy could detect who the next master is?? Then route every request to do WRITE operations on that newly elected master?
Moreover, the old master; once comes back up will not be the master but becomes slave/replica or standby.
I'm using postgres-9.6
Thanks for the response.


texts: 'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX?

texts:
I'm running PostgreSQL 9.3 on Mac OS X Yosemite.

I try to create an unaccent lowercase trigram index. To achieve it I did this:

mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1";
       CREATE EXTENSION unaccent SCHEMA public;
       ALTER FUNCTION unaccent(text) IMMUTABLE;
CREATE EXTENSION
CREATE EXTENSION
ALTER FUNCTION

Then I tried to create the index:

mydb=# CREATE INDEX author_label_hun_gin_trgm ON address
       USING gin (public.unaccent(lower(label_hun)) gin_trgm_ops);
ERROR:  text search dictionary "unaccent" does not exist

... and got this error. If I try to list the available text search dictionaries the unaccent dictionary seems to be there:

mydb=# \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
 public     | unaccent        |

Any idea what could be wrong here?


texts:
I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes
See the Sample Data below :

    addon       status  amt1    amt2    company
    theft cover yes      7       7      comp1
    key loss    yes      2       2      comp1
    theft cover no      NULL    NULL    comp2
    key loss    yes      2      33      comp2
    key loss    yes      1       1      comp3
    theft cover yes     12      22      comp3
    theft cover yes     11      22      comp4
    key loss    no      NULL    NULL    comp4
    theft cover yes     22      55      comp5
    key loss    yes     33      44      comp5

My query:

SELECT     addon, status, amt1, amt2, company
FROM         test
WHERE     (addon = 'theft cover' OR
                      addon = 'key loss') AND (status = 'yes')

Please help me. The above query is not working.


texts:
Apologies if this has been answered elsewhere but I couldn't find anything here or online.

Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if certain characters exist within the file and if they do I'd have to skip to the next section of the stored procedure. So far I've simply bulk inserted the entire file into a one column table then did:

IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%}%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%~%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%#%')>0 GOTO NEXTSECTION

It's reliable but very slow when dealing with large files (sometimes over 10GB). I'm thinking the below query would be quicker for large files

DECLARE @FILE NVARCHAR(MAX)
select @FILE = BULKCOLUMN from (
select * from openrowset(BULK N'filpath', single_clob) [a]
)a

IF(SELECT IIF(@FILE LIKE '%{%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%}%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%~%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%@%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%£%',1,0)) = 1 GOTO NEXTSECTION

But as I say Bulk insert is reliable and I'd hate to risk the file being truncated when using OPENROWSET if there is some sort of file size limit.

Any advice on the matter would be appreciated.


texts:
have an issue with MariaDB 10.2.12 running in aws rds (single master, no slave)

when I run prepared statement using a variable substitution

PREPARE stmt1 FROM
"select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ?
 group by `subscribers`.`id`
 limit 1";
SET @a = 77586744;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;

it takes 13 minutes to process (those tables have millions rows)

however, if I run the very same query without variableS:

SET @sql = CONCAT("select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ",77586744,"
 group by `subscribers`.`id`
 limit 1");
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;

it only needs 0.05s to run. The same time it takes if I not use PREPARE at all.

According to profiler output the long run involves several "converting HEAP to Aria" with reindexing and sorting it, which do not take place if no variable involved.

Why do this converting happen? it seems there is no lack in tmp/heap since the query runs in 0.05s, thus it's  "USING variable" changes something I don't know.

btw, we've faced the issue running application on PHP 7.1.20 with Laravel Framework 5.6.39,
so I cannot just rewrite a query to get rid of "USING variable" as it something that happens under the hood of PDO.

UPD:
EXPLAIN for slow query

EXPLAIN for fast one

OK, explain differs, but it's kind of expected from profilier output.
The thing I still  don't get is why it differs while the query stays the same.


texts: Why doesn't SQL Optimizer use my constraint?

texts:
I want to create a table with a NOT NULL bool column.
I use TINYINT with CHECK constraint BETWEEN 0 and 1.  The constraint is new and thus trusted

Now I would expect that SQL optimizer now knows that this column can only be 0 and 1 so when I write query col >= 2 I will see Constant Scan in actual execution plan (like when I would when check for NULL or SELECT TOP (0)

But this is not the case, it opts for the table Scan. Do I also need to have index on this column?

In my test below I use TINYINT with CHECK constraint. User Defined Type based on TINYINT with bound RULE and good old BIT.

GO
CREATE TYPE dbo.myBool
FROM [INT] NOT NULL
GO

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
go

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'
GO

DROP TABLE IF EXISTS dbo.RuleTest
CREATE TABLE dbo.RuleTest
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , oldSchoolBool TINYINT NOT NULL CHECK (oldSchoolBool BETWEEN 0 AND 1)
    , customBool dbo.myBool NOT NULL
    , myBit BIT NOT NULL
)

;WITH tally (n)
AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS a(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS b(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS c(n)

)
INSERT INTO dbo.RuleTest
(oldSchoolBool, customBool, myBit)
SELECT
    ABS(CHECKSUM(NewId())) % 2
    ,ABS(CHECKSUM(NewId())) % 2
    ,ABS(CHECKSUM(NewId())) % 2
FROM tally t

SET STATISTICS IO ON;

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool IS NULL

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2

go
SELECT * FROM dbo.RuleTest rt
WHERE rt.customBool >=2
go
SELECT * FROM dbo.RuleTest rt
WHERE rt.myBit >= 2

SET STATISTICS IO OFF;

I see one Constant Scan for the NULL check and 3 table scans for the rest.


texts:
We have a problem with a query from a SQL Server 2008R2 - 64bit
to an Oracle Linked Server.

We use the Oracle 64-bit driver for Windows.

When we query a certain table we get the following error:

  Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle'
  for linked server 'SERV_VISION2' returned data that does not match
  expected data length for column
  '[SERV_VISION2]..[VISION].[INV_M001].CUS_NM'. The (maximum) expected
  data length is 30, while the returned data length is 24.

The problem seems to be the data in the CUS_NM column.
In the rows where he gives this error the data is like this:

¿¿¿

So like a question mark but upside down. There doesn't seem to be a problem with the
length of the column, for one reason he can't handle this data I guess.

Did anyone experience this problem?
Anyone a clue how to solve this?


texts: Error statement is not logged, only *password---- with many dashes

texts:
I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works great.
Sometimes, the SQL statement (sql_text) that is logged contains only the following:

*password------------------------------------------------------------------------------------------------------------------------------------------

What is the reason, and how do I find out which statement is causing this?


texts: Can I rely on the order of columns on an index generated by database tuning advisor

texts:
I got the following recommendation on a table:

CREATE NONCLUSTERED INDEX
    [_dta_index_MyTable_6_44943632__K16_K52_K8_K9_K15_1_2_4_5_6_7_10_11_12_13_14_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_]
ON [dbo].[MyTable]
(
    [ColumnA] ASC,
    [ColumnB] ASC,
    [ColumnC] ASC
)

However, I am a bit surprised about the order of the columns in the recommendation. I would expect the result to be ColumnB, ColumnA, ColumnC.

Can I rely on the recommendation or should I decide the order of the columns?


texts: Paginated SqlServer Query with Changing Data

texts:
The traditional way to do server-side pagination of queries with SqlServer (earlier than 2012) has been to use ROW_NUMBER(), as somewhat covered here and in other places.

Let's say that I'm building an "infinite scroll" UI component in a web app, and using such a paginated query on the server side to fetch blocks of results which are then rendered.  When the user scrolls down, the next page of results are fetched using the ROW_NUMBER() of the last record from the previous request as the starting point for a new request.

This works very well, but I'm wondering what to do when the underlying data changes.  If my results are ordered by time, and a new record is entered, that new record would be at the "top" of the list.  The ROW_NUMBER() of the results would then all shift by one, breaking the continuity of the infinite scroll, thereby producing a duplicate returned record.  Deleting a record results in the opposite problem of a "disappearing" record because the client already "fetched that ROW_NUMBER()".

Is there a way to overcome this in the query?  I know I could suck out the entire result set and cache it in memory in my App Server or something, creating a sort of "data freeze" at least for that session, but I'd prefer not to.  Given that each row in the result set has a unique PK aside from the ROW_NUMBER() value, it seems like it might be possible to ask "give me the next 20 rows starting with this PK" to avoid shifting ROW_NUMBER() values, but I can't see a way of expressing this.


texts: Is it possible to grant trigger permissions on only one schema MySQL

texts:
I am an analyst that was recently granted a schema on MySQL that I could use for some business needs.

I was told by my DBA that the permissions associated with Triggers require global super user privileges. It's reasonable that I don't have global super user privileges, but would like to have those privileges for a schema that I have ownership on.

Is this something that is possible? If so, how do you do it?

Thank you


texts: How to insert values into a table from two different tables?

texts:
I have three tables

students table
------------------------------------
id(PK, A_I)  |  student_name | nationality

teachers table
------------------------------------
id(PK, A_I)  |  teacher_name |  email

classroom table
----------------------
id(PK, A_I)   | date   | teacher_id(FK to teachers.id)  |  student_id(FK to students.id)

If I was given teacher's name (david for example) and student_id (7 for example) and asked to insert the teacher_id into the classroom table based on the id in the teachers table, I would do :

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', id, 7
from teachers
where teacher_name = 'david';

Now, what if I was not given the student's id directly and given only the name of the student? Suppose I was given teacher's name 'david' and student's name 'sam'. How do I get the teacher_id from teachers table and also student_id from the students table and insert both into the classroom table based on their respective names?


texts:
I need to grant select permissions to a sensitive table over the linked server. This table should only grant access to members of an AD group. I currently have other linked servers set up with remote login credentials which obviously will circumvent any permissions and grant all those who have access to the server permissions to the Linked Server. How do I go about correctly granting AD based  security pemissions?

Thanks


texts: Couldn't find sql server agent in Sql Server 2014 Management Studio?

texts: Is there any way to add it in SQL Server Express edition?


texts: Updating a PostgreSQL Table With Different Random Dates

texts:
I have two tables in PostgreSQL, Employee and Leave like following:

CREATE TEMP TABLE employee_table AS SELECT
  id::int,
  name::text
FROM ( VALUES
  (1, 'John' ),
  (2, 'David')
) AS t(id, name);

CREATE TEMP TABLE leave_table AS SELECT
  id::int,
  leave_date::date,
  emp_id::int
FROM ( VALUES
  (1, '01/10/1993' ,1),
  (2, Null         ,1),
  (3, Null         ,1),
  (4, '02/12/1990' ,2),
  (5, Null         ,2),
  (6, Null         ,2)
) AS t(ID,Leave_Date,Emp_ID);

I want to update the Leave table and set the Leave_Date column to a random date maybe 01/01/2000 for each employee but if an employee has more than one Null entry in Leave table, I want to update his null entries with two different dates which means one employee should not have two same Leave_Date value in Leave table and after update my leave table should look like following:

ID        Leave_Date    Emp_ID
1         01/10/1993      1
2         01/01/2000      1
3         01/01/2001      1

As shown above, initially john had two null entries in Leave table and the result shows that those entries are update with two different dates. Is there any way to this?


texts: Mongo sharding issue with chunk split and Data transfer

texts:
I am getting this error in my primary shard

warning: moveChunk failed to engage TO-shard in the data transfer: still waiting for a previous migrates data to get cleaned, can't accept new chunks, num threads: 3
Tue Jul 22 11:06:47.639 [conn405736] MigrateFromStatus::done About to acquire global write lock to exit critical section

This is from Mongos collection :

Thu Jul 24 11:01:52.056 [conn148389] warning: splitChunk failed - cmd: { splitChunk: "ibeat20140724.visitor", keyPattern: { host: 1 }, min: { host: "eisamay.indiatimes.com" }, max: { host: "timesofindia.indiatimes.com" }, from: "shard0000", splitKeys: [ { host: "navbharattimes.indiatimes.com" } ], shardId: "ibeat20140724.visitor-host_"eisamay.indiatimes.com"", configdb: "192.168.24.192:27017,192.168.24.54:27017,192.168.24.55:27017" } result: { who: { _id: "ibeat20140724.visitor", process: "ibeatdb61:27017:1399428004:822699867", state: 1, ts: ObjectId('53d09a4705eb5bebaa264d1f'), when: new Date(1406179911981), who: "ibeatdb61:27017:1399428004:822699867:conn426252:1780690214", why: "split-{ host: "eisamay.indiatimes.com" }" }, ok: 0.0, errmsg: "the collection's metadata lock is taken" }

This is in from change log collection:

{ "_id" : "ibeatdb61-2014-07-21T23:28:04-53cda20405eb5bebaae92ed5", "server" : "ibeatdb61", "clientAddr" : "192.168.22.106:57441", "time" : ISODate("2014-07-21T23:28:04.308Z"), "what" : "moveChunk.from", "ns" : "ibeat20140721.pageTrendLog", "details" : { "min" : { "host" : { "$minKey" : 1 } }, "max" : { "host" : "beautypageants.indiatimes.com" }, "step1 of 6" : 0, "step2 of 6" : 177, "note" : "aborted" } }

Could you please help me to resolve these errors

Regards
Viren


texts:
I have table like:

bu_id   bu_name     parent_id
1       DefaultBU   NULL
601     bu1         1
602     bu2         601
603     bu3         NULL
604     bu4         603

I want to retrieve the record which bu_id is 1 and the records which has parent_id as selected records bu_id, like:

for 1 bu_id:

I need resuts like:

bu_id   bu_name     parent_id
1       DefaultBU   NULL
601     bu1         1
602     bu2         601

I have tried with:

SELECT b.bu_id, b.bu_name, b.parent_id
FROM `business_unit` AS a
INNER JOIN `business_unit` AS b
WHERE (
b.parent_id = a.bu_id
)

Resuts:

bu_id   bu_name     parent_id
601     bu1         1
602     bu2         601
604     bu4         603


texts: Is there a better way than this to split a string and get the first substring?

texts:
I'm trying to cut off everything after a certain character (in this case '&') and if there is no occurence of that character, get the entire string.

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

This seems to be a rather needlessly complicated way of doing it.   Is there a better/shorter way?

Here is a full test case to indicate what I'm doing...

declare @str as varchar(255)
set @str = 'abc&def&ghi'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'

set @str = 'abc'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'


texts: Why does this query return users not born on the date provided?

texts:
I am trying to search for a client using name and date of birth. My query is included below.

Select *
From Client
Inner Join Address
On Client.num = Address.cl_num
Where
   Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
    Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
And Client.year = 1973
And Client.month = 06
And Client.day = 05

The results however show clients who weren’t born on that day. Is there a problem with my statement?

The query is run on an iSeries server.


texts:
Could anybody please suggest me a way to monitor the load on my database server?
What perfmon counters should I monitor on daily basis and confirm that load on the server is increasing day by day [or] by week by week [or] every month. Also, suggest me if we can achieve this using DMV's so that we can store this info in a table using a SQL agent job. Because as a sql guy I would be more happy working with TSQL queries.

Do all this helps me, to put for forward my argument to the management that this server can handle this much of load based on data and in case if the load is increased on the server , we might need more resources ( like cpu, better storage , more RAM etc ...) or move to a new server which has more power.

Also, please suggest any load testing tools so that I can test this on testing environment.


texts:
I am trying to fetch User's Role Name by traversing through multiple tables in MySql. If I pull User.Id everything works but right after I add one more level for UserRole.Name it throws an error.

Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Opportunity.AccountId = Account.Id' at line 2

DB Tables

Opportunity has AccountId as column.
Account has UserId  as column.
User has UserRoleId as column.
UserRole has Name as column.

Query

SELECT UserRole.Name FROM Opportunity
LEFT JOIN Account JOIN User JOIN UserRole ON User.UserRoleId = UserRole.Id ON Account.UserId = User.Id ON Opportunity.AccountId = Account.Id LIMIT 1\G


texts: What to do with a table with too many indexes?

texts:
I'm the new DBA for this company.  I'm seeing some of their tables have a LOT of indexes; for instance some have over 50 or 60.

Is this a good thing? From my research, it seems this is not good for performance.

How would you manage this?

I would like to delete all the indexes and start from 0.  I think the developers have been using Tuning Advisor without really understanding indexes.


texts:
On a WordPress site I want to replace " !" and " ?" by " !" and " ?". The challenge is that the replacement string not uses a common space char but a thin space char (U+2009).

PHPMyAdmin is used for database editing with this query:

UPDATE wp_posts SET post_content = REPLACE (post_content,' !',' !');

(Yes, it's the correct thin space char, but Courier is a fixed-width font not showing a difference.)

It seems that PHPMyAdmin is not correctly handling the special char, because simulating the query delivers zero hits.

But trying another replace query works, so the statement should be correct. Tables are using "utf8mb4_unicode_ci" as collation, which seems to be the default and correct setting of WordPress. Database server is MariaDB (10.1.26).

I'm not familiar enough how to specify Unicode chars in SQL queries. Can someone help me out there?


texts: Setting connection properties (application_name) for Postgres connections

texts:
I want to attach an application name to my connection like PgAdmin does here:

(Query in text form: select pid,query,wait_event,state,* from pg_stat_activity;)

My connections are created this way (here in VBA):

Dim cnDB As New ADODB.Connection
cnDB.Open "PostgreSQLDsnHere"

Where that DSN is created by:

for 32 bit: go here: %WINDIR%\SysWOW64\odbcad32.exe
for 64 bit: go here: %windir%\system32\odbcad32.exe
Go to the System DSN tab and enter the information (Data Source,
Database,Server IP, User Name, Password, Port)

I could not figure out how to add the application_name in this context/connection setup (using a DSN).

I tried adding this in various places in my VBA code, but it didn't work:

cnDB.Properties("ApplicationName") = "testing"

Where do I enter/set the application_name?

I know I can set the setting if I use a standard plain text connection string, but I cannot do that for security reasons. I have to use a DSN.

Non-VBA answers very welcome!


texts: A join that uses ordered phyical records is more efficient than others?

texts:
If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered?
References are welcome.


texts: Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

texts:
I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name.

This is the schema to replicate the issue:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

And you need to add ONLY_FULL_GROUP_BY to your SQL mode:

SET SESSION SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

And this is the query to create the error:

SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > 1 AND t2.c1 > 1;

Error reads:

  Non-grouping field 'c1' is used in HAVING clause

Both t1.c1 and t2.c1 are grouped, so I don't see what is wrong with this query.

Additionally if the query refers to different column names from the same table, the query works.  For example:

SELECT t1.c1, t2.c2
FROM t1, t2
GROUP BY t1.c1, t2.c2
HAVING t1.c1 > 1 AND t2.c2 > 1;

Am I doing something wrong?  I can't find a report of this issue anywhere.  I can replicate the error on MySQL version 5.5.44 on Ubuntu and 5.6.24 on Gentoo.

SQLFiddle for this - http://sqlfiddle.com/#!2/da2704/4

The simple query above is just to demonstrate the issue.  Here is a version that cannot be achieved using WHERE instead of HAVING and suffers the same problem:

... HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2)

UPDATE:

A MySQL Bug has been raised for this - http://bugs.mysql.com/bug.php?id=80455


texts: Rolling week numbers into one row and getting cummulative values

texts:
I have a table of orders with varying dates and have been asked to get the weekly order count for past 8 weeks and cummulative order count overall

I have been using the datepart clause to break my dates in week and the count function to calculate the amount of orders in that week. The code below breaks my dates into week numbers and there is a count for orders but I would like to roll it up if possible so all week 1 orders are on the same line?

Select datepart(wk,orders.date_created), COUNT(*)
from Orders
group by date_created;

I would also like to count the number of orders by week from a certain city.

Select datepart(wk,orders.date_created), COUNT(*)
from Orders
right join City as C
on Orders.city_id = C.city_id
Where city_name = 'Zurich'
group by date_created

This works to a certain extent but not as neat as I would like.  I would also like a cummulative total of orders as the weeks go on, week 1 + week 2, so on and so forth.

Any ideas