texts:
When I run

ALTER DATABASE EMS102013 SET SINGLE_USER;

DBCC CHECKDB (EMS102013) WITH NO_INFOMSGS, ALL_ERRORMSGS

shows message

  Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was
  detected while collecting facts. Possibly tempdb out of space or a
  system table is inconsistent. Check previous errors.

how can I fix this problem ?


texts: Clearing Log Shipping config Secondary server inaccessible

texts:
We are using SQL 2012

Our Secondary server in a log shipping configuration went offline never to come back (the VM was permanently deleted before log shipping configuration was updated).

On the Primary server, I am trying to clean up the LS configuration but everything I've tried times out and/or gives me errors about having configuration still existing.

I have done the following:

Deleted the LS jobs from the primary server (Backup and Alert)
Run the Stored Procedure "sp_delete_log_shipping_primary_secondary" with success
Attempted to run the Stored Procedure "sp_delete_log_shipping_primary_database", this fails.

The error with sp_delete_log_shipping_primary_database is as follows:

  Msg 32011, Level 16, State 1, Procedure sp_delete_log_shipping_primary_database, Line 51
  Primary Database <"DB name"> has active log shipping secondary database(s). Drop the secondary database(s) first.

If I try to use the SQL management studio GUI, it tries to get me to log into the Secondary server (no longer exists) and times out/errors when it cannot communicate with it.

How do I get log shipping configuration cleaned up on the Primary server, knowing that the Secondary one doesn't exist anymore?

I have flexibility to do a backup/restore of the database during troubleshooting, if needed.


texts: What is the equivalent of SELECT  in Flux Query Language?

texts:
What would be equivalent flux query for SELECT address, name FROM addresses ? (I am referring to FluxQL, the new query language developed by InfluxData)

I didn't find a clear answer to this in the limited Flux Documentation present. Flux documentation says that filter() function is the equivalent of both SELECT and WHERE clauses, but all examples given are equivalent of WHERE clauses, nothing on SELECT.

Edit: An answer posted below gives the equivalent query in InfluxQL, which is not what I am asking for. Yeah I know, FluxQL, InfluxQL, it can get confusing.

These are the documentation for FluxQL for better reference:

https://docs.influxdata.com/flux/v0.50/introduction/getting-started

https://v2.docs.influxdata.com/v2.0/query-data/get-started/


texts: SELECT and UPDATE in a single query while updating selective fields of duplicate records only

texts:
I have a MySQL table named Contacts_table with, say, the following contents:

ID          NUMBER          NAME            CITY
001         2222222222      John Doe        Los Angeles
002         3333333333      Rich Malfoy     Houston
003         4444444444      Harry Potter    New York
004         5555555555      Billy Bones     Boston
005         6666666666      Joe Sanders     Chicago

*ID: Primary key; auto-increment

*NUMBER: Unique key

And I am using PHP to read a comma-separated text dump of values for these fields, parse those values, and add them to the table. Say, a sample text dump has the following contents (shown here as table for clarity):

NUMBER          NAME            CITY
1234567892      Earl Grey       Salem
3333333333      Rich Malfoy     Sacramento
7656453248      Bill Gates      New York
5555555555      James McGill    Boston
6666666666      NULL            Baton Rouge

Now, as you can see, there are some entries (based on NUMBER) in my TXT that do not exist in my table, i.e. 2nd, 4th, and 5th records. Then there are others that already have a corresponding entry in the table but with possibly different values on other fields. What I want my code to do is update new entries (with NUMBER values 3333333333, 5555555555, and 6666666666 in this example). As for other entries, the code would only update those fields where the TXT dump has a non-NULL value. Thus, this is what the updated table would look like:

ID          NUMBER          NAME            CITY
001         2222222222      John Doe        Los Angeles
002         3333333333      Rich Malfoy     Sacramento
003         4444444444      Harry Potter    New York
004         5555555555      James McGill    Boston
005         6666666666      Joe Sanders     Baton Rouge
006         1234567892      Earl Grey       Salem
007         7656453248      Bill Gates      New York

Like I said, wherever there's an existing entry, the fields have to be checked for values and only those fields are updated for that record which have a non-NULL values in the corresponding TXT dump.

I have a whole bunch of INSERT ON DUPLICATE KEY UPDATE queries (basically, one for each entry in the TXT dump) which is going to be a lot if the dump consists of hundreds of records. Is there any way to build a single query to handle this task in a single transaction?


texts: Error connecting to the server:FATAL:couldnot open file"base\12135\11946":No such file or directory

texts:
I have a issue from PostgreSQL 9.4 .The issue is given below:

  An Error has occurred: Error connecting to the server:FATAL:could not
  open file"base\12135\11946":No such file or directory.

This issue is in Windows 7.


texts: TDE Enabled DB Always Going Into "Recovery Pending" on Secondary Node

texts:
I have a Windows Failover Cluster set up with SQL 2016 Enterprise SP2. TDE was implemented on a DB, on node1 the DB is able to start without issues. However, when a failover occurs, on node2 the DB always goes into "Recovery Pending" state and has to be failed over back to node1 in order to start correctly.

Error: 15581, Severity: 16, State: 7.
Please create a master key in the database or open the master key in the session before performing this operation.

I have checked the certificates and everything seems to match up. Not sure what is happening that it only seems to be able to start on node1 and not on node2.


texts: Shared Location Options in MS SQL Server Data Migration Assistant

texts:
For studying purposes I want to upgrade AdventureWorks sample database from MS SQL Server 2017 Express edition to Developer. I am using DMA for that, and can't understand the meaning of Shared Location Option. What I have to specify in that field in order to continue my migration? What's the use of backupshare?

Thanks for your time!


texts: MySQL transaction size - how big is too big?

texts:
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.

There are many aspects, and the imports may yield anywhere between 100k-1mil+ records.
This equates to a payload ranging from several MB to a few hundred MB of data.

I know temp tables are another option - but this method seems so handy.

Are there any caveats to be aware of regarding this kind of practice with a large amount of data manipulation between commits? (Outside of the typical write/indexing load burst once committed)


texts: Quickly restore test/QA SQL Server database

texts:
My company is releasing new mission critical software.
For thorough QA, we are doing database comparisons.

Our process is:

Restore full backup.
Test code.
Compare.
Repeat.

This database is relatively large, and step 1 can take up to 2 hours.

Is there any method someone could suggest that would speed this up?  We are making minimal changes when the new code is being run and compared.  I'd like to quickly revert back to before the test were run.

Thanks in advance!


texts: How can I find out if a SQL Function can be inlined?

texts:
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will actually be slower.

That's interesting though because I didn't even know to_char() wasn't IMMUTABLE. Moreover, I didn't know that IMMUTABLE would ever make anything slower. My question is now, how do I find out if a function marked as IMMUTABLE is being inlined, or not?


texts: PostgreSQL lost connection during massive index operation? What exactly happened?

texts:
Using PostgresQL 9.4, Ubuntu 14.04, and 32 GB RAM (with modified settings in postgresql.conf file; I think the highest allocation is 16GB).

I ran a ts_vector creation on a text column in a table containing a little more than 1.8 billion rows. It was successful.

However, running an index on the new vector column failed in a strange way.

database_name=# create index tsv_column_idx on table_name using gin(tsv_column);

The query ran for about 56 hours in a screen session, and then I got this message:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Trying another query resulted in:

!> <random query>;
You are currently not connected to a database.

Why would the connection have been lost? Nobody else connected to the database or made updates to the server. Is this an issue with memory allocation?

I ran dmesg | grep -iC 3 "kill" and nothing came up.

It seems like there was some kind of shutdown error... no idea how that occurred though.

2015-10-09 14:55:32 PDT [1506-2] LOG:  received fast shutdown request
2015-10-09 14:55:32 PDT [1506-3] LOG:  aborting any active transactions
...
2015-10-09 14:55:32 PDT [3803-4] postgres@database FATAL: terminating connection
 due to administrator command
2015-10-09 14:55:32 PDT [3803-5] postgres@database STATEMENT:  create index ...;


texts: dbcc memorystatus - buffer pool target on VM higher than max memory

texts:
I'm doing some digging into potential memory/configuration problems on a sql server 2012 SP2. I stumbled upon something that seems a bit strange in the performance counters as well as the dbcc memorystatus information.
here's the info from the buffer pool section of dbcc memorystatus:

If i'm reading things correctly the value is the number of 8 KB pages. The server which is a VM has 16 gb of memory total and SQL server is assigned a min/max value of 12GB.
The "Target" value of 32751616 gives me about 262GB which just so happens to be the total amount of memory in the VM host. If i check the "target pages" performance counter on the server i see the same number. I'm guessing this hints of the VM not being configured properly but what i'm really wondering is how the sql server will handle this. Will it impact performance / will the server try to commit more memory than is actually available?

Looking at the memory manager things seem to be looking more normal:

I've been searching the web for information about target buffer pool exceeding max memory but I haven't been able to come up with much so any input is much appreciated.

Edit. Adding a picture of the performance counter.


texts: Monitoring multiple Oracle databases on a single screen

texts:
We would like to monitor database activity of multiple Oracle databases on a single screen (10g and 11gR2), divided by the number of databases.

Some tools are offered here: Tools to monitor Oracle Database Performance on Standard Edition, but we couldn't figure out if these products support such an activity. Currently, we are trying to merge and align Enterprise Manager's graphs in a html page using frame tags.


texts: Perform group by with random group expression (randomly partitioning rows) MYSQL

texts:
Let's say I have a table (with millions of rows)

cat1|cat2|value
A   |   B|   10
...

and I want to group the data by cat1 and also split the grouping into 10 (at least roughly) equal partitions of the data.

