texts: SQL Server Agent Alerts and Severity Errors

texts:
I've setup alerts in SQL Agent for errors of severity level 15-16 for testing, and found out that these are not always triggered
To be more exact, these seem to trigger only when given error number has is_event_logged = 1 in sys.messages
This means when below commands are run:
create table  -- Msg 102,  Level 15, State 1  -- Incorrect syntax near 'table'
select 1/0    -- Msg 8134, Level 16, State 1  -- Divide by zero error encountered

Those errors (102, 8134) have is_event_logged = 0 and are not written to application log -> alerts for severity errors level 15-16 do not fire
When I try generate error severity 15 or 16, that has is_event_logged = 1, alerts fire just fine
raiserror(18054,16,1)
raiserror(15716,15,1)

Same with severity 17-25 errors, only ones that have is_event_logged = 1, can fire an alert
Questions:

It seems not severity level of error, but actually is_event_logged column controls whether or not error can trigger an alert that looks for severity level errors ?
Is this intended behavior in SQL Server ?

If I need to create custom error message as an alert for an application, using sp_addmessage procedure, and later setup an Alert for this error message,
@severity parameter does not really matter, I need to use @with_log parameter ?

Is there any way to make ALL errors of given severity levels (15,16,17-25) to fire an alert, regardless of is_event_logged equals to 1 or 0 ?
I am afraid to miss something important, because there are a lot of 17-25 severity errors that are not logged to app.log -> don't fire an alert email ?


texts: adding another table to existing MySQL replication scenario

texts:
I'm running a simple MySQL replication scenario MASTER  ➙  BLACKHOLE  ➙  SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE.
Now I would like to add several more tables (that already exist and filled with data on MASTER) to existing replication, with minimum downtime on MASTER and SLAVE.
I have general understanding of steps to undertake in order to have a consistent snapshot (lock master tables, stop replication, dump&source etc), but this process includes 10+ steps and prone to human mistakes, especially if we set up more slaves in future.

Question: is there an easier option to add new table(s) to replication? Like using

mysqldump --master-data ...

and then somehow start replication of a particular table from a certain binlog position?

Thank you.


texts: What options are there for rebuilds of indexes on large tables for a data warehouse?

texts:
I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the data is used to speed up performance.

I typically drop some of the non-clustered indexes to both speed up inserts and to reduce index fragmentation after large data loads. However, this process of dropping and rebuilding is causing a huge amount of time as the data grows.

Example: One table took 2 hours to apply a new non-clustered index on 100m+ rows.

Likewise, if I leave the non-clustered indexes in place, they will increase the inserts 3x to 10x in some cases that force your hand to drop and rebuild.

While it's awesome to drop and rebuild indexes, they don't really pan out that well as data grows in those tables. What options are available to me? Should I bulk up the server with more memory (currently 32GB) and cpu (4 vCPU)? Should I rethink my indexes? Should I find a balance of keeping some indexes on for reorganization versus dropping and rebuilding?

(Note: I don't have enterprise edition.)

I'm thinking my only option is enterprise edition with table partitioning where I can rebuild indexes per partition as opposed to the whole table.


texts: Migrate existing data when altering the column type from string to json

texts:
I have a table with an existing column type varchar. I need to convert this column type to new type jsonb. The problem is if the column has already data 'str1:str2' then after converting the type to jsonb column has invalid JSON string 'str1:str2'.
I want to migrate the existing string 'str1:str2' into the proper JSON string with keys as '{"key1":"str1","key2":"str2"}' before/while converting the column type.
Is it possible to do it using ALTER TABLE ALTER COLUMN....Using function() clause or in some other way.


texts: Query to calculate start and end dates from a list of only start dates

texts:
I have data in the following form that needs to be combined such that I create a single row from two rows of the input data where the second column is the value of the next row less one day.

I've struggled trying to come up with a way to do this, but not gotten very far. I hope this illustration explains the problem:

Table structure

CREATE TABLE Process(
    ProcessOrder int NOT NULL IDENTITY (1, 1),
    ProcessDate date NOT NULL
);

Query

SELECT ProcessOrder, ProcessDate From Process ORDER BY ProcessOrder;

Data

ProcessOrder  ProcessDate
------------  -----------
208           2016-01-04
209           2016-01-11
210           2016-01-18
211           2016-01-25
212           2016-02-01
213           2016-02-08

I need to get the above data into the following shape (note that ProcessEnd is one day less than ProcessBegin from the subsequent record):

ProcessOrder  ProcessBegin  ProcessEnd
------------  ------------  ----------
208           2016-01-04    2016-01-10
209           2016-01-11    2016-01-17
210           2016-01-18    2016-01-24
211           2016-01-25    2016-01-31
212           2016-02-01    2016-02-07
213           2016-02-08    *NULL*

EDIT

I am guaranteed that values in the ProcessOrder column are consecutive with no missing values.


texts: SQL Query to compare (HL7) value at certain position in string based on delimiters (not position)

texts:
This is complicated by the fact that I don't know what DBMS the backend uses.  I know it's Cache, but I don't seem to be able to do anything but the most basic commands.  I cannot seem to SET or DECLARE variables.

Here is an example:

  MSH|^~\&|unimportant|This is the substring I want^123123123^ABC|unimportant|...

So let's call that specific substring SendingFacility.  What is the simplest, most compatible way, to write something like this:

SELECT * WHERE SendingFacility = 'This is the substring I want'

I tried SELECT WHERE SendingFacility REGEXP '<some regex>' but I get the following error:

[Error Code: 14, SQL State: 37000]  [SQLCODE: <-14>:<A comparison operator is required here>]

If this looks like HL7 that's because it is :)


texts: Does this simple database design work even if it's cyclic?

texts:
I am a graduate student working on breast cancer in a veterinarian facility. I am designing a database to keep track of the health of my mouse colony.

Each day, animal care staff verify each cage for health problems. If they find something suspicious (e.g. tumour, skin ulceration), they file a health report for the veterinarian to check. Each report can have multiple observations.

Then, the vet decides whether or not it is a real case. If it is real, the vet will create a clinical case number with open/close dates. If not, they won't open a case. However, I want to keep track of these "false positives" for further study (at this point, a primary key can be automatically created).

Here is my tentative database design:

Please correct me if I am wrong, but I have created two transition tables (red) to respect the first nominal form for the observations (i.e. only one value per entry)?

Finally, I have not seen any database design like mine where the tables are connected into a circle, so I am worried that I have made an error.

I would greatly appreciate the community's feedback. Thank you!


texts: A simple T-SQL query plan reuse question

texts:
Even though I have parameterized this INSERT on SQL Server, it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?
The query is simple and fully parameterized:
INSERT INTO USER_LOG_DATA (POSTDT, SESSION_ID, USERID, SUBJECT, APP, ACTION_NAME, ARGS, SUMMARY, DETAIL) VALUES (@timestamp, @session, @user, @subject, @app, @action, @argsString, @summary, @detail)
However if the length of one of the strings changes, the plan is not re-used.  So if the value of the @userid changes from 10 to 8, a new plan is created.


texts: MySQL - Alter table to automatically put in a UUID

texts:
Ok, I know I've done this before.  But I cannot for the life of me figure it out.  I created a table.  One of the columns is labled "LogID", it is also the primary key.

How do I alter it so that this column generates a UUID for each new entry?

Thanks


texts: databasename_indx.ndf file not referenced but can't delete

texts:
I have a SQL Server 2000 Express database for a legacy app I'm ditching soon. Until then, I'm at my 2 GB limit.

According to sp_helpfile the database has one .mdf (900 MB) and one log file, but the .ndf (1100 MB) is not referenced. But if I stop the SQL Server and delete the .ndf file, then I can't access the database once I start SQL Server back up, and the following error is logged:

  Data File .\CallAccounting_indx.ndf does not exist - unable to activate.
  Device activation error. The physical file name '.\CallAccounting_indx.ndf' may be incorrect.

I'm in the middle of purging old records to free up pages so I can't recreate the other errors just now.

I have no idea how to proceed here. Am I truly hitting the 2 GB limit, or is there something easy I can do here to clean up?

groupid allocpolicy status  groupname
1       0       16  PRIMARY
2       0       0   INDEXGROUP

fileid  groupid size    maxsize growth  status  perf    name                filename
1       1       59360   -1      10      1081346 0       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
2       0       96      -1      10      1081410 0       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf

status  fileid  name            filename
32770   1   CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
32834   2   CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf

fileid  groupid size    maxsize growth  status  perf    dbid    name                filename                                                                    createlsn               droplsn
1       1       59360   -1      10      32770   0       5       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf     0x00                    0x00
2       0       96          -1  10      32834   0       5       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf      0x00000000000000000000  0x00000000000000000000
3       2       50208   -1      10      32770   0       5       CallAccounting_indx C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_indx.ndf    0x050000001E0000000100  0x00000000000000000000


texts: Use of log_bin_trust_function_creators in MySQL

texts:
What is the use of log_bin_trust_function_creators?
Also, Is the value of the parameter dynamic?
Will it change if I restart my MySQL service?


texts: How to determinate in a trigger direct inserts and inserts via stored procedure

texts:
TableX can be modified in two ways:

client does "direct" inserts
client uses stored procedure to inserts records

How to determinate the way of client's call (direct or stored proc) in a trigger of tableX

Thank you.


texts: How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

texts:
How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone?

I tried the following:

CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ(
    '1970-01-01 00:00:01', '+00.00', @@session.time_zone));

but got a syntax error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near CONVERT_TZ('1970-01-01 00:00:01', '+00.00', @@session.time_zone)) at line 1

Is there any way to do this without creating a TRIGGER?  If not, how would I do it with a TRIGGER?


texts: How to Check and Repair MySQL database Using Mysqlcheck

texts:
I need repair Mysql database using Mysqlcheck command

this is the guide

run MySQL 5.5 Command Line Client as Administrator

insert password

and run

mysqlcheck -r mydb

