texts:
I’m pretty familiar with Relational DBMS in general, but not with Oracle. I have Oracle 18c XE running inside Docker (on MacOS). I connect to it using a third party client (SQLPro Studio on MacOS). Now I want to get started.
If this were MySQL I could issue CREATE DATABASE whatever; USE whatever; and proceed with the tables. The same with Microsoft SQL Server, and something similar with PostgreSQL. I could also create an additional schema to keep better organised.
I gather that Oracle XE only gives you a single database, and I would want to create a suitable schema. At this point I’m at sea. I gather that I would need to create a new user associated with the schema.
I also know that different RDBMS have different ideas about what a database or a schema is, so I’m not sure whether this is the correct definition or solution.
I’m having no success creating either a new user or a new schema in my client.
I have tried:
CREATE USER fred IDENTIFIED BY 'h3art0fg0ld`;

but I get the message:

invalid common user or role name

I also read that I should use something like c##fred, which did work, but (a) I don’t really know what that means and (b) the statement GRANT connect, resource, create sessions TO c##printsdb; gives me the message “missing or invalid privilege”, so I’m no further ahead.
Being in a Docker container, I don’t know whether I have access to SQL*Plus, which some online tutorials presume.
How do I create and use a schema, or whatever is required to contain a collection of tables?


texts:
Rephrased, see original question below.

I have the following tables

Table A (UserId, Age)
Table B (UserId, Age)

For each row in table A I want exactly one row from table B that satisfies the following critera

Do not exist in Table A.
Has the same age as the record in table A.
Has not been assigned to any other row in table A (i.e. the column UserIdFromTableB in the result set should be unique)

Assumptions

There will be enough rows (20x) in table B to find a corresponding match by age.
If this condition fails, NULL should be returned.

Original question

I have a table A (UserId, Age). For each record in table A, I would like to select exactly one records from table B (UserId, Age).

B.UserId must be distinct from A.UserId, but A.Age must be equal to B.Age.

Each record from table B should be joined at most once - each record in table A should be assigned a (random) record from table B, but any given row in table B should only be assigned to a single row in table A (or should be left out of the results altogether).

Is there a set-based solution for this? It will of course be possible to do it in a procedure, but as the number of rows in table A can be quite large.

Edit: Table A and table B are different tables and do not have the same number of rows. If there are multiple rows in B each satisfying the join criteria (Age), a random row should be chosen.


texts: Not able to produce table based on conditional select

texts:
I would like to achieve something that probably ends up in some kind of conditional select (MS SQL) but I do not know how to.

I have tried to simplified my problem to the description below:

I have 3 tables: 1 containing Articles (Art_number, Art_description and Productgroup), the second with Customers (Cust_number and Cust_name) and a third table containing discount information (Cust_number, Productgroup and Discount_percentage)

Table: Articles

001,fork,cutlery
002,knife,cutlery
003,plate,tableware
004,cup,tableware

Table: Customer

1234,smith
5678,jones

Table: Discounts

1234,cutlery,0.9
1234,tableware,0.8
5678,cutlery,0.75
5678,tableware,0.7

My questions is about the relation with a 4th table that contains some (not all!) alternative (customer related) article numbers named Cross_Ref that links the first two tables by containing a article number and a customernumber (Art_number, Alt_Art_Nr and Customer_Nr)

Table: Cross_Ref

002,1002,1234
002,92002,5678
004,1004,1234
004,92004,5678

I would like to produce a table that shows the article numbers in the first column,
Article description in the second column.
The alternative article numbers in the third, column (if available, otherwise empty) and the Customer name in 4th column

It should look like this with the information above:

001,fork,,smith
001,fork,,jones
002,knife,1002,smith
002,knife,92002,jones
003,plate,,smith
003,plate,,jones
004,cup,1004,smith
004,cup,92004,jones

My query currently looks like this:

SELECT Art_number, Art_description, Alt_Art_Nr, Cust_name
FROM Articles,
Customer,
Cross_Ref
WHERE Articles.Art_number = Cross_Ref.Art_number
and Customer.Cust_number = Cross_Ref.Customer_Nr
and Customer.Cust_number = Discounts.Cust_number
and Articles.Productgroup = Discounts.Productgroup

This only shows me those lines that indeed have an alternative article number, but do not show the articles that do not have a reference mentioned in the Cross_Ref table.

In principle it is a combination of 4 tables that are connected fully by 3 tables, but the 4th table only contains a number of the articles (that have a alternative number) and thereby resulting in only those articlenumbers in the query result, while I would like to see all the article numbers and empty (NULL) fields, if there is no result.

Can anybody show me an example how to archive this?


texts: Cant restore database from A to B using any method in SSMS GUI

texts:
I have two databases. One is production (A), the other is development (B).

I use to be able to restore database from a backup.BAK file of A in B by using SSMS > right clicking databases > restore database and using the appropriate settings.

Today I tried the following and in every situation I got the same error "Exclusive access could not be obtained because the database is in use"

Took database B offline (and selected option to drop all connections)
Tried with database B online and choosing option to drop active connections with risk of being left in single user mode (got stuck in single user mode and restore still failed with same error).
Tried with database B online and without choosing option to drop active connections.
Tried with database B online and with option to overwrite the existing database and without and with and without option to close existing connections to destination database.

I'm using SSMS 2017 and just looking for a simple way to restore database B with a copy of A. Anybody know a way to resolve?


texts: Why is `SELECT @@IDENTITY` returning a decimal?

texts:
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.

INSERT INTO Customers (
         Type, Name, Address, ContactName,
         ContactNumber, ContactEmail, Supplier)
VALUES (
         @Type, @Name, @Address, @ContactName,
         @ContactNumber, @ContactEmail, @Supplier)

SELECT @@IDENTITY

The call to connection.Query<int>(sql, ...) is throwing an Invalid Cast Exception. I've debugged it and it's at the point where Dapper calls GetValue on the returned SqlDataReader.

The return type of GetValue is Object, inspecting it in the debugger show's it's a boxed decimal.

If I change the select to SELECT CAST(@@IDENTITY as int), the return of GetValue is a boxed int and the exception isn't thrown.

The Id column is definitely of type int; Why would SELECT @@IDENTITY return a decimal?

Some additional information:

The database is brand new.
The Customers table is the only object I've added to it. There are no other (user) tables, views, triggers or stored procedures in the database.
There are 10 rows in the database, there Id's are 1,2,3,4,5,6,7,8,9,10 (i.e. the column isn't beyond the limits of an int).

My table definition is

CREATE TABLE [dbo].[Customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Address] [nvarchar](1000) NOT NULL,
    [ContactName] [nvarchar](255) NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [ContactEmail] [nvarchar](255) NOT NULL,
    [Supplier] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: Why has my root password changed after I updated MySQL from 5.7 to 8.0?

texts:
I have set my root account passwords like this:
mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('pswd2');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('pswd3');

I had MySQL 5.6.47 Then I updated to 5.7.31 and everything was ok. Then I updated to 8.0.21 and immediately faced with 2 problems:

MySQL wouldn't start but end up with 1053 error. The problem was in missing Visual C++ Redistributable dll file.
In order to login into my root account I have to type the pswd3 password which had been set for the 'root'@'::1' account. pswd1 and pswd2 won't work anymore.

What may be the problem here?


texts: drop postgresql schema public without losing extensions

texts:
As part of my development workflow, I often use
drop schema public cascade;
create schema public;

in order to get a clean state of the test database before re-loading my table and stored procedure definitions.
With the right setup, these statements work without superuser permissions.
My table definitions depend on extensions such as
create extension "pgcrypto";
create extension "uuid-ossp";

and whenever I drop the schema, they are lost...
which wouldn't be a problem, if they didn't require super-user permissions to re-create.
Possible solutions I have considered:
Make my user super-user
alter user myuser with superuser;

Sadly, this is the number-one suggestion when you google for any sort of permission-related issue. I would very much like to avoid super-user permissions, even on my development and testing system.
Manually re-create the extensions every time I drop the schema
To my best knowledge (and experimental results), the problem here is that I have to re-connect to the database as regular and super-user several times...

connect as regular user to run drop schema / create schema
connect as superuser to run create extension
connect as regular user to run the various create table / create function statements

This is a lot of hassle and I'd rather avoid it.
It'd be acceptable to switch once, but twice is definitely too much. When I run create schema as superuser, the regular user cannot create tables anymore.
Revoke permission to drop the extensions
update pg_extension set extowner=0;

... that's what I tried, but it didn't seem to change anything.
?
Is there an other, creative solution for this issue? Maybe

dropping all individual parts of the schema but not the schema itself
granting permissions to re-create extensions to my user
creating the extensions in a different schema, if such a thing is possible?
something else entirely?

My searches there didn't yield anything useful, but that may be due to my limited experience in the topic.


texts:
I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error:

  You can't specify target table 'animal_table' for update in FROM clause

source command (Oracle):

UPDATE animal_table
     SET (animal_info1,
          animal_info2,
          animal_info3) = (SELECT animal_nbr, animal_amount, '00'
                           FROM animal_table t2
                           WHERE t2.category_id = animal_table.category_id
                             AND t2.sumary_id = animal_table.summary_id
                             AND t2.animal_type = 'special'
                          )
  WHERE animal_type = 'cats'
    AND category_id = 'foo';

suggested target command (MySQL):