The purpose of the random grouping is for the machine learning technique of cross-validation.  (It's similar to splitting subjects into a control group and experimental group.)

What I would imagine to use is the following:

select cat1, r, sum(value)
from t
group by cat1, floor(rand()*10) as r

which doesn't work.

One way which I believe would work would be simply to insert a new column with rand into my table and then group appropriately on that.

Is there any way of doing this grouping otherwise?


texts:
slava=# explain (analyze) SELECT * from pending_posts WHERE user_id <> 1456
slava-# ;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pending_posts  (cost=0.00..17906.00 rows=999994 width=14) (actual time=0.021..231.803 rows=999994 loops=1)
   Filter: (user_id <> 1456)
   Rows Removed by Filter: 6
 Planning time: 0.108 ms
 Execution time: 289.845 ms
(5 rows)

slava=# explain (analyze) select * from pending_posts where user_id <> 1456 order_by user_id;
ERROR:  syntax error at or near "order_by"
LINE 1: ...select * from pending_posts where user_id <> 1456 order_by u...
                                                             ^
slava=# explain (analyze) select * from pending_posts where user_id <> 1456 order by user_id;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using p_i on pending_posts  (cost=0.42..50103.17 rows=999994 width=14) (actual time=0.033..899.520 rows=999994 loops=1)
   Filter: (user_id <> 1456)
   Rows Removed by Filter: 6
 Planning time: 0.141 ms
 Execution time: 953.982 ms
(5 rows)

Given that user_id is integer indexed with btree, should't it be faster to sort and then select using tree search?


texts: Syntax error when trying to add columns to table in DBeaver (postgresql)

texts:
I've tried this a few different ways but every time I get a syntax error when trying to add columns to a table.

I'm new to databases so it's probably something basic that I'm doing wrong, but I just don't see why this isn't working.

Any input would be appreciated.

P.S. at the end of the query line there is a ";"... You just can't see it in the screenshot.

So far I've tried changing "add columns" to "add column" and just "add" followed by the columns. I'm aware I can just use the GUI to copy columns in DBeaver but I'd rather get this SQL working.


texts: row count incorrectly displayed as 0 in redshift

texts:
I have a table in redshift with columns user_id and some_property, neither of which are unique.

The following query returns 0:

SELECT COUNT(DISTINCT user_id) FROM table
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM table WHERE some_property = 1)

Suggesting that for every user_id in the table, there is an entry with some_property=1.

However if I run

SELECT COUNT(DISTINCT user_id) FROM table

then it returns a number greater than

SELECT COUNT(DISTINCT user_id) FROM table WHERE some_property = 1

To me this is a logical contradiction. What am I missing here?


texts:
1st setup was a DB slave that was used by customers. That was sufficiently fast.
2nd setup (now) is replication of a DB slave that is just used by me. The current setup is slower on query execution (even though I don't have to share CPU and memory) and the theory is that this is because the DBMS is not "warm" by use but f.x. has to reload the index every time I use it. This leads to weaker performance. The conclusion would be to run scheduled queries that keep the DB "warm".

I would like to know three things which are directly connected:

What is the primary reason for this effect? Is it because after every small write access to the slave which gets replicated onto my server, indexes are loaded into the memory first before a query is executed? My guts tell me there something more complex going on.
How would a query have to look like that optimally uses the DB (scheduled) to keep it warm and ready? I am aware of this post: MySQL warm procedure - but I am not so much interested in a generic query but in understanding the logic behind it.
What determines how frequently such a keep-warm-query (or set of) should be run?

SHOW VARIABLES LIKE "%version%";

innodb_version          5.6.13-rel61.0
protocol_version        10
slave_type_conversions  -
version                 5.6.13-rc61.0
version_comment         Percona Server (GPL), Release 61.0
version_compile_machine x86_64
version_compile_os      Linux


texts:
I have an odd encryption and decryption problem. Encryption worked fine in SQL 2008 R2, we were using TRIPLE_DES_3KEY. This has been deprecated in SQL 2016, so we were are testing out AES_256. My original string keeps getting cut off, not sure what it's doing or how else to explain. I am sure I'm missing something, but not sure what. Detals below. Thanks!

My Symmetric Key:

CREATE SYMMETRIC KEY SK03
WITH
    ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';

My Table:

CREATE TABLE [dbo].[_EncryptionTest](
    [OriginalValue] [varchar](max) NULL,
    [Encryptedvalue] [varbinary](8000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My Stored Procedure:

CREATE PROCEDURE [dbo].[_usp_EncryptionTest]
    @InputString varchar(MAX),
    @InputString2 varchar(MAX)

AS
BEGIN
    SET NOCOUNT ON;

    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'

    INSERT INTO _EncryptionTest
        (OriginalValue, Encryptedvalue)
    VALUES (
        @InputString
        ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
        )

    CLOSE SYMMETRIC KEY SK03;

END

My Test SQL:

EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'

As you can see from the image below, the data gets updated to my table.

My Decryption SQL:

OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'
SELECT
    [OriginalValue]
    ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))
FROM
    _EncryptionTest
CLOSE SYMMETRIC KEY SK03;

The image below should show the results with the decryoted value cutoff.


texts: Mariadb with more than one node each node will have recent 1 year data

texts:
I am creating a saas which will then have time based data every seconds for example.
I want horizontal scalling when data reach its limit of 1 year.
after 1 year old data should be shift to another server so only 1 year data is available on 1st node
2nd will have 1 year old data and 3rd will have 2 years old data so on.

let me know which db tool I should use to achieve this and how?
or
let me know better solution if any


texts: Make a column for each count(*) in a day of the week

texts:
I have a table of people who are doing piece work. Every entry in the table has a user-ID, name, and a Date for each report they enter.

I need to make a weekly report of what these people have done per day starting with Sunday

How do you group by a week starting Sunday?
How do you make a column for each COUNT(*) per day?

t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-28
t0001, Tod, 2015-6-28
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-28

This is what I'm looking for.

NAME | S | M | T | W | R | F | S | TOTAL
----------------------------------------
TOD  | 2 | 5 | - | - | - | - | - | 7
BEN  | 1 | 2 | - | - | - | - | - | 3


texts:
We have a question related to a DB query :

CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned NOT NULL,
  `name` varchar(60) NOT NULL,
  `updated_action_at` datetime(3) DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY `action_empId_fk` (`emp_Id`),

  CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,

) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1

CREATE TABLE `ActionAssignedTo` (
  `action_Id` int(10) unsigned DEFAULT NULL,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  KEY `actionassignedto_emp_id_foreign` (`emp_Id`),
  KEY `actionassignedto_action_id_foreign` (`action_Id`),
  CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `mobile_Number` varchar(15) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)

We have the following query :

Select     notes,
           Action.id,
           AssigneeName.name,
           ActionAssignedTo.emp_Id
from       Employee
inner join Action
on         (Action.emp_Id = Employee.id
and        Action.emp_Id in (select Employee.id
                             from Employee
                             where Employee.vendor_Id = 1))
inner      join ActionAssignedTo
on         Action.id =  ActionAssignedTo.action_Id
inner join Employee as AssigneeName
on         ActionAssignedTo.emp_Id =  AssigneeName.id
where      Action.emp_Id = 4

Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :

(columns from the query)

notes                       | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id
1. Finish Designing Queries | 10        | John              | 2
2. Finish Designing Queries | 10        | Jack              | 5

If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID  --> is there a way we can merge them something like

1. Finish Designing Queries |10         | { John : 2  , Jack  : 5}

Basically records being merged into one.


texts: Restore a database with compression on a non-enterprise edition SQL Server

texts:
Is it possible to restore a compressed an object from backup without the Enterprise Edition?

I'm getting the following error after a RESTORE DATABASE operation.

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 2
Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

I can think of two solutions but I don't know whether it's possible or how to do it if so:

Decompress the object from the backup without Enterprise Edition using some sort of utility.
Remove the offending object from the backup using some sort of utility so I can use the other non-compressed data. (Not sure how it would handle references such as foreign-keys though.)

Would it be possible to somehow do this in SQL Server? It's refusing to load the entire database even though not everything is compressed. Is there a way to force SQL Server to attempt to load the other tables?


texts:
I have a huge set of employee ids (1000 to 10000) which I can't pass through IN clause. I don't want to use a Global Temporary table since I don't have write access in the DB (only read access).

What is the best way to pass the data to the query?

SELECT EMPLOYEE_NAME,AGE,SALARY
from EMPLOYEE
WHERE EMPLOYEE_ID IN (???)


texts: The database is in emergency mode and I have tried fixing it. But when I used the statement DBCC CheckDB (TestDB, REPAIR_ALLOW_DATA_LOSS) the log was not rebuilt. The log file is not present now. How can i bring the database online?


texts: SQL Server - creating a clustered index in a large heap - should I add a column?

texts:
I'm going to add a clustered index to a fairly large table:

over 7 million records
106 columns
no PK
no other indexes.

There is an Id column which could potentially be used for the index key, but it's nvarchar(18) and not unique (although there are no duplicate values there).

I'm however reluctant to use it because of its datatype (performance impact) and have been thinking of adding an int identity(1,1) column and use it as the clustered index key.

Which solution would you rather recommend?
TIA


texts: Tools to monitor Oracle Database Performance on Standard Edition

texts:
Here is what I am looking for:

Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation to management.

If you have used the tool, I would be interested in pros and cons you have discovered.  I can come up with a list, but the insight of actually having used the tool is worth far more.


texts: Transaction Log maintenance on Mirror database

texts:
SQL Server Version: 2008 R2 Enterprise SP2

I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect.  We have a single production instance with 3 databases each being mirrored offsite to a DR instance.

While looking at the DR instance I noticed that the LDF files were huge, over 35GB for the heavily used databases.

I understand that this is probably due to the mirror databases being in Full recovery mode and that the logs have never been backed up, they will just continue to grow until running out of drive space.

We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?

At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?