the return

I don't understand if working or not ...

Please, can you help me?


texts: Pagination performance, with subquery, inner join and where

texts:
I am trying to optimize following query (this is most simplified version I could came up with):

SELECT tr.Id, StatusDate
FROM (
    SELECT tr.Id, tr.StatusDate
    FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
    INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
    WHERE tr.ClientId = 4 AND dev.Deleted = 0
) AS tr
ORDER BY StatusDate DESC
OFFSET 1000000 rows
FETCH NEXT 25 ROWS ONLY

The problem is, query performance is directly proportional to OFFSET - for offset=0 query executes in 0.0s, but for offset=1000000 execution time is about 23s (and with even greater offset, it can take up to few minutes).

I'm almost sure that my problem could be solved with appropriate clustered index on ArchivedTaskResults table, but after trying for few hours I still haven't found good index yet.

ArchivedTaskResults tables are really big, having about 50000000 rows (50 M)

Additional information:

I would be extremally happy if someone could solve problem I described above, but to be honest, my REAL query is even more bizarre (disclaimer: I am not the one that designed this database):

SELECT tr.Id, StatusDate
FROM (
    (
        SELECT tr.Id, StatusDate
        FROM mon.TaskResults as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    ) UNION ALL (
        SELECT tr.Id, tr.StatusDate
        FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    ) UNION ALL (
        SELECT tr.Id, tr.StatusDate
        FROM mon.ArchivedTaskResults_201505 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    )
) AS tr
ORDER BY StatusDate DESC
OFFSET 1000000 ROWS
FETCH NEXT 25 ROWS

This is much more complex, because even queries with offset=0 have long execution time (I guess its because sql server have no idea that ArchivedTaskResults_201505 comes 'after' ArchivedTaskResults_201504, and tries to sort them in memory, but it's only my blind shot).

It would be beyond my wildest dreams if someone managed to help me with THAT query, but if it's impossible because of strange database design, I guess I could solve it with software (I could query tables one at a time from my application, instead of doing everything in SQL. Or using stored procedure for that) - but only if my first query could be improved.

Thanks in advance.


texts: Does relational design discourage splitting tables unnecessarily?

texts:
It's been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies
K -> A
K -> B
K -> C

my assumption was that the "best" schema is just {KABC} and not something like {KAB, KC} or even {KA, KB, KC}. At least in practice that is how I've seen database designers implement the table.
However, a quick refresher on Wikipedia indicates that the normalization formalism

doesn't make any statement in the direction of obtaining a "minimal schema",
6NF would even require {KA, KB, KC}. Since 6NF implies the other normal forms, it implies that it is even impossible for them to make such a minimal requirement.

I'm a bit confused that I got this wrong all the time. Does the notion of "obtaining a minimal number of tables" really play no role in formal relational design, and it is just common practice?


texts: Exception handler on multiple row insert PostgreSQL

texts:
I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong:

BEGIN
  INSERT INTO suppliers("id","name", "url", "logo", "clicks", "order")
                SELECT * FROM dblink('dbname=linkeddatabase', 'SELECT id,"name", url, logo, clicks, "order" FROM suppliers')
                AS supp(id int,"name" character varying, "url" character varying, "logo" character varying, "clicks" int, "order" int);
  EXCEPTION WHEN unique_violation THEN
  -- do nothing
END;

Problem:

It looks like it stops as soon as it reaches a duplicated row. Cause as soon as the function is finished, my table contains the same amount of rows...

What I want:
If it needs to insert 1000 rows, of which the first 100 are duplicated, it still needs to insert the last 900 rows. Any ideas to fix this with the current function?
Or do I just need to create some loop?


texts: String comparison with integer is not working sometimes in MySQL 5.5

texts:
I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working.

Code is "value = 1", here 'value' is declared as varchar(45) and its value can be anything either 1 or some string.

But after upgrading, it is not working properly. Sometimes it works fine and sometimes not. I tested in different setups, in some setups it is working but in some other it is failing.

Here if I change the code as value = '-1', then it is working fine in all cases and different setups.

But I had used this case in my application in many places.

Is there anything to be handled for newer version other than using single quotes?

Please help me out of this.


texts: How Does SQL Server Choose Indexes to Use

texts:
I've recently begun working on indexing the various views in our DB. Often, some of these tables are only used for joins, and rarely are they used in where or order by statements. What this means, is that we are creating multiple indexes (or one large relatively less efficient index) which are all keyed on the primary key. I should clarify that there are no where or order by containing anything from this table and there are no key-lookups in the views I am having issues with.

If SQL has multiple indexes that are all keyed on tbl.ID, how does it make the choice to use one index over the other? I find that it can vary between views even if the data pulled from the index will be the same. Generally, there is not a huge gain/loss in efficiency in choosing one index over the other; leading me to believe that it chooses a 'good enough' index and moves on (since there are many keyed off of the same column).

Further, should creating multiple indexes with the same key be avoided for this reason?

The specific problem i am currently dealing with has the view choosing an index like this:

CREATE NONCLUSTERED INDEX [index1]
ON a.tbl ([ID])
INCLUDE (number,name,year, ...)

Where there are 14 columns in the include (it's unfortunate, but required based on our current query structure)

Over the more efficient index:

CREATE NONCLUSTERED INDEX [index2]
ON a.tbl ([ID])
INCLUDE (number,name,year, ...)

Where there are only 5 columns in the include.

Both indexes have the information that the view needs, but one is obviously much smaller and more efficient to use. The larger index needs to exist to cover a massive view that calls all of the columns in it (roughly half of the table).

It is probably worth mentioning that I could choose to create one massive index as above, which covers almost every view slightly less efficiently than these smaller indexes do. From my testing, it seems that adding these additional indexes does not add a significant cost to updating the tables; and so I chose to create them for the small performance gains. In general, they are used as they are needed, but in certain cases, the optimizer chooses a less efficient option for no apparent reason.


texts: Modifying table structure within a transaction?

texts:
In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction.

We have a tool (written in Java) that should modify a schema by:

adding some columns
removing some columns
updating a description table with the new schema layout

The tool as written will display the current schema layout to the user and allow him to modify it. (Basically adding or removing custom "attribute" columns to some tables) Once he's satisfied, he can apply his changes.

Please note: The basic schema layout, and the fact that you need to ALTER TABLE to change some things, is predefined by a standard and cannot be changed by us, as other tools wouldn't work anymore.

The problem now is that we cannot run these changes in a single transaction since, AFAIK, it's not possible to do multiple ALTER TABLE statements within a transaction.

What options do we have to "roll back" to the initial state if something goes wrong while applying the changes?

Note: Someone here proposed RESTORE POINT + FLASHBACK -- is it a good idea to call this from this (Java) tool? (We do not fully control the database instance at some sites where the tool should be used.)

Note: Oracle 10g2 and above


texts: In DB2 LUW 9.5, is there any way to change the collating sequence without recreating the db?

texts:
The documentation says "no" :-)

I'm looking for some heady old DBA to whisper dark secrets about cludgy hacks that I'll get fired for sure for using ;-)


texts: MariaDB Full Text Index - How to query old data as well

texts:
I have added new full text index to mariadb

ALTER TABLE <table name> ADD FULLTEXT INDEX records

I can query against this index only newly added and updated records but for older records (which is added before this index) I cannot query

Is there any workaround that I can also fetch older records


texts: Will mysql_secure_install script block master updates to slave?

texts:
I had setup two virtual mysql database servers in two oracle linux 6 vm's, which come with mysql server ver 5.1.47

Master named prod_master & slave named prod_slave1.

I installed both the mysql servers in two virtual hosts machines with mysql_secure_installation script :

which removes test db
change root password
disable remote login

The bound ip-address for prod_master to 192.168.1.100 & prod_slave1 to 192.168.1.101

I had enabled the bin-log and index in my.cnf on master and relay log in slave. On master, I had created user repl_user and granted replication privileges and started master. The master has successfully started and created bin-log.
When i issued the commands show master status, show binary logs, the logs are visible.
Coming to the slave, after configuring the my.cnf, issued the change master to using repl_user with credentials and issued the command start slave.
slave started. Then issued the command show slave status : it shows slave status like "connecting to master" in the slave attribute connection i/o error : unable to connect to the master with port no 3306.

I had granted the replication privileges with @192.168.1.%, and also issued the port on slave side. Now, here the major issue is: I couldn't connect remotely to the master or even from slave. From master connecting to slave :

mysql master> mysql -h 192.168.1.101 -uroot -proot ( not able to connect )

I am not even getting connection refused error. And the same case even from slave to the master. After starting the slave i had viewed the slave mysql.log found that slave replication thread getting dead. due to connectivity issue. Is it problem , that i had installed using the script mysql_secure_install. Please guide me. I Want to know whether :

grant issue for replication problem (or)
because of the script mysql_secure_install

Note : Both the master and slave vm-machines are pinging on both sides.


texts: MySQL partitioning by hash not repeatable

texts:
I have a MySQL (5.6.17) table with a few million records that I wish to partition. In order to do this, I use this query:

ALTER TABLE tablename
    PARTITION BY HASH(id)
    PARTITIONS 8;

Seems to work fine and PHPMyAdmin reports 8 partitions, with the data sufficiently distributed amongst the partitions.

If I repeat the exact same query, PHPMyAdmin again reports 8 partitions, but with a completely different distribution of the data. Every time I run the query, it results in different distributions.

The MySQL documentation says HASH does a modulus on the data, so it should be repeatable and produce the same results every time.

Can anybody explain what is happening?
Is PHPMyAdmin reporting it wrong?
Does HASH() do something else than a simple modulus?

p.s. The column type used for the ID is an INT and I'm testing on a non-live table, so content is unchanged between queries.

p.p.s. Running EXPLAIN PARTITIONS on some queries seems to indicate the records are partitioned by modulus.