UPDATE animal_table
SET
    animal_info1 = (SELECT animal_nbr FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special'),
    animal_info2 = (SELECT animal_amount FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special'),
    animal_info3 = (SELECT '00' FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special')
    WHERE animal_type = 'cats'
    AND category_id = 'foo';

can anyone help me out here?


texts: Query to return fields of distinct values per key

texts:
In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list of all possible values of certain fields that are present in the fact table.

Assume that all of the source data is in a single flat Fact table. Imagine something like this:

|Org   |Gender| Age|State|
--------------------------
|     1|     M|   1|   FL|
|     1|     F|   2|   VA|
|     1|     M|   3|   CA|
|     1|     M|   2|   NJ|
|     2|     M|   2|   VA|
|     2|     M|   3|   VA|
|     2|     M|   5|   VA|
(Times 400+ million, but for only about 100 Orgs...)

I would like to create an aggregation table that has the following output structure:

|Org   |Gender| Age|State|
--------------------------
|     1|     M|   1|   FL|
|     1|     F|   2|   VA|
|     1|  NULL|   3|   CA|
|     1|  NULL|NULL|   NJ|
|     2|     M|   2|   VA|
|     2|  NULL|   3| NULL|
|     2|  NULL|   5| NULL|

So that for each Org, the non-NULL DISTINCT list in each column represents the list of distinct values in the Fact table for each column. I can't use a DISTINCT operator because that will look for the list of distinct combinations of fields, not where each field is distinct and the number of rows for each Org is at most the number of distinct values for that Org.

Added comments:
This is for a Data Warehouse, so the source data started off in the transactional relational database that is the companies working database. It's then denormalized into a Fact table and set of Dimension tables that support the possible values of other data. I don't have access to the original relational DB for this project.

Purpose:
But we are now putting an ad-hoc reporting interface on top of the data warehouse and in order to efficiently query the Fact and Dimension tables we have to build some aggregation tables so that the selection fields of the reporting interface don't have to query the hundreds of millions of rows in the fact+dimension tables to find the possible values for each org to present in select dropdowns after the Org is chosen.

I've done a lot of googling and haven't found much, but I don't even know what you would call this kind of structure. "Columnar distinct?"

The database I'm using is SQL Server 2012, if it matters.


texts: Splitting a table based on business logic

texts:
This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach.

Typically, I break database design into two categories, Business Logic and Recorded Data.  Business Logic are those things which bring meaning to the Recorded Data.  Eg, PartSerialNumber has meaning because we have parts with serial numbers.

So I'd create a structure like...

CREATE TABLE PartsTable
{
    PartID INT, -- NOT NULL IDENTITY PK...
    PartSerialNumber VARCHAR(20), -- NOT NULL
    CreateDate DATE, -- NOT NULL, maybe default
    -- other stuff related to the specific part
}

 -- this is a business logic table, the codes have meaning to the business
CREATE TABLE ErrorCodes
{
    ErrorCode VARCHAR(5), -- NOT NULL, PK
    ErrorDescription VARCHAR(MAX),
    -- OTHER STUFF FOR THE ERROR CODES
}

Then we have a logging system to catch errors,

CREATE TABLE PartErrors
{
    PartID INT , -- NOT NULL FK to PartsTable
    ErrorCode VARCHAR(5) -- NOT NULL, SEARCHED ON, FK to Business logic table
    ErrorDate DATE -- NOT NULL, when the error occurred, SEARCHED ON
    -- other search terms
}

Now, there is additional information about each error, but these are not searched on; maybe something like the clock frequency of the part when the error occurred.

So then I'd create another table

CREATE TABLE PartErrorsData
{
    PartID INT, -- not null, fk to PartsErrors
    ErrorCode VARCHAR(5), -- NOT NULL FK TO PARTSERRORS
    ErrorDate DATE, -- not null FK to PartsErrors
    ClockFrequencyHz INT, -- this may be null if the logger could not read the freq
    MachineNumber INT -- may be null if the logger cannot comm with the machine
    -- maybe other stuff, again, nothing that is searched upon, nor in the BL
}

So the question:  Is there any merit in separating these tables or should I have simply put all the columns from the PartErrorsData table into the PartsErrors table?

It seems like we can somewhat accomplish this with an additional index on the fields which are searched.  I'm somewhat torn between using an index on the searching fields and breaking the search fields from the data fields.

It is important to keep in mind that the fields on the search table are business logic fields; they relate to a table containing BL.  The Data table is pretty free form and is simply recording values from feedback files.

I personally like this approach as it clearly defines how the BL relates to the data so that we can join additional tables if necessary.  For instance, if there is another system which has Error related data, we can join it to the PartErrors table without having to worry about the data, just the BL.

EDIT:
Updated the tables so that the compound key is unique.  This was a mistake with the original question.  Of course it would have to have all the data required to correlate to an instance of the error.

Another note in response to the first answer.  We have [at least] 3 tables of different error data.

Some parts may provide clock freq, and machine id.  Other parts may report temperature surrounding the part, voltage coming into the part, etc.

So, using one table would have NULLs in all fields which the part does not report.

Regarding data which has no meaning to business, a clock frequency does not have meaning without the part.  A value recorded by the recording mechanism could have errors.  Maybe we recorded the frequency as 100Hz, but it was supposed to be 100 MHz.  The VALUE 100 does not have meaning until we bring it to light with the BL.  This part has a clock which is measured in MHz.

However, the value 00-139-228-AA has meaning to the business as a part number so that an incorrectly recorded part number &&^%% would be noticed immediately as it does not correlate back to the BL.  The mistaken value is not a catch-able error.


texts: dm_db_index_operational_stats showing more blocking than extended event report

texts:
I’ve implemented the XE to capture blocking on my instance (as per this great article https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comment-2470072. )

I did this because I kept an eye on the blocking states, using this beauty of a query:

select
   db_name(database_id) DB,
   object_name(object_id) Obj,
   row_lock_count + page_lock_count No_Of_Locks,
   row_lock_wait_count + page_lock_wait_count No_Of_Blocks,
   row_lock_wait_in_ms + page_lock_wait_in_ms Block_Wait_Time_in_ms,
   index_id
from
   sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
order by
   Block_Wait_Time_in_ms desc

Occasionally some blocking gets logged to the file, but not as many as I would expect. My expectation being that the number in No_Of_blocks from the query, would correspond with the number of blocks logged to the file as per the XE.

Is there anything the XE might not capture versus the query? Any ideas? Thanks for your feedback!


texts:
Consider this a follow-up to BULK INSERT: Trials and Tribulations

I have the following VIEW defined and would like to insert into the multiple underlying tables when sending a BULK INSERT from a CSV.

CREATE VIEW dbo.StagingViewB as
  SELECT
    Q.[SQSQueue],
    Q.[SQSMessageID],
    D.[ReceiverID],
    Q.[DLOrder],
    D.[DetectionDate],
    D.[DetectionMS],
    D.[TagID],
    M.[TxAmplitude],
    M.[TxOffset],
    M.[TxNBW],
    M.[TxChkSum]
  FROM         [StQueue] as Q
    INNER JOIN [StDetection] as D ON Q.eID=D.eID
    INNER JOIN [StDetMeta] as M ON Q.eID=D.eID
WITH VIEW_METADATA

I want an underlying autoincrement primary key (eID) to tie the 3 underlying tables together.  In other words, this is the StQueue DDL

CREATE TABLE [dbo].[StQueue](
    [eID] [bigint] IDENTITY(1,1) NOT NULL,
    [SQSQueue] [varchar](25) NULL,
    [SQSMessageID] [varchar](50) NULL,
    [DLOrder] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [eID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Q,M,D aliased tables all have eID, but I'm willing to remove the IDENTITY(1,1) part of the definition (on all except one), as I don't think it's wise to trust that three separate tables will remain synchronous with respect to the value of eID for the next insert.

To avoid this issue, should I do a SET @ID = COALESCE(IDENT_CURRENT('StQueue'),0)+1
or

SET @ID = SELECT MAX(eID) FROM StQueue
SET @ID = COALESCE(ID,0)+1

And then do

INSERT INTO StQueue ([SQSQueue], [SQSMessageID],[DLOrder])
  VALUES (SQSQueue, SQSMessageID, DLOrder) From Inserted
INSERT INTO StDetection (eID, ReceiverID, DetectionDate, DetectionMS, TagID)
  VALUES (@ID, ReceiverID, DetectionDate, DetectionMS, TagID) FROM Inserted
INSERT INTO StDetMeta (eID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum])
  VALUES (@ID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum]) FROM Inserted

Microsoft warns:

  Be cautious about using IDENT_CURRENT to predict the next generated
  identity value. The actual generated value may be different from
  IDENT_CURRENT plus IDENT_INCR because of insertions performed by other
  sessions.

I assume this warning applies equally to the MAX-based method. Should I INSERT...WITH TABLOCK on the underlying tables to avoid that issue? Or would the multi-session/thread issue still be a problem. The problem might still exist because the computation for the next ID number happens before attempting to grab a lock, discovering it's already locked, and spinning wheels until the existing lock is released, the value would be stale any time a lock is encountered or a race exists where two threads are trying to grab the attention of the table (establish a lock) at the same time?  Is there a way I can request a lock for all three tables at once? Would this be wise to do before grabbing IDENT_CURRENT('StQueue')?

Will this whole process be slower than BULK INSERTing and then SELECT eID,x,y,z from a single master table into the 3 "child" tables? Would iterating through a CURSOR be worse?


texts:
This is my OS Detail and I want to use latest version of MySQL which version should I prefer?

axt@root:~$ cat /etc/os-release

NAME="Ubuntu"
VERSION="14.04.2 LTS, Trusty Tahr"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 14.04.2 LTS"
VERSION_ID="14.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

axt@root:~$ hostnamectl

Static hostname: axt
Icon name: computer-vm
Chassis: vm
Operating System: Ubuntu 14.04.2 LTS
Kernel: Linux 4.15.13-x86_64-linode106
Architecture: x86_64


texts: how azure sql geo replication works internally?

texts:
My company moved to azure and started to use azure Sql database and geo replication.

For our on-premise servers, we heavily rely on Microsoft transactional replication to move data. Now we setup transactional replication between on-premise server (publisher) and azure sql db. Then we found that the transactional replication itself is constantly consuming 100% DTU on azure(we are using a 125 DTU P1).

We upgraded it to 250 DTU and replication is much happier. However, we are not sure it is a great idea to constantly updating to higher DTU. So we did this:

Created two 125DTU servers.
Setup transactional replication between on-premise server and server A.
Setup geo replication between server A and server B.
Our customers will use server B.

The benefit is that heo replication will not trigger the 100% DTU throttle, and when we raised this to a Microsoft support, he also said it is a good idea. The support person also said that sql geo replication is much more efficient to transactional replication.

However, when we asked him what kind of technology azure geo-replication internally uses and why it is better than old transactional replication, he just gave out some marketing reply:

geo-replication leverages the Always On technology of SQL Server to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation. Impact to your main database is minimal as this process runs with the same context where the automated backups run, for example.

Personally, I didn't really get why geo replication is better than old replication from the reply. Does anyone know some technical detail?


texts: 'Locking' will occur when I create more than 5-6 non-clustered indexes?

texts:
When I create more than 5 to 6 non-clustered indexes on a single table in SQL Server it will cause a locking problem.

Is this right or wrong?  And if your answer is "right" and my  actual requirement is more than 5-6 non-clustered indexes then what shall I do?


texts: SQL Maintenance Plan 'Could not find part of the path' - where is it getting the path from?

texts:
I have a SQL Server 2012 maintenance plan which backs up all of the databases. Today I had an issue with a failed backup which I was able to resolve by viewing the detail in the 'Log File Viewer' through SSMS:

The three 'child' items read as follows:

03/02/2014 05:36:48: Could not find a part of the path 'F:\\sql\\log\\Full Backup All Dbs_Subplan_1_20140203053648.txt'.

03/02/2014 05:36:48: Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.

03/02/2014 04:00:01: Executing the query "BACKUP DATABASE [MainDb..." failed with the following error: "Write on "M:\\sql\\full\\MainDb\\MainDb_backup_2014_02_03_040001_7658846.bak" failed: 112(There is not enough space on the disk.)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have now sorted out the disk space issue and have re-run the full backup successfully, but I remain concerned about the log item above which references F:\sql\log because the machine does not have an F: drive at all, just a C: and an M: drive. It does appear that the 'Could not generate mail report' issue is related but again this error is a bit odd; I am receiving emails when the various jobs complete and I was alerted to the disk space error via an email, so I wonder why an error is logged if the email was sent successfully?

I've dug through the SQL Server, SQL Agent, Database Mail and the maintenance plan configurations but I can find nothing which is set to F:\sql\log and so I have no idea why the maintenance plan is trying to write to that path, or why it is logging an error when email is sent successfully. Can anyone help me to work out how to resolve this?

Thanks for any assistance.


texts:
the galera replication can initiate a node with SST(mysql and mariadb) or mariadbbackup(with MariaDB 10.1 and above) . a SST operation works with rsync and transfer data to new node and then apply redo log . we knew that ! but in this situation donor node going to Donor/Desynced state and cache transactions . after completion of SST operation , this transactions will apply .
so i have a question ,how this cache transfered to new node . because new node after completion of rsync will try to apply redo log in recovery mode and then go up and run .
is gcache.page file has been transferred ? or any other operation is in progress ?


texts:
I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe 20 minutes. It generates about 15GB of data. The largest dumped files are in the 2GB range.

When I load the data into MySQL on another box, a six-core, 8GB machine, it takes forever. Easily 12 clock hours or more.

I'm just running the mysql client to load the file, i.e.

mysql database < footable.sql

directly with the file directly out of mysqldump

mysqldump database foo > footable.sql

Clearly I am doing something wrong. Where do I start so it can finish in a reasonable time?

I'm not using any switches on either the dump or the load.


texts: Is it safe to use innodb_flush_log_at_trx_commit = 2

texts: I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?


texts:
I have a linked server connection for a postgresql into sql server.  I can run this in Sql Server  Select * from OpenQuery(LinkedServer,'Select * from alphadawg') and this returns all results, but the second I try to add in any functions I start getting errors.  For example, if I wanted to TRIM() the trailing spaces off of a field

Select * from OPENQUERY(LinkedServer,
              'Select TRIM(trailing ' ' from ad.userid) As "User ID"
              FROM alphadawg ad')

I get an error of

  Msg 102, Level 15, State 1, Line 5
  Incorrect syntax near ' from ad.userid) As "User ID"

Okay, so maybe the issue is I am trying to execute postgresql in Sql Server, so I altered the syntax to

    Select * from OPENQUERY(LinkedServer,
              'Select RTRIM(ad.userid) As "User ID"
              FROM alphadawg ad')

But that then threw an error of:

  OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "Requested conversion is not supported.".

How can you use the TRIM() function with a linked server connection between postgresql and SQL Server?


texts:
DBMS: SQL Server 2012 Express

I have an SQL Statement which looks something like this:

SELECT

    OverSpeedings = SUM(OverSpeeding),
    OverspeedingPoints = SUM(OverSpeeding) * 5,

FROM
    MyTable

GROUP BY
        COLUMNS_HERE

I was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Or should I use a CTE with one SUM like the following:

SELECT

    OverSpeedings,
    OverspeedingPoints = OverSpeedings * 5,

FROM
    (
    SELECT

        OverSpeedings = SUM(OverSpeeding)

    FROM
        MyTable

    GROUP BY
        COLUMNS_HERE
    ) A


texts: Is this work flow suitable for Amazon RDS?

texts:
I am considering to set up a MySQL database via amazon web services (AWS). Consider the following steps which characterize my workflow:

There is a daily data inflow into several AWS EC2 instances
On each EC2 instance, incoming data is stored in a RData file (tabular format)
RData files from all EC2 instances should be exported as tables to a central database (file size is relatively small, less than 10 MB per file)
Using R/RStudio, data cleaning and data aggregation routines need to be performed on the central database
All steps must be automated via cron jobs

Steps 3 and 4 are my main concern.

Is this a standard work flow which can be integrated easily with Amazon Relational Database Service (RDS)?

Or should I consider a different approach (for example, running the SQL database on a separate EC2)?


texts:
I'm learning SQL Server 2016, I learned how to extract JSON from tables.

Now I need to store JSON in a column. My JSON was:

{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}

I`m using nvarchar as the column type, but when I store it using:

UPDATE test2 SET endereco = '{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}'

When I do a select, it shows like this:

{\"endereço\":\"R. Nome da Rua, 000\", cep:\"00000-000\"}

But I was expecting this:

{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}

What was I supposed to do?

My test table creation script:

CREATE TABLE [dbo].[teste2]
(
  [id] [int] IDENTITY(1,1) NOT NULL,
  [descricao] [varchar](200)  NULL,
  [endereco] [nvarchar](MAX)  NULL
);


texts: Running a linked server query under a domain user

texts:
Is there a way to execute a sql job with a linked server query as a domain user? The linked server authentication is AD so I need to run the query with a set of windows credentials.

Execute As doesn't work for me. No password.

Tried Proxy account -Can't set job to run with it.


texts: Best to isolate the database in multiple instances?

texts:
I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs:

projectname_blog_blogposts

We all know that the table will be very big. Probably hard to maintain, maybe awful situation if the table is corrupt or something like that. I don't know, I don't have any experience to confirm such a statement.

So instead of doing that. Is this approach better:

projectname_blog_1_blogposts
projectname_blog_2_blogposts
projectname_blog_3_blogposts

And then split them up like this:

DATABASE01

projectname_blog_1_blogposts

DATABASE02

projectname_blog_2_blogposts

DATABASE03

projectname_blog_3_blogposts

Sorry if the question is fuzzy. But I do hope you guys understand me.

Where can I read more about this so I can make a good choice?


texts: Using SQL Server's 2016 Temporal Tables to Record Prior History

texts:
I've been reading with interest about SQL Server 2016's Temporal Table capability.

It almost seems like the answer to a problem I have (and I'm sure many have had), except for one thing:

  StartTime and EndTime durations are immutable.

Which is great for transactions going forward...but what if you want to model stuff that changed in the past?

Is it possible to model things like "Organization A was named X between 1970 and 1980, Y between 1980 and 1990, and Z since 1990" using temporal tables? Or are we stuck with traditional methods for this stuff?

It looks to me like temporal tables are only good with changes that happen after the temporal table is created, but I'd like someone to confirm or deny that. (Bonus points if my assumption is correct and you can point me to something that describes the ideal way to implement tables and queries to handle historical queries.)


texts:
I have two major doubts:

Does a DELETE query first identify all rows to be deleted (from the where clause) and then delete them all, or does it delete rows as and when they match the where clause?
What level of lock does a DELETE query take? Is a full table lock? If not then how is the case of insertion/update while the query is running handled?

For instance I run a DELETE where column X has value 0. At the time the DELETE query examined a particular row the column X had value 1, then later (but before the query finished) some other user updated that row's X column to 0. So what happens when the delete finishes? Will that row be present or absent?


texts: How to handle table that has an id which references multiple dimension tables?

texts:
I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-commerce website. Each type of impression: manufacturer, product, evaluation is contained is a single objectid column, which references the corresponding dimension table for either manufacturer, product, or evaluation.

What I am struggling with is:

manufacturer impressions = manufacturer + product + evaluation impressions
product impressions = product + evaluation impressions

I need to be able to query all of the manufacturer impressions, which is a combination of the 3 types of impressions above. If I just query those that match on manufacturer, I will be leaving out the impressions that match on product and/or evaluation. The same thing goes for product impressions.

What is the best way to create a reference table or to be able to query all manufacturer impressions? I need to be able to write a query that combines objectid = manufacturerid + objectid = productid (which has a corresponding manufacturerid) + objectid = evaluationid (which has a corresponding productid and then a corresponding manufacturerid that links to the productid).

Here's a sample query I'm using, but it's not returning any rows when I add in the second join statement:

select * from tracking t
left join products p on t.objectid = p.productid and t.objecttype = 'Products'
left join evaluations e on t."ObjectID" = e."ProductEvaluationID" and t.[ObjectType] = 'Evaluations'
left join manufacturers m on t.objectid = m.manufacturerid and t.objecttype='Manufacturers'

Any advice is greatly appreciated, thanks!


texts: Optimal index wasn't being used until rebuilt

texts:
SELECT COUNT(T1.RECID)
  FROM LEDGERJOURNALTRANS T1
  WHERE PARTITION=5637144576
  AND DATAAREAID='abcd'
  AND JOURNALNUM='abcd-00018'

This query gets run numerous times when users view a certain form, so I created a specific index for it on the three columns in the WHERE clause.  Before I created this index, it was using an "include all" index that the vendor had set up.  The "include all" index, as you might have guessed, included every column, which meant a lot of logical reads.  The new index cut the query time to 1/3rd of what it was and reduced logical reads by 30 times.  I tested without an index hint and it used the new index.

So then today I noticed that these queries were showing up as long-running, and I checked the execution plan and it was back to using the "include all" index.  I ran the query specifying the new index, and it was much faster as expected.  I updated stats on the table and both indexes, but it still used the "include all" index.

Finally, I rebuilt the new index, and now the query is back to using the new index by default.

Does anyone have an explanation of what would have caused this so I can hopefully find an approach to keep it from happening?  The index was not fragmented at all, as no data has been added since it was created.

And yes, I plan to get rid of the "include all" index eventually.

Thanks!


texts:
I want a query which tells me which user granted a role to another user in my Postgres database. Expected output would be as follows:

SELECT  role, grantor, grantee, grant_date FROM information_schema.<table>;

role              grantor              grantee            grant_date
grp_db_admin      Tom Dwan             Eli Elezra         2019-09-09
........
grp_db_viewer     Viktor Blom          Patrick Antonius   2019-08-02

So far I've found the information_schema.role_table_grants and pg_catalog.pg_roles tables, however these don't allow me to see who it was that granted someone a role group.

Any help would be appreciated.


texts: GRANT command not working in MySQL Server

texts:
I'm trying to execute the below command as root user to provide accessiblity from my application.

GRANT ALL ON communitycourts.* TO 'root'@'%' WITH GRANT OPTION;

But it returns 0 rows affected, but when I dont see this in result of  SHOW GRANTS. Pelase help me


texts: Query input must contain at least one table or query error

texts:
I'm working on a java program that connects to a Microsoft Access database and displays the data in a GUI. I'm currently trying to code a button that adds a new row to a table called Grade that holds grades for students that exist in another table called Student for courses that exist in a third table called Course.

The condition for adding a new grade is that the student and course already exist in another table. I thought I had gotten the SQL statement right but when I run it, an error in access appears that says "Query must contain at least one table or query".

Here is my SQL statement:

INSERT INTO Grade (StudentID,CourseID,Semester,Year,Grade)
VALUES((SELECT StudentID FROM Student WHERE StudentID = 5812356),(SELECT CourseID FROM Course WHERE CourseID = 'CS101'), 'Fall', '2016','B');

The StudentID and CourseID specified exist in the Student and Course tables.

I don't understand this error since I have specified which table I'm trying to add to and I have written out a query.

I appreciate any help given.


texts: What is the performance difference between IN (42) and id = 42 in MySQL?

texts:
Are there any performance differences between

SELECT *
FROM table
WHERE id IN (42);

and

SELECT *
FROM table
WHERE id = 42;

?

The question is about just supplying a single value. The natural assumption for me is that the optimizer will treat them as equal and perform the same optimization when fetching the data. But is that correct?


texts: Mongodb Reads & Writes for a specific time period

texts:
Need to know if there is any possible way to find the total no. of reads and writes happening to my mongodb environment.
We are using Mongodb 3.4 version in a sharded environment and need to find the total no. of reads and writes happened for a specific time period(maybe 1 day).
mongostats and mongotop are not useful to get the reads & writes on a daily basis.
I have tried from the mongodb logs and I was able to find the writes(insert + update + delete) but unable to get the no. of reads.
We use Ops Mgr(3.4) but it only gives the average read/write and not the exact count.
So my need is to get the total no of reads and writes happening in Mongodb for a time period(1 hour, 1 day, 1 week)
Any help would be much appreciated.
Thanks
Kenny


texts: Hash Match Spills and Key Lookup causing major performance issue

texts:
I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I would appreciate any help I can receive that would help me improve the performance of this query.

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


texts:
I have an SQL server 2016,

  version:
  (SP2-CU4-OD) (KB4482972) - 13.0.5239.0 (X64)

The server is a Win 2016, with 64 GB RAM.
I have configured max memory for SQL to 54 GB, but it uses less than 1 GB.
Data are been migrated from an older SQL version.

Somebody can help me?

Thanks


texts: MySQL How to copy all data in tables from one huge database to another in the same server

texts:
Consider that I have 2 databases with the exact same structure sitting on the same server one with data and the other is empty. The usual way to copy the data from the first one to the second is to export it on my local machine and then import it on the other one but what if the database size is over the server's limit (and I have no control over that limit), the only two options I know of are:

To export bits and pieces of the first database over a number of times so I'd end up with 10 sql files for example each not exceeding the upload size limit and import those one by one.
To write a query to copy a table from the first database to the second (i.e. INSERT INTO database2.table SELECT * FROM database1.table) Which is a better option if you have small number of tables because you'd have to write the same query for each table.

Is there a way to use the second method where I wouldn't have to write the tables' names? In theory I should be able to get the tables names from the information_schema database and work on those, but I'm not sure where they are stored and how that would work in a query.


texts: What steps can I take to determine if my server is under-provisioned for memory?

texts:
My server is running SQL Server 2016. The environment is fairly high workload with a lot of write transactions and reading of the data throughout the day. I have a hunch the server isn't provisioned with enough memory and would like to dig in and see if that's so. What are the best ways to determine if and how much contention is being generated by the amount of memory available on the server?
I did take a look at the DMV sys.dm_os_wait_stats and when sorted by waiting_tasks_count desc, the top two wait types are "MEMORY_ALLOCATION_EXT" and "RESERVED_MEMORY_ALLOCATION_EXT" by an order of magnitude larger than any of the other wait type task counts. Are there other places I can check as well for memory pressure or contention?
Edit: The total size of all databases on this server is 3 TB, the primary database with most of the transactions is 2 TB, and the total amount of RAM on the server is 32 GB.
Edit 2: Here are the Lazy writes / second perfmon counter results over the course of a day:


texts: Selecting from a table contains a XML column

texts:
I have received an Excel file (verion 2010) and based on the requirement I was asked to save it as an XML file and then import into SQL data ready to be queried in normal SELECT statements.

Below is the list of steps I followed but now that I have a SELECT on the data, no records is returned. I'm not sure whether it's due to the conversion that I did to get an XML file from an Excel file, or there is a problem in my OPENXML query.

To obtain a XML file from the .xlsx file, I opend the file in
EXCEL 2010 and saved it in 'XML spread sheet 2003 (*.XML)'.
I imported the XML file in a table using the code below:

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\DEV\TestXML.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

 1. The XML data has been stored now in the table, so I use the code below to read it in a SELECT statement to access the individual columns:

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 SELECT @XML = XMLData FROM XMLwithOpenXML

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

    SELECT SNo,Salutation,PatientNRIC,FirstName,LastName,Gender,Race
    FROM OPENXML(@hDoc, 'ROOT/SNo/Salutation/PatientNRIC/FirstName/LastName/Gender/Race')
    WITH
    (
        SNo [nvarchar](50) '@SNo',
        Salutation [nvarchar](100) '@Salutation',
        PatientNRIC [nvarchar](255) '@PatientNRIC',
        FirstName [nvarchar](255) '@FirstName',
        LastName [nvarchar](255) '@LastName',
        Gender [nvarchar](255) '@Gender',
        Race [nvarchar](255) '@Race'
    )

Then

EXEC sp_xml_removedocument @hDoc
    GO

However what I get is only the name of columns, the actual data has not been returned:

I am totally new to OPENXML comment and working with XML files, tried reading different posts but still couldn't figure out why no data is shown in my select query. Any help is appreciated.


texts:
A trigger has given me the following:

 ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation

and this is when working on altering schemas/triggers/etc from an import of a client database.

I ran

show errors trigger adminuser.db_error_change_trig

and the results were:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PL/SQL: Statement ignored
2/5      PLS-00201: identifier 'DBMS_ALERT' must be declared

Now, googling, I have found that I need to grant explicit execute privileges to the public user which I did with

grant execute on dms_public to public

However, this has not worked.
How can I determine what is going on, as well as how to fix this.  Thirdly, is dbms_alert schema specific? Meaning, my understanding is the the full name of the object is SYS.dbms_alert, is there another for another schema or is one global one?


texts: About real time statistics oracle 19c only for particular table

texts:
Reg Real-Time statistics which extends online statistics gathering(12c) to also include conventional DML statements.
Real-time statistics helps the optimizer generate more optimal plans.Whereas bulk load operations gather all necessary statistics, real-time statistics augment rather than replace traditional statistics.

Oracle introduced new parameters

_optimizer_gather_stats_on_conventional_dml and _optimizer_use_stats_on_conventional_dml which are true by default and
_optimizer_stats_on_conventional_dml_sample_rate at 100%

How does real time statistics works?

By default the _optimizer_gather_stats_on_conventional_dml is true so its automatically kicks off

When a DML operation is currently modifying a table (conventional), Oracle Database dynamically computes values for the most essential statistics if the above parameter is on.

Consider a example of table that is having lot of inserts and rows are increasing. Real-time statistics keep track of the increasing row count as rows are being inserted. If the optimizer performs a hard parse of a new query, then the optimizer can use the real-time statistics to obtain a more accurate cost estimate.

USER_TAB_COL_STATISTICS and USER_TAB_STATISITICS has columns NOTES tell real time statistics have been used. "STATS_ON_CONVENTIONAL_DML".

Execution Plan shows

|Id| Operation                        | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop|
---------------------------------------------------------------------------------------
| 0| INSERT STATEMENT                 |     |    |     |910 (100)|        |     |     |
| 1|  LOAD TABLE CONVENTIONAL         |SALES|    |     |         |        |     |     |
| 2|   OPTIMIZER STATISTICS GATHERING |     |918K|  25M|910   (2)|00:00:01|     |     |
| 3|    PARTITION RANGE ALL           |     |918K|  25M|910   (2)|00:00:01|   1 |  28 |
| 4|     TABLE ACCESS FULL            |SALES|918K|  25M|910   (2)|00:00:01|   1 |  28 |

Also the explain plan in the query used will tell in note section
    Note

    -----
    - dynamic statistics used: stats for conventional DML

LOAD TABLE CONVENTIONAL and OPTIMIZER STATISTICS GATHERING are the new parameters that have been added to the explain plan for this new feature.

can we apply real-time statistics only for a particular set of table . if yes, how can we achieve them ? , as we know getting real time statistics for any dml operation thats occuring for more that 100k rows is very costly operation

I tried finding out the way, but was not successfull


texts:
I'm using SQL Server in various versions. In case of this I'm using functions which got deprecated.

My question is, how a deprecated function affects the usage of SQL Server?

The functions are still working but could it be, that any settings blocking the execution?

Thanks for your help.


texts:
BACKGROUND I've recently been looking into some fairly high CXPacket wait times recently which has had me using SQL Sentry to monitor processor activity pretty closely.

One thing that I've noticed as a result is that we have massive spikes in context switching. Below is a 5 minute sample but this pattern is very common throughout the day.

As you can see it spikes pretty regularly. Now my understanding of this would lead me to believe that this would be the result of CPU pressure. However during that time barely get over 60%.

After some research this led me to believe that this is happening as a result of hyper threading. I know I'd read earlier some of the dangers of hyper threading. However that was written a looooong time ago.

To make a long story short. Is hyper threading likely to be the culprit for this spikes in context switching? Is it possible that context switching is negatively impacting my parallel queries? Should I disable hyper threading in my environment?

UPDATE although this specific thing is happening in my environment, the question at it's core is more universal. How impactful can high levels of context switching be on parallel queries? Can hyper threading cause this sort of issue?

Ultimately most of what I find on the internet suggests that hyper threading and SQL Server are not good friends, however most it that info is extremely dated.

My System There were a lot of configuration questions so I'll address those here so they can be ruled out. We have the power settings on performance at both the OS an bio level. Our Maxdop is set to 8 and the cost threshold for parallelism is 25. We have 32 logical cores and 16 physical. Also this is for the most part a data warehouse load scenario.


texts: Any solution to fix circular replication fail-over?

texts:
I've developed a point-of-sale system for our corporation, which has 25+ branches across the country. Each branch has its own MySQL POS database on a WAMP server.

I want every event that occurs in any one branch to be reflected in the databases of all the other branches.

Some people advised me with ring topology (circular replication), but there's a big problem with that: when one server goes down, replication will stop.

I want a more reliable solution for replicating my database.


texts: changing the connection string in SQL Server

texts:
we deployed the packages from old server to new server .
how to change the connection string i.e initial catalog or server name parameter from Agent job > configuration  > connection manger .Is there a possibility to do from here ?

I'm receiving the below error if i'm changing the data source and servername


texts:
I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one.
All items are identified by their 7 digit id.
Since I'm guessing that there will be a pretty sparse ranking (perhaps about 1k items per user), I'm not sure how to store these rankings.

As I see it, there are two obvious solutions:

Add one column per user to the items table where I note each rank. --> Problem: If one rank changes, there will have to be a lot of updates.
Store the entire ranking of one user as an ordered list in one textfield. --> Then I can do all the reordering and inserting in my code and could easily query a few top ranked items. But I always have to read a whole bunch of data.

What would be the more efficient way and am I missing another solution.

I am well aware of a few other questions regarding the same general topic but they have a global ranking of the items (also all items actually have a rank which is not the case in my problem) and usually handle 100x more data.


texts:
I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.)

The way our system is set up right now there are two backup systems:

Weekly full backups (.bak) and hourly transaction log (.trn) backups. We keep several sets of these backups and they are regularly shipped offsite.
SQL Server logs (.ldf), with recovery model set to Full. This file is on a separate drive from the main .mdf file, but is otherwise not backed up.

In the event of an emergency restore (or when restoring backups to a dev machine), my procedure is to use the .bak files and then apply the .trn files. We have a script that makes this procedure relatively straightforward.

My questions:

Is it possible to restore the database from the .ldf file? What is it even for?
Is it needlessly redundant to have both of these transaction logs?
Is it important to back up the .ldf file?


texts: How to find which tables were deleted (in Oracle)

texts:
A user dropped number of tables (~1449) on Development server and the user has no record of the tables name that were dropped.

Things we know:
1. Date on which the action was performed.
2. Username of the user who executed the statements.

Is it possible to get the table names (that were dropped)?


texts: How to design database for tree view with an infinite depth

texts:
I am trying to combine C# and SQL2005 for an application which need to have tree view control.

One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That data should be stored into database and at user request i need to draw again tree view whit parent and child nodes as it user was edited and arranged before.

How I should design my table(s) that can easily grow at two axes of tree view.


texts:
I have 3 tables
Vendors
Trucks
Fruits

Truck table has truck # and is foreign Keyed to Vendors with a split uniqueness constraint on both truck and Vendor (different vendors can have the same truck number but no vendor can have 2 trucks the same number)

Fruit tracks shipping history and vendor (has FK to Vendor as well)
I want to add a foreign Key on Fruit to Truck # but I am not sure how to do this


texts: Updating a table with millions of records, its been 4 days

texts:
I am currently updating a table with millions of records, its been 4 days and query is still executing.

I checked the activity monitor its shows that query is running.

In event log there is no errors at all.

Performance wise:

Tempdb in disk A (850 gb free space)
database file in disk B (750 gb free space)
16 GB ram

Please suggest me what should i do?

The query

UPDATE
    dbo.table1
SET
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
    dbo.table2 t2
WHERE
    LEFT(dbo.test1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default


texts: Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

texts:
I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API.

I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via SQL. Example:

cur = self.conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT dbId, downloadPath, fileName, tags FROM {tableName} WHERE dlState=%s".format(tableName=self.tableName), (2, ))
ret = cur.fetchall()
cur.execute("COMMIT;")

Basically, Is the transaction that is started by the cur.execute("BEGIN;") limited to just that cursor, or is it for the whole connection (self.conn.cursor())?

Some of the more complex things I am doing involve multiple separate database operations, that I logically break down into functions. Since this is all in a class which has the connection as a member, it's a lot more convenient to create cursors within each function. However, I'm not sure how creating cursors within a transaction works.

Basically, if transactions are per-connection, I can just create lots of cursors on-the-fly within the transaction. If they're per-cursor, that means I have to pass the cursor around everywhere. Which is it?

The documentation does not touch on this, though the fact that you can call connection.commit() makes me fairly confident the transaction-control is per-connection.


texts: Is there a program I can use to redistribute DB files across multiple volumes efficiently?

texts: I'm working with a VLDB instance with over 60 DBs and close to 200 DB files, allocated over nearly 20 volumes. The instance is a Dev server getting periodic restores from a production instance with a different disk volume configuration. Since the last restore interval new DBs have been introduced and others expanded, requiring me to edit the restore scripts to specify new WITH MOVE paths. Is there a script or software package that can take the list of DB file sizes and volumes and figure out the best arrangement of the files to make the best use of each volume? The closest article pertaining to this is here.


texts: Unable to connect to Listener after rebuild

texts:
I dropped an AG with

DROP AVAILABILITY GROUP [MyDb]

it had a listener.

I left the listener open in the object Explorer of SSMS 17.3.  While the AG and Listener were down. I hit refresh on the listener a couple times in SSMS. (because I have OCD and had to make sure it was gone)

I recreated the AG with the same listener and when I hit refresh it would not connect.  I get the message below.  I tried the following and was not able to connect, receiving the same error.

Disconnect the listener in 17.3 and try to create a fresh connection
Open SSMS 16 (13.0.15900.1) and try and connect
Close and reopen both SSMS sessions.
Try adding MultiSubnetFailover=True to the Additional Connection Parameters tab of the connect popup (this a multi-subnet listener)

I was able to connect using the fully qualified domain name 'MyListener.fully.domain.net', but was still unable to connect with just 'MyListener'

A coworker was able to connect to the listener without issue, using both 'MyListener' and 'MyListener.fully.domain.net'

I rebooted my Windows 10 PC, and after reopening SSMS I was able to connect with just the listener name 'MyListener'.

I upgraded SSMS to 17.4 and the same thing happens.

What is happening here? How can clear the issue without rebooting my PC?

  TITLE: Connect to Server

  Cannot connect to MyListener.

  ADDITIONAL INFORMATION:

  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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

  The network path was not found

  BUTTONS:

  OK

Note in all cases I created the AG and added the listener using a variation of the T-SQL from https://www.brentozar.com/archive/2016/06/sql-server-2016-availability-groups-direct-seeding/  The AG is on an instance running SQL Server 2016 RTM, the database is AdventureWorks2014.


texts: what is difference between oracle instant client and oci?

texts: I've been reading upon the definitions of oracle instant client and oci but being new to oracle database having a bit of difficult time to understand what they are/for. I would appreciate someone can clarify the difference of two. Thank you!


texts: How (not) to manage SSRS report-execution history retention

texts:
SQL Server 2008 R2 Reporting Services keeps a log of who runs what report, stored in the ExecutionLogStorage table of the ReportServer database.  The default retention period for that history data is 60 days.

A few months back, I wanted to increase that retention period on several of my reporting servers, so I searched for how to do it and found a couple of different blog posts which said that you update the ConfigurationInfo table in the ReportServer database.  Specifically, they said you set the Value column to the number of retention days for the row where Name = 'ExecutionLogDaysKept', and that if you set the value to 0, execution history will be kept indefinitely.  So that's what I did, and promptly forgot about it.

Fast-forward to this morning, when I ran a who-launched-what-report-when query against the ExecutionLog view, and promptly got back no rows.  While investigating, I learned about an SSMS GUI with which you can also manage report-execution history retention; the GUI evidently updates the same ConfigurationInfo row, but its labels make it fairly apparent that setting the 'ExecutionLogDaysKept' value to 0 actually translates to, "delete execution-log entries older than 0 days," or basically, "don't keep any history prior to today" -- the diametric opposite of "keep history indefinitely."

This is borne out in an examination of the stored procedure ExpireExecutionLogEntries, which references the 'ExecutionLogDaysKept' value, and in which the comments and the code itself clearly show that the correct value to indicate indefinite history retention is -1, not 0.

The authors of those blogs shall remain nameless, since they were SQL folks with whom I was not especially familiar and whose advice I therefore shouldn't have followed so blithely. I suppose this post is mostly a caveat not to make the same mistake I did; however, I do have a couple of questions:

Has anyone else experienced the same thing?
Does report-execution history retention management work the same way in
subsequent versions of SSRS?

Edit: Apologies to those unnamed blog authors, as it appears that the erroneous retention-setting info actually originates in at least one location in Books Online for 2008R2. It's still present in BOL for 2016 -- not sure whether there's still a disconnect with the underlying procedure code. Submitted a Microsoft Connect entry requesting that the documentation be corrected.


texts:
[I wasn't sure if this is the proper place to post SQL questions.] I have a public dataset of pharmaceutical prices.  Any given drug gets a new price on some unpredictable day, and then the price remains that price until the next price change.
E.g.
drug            date          new_price
acetaminophen   2020-01-09    0.25
oxycontin       2020-01-10    1.40
valaxirin       2020-02-10    2.34
oranicin        2020-02-11    1.54
acetaminophen   2020-02-12    1.47

I have to do a variety of analytics e.g. "what was the price of acetaminophen on 2020-02-01?"  Well that would of course be 0.25, but I need a way to figure that out in SQL.  I have a variety of more complex queries, e.g. "list the ten cheapest drugs on a given date".  So a solution I think needs to be generalized.
I realize that one possible solution would be to run a job that populates the database with prices for every day of the year, but I prefer not to solve the problem that way.


texts: How to find the most common strings between rows

texts:
In SQL 2017, I have a 'food' table as follows:
ID    item    Ingredients
----  -----   -----------
1     item1   flour,sugar,cocoa,butter
2     item2   flour,sugar,butter,water
3     item3   sugar,cocoa,water
4     item4   sugar,cocoa,butter
5     item5   flour,water

Although the "Ingredients" are a CSV in a single field, I have no problem moving them to a related table:
ID    Ingredient
---   ----------
 1    flour
 1    sugar
 1    cocoa
 1    butter
 2    flour

etc
or just select them with a CROSS APPLY string_split
select ID, item, ingredient
from food
CROSS APPLY string_split(food.ingredients,',') as ing

Now here is what I am trying to attempt:
For any given row ex:(where id = 1) I want to return the top n rows (other than id = 1) that have the most common ingredients with id = 1.
So the top 2 with the most common ingredients with id = 1 should be:
ID    item    Ingredients
----  -----   -----------
2     item2   flour,sugar,butter,water    ( 3 in common)
3     item3   sugar,cocoa,water           ( 2 in common)
4     item4   sugar,cocoa,butter          ( 3 in common)
5     item5   flour,water                 ( 1 in common)

The result should return rows with id = 2 and id = 4 as both have 3 ingredients in common with row id = 1
This is my first question on StackExchange, and I hope it is clear.
I can write this as a C# or Python batch program to create a table of 'closest ingredients', but I'm hoping I can do this in SQL.
Thanks !


texts:
My question is more or less theoretical - what is the best approch to get query data by intervals.

The situation

I have a system that manage counters (thousands of counters - counts consumption of electricity/water etc...), every 15 minutes the counter is sampled and stored in the DB. there is one table that stores all counters data [id, counterID, Timestamp, CounterValue].

Now, I want to query the data about the Interval (hourly/daily/weekly/monthley) consumption.

The intervaled consumption defined as the first measurment (counting) in the start of the next interval minus the first measurment in the start of the current interval. since all measurments are agregated (counters) the delta between two counters represents the consumption in the interval.

Since there is ALOT of data and I want the queries to be fast I need a better way then just quering the whole table... (I think...)
quering the whole data (even with small amount of data - this query is slow...):

SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
        FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;

I thought about some ways doing this:

Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
Add tables for every interval with columns [id, date, counterId, counterDataRecord] so there will be tblDailyCounter, tblWeeklyCounter etc...

is there any other idea that Im missing?

what is the pros&cons of those aproches?

what is the most recomanded / best practice?


texts: Show Primary and Secondary Accounts in a table

texts:
I have a table that provides the relationships between Primary and Secondary accounts which looks like this:

CREATE TABLE testRelate
(
  AccountRef nvarchar(20) PRIMARY KEY,
  RelationshipType nvarchar(20),
  RelationshipRef nvarchar(10),
  PrimaryRelationshipRef nvarchar(10)
);

I am trying (with no success) to find a way to show each account and its related accounts in a single row. Something like this:

The format is not terribly important (All the Primary/Secondary Account numbers could be concatenated into one field if it makes it easier, though if the first could be Primary that would be preferable). As long as each account shows all its related accounts in one row I would be more than happy! In the data the maximum number of secondary accounts so far is 4, but that is not fixed.

The best I have managed is

SELECT
    testRelate.AccountRef AS ACref,
    testRelate_2.AccountRef
FROM
    testRelate AS testRelate_2
INNER JOIN
    testRelate AS testRelate_1
    ON testRelate_2.[RelationshipRef] = testRelate_1.[RelationshipRef]
RIGHT OUTER JOIN
    testRelate
    ON testRelate_1.[PrimaryRelationshipRef] = testRelate.[PrimaryRelationshipRef];

Which produces the desired matches but not in one line.


texts: Datetime fields return as integers in view

texts:
We ran into a curious case today where a view that's doing "select * from table" had a couple of datetime fields come back as integers.  This was working previously up until very recently.  These is nothing in the view itself that is forcing these columns to return as integers.

Another curious thing is that rewriting the view to explicitly select all of the columns makes the data come back as datetimes.  I'm thinking maybe it's some sort of bad cache plan, but I'm unsure.

What could the culprit be of the data pulling back incorrectly?


texts: MongoDB: db.collection.watch on sharded clusters

texts:
I'm interested in db.collection.watch feature on MongoDB sharded clusters.

Did you have any problems with it in the production environment?

To guarantee total ordering of changes, for each change notification the mongos checks with each shard to see if the shard has seen more recent changes.

Is it possible to reduce this checks? I don't want to see changes from all shards. Maybe $match filter could help me

Thank you!


texts: Making a trigger to update FK based on columns in another table and an external table

texts:
I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK.
Here's my plan:

Create an external table of a CSV file that has the information Oracle needs to know which PKs to reference when inserting information into the FKs that reference those PKs.

Create a trigger that will update the values of the FK (based on some joins between tables in the database and the external table).

My questions are:

What's wrong with my trigger statement?
Is this plan a good idea? Is my plan of having to reference an external table to create a FK really dumb / not something any dba would ever do? (this is my first project, so it's completely plausible that I'm not seeing some huge flaw with this).
I also obviously have a statement to insert from an external table into purified_enz. But the insert and creation are all one statement. If i'm going to also reference this table for the trigger, is it better practice to just reference the same external table for the insert?

Here's the code I have:

Create an external table that will be used by the trigger.

CREATE TABLE pur_connector
    (
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      emptycol VARCHAR2(1),
      purified_enz_id VARCHAR2 (50),
      g_id VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      final_buffer_system VARCHAR2 (50),
      buffer_mol NUMBER (6, 2),
      final_nacl NUMBER (4),
      final_ph NUMBER (4, 2),
      final_add VARCHAR2 (50),
      yield NUMBER (6, 2),
      concentration NUMBER (6, 2))
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_pur_connector_from_purified_enz.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('PURIFIED_ENZ.CSV')
    ) REJECT LIMIT UNLIMITED;

And
2) The trigger.
CREATE OR REPLACE TRIGGER connect_purified_enz_to_produced
    AFTER INSERT ON purified_enz
    FOR EACH ROW

BEGIN
    UPDATE purified_enz SET g_id = :NEW.produced.produced_id WHERE (
             ( SELECT ext.g_id, produced.g_batch as "produced g batch", ext.g_batch as "ext g batch",
             produced.produced_id, produced.construct_id FROM
                produced INNER JOIN pur_connector ext
                ON produced.construct_id = ext.construct_id
                INNER JOIN pur_connector ext2
                ON produced.g_batch = ext2.g_batch );

END;
/

Error:
2/5       PL/SQL: SQL Statement ignored
2/36      PLS-00049: bad bind variable 'NEW.PRODUCED'
8/53      PL/SQL: ORA-00936: missing expression

Except my :NEW variable is wrong. I'm confused about how I should tell Oracle that the new variable is the produced.produced_id that results from the join that follows. Can someone help please?
End result of what I want is to take the values is red and copy them into this null column:


texts: Move DTC from one node to another in Windows 2003 R2 Clustering

texts:
I have a Windows 2003 R2 two node cluster that is running MSDTC as part of the Cluster Group.  Is it possible for me to change the current MSDTC owner without moving the entire Cluster Group to the new owner?

It is my understanding if I run the following command I will move the entire Cluster Group to Node to and not only the MSDTC.

cluster group "Cluster Group" /move:Node2

I don't see anything under the cluster resource command here that allows me to change the resource owner.


texts: Why would a file generated by mysqldump be substantially larger than the data directory

texts:
I have a MariaDB server - 5.5.60
I have a database xyz that has both MyISAM and InnoDB tables
The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB tables.

If I run du -sh /var/lib/mysql/xyz I get 11M

If I run mysqldump -u root -pXXX "xyz" > xyz.sql the resulting file is 371M

I can use the xyz.sql file to restore the database just fine.  With other databases I have had all MyISAM tables and never noticed much of a difference in size.

Why is the .sql file so much larger than the actual database directory?  Is there something I should do differently when using mysqldump with a database with InnoDB tables?


texts: Updating/Inserting 2000 entries takes over 12 hours to complete

texts:
I have three tables of around 10 million rows each in MySQL on Amazon RDS and I am finding the insert/update performance to be VERY slow. Each table contains unrelated data.

Each table is regularly updated with about 2000 rows each time. The rows either update existing values or insert new ones. I am finding that updating or inserting 2000 rows takes over 12 hours to perform.

The database has 100GB of General SSD storage which means that it can sustain 300 IOPS. CPU utilisation is below 20% and RAM is 50% free.

I am using an index and a primary key which was constructed from two columns within the table using: CONSTRAINT PK_1 PRIMARY KEY (DATE, NAME)

I am using the following query:

    INSERT INTO Table (DATE, NAME, COLUMN_1, ..., COLUMN_10)
    VALUES
    ('2015-05-26','David', VALUE_1, ... , VALUE_10),
    ...
    ('2015-05-26','Tom', VALUE_1, ... , VALUE_10)
    ON DUPLICATE KEY UPDATE COLUMN_1=VALUES(COLUMN_1), ... , COLUMN_10=VALUES(COLUMN_10);

The above query inserts/updates 7 rows and 10 columns of data. This happens for all 2000 entries(users).

The reason why I have chosen to use the above statement instead of a REPLACE INTO statement is because whilst the table is 40 columns wide, I only receive 10 of those columns at once. The values of the rows can also change for up to a week.

By default, the data was being inserted into the three tables sequentially. I have tried inserting the data in parallel. However, there was no significant  improvement in the performance.

I don't understand why it takes over 12 hours to insert 2000 rows. What are my options to improving performance?


texts: How to perform query operation on another query result

texts:
How to get approved , unapproved , deleted count from the below result using sql query.

SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher`
    WHERE company_id = 1 GROUP BY approved,deleted_at;

I'm attaching the above-queried result as an image.

  Desired output: Approved:32 not_approved:4 deleted:7


texts: Design star schema for many-many relationship

texts:
What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships.

I understand how to take basic data including a many-to-many relationship, and get to a normalized production database:

For example:

If I want to handle sales transactions, given tables Product Entity, Promotion, and Employee, the initial step is to construct a table, SaleTransaction:

SaleTransaction
- TransactionID
- ProductID
- EmployeeID
- SellingDateID
- Quantity
- SaleAmount
- PromotionID

The Promotion entity will be:

Promotion
- PromotionID
- ProductID
- DiscountAmount

However, that would only allow 1 product and 1 promotion per sale transaction. Since we want to allow one or more products, and zero or more promotions:

I remove PromotionID, ProductID, Quantity, and (unless required by some business rule) SaleAmount from SaleTransaction
I create SaleTransactionDetail:

- DetailID
- TransactionID
- ProductID
- Quantity
- SaleAmount

I will create SaleTransactionPromotion:

- TransPromoID
- PromotionID

What would be the equivalent steps to take a production DB design to a DW star schema design?


texts: Eliminate database fragmentation (export, drop and re-import tables)

texts:
I have one datafile that is highly fragmented. Its size is 10GB now and real size that is used is 2.5GB,so 7.5 is free.

I decided to export data, drop that datafile, recreate it and import data.

What should I mention? Is there any chance to loose some objects?? After dropping datafile?

I have one tablespace, with one datafile. Please provide me with any suggestions about exporting scripts.

There is an option:

TABLESPACES          list of tablespaces to export

if I write script like that

exp username/aaa file=a.dmp tablespaces=my_tb_name log=a.log grants=y indexes=y constraints=y triggers=y

is it enough to import everything from that tablespace and anything that is related to the containing objects(I mean grants, constraints, triggers...)

And is it necessary to be on a database server not on a client to run the export ?

Thank you a lot, waiting you clever suggestions


texts: Deadlock when dropping multiple constraints on multiple tables at the same time

texts:
Background

We have a data warehouse process that calls the following code:

exec sp_executesql N'EXEC maintenance.sp_fk_statements @P1,@P2,@P3',
    N'@P1 varchar(3),
    @P2 varchar(9),
    @P3 varchar(4)',
    'dbo',
    'table_name',
    'DROP'

This sp pulls a list of drop constraint statements from another table, loads it into a cursor, and then iterates through the cursor calling exec(@sql) on the drop statement.

An example of what one of the drop statements looks like:

ALTER TABLE dbo.table_name DROP CONSTRAINT [FK_constraint_name];

The Problem

The stored procedure above is called on several tables at once.  This didn't cause issues before, but starting last night has started causing deadlocks.

Specifically, the deadlock is occuring on the object mssqlsystemresource.sys.fn_MSconstraint_columns, with this query (captured by SentryOne):

insert into @columns
--
-- PK,UQ are key constraints
-- (excludes implicity added "uniqifier" columns)
select  schema_name(sys.key_constraints.schema_id) as schema_name,
        sys.key_constraints.name   as constraint_name,
        sys.key_constraints.type,
        sys.columns.name as constraint_column_name,
        sys.columns.column_id as constraint_column_id
from    sys.key_constraints WITH ( READPAST )
join    sys.indexes
on      sys.key_constraints.parent_object_id = sys.indexes.object_id
    and sys.key_constraints.unique_index_id = sys.indexes.index_id
join    sys.index_columns
on      sys.indexes.index_id = sys.index_columns.index_id
    and sys.indexes.object_id = sys.index_columns.object_id
join    sys.columns
on      sys.index_columns.object_id = sys.columns.object_id
    and sys.index_columns.column_id = sys.columns.column_id
where   sys.key_constraints.type in ('PK','UQ')
    and sys.key_constraints.parent_object_id = @objid

union

--fake PK rows for indexed view
select

I am unsure as to why this would be causing deadlocks on this particular resource all of a sudden, when this process has been running fine for a while.  Is there anything I can do to fix this other than tell them to drop the table constraints in series rather than parallel?

Note: Yes, that SQL query is incomplete, that's how it's shown in SentryOne.


texts:
What is the correct way to give someone permission to create extended event sessions in Azure SQL Server?

I've been bouncing queries to my manager non-stop today, because when he tried to grant me permission to do it myself using the following statement:

GRANT ALTER ANY EVENT SESSION TO myUserName

He got the following errors:
Securable class 'server' not supported in this version of SQL Server. and
This property is not supported: ApplicationName.

Has anyone else ever needed to grant this permission before on an azure sql database?


texts: MariaDB: calculate the reads/writes ratio in percent

texts:
I found a blog post howto calculate the reads/writes ratio in percent.

I receive NULL values in our env.

> SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) AS `Total`,
    ->  @total_reads := SUM(IF(variable_name = 'Com_select', variable_value, 0)) AS `Total reads`,
    ->  @total_writes := SUM(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes`,
    ->  ROUND((@total_reads / @total_com * 100),2) as `Reads %`,
    ->  ROUND((@total_writes / @total_com * 100),2) as `Writes %`
    -> FROM information_schema.GLOBAL_STATUS;
+-----------+-------------+--------------+---------+----------+
| Total     | Total reads | Total writes | Reads % | Writes % |
+-----------+-------------+--------------+---------+----------+
| 109286055 |    85029655 |     24256400 |    NULL |     NULL |
+-----------+-------------+--------------+---------+----------+
1 row in set (0.03 sec)

MariaDB [(none)]> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.01 sec)

any idea?


texts:
How do i find out the most active database?

I mean to say I have 10 DBs and some transactions keep on happening on these DBs.

I want to know which is less used or which DB has less no of transactions or less active through out the day


texts:
I'm looking for an additional bit of help with regard to some problems I'm looking at for one of my company's clients. Basically I have two related problems with regard to a 2-node active/active cluster hosting an instance of 2008 R2 on each instance.

One of the instances, normally on node#2, is at RTM. And when failing over to node #1 there is a time-out bringing the SQL Server service on-line, however once the time-out has, er, 'timed out', the service can actually be brought on-line OK manually. And looking at the Log there are errors of the form

  ‘Login failed ..... Reason: Server is in script upgrade mode…..’.

At first I thought this was the result of a failed attempt at installing SP1 however I'm now not so sure. SP1 has definitely been installed on both nodes and the other instance, normally on node #1, is at SP1. I assume this was following the recommendation to install the service pack on the 'inactive' node, failing over and repeating the process. However I'm having difficulty interpreting the installation logs to see if just one instance has been updated or both, with one failing. So I was hoping someone could help me with that in terms of what log file I should be looking at.

Also, what might be the meaning of the 'script upgrade mode' errors? Does this indeed sound like a failed SP1 upgrade or is something else at play here? Curiously the problem only occurs in one direction - from node #2 to node #1. When failing back to node #2 the SQL Server service comes back on-line without the need for any manual intervention.


texts: "Unrelated" INSERT and UPDATE blocking each other

texts:
Repro scenario:

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

I start transaction 1:

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

and then execute transaction 2 in parallel:

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

As it turns out, transaction 1's uncommitted INSERT blocks transaction 2's UPDATE, even though they affect disjoint sets of rows (mykey = 'B' vs. mykey = 'A').

Observations:

The blocking also occurs on the lowest transaction isolation level READ UNCOMMITTED.
The blocking goes away if I put a unique index on mykey. Unfortunately, I cannot do that, since key names can be reused once a key has expired.

My questions:

(Out of curiosity:) Why do these statements block each other even on the READ UNCOMMITTED level?
Is there an easy and reliable way to make them not block each other?


texts:
For starters: Our company is based in Germany and thus is based on German law.

Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored.

Now we're forced to provide our customers (real estate agencies) with the possibility to store passport information alongside a customers record.

I do not want to participate in any security-discussions, since this whole thing is going to be heavily encrypted anyway, so security should be out-of-scope.

I'm more wondering on how to design such an SQL Server table to be ready for international passports and "identity documents". Are there information that ANY country specifies in their passports, some international standards or such?

Which columns would be needed to conform to other countries' anti money-laundering laws?


texts: SQL Server Timeout: Drop and Recreate Index Solves the Issue

texts:
After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having.  The strange thing is after about a month of use, the query starts to timeout again.  If we drop the same set of indices and recreate them, the query starts running very quickly again.

The indices are nothing fancy:

CREATE NONCLUSTERED INDEX [IX_Transaction_BillingAccountId] ON [dbo].[Transaction]
(
     [BillingAccountId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Any ideas as to why this would happen? We are using SQL Server 2008 Express.


texts: How to catch and handle only specific Oracle exceptions?

texts:
From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.

How can I rewrite the following to catch only the error ORA-00955?

begin
      EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
exception when OTHERS then
    Null;
end;

BTW Is there any syntax to catch errors by just providing the error-codes?


texts:
Version 678 from github is displaying results in SSMS query window as expected but not creating a table, using the following commands:

USE DBAResources;
GO
EXEC dba.sp_BlitzCache @Top=10, @OutputDatabaseName = 'DBAResources', @OutputTableName = 'dba.BlitzCacheResults';

BlitzCache is compiled as stored procedure dba.sp_BlitzCache in database DBAResources. Note the use of the non-dbo schema for the stored procedure and the table.

Running on SQL Server 2016 SP 1 Standard.


texts:
Quote from docs:

  Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT.

So does PostgreSQL see changes committed by another transactions or not?


texts: Change Tracking generating enormous transaction logs

texts:
I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min.

Using the following command I'm tried to examine the contents of the log:

SELECT Operation, Context, AllocUnitName, [Lock Information], Description
FROM sys.fn_dblog (NULL, NULL)

Most of the rows appear like this:

I used this query to look at the contents on the syscommittab table which appears to be an index table for Change Tracking (enabled in this DB)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT TOP 1000 *
FROM sys.dm_tran_commit_table
ORDER BY commit_time DESC

This is where I get lost, I assume that based on the time stamps there are approx 3-4 changes per sec being made in Change Tracking which I would expect as its tracking every table in the DB. The operation is a DELETE which I assume is a cleanup task running.

Could there be another reason?

Is there any other method to determine why the log is so busy?


texts: How can i merge 3 sql select queries in single query?

texts:
I am using this query

SELECT
  (SELECT tr.object_id, p.post_title, p.comment_count
   FROM wpug_term_relationships AS tr
   LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
   WHERE tr.term_taxonomy_id =122
   ORDER BY tr.object_id DESC
  ) AS Column1,

  (SELECT tr.object_id, p.guid, p.post_type
  FROM wpug_term_relationships AS tr
  LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
  WHERE tr.term_taxonomy_id =122 AND p.post_type='attachment'
  ORDER BY tr.object_id DESC
  ) AS Column2,

  (SELECT tr.object_id, p.meta_value, p.meta_key
   FROM wpug_term_relationships AS tr
   LEFT JOIN wpug_postmeta AS p ON p.post_id = tr.object_id
   WHERE tr.term_taxonomy_id =122 AND p.meta_key IN ('rehub_views', 'rehub_offer_product_price')
   ORDER BY tr.object_id DESC
  ) AS Column3


texts: Indices for a join query with datetime range

texts:
Given the following query:

SELECT DISTINCT COUNT(DISTINCT "patients"."id")
FROM "patients"
INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id"
WHERE "patients"."practice_id" = 6
AND "patients"."species" = 'canine'
AND "transactions"."practice_id" = 6
AND (("transactions"."transaction_date" BETWEEN '2015-01-01' AND '2015-12-31' OR 1=0));

This query is generated by Rails, and that's where the OR 1=0 shorcut comes from.

What indices would you make sure were in place to ensure that this query runs as quickly as possible?

I'm working with a large database, where the transactions table is around 200 million records. The patients table is much smaller.

Execution time with just one query running is around 2.3 seconds, but we need to be able to run about 50 of these queries simultaneously. When that happens, they bog down incredibly, to about one query/minute.

How would you make this run faster?


texts: Adding ReadOnly Domain-less replica to existing AG with two replicas on two nodes WSFC

texts:
I have AG setup as follows:

Multi sub-net WSFC (Windows Server 2016) - All nodes part of same
domain
AG with two replicas (SQL 2017) - Each node (mentioned above) contains one standalone instance.

All working good. Now, is it possible to add another AG replica with separate SQL instance that is not on the same domain and not clustered. the requirement here is to have third replica for read-only workload of an external application and that should not be part of same production domain.


texts:
I have a file system for my archives which gets full in a short span of time. I want RMAN to backup all the archives automatically and delete the archives after backing it up when the archives mount-point gets full.

Is that possible?


texts: Missing 'Microsoft Office 12.0 Access Database Engine' in SSMS

texts:
I've got several users that need to import / export ACCDB Access files into out of SQL Server.

Unfortunately, I have been unable to get 'Microsoft Office 12.0 Access Database Engine' to show up as a data source despite installing / uninstalling seemingly every different bit version of the driver:

We are running SQL Server 64 bit Enterprise 2012 on Server 2008 R2 64 bit. No Office is installed on the server itself.

I'm curious to see what is the proper configuration, because nothing is working for me! Wondering if I'm missing another component.

Used the following drivers to no avail:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&WT.mc_id=soc-n-%5BTR%5D-loc-%5BServices%5D-%5Bfarukc%5D&id=13255

Also have tried this:

http://www.microsoft.com/en-us/download/details.aspx?id=23734


texts:
SQL Server 2008 web edition x64
I'm opening: (connection)/Security/Logins/John.
Select "User Mapping'. On right pane is marked checkbox at DB1 line.
I turn on checkbox at DB2 line, correct username and schema appear.
Press 'ok'.
Opening (connection)/Security/Logins/John once more and there is mo mark at DB2 checkbox, it's clear. No username/schema at that line, of course. Mark checkbox again, and so on until become mad.

No error messages, no errors in log.

I can't turn off DB1 checkbox because of 'Canno drop the user dbo'

DB1 really doesen't importanf for me, but I need DB2 mapping. How can I fix this?


texts: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year

texts:
I have a a business requirement that each record in the Invoice table has an id which looks like YYYYNNNNNN.

The NNNNNN part needs to restart at the beginning of each year. So the first row entered in 2016 would look like 2016000001 and the second like 2016000002 etc.
Lets say the last record for 2016 was 2016123456, The next row (of 2017) should look like 2017000001

I don't need this id to be the primary key and I store the creation date as well. The idea is that this 'display id' is unique (so I can query by it) and human group-able, by year.

It is unlikely that any records would be deleted; however, I would be inclined to code defensively against something like that.

Is there any way I could create this id without having to query for the max id this year every time a insert a new row?

Ideas:

A CreateNewInvoiceSP, which gets the MAX value for that year (yucky)
Some magical built in feature for doing exactly this (I can dream right)
Being able to specify some UDF or something in the IDENTITY or DEFAULT declaration (??)
A view which uses PARTITION OVER + ROW() (deleted would be problematic)
A trigger on INSERT (would still need to run some MAX query :( )
An annual background job, updated a table with the MAX for each year inserted which I then... Something?!

All of which are a bit non ideal. Any ideas or variations welcome though!


texts: How find all the data loaded on previous day?

texts:
Issue: Data Loading into development environment from production database

My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is

How can I get all the data loaded into Oracle database yesterday?
Is there a way in Oracle to get all data loaded into db on previous day, manage all dependencies and generate another dev snapshot with one day minimal data?


texts: Is there a oracle SQL query show me how many time I had Downtime in my Database through a year?

texts: I want to see log for how often my oracle Database goes down ,so is there a SQL query can give me this info..