Again this is transaction log maintenance recommendations on the MIRROR database.

Thanks for any input


texts: How to count occurrences of date only in a field that contains datetime data?

texts:
I have a MariaDB database/table that contains log entries. For the purpose of example and broader help to other users, let's call the table FOO_TABLE and the field FOO_TIMESTAMP.

The FOO_TIMESTAMP field had a data type of datetime.  Examples of content include:

select FOO_TIMESTAMP from FOO_TABLE limit 10;
+---------------------+
| FOO_TIMESTAMP       |
+---------------------+
| 2013-01-21 12:28:17 |
| 2013-02-06 08:54:05 |
| 2013-02-06 08:59:55 |
| 2013-02-06 09:05:46 |
| 2013-02-06 11:47:38 |
| 2013-02-06 12:14:04 |
| 2013-02-06 12:15:05 |
| 2013-02-06 12:42:45 |
| 2013-02-06 13:01:49 |
| 2013-02-06 13:20:50 |
+---------------------+
10 rows in set (0.00 sec)

I want to get a count of how many rows were created per day.  I'm not interested in the specific time.  So for example, I'd like the following information from the data set above:

2013-01-21     1
2013-02-06     9

I've searched prior stack exchange sites and see plenty of references to the GROUP BY option but I'm not sure how to only filter it on the date... and not have it look at the full time too.

I can get the date by itself by running select date(FOO_TIMESTAMP) from FOO_TABLE; but I still can't get a count of the occurrences of that.

I thought of trying something like select date(FOO_TIMESTAMP) as DATEONLY from FOO_TABLE group by DATEONLY;  but that still doesn't give me count of date occurrences.

What am I missing here?


texts: MySQL Group Replication starts master node with super_read_only

texts:
I am trying to set up MySQL group replication. Only problem is, that when I try to start replication group, It starts with super_read_only.
Here the configurations in my.cnf file
[mysqld]

max_binlog_size = 4096
default_authentication_plugin     = mysql_native_password

log_bin                           = mysql-bin-1.log
enforce_gtid_consistency          = ON
gtid_mode                         = ON
log_slave_updates                 = ON
binlog_checksum                   = NONE

plugin-load-add                   = group_replication.so
plugin-load-add                   = mysql_clone.so
relay_log_recovery                = ON
transaction_write_set_extraction  = XXHASH64
loose_group_replication_start_on_boot                    = OFF
loose_group_replication_group_name                       = 74fe8890-679f-4e93-9169-a7edfbc1d427
loose_group_replication_group_seeds                      = mysql_cluster_mysql0_1:3306, mysql_cluster_mysql1_1:3306, mysql_cluster_mysql2_1:3306
loose_group_replication_single_primary_mode              = ON
loose_group_replication_enforce_update_everywhere_checks = OFF
bind-address = 0.0.0.0

instances are run inside docker, that's why group seed addresses has these hostnames.
Also here the procedure for running master instance.
DELIMITER $$

USE `db`$$

DROP PROCEDURE IF EXISTS `set_as_master`$$

CREATE DEFINER=`root`@`%` PROCEDURE `set_as_master`()
BEGIN
  SET @@GLOBAL.group_replication_bootstrap_group=1;
  CREATE USER IF NOT EXISTS 'repl'@'%';
  GRANT REPLICATION SLAVE ON *.* TO repl@'%';
  FLUSH PRIVILEGES;
  CHANGE MASTER TO MASTER_USER='root' FOR CHANNEL 'group_replication_recovery';
  START GROUP_REPLICATION;
  -- SELECT * FROM performance_schema.replication_group_members;
END$$

DELIMITER;

After running CALL start_as_master; in Sqlyog, process stucks on below lines.
'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.

2021-03-03T21:47:55.934818Z 8 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'

2021-03-03T21:47:55.935929Z 9 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'

Why does it run with super_read_only=ON?
Is there anything I miss during configuration or running script?
MySQL version is 8.0.23.


texts:
I have used MySQL and SQL server all of my professional career.

The company I will be working for uses PostgreSQL.

Can someone who was/is in a similar situation please give me some insight on what the biggest differences are and what types of client tools I would use to connect to and manage the database?

Thanks


texts: Minimal grants for readonly single-table access on PostgreSQL

texts:
Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL.

Let's assume that these commands are executed on login with sufficient privileges (i.e. postgres login in default install).

CREATE ROLE user_name NOSUPERUSER NOCREATEDB
NOCREATEROLE NOINHERIT LOGIN PASSWORD 'pwd' VALID UNTIL 'infinity';

Now I want to grant select on table tab_abc on database db_xyz, so here it goes (database db_xyz is selected as current via PgAdmin or something like that):

grant select on tab_abc to user_name;

The question is: is this sufficient or there should be more grants (database connect, usage maybe)?

These commands seem to work for me, but my default installation has default security settings. What additional grants should I add if server admin configured stronger security?

It seems that I don't need to grant connect nor usage - are that implicite while granting select? Is it always so?


texts:
We have a very old PeopleSoft HR system running on DB2 8.x.  Numerous times a day we get the following errors in the db2diag.log file.

 2019-11-04-01.22.24.012784-300 E16054C794         LEVEL: Error (OS)
 PID     : 229596               TID  : 772         PROC : db2hmon 0
 INSTANCE: hrprod               NODE : 000
 FUNCTION: DB2 UDB, oper system services, sqloPdbConnectSocket, probe:20
 MESSAGE : ZRC=0x810F0019=-2129723367=SQLO_CONN_REFUSED "Connection refused"
 CALLED  : OS, -, connect
 OSERR   : ECONNREFUSED (79) "Connection refused"
 DATA #1 : String, 32 bytes
 Unable to connect socket on path
 DATA #2 : socket, 4 bytes
 7
 DATA #3 : sockaddr, 39 bytes
 0x303B4F38 : 0001 2F75 7372 2F75 7365 7273 2F64 6232    ../usr/users/db2
 0x303B4F48 : 6173 2F64 6173 2F74 6D70 2F64 6173 6E61    as/das/tmp/dasna
 0x303B4F58 : 6D65 6470 6970 65                          medpipe
 DATA #4 : File name, 37 bytes
 /usr/users/db2as/das/tmp/dasnamedpipe

We're not sure what's causing the error but the reference to DAS in the error has me thinking the instance is trying to connect to the DAS server, or maybe the other way around.  The DBA who was responsible for the system said the error has appeared for a long time and he couldn't figure out the cause but it had no noticeable negative implications on functionality.  He retired last week.  I'd like to fix the issue if possible.   I'm not familiar with 8.4 and it's use of DAS other than DAS being needed to administer multiple instances, using a GUI, on the same server (there's only one instance on the server).

Any help tracking this down greatly appreciated. :)

Richard


texts: SHOW CREATE PROCEDURE procedure_name not showing the procedure content


texts: Access view based on table in another database without account in that other database

texts: I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account in database2. How can I solve this issue? I don't want to create an account in database2.


texts: DBeaver Error 1146 (42S02) on SQL database restore- Table 'acumatica.AUNotification' doesn't exist

texts:
I am trying to restore a database dump from a file for an online SQL course I'm taking into DBeaver. When I restore it, however, it runs for a little bit and then I get the error message

ERROR 1146 (42S02) at line 4037: Table 'acumatica.AUNotification' doesn't exist

I've tried:

Manually dropping the table via the mysql command line, and then copying the CREATE TABLE statement from the .sql dump file to manually create it.
Reinstalling SQL
Using sqldump except I think it just dumped the existing file.
Doing mysql -u root -p --database=acumatica < dump-acumaticadb-201903141403.sql

All of my attempts to restore it work for a little bit- when I refresh the database in DBeaver, it shows the table aunotification and all preceding tables. There are no subsequent tables. Also, I'm using the utf8mb4 charset, so I don't think that's the issue.

I would really appreciate any help someone could give- the other threads I've found online have not helped me.

Here's the full error message in DBeaver after restoring the database:

/usr/bin/mysql --host=localhost --port=3306 -u root acumatica
Task 'MySQL script' started at Sun Mar 22 11:43:56 PDT 2020
ERROR 1146 (42S02) at line 4037: Table 'acumatica.AUNotification' doesn't exist
Task 'MySQL script' finished at Sun Mar 22 11:44:01 PDT 2020
2020-03-22 11:44:01.556 - IO error: Process failed (exit code = 1). See error log.
2020-03-22 11:44:01.557 - java.io.IOException: Process failed (exit code = 1). See error log.
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.executeProcess(AbstractNativeToolHandler.java:182)
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.doExecute(AbstractNativeToolHandler.java:237)
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.lambda$0(AbstractNativeToolHandler.java:52)
    at org.jkiss.dbeaver.runtime.RunnableContextDelegate.lambda$0(RunnableContextDelegate.java:39)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)

https://imgur.com/a/Tvcc9k3 error log screenshot

https://pastebin.com/KfCRW2G2 create statements and surrounding file


texts:
I execute a query eith a long textual column name:

select column as "a_very_very_long_field_name"
from table;

The result looks like :

a_very_very_long_fi
--------------------

1.

2.

...

Is it possible to set the result for unlimited length?


texts:
I am currently working with a SQL Server 2008 R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables have Columns with Ansi Padding on AND columns with Ansi Padding off.

I have now been tasked with "converting" those tables so that every column uses Ansi_Padding ON.

I would like to know what is the easiest way to do this?

I am supposed to write a script which "converts" one table but in the end I am going to need a script which changes all the tables.


texts: Why postgres is using an odd index with no relation to the query

texts:
(Debian 7, Postgres 9.3, dedicated machine with huge cache)

I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two and in the explain query Postgres is using an odd index not at all related to the query stuff like this:

Query:

select l.name,
       count(1) as quantity
from process_data pd
     join process_location l on pd.fk_location = l.id_process_location