texts:
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
         CROSS APPLY (SELECT TOP 1 grp_id as gid
                      FROM tbl_groups as g2
                      WHERE g1.grp_fk_obj_id = g2.grp_fk_obj_id
                      ORDER BY g2.date_from DESC, ISNULL(date_to, '4000-01-01') DESC) as a
WHERE g1.grp_id = gid

grp_id is a primary key. grp_fk_obj_id is a foreign key to another object. There are indexes on both of these columns (I guess it comes as default).
It takes about half a second to complete but I need it to make work faster. I took a look at the execution plan and it shows that "Top N sort" has a cost of more than 90%. Also, I have noticed that if I remove a where clause inside the cross apply then it runs at least 5x faster, but I need that where clause in one way or another.
Do you see any possibilities to improve the performance of this query?
EDIT:
table creation DDL:
create table tbl_groups
(
    grp_id        bigint identity
        constraint PK_tbl_groups
            primary key,
    grp_fk_obj_id bigint      not null
        constraint FK_grp_fk_obj_id
            references tbl_other,
    grp_name      varchar(30) not null,
    date_from     date        not null,
    date_to       date
)
go

create index IDX_grp_fk_obj_id
    on tbl_groups (grp_fk_obj_id)
go

create index IDX_grp_name
    on tbl_groups (grp_name)
go


texts: File permissions for database backups and certificate backups

texts:
I have backed up a database and backed up the database master key to the very same folder. Everyone has full access to this folder, however, only the database backup inherits the permissions of the folder whereas the key backup does not.

I need to use admin privileges to view the security of the master key backup file.

Can anyone suggest why this may be?  Both were created by the same account via backup command in T-SQL.


texts: How to Deny permissions for newly created user in DDL Trigger?

texts:
I'm having trouble with a SQL DDL trigger for Create_User and I'm wondering if there is an issue with my syntax or something else I'm not seeing?

I have some tables in my database which are only available to a few users. The rest of the users in the database are not allowed to query these tables in any way. I've had no problems limiting current user permissions with

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO UserName

I'm trying to set up a DDL trigger to automatically run the Deny command when any new users are created. I am not the only one creating users in the database, so I won't be able to manually set permissions myself when new users are added.

With my trigger, I constantly receive the error

Cannot find the user 'UserName', because it does not exist or you do not have permission.

If I change the UserName placeholder to an already existing user in the trigger, I do not receive any errors - so it is not a permissions issue for my account. Here is my trigger:

CREATE TRIGGER [NewUserCreation]
ON DATABASE
After Create_User
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();
    SELECT UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
    DENY SELECT, INSERT, UPDATE, DELETE, ON dbo.SecretTable TO [UserName]

END
GO

ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO

Is there something I'm missing? The test results I've experienced so far seem to imply that the trigger is firing before the user account is actually created.


texts:
I have searched but not been able to find a list of the codes and descriptions available when using the configuration.ini file.

Is anyone able to link me some information?


texts: Is it possible to filter the list of databases in the Object Explorer of the SSMS?

texts:
I know how to filter the list of tables - using the filter icon. However, this icon is disabled when the Databases node is selected.

Still, I am wondering whether it is possible.


texts: Delete duplicate records with no change in between

texts:
I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 out of 30 vendors a day. Each vendor has around 35,000 unique products. No product can belong to more than one vendor.

CREATE TABLE vendor_prices (
  id serial PRIMARY KEY,
  vendor integer NOT NULL,
  sku character varying(25) NOT NULL,
  category_name character varying(100) NOT NULL,
  price numeric(8,5) NOT NULL,
  effective_date timestamp without time zone,
  expiration_date timestamp without time zone DEFAULT (now() + '1 year'::interval)
);

I'm trying to delete irrelevant records where there was no price change and it's no longer the last update for said product, eg.:

  effective_date     price
  '2015-05-01'       $1.99
  '2015-05-02'       $1.99 delete
  '2015-05-03'       $1.59
  '2015-05-04'       $1.99
  '2015-05-05'       $1.99 delete
  '2015-05-06'       $1.99 keep for new expiration date

So after each load (I figured it'll be easier for one vendor at a time) I want to do some kind of delete. Here is the long non-performing solution that I came up with.

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor integer)
  RETURNS integer AS
$BODY$
BEGIN
    -- Delete Redundant prices
    delete from vendor_prices
    where id in (
      select id from (
        select vp1.id, vp1.vendor, vp1.sku, vp1.price, vp1.effective_date, vp1.expiration_date
          from vendor_prices vp1
          inner join (
              select vendor, sku, price from vendor_prices
                where vendor = _vendor
                group by vendor, sku, price
          ) vp2
          on vp1.vendor = vp2.vendor and vp1.sku = vp2.sku and vp1.price = vp2.price
          where vp1.vendor = _vendor
      ) dupe

      -- fetch the irrelevant record
      WHERE (select a.effective_date from vendor_prices a
        where vendor = _vendor
        and a.price = dupe.price and a.sku = dupe.sku and dupe.effective_date > a.effective_date

        -- but make sure there's no price change in-between(
        and (select b.effective_date from vendor_prices b
          where vendor = _vendor
          and b.sku = dupe.sku and b.effective_date < dupe.effective_date and b.effective_date > a.effective_date limit 1) IS NULL
          limit 1
      ) IS NOT NULL

      -- and that this is not the last update on said product, otherwise we'll keep it for expiration_date
      and ( select c.effective_date from vendor_prices c
              where vendor = _vendor
              and c.sku = dupe.sku
              and c.effective_date > dupe.effective_date limit 1
          ) IS NOT NULL
    );
 return 0;
END;
$BODY$
LANGUAGE plpgsql

This function ran for a few hours so I killed it. The table has around 5 million records. I tried all kinds of different indexes and combo indexes but nothing seems to help. There may be other inserts and deletes while I'm running this function.

Running PostgreSQL 9.3.4 on Solaris 11.2.
I have plenty of RAM and disk space.


texts:
Hi there everyone!

I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, racing, extreme and trending attached to it. The way it gets stored into the database is by a linkcats table in which it stores the link_id and the cat_id. The example above would result in 4 records for that link_id, since it had four categories assigned to it.

The site learns what people like as they browse. As they view, vote on and post content, the site figures out their most and least favorite categories. My intention is to display the results of what the site thinks is their most favorite while cutting out what it thinks is their least favorite. To do that, I'm trying to display links from their 5 most favorite categories while nixing them if any unfave cats are assigned to them.

Here's what I tried:

SELECT shortenified_urls.*
FROM shortenified_urls
LEFT JOIN linkcats ON linkcats.link_id = shortenified_urls.id
WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)
AND shortenified_urls.is_social = '1'
AND shortenified_urls.active = '1'
GROUP BY shortenified_urls

This didn't work, I think because of the multiple categories. Since the links are being matched more than once due to multiple category matches, even if links matching 28 get passed by, if that same link gets matched on another of it's categories, it will still get included in the results. At least, I think that's what's happening.

Does anyone have an idea on how I might alter the query to achieve the desired result?

Thanks for your time!


texts: Will deadlocks occur when deleting rows from a table that is being added to by another process?

texts:
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.

A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.

The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.

My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?

The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?

Should I also set TRANSACTION ISOLATION LEVEL?

Thanks