where pd.active and pd.fk_status = 1
group by l.name
order by l.name
limit 1000

The explain query give me this:

+----------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                  QUERY PLAN                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Limit  (cost=166513.62..166513.88 rows=107 width=33)                                                                                |
|   ->  Sort  (cost=166513.62..166513.88 rows=107 width=33)                                                                                |
|         Sort Key: s.name                                                                                |
|         ->  HashAggregate  (cost=166508.94..166510.01 rows=107 width=33)                                                                     |
|               ->  Hash Join  (cost=4.84..165278.56 rows=246076 width=33)                                                                     |
|                     Hash Cond: (d.fk_location = s.id_process_location)                                                                       |
|                     ->  Index Scan using idx_process_data_last_execution_start on process_data d  (cost=0.43..161890.61 rows=246076 width=8) |
|                     ->  Hash  (cost=3.07..3.07 rows=107 width=41)                                                                            |
|                           ->  Seq Scan on process_location s  (cost=0.00..3.07 rows=107 width=41)                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------+

As we can see, the query is using the index idx_process_data_last_execution_start, wich is:

"idx_process_data_last_execution_start" btree (priority, last_execution_start) WHERE fk_status = 1 AND active

None of its columns are mentioned in the query, so the question is: Why it's being used and how it's being helpful?

The second question is, why it's not using this index I created:

"idx_process_data_fk_location_active_status_1" btree (fk_location) WHERE active AND fk_status = 1

That would make much more sense and it's even smaller in size. The odd index is 41mb long and the second one is 30mb long.

I'm trying hard to understand how indexes work.


texts: The SQL Server (MSSQLSERVER) service terminated unexpectedly. Event ID: 7034

texts:
Unable to find the cause why the SQL Server Service was Terminated Unexpectedly? (Event ID: 7034)

I looked into the SQL Server Error Logs but could find the reason about why the SQL Server service was terminated.

So check the windows event Viewer and I found the following error msg:

Event Message : The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Error Event Id : 7034

Since last night my SQL server service is terminating with the this error.

This service terminates and restarts automatically and sometimes doesn't restart we have to manually start it again.

Version Details:
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Standard Edition (64-bit)

OS: Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hyper-visor)


texts: postgresql, connection limit for different roles

texts:
Is possible setup in postgresql.conf connection limit for specified role globally (in some .conf file) or individually?
For example, I have 3 roles and max_connections = 600.

Need to setup max 10 connections globally for all 3 roles.
Need to setup max 10 connections for 1st role, 20 connections for 2nd role, and 30 connections for 3rd role.

About second point, I read somewhere it is possible, but not via config file?
Thank you.


texts:
I'm thinking to use PostgreSQL for a project because its free, people say its good, but I'll need some encryption in my db. I did a little search and I found these options in PostgreSQL:

bf
md5
xdes
des

I don't know what is the "most safe", Can you guys help me with this? In this case I prefer security with a little slowness than speed and a little security.

In this case I guess encryption is better than "just" hash the passwords, and really need this on db. I need a strong encryption for login information of my users.The difficulty is not how to store, but how to protect this with encryption.


texts: DB Optimization with 2 to the power of N - Approach clarification

texts:
This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know whether this a better approach for the explained scenario in the link below,

DB Optimization Blog Link

If there are another better option kindly advice.


texts: Delete JSON object from array, in multiple columns, post

texts:
CREATE TABLE arr_test (
  id serial primary key,
  data jsonb
);

INSERT INTO arr_test (data) VALUES
  ('{"test_row": 1, "my_arr": [{"serial":"AAA", "content":"123"}, {"serial":"BBB", "content":"345"}]}'),
  ('{"test_row": 2, "my_arr": [{"serial":"CCC", "content":"456"}, {"serial":"DDD", "content":"567"}]}'),
  ('{"test_row": 3, "my_arr": [{"serial":"AAA", "content":"678"}, {"serial":"EEE", "content":"789"}]}');

I'm using PostgreSQL.
and I'm trying to delete all the objects in "my_arr" where "serial":"AAA", and I'm trying to do it to the entire column at once.
Tried many queries but non of them seems to work
Thank you!!


texts:
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.

I have a UTF-8 database, where collation and c_type are en_US.UTF-8, and Postgres version is 9.6.2.
When I search for non-ASCII rows like this:

select title from wallabag_entry where title ~ '[^[:ascii:]]';

I get both Unicode and non-Unicode symbols (full output is here):

Сталинская правозащитница: мать Меленкова бабушка Настя
Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?
Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев
Как комиссар Крекшин в 1740-е чуть не отменил историю России
Have you heard of Saint Death? Don’t pray to her.
Архаїчна українська мова: перевага чи недолік?
Гренада не их
China’s marriage rate is plummeting because women are choosing autonomy over

What is wrong with this query?


texts: Converting existing table with varbinary into filestream enabled table?

texts:
I have an existing database with 70%+ data in one table with a varbinary column.  As the database keep growing (takes longer and longer to backup and restore) I think it may be beneficial to enable filestream so we can separate the relatively stable binary files from the rest of the database.

If anyone can comment on my plan:

Enable the Filestream feature on the SQL Server.
Attach a network share (for storing the filestream data) to the server
Create a filestream file group for the database.
Create a table ("file2") that is identical to the existing table ("file") with an additional ROWGUIDCOL column in the Filestream file group.
Insert existing data from "file" to "file2".
Truncate the old table "file".
Delete the old table "file".
Rename the table "file2" to "file" so existing application continue to work.
(Optional) shrink database then reindex

Thanks


texts:
I'm using MongoDB version 3.0.0. I'm trying to setup mongodb replication on our machine. Replication was initially setup but due to some changes on the VM the entire thing crashed. When I tried to set it up again, the secondary and Arbiter became stuck in StartUp Mode.

In the mongoDB conf file I have set

replSet=ReplicaSet1

I added 2 machines using the command

rs.add("10.235.96.12:27017")
rs.add("10.235.96.12:27017")

But after this on the primary when I do a rs.status() secondary and arbiter are still shown in StartUp

ReplicaSet1:PRIMARY> rs.status()
{
    "set" : "ReplicaSet1",
    "date" : ISODate("2015-07-31T04:45:57.260Z"),
    "myState" : 1,
    "members" : [
            {
                    "_id" : 0,
                    "name" : "BOSPROD9:27017",
                    "health" : 1,
                    "state" : 1,
                    "stateStr" : "PRIMARY",
                    "uptime" : 63104,
                    "optime" : Timestamp(1438257913, 1),
                    "optimeDate" : ISODate("2015-07-30T12:05:13Z"),
                    "electionTime" : Timestamp(1438254975, 2),
                    "electionDate" : ISODate("2015-07-30T11:16:15Z"),
                    "configVersion" : 7,
                    "self" : true
            },
            {
                    "_id" : 1,
                    "name" : "10.235.96.12:27017",
                    "health" : 1,
                    "state" : 0,
                    "stateStr" : "STARTUP",
                    "uptime" : 62663,
                    "optime" : Timestamp(0, 0),
                    "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
                    "lastHeartbeat" : ISODate("2015-07-31T04:45:56.520Z"),
                    "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                    "pingMs" : 0,
                    "configVersion" : -2
            },
            {
                    "_id" : 2,
                    "name" : "10.235.96.13:27017",
                    "health" : 1,
                    "state" : 0,
                    "stateStr" : "STARTUP",
                    "uptime" : 60043,
                    "lastHeartbeat" : ISODate("2015-07-31T04:45:55.786Z"),
                    "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                    "pingMs" : 0,
                    "configVersion" : -2
            }
    ],
    "ok" : 1
}

When I try to do a rs.conf on the Secondary or Arbiter I get the message

2015-07-31T05:23:07.927+0000 E QUERY    Error: Could not retrieve replica set config: {
    "info" : "run rs.initiate(...) if not yet done for the set",
    "ok" : 0,
    "errmsg" : "no replset config has been received",
    "code" : 94
}

I did try multiple times to clean all the local.0, local.1 files in all the machines and also removed all the journal files from the journal folder as well. Still I'm getting the same issue. Can someone tell me what is it I'm doing wrong here?

I have set replset on all the members. Tried starting in standalone mode and it works.


texts: Index utilization issues in SQL Server queries

texts:
These days I have to optimize queries written by someone long back and I don't have business info behind them.So trying to optimize them a safe way,without hampering business logic.
While tuning queries, I have come across some doubts which I want to be clear.

The table source_price_curve is as follows:

CREATE TABLE [dbo].[source_price_curve](
    [source_curve_def_id] [int] NOT NULL,
    [as_of_date] [datetime] NOT NULL,
    [Assessment_curve_type_value_id] [int] NOT NULL,
    [curve_source_value_id] [int] NOT NULL,
    [maturity_date] [datetime] NOT NULL,
    [curve_value] [float] NOT NULL,
    [create_user] [varchar](50) NULL,
    [create_ts] [datetime] NULL,
    [update_user] [varchar](50) NULL,
    [update_ts] [datetime] NULL,
    [bid_value] [float] NULL,
    [ask_value] [float] NULL,
    [is_dst] [int] NOT NULL,
 CONSTRAINT [IX_unique_source_curve_def_id_index] UNIQUE NONCLUSTERED
(
    [as_of_date] ASC,
    [source_curve_def_id] ASC,
    [maturity_date] ASC,
    [is_dst] ASC,
    [curve_source_value_id] ASC,
    [Assessment_curve_type_value_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[source_price_curve]  WITH NOCHECK ADD  CONSTRAINT [FK_source_curve_def_id] FOREIGN KEY([source_curve_def_id])
REFERENCES [dbo].[source_price_curve_def] ([source_curve_def_id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[source_price_curve] CHECK CONSTRAINT [FK_source_curve_def_id]
GO

It has around 130 million records.The following indexes are defined within this table.

CREATE CLUSTERED INDEX [source_curve_def_id_index] ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [source_curve_def_id] ASC,
    [maturity_date] ASC,
    [is_dst] ASC,
    [curve_source_value_id] ASC
)
CREATE NONCLUSTERED INDEX [source_price_curve_123] ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [as_of_date] ASC
)
INCLUDE (   [maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id]
ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [curve_source_value_id] ASC
)
INCLUDE (   [curve_value],
    [maturity_date],
    [source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id_Assessment_curve_type_value_id]
ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [curve_source_value_id] ASC,
    [Assessment_curve_type_value_id] ASC
)
INCLUDE (   [curve_value],
    [maturity_date],
    [source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_maturity_date] ON [dbo].[source_price_curve]
(
    [maturity_date] ASC
)
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id_curve_source_value_id]
 ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [curve_source_value_id] ASC
)
INCLUDE (   [as_of_date],
    [Assessment_curve_type_value_id],
    [curve_value],
    [is_dst],
    [maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id111]
ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [Assessment_curve_type_value_id] ASC,
    [curve_source_value_id] ASC,
    [maturity_date] ASC,
    [as_of_date] ASC
)

I run a query as follows:

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
FROM source_price_curve spc
WHERE spc.as_of_date >= @as_of_date_from

The execution plan of this query is provided in the below link:
https://www.brentozar.com/pastetheplan/?id=BJ2_3boSZ

I wonder why it is utilizing index IX_PT_source_price_curve_maturity_date(with index on maturity date column only) when filter condition is on as_of_date(while selecting data from source_price_curve).
Why isn't it utilizing the clustered index which has as_of_date as the first column in index order ?

Thanks in advance.


texts: How to re-create the ##MS_PolicyEventProcessingLogin## principal

texts:
I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how this happened.

How do I recreate this principal?


texts:
I am attempting to use a Left Outer Join to return data from two tables.  MetaKnight has the most storeNames and I want all of those to be returned, and of course the corresponding data from KnightNinja.  I threw together the below syntax, but my syntax is only returning the name of the stores in both tables that meet the criteria.  For example, Store D & Store E should be returned since the date is in January of 2017 - but the syntax does not return it.  Why?

Here is sample garbage DDL & Query

Create Table MetaKnight
(
    storeName varchar(500) NOT NULL
    ,storeNumber varchar(100) NOT NULL Primary Key
    ,openDate date NOT NULL
)

Create NonClustered Index snIndex
On MetaKnight (storeName ASC)

Create Table KnightNinja
(
    storeName varchar(500) NOT NULL
    ,ssID varchar(100) NOT NULL Primary Key
    ,c1 int not null Default(0)
    ,c2 int not null Default(0)
    ,c3 int not null Default(0)
    ,c4 int not null Default(0)
    ,d1 date not null
)

Create NonClustered Index storeIndex
On KnightNinja (storeName ASC)

Insert Into KnightNinja (storeName, ssID, c1, c2, c3, c4, d1) Values
('Store A', '1234', '7018', '0',    '4635', '6',    '2017-03-13'),
('Store B', '5678', '0',    '0',    '0',    '0',    '2017-01-28'),
('Store C', '9101', '219',  '898',  '154',  '11',   '2017-01-10'),
('Store D', '8842', '25019',    '258947',   '5746', '0',    '2017-01-31'),
('Store E', '6643', '26682',    '25920',    '3410', '281',  '2017-02-15'),
('Store F', '2211', '57',   '0',    '392',  '22',   '2017-03-11'),
('Store G', '1432', '398',  '1007', '407',  '0',    '2017-01-10'),
('Store G', '8879', '534',  '3125', '954',  '19',   '2017-03-11'),
('Store H', '8675', '40',   '0',    '146',  '147',  '2017-01-01'),
('Store I', '2411', '0',    '4899', '0',    '0',    '2017-01-20')

INSERT INTO MetaKnight (storeName, storeNumber, openDate) Values
('Store A', '2.2',  '2017-01-19'),
('Store B', '8103', '2017-10-25'),
('Store C', '5522', '2017-10-25'),
('Store D', 'Blue', '2017-09-26'),
('Store E', 'B6565','2017-04-28'),
('Store G', 'E999', '2017-01-14'),
('Store G', 'L1354','2017-01-14'),
('Store G', 'R2288','2017-01-14'),
('Store H', 'A5678','2017-01-28'),
('Store I', 'C1234','2017-01-14')

Select
mk.storeName
,[Ninja Count] = COUNT(kn.ssID)
,[Meta Count] = COUNT(mk.storeNumber)
,[C1 Total] = SUM(COALESCE(kn.c1,0))
,[C2 Total] = SUM(COALESCE(kn.c2,0))
,[C3 Total] = SUM(COALESCE(kn.c3,0))
,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON mk.storeName = kn.storeName
AND kn.d1 >= '2017-01-01'
AND kn.d1 < '2017-02-01'
WHERE mk.openDate >= '2017-01-01'
AND mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

Drop Table KnightNinja
Drop Table MetaKnight


texts:
I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following:
CREATE server 'test' foreign data wrapper postgres_fdw
OPTIONS (dbname 'name_db', host 'ip_adress');

CREATE USER MAPPING FOR "Userame"
SERVER 'test' OPTIONS (user 'Username', password '******');

Afterwards it is possible to query with db_link left join etc.. so far no problem. My question is now, how can I hide the passwords for the different users that are visible in foreign data wrappers-postgres_fdw-'test'-user mapping-options ???


texts: How to combine GROUP BY and ROW_NUMBER ( ) OVER

texts:
I Have

CusID Name Country PhoneNo
------------------

1     JOHN USA     555555
1     JOHN USA     555554
2     MIKE CANADA  111111
2     MIKE CANADA  122222
5     JOHN UK      888888
5     JOHN UK      888889

I want to display output like this:

CusID Name Country PhoneNo
------------------

1     JOHN USA     555555
                   555554
2     MIKE CANADA  111111
                   122222
5     JOHN UK      888888
                   888889

(or) any other methods in SQL?


texts: creating a 24 hours data table - Suggestions/improvements


texts: Get rows having different values for a column based on the duplicate values of combination of other 3 columns

texts:
I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.

Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.

But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.

rows 5 and 6 are not to be listed since they are single row with different values.

+----------+-------+--------+--------+
| dept     | role1 |role2   |DEF     |
+----------+-------+--------+--------+
| a        | abc   | er     | 0      |
| a        | abc   | er     | 1      |
| b        | qwer  | ty     | 0      |
| b        | qwer  | ty     | 0      |
| c        | der   | ui     | 1      |
| d        | nerr  | io     | 0      |
+----------+-------+--------+--------+
output

+----------+------+------+------+
| dept     | role1|role2 |DEF   |
+----------+------+------+------+
| a        | abc  | er   |0     |
| a        | abc  | er   |1     |
+----------+------+------+------+

I tried using distinct with having but not able to check the values of column DEF to get desired result.

Can anyone help me on this?


texts: Replicate data with minimal downtime using transactional replication

texts:
I have a SQL Server database with nearly 7TB of data. I want to replicate it using transaction replication.

What will be its approximate downtime? Please suggest a method to implement it with minimal or zero down time if any?

We have not yet implemented the procedure so I don't know exactly about the latency. We are planning to do it with normal LAN (in GB).


texts: Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code?

texts:
I'm not sure if I can ask this question here. Let me know if I can't. Basically, my code works fine, but I would like to improve the code itself.

I'd like to improve this code to require fewer lines (faster is optional, but not mandatory). I think my code could be optimized in space because it performs two queries about the same table in the two join statements.

Here is the dbfiddle of my query. Note that even when the page said "MySQL 8.0" I'm using MySQL 5.7 (an old version where a lot of new stuff can't be done).

First, we create the (simplified) tables:

CREATE TABLE products (
 code varchar(10) NOT NULL,
 name varchar(100) NOT NULL,
 PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE transactions (
 date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 code varchar(10) NOT NULL,
 mode enum('Buy','Sell','Count','Return') NOT NULL,
 stock int(11) NOT NULL,
 average_price decimal(14,4) NOT NULL,
 PRIMARY KEY (date),
 KEY Code (code),
 CONSTRAINT transactions_ibfk_1 FOREIGN KEY (code) REFERENCES products (code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then we populate with records:

INSERT INTO products(code, name) VALUES
  ("A", "Alpha"),
  ("B", "Beta")
INSERT INTO transactions(date, code, mode, stock, average_price) VALUES
  ("2000-1-2", "A", "Buy", 5, 1),
  ("2000-1-3", "B", "Buy", 4, 2),
  ("2000-1-4", "B", "Sell", 2, 3),
  ("2000-1-5", "B", "Sell", 1, 4),
  ("2000-1-6", "A", "Buy", 6, 5),
  ("2000-1-7", "A", "Buy", 8, 6)

Finally, we make the view (if you prefer in your answer you can alter the table instead of making a view, but I didn't figure how):

Create VIEW products_view_joinA_and_JoinB
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd, t5.date as last_buy
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code))
      left join(
        select t6.code, t6.date from transactions t6
        where (t6.date = (
          select t7.date from transactions t7 where (t7.code = t6.code and t7.mode = "Buy") order by t7.date desc limit 1)
       )) t5 on((t5.code = t1.code)))

Finally, we test it:

SELECT code, name, stock, cost_usd, last_buy FROM products_view_joinA_and_JoinB

code | name  | stock | cost_usd | last_buy
:--- | :---- | ----: | -------: | :------------------
B    | Beta  |     1 |   4.0000 | 2000-01-03 00:00:00
A    | Alpha |     8 |   6.0000 | 2000-01-07 00:00:00

For a better understanding I make these two views with half code each one:

Create VIEW products_view_joinA
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))