Table Definition:

    CREATE TABLE [dbo].[DummyName](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
    [Payment Amount] [decimal](28, 10) NULL,
    [Payment Date] [datetime] NULL,
    [Payer ID] [nvarchar](34) NULL DEFAULT (N''),
    [Payer Account] [nvarchar](174) NULL DEFAULT (N''),
    [Payer Name] [nvarchar](174) NULL DEFAULT (N''),
    [Payer Type] [nvarchar](35) NULL DEFAULT (N''),
    [Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...

Index Definition:

ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


texts: Can't connect Oracle 11g2 Database to Job Scheduler Agent on client/application server

texts:
Running 11g2 database on one Linux server and installed Oracle client and job scheduler agent on separate Linux (application) server sitting next to it. The connection betweeen them worked fine for years.
Then I changed the host names for the both the db and app servers.
Database Server Host Name Change:
host4.old_domainname.com --> host4.new_domainname.com

Application Server Host Name Change:
host3.old_domainname.com --> host3.new_domainname.com

I didn't change the IP addresses, nor the Oracle database SID or service name. I updated the schagent.cont file HOST_NAME appropriately, as well as /etc/hosts files on both servers. The tnsname.ora and listener.ora files assigned 'HOST=host4' so I didn't update them. Then I rebooted the servers and brought up the DB, listener and schagent, but get an error:
Thu Dec 31 21:54:00 2020
Errors in file /home/dbname/oracle/diag/rdbms/dbname/dbname/trace/dbname_j000_11467.trc:
ORA-12012: error on auto execute of job "schemaname"."jobname"
ORA-12537: TNS:connection closed
ORA-06512: at "SYS.DBMS_ISCHED", line 4966
ORA-06512: at "SYS.DBMS_ISCHED", line 6898
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-12541: TNS:no listener
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3975
ORA-06512: at line 1

Looking for hints how to troubleshoot/fix.


texts: Microsoft SQL Server AG Synchronous Mirroring is not synchronous

texts:
We were upgrading to SQL Server 2012 and decided we wanted to divorce ourselves from replication we were using for Reporting services on Node 2.  AG and mirroring looked like a good solution -- and we went with synchronous.  It was also decided that app's would write to node 1, and read from node 2 to minimize contention on the data.

One app was written to insert the data, and then they check immediately on node 2 whether the data they inserted is present.

Now from my understanding, synchronous mirroring on AG should receive the request, send the data to node 2 -- node 2 commits, then tells node 1 its done and then node 1 returns to the user..

What occurred was not that at all -- they sent the data, did a retrieval from node 2 -- data did not match. Error.

I checked and double checked settings, and had another DBA who had been using AG's and synchronous mirroring look the stuff over -- and our best conclusion is that from what we are seeing Microsoft's 'synchronous' mirroring is NOT synchronous.  Has anybody else seen this?


texts: Restoring a single database from mysql enterprise full backup

texts: I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .


texts:
I have big dynamically constructed query with

FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1

Query is very slow, but if I remove my_func() from JOIN condition, it works ok. Next variant has the same time:

FROM (
  SELECT *
    , my_func(col1, 1) AS join_cond1
    ...
    , my_func(col1, 6) AS join_cond6
  FROM main_table
) t
  LEFT JOIN table1 t1 ON join_cond1 = t1.col1
  ...
  LEFT JOIN table6 t6 ON join_cond6 = t6.col1

How I can speed up the query?

Update

I wrote that I can't create 6 indexes on all the table1...table6, but indexes are needed on main_table only. Sorry for my mistake.

PostgreSQL has functional indexes, and I tried dynamic generation like

EXECUTE 'CREATE INDEX ix_main_table_my_func_'||_number||' ON main_table (my_func(col1, '||_number||'))';

and it was much more quickly than CTE. But I have troubles with deadlock if function is called concurrently from multiple places since CREATE INDEX sets lock. PostgreSQL can CREATE INDEX CONCURRENTLY ..., but it does not work in transaction blocks (and functions because functions are executed in transaction blocks).


texts: How to aggregate timeseries several months long - when I only want a portion of each month

texts:
I have a need to see a trend of data for "month to date"  So I want to pull the 1st x number of days of each month for the last 14 months, and the n aggregate this.

My data has approximately 10k data points each day

So far I have only been able to figure out how to do this by writing a double while loop - the outer loop counting down the months and the inner loop selecting each day and aggregating the data for the day - then storing it in a temp table.

Once I have stepped through each month and each day I then selet the data from the temp table and aggregate this to give me monthly data summed for each contract type.

For various reasons I need to run my select 4 times for each day.

This all ends up meaning my SQL takes around 120 secs to run.  This is sub-optimal - as I am hoping to have this used by SSRS to pull a report on demand when a user wants to see it. Making them wait 2 whole minutes?  Not Desirable - especially considering primary target audience is the exec team

Here is my SQL (NB I've changed a couple of table/ column names )

Declare
    @ReportDate as DATE
    ,@month as DATE
    ,@dayCounter as INT
    ,@monthCounter as INT
    ,@reportDateCurrentMonth as DATE

SET @ReportDate = sysdatetime()
SET @month = @ReportDate
SET @dayCounter = DATEPART(DAY,@ReportDate)
SET @monthCounter = '0'
SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)

CREATE TABLE #DailyVolumes
    (
       contract_name varchar(50)
       ,Volume INT
       ,date_registered DATETIME
    )

WHILE @month > DATEADD(month,-15,@ReportDate)
    BEGIN

          WHILE @dayCounter > '0'

          BEGIN

                INSERT INTO #DailyVolumes
                SELECT
                CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name
                    ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
                    ,a.date_registered
                FROM accession a
                    Left  join value1 P on a.value1_id = p.value1_id
                    left  join [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn on a.contract_code = DWRccn.contract_code
                where
                    [date_registered] = @reportDateCurrentMonth
                    AND a.lis_code = 'S'
                    AND visit_type IN ('I', 'E')
                GROUP BY
                    CONCAT('PREFIX-',DWRccn.contract_name)
                    ,a.date_registered

                -- Set new values on Daily counter & Date to grab
                SET @dayCounter = @dayCounter - '1'
                SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth)
          END;

       SET @monthCounter = @monthCounter - '1'
       SET @month = DATEADD(month,-1,@month)
       SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
       SET @dayCounter = DATEPART(DAY,@ReportDate)

    END;

-- SUM Daily Data into Monthly Slices
Select
    sum(Volume) AS Volume
    ,contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered
FROM #DailyVolumes
group by
    contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0)
ORDER BY
    MonthRegistered DESC
    ,contract_name
-- Clear Temp Table
DROP TABLE #DailyVolumes

I am hoping someone can tell me how to accomlish what I am after...  Which is to do away with the loops and have the data aggregate in a single operation

NB I have done my aggregation for full month periods - that was a piece of cake compared to this


texts: Creating encrypted backups inhouse to be restored at client

texts:
I am trying to understand the mechanics of database backup encryptions, but there are some issues, which I can't seem to wrap my head around. I have 2 requirements:

I need to be able to create and restore ENCRYPTED database backups "locally", i.e. on servers, which I have full access.
I need to be able to transfer the ENCRYPTED database backup to a client, who is responsible for the administration as well as performing the restore.

On my local server

I have created a Master Key and backed it up to a secure location:

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey_Password';

BACKUP MASTER KEY
TO FILE = '<Secure_Location>\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';

Next I have created a certificate and backed it up to a secure location:

CREATE CERTIFICATE MyBackupCertificate
WITH SUBJECT = 'MyBackupCertificate_Subject';

BACKUP CERTIFICATE MyBackupCertificate
TO FILE = '<Secure_Location>\CERTIFICATE_BACKUP.cer'
WITH PRIVATE KEY(
    FILE = '<Secure_Location>\CERTIFICATE_BACKUP_PRIVATE_KEY.key',
    ENCRYPTION BY PASSWORD = 'Certificate_BACKUP_Password');

Finally, I have backup up the database using the certificate to encrypt it:

BACKUP DATABASE MyDatabaseToBackup
TO DISK = '<Backup_Location>\BACKUP_FILE.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyBackupCertificate)

On my client server

I have transferred the database backup file to a location accessible from the SQL Server Instance. Also the .cer file has been transferred to this location.

USE MASTER
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Another_MasterKey_Password';

Created the certificate from the .cer-file:

CREATE CERTIFICATE MyDatabaseToBackup
FROM FILE = '<Client_Secure_Location>\CERTIFICATE_BACKUP.cer'

When trying to restore the file list, I am met with the message

RESTORE FILELISTONLY FROM DISK = '<Client_Backup_Location>\BACKUP_FILE.bak'

  Msg 15507, Level 16, State 30, Line 11
  A key required by this operation appears to be corrupted.

I have been around some other posts, which are trying (almost) the same, but I do not understand, why I have to transfer the certificates private key to my client. Doesn't that defeat the whole purpose of encryption?

Restoring encrypted database on another server (using Backup Encryption)


texts:
What are the implications of changing the SQL Server Service Logon Account?
My default instance currently uses Network Service.
I want to change this to a domain account.
Are there any implications?


texts: Model a tree that belongs to a row in another table

texts:
Given the tables "users" and "nodes" (which will contain an adjacency list tree) what is the best way to link these tables (each user has a tree).

Putting the column "user_id" in the "nodes" table seems very ineffective to me, but I couldn't find other solution.


texts: Services Cannot Connect to Multi-Subnet Availability Group After Failover

texts:
We have a two-node SQL Server 2014 AlwaysOn Availability Group that spans multiple subnets. We also have two proprietary services on the same cluster role as our AG. We decided to put them on the same role because we prefer that the services always be on the same node as the SQL Server in the event of a failover.

During a planned or unplanned failover, the two services cannot connect to the AG when they attempt to come online and fail with the following message.

  The target database, 'X', is participating in an availability group
  and is currently not accessible for queries. Either data movement is
  suspended or the availability replica is not enabled for read access.
  To allow read-only access to this and other databases in the
  availability group, enable read access to one or more secondary
  availability replicas in the group.  For more information, see the
  ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.

I do not expect this because of the following conditions:

The SQL client in the services are using the MultiSubnetFailover=true keywords.
I confirmed on each node that both IPs of the AG Listener are being returned by DNS.
I can connect to the listener using sqlcmd (with the -M switch) on the node that is attempting to come online during the time the services fail to connect.

What usually happens is that the cluster will attempt to bring up the services on the secondary node, which fails as per the message above. It then fails back to the original primary node, as per my the policy settings on the cluster services. I then immediately invoke the failover a second time and it succeeds.

The obvious culprit would be the service, but our developer has confirmed that the MultiSubnetFailover=true is being used in the connection string. Also, the service connects eventually, just not during the failover process. Has anyone seen anything that can explain this behavior or have any troubleshooting tips?


texts: How to boost performance of virgin queries in MS SQL Server?

texts:
I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to improve performance of first time (virgin) queries that go to that  SQL Server. Some queries process so much data that they may cause SQL Server to use tempdb. I don't use temp table variables or temp tables, so SQL Server decides to use tempdb by itself whenever it needs to.

My db size is 16Gb, I have 32Gb of physical RAM available on my server machine.

I understand that MS SQL Server caching strategy tries to keep data in RAM to speed-up performance of similar queries if they need same data to be loaded again. In addition to that it will try to use available RAM instead of tempdb to speed-up performance without causing disk access.

I suppose that when query that needs to store something in tempdb SQL Server comes and there is not enough RAM available, SQL Server has 2 choices:

1) to unload some cached data and use spared RAM instead of tempdb to avoid disk writes

2) keep cached data for future queries and start using tempdb, which causes writes to slow disk.

I don't know what choice SQL Server will make in this situation, but I would like it to make choice #1 because I care only about performance of first-time (virgin) queries, because I never send same query to SQL Server again (though I may send similar query).

What is SQL Server caching strategy for this scenario ?

How does it balance usage of RAM between avoiding tempdb for virgin queries and speed of second time queries ?

Is it possible to configure SQL Server in such a way that it will make choice #1 ? If yes then how ?

How else can I boost performance of all virgin SQL queries ?

Since I don't know about SQL Server caching strategy I want to place database on RAM Disk. This will make sure that any virgin query has high speed of loading of uncached data even if SQL Server always makes choice #1. The risk of it is that SQL Server may start using more tempdb with less available RAM (only 16Gb left after I use 16Gb for RAM Disk) if it keeps making choice #2, which will slow down those virgin queries that cause spills into tempdb.

I am interested in solution for SQL 2008 R2, but I guess it's probably the same for SQL 2008, SQL 2005 and may be SQL 2000.

Clarifications:

There are no other applications running on that box, it's dedicated to SQL Server. Website runs on separate box.

It's SQL Server 2008 R2 Standard Edition 64 bit on Windows Server 2008 R2 Enterprise 64 bit.

I run only read-only queries and database is set to be read-only.

Let's assume that there are already good indexes. This question is about SQL Server making choice #1 vs choice #2, how does it make it, if there is a way to control it and if RAM Disk helps it to make the right choice for virgin queries.


texts:
My local machine: Windows 7, 32-bit. I have installed on it the psqlODBC for 32-bit. MS Access is 32-bit as well. I have tried both Unicode and Ansi connections to...

The server: a 64-bit Ubuntu, and the PostgreSQL database I want to link Access to is SET SERVER_ENCODING TO LATIN1; because I am Spanish and the data this DB will be storing requires the server to be able to handle our characters and, correct me if I'm wrong, UTF-8 does not do that.

The database works, access works, and everything is just fine as long as I don't ask Access to retrieve and link a table stored in PostgreSQL which fields have accented characters (such as 'ó'). This is: I can link tables that have no fields with accented characters, and I cannot link tables that contain fields with accented characters. HOWEVER, there is no problem when the records contain accented characters: I can both enter them from Access and read them from Postgre back into Access.

Anyone knows the reason behind this weird behaviour? I can live without accented field names but it's really bugging me...


texts:
My disk I/O utilization is at 95% at my MySQL server. What are steps I could take to fix this? It is a Ubuntu VP with 16 GB Memory and 8 cores.

Here is a screen-shot of the load:


texts: How do you use Extended Events to find port numbers?

texts:
I'm migrating some TSQL code from one server to another.  The server I'm moving it from is production, so I'm not easily able to adjust settings for obvious reasons.  The code runs fine on the production server, but the firewall is off, we don't desire this configuration and want the firewall enabled on the new server.

How can I leverage Extended Events to tell me which ports are being used during TSQL code execution?

Or do I need to use something like WireShark?

SQL Server Version: 12.0.4213
Windows Version: 6.3.9600 N/A Build 9600 (2012 R2 Standard)
VMWare VM


texts: Incorrect total using SUM of DATEDIFF results

texts:
When i run a few DateDiff(SECOND, value1, value2) it returns a lists of values

93, 185, 1879  which totals 2157 sum with the calc and with SUM()

However, when i use DateDiff(SECOND, firstValue1, lastValue2) returned 2476

A example of the problem is:

declare @testTable table
(
    DateD float
)
insert into @testTable
select datediff(SECOND, '2015-08-12 22:40:29.847', '2015-08-12 23:11:48.000')
UNION
select datediff(SECOND, '2015-08-12 23:11:48.510', '2015-08-12 23:14:53.000')
UNION
select datediff(SECOND, '2015-08-12 23:20:12.147', '2015-08-12 23:21:45.000')

select sum(DateD)from @testTable

select datediff(SECOND, '2015-08-12 22:40:29.847', '2015-08-12 23:21:45.000')

What is my fault?

Thanks.


texts:
We are like SSIS heavy shop and there are more than 30+ packages that runs in that 24 hr cycle. However, lately every few other weeks we are noticing that the packages fail to execute and throws out this error provided below in the job log. Our packages are configured either through SSIS catalog or through packages stored in msdb. However, both of them are failing. I came across one of the articles from Microsoft and this is the only article that is an exact replica of what I am facing in my environment but even after applying the same solution I am back to square one with the error re-appearing. I've provided the environment details below. Can someone point me in the right direction.

SSIS package fails with error : The step did not generate any output. The return value was unknown

Message:

  Executed as user: x. The step did not generate any output.  The return value was unknown.  The process exit code was -1073741502.  The step failed.

SQL Environment Details:->
SQL Server 2012 SP3,
Windows Server 2012 Datacenter (x64),
RAM: 28 GB,
Only a single SQL instance running on the machine and there maybe a couple of other services but that's all.


texts:
I have a SQL Server 2008 instance and one table is quite large (log data) so I delete old logs. Now I start to read articles on this problem, here is one.

As I see it there is no solution! If I shrink the database and then rebuild the index, the database will be about the same size as before. If I don't rebuild the index, the database will shrink, but performance will be low, which is not an option.

So shrinking for production databases seems not to be an option. Is there any solution? Does deleting from the database make any sense? How do big companies delete log data from their databases?


texts:
Sorry if this question is very basic, but I just can't figure it out, and couldn't find any good answers.

I have a very long list of files, folders and Sizes. I need to Seperate the folders to columns, that I have a column for each folder.

I have a FilePath as a String (eg folder1\folder2\folder3).
I want to seperate this into multiple Columns:

 First  |  Second  |  Third  |  Fourth  |  ...
folder1 | folder2  | folder3 | NULL     | ...
Foldera | folde rb  | folderc | folderd  |

using cross apply string_split(PATH,'\')as folder
I get Folder1.
using row_Number() I can define wich folder I have in my column, but it is always only 1 column.

Actual Example:

select [Parentpath], [Size], [spl].[value] from Files
cross apply string_split([ParentPath], '\') as [spl]

Parentpath                      || Size   ||  Value
Business\Packets\Data\Archive   || 29334  || Business


texts: What is the best way to transport database fields from one database to another?

texts:
I have two databases. The table name and fields name are different and field numbers are unequal. I need to transport all fields from one database to another. I can import the database as CSV format.

I can use a PHP script which will accomplish this.

But is there any other way to do this easily without any script.


texts: SQL Server certificate for backups: Does expiration date matter?

texts:
If I have a cert in SQL Server, used for backups:

Can backups still be created after cert expires?
Can restore still happen after cert expires?

What is the point of having these certs expire? (other than to expose me to backups failing due to expired cert...)


texts: How to get INNER JOIN result from two tables

texts:
I have the three MySql tables :

table_fonts

font_id (int)
font_name (varchar)

table_charsets

charset_id (int)
charset_name (varchar) (examples: 1=latin, 2=greek, and so on)

table_charsets_by_font

font_id (int)
charset_id (int)

I am having trouble into creating my INNER JOIN query to obtain a list with all charsets (table_charsets.charset_name) listed in columns, with a value of 1 if existing, or a value of 0 if not existing — aka a TINYINT —, like this :

font_id | font_name | charset_id1 | charset_id1_existing | charset_id2 | charset_id2_existing ... and so on

For example :

1 | Arial           | 1 | 1 | 2 | 1 ... and so on
2 | Arial Black     | 1 | 1 | 2 | 1 ... and so on
3 | Times New Roman | 1 | 1 | 2 | 0 ... and so on

So far, all my attempts have failed. Could someone point me to the right direction ?
Thank you :)


texts: Import Oracle schema data without losing modifications of stored procedures

texts:
I have this scenario:

One huge (thousands of tables), complex, Oracle 8 production database,
One huge (thousands of tables), complex, Oracle 9 development database,  (same structure as production)
In the development database, some stored procedures and packages have been modified and new ones have been added.
The development database has new tables in some schemas
Neither Oracle 8 exp nor Oracle 9 imp has ROWS ONLY option

We usually do as follows because it's the only way to get data updated properly because a import with ignore=yes would only insert new data, but wouldn't update pre-existing rows with the same PK but different values in non-PK columns:

Delete one schema, then create the user again to have an empty schema
Import from user to user to the empty schema

The problem is:

How to update the development database with fresh data from a production export without having to delete the schema first, since there're new stored procedures/packages as well as modified ones in the development database ?
The comparing process to re-create only modified or new stored procedures after deleting the schema, for getting them back from a backup, would be too error prone.
There are thousands (literally) of tables so we don't want to program a stored procedure to refresh the data in certain order etc. That would take months to write and test.

What would be an import-based solution to this ?

EDIT: I failed to mention that prod is Solaris and dev is RedHat.


texts: Best database replication technique for a very unstable (power wise) network

texts:
I am part of a small network (500 users) who are all connect via WiMax to each other. The people connected to this network live in about a 70 km radius. This network allows for a transfer speed of about 1.5 MB/s. We are using a website which uses a Mysql database. We are all living in South Africa which is currently experiencing a phenomenon call Load Shedding. This is where the municipalities turn of the power to specific regions to save power as South Africa is currently experiencing a power shortage.

We want to set up a database replication grid for this website in question. In the case of load shedding, no warning is given for power failures so it can be assumed that the servers will shut down abruptly.

Can anyone recommend a database setup and replication type the will allow seamless synchronization after load shedding occurred? We would like for both servers to have write access for in the case that one server goes down, the user will still be able to connect to the online server.

So in a nutshell:
 - Seamless/Easy/Automatic synchronization in the case of inconsistencies in the database.
 - Fast enough to not delete the entire database, and re-download it form the acting master server.
 - Able to keep sync with a limit of 1.5MB/s bandwidth.
 - No data is lost. Both servers have to be in perfect sync.

Some other technical details of the database:
 - Handles average of 250 queries per minute.
 - Database size is 1 GB in size.

I have read up about Master-Master synchronization, but also read that it is not very reliable in abrupt shutdowns, nor for low bandwidth applications. Does anyone have an idea for a setup like this.


texts: How to establish a Two-to-One table relationship (PK-FK-CK)?

texts:
My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables.

I have Projects and People objects, mapped to their respective tables. Then I have Messages, also mapped to its own table.

A message can belong to either a person (people table) or a project (projects table) (n-1).

My question:

Should I create two columns -- belongs_to_id and belongs_to_type -- for Messages and query to get them? or;
Should I create an association table for each -- people_have_messages and projects have messages?
What is the correct way to work in this case scenario?
How can I create FK constraints and restraints if I go with the first option?

My problem is, as both a Project and a Person (people table) can have the same PK (id), it wouldn't be enough to reference without stating the table and I don't know what exactly to do.

Tables:

People:
--------------
ID (PK unique)
Name

Projects:
--------------
ID (PK unique)
Title
Description

Message:
--------------
ID (PK unique)
Text


texts: How to return several arrays of doubles as a table in C# SQLCLR function

texts:
What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It works fine for 1 array but I'm having trouble extending it to several. I'm mostly unsure on what to return from my method. Some code below:
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
    TableDefinition = "impliedVol float, maturity float, strike float")]
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(
     string uploadDate, double strike, double yearsForward, double intervalDuration,
     string curve, string surface)