SELECT code, name, stock, cost_usd FROM products_view_joinA

code | name  | stock | cost_usd
:--- | :---- | ----: | -------:
B    | Beta  |     1 |   4.0000
A    | Alpha |     8 |   6.0000

And

Create VIEW products_view_joinB
  AS select t1.code, t1.name, t2.date as last_buy
    from (products t1 left join (
      select t3.code, t3.date from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code and t4.mode = "Buy") order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))

SELECT code, name, last_buy FROM products_view_joinB

code | name  | last_buy
:--- | :---- | :------------------
A    | Alpha | 2000-01-07 00:00:00
B    | Beta  | 2000-01-03 00:00:00

The output works fine, but the code itself has some duplicated lines that I would like to remove, I think that would also increase its speed.


texts: What happen when a transaction is committed and SNAPSHOT isolation is enabled?

texts:
I am actually considering using SNAPSHOT isolation for one WCF service.
Before doing so, I would like to know how it works.

AFAIK, when it's enabled, updated rows are maintained in tempDB (instead of modifying data directly). Because of that, other readers (eg : that execute a SELECT) won't be blocked and will not see any change until transaction is committed.

What is not clear to me is what happen when transaction is committed.
I tried the following (on a huge table with SNAPSHOT enabled) :

1)

BEGIN TRANSACTION
UPDATE foo SET bar = 'something' --take a LOT of time, which is expected

2) (in another connection/tab)

SELECT * FROM foo --still works, because of SNAPSHOT isolation

3) (back to connection/tab started in 1.)

COMMIT  --very fast, almost immediate.

I expected that commit to be very slow, because I thought SQL would have to copy rows from tempDB back to the pages that contains the actual rows data.
Instead, it was almost  immediate. Why is it so ?

Also when transaction was still not committed, I took a look at tempDB but could not see any table or data (when a temporary table is created, for example, it's possible to see it in tempDB).


texts: Get distinct values through direct query of an index

texts:
I have a relatively large SQLite database that I'm building. The general access profile of this database is:

Initial load of 300 million rows.
Initial read of each row, and UPDATE on approximately 30 million rows
Ongoing read-only access through a python script with a MyDatabaseReader class.

The data structure is a flat denormalized table and queries are of the form:

WHERE chromosome=TEXT, position=INT, reference_sequence=TEXT, alternate_sequence=TEXT

I would like to validate that an incoming query requests a valid chromosome name, based on the contents of the database. Requesting chromosome='chr13' is valid, but in the case of '13' or 'chr31' I would like my python script to throw an error, instead of silently returning zero rows. To enable this, my during the init of my python class, it makes an initial query to get the set of valid chromosome names:

SELECT DISTINCT chromosome from dbsnp;

This query takes a LONG time, and bogs down the execution. I have tried both a compound index on (chromosome, position, reference_sequence, alternate_sequence), as well as a single index on chromosome, and have verified with EXPLAIN QUERY PLAN that in both cases the index gets used.

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp;
order|from|detail
0|0|TABLE dbsnp WITH INDEX chromosome ORDER BY

My question: Is there some SQL trick to query directly from the chromosome index. I don't care about anything else in the row, and it seems that the index is a pre-built version of the data that I want to return.

Alternatively, I'm considering constructing a chromosome_names table that I populate with my SELECT DISTINCT query above after database load and update. Because it scares me to construct a static table that can fall out of sync with the main table I'm considering triggers to update the chromosome_names table on change of the main table. However, I'm concerned that this may cause significant churn should I update rows in the main table, and more importantly, that I'm reinventing what is essentially contained in an index.

Is there a good way to get my distinct values query from the index directly, or alternatively, is there a way to have SQLite throw an error should the queried value for chromosome be outside of the set of contained values (note: position, ref, and alt sequences are expected to sometimes query for unexpected values so erroring on zero rows returned will not work).

Thanks


texts:
I have a quick question regarding the cleaning of no longer necessary transaction log backups. So, I have a database in FULL recovery mode. Every night, at 10 PM a backup takes place (via maintenance plans). Now, I also have a different maintenance plan, that takes a transaction log backup every 15 minutes during the work hours, so at the end of the day, there are quite a few files.

What is the best way to clean these if the 10 PM backup executes successfully? Can I just delete all the transaction log backups from throughout the day? (at this point they are already caught in the backup, right?). Basically I am trying to start the day fresh, so to speak, with all the data backed-up and a new set of transaction log backups.

Thanks!


texts:
Microsoft SQL Server offers an arbitrary application-defined locking mechanism through the sp-getapplock command and related commands.

The key, or resource_name, is specified as a string, a varchar 255. But the documentation also mentions that the key/name is hashed.

So that leads me to wonder:

What kind of hashing function is used?
How well-distributed are the results of that hashing function?
What kind of input should I use to widen the distribution of hash result, to minimize chance of coincidental collision?
What is the theoretical and practical limits on the number of applocks I can have active?
What resources are used by applocks, such as amount of memory or limits on memory?

I am implementing a pessimistic locking mechanism for the rows of many tables for a special-needs app. As my applock key/name I plan to use a number representing each table combined with an ID number stored in each row. An example key/name might be something like mydb_myschema_table0000000142_row0000241738. I may have many of these active at a time, so I need to know about these limits.


texts: sql server Group by with calculated values

texts:
I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated variables in group by statement (in group by should stay only name of the variable)?
Please find me the solution wrom which total sql code is as small as possible.

select
       p.id,
       p.name,
       case when
        (p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 end as validity_name,
       p.surname,
       case when
        (p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%')) then 0 else 1 end as validity_surname,
       p.adress,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id)
group by id, name, validity_name, surname, validity_surname, adress


texts: Find out date and or number of error logs xp_readerrorlog

texts:
I have some code to help automate checking the error logs daily on our SQL Server.  This code calls xp_readerrorlog 0 to check the error log, and xp_readerrorlog 0,1 to check the SQL Server log.

Sometimes, however, there is more than one error log I need to scan.

I would like to be able to see, by T-SQL, the date of the log or, barring that, find the number of logs that exist.

I read that we can have up to 99 archive logs, but the server throws the following error message when trying to display an error log that doesn't exist.

"xp_readerrorlog() returned error 2, 'The system cannot find the file specified."

I know the date information and number exists since SSMS displays it( see the image below.)


texts: Trying to create a table with foreign keys and its missing a few rows

texts:
This is what I have so far:

CREATE TABLE GRADE
(
    COURSE_NO   VARCHAR2(8),
    STUDENT_ID  VARCHAR2(15),
    GRADE   VARCHAR2(3),
    PRIMARY KEY(COURSE_NO,STUDENT_ID) ENABLE
);

ALTER TABLE GRADE
ADD FOREIGN KEY (COURSE_NO)
REFERENCES COURSE (COURSE_NO)ENABLE;

ALTER TABLE GRADE
ADD FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENT (STUDENT_ID)
ON DELETE CASCADE ENABLE;

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES ('CS100','123','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','234','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','456','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','567','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','678','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','789','C');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','890','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','901','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','012','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','013','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','014','C');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','015','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE220','016','B');           -- This one doesn't work

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
       VALUES('EE200','017','A');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE200','018','B');           -- this one either

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
       VALUES('MA220','019','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
     VALUES('MA220','020','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('MA220','021','B');           -- This insert doesn't work


texts:
I have an SSIS package that loads data nightly from a warehouse to a database. Both the SSIS and SQL Server agents are on SQL server 2012 Enterprise and Windows Server 2012 R2 Standard.

When I run the SSIS Package manually it takes less than a minute consistently.But when this package is executed by the scheduled job SQL Server Agent, it hangs for hours and hours and sometimes it does not even complete.

Here is my observation of the time it takes to run the package, after I restarted the Server (box). The scheduled job runs:

1st day 45 seconds --- pretty much the same time it takes to run when I run the SSIS package manually.

2nd day 2 hours and 25 minutes

3rd day 45 minutes

4th day 10 minutes

5th day ran more than 7 hours. I killed the schedule job and ran the SSIS package manually, it finished in less than one minute.

What would be the problem?


texts: Find missing timestamps grouped by key: finding gaps in my data

texts:
I have a table that has a timestamp, some data and a identifying key for the data source:

create table test_data (
    id serial primary key,
    key text,
    timestamp timestamp with time zone
);
INSERT INTO test_data
    (key, timestamp)
VALUES
    ('Source_A', '2018-03-15 01:07:06.603029+00'),
    ('Source_B', '2018-03-15 10:00:01.603029+00'),
    ('Source_A', '2018-03-15 11:05:06.603029+00'),
    ('Source_B', '2018-03-15 15:09:06.603029+00'),
    ('Source_B', '2018-03-15 16:09:06.603029+00');

I want to find the number of missing hours in the data grouped by each data source. I've got this code that works for a single group:

SELECT
COUNT(hours)-1 AS missing_hours,
'Source_A' AS key
FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') AS hours
  WHERE hours NOT IN
  ( SELECT TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600) AS time_bit
   FROM test_data
   WHERE key = 'Source_A'
   GROUP BY time_bit)

Running this give me:

missing_hours,  key
22,             Source_A

I'm struggling to figure out how I can group by key and then get the number of missing hours for all data sources:

missing_hours,  key
22,             Source_A
21,             Source_B

Any ideas? This will be running on monthly partitioned tables with ~50 million rows each so I don't want to have it be too expensive. The single key query runs in about 2 secs.


texts: Allow db_owner to add/view existing logins to database

texts:
On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database.  The problem is they can't view them, they can only see themselves in the list.  I know this was a security change in later versions of SQL Server to separate/restrict permissions more, but in our development environment it is just a pain.

I came across a post that suggested giving access to several server metadata views to the public role.  As this would make things more like SQL 2000.  (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104997 which internally references http://technet.microsoft.com/en-us/magazine/cc161026.aspx)

GRANT VIEW ANY DEFINITION TO public
GRANT VIEW SERVER STATE TO public

But this seems like giving too much permissions over to the public role.  I just want to give the dbo's just enough rights to the system metadata views to allow them to browse for the existing logins on the server.  That way they can select who they need to add without having to message the server admins each time.

Does anyone know the minimum set of views that should be granted permissions in order to allow this?

Thanks!


texts: MySQL Group Replication fails to understand "::ffff:" IP Address

texts:
I'm trying to set up group replication using 3 x MySQL 8.0.16 databases, but when I start up the second Node it fails. The error log reports:

[GCS] Connection attempt from IP address ::ffff:10.12.1.2 refused. Address is not in the IP whitelist.

But if I add a whitelist:

group_replication_ip_whitelist="::ffff:10.12.1.2";

I get:

[GCS] Invalid IP or subnet mask in the whitelist: "::ffff:10.12.1.2";

Is this an issue with the server at my end, or an issue with Group Replication not understanding the IP Address?

My configuration is:

server_id=<different for each one>
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
group_replication_group_name="8112d18e-9e6e-11e9-a526-0e51be33883f"
group_replication_start_on_boot=off
group_replication_local_address= "10.12.1.1:33061" /*This is different for each one*/
group_replication_group_seeds= "10.12.1.1:33061,10.12.1.2:33061,10.12.1.3:33061"
group_replication_bootstrap_group=off

If I look on the first nodes error log I can see:

Connection attempt from IP address ::ffff:10.12.1.2 refused. Address is not in the IP whitelist.'

But again, adding it to a whitelist throws the above error about there being an invalid IP Address,


texts: Design Question - Stock and OnSale Table

texts:
Background :

I'm designing a solution for a repair toy association.
Since they have office - I'll use Access.

Plot :

Stock will store items -
I choose this primary key (Item_Id,Record_date) because I had to able :

Ex : Item1 entered by User_X multiple times at different dates.
Items will be stored in stock THEN will be put online via Ebay
(Ebay API isn't on question - they don't want full automatisation)

Stock(Id_item, Record_date, Record_user_id)
OnSale(Id_item, Record_date, Record_user_id, OnSale_date,....)

Questions :

Can't decide if I am allowed to merge Stock & OnSale Table & use Price column as a test to differenciate them ? Or should they be separated ?
Is that correct to have 2 1-to-many relationship linked to one table (User - Stock & User1 - Stock)

If I am completly wrong, I'm really open to any suggestions that could improve my knowledge.

Edit :

Thank for the comment. My first version of the db using SoldOut & Remove Table as "Log table". I will make the changes about separating Stock & OnSale Items.
Correct me if i'm wrong :

Item is entered in stock
Item moves to OnSale Table when someone write the full spec.
Item moves to SoldOut when it has been sold.

Best regards, Falt


texts: Migrate SSRS Reports to Power BI Report Server

texts:
I have to servers: one with the SSRS Report Server and a second server with the Power BI Report Server.

The issue is I'm trying to migrate the SSRS Reports to the Power BI Report Server instance on a third server that I plan to unify both report databases.

As far as I understood based on a Microsoft article (Migrate a report server installation), it's possible to migrate the SSRS database to the Power BI Report Server as shown in the picture to the same instance and service (Power BI Report Server in this case):

I've created the backup of the SSRS databases (and encryption key) and restored on the PBI server, but it's not working. If I restore only the PBI database (and its encryption key) it works fine on the destination server. On the Report Server Configuration Manager I can only set the ReportServerPBI (that I restored from the server that already runs the PBI) to have it working. If I set the ReportServer database that I've had made the backup it doesn't work.

Am I mistaken or is there any way that I can put these services in the same server/database and running under the Power BI Report Server?

Will I have to keep separate instances to have both dashboard types running?


texts: How to determine the data type of all fields in a result set in SSDT?

texts:
I'm writing a table valued function in SQL Server Data Tools (SSDT).  Table valued functions require you to explicitly state the data type of the returned table.  In my particular case, I've written a query which returns the values that I want.  I can execute it using SSDT, but can't figure out how to quickly determine the data type of the columns returned.

Is there a way to get the list of columns that it returns and the data types of said columns so I can quickly and accurately populate the list of columns in the ReturnTable?

The closest I've gotten is to hover over the field name in the SELECT clause, but that doesn't provide the length or precision limitations.

Edit: I struck through my comment regarding table valued functions require you to explicitly state the data types of the columns in the returned table based on @srutkzy's answer.  Thanks!


texts: SQL Server on big machine falls over inexplicably

texts:
We've been doing some throughput testing with our web application.

The production application resides on a couple of machines in the amazon cloud, consisting of a web server and a database server.  We wanted to know if replacing the current database machine with a bigger one would increase throughput.

A workload was constructed, and run against machines configured identically to the production web and database servers (call them W and DB A).  Observing pegged cpu activity on DB A, we configured a database server(DB B)  with more cpu capacity and re ran the tests.

We were pleased with the results; response time was cut in half, cpu usage was down to 80 % or so.

Amazon has available one machine bigger (DB C), so we thought we'd do it again. (the cloud is great!)  But this time, the the workload started failing immediately, with deadlocks and time outs waiting for connections.  CPU utilization was pegged at 100 %.  The poor thing just couldn't keep up.

Having DB B still available, we pointed W back at DB B and verified the test; all was okay.  We pointed Web to DB C, and it fell over again.

One thing that was different between B and C, was that with B, the sysAdmin was able to image the disks from production.  However with C, the system drive couldn't be imaged, and so the machine was spun up with their latest version of SQL Server.

The data and log drives were available, so I configured the pre-installed SQL Server to attach the relevant .ldf and .mdf files (after having to use takeown and cacls to adjust the permissions of the files, which had their sids all messed up).  After attaching the db files to the database, I successfully logged in through the web application and clicked around to see that things appeared to be working.

The machines A B and C are rated at 26, 52 and 108 ECUs respectively (an ecu is a relative measure of integer performance). C is plenty big enough.  I'm wondering if there is some bonehead switch that I missed turning on while configuring machine 'C'?

Some stats on the machines tested:

Name vCPU  ECU    Mem
A.....8.....26.....30
B....16.....55.....30
C....32....108.....60

The error log indicated a 17054, but afterwards seemed to load and run SQL Server okay.

The error log also indicated that it detected 32 cpus when run on C, and made no complaint of licensing issues. I didn't think to check whether all cpus were being used during the execution of the test.

We've gone over the configuration options on both B and C; can't see any significant differences; SQL Manager wasn't running on C, but other than that, the instances were configured identically.

How could I go about determining why this is happening?


texts: Super slow MariaDB 5.5 count(*) on large simple table

texts:
I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here.

Simple queries have normal performance:

MariaDB [testing]> select count(*) from domains where domain like 'super%';
+----------+
| count(*) |
+----------+
|    10868 |
+----------+
1 row in set (0.26 sec)

MariaDB [testing]> select count(domain) from domains where domain like 'slow%';
+---------------+
| count(domain) |
+---------------+
|           582 |
+---------------+
1 row in set (0.07 sec)

However count(*) and count(domain) are ridiculously slow:

MariaDB [testing]> select count(domain) from domains;
+---------------+
| count(domain) |
+---------------+
|      12802537 |
+---------------+
1 row in set (59 min 45.86 sec)

Explain claims it is using INDEX, yet it still shows full table scan.

MariaDB [testing]> explain select count(domain) from domains;
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| id   | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows     | Extra       |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
|    1 | SIMPLE      | domains | index | NULL          | domain | 120     | NULL | 12762541 | Using index |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Schema:

CREATE TABLE `domains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` char(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=14782143 DEFAULT CHARSET=latin1;

 MariaDB [testing]> desc domains;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| domain | char(120)        | NO   | UNI |         |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

my.cnf is pretty much default, except for

innodb_buffer_pool_size = 1600M

I am using deadline scheduler. No other queries are running. Nothing else is using the disk. Linux is not swapping.

Aside from ditching InnodDB, is there something else I can try?


texts: PostgreSQL. Unstable execution time hugely exceeding planning time

texts:
PG version: 10.9

Schema:

create table core_bundle
(
    id serial not null constraint core_bundle_pkey primary key,
    dt_add timestamp with time zone not null,
    dt_upd timestamp with time zone not null,
    aid integer not null constraint core_bundle_aid_check check (aid >= 0),
    prc_after timestamp with time zone,
    prc_grp uuid,
    status integer not null constraint core_bundle_status_check check (status >= 0)
);

create index core_bundle_aid_2d875f86 on core_bundle (aid);
create index core_bundle_prc_grp_a0b838ca on core_bundle (prc_grp);
create index core_bundle_status_5cfd1324 on core_bundle (status);

From time to time execution time for the following query dramatically varies.

explain analyze
    SELECT "core_bundle"."id", "core_bundle"."aid"
    FROM "core_bundle"
    WHERE (
            "core_bundle"."prc_after" <= '2019-07-11T14:29:11.159787'::timestamp
            AND "core_bundle"."status" = 13
        )
    ORDER BY "core_bundle"."id" ASC
    LIMIT 1;

Nice case:

Limit  (cost=266.19..266.19 rows=1 width=8) (actual time=0.288..0.289 rows=1 loops=1)
  ->  Sort  (cost=266.19..266.74 rows=221 width=8) (actual time=0.287..0.287 rows=1 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Scan using core_bundle_status_5cfd1324 on core_bundle  (cost=0.43..265.08 rows=221 width=8) (actual time=0.029..0.257 rows=167 loops=1)
              Index Cond: (status = 13)
              Filter: (prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone)
Planning time: 0.138 ms
Execution time: 0.319 ms

Bad case:

Limit  (cost=0.43..127.12 rows=1 width=530) (actual time=2208.373..2208.375 rows=1 loops=1)
  ->  Index Scan using core_bundle_pkey on core_bundle  (cost=0.43..391587.09 rows=3091 width=530) (actual time=2208.372..2208.372 rows=1 loops=1)
        Filter: ((prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone) AND (status = 13))
        Rows Removed by Filter: 4179221
Planning time: 0.218 ms
Execution time: 2208.419 ms

Shared hit is low for nice case (around 67) and about 1030528 in a bad one.

In a bad case there's neither sort nor status index. I wonder what might be the reasons behind that, and what can be done to get rid of bad cases?

Unfortunately adding an index pinning status to a certain value is not really an option, since value varies in where clause.

May default_statistics_target tuning be of help here, what value should I prefer then? Or would an index on prc_after will be efficient and enough to improve things?

Maybe related:

SELECT very slow when no results and a LIMIT is specified
Index that is not used, yet influences query


texts: Is it possible to have SSRS generate report while a parameter is being typed?

texts:
I have only been using SSRS for a few weeks now and from what I have seen, I know that we create a dataset, define parameters (I do it by using a paramaterized stored procedure and linking that to a dataset so it automatically recognizes the parameters), and once they are typed, you hit the 'view report' button.

But I was wondering if there is a way to have a report generated as we type the parameters. For example, if, say, I have  a LIKE in my main query for the dataset, which is linked to a parameter. So as the user types a few characters or even a single character, is there a way to have results appear as each character is typed without having to hit the 'view report' button?

If it is not possible in SSRS, what other softwares/options do I have to achieve this for data stored in SQL Server?


texts: best way to run sql server management studio on linux

texts:
I'm using ubuntu 18.04.2 on my desktop and running sql server 2017 from docker.

I want to use sql server management studio to manage the server.

SO far now, I have a windows 7 enterprise with with ssms 17.9.1 and it works ok

It's really annoying having to boot a whole windows enterprise just for a single app, so I wanted to know if there's some better way to run it. A lighter or better integrated alternative.

that version of ssms doesn't seem to work with wine.


texts: SQL Server Primary key / clustered index design decision

texts:
Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.

I've got a product history table that is inserted into frequently (never updated) and the table has this kind of structure

ProductNo        String(20)
CreatedDateTime  DateTime
Description      String(100)

At the moment the primary key is made up of a combination of ProductNo and CreatedDateTime in an attempt to define a unique index key. We can have many records per productno.

I'll be creating some 1 to 1 related tables and don't want to carry both the productno and the createddatetime fields into the related tables to act as foriegn keys. I also think this combination is a little fragile in order to guarantee uniqueness.

So, I'm planning to add a new field to the table 'ProductHistoryPK' as an incrementing Int or SequentialGuid to act as the primary key and a foreign key to related tables.

In terms of indexes I'm thinking of creating

Non-clustered primary key on the new ProductHistoryPK field.
Clustered Index on the ProductNo field as this is field that is
often searched on.

Any thoughts or pointers regarding this?

Thanks...


texts: Grant Admin to an Active Directory account in SQL Server

texts:
I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that group.

I need to grant admin access to this AD group, for the whole instance. This means that I won't need to execute grant commands to every new table and database created. Any user on that group will automatically be able to create, drop, insert, select, etc.

Even trickier. I must be able to use Windows Authentication to login with MyAccount (I login on Windows and then login to SQL Server without needing to type username-password again), and login using ServiceAccount by typing username and password. I can't be forced to login on Windows with ServiceAccount (or run SSMS/SSDT/etc with that account) to be able to login on MSSQL.

What's the best practice for configuring that?


texts:
The SQL Server 2017 version on Linux comes packaged with a utility called mtlogreader.exe that is absent from the Windows distribution,

MtLog Reader - Dump Hekaton MtLog files

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\mtlogreader.exe <file> [options]

Options:

        -verbose <int>  Verbosity (0-4) Default 1. (short form: -v)
        -buffio <bool>  Buffer stdout (true/false) Default true (short form: -b)
        -keySecret1 <string>    The first secret key blob used for crypto keys (short form: -k1)
        -keySecret2 <string>    The Second secret key blob used for crypto keys (short form: -k2)
        -instructionFile        Treat file as instructionfile. (short form: -i)
        -targetruntimeinseconds <int>   The target run time in seconds (only valid with -i). (short form: -t)
        -skipchksum     Skip checksum validation. (short form: -sc)
        -maxbsn <uint64>        Max BSN to dump. Default 0 (dump whole file). (short form: -m)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing checkpoint file
information extracted at dump time, containing the root file name followed by a
list of container directories (one per line).
 the -t parameter is an indication of how long to run the tool
when used with -i it is used to limit the processing of checkpoint files

I can't find much about Hekaton though. I've found some errors here and I see it has a pretty old wiki page where can I find documentation on the Hekaton subsystem but how to use it? Has Hekaton been rolled into the "Analysis Services"?


texts: How to JOIN a table with a name that equals to a determined value in MySQL?

texts:
The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here.

To map this to the database I’m using the Class Table Inheritance design pattern.

I’m cheating a bit and let the “super-entity” (i.e., the supertype) know, which type of “sub-entity” (i.e., the subtype) belongs to it. That means, the “super-entity” has a property (i.e., attribute or column) named type. The value is camel-cased (for an easier usage in the application). The table names are always underscored.

Here is a diagram depicting my database structure

And a diagram displaying class models for my application program objects

The problem is now the retrieving. When I want to get a Car with id=123, I don't know at first, which type it has and hence which car_* table I have to JOIN. Well, I could workaround it, either JOINing  every time all car_* tables or making two request (when the first one is something like a “pre-request” and is used to get the type: SELECT type FROM car;), but I’m sure, there must be a better solution.

How to SELECT/JOIN a TABLE with a variable name, that equals to a selected value in another TABLE?


texts:
Multiple users access to write on the same table on same time on same record. How to avoid this problem in SQL Server?

Already we implemented record locking. But the record locking will be be enable at updated time. For example, I have one record, user 1 opens the record and edits something, user 2 also opens the same record and edits. Both are submitted at the same time. If they submitted serially, there would be no problem. If they submitted at the same time (it is occurring rarely). How to solve this problem?


texts: Using a Calculated Field in a Separate Calculated Field

texts:
I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field.  For example:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1 AND RateReset = 3 THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate


texts: How to write a query in SQL Server to find nearest values

texts:
Let's say that I have the following integer values in a table

32
11
15
123
55
54
23
43
44
44
56
23

OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a certain number of closest records. Let's say I want to return 10 closest record matches to the numer 32. Can I achieve this efficiently?

It is in SQL Server 2014.


texts: Combine multiple stored procedures into one report?

texts:
This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is a link to the data dictionary:
Data Dictionary

The output for my report is supposed to output these values for a college name or even a partial match to a string.

Currently, this is one of the 4 stored procedures that I have to output at 10%, 25%, 75%, and 90%. I'm trying to combine 10%, 25%, 75%, and 90% into one report when I execute the combined stored procedure. Also, when searching for a string in the combined stored procedure it will return any similar values. For example, the string 'ala' returns any strings in the format specified and in this case, it would probably return all colleges relating to Alamaba.

USE [college]
GO
/****** Object:  StoredProcedure [dbo].[sp_10Report]    Script Date:
2/23/2019 1:41:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_10Report] (
@UNITID float
)
AS
BEGIN

DECLARE @INSTNM VARCHAR(200)
DECLARE @Query NVARCHAR(500)
DECLARE @CUML_DEBT_P10 float
DECLARE @CUML_DEBT_N float

DECLARE @AVG10 float
DECLARE @AVGN float
DECLARE @SUMN float
DECLARE @WAVG float

SET @AVG10 = (select  AVG(CUML_DEBT_P10) from Debt)
SET @AVGN = (select AVG(CUML_DEBT_N) from Debt)
SET @WAVG = (select SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N) from Debt)

select 'Cumulative debt at 10%' as Satistic,
CUML_DEBT_P10 as 'Institution Value',
@AVG10 as 'Average Value',
@WAVG as 'Weighted Average' from Debt
where UNITID = @UNITID

END
GO

Here are the rows that are important.


texts:
My backup on one of my servers is failing once a week.  This is the alert I'm getting:

  DESCRIPTION:  BackupVirtualDeviceFile::PrepareToFreeze:  failure on
  backup device '{DE47BFA0-E5AD-46DD-868C-F374CB5C2EF7}1'. Operating
  system error 995(The I/O operation has been aborted because of either
  a thread exit or an application request.).

I have no idea where to start to figure out why it's fialing, and google hasn't helped me much.

If I look at the application error log on the server, I have a SQLVDI error:

  SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).
  Process=2028. Thread=2452. Client. Instance=.
  VD=Global{DE47BFA0-E5AD-46DD-868C-F374CB5C2EF7}1_SQLVDIMemoryName_0.

After that I have a MSSQLSERVER error:

  BackupVirtualDeviceFile::PrepareToFreeze:  failure on backup device
  '{DE47BFA0-E5AD-46DD-868C-F374CB5C2EF7}1'. Operating system error
  995(The I/O operation has been aborted because of either a thread exit
  or an application request.).

And a SQLWriter backup:

  Sqllib error: OLEDB Error encountered calling ICommandText::Execute.
  hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1,
  Severity: 16 Source: Microsoft SQL Server Native Client 10.0 Error
  message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000,
  Native Error: 3271 Error state: 1, Severity: 16 Source: Microsoft SQL
  Server Native Client 10.0 Error message: A nonrecoverable I/O error
  occurred on file "{DE47BFA0-E5AD-46DD-868C-F374CB5C2EF7}1:" 995(The
  I/O operation has been aborted because of either a thread exit or an
  application request.).

How can I figure out what's causing this failure?