//Create 3 arrays of doubles
    double[] array1;
    double[] array2;
    double[] array3;

    return [???];
}

public static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
   //impliedVol = (double)obj;  //This is what I do if only returning one array
}

EDIT:
Based on feedback this is my new attempted solution.
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(string uploadDate, double strike, double yearsForward, double intervalDuration, string curve, string surface)
    {
            //omitted code above this line.
        CapletStipping thisCapletStripping = new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
        double[][] theseStrippedCapletVols = thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

         List<capletVolatilityNode> capletVolatilitiesList = new List<capletVolatilityNode>(theseStrippedCapletVols[0].Length);

        for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
        {
            capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode(theseStrippedCapletVols[0][i], theseStrippedCapletVols[1][i], theseStrippedCapletVols[2][i]);
            capletVolatilitiesList[i] = thisCapletVolatilityNode;
        }

        return capletVolatilitiesList; // theseStrippedCapletVols;
    }

    public class capletVolatilityNode
    {
        public double impliedVol;
        public double maturity;
        public double strike;
        public capletVolatilityNode(double impliedVol_, double maturity_, double strike_)
        {
            impliedVol = impliedVol_;
            maturity = maturity_;
            strike = strike_;
        }
    }

public static void FillRow(Object obj, out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{

    capletVolatilityNode row = (capletVolatilityNode)obj;

    impliedVol = Convert.ToDouble(row.impliedVol);
    maturity = Convert.ToDouble(row.maturity);
    strike = Convert.ToDouble(row.strike);

}


texts: SQL Server 2014 Always on listener on named instance for default port

texts:
This is my issue.
I've set up a test environment with SQL Server 2014, 2 named instances, WSFC and AG. They're set up like this.

 sqllab04\inst01 - sqllab05\inst01 - Listener VT-DB-LAB02 (port 1433)
 sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

Both listeners have it's own IP with DNS set up (dns name above).

INST01 on both 05 & 06 are set with port 1433 as per default in config manager.
INST02 on both 05 & 06 are set with port 1432 as per default in config manager.

Listen All is active on both and on INST01 it's set TCP Port 1433 on all and INST02 is 1432 on all.

The problem is following:
When we connect using ssms to VT-DB-LAB02 we can connect without any issues.
When we then connect to VT-DB-LAB03 we can connect but the connection goes to VT-DB-LAB02

From what I understand the issue is that inst01 is using port 1433 as default instance and is listening on ALL 1433 but what I don't know is how to get around that.

We still want to use port 1433 for inst01 and not change it to another port which I believe would solve it. I know that it's not the most secure way to set it up but I'm just testing right now.

Any suggestions? Let me know if anything is unclear or if I've missed some information.


texts:
I have local set up for 3 mongo DB instances with successful replica set. (1 Primary, 2 secondary).

If I terminate Primary instance, one of the secondaries becomes Primary but its taking around 10 seconds.

Is there a way I can reduce the time.

I have updated rs.config as below:

  config = rs.config()
  {
"_id" : "rs0",
"version" : 3,
"protocolVersion" : NumberLong(1),
"members" : [
    {
        "_id" : 0,
        "host" : "127.0.0.1:27020",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    },
    {
        "_id" : 1,
        "host" : "127.0.0.1:27021",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    },
    {
        "_id" : 2,
        "host" : "127.0.0.1:27022",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    }
],
"settings" : {
    "chainingAllowed" : true,
    "heartbeatIntervalMillis" : 1000,
    "heartbeatTimeoutSecs" : 3,
    "electionTimeoutMillis" : 10000,
    "catchUpTimeoutMillis" : 2000,
    "getLastErrorModes" : {

    },
    "getLastErrorDefaults" : {
        "w" : 1,
        "wtimeout" : 0
    },
    "replicaSetId" : ObjectId("5976e7bd5b7837e0fbba2871")
  }
}

EDIT

I have reinstalled MongoDB enterprise edition:

   MongoDB shell version: 3.2.15


texts: How can I get SQL to do the sensible thing without resorting to a query hint?

texts:
I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000.

Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2) first. It seems obvious to do this first because:

only 10% of rows fulfil the criteria about StatusID (I have checked, and SQL's statistics know this)
Grouping has relatively few rows
there is an index on StatusID

Those things combined mean that if the G.StatusID IN (1,2) condition is looked at, far fewer rows from Items have to be examined.

Select
    GroupingID,
    Sum (Amount) FullAmount
From
    Grouping G
Join
    Items I
        -- With (INDEX(IX_Items_GroupingID))
        On G.GroupingID = I.GroupingID
Where
    I.ThingID Is Null
And
    G.StatusID IN (1,2)
Group By
    G.GroupingID

However SQL Server refuses to do this. It is reading the entirety of Items into memory, and suggests that I implement an index on ThingID. Implementing such an index does speed everything up, but I don't see why I should need to.

If I uncomment the index hint in the code above, SQL Server does as I want it to.

I'd prefer not to put the index hint into production. What is causing SQL Server to pick a slow plan?

(For background, around 25% of Items fulfil I.ThingID Is Null.)

Slow plan on Paste The Plan

Fast plan on Paste The Plan

Amount is a column from Items


texts: mysql multiple column index Cardinality order

texts:
How does Multiple-Column Indexes work in MySQL? How is it stored internally?
consider a table with Multiple-Column Indexes on two columns (X,Y).

If X has High-cardinality than Y i.e., X is less duplicated than Y, what should be the order of multiple-column index X,Y or Y,X.

If it is X,Y there is going to be lesser number of rows with same X value. will this improve the performance because B-tree look up of X will be faster?


texts: Is an anomaly free relation necessarily normalized?

texts:
A normalized relation can still suffer anomalies. Going the other way, which (if any) normal forms can be violated by a relation that's free from anomalies? If such exist,

For each such normal form that can be violated, what is an example of an anomaly free relation that violates the normal form?
Are there any normal forms that an anomaly free relation is guaranteed to comply with?


texts:
I've been running into problems whenever I'm importing a .sql file into my database running locally. After the import, no matter how large the DB was (last one was only 800kb), I'm signed out of phpmyadmin and MySQL stops working. The only way I know of to resolve this at this moment is to run the install process again for MySQL. After that everything is working fine again but it is driving me crazy. Especially if I have to do this multiple times each day. Anyone who know what's going on and how to prevent this?

To be more exact, this is what I get from applications running this database:

  MySQL server has gone away


texts: Schema changes in transactional replication

texts:
I have a publisher (SQL Server cluster)  in one box  and the distributor/subscriber together is on the other box. I have a requirement by the vendor that if there is a new build (schema/database changes)  then i will always have to intialise from a back up. I am confused about the steps to do... Below is my plan:

Delete/Drop Subscription
Disable/Delete  Publication
Reconfigure publication with initialise form back up
Restore from Full backup on subscriber and apply differential as is in simple mode
set up subscription using the last differential file.

Please advice is there any need to delete/disable the distribution database


texts: PostgreSQL: What is the best way to cast from from text to point[]?

texts:
I'm looking for a way to cast from a text value:

'(180,90),(-180,-90)'

into a point[] value:

{"(180,90)","(-180,-90)"}

What is the best way to do this?

One possibility that I can think of is to manipulate the string to be of this form:

'{"(180, 90)", "(-180,-90)"}'

Which I can then cast to point[] like so:

select '{"(180, 90)", "(-180,-90)"}'::point[];

Surely there's a better way? The solution I currently have is this:

select concat('{', replace(replace(<text>, '(', '"('), ')', ')"'), '}')::point[]


texts: Is there any limit to the number of databases you can put on one SQL server?

texts:
I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too great; we're hoping to have thousands, or even tens of thousands of customers.
Questions

Is there any practical limitation on the number of micro-databases you can/should have on one SQL Server?
Can it affect performance of the server?
Is it better to have 10,000 databases of 100 MB each, or one database of 1 TB?

Additional information
When I say "micro-databases", I don't really mean "micro"; I just mean that we're aiming for thousands of customers, so each individual database would only be a thousandth or less of the total data storage. In reality, each database would be around the 100MB mark, depending on how much usage it gets.
The main reason to use 10,000 databases is for scalability. Fact is, V1 of the system has one database, and we have had some uncomfortable moments when the DB was straining under the load.
It was straining CPU, memory, I/O - all of the above. Even though we fixed those problems, they made us realize that at some point, even with the best indexing in the world, if we're as successful as we hope to be, we simply can't put all our data in one big honkin' database. So for V2 we're sharding, so we can split the load between multiple DB servers.
I've spent the last year developing this sharded solution. It's one license per server, but anyway that's taken care of since we're using VMs on Azure. Reason the question comes up now is because previously we were offering only to large institutions and setting up each one ourselves. Our next order of business is a self-service model where anyone with a browser can sign up and create their own database. Their databases will be much smaller and much more numerous than the large institutions.
We tried Azure SQL Database Elastic Pools. Performance was very disappointing, so we switched back to regular VMs.


texts: PostgreSQL Auto Partitioning, StatementInvalid: PG::NullValueNotAllowed

texts:
I'm trying to import a huge address database from a set of CSV files. I following a tutorial to create postgres partitions automatically.

I create my database with eighteen fields. Every field is a varchar. The field I want to use for partitioning is "comune".

Following the guide I create this function

CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_comune TEXT;
      partition TEXT;
    BEGIN
      partition := TG_RELNAME || '_' || partition_comune;
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' (check (comune = ''' || NEW.comune || ''')) INHERITS (' || TG_RELNAME || ');';
      END IF;
      EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING patent_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

And then

CREATE TRIGGER address_list_insert_trigger
BEFORE INSERT ON address_list
FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

When I try to insert a record (I use activerecord orm but with psql is the same) I have en error:

PG::NullValueNotAllowed: ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function create_partition_and_insert() line 9 at EXECUTE
: INSERT INTO "address_list" ("id_egon", "regione", "provincia", "comune", "frazione", "procom_2016", "ist_sez", "ist_pro", "ist_com", "dug", "via", "presso", "colore", "num_civ", "esponente", "civ_extraurb", "liv_serv100_30", "linea_lunga") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)

Where is my fault?


texts: PostgreSQL: Simple IN vs IN values performace

texts:
We have queries with IN filters with long list of INT values
Sometimes, they running extremely slow, and I have found suggestion to use syntax
Field IN (VALUES(1465), (1478), ...

Instead of
Field IN (1465, 1478, ...

On some cases it helps, but on other it makes query running 1000+ times slower
So the issue is:

some queries much faster with simple IN
some queries much faster with IN + VALUES

Here is explain on case when it’s extremely slow:
->  HashAggregate  (cost=5.78..9.62 rows=385 width=4)
       Group Key: ""*VALUES*"".column1"
                   ->  Values Scan on ""*VALUES*""  (cost=0.00..4.81 rows=385 width=4)"

What is the right way to pass long INT values list to IN filter?
I am using PostgreSQL 13.1 on Ubuntu


texts: Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

texts:
I have a materialized view in my Oracle 10g database that I need to rebuild.  So I'm doing something like this...

drop materialized view ViewName;

CREATE MATERIALIZED VIEW ViewName
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT ....
FROM ....

This creation of the materialized view takes 12 minutes to run in our DEV schema and at one time ran 5+ days in our QA schema without completing.  There are no locks on the tables when running the script in the QA schema.  Both of the schemas arre also on the same table space.  The DB definition is exactly the same.

Any ideas on why it is taking so long to execute the DDL statement to create the MV in our QA schema?  Or any ideas what's preventing it from being executed?

Thanks


texts: Mysql Daemon cannot start after upgrade from 5.1 to Mysql 8

texts:
I preinstalled Oracle Enteprise Linux and it automatically installed Mysql Community 5.1. But I wanna update it to 8.
After update the daemon won't start and the /var/log/mysqld.log shows that

  [ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

Then I change the /etc/my.cnf file and add

  innodb_data_file_path = ibdata1:10M:autoextend

I tried to start it again and the log file said

  [ERROR] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

because if that, I moved the /var/lib/mysql/ib_logfile* to backup directory and try to start mysqld again.
BUT it still won't start because the log file said

  [ERROR] [MY-012960] [InnoDB] Cannot create log files because data files are corrupt or the database was not shut down cleanly after creating the data files.
  [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
  [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
  [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  [ERROR] [MY-010119] [Server] Aborting

Any help would be appreciated.


texts:
I would like SQL Server to search a phrase "centos 8" for example. The exact match.
But since number "8" is considered a stop word, it only searches for "centos".
Even if I use quotes
 select [KEY] FROM CONTAINSTABLE(myTable, myColumn, '"centos 8"')

How can I make it search for a phrase without disabling the stop words feature completely? (very useful in other cases)


texts: SQL Agent Jobs hanging at last Run date and time

texts:
For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done.
When I enable the jobs via TSQL it seems the schedule hangs (at last run date) and does not execute future schedules and LS DBs fall out of sync. Restarting the SQL Agent & manual start of the job resolves the issues ok.

SQL Server 2016 SP1.
Using msdb..sysjobs to disable/enable the job and
msdb..sysjobschedule to disable/enable the schedule

Anyone any ideas?


texts: How to convert MYSQL Query to support with Derby database

texts:
I used below mention query to cerate report in My Project with MYSQL with Java NetBeans. It works successfully.
SELECT CASE WHEN sortId = 1 THEN CAST(orderId AS CHAR(10)) ELSE '' END AS orderId, CASE WHEN sortId =
1 THEN orderName ELSE '' END AS orderName, itemName, itemUnit, itemRate
FROM
(
SELECT orderId, orderName, itemName, itemUnit, itemRate, ROW_NUMBER() OVER (PARTITION BY orderId
ORDER BY itemId) AS sortId
FROM
(
    SELECT orderId, orderName, itemName, itemUnit, itemRate, itemId
    FROM tblitem
    INNER JOIN  tblorder
        on tblorder.orderId = tblitem.orderRef
) orderItems
) orderItemsSorted
ORDER BY orderItemsSorted.orderId, orderItemsSorted.sortId

Later I wanted to use Derby Database in Netbeans. When I attempted to use that Query makes error message
Syntax error: Encountered "PARTITION" at line 4, column 81.

To avoid this problem can I use "The result offset and fetch first clauses" in Derby?
Thanks in advance to help me to convert or change this query to work in Derby.
I Below mentioned table creation and join Quires
TBLORDER (Order Table)
  CREATE TABLE TBLORDER (
  ORDERID INTEGER,
  ORDERNAME VARCHAR (50),
  PRIMARY KEY (ORDERID)
  );

TBLITEM (Item Table)
  CREATE TABLE TBLITEM (
  ITEMID INTEGER,
  ITEMNAME VARCHAR (50),
  ITEMUNIT VARCHAR (10),
  ITEMRATE DOUBLE,
  ORDERREF INTEGER,
  PRIMARY KEY (ITEMID)
  );

CREATE FOREIGN KEY
  ALTER TABLE NAME.TBLITEM
  ADD FOREIGN KEY (ORDERREF)
  REFERENCES NAME.TBLORDER (ORDERID)

JOIN QUERY
  SELECT ORDERID, ORDERNAME, ITEMNAME, ITEMUNIT, ITEMRATE
  FROM TBLITEM
  INNER JOIN  TBLORDER
  ON TBLORDER.ORDERID = TBLITEM.ORDERREF


texts:
I have some very basic knowledge in SQL which I mainly learn from some theoretical data science course. I would like to write a few lines of SQL just to experiment and train myself.

Can you please suggest a few free relational database for which I could write a few queries? Those databases can be about health, economy, climate change, social interaction, genetics, .... whatever seems to be of interest to most people.


texts: What does column family-oriented datastore mean in practice

texts:
I was reading this article about HBase. Within it, the storage format of HBase is described:

HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed by a key that you can use for lookup

So what does column family oriented mean? Is it the same as column oriented? In that case, how can it be both column family oriented and row oriented?

So row oriented storage is like this:

001:10,Smith,Joe,40000;
002:12,Jones,Mary,50000;
003:11,Johnson,Cathy,44000;
004:22,Jones,Bob,55000;

Column oriented storage is like this:

10:001,12:002,11:003,22:004;
Smith:001,Jones:002,Johnson:003,Jones:004;
Joe:001,Mary:002,Cathy:003,Bob:004;
40000:001,50000:002,44000:003,55000:004;

So how is data actually stored in HBase? Do you have example?


texts:
SQLite studio does not support square root function, so I have defined it in function editor as

proc SqrtA {num} {  set ans [expr {sqrt($num)}] return $ans }

with function nameSqrtA and one input argument, lang. tcl but when I am using it in query editor, I am not getting the square root.

For example I had tried for:

select SqrtA(9);

Can anyone please help me, in how to include square root function in SQLite studio?


texts: Add Gameweek Fixeture based on Datefield

texts:
having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in a 3 days timespan (some datediff to be used, can;t figure it out)

Initial Table

Date            HomeTeam     AwayTeam
2018-08-10     Man United    Leicester
2018-08-11     Bournemouth   Cardiff
2018-08-11      Fulham       Crystal Palace
2018-08-11    Huddersfield   Chelsea
2018-08-11      Newcastle    Tottenham
2018-08-11       Watford     Brighton
2018-08-11       Wolves      Everton
2018-08-12      Arsenal      Man City
2018-08-12     Liverpool     West Ham
2018-08-12    Southampton    Burnley
2018-08-18   Cardiff         Newcastle
2018-08-18     Chelsea       Arsenal
2018-08-18     Everton       Southampton

Desired Output

Date            HomeTeam     AwayTeam          RoundNumber
2018-08-10     Man United    Leicester         1
2018-08-11     Bournemouth   Cardiff           1
2018-08-11      Fulham       Crystal Palace    1
2018-08-11    Huddersfield   Chelsea           1
2018-08-11      Newcastle    Tottenham         1
2018-08-11       Watford     Brighton          1
2018-08-11       Wolves      Everton           1
2018-08-12      Arsenal      Man City          1
2018-08-12     Liverpool     West Ham          1
2018-08-12    Southampton    Burnley           1
2018-08-18    Cardiff        Newcastle         2
2018-08-18     Chelsea       Arsenal           2
2018-08-18     Everton       Southampton       2


texts:
I need to migrate my Event Notifications from current server to another one. I would like to generate that code, but I couldn't find an way to do that.

Try 1

I have tried reverse engineering from sys.server_event_notifications but it does not return a value that I can marry with sys.event_notification_event_typeid.

Try 2

I tried also a DAC to query system views, getting more columns from that, but no joy.

Try 3

Tried sp_helptext the objects, but it can't be found.

EXPECTED OUTPUT (for one event)

CREATE EVENT NOTIFICATION [CaptureDeadlocks]
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE N'DeadlockService', N'current database';

Any ideas?


texts: How to compare across rows in large table?

texts:
I need some help with a complicated query. Here are the dataset structures I'm dealing with. This is a MySQL database.

*transactions_table*

zip  | date
-----------------
1234 | 2010-05-01
4567 | 2010-03-10
8901 | 2010-02-25

*stores_table*

brand_id | zip
---------------
1        | 1234
2        | 4567
3        | 8901
4        | 2222
5        | 6666

*manager_table*

brand_id | name
---------------
1        | jane smith
1        | joe smith
2        | ellen katz
3        | foo fuzz
4        | bar bazz

My goal is to get all the transactions in stores that are managed by managers whose names do not match for a regex on the final word of the name column. The number of managers per store can be 1 or 2; it's inconsistent.

I can join all the data together, but then I can't figure out how to perform that last filter step. Here's what I have so far:

SELECT
manager.name, store.zip, txn.date
FROM
transactions_table txn
JOIN
stores_table store
  ON
  txn.zip = store.zip
JOIN
manager_table manager
  ON
  store.brand_id = manager.brand_id

Which gives me a derived table that looks like:

name       | zip  | date
------------------------------
jane smith | 1234 | 2010-05-01
joe smith  | 1234 | 2010-05-01
ellen katz | 4567 | 2010-03-10
foo fuzz   | 8901 | 2010-02-25

But I want to exclude the first two rows, because they represent a transaction at a store managed by people with names that match on their last name.

I want the dataset to look like:

name       | zip  | date
------------------------------
ellen katz | 4567 | 2010-03-10
foo fuzz   | 8901 | 2010-02-25

A couple notes:

I realize the domain this represents seems a little weird, but it's a simplified example for the sake of trying to nail down this query
scale is an issue. the real manager_table is about 38MM rows and the real stores_table is about 18MM, so I'd like to avoid a join on a derived table is possible


texts: Display equal ranking if count is the same

texts:
I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For example, if the the first 4 have the same article count then i want them to all rank as 1 and then for the 5th person to rank as 5. The following query outputs as shown below:

SELECT @rownum:=@rownum + 1 as Rank,
   subq1.*
FROM ( SELECT Uni.University_Name, COUNT( * ) AS ArticleCount
FROM Author_University AS AU, University AS Uni, Articles AS Art
WHERE Art.Year_Published >= '2010'
AND Uni.University_ID = AU.University_ID
AND Art.Article_ID = AU.Article_ID
GROUP BY University_Name
ORDER BY ArticleCount DESC
LIMIT 0 , 25
  ) subq1,
(SELECT @rownum := 0) r


texts: SQL Outer Join while conditioning on an extra column


texts: Efficient way to get data for each date range intervals using single query | MongoDB

texts:
I have collections about products and purchases. I need to get statistics for each time interval.
I need following:

Name
quantity (date 1 range)
quantity (date 2 range)

Name One
500
400

Name Two
251
450

Name Three
444
600

I have following collections:
//Products
{
 name: {
    type: String,
    required: [true, "The prodcut name is required"],
    unique: true,
}
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
  },
}

//Orders model scheme
 {
   product: {
    type: Schema.Types.ObjectId, ref: "Products",
},
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
},
quantity: {
    type: Number,
    default: 0,
},
orderDate: {
        type: Date,
        default: Date.now, //new Date(),
    },
  }

And I have the following aggregate function:
    OrdersModel.aggregate(
        [
            { $match: { orderDate: { $gte: new Date( sdt ), $lte: new Date( edt  ) } } },
            {
                $group:
                {
                    _id: "$product_name", //{ name: "$name"},
                    totalQuantity: { $sum: "$quantity" },
                }
            },
            {
                $project: {
                    _id: 1,
                    qty: "$totalQuantity",
                    //...
                }
            }
        ]
    )

This query is working, but only for one time interval. But I need to get results for multiple time intervals.
I'm new to NoSQL, I've tried retrieving data from one or more-time intervals, but I couldn't find out how to achieve this. How can I solve this problem?
I need to get the sales total Quantity of a product at different time intervals.
2010-01-01 to 2012-01-01
2015-01-01 to 2017-01-01
...

Time intervals can be dynamic. There can be a minimum of 1 and a maximum of 4 time intervals.


texts: What is the difference between SQL Server 2012 SP4 vs SP3 with CU-10?

texts: Sorry, may be very stupid question but what is the difference between SQL Server 2012 SP4 and SQL Server 2012 SP3 + CU-10?


texts: Periodic connection failure on SQL Server 2012 AlwaysOn cluster

texts:
Since last wednesday (so a we're dealing with this for a week now) we're getting periodic connection problems on our production database cluster. (An alwayson cluster running on windows 2012, three machines, two sync and one async).

When it starting we noticed the following two messages in the SQL Event log that have been repeating ever since when the problem arise;

Error: 18456, Severity: 14, State: 46.
Login failed for user 'xxx' Reason: Failed to open the database 'xxx' configured in the login object while revalidating the login on the connection. [ CLIENT: xxx.xxx.xxx.xxx]

We've been trying just about everything and are reaching desperation currently. The things we've tried;

Restart our firewalls
Restart our switches
Failover and reboot the nodes (one by one)
Turn off the async node and see if anything changes
Turn off all but the primary node to see if anything changes
Replace the switch
Remove some of the network cables and run on one NCI
Remove the teaming from a server and failover to this server

Non of this seems to do anything.

The errors connecting code receives vary from a Timeout when beginning a transaction to a Timeout executing a query. (A query that does not show up in SQL Profiler when looking for queries running longer than 5000ms).

The strangest error however to me is the following;

System.Data.SqlClient.SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=14991;

Now I in no way consider myself a real DBA, hell I manage the software development department and am actually looking to hire one currently... However in my experience and with my limited knowledge I feel it is very odd to not see any of these queries that are timing out in my profiler...

As a matter of fact, I see no blocking queries or queries that raise my suspicion in any way...

Another thing to add here is that it seems to come in waves... Sometimes everything is fine for 15-25 minutes and then it's bad for ~2-5 minutes. This happens the whole day as well, so not during peak hours or when our normal processes run. (Basically I'm seeing errors from our API's trying to reach the database, very few processes run @ night and non of them this periodically).

It's the whole network experiencing this as well, meaning I can rule out code since we didn't publish any changes nor do we connect to the database on the same fashion. (Some through NHibernate, some ADO.NET, some old ADO)

The biggest changes happened on the network side but our sys admin seems adamant it can't be since non of the changes should affect this and they can't see anything on the switches that are suspicious.

Does anyone here have any idea what we could try / should look at next?


texts: Reverse dictionary (key to sequence of values) search with NoSQL database

texts:
Suppose we have a dictionary that maps keys to sequences of values - for example:

potato: A, B, C
tomato: B
lettuce: C
carrot: A, C
cucumber: C

I would like to perform queries such as:

query A, B, C returns potato
query C returns lettuce, cucumber (order shouldn't matter)

I understand that the reverse dictionary lookup could be done with normalized relational databases (as suggested in this post). However, my intuition is that a non relational database could better fit this problem, like a graph. Additionally, I'm looking for something read-optimized.

Is there a NoSQL database that fits this kind of problem? Where should I direct my search for such an implementation?


texts: How do I retrieve information about a SQL Server Agent job scheduled against databases on SQL server 2005?


texts: Increase in number of temp files being generated after increasing checkpoint_timeout

texts:
I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config.

I changed the checkpoint_timeout from the default value of 300 to 1800, i.e. checkpoints are triggered every 30 mins instead of every 5 mins.
I also changed the the autovacuum_scale_factor from 0.3 to 0.1 and autovacuum_threshold from 100 to 10000.

I can see in pgbadger analysis of the log files that autovacuuming is happening slightly more often and checkpoints are triggering every 30 minutes. However, I also see a change in the graph of temp files being created. After this change, more number of tempfiles are being created.

Isn't that a bad thing? Why is that happening and should I be worried?


texts:
Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?

The RDBMS that is used is SQL Server.

In fact, after creating a unique index (not null), I can create a foreign key constraint on an other table pointing to the column that is indexed without having to declare that this column is "the primary key" ... so, what is the purpose of it ? Is it a question of esthetics ?


texts: How can I see the current Database Mail configuration?

texts:
Our SQL Server (2008) instance is configured to send mail and everything is working correctly, but I can't figure out how to view the existing configuration, in particular the SMTP server.

From SSMS I can only start the configuration wizard, and I can't find anything online - plenty of info on how to set it up but nothing on how to view the current settings.

How can I view the existing settings?


texts:
I made this solution but it continues not returning any value If the user not exists.

select
    case when TypeId = 1 then 1
         when TypeId = 0 then 2
         when count(1) < 1 then 3
         end
    from Type
    where mail = 'name@test.com'
    group by Type


texts:
I'm new to oracle environment. I want to know the difference between service and listener in oracle?

I'm just doing my assignment on accessing an oracle database over a network.


texts: New to CTE - How to do a count after making table unique

texts:
I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries.

I have the below table in SQL. I'm trying to write a CTE to give me one line of data per day for each person. Where a person has more than one location ('where' field) for a day - I need to exclude all lines for that day. If a person has multiple lines for one day but the location is the same place - I want to only keep one line of this data.

This is an example of the original table:

This is an example of the result that I'm looking for (note - I don't need the ID field in the result).

Below is the definition and insert statement to the original dataset:

CREATE TABLE Tbl1
(`ID` int, `Person` varchar(5), `Date` varchar(10), `Where` varchar(6));

INSERT INTO Tbl1
(`ID`, `Person`, `Date`, `Where`)
VALUES
(1, 'Laura', '17/10/2019', 'School'),
(2, 'Laura', '18/10/2019', 'School'),
(3, 'Laura', '19/10/2019', 'School'),
(4, 'Laura', '19/10/2019', 'Park'),
(5, 'Laura', '20/10/2019', 'Home'),
(6, 'Laura', '21/10/2019', 'School'),
(7, 'Laura', '21/10/2019', 'School'),
(8, 'Laura', '21/10/2019', 'Home'),
(9, 'Tom', '17/10/2019', 'Home'),
(10, 'Tom', '18/10/2019', 'Home'),
(11, 'Tom', '18/10/2019', 'Home'),
(12, 'Tom', '19/10/2019', 'Home'),
(13, 'Tom', '19/10/2019', 'School'),
(14, 'Tom', '20/10/2019', 'Home');