texts: Having DB Mirroring and Replication together

texts:
Can anybody help me understand why do one go combination of Database Mirroring + Replication.
Why can't I just have Database mirroring for high availability of my database. What is the need of combining replication as well.
In what scenario's we choose combining both these technologies.?
Can anyone give an example of combining these 2 technologies ?
I am not sure if people a make things complex combining all high availability solutions. ( Clustering + db mirroring + replication)


texts: Restricting Insert and Update to functions

texts:
We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving write access to the user for those tables which the functions inserts and updates?
So i basically want the user to be able to execute the function and for the changes to happen through the function, but the user should not be able to insert or update the table on his own outside of the function.

TIA.


texts:
In SQL Server 2014 we need to change naming convention for indexes and  keys. The old naming convention is not following any standards. My questions are:

How can we change the naming convention for 200 tables in a production server?
Can the naming be changed at once using some scripts, or do we have to change them one by one?

Edit:
Keys:
Primary or Foreign Key


texts: Improve MySQL Query Performance - Too many records

texts:
I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during administration process. My server load drastically increases when I do search for phone numbers in the entire database. Since I dont want to keep duplicate phone records in the database, I used to check the existence of phone number in the whole database, which cause my application a little bit slower. My Question: How do I improve querying the entire database with high performance.

phone1 -> Datatype Varchar(10)

I tried indexing and it do a little for me. Any other ways to improve performance of my system.

Table Structure:

CREATE TABLE `phone_directory` (
  `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
  `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
  `first_name` VARCHAR(30) DEFAULT NULL,
  `middle_initial` CHAR(1) DEFAULT NULL,
  `last_name` VARCHAR(30) DEFAULT NULL,
  `address1` VARCHAR(100) DEFAULT NULL,
  `address2` VARCHAR(100) DEFAULT NULL,
  `address3` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(50) DEFAULT NULL,
  `state` CHAR(2) DEFAULT NULL,
  `postal_code` VARCHAR(10) DEFAULT NULL,
  `phone1` VARCHAR(12) DEFAULT NULL,
  `phone2` VARCHAR(12) DEFAULT NULL,
  `phone3` VARCHAR(12) DEFAULT NULL,
  `email` VARCHAR(70) DEFAULT NULL,
  `fax_number` VARCHAR(255) DEFAULT NULL,
  `manager_name` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(6) DEFAULT NULL,
  PRIMARY KEY (`lead_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Query:

SELECT * FROM phone_directory WHERE phone1 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone2 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone3 IN ('315XXXXXXX','0315XXXXXXX');


texts:
Have a look at this query.
Is there anyway of making the IN-statement a JOIN instead?

Yes, I'm quite new to this. Thanks in advance for any help.

SELECT * FROM company AS t1, employee AS t2
WHERE t1.id IN
    (SELECT id FROM multinational WHERE market = 'EMEA')
AND t2.ID = t1.employee_id
ORDER BY salary DESC
LIMIT 0, 24


texts:
I'm trying to remotely connect to my postgresql database. I have two virtual machines set up both running ubuntu 14.04. I am trying to connect to the second vm using the first vm using

psql -h 10.0.1.23 -U postgres -d postgres

But I receive the error:

Could not connect to server: Connection refused
    Is the server running on host "10.0.1.23" and accepting
    TCP/IP connections on port 5432?

I have changed the pg_hba.conf and added

host all all 10.0.1.64/24 md5
host all all * md5
host all all 0.0.0.0/0 md5

And changed the postgresql.conf listen_address=" * "

In an attempt to allow all incoming connections. I have also tried to change the firewall settings, but I am unsure of whether or not the ports are properly listening for the connection.


texts: Enabling admin connection on SQL Server Express to fix logon trigger

texts:
Well, luckily I got this error on my test environment. I'm so scared about this error that I disabled all logon triggers I was using on production environment.

I created the famous logon trigger, and I put a database in offline mode just to mess with something else. Then I discover that because the trigger can't find the table, I can't access the instance anymore. There are plenty of questions related to this, but even doing everything I could find, I still can't acces the instance, not even with SQLCMD.

I'm doing this:

sqlcmd -S server\instance -U sa -P <pass>  -A

If I don't use -A I receive the famous error:

logon failed for sa due trigger execution.

If I use -A:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server
  Network Interfaces: An error occurred while obtaining the dedicated
  administrator connection (DAC) port. Make sure that SQL Browser is
  running, or check the error log for the port number [xFFFFFFFF]. .
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout
  expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

Why i'm not able to connect to the sql server

sql server express here.

I could enable SQL Browser, but I still can't access it.

I don't think this question is duplicated because is not about the trigger, is about Why I can't access the server with SQLCMD. I pretty much know how to fix this. I just don't know how can I acces sql server without triggering the trigger.


texts: AlwaysOn Availability Groups log_send_rate

texts:
On all our AlwaysOn setups, running Windows 2012 and SQL Server 2012 in virtual machines and on bare-metal, I find that the log_send_rate in
sys.dm_hadr_database_replica_states is consistently returning incorrect values.

For example (for synchronous mode)

sys.dm_hadr_database_replica_states.log_send_rate (ave = 36,571 (kb/s listed in bol))

Perfmon - SQLServer:Availability Replica - Bytes Sent to Replica/sec (max = 486,000.000, avg = 259,000.000)

Perfmon - SQLServer:Databases - Log Bytes Flushed/sec  (max = 653,044.000, avg = 341,000.000)

I've not seen any posts about this but it doesn't appear to be working correctly. A correct log_send_rate value is useful for monitoring AlwaysOn.

Has anyone else experienced this?


texts: Distribute records on different MySQL databases - MySQL Proxy alternative

texts:
My scenario is the following:

Right now I am using one big MySQL database with multiple tables to store user data. Many tables contain auto increment columns.

I would like to split this into 2 or more databases. The distribution should be done by user_id and is determined (cannot be randomized). E.g. user 1 and 2 should be on database1, user 3 on database2, user 4 on database3.
Since I don't want to change my whole frontend, I would like to still use one db adapter and kind of add a layer between the query generation (frontend) and the query execution (on the right database). This layer should distribute the queries to the right database based on the user_id.

I have found MySQL Proxy which sounds exactly like what I need. Unfortunately, it's in alpha and not recommended to be used in a production environment.
For php there is MySQL Native Driver Plugin API which sounds promising but then I need a layer that supports at least php and java.

Is there any other way I can achieve my objectives? Thanks!


texts: GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

texts:
I'm trying to GRANT permission for an SQL login to access the functions fn_db_log and fn_dump_dblog but GRANTing SELECT on the function still fails with error:

Msg 9010, Level 14, State 1, Line 3
User does not have permission to query the virtual table, DBLog.
Only members of the sysadmin fixed server role and the db_owner fixed database role have this permission

Below are the steps I followed:

create database duumy;
GO
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [duumy]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [duumy]
GO
EXEC sp_addrolemember N'db_owner', N'test'
GO
USE [master]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [msdb]
GO
CREATE USER [test] FOR LOGIN [test]
GO

use master;
GRANT SELECT ON FN_DBLOG TO test;
GRANT SELECT ON FN_DUMP_DBLOG TO test;
GRANT VIEW SERVER STATE TO test;

I'm not sure what I'm missing here. Please assist.


texts: Bulk load data and provide row-by-row feedback

texts:
We want to allow our users to import CSV files containing many thousands of records to "pre-load" a particular table in our system. It's not strictly a one-time operation, it may be performed multiple times as they get feedback, tweak the CSV, and re-import.
I understand that this is not a lot of data, relatively speaking. But, we want the operation to be atomic (this is a multi-user system other users may also be adding or modifying data) and we want to provide per-row feedback (like "Row 123 is a duplicate of item 'abc'").
I know that statements like COPY or tools like pg_bulkload can do the work very quickly, but this is user-interactive via our web app and we require row-by-row feedback.
Our naïve approach was to run sequential INSERT statements and to catch exceptions. Even though we're re-using a single connection, this makes separate calls and uses a separate transaction. This is taking way too long.
for(var i = 0; i < rows.Count; i++)
{
    try
    {
        connection.Execute("INSERT INTO table1 VALUES (...)");
        // format a "successful" response
    }
    catch (PostgresException e)
    {
        // format a "failed" response based on exception details...
    }
}

We tried creating a single INSERT statement with multiple VALUES, but this does not provide row-by-row feedback and it stops on the 1st error (I found no useful way to use ON CONFLICT here).
We also tried sharing a transaction and running sequential statements but the transaction is "aborted" on the 1st error.
using(var tx = connection.BeginTransaction())
{
    for(var i = 0; i < rows.Count; i++)
    {
        try
        {
            connection.Execute("INSERT INTO table1 VALUES (...)", tx);
            // format a "successful" response
        }
        catch (PostgresException e)
        {
            // format a response based on the type of failure...
        }
    }
}

I'm beginning to think that we need to run this entire operation within the database in SQL. We have only one constraint right now, a NOT NULL UNIQUE "name" column. So the statuses would be something like...

"OK": the row inserted fine (return the new PRIMARY KEY "id" column)
"Conflict": the name in this row is a duplicate of an existing row either a row seen earlier in the CSV or already in the table (return the duplicate name)
"Bad Request": the name in this row is missing, empty, or all whitespace

We also need to return the index (in insert order, I guess) in order to approximate the line number in the original CSV file. I'm thinking we need to INSERT into a temporary table and use something like row_number().
Given a CSV like this (name + some example columns, just to make the point)
name,region,code,set
A1,A,100,1
A2,A,101,1
A1,A,102,1
,B,200,2
C1,C,300,3

I would need responses like this:

index
id
name
status

1
100
A1
OK

2
101
A2
OK

3
[null]
A1
Conflict

4
[null]
[null]
Bad Request

5
102
C1
OK

Am I on the right path? Is this a workable solution or is there some other technique that would work better?
Does it make sense to work the queries out by status? Like, rather than try to process one record at a time, can I try to find all of the bad requests 1st, then all of the duplicates, then all of the new rows (and insert them)? I'm fine with this response, we can always reorganize them in the application code.

index
id
name
status

4
[null]
[null]
Bad Request

3
[null]
A1
Conflict

1
100
A1
OK

2
101
A2
OK

5
102
C1
OK

Believe me, I'm asking around about the requirement, but it seems stuck for now &emdash; they really want to provide the user with specific row-by-row feedback.
If I insert into a temporary staging table, can I run 3 set-based operations? One for bad requests, one for duplicates, and one for new records?


texts:
I am trying to create a schema for a mySQL database through my sites phpMyAdmin section. However when I try to run the following script:

CREATE SCHEMA `mydb`

I get this error and I am unsure what to do! I am very new to mySQL so any advice would be much appreciated. I am aware that it is some sort of permission problem?

ERROR 1044: Access denied for user 'user'@'%' to database 'mydb'


texts: mysql workbench *reverse engineer* is not connecting tables graphically. How to fix?

texts:
I thought that previously when I reverse engineered databases, mySql Workbench figured out the relationships from the id names and connected the tables leading to an awesome ERD, even when the foreign key constraint clauses don't exist and they are just used through the fields (xxxx_id) have the foreign key values.

However currently, when I pick various databases (I create them in rails) and try to import them I end up with this nasty looking smodge:

Instead of:


texts: Postgres Query not using Index but faster with Index

texts:
Sorry if this seems to be a duplicate question. I'm using Postgres 11.6 on AWS RDS. I have 2 tables:
CREATE TABLE public.e
(
    id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    p_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT e_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE public.ed
(
    e_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    <other columns + primary key>
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

I have an index on ed.e_id:
CREATE INDEX ix_ed_e_id
    ON public.ed USING btree
    (e_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

When I run this query:
select *
from ed, e
where e.id = ed.e_id
and e.p_id = '5c7cae8df6d10f1064b2eaf5';

(Problem persists when using from ed inner join e on e.id = ed.e_id)
The explain analyze plan is:
Gather  (cost=1136.68..141235.01 rows=28320 width=311) (actual time=0.456..871.155 rows=102709 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Join  (cost=136.68..137403.01 rows=11800 width=311) (actual time=0.241..688.095 rows=34236 loops=3)
        Hash Cond: (ed.e_id = e.id)
        ->  Parallel Seq Scan on ed ed  (cost=0.00..133210.10 rows=1544610 width=218) (actual time=0.005..314.524 rows=1235269 loops=3)
        ->  Hash  (cost=135.67..135.67 rows=81 width=93) (actual time=0.125..0.126 rows=81 loops=3)
              Buckets: 1024  Batches: 1  Memory Usage: 19kB
              ->  Bitmap Heap Scan on e e  (cost=4.91..135.67 rows=81 width=93) (actual time=0.045..0.097 rows=81 loops=3)
                    Recheck Cond: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
                    Heap Blocks: exact=31
                    ->  Bitmap Index Scan on ix_e_p_id  (cost=0.00..4.89 rows=81 width=0) (actual time=0.035..0.035 rows=81 loops=3)
                          Index Cond: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
Planning Time: 0.329 ms
Execution Time: 877.804 ms

With a Parallel Seq Scan on ed for the ed.e_id match.
When I SET SESSION enable_seqscan = OFF, the explain plan is:
Nested Loop  (cost=0.72..395895.14 rows=28320 width=311) (actual time=0.037..60.068 rows=102709 loops=1)
  ->  Index Scan using e_pkey on e e  (cost=0.29..917.61 rows=81 width=93) (actual time=0.019..4.995 rows=81 loops=1)
        Filter: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
        Rows Removed by Filter: 10522
  ->  Index Scan using ix_ed_e_id on ed ed  (cost=0.43..4757.83 rows=11844 width=218) (actual time=0.013..0.334 rows=1268 loops=81)
        Index Cond: (e_id = e.id)
Planning Time: 0.273 ms
Execution Time: 64.675 ms

A whole order of magnitude faster (877ms vs 64ms)! I tried VACUUM ANALYZE ed, but that didn't help. I even tried changing e.id & ed.e_id to UUID type, but that didn't help either.
How can I convince Postgres to use the ix_ed_e_id index without setting enable_seqscan to off?


texts:
I am using the mysql diff tool at http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldiff.html

It works great but I have to do this for each table

mysqldiff --difftype=differ --server1=PASSWORD@localhost --server2=root:PASSWORD@localhost pos.phppos_additional_item_numbers:compare.phppos_additional_item_numbers

Is there a way to have this repeat on all tables without writing a script?

If I remove the table names it just compare the name and NOT the schema of tables


texts: Log Reader Agent Blocking Distributor Agent T-log Stuck with log_reuse_wait_desc Replication

texts:
I've got a nasty SQL Server 2012 replication issue. I have a transactional publication going to a pull subscriber.
The log reader agent on the publisher has gotten stuck in a loop of:

  Delivering replicated transactions, xcount: 1 with an increasing command count messages

and

  The replication agent has not logged a progress message in 10 minutes

messages.

So the log reader agent seems to be working but nothing is being replicated to the distributors. All distributors state

  The process is running and is waiting for a response

or they timeout and try again.
When I run sp_whoisactive, I see the spid for the log reader agent for that database is blocking all the distribution agents from the subscribers of that database....yet the status of the log reader process is sleeping.  The t-log for the publication database is growing fast and eating up storage. It's stuck with log_reuse_wait_desc=REPLICATION, so I cannot shrink it. Anyone run into this before? Any suggestions on how to fix this? Appreciate the help. Thanks.


texts: Restore database from tape error 15105, cannot open backup device

texts:
I'm using HP Data Protector software to backup my SQL databases. When i try to restore a database from tape i get the following error.

When i click on script, it says this:

RESTORE DATABASE [ICT_TEST] FROM  TAPE = N'Data Protector_STD2008_ICT_TEST_213007077_0' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [ICT_TEST] FROM  DISK = N'E:\SQLbackup STD2008\ICT_TEST\ICT_TEST_backup_2015_02_27_224513_2053343.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

(I start SQL Management Studio as Domain Administrator)

Does anybody have a tip on how to resolve this issue?


texts: Primary Key issue in MemSQL doesn't support case sensitive

texts:
Creating table in MemSQL and creating composite primary key.
Lets create simple table with following structure:

Table User Data
Name userid

Primary Key (userid)

And assume application support case sensitive userid. So that following user id considered to be different: abc ABC

Insert following data:
1) JOHN ABC
b) ALEX abc

Insert fails in MemSQL as it consider 'ABC' same as 'abc'.

Please help me to understand if case sensitive primary key possible in MemSQL table or not.


texts:
Problem
I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the statistics objects for some of the clustered indexes is very skewed. Below is a snip of the top operators for the query, ordered by actual rows:

(source: imgh.us)
EDIT: Redacted the database name.
The third operator from the top (the one I have selected) is a clustered index seek, and when I look at the statistics object for that particular index I see an absurd amount of skew:

(source: imgh.us)
Is there a reason for this? I'm confused as to why SQL Server is only generating 3 steps for this histogram with a full scan. With 225,000+ rows in the table, I would expect to see a more steps with smaller ranges, but what I'm actually seeing is nearly every row included in one step. Rebuilding indexes and updating stats does not redistribute this.
In addition to that particular index, it appears many other clustered index statistics have a similar problem, and provide similarly bad estimations. Another operator in the above list is a clustered index scan on the WorkOrder table, and it also produces a relatively bad estimation. Though it appears this is the cardinality estimator picking 30% of the rows, so perhaps it is an issue with parameter sniffing (or something other than statistics).
Basically, my questions are as follows:

What should clustered index statistics look like?
Is the above distribution expected?
If it is expected, how do I work around these bad estimates?
If it is not expected, how do I correct these bad statistics?

EDIT2: Here is the XML Plan: https://www.brentozar.com/pastetheplan/?id=rJTjT4zze


texts: Modify Backup Directory Path Ola Maintenance Script

texts:
I have configured backup using SQL Server Maintenance Solution by Ola (https://ola.hallengren.com).

In the backup job, I specified BackupDirectory as a shared folder (eg: \comp1\path). It worked fine, but now I need to change the path. Assuming that I don't want to modify/recreate the jobs, is there a way to modify the path inside the sql jobs?


texts: MsSql express export from local pc to RDS, how to transfer NDF/LDF files?

texts:
First time mssql admin here. Never worked with mssql before I might asked question improperly.
Couldn't find any posts mentioning ndf/ldf files import to AWS RDS mssql database.
Is data from these files included in mssql backup or we need to import NDF/LDF files separately?
We are using latest Express db.


texts:
Behind the scene: Basically I operate an app that heavily relies on time calculation. We store unixtime in the database according to the user's timezone. Now to perform specific operations we need to convert that time from the native timezone to UTC. We recently transferred the application from another server It was working perfect but when we moved the application to a new server. The calculation that was written in Server Side Langauge was accurate but those calculations in which we wrote the logic in stored procedure output wrong date while converting it from unix_time to timestamp. Now you might question how do you know that output is wrong? Because I ran the same query over 2 different servers and the one that I am talking about returned wrong output.

The result from server having bug.

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 11:10:04

The accurate result:

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 05:10:04

If you put the same value of unixtime in the epoch converter (https://www.epochconverter.com/) you will get the value above mentioned select timezone America/New York.

I have also checked that clock of the host machine is fine. Please tell me how to resolve this error.
I am using MariaDB version 10.3


texts: Applying audit policy on multiple auditing subject or object

texts:
I have created a policy on execute category and I would like to know if this policy can be applied on multiple auditing subject or object. Since the execute category will generate many audit entries I would like to limit the results by applying this execute audit policy on specific user/table combination.

Is it possible?


texts: Is necessary to backup Google Firestore DB?

texts:
Im working a production app in Google's Firebase Firestore platform, have been development from a very long time (year and a half ago) and now that its in production I'm worried about backing up and data security at long term.

I know Google's infrastructure its First Class Quality, but I would like to know if somebody using Firestore actually has suffered some kind of data loss from provider or If you guys using Firestore practice periodically updates.

Thanks!


texts: Update and insert performance with partial indexes

texts:
I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query.
For example, the table similar to this:
CREATE TABLE public.contacts (
    id int8 NOT NULL DEFAULT ssng_generate_id(8::bigint),
    created timestamp NOT NULL DEFAULT timezone('UTC'::text, now()),
    contact_pool_id int8 NOT NULL,
    project_id int8 NOT NULL,
    state_id int4 NOT NULL DEFAULT 10,
    order_x int4 NOT NULL,
    next_attempt_date timestamp NULL,
    CONSTRAINT contacts_pkey PRIMARY KEY (id)
);

And there are two types of query:
SELECT * FROM contacts WHERE contact_pool_id = X AND state_id = 10 ORDER BY order_x LIMIT 1;

and
SELECT * FROM contacts WHERE contact_pool_id = X AND state_id = 20 AND next_attemp_date <= NOW ORDER BY next_attemp_date LIMIT 1;

For those queries I've created partial indexes:

For state_id = 10 (new contacts)

CREATE INDEX ix_contacts_cpid_orderx_id_for_new ON contacts USING btree (contact_pool_id, order_x, id) WHERE state_id = 10;

For state_id = 20 (available contacts)

CREATE INDEX ix_contacts_cpid_nextattepmdate_id_for_available ON contacts USING btree (contact_pool_id, next_attempt_date, id) WHERE state_id = 20;

For me, those partial indexes are faster than a single index.
And what about an update and insert performance? If I change a row with state_id = 20, will it affect only index 2 (for available contacts) or both of them will be affected?


texts: Testing multiple queries from input file

texts:
I have set of tables and need to execute select query based on different users. Table names are stored in file and I am looking forward to write a script to execute a script to read table name from the file and execute select query as specific user. Need to validate if the query was successful or failure.

Any suggestion on the tools for Oracle database / scripting(python or java) to be used for automation.

Thanks in advance.


texts: Mongo repair database "ERROR: error: exception cloning object in local.system.replset "

texts:
I'm trying to repair a mongo database and am running into the following error early on:

Wed Jul 24 16:55:21 [initandlisten] ERROR: error: exception cloning object in local.system.replset
E11000 duplicate key error index: local.system.replset.$_id_  dup key: { : "replicaset-name" }
obj:{ _id: "replicaset-name", version: 92, members: [ { _id: 16, host: "host1:27032" },
 { _id: 17, host: "host2:27032" },
 { _id: 18, host: "host3:27032", priority: 0.0, hidden: true },
 { _id: 19, host: "host4:27032", priority: 6.0 },
 { _id: 20, host: "host5:27032", priority: 7.0 },
 { _id: 22, host: "host6:27032", priority: 4.0 },
 { _id: 23, host: "host7:27032", priority: 3.0 },
 { _id: 24, host: "host8:27032", votes: 0, priority: 0.0 } ] }

(replica set and host names have been sanitized for public posting)

To attempt the repair I shut down the mongod and started it up with

/usr/bin/mongod-2.2 -f /var/local/mongo/mongod.conf --nojournal --repair --repairpath /mnt/mongo_repair/

My mongod.conf consists of just two lines:

dbpath = /var/local/mongo/data/replicaset-name
port = 17000

The .conf normally did have a replSet declaration, but this was removed for the repair as I'm trying to run the repair on a secondary.  (Per the docs repairs can only be done on primaries or standalones; so need to make it look like a standalone temporarily).

Any idea what the problem could be?


texts:
We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.

I tried to use the suggested proc in the article below, but when I run the proc, SQL complains that can't run sys.sp_OACreate and sys.sp_OADestroy which are called in this proc.

Do you know how we can turn on these components or know any better way to write to a file using T-SQL?

Thanks in advance.


texts: How to speed up datetime MySQL queries or improve db scheme for time series data?

texts:
I'm suffering a hard time because of a university project. The goal is to retrieve measurement values from a field test and to store them in a db. Upon those stored values calculations shall be executed...

The current DB scheme has one table for incoming data values, called data:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | bigint(20) | NO   | PRI | NULL    | auto_increment |
| data_node_id | bigint(20) | NO   | MUL | NULL    |                |
| timestamp    | datetime   | NO   | MUL | NULL    |                |
| value        | float      | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

This table holds about 1.7 billion records so far and is ~165GB in size.

Now I'm trying to do some simple queries to extract data for research like:
SELECT timestamp, value FROM iren2.data WHERE data.timestamp >= now() - INTERVAL 7 DAY AND data_node_id = 114
Those queries take more than 10 minutes to finish :( Is there any way to speed up things? Did I miss something in the scheme design?

By the way, the db is a MySQL 5.7 Community Edition running on a Ubuntu 16.04 HyperV machine with 10 vCores (Intel® Xeon® Processor E5-2698 v3) and 256GB Memory.

Thanks for your help! :)

EDIT
Some more information on config:

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 216G
innodb_thread_concurrency = 0
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048


texts: Aggregating spend by customer where accounts can be re-assigned to different customers

texts:
I want to join unique customer ids with their spends.

Problem: The account_no can be assigned to a different customer (A,B,C etc) at different point in time (assume an employee A leaving the organization and his account no is assigned to B).

Eg from the tables below: If a new customer, say B, is assigned an account number 123 (which was earlier assigned to A) on 1 Mar2015 then spend post 1 Mar2015 must fall in Bs remit til his account 123 is assigned to the next customer.

Tables to Join: Table 1 and Table 2

Desired Result: Table 3

Table 1: Assignment of account to different customers at different dates

Customer_ID   Account_no     Date
A              123           1Feb2015
B              123           1Mar2015
A              124           5Mar2015
C              123           1May2015
B              124           20May2015

Table 2: Daily level spend info

Account_no      Spend        Date
123             100         2Feb2015
123             200         2Mar2015
124             200         7Mar2015
123             200         17May2015
124             150         25May2015

Desired Output:

Customer_ID     Spend
A               100+200=300
B               150+200=350
C               200

The account No can be assigned twice, thrice or more to a different customer in case it is closed by multiple customers.

How do I aggregate the spend customer Id wise?


texts: Based on the result from the table, get the result from another table

texts:
I have two tables users and products:

1) users:

 id |  username    | ... |
--------------------------
 1  | user1name 1  | ... |
 2  | user2name 2  | ... |
 3  | user3name 4  | ... |
 4  | user4name 5  | ... |

2) products:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |
 3 |  product3name 3 |    2    |
 4 |  product4name 4 |    3    |

I want to get products based on the result from the users table. I have a user table connection with a product table.

My request:

SELECT P.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.USER_ID)
            WHERE LOWER(U.USERNAME) LIKE LOWER('%use1r%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID

What answer I expect to receive:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |


texts: migrate SQL Server geography data to PostGIS geometry data

texts: I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS.  I know there's a way to do the conversion outside of SSIS because I did it once a while back, unfortunately I don't remember what I did.


texts:
I've following tables with respective fields

1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data
2) wi_individual_p(ind_id,prg_id,...) Around 250K data
3) wi_training(trn_id,trn_start_date,trn_deleted,trn_beneficiary_type,...) - Around 25K data
4) wi_indv_training(ind_id,trn_id,is_deleted) - Around 450K data
5) wi_district(dst_id,dst_name) - Around 75 data

I was required to define a query to report unique individuals who are participated in the training within the given ranges of training dates (dst_name wise). So, I created following QUERY to fetch the records

SELECT
wi_district.dst_name,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2014-07-01' AND '2015-06-30' THEN wi_individual_g.ind_id
    END)) AS y3,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2013-07-01' AND '2014-06-30' THEN wi_individual_g.ind_id
    END)) AS y2,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2013-02-01' AND '2013-06-30' THEN wi_individual_g.ind_id
    END)) AS y1
FROM
wi_individual_g
    INNER JOIN
wi_individual_p ON wi_individual_p.ind_id = wi_individual_g.ind_id
    AND wi_individual_g.ind_is_recepient = 'yes'
    INNER JOIN
wi_district ON wi_district.dst_id = wi_individual_g.ind_district_id
    AND wi_individual_g.ind_deleted = 0
    INNER JOIN
wi_indv_training ON wi_indv_training.ind_id = wi_individual_g.ind_id
    AND wi_indv_training.is_deleted = 0
    INNER JOIN
wi_training ON wi_training.trn_id = wi_indv_training.trn_id
    AND wi_training.trn_deleted = 0
    AND wi_training.trn_beneficiary_type = 2
    AND wi_training.trn_start_date <= '2015-06-30'
GROUP BY wi_district.dst_name

The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE. This query is taking around 4 mins to execute and fetch the records. How can I apply other optimizations to the present query? Please provide me some solutions in MySQL!

The EXPLAIN for the above QUERY is:


texts: SQL Server 2012 backward compatibility for backups with 2008

texts:
I have a number of clients with SQL Server 2008 and that's what I have here on my server too. I use backup files to send databases back and forth between clients and at my office.

I have read that when you create a backup from SQL Server 2012 there is no way to restore it onto a 2008 instance. I assumed that the compatibility level would take care of this problem, but it doesn't. Therefore, I am at a loss as to how to upgrade. Other than upgrade all my clients all at once, which is impossible, I can think of no clean way to do this.

I have the need to send a database to a client as well as receive a database from a client. This is my first version upgrade on SQL Server, so I'm new to this problem. Any ideas on how to proceed?


texts: SELECT count(*) from a materialized view with unique index is slow

texts:
I have a materialized view with a unique index (on the id column)
and both of these queries are slow (they scan the whole table):

SELECT count(DISTINCT a.id) from the_view a;
SELECT count(*) from the_view a;

the plan is:

"QUERY PLAN"
"Aggregate  (cost=5303367.00..5303367.01 rows=1 width=0)"
"  ->  Seq Scan on the_view a  (cost=0.00..4953116.20 rows=140100320 width=0)"

I'm surprised that the index is not being used.


texts: Any Reason to have Regular Failover Clustering in Azure Cloud?

texts:
Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud?

I understand the benefits having Availability Groups in Azure Cloud for Database-Level protection. People can transition heavy Read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

However why would someone require Failover Clustering in Azure Cloud for Server-level protection? Don't Cloud Services inherently offer server level protection by itself? This is different to question which asks about Availability Groups: Any Reason to have AlwaysOn Failover Clustering in Azure Cloud? , this question is about FCI


texts:
I want to create a DB trace file using DEA(Database Experimentation Assistant). I am using below softwares:

-SQL Server Express 2012 on Win10 machine where my DB is present.

-DEA is installed on a separate win10 machine.

When I am trying to capture the trace using DEA, it is giving me below error:

  Unable to start the trace on the target SQL Server, please check if
  you have required permissions and SQL Server account has write access
  to the specified trace file path. | Sql Error Code (19062)

On both my machine, Windows user have admin rights
-'Alter Trace' permission is granted to that DB user, which I am passing to DEA.
-Using that user, I am able to access the DB using SQL Management Studio from the machine where DEA is installed.


texts:
When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify the default but can't seem to find how to do it.

Is it possible?


texts: MySQL database stops working after log enabled

texts:
I have this strange issue. I have MySql workbench, which I use to write queries for MySQL database (xampp 3.2.1). Everything works fine, but recently I found myself having to profile the queries my app sends, so in the config file (my.ini) I added this this line:

general_log             = 1

After adding this line, my server starts, I can connect to it via workbench, but when I try to get the data from my database

SELECT * FROM applicantsDB.Applicants;

I get

  Error Code: 1146. Table 'applicantsDB.Applicants' doesn't exist

While the same query succeeds if the logging is off. I can still get data from my other databases. What might be causing this?

EDIT now it doesn't even work when logging is off (other dbs are fine). Tried creating a new db and importing the schema from applicantsDB. Worked fine until I decided to restart the server. If I do the same without logging on, everything is fine (across restarts too) but when I enable logging, everything breaks.

EDIT V2
sample error log:

2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/applicants'
InnoDB: in InnoDB data dictionary has tablespace id 50,
InnoDB: but the tablespace with that id has name applicantsDB/applicants.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/files'
InnoDB: in InnoDB data dictionary has tablespace id 52,
InnoDB: but the tablespace with that id has name applicantsDB/files.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

EDIT V3
when I create the database with the name in lower case everything works fine. Still, this does not explain why enabling logging can break a database


texts: difference between phpmyadmin and php PDO


texts: Deleting bin logs in replication environment

texts:
I have a question about deleting binary logs in the replication environment:

We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy processing times, whereby bin log directory gets full. Logs are expired every 3 days. I was wondering, is there a reason why logs should be kept for 3 days on all the boxes – master and both slaves? Would it make sense to, for example, keep logs for 3 days on a master, but for 1 day on slaves? What is the best way to go about it?

Thank you!


texts: Can the service-broker cause SQL Server to crash when using SQLDependency

texts:
I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the production server the server crashed. I can not be absolutely sure that it is this because we picked it up ~10 minutes after I ran the application.
From what I understand from how the SQLDependency works it uses stored procedures to interact with the service broker.
The errors I received

SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
Could not find an entry for table or index with partition ID 428869310480384 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

We are running SQL Server 2005 SP1.
Edit:(removed code the link explains it better) I followed this example and mainly changed the query and what I do with the data.
http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/


texts: Is an ER diagram an example of a conceptual, logical or physical schema?

texts: I have been reading databases, and I clearly understand the difference between a conceptual, logical and physical schema. An ER diagram is an example of which of these schema's?


texts:
I am trying to connect to a local oracle on both windows and linux machines.
I am running the following command

echo Select count(*) FROM all_tables where owner='schema_name'; | sqlplus USER/PASS@localhost:1521

It works on windows but I get a syntax error on Linux. Can someone please fine tune my command to run on Linux?

This is the error I get:

  -bash: syntax error near unexpected token


texts: What is the safe way to update production data if data found inconsistent

texts:
I am in a small company and require to fix a bunch of data in production which are inconsistent.  I have written the script to handle the fix.

I understand that writing sql to fix data is way more risky than working on fixes normal front end or backend code. For front end or back end, we can write thousands of test code to automatically test result or even little bug the affect might not be as dramatic.

How do you guys fix the data in production normally if many data was inconsistent? Are there any method or strategy which can reduce the risk


texts: Calculating space required before a query is run

texts:
I have a Postgres database table that occupies about 20GB. I want run a simple aggregation query against the table (by simple I mean that the query selects from the one table only, doesn't involve any joins and just uses DISTINCT, MIN() and MAX()).
The query (when it works) takes about 20 minutes to run (this is fine) the problem is that it seems to fail if there isn't at least as much free-space available as the size of the table - i.e. 20GB of free space.
I've tried EXPLAIN, but I can't spot a way to extract a useful value (in terms of disk space) from the results. The first line of the EXPLAIN says 8 million rows, with a width of 61 - which seems very low when compared with the 20GB of space I know I need.
I've seen posts suggesting I should use EXPLAIN (BUFFERS, ANALYSE), but this doesn't seem practical because it actually executes the query.
How do I determine how much space a query will need before actually running the query?
The table is partitioned by month and holds data spanning about 3 years. The partitions are spread over two tablespaces (on separate hard discs). I'm not actually that bothered about how long the query takes, only how much space is required.
I have inserted the results of the query into a table, and it's sized at only 45MB, so it seems I need massive amounts of tempspace to generate a very small result set.


texts:
I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret   ||HeureDebut                 ||HeureFin                 ||EnumArret
2020-11-30  ||2020-11-30 14:00:00.000    ||2020-11-30 15:00:00.000  ||PS
2020-11-30  ||2020-11-30 16:00:00.000    ||2020-11-30 17:00:00.000  ||HI

i want to sum the datediff(HeureDebut,HeureFin) in columns for each EnumArret
so i run this query :
SELECT ArretProductionJournee.DateArret,
(select
sum (datediff(minute,ArretProductionJournee.HeureDebut,
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'HI')as HI,

(select
sum (datediff(minute,ArretProductionJournee.HeureDebut,
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'PS') as PS

FROM            dbo.ArretProductionJournee
                where ArretProductionJournee.EnumArret Like 'HI'OR
                ArretProductionJournee.EnumArret Like 'PS'
group by  ArretProductionJournee.EnumArret, dbo.ArretProductionJournee.DateArret

Result :
DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   NULL
2020-11-30  ||60   ||   NULL
2020-11-30  ||NULL ||   60

The result i want is Grouping the sum by the date and the enumArret:
DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   0
2020-11-30  ||60   ||   60


texts: NLS_ENV is not a valid job attribute Impdp oracle

texts:
I'm facing this error while importing database using data pump from 12c to 11g while importing job scheduler

ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27469: NLS_ENV is not a valid job attribute

So can anyone help how to solve this error

Source Database ->Standard Edition Release 12.2.0.1.0
Destination Database -> Enterprise Edition Release 11.2.0.4.0

I'm using version parameter in expdp command.


texts:
create table tab1(id int,name text);
create table tab2(id int,name text);

  create or replace function t_tab() returns trigger
as
$$
begin
insert into tab2
values (new.id, new.name)
return new;
end;
$$
language plpgsql;

create trigger t_tab
after insert on tab1
for each row
execute procedure t_tab();

The statement:

insert into tab1(1,'king');

inserts data in both tables tab1 and tab2.

Now my question is: I want same thing in tab2 table. When I insert data in tab2 table automatically insert same data in tab1. If I create same trigger function in tab2 then the execution unlimited. Is there any solution to fix this?


texts: Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle

texts: The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list?  How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?


texts: MySQL case sensitive table names on Linux

texts:
Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't find tables.

I have changed my /etc/mysql/my.cnf file adding the row:

lower_case_table_names=1

But that did not change anything.

My server version is:
5.1.61-0ubuntu0.11.10.1 (Ubuntu)

How can I configure MySQL to ignore case in table names?


texts: 10.4.10-MariaDB Column 'authentication_string' is not updatable

texts:
So I just installed mariadb in a new server as always, but it seems none of my commands I used to set root password and allow remote root logins work anymore and googling isn't helping me much.

mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;"

mysql -u root -e "use mysql;update user set authentication_string=password('password') where user='root';FLUSH PRIVILEGES;"

mysql -u root -e "use mysql;UPDATE user SET plugin='mysql_native_password' WHERE User='root';FLUSH PRIVILEGES;"

This is what I used on my ssh terminal to allow remote access to root using a password. How can I do this in this new version?


texts: Insert if names do not exist and return ids if exist

texts:
Suppose I have an array of string "a","b","c","d","e".

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

What will be the query to pass an array of strings and insert each if that name doesn't exist and return all IDs for the given array? In this example, it should return 1,2,3,4,5 as an output of SQL execution.


texts:
I'm doing a small crawl over multiple sites and I have a lot of links which are represented by ID's (example.com/foo = 354).

I'm currently storing the link -> link references and the link text. So in the following table page "2845" contains a link to 4479 with the text "About Us". Nothing big, just 3NF.

+----------+----------+-----------------+
| url_1_id | url_2_id | text            |
+----------+----------+-----------------+
|     2845 |     4479 | About Us        |
|     2845 |     4480 | Who We Are      |
|     2845 |     4481 | What We Do      |
|     2845 |     4482 | Core Principles |
|     2845 |     4483 | Research Staff  |
+----------+----------+-----------------+

However, by my calculations (most of the pages containing 500-1000 links each) I should have 6GB of link data by the time I've only parsed 200k pages.

Is there a better way to store link data? Especially if there is a good way to normalize repeated navigation menus for all site pages.


texts:
I have used the Streaming Replication guide on the PostgreSQL Wiki to configure replication between my two db servers.

But, when I updated a row on master server it is available on the slave instantly. It should be asynchronous, isn't it? What I am missing?

Servers are in separate places. The master's upload speed is ~75kb/sec. As far as I know, first of all, 16MB xlog file should be upload to slave, after that transactions available on slave. But it is happening in 1 sec. Why is it so fast?


texts: CASE statement and SARGability - specific use case

texts:
edit: SQL Server - I'm hoping this is a generic enough question I need not specify version, but most of the instances I'm working with are 2012 or later.

I am not good enough to mock up data and actually test this, so I'm hoping someone could look at it and answer with simple experience.

Imagine you have a state table with American state abbreviations in a column (and it's indexed, like a good lookup column). When writing ad-hoc queries, users will often hit up this column to filter on, but using criteria that represents information that is not implicit in the database.

For example, if they want to get "big states" they may include a filter in their ad-hoc query that shows something like

...
where
  StateAbbreviation in ('AK', 'TX')

aka the dreaded "Business Rules"

So this query is fine, it performs well, and makes use of the index. But, man, what a bear to write every time we need to query "the big states." I'm tempted to create a view with this filter in it's definition to make it easier on them.

The problem here is that those business rules are specific to some number of ad-hoc queries supporting a line of business, but don't really have universal uses. So creating a view that filters out data in this way will have little utility.

So instead of writing a view where that criteria is in the filter, I want to write a view where the criteria result is calculated, such as

select
  StateAbbreviation
  , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end
from tblStates

Now, when they want to write a query for big states, they just include

where IsBig = 1

in the query.

So, my question is simple - if the view were called with that criteria, can the index on StateAbbreviation be used?

I know there are all kinds of things I could do inside a CASE statement that may change the answer, so for the purposes of answering this very specific question, assume the case statement will only ever look like that. It will not make use of multiple fields, it will not aggregate - just a simple in or out calculation of literals to expose complex filter criteria to report writers in a simpler way.


texts:
We have a MDW Database in our PROD Server of size 40GB.

I can see all the collector Jobs are not running properly and the purging MDW job.

mdw_purge_data_[ManagementDWH]

is not running saying the owner does not have access to server.

When I access the Tables in MDW Database I can see all the tables are updated few months ago.

Can anyone help me with below queries :

Do we really need MDW Database ?
What if I delete my existing MDW Database (Unless we need to troubleshoot any performance issues) ?

Please let me know if is required to keep the MDW Database.

Thanks


texts: Is it good practice to check for existence of records before deleting them

texts:
I've came across the following SQL/PLSQL while investigating a stored procedure written by someone else:

1. V_COUNT NUMBER(5,0);
.........
2. SELECT COUNT(*) INTO V_COUNT FROM TABLE1 WHERE COL1='SOMEVALUE';
3. IF V_COUNT>0 THEN
4.   DELETE FROM TABLE1 WHERE COL1='SOMEVALUE';
5. END IF;

Isn't only the DELETE statement in the line 4 sufficient to cover all the logic that's meant with these 5 lines? I first thought maybe V_COUNT is used somewhere else, but it's not. I can't think of any good reason in making a simple delete statement so complex. Do you also think it's a bad design or is there a point that I can't see?


texts:
I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup performance is optimal. I am aware of implementing the below, however I'd like to get an idea of how I can improve beyond that scope.

Current Considerations:

Backup Compression
Multiple data files for larger databases - perhaps over 100GB?
Writing backups to another I/O subsystem
Defining values for parameters including BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT
Purging old files
Deleting backup history

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values? I appreciate it will be a bit of trial and error with testing but it would be useful to get an idea of best practices.

To give an idea of my routine, I'm to run hourly transaction log backups, daily differentials and weekly full backups.

Thanks


texts: Is there any way to filter or ignore messages written to log?

texts:
My database is behind a load balancer and even though the health check interval is set to 10secs, im getting multiple checks per second which result in thousands of "incomplete startup packet" messages written to the logfile.

This makes it hard to find errors when I have to scroll forever in the log file to find actual errors.

Is there any way to ignore just the message "incomplete startup packet" from being written to the log?


texts: How to handle schema changes in source systems when running an ETL?

texts: How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.


texts: User Password Expiry date is null in user_users?

texts:
Is it possible to find my password expiry date without asking the DBA?

My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is empty.


texts: How to include a semicolon (";") in a MySQL pager command

texts:
I've just discovered the pager command in the MySQL command-line client, and I'm having a tonne of fun. This blog post is great:

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Unfortunately as a Perl programmer I find myself wanting to do more and more complicated pagers, and I've hit into a problem: I don't seem to be able to use a semicolon character in my pager command.

Simple:

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> select id from listings limit 5;
cfbe10c0a46ea57f6fa199bc9e05ed3a  -

More complicated but still fine:

mysql> pager perl -nle 'print "    $_"'
PAGER set to 'perl -nle 'print "    $_"''
mysql> select id from listings limit 5;
    +---------+
    | id      |
    +---------+
    | 2003921 |
    | 7164746 |
    | 7166576 |
    | 7166605 |
    | 7166853 |
    +---------+

(That one is useful for dealing with Markdown!)

But unfortunately if I add a semicolon on the end of my statement it says I have a syntax error:

mysql> pager perl -nle 'print "    $_";'
    -> \c

mysql> pager perl -nle 'print "    $_";';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pager perl -nle 'print "    $_";'' at line 1

I've tried escaping the semicolon in the Perl command with a backslash or two (in case the backslash itself needed escaping) but nothing seems to work.

Is it possible to include a semicolon in a pager command?


texts:
I have created a linked server in SQL Server 2008 R2 Enterprise Edition for MySQL i.e. MySQL database is accessible under SQL Server environment. I have a created a trigger in SQL Server such that every new entry in the SQL Server object(table) also insert a new entry in the MySQL database object.

The issue here occurs with performance, that is without trigger insert in SQL Server is fast but when trigger is created the insert becomes very flow. The performance is degraded too much.

Please help me how to optimize the performance.


texts: SSRS 2008 to 2014 Migration Excel Export error

texts:
We are working on migrating our SSRS from SQL Server 2008 to SQL Server 2014.  After brining the reports over I have a set of reports that will execute just fine but when I try to perform a standard export to excel from the 2014 Server I get a message box saying:

  We found a problem with some content in ACM Consolidated
  Invoie_2014.xlsx. Do you want us to recover as much as we can?
  If you trust the source of this workbook, click yes.

However, I do not receive the same message when exporting to Excel from the 2008 server.  I believe the problem is related to some Numeric fields that are coming back with a value of 0.0000000000000000000.  Has something changed with the excel export function between these two versions?


texts:
I have a table that has a unique ID, name, email, and some other fields. I want to match this table against another table on email, but sometimes emails are shared. The unique ID is not shared among tables, but I will input the ID into the other table when I have identified them. Name is not unique.

I'd like to build two rounds of joins. The first round I would like to match on email and append the ID, and the second round would match on ID and update fields as necessary.

Is there a way to query into the table and grab only those that have unique emails, while ignoring those that share emails? I could then append the ID and re-query those that don't have IDs and join on the name. Would that even work?

Would this be a good use of a partition? I don't understand those well.


texts: Query to normalize table/combine row text

texts:
I have a table (call it oldTable) with columns like so:

ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)

The primarykey is multi-part: ID+Rank+TextLineNumber.

I'm trying to transform/join it into another table (call it newTable) with columns like so:

ID (int), Rank (int), CombinedText (varchar)

and the primary key would be ID+Rank.

ID and Rank on the new table are already populated, but I need a query that would update the CombinedText column of the newTable with the following considerations:

The Rank given on the new table may not exist on the old table, in which case it needs to pick the highest available rank from the old table that is not greater than the rank on the new table.
The CombinedText column is a string concatenation of the "SomeText" column from the old table, concatenated in order of "TextLineNumber" using the Rank found from the first consideration.

Here's some example data:

old-
http://i54.tinypic.com/jq0vmx.png

new-
http://i53.tinypic.com/dhfyn8.png

I'm using MSSql 2005 if that matters.
I currently do this using T-SQL and while loops, but it's become a serious performance bottle neck (taking about 1 minute for 10000 rows).

Edit: Expanded example data in CSV:
Old:

ID,Rank,LineNumber,SomeText
1,1,1,the qu
1,1,2,ick br
1,1,3,own
1,2,1,some te
1,2,2,xt
1,3,1,sample
2,7,1,jumped ov
2,7,2,er the
2,7,3,lazy
2,13,1,samp
2,13,2,le text
3,1,1,ABC
3,1,2,DEF
3,1,3,GHI
3,1,4,JKL
3,50,1,XYZ

New:

ID,Rank,CombinedText
1,2,some text
2,13,sample text
2,14,sample text
3,4,ABCDEFGHIJKL
3,5,ABCDEFGHIJKL
3,50,XYZ
3,55,XYZ

edit2:
Here's an example query that I found that does work but isn't fast enough (relying on multiple sub-queries):

update newtable set combinedtext =
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=1),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=2),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=3),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=4),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=5),'')

It also assumes a max line number of 5 which may not be the case. I don't mind hard-coding the linenumbers in all the way to a max of 20 if that's what it takes, but ideally it would be able to account for them differently. Getting execution time under 20 seconds (the actual data) is the goal...


texts: how to import .xls file into SQLite database?

texts:
I have an issue with importing .xls file into SQLite database. I am working on windows xp and using a SQLite manager for maintain the database. I can import the .csv file to the database by using availabel options in menu bar but I can't import the .xls file into this. I want to directly import the .xls file without converting into .csv file.

If I use the .csv file for import the data then it doesn't give the proper output due to comma separation.

Is there any way to import like this? Please give me the proper solution

I already googled but no solution I got.


texts: Replicate data of some tables of a local sql server to a database in Azure

texts:
We have a SQL Server which runs on a machine in our office with a database and tables which get written to daily.

Due to some business requirements we need some of these tables replicated on to a database on Azure. There will be not too many rows, only some hundred / thousand rows per day.

I have several ways to do this:

Create a job in our application which gets the data from the local SQL Server and writes it to the Azure DB via Entity Framework
SQL Server Replication (could this be used?)
Stretch Database (unfortunately not possible because some of the tables can't be stretched apperently..)
Linked server in the Azure-DB with a view that selects our local DB? (is this possible?)

Did you have such requirements before and what did you use to solve this?

What different option would you suggest?

Thanks in advance :-)

Additional infos:

SQL Server v13.0.4435.0
Microsoft SQL Server Standard (64-bit) Edition


texts: Can't get update one prompt for same variable over multiple tables

texts:
I've tried using the following:

select * from TABLE1 where COLUMN= :VAR
union
select * from TABLE2 where COLUMN= :VAR;

I'm getting the following error message back:

[Teradata Database] [TeraJDBC 14.10.00.26] [Error 3654]
[SQLState HY000] Corresponding select-list expressions are incompatible.

If I just run:

select * from TABLE1 where COLUMN= :VAR;

then it works, but I need to be able to answer one prompt for the same variable that is used across multiple tables. I'm using Oracle SQL Developer Version 4.0.3.16


texts: PostgreSQL surrogate keys: use sequence/bigint or OID?

texts: I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a sequence?


texts:
I have a massive table where a row is defined by two ids (Tid,Bid) that looks like this:

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  1  |  T  |   Insert |     NULL   |   50  |  10:11 |
|  1  |  T  |   Update |   Executed |   50  |  10:12 |
|  1  |  T  |   Remove |   Executed |   50  |  10:50 |
|  1  |  S  |   Insert |     NULL   |   10  |  10:10 |
|  1  |  S  |   Update |   Executed |   10  |  10:11 |
|  2  |  T  |   Insert |   Executed |   22  |  12:20 |
|  2  |  T  |   Remove |   Executed |   22  |  12:44 |
|  3  |  B  |   Insert |   Executed |   44  |  15:21 |
|  3  |  B  |   Update |   Executed |   48  |  15:25 |
------------------------------------------------------

From this table I want to select only rows with the latest time and have the status Executed and exclude all rows with the same ids if one of those rows contained the Action Remove, so the final resoult should look like this :

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  1  |  S  |   Update |   Executed |   10  |  10:11 |
|  3  |  B  |   Update |   Executed |   48  |  15:25 |
------------------------------------------------------

Currently my solution is built with multiple nested Selects and it dosent run very fast, is there a more elegant way to solve this?


texts:
I have to migrate working DB from old server (~50 Gb, mysql-5.5.43, openvz, all data in InnoDB) to a new hardware and new mysql version (mysql-5.6.28, debian). DB is "alone" - no replica servers. Dumps regulary made by mysqldump. Also software on PHP is running on server

Simple move using mysqldump isn't ok, 'cause restoring base is longs about 8 hours. I have no window of time for this...

So, there's a thought: make new server as a slave to existing server, wait till mysql replication sync is done,  turn off old server, make slave become master and continue working. Is this possible? Will be there any software-kind problems?

Or, this thought is wrong. If so, how, then, I have to migrate my DB to new hardware and software with minimal downtime?


texts:
I got this error. How can I resolve it?

  Failed to update database "msdb" because the database is read-only.
  Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
  Processed 184 pages for database 'BakupDB', file 'BakupDB' on file 1.
  Processed 2 pages for database 'BakupDB', file 'BakupDB_log' on file 1.

Sorry for the bad question.

I also get this error:

  Unable to open the physical file "C:\Users\geert\Desktop\C#proj\App_Data\ASPNETDB.MDF". Operating system error 32: "32 (failed to retrieve text for this error. Reason: 15105)". Cannot attach the file 'C:\Users\geert\Desktop\C#proj\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

This is my DB:


texts: Is it good to save Keywords stored by users in a new DB schema?

texts:
My question is more from the schema design and of further performance of the DB.

I'm designing a DB schema which will store strings primarily. Actually, users work on these strings, as normal mortals, in macro-enabled Excel workbooks and there, they have one column specifically designed to store keywords so they can find words within a second column quickly.

Some of these words are obviously deduced. For example, if column 1 is the keywords column and column 2 is the text column, column 2 could say "information required for feature description" and, its keywords in column 1 would be "information", "feature" and "description".

So far so good, common sense, but there are texts that have, for example, "N", "S", "W", "E", as the four points of a compass but in the keyword column, instead of having "North", "South" and so on, the keyword for all is just "compass".

We could ask for a keyword change but it would be a little troublesome, so my question is, is it worth saving these keywords in the schema, whether there's a table for them or not, or is it better to get the keyword as the result of a query from the texts stored?

We know working with strings is difficult, would these affect performance in the future?

BTW, if it helps, I'm thinking of using PostgreSQL.


texts: SQL Joins : How can i get latest record from 2nd table (one to many relationship)

texts:
I have two tables

1.Product  table having columns

prod_code
alert_limit
prod_description

2.Daily_data table having columns

prod_code
used_today
date
stock_left

How should i join these two tables to get recent records of each product on the basis of date ?
I have tried

SELECT p.*, d.*
FROM product p
JOIN daily_data d ON (p.product_code = d.prod_code)
LEFT OUTER JOIN daily_data d2 ON (p.product_code = d.prod_code AND
(d.date < d2.date OR d.date = d2.date AND d.prod_code < d2.prod_code))
WHERE d2.prod_code IS NULL;

but the above is giving me one recent row of the last product added


texts: Unable to set user/group as principal on a proxy

texts:
I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to these groups. I'm also trying to remove sysadmin role from users.

I've followed instructions from Grant Admin to an Active Directory account in SQL Server and Deny users permission to create tables in master.

After doing those changes, everything seemed working... until I tried to manually start a job. I got the message non-sysadmins have been denied permission to run dts execution job spets without a proxy account.

If I add the AD group to sysadmin it works. If I remove, it fails.

Following Running a SSIS Package from SQL Server Agent Using a Proxy Account I created a proxy. Then I started getting the message Unable to start execution of step 1 reason jobowner doesn't have permission to use proxy 1 for subsystem.

How to give SQL Server Job Owner permission to Proxy a Credential explains how to set a user as principal to the proxy. It didn't work. I tried adding the group and a user. But when I click OK and reopen the dialog, the one I added isn't listed anymore.

So, basically, how to grant a non-sysadmin user permission to execute jobs? Preferably granting it to an AD group instead of specific users.


texts: Sybase SQL - Truncation error occurred. Command has been aborted and find CACHE QUALITY

texts:
Need help with below code, as it fails with truncation error

Truncation error occurred.
Command has been aborted.

create table monCacheQuality (
        ServerName sysname
        ,CollectionDateTime smalldatetime not null
        ,PhysicalWrites decimal(15, 0) not null
        ,PhysicalReads decimal(15, 0) not null
        ,LogicalReads decimal(15, 0) not null
        ,CacheQuality decimal(15, 0) not null
        ,CacheHitPct decimal(15,4) not null
        )

-- Main code starts here
declare @physical_read1 decimal(15, 0)
    ,@logical_read1 decimal(15, 0)
    ,@physical_write1 decimal(15, 0)
    ,@cache_search1 decimal (15,4)

declare @physical_read2 decimal(15, 0)
    ,@logical_read2 decimal(15, 0)
    ,@physical_write2 decimal(15, 0)
    ,@cache_search2 decimal (15,4)

while (1=1)
begin
    select @physical_write1 = PhysicalWrites
        ,@physical_read1 = PhysicalReads
        ,@logical_read1 = LogicalReads
        ,@cache_search1 = CacheSearches
    from master..monDataCache

    waitfor delay '00:00:20' -- Log every 20 sec

    select @physical_write2 = PhysicalWrites
        ,@physical_read2 = PhysicalReads
        ,@logical_read2 = LogicalReads
        ,@cache_search2 = CacheSearches
    from master..monDataCache

    insert monCacheQuality
    select @@servername as ServerName
        ,getUTCdate()
        ,@physical_write2 - @physical_write1
        ,@physical_read2 - @physical_read1
        ,@logical_read2 - @logical_read1
        ,case
            when @physical_read2 - @physical_read1 = 0
                then - 1
            else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
            end as CacheQuality
        ,100-(((@physical_read2-@physical_read1)/(@cache_search2-@cache_search1))*100) as CacheHitPct
end


texts:
I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information).

My server lives on a cloud hosted server. I am now sitting with 2 issues because of the file size:

I pay for storage. I am happy to pay for my core business, but paying for silly data seems....silly
My backups are now huge also. I do nightly full backups, and then ship them off to an FTP server. This process is taking longer and longer.

Given my issues, is shrinking so bad? I will rebuild all my indexes.
I did a dry run, and brought my database down to 6GB.

This guy (who I have always trusted as my SQL guru), says it's bad....mkay
"SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance"


texts: Insert into value as generate row with different minutes

texts:
I try backup and restore using WAL-E/WAL-G.

I can backup and restore.

But i want check recovery_target_time.

During recovery, by default WAL-E will attempt to pull and restore all WAL segments available. If you wish to restore to a specific POINT IN TIME than specify.

# restore to 4:38PM on 3/6/2012
recovery_target_time = '2012-03-06 16:38:00'

I think need generate value every minute long time and Insert into table.

Found insert events with CURRENT_TIMESTAMP

INSERT INTO events (ts,description) VALUES (CURRENT_TIMESTAMP,'disc full');

Found insert SELECT NOW()

INSERT INTO indexing_table(created_at) SELECT NOW() FROM generate_series(1,100);

This is no answer :(

How build table?

         created_at
-------------------------------
2019-02-13 14:16:20.799219+03
2019-02-13 14:17:20.799219+03
2019-02-13 14:18:20.799219+03
2019-02-13 14:19:20.799219+03

or

    created_at
-------------------
2019-02-13 14:16:20
2019-02-13 14:17:20
2019-02-13 14:18:20
2019-02-13 14:19:20

Need generate row with different minutes.

Any help would be appreciated in creating these tables!
Thanks!


texts: Query To Show All Users Who Have Multiple Managers

texts:
I have a database that holds user information.  I need a way to find out what users have multiple managers.  Below is a subset of data to illustrate the data structure.
Using MS SQL Server 2016 what would be a query to show me all userid and managerid where for each user the count of managerid > 1?
Declare @@Test Table
(
    userid varchar(100),
    mngrid varchar(100)
)

Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')

Select * FROM @@Test


texts: Semi-Annual archiving of SQL Server database

texts:
Twice a year we create a static, archived subset of our production database.  The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied over).  Also, several records are not included (records marked as inactive, etc.)

Once created the archive db is set to read-only access.  From the front-end, users are able to switch from the production db to any one of the read-only archives.  The archives remain available indefinitely.

The data is in SQL Server 2008 R2.  What is the best way to automate this type of situation (we've done it several different ways in the past, but have not settled on a single, straight-forward approach.)


texts:
I've used the following to grant all privileges on mydb to myusername:

GRANT ALL PRIVILEGES ON mydb.* to myusername @'%' IDENTIFIED BY 'mypassword';

I thought that the % is a wildcard for all hosts. It worked for a while, but then I had some permission errors when the user tried to access the db from the same machine. I added the following lines:

GRANT ALL PRIVILEGES ON mydb.* to myusername @'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* to myusername @'127.0.0.1' IDENTIFIED BY 'mypassword';

And now the user can access the database.

What does the % stand for? Is there a wildcard for all hosts?


texts: How to change "tmpdir" on a running MySQL instance

texts:
Is there a way to change the tmpdir on a MySQL server without restarting it?
The issue is that I use a tmpfs mounted at /var/lib/mysqltmp but sometimes it's overflowing and causes issues. I don't want to restart the server, because that means around 2-3 hours of service degradation.

Another possible exit from this situation is to unmount the tmpfs, but trying it causes this error:

 [root@db2 /]# umount /var/lib/mysqltmp/
 umount: /var/lib/mysqltmp: device is busy.
    (In some cases useful info about processes that use
     the device is found by lsof(8) or fuser(1))


texts: Return rows one at a time from variable table

texts:
I have a database which contains job numbers, each job has a list of invoices and a list of journals. Its an IRQ request accounting system.
In order to see which jobs have a zero balance I'm subtracting a sum of values from another sum of values. I'd like to see the result per job number.

I currently have a query:

 DECLARE @job_nos AS TABLE ( job_no INT )

    INSERT  INTO @job_nos ( job_no )
            SELECT  jm_job.job_no
            FROM    [FCU_LIVE].[dbo].[jm_job]
                    INNER JOIN [FCU_LIVE].[dbo].[xe_log] ON ( CAST(jm_job.job_no AS VARCHAR(8000)) = xe_log.keystring )
                    INNER JOIN [FCU_LIVE].[dbo].[jm_job_status] ON xe_log.to_value = jm_job_status.status_code
                    INNER JOIN [FCU_LIVE].[dbo].[jm_division] ON jm_job.division_no = jm_division.division_no
                    INNER JOIN [FCU_LIVE].[dbo].[sys_phone_list] ON jm_job.cust_id = sys_phone_list.list_id
            WHERE   field_id = 'active'
                    AND jm_job.division_no = 4
                    AND jm_job.billing_type = 'I'

    SELECT  ( SELECT    SUM(jm_installment.billing_amount)
              FROM      [FCU_LIVE].[dbo].[jm_installment]
              WHERE     jm_installment.job_no IN ( SELECT   job_no
                                                   FROM     @job_nos )
            )
            - ( SELECT  SUM(jm_wo_transaction.billing_amount)
                FROM    [FCU_LIVE].[dbo].[jm_work_order]
                        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                                                                           AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                WHERE   jm_work_order.job_no IN ( SELECT    job_no
                                                  FROM      @job_nos )
                        AND jm_wo_transaction.override = 'N'
              ) AS WIP

Which gives me a single amount. The TOTAL sum of all job numbers returned by the multiple variable.
How can this be changed to output, the sum result for each job number on a separate row?

I'm blindly using this approach after googling possible options. If you have a better approach overall please let me know.


texts: Couchbase 4.5.1: What is beam.smp and why it is consuming too much memory

texts:
We are having a single node Couchbase [4.5.1-2844 Community Edition (build-2844)].

For some reason, we are seeing high memory usage on the server. When we log into the server and view the stats we can see that memcached and beam.smp service is using the memory.
I need to know, what is the use of beam.smp process, and why it is consuming high memory.
Screenshot for the same is attached to the question. Also I have followed the following links to debug the issues:

https://forums.couchbase.com/t/beam-smp-memory-usage/11843/2

https://issues.couchbase.com/browse/MB-20521


texts: Convert SQL Server UPDATE Statement for Use in Oracle

texts:
I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server.

I am looking for some guidance on how to convert it.

UPDATE
    SOB
SET
    COA =   CASE
                WHEN
                    (
                        SELECT COUNT(*)
                        FROM SOB
                        WHERE
                            ORD = T.ORD AND
                            SHP_KEY <= T.SHP_KEY AND
                            SHP_DTTM <= T.SHP_DTTM AND
                            SOB_KEY <= T.SOB_KEY
                    ) > 1 THEN 0
                ELSE 1
            END
FROM
    SOB T
WHERE
    COA IS NULL


texts: Foreign key constraints with MySQL/InnoDB

texts:
I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL.

I assume that they effect inserted/updates to a degree however I am not to worried about that in my testing.  I was wondering if they effect SELECT statements and if they effect data/index size?


texts: Remove field from composite key and collate duplicate data

texts:
I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again.  The modification I'm going to make is going to remove part of the primary key, which will result in tens of thousands of rows which need to be collated.

The part of the key being removed is a date field, which originally was needed to help uniquely identify incoming data.  However, the way this field is assigned at the source (outwith my control) has since changed and we now want to only keep the earliest occurrence of a particular record and discard the later occurrences.

The rest of the fields in the key (4 FK's) will produce unique records.

I'm assuming I'll need to transfer the data to another table, make the change and then re-insert?  Or is there a better way?


texts:
I have a VM 2012 R2 server with SQL Server 2014 Express SP2 installed. There are some applications and databases, but nothing too big. Databases are small, about 100MB size combined.

At some moment (not from the beginning) the SQL Server process started to show constant high CPU usage. Usually the CPU load is about 15-20%. It shows a periodic picture with a minute of 15-20% load, and a couple of seconds of 0% usage between the periods. Sometimes SQL Server stops to do that (whatever he does...) for like 2 minutes, then starts again with 10-20 seconds of >95% load and "usual" 15-20% after...

Of course it affects performance for users.

I have investigated all I could (session reports, query reports, profiler, memory allocation SP...), but I haven't found anything that could cause this. Or, exactly, I've found there are no sessions, no queries and nothing in the actual applications installed that could load the CPU so much. I tried to shut down all applications using SQL Server and detach their DBs and then restart the server. No use.

I even uninstalled and reinstalled SQL Server, but it started to consume CPU short after startup, while being "clean".

Also, there was no "unusual" configuration, it was just a clean VM with a straightforward SQL Server installation. I have installed it, created the DBs, logins and that's it.

From the Troubleshooting SQL Server high CPU usage by Karthick P.K, I've checked the possible issues with timer, that can be caused by power plan or other settings. Using -T8038 at startup did not affect the CPU load. Power plan is set to high performance.

Why is SQL Server using so much CPU, even if there are no queries and attached DBs? How can I at least learn what's happening?


texts: Performing SELECT on EACH ROW in CTE or Nested QUERY?

texts:
This is a problem in PostgreSQL

I have a table which stores the tree of users;

    +------+---------+
    |  id  | parent  |
    |------+---------|
    |  1   |   0     |
    |------|---------|
    |  2   |   1     |
    |------|---------|
    |  3   |   1     |
    |------|---------|
    |  4   |   2     |
    |------|---------|
    |  5   |   2     |
    |------|---------|
    |  6   |   4     |
    |------|---------|
    |  7   |   6     |
    |------|---------|
    |  8   |   6     |
    +------+---------+

I can query a complete tree from any node by using the connectby function from the tablefunc extension, and I can separately query the size of tree in terms of total nodes in it, for example

  tree for #1 has size 7
  tree for #5 has size 0
  tree for #6 has size 2, and so on

Now I want to do something like Selecting all possible trees from this table (which is again carried out by connectby), count the size of it and create another dataset with records of ID and size of underlying tree, like this:

    +------------------+-------------+
    |  tree_root_node  |  tree_size  |
    |------------------+-------------|
    |      1           |     7       |
    |------------------+-------------|
    |      2           |     3       |
    |------------------+-------------|
    |      3           |     0       |
    |------------------+-------------|
    |      4           |     3       |
    |------------------+-------------|
    |      5           |     0       |
    |------------------+-------------|
    |      6           |     2       |
    |------------------+-------------|
    |      7           |     0       |
    |------------------+-------------|
    |      8           |     0       |
    +------------------+-------------+

The problem is, I am unable to perform the same SELECT statement for every available row in original table in order to fetch the tree and calculate the size, and even if I could, I dont know how to create a separate dataset using the fetched and calculated data.

I am not sure if this could be simple use of some functions available in Postgres or I'd have to write a function for it or simply I dont know what exactly is this kind of query is called but googling for hours and searching for another hour over here at dba.stackexchange returned nothing.

Can someone please point to right direction ?


texts: Job waiting for resources - is it better to kill it or let it wait

texts:
Our server is very busy sometimes and I can see my jobs are waiting for resources. Is it better to kill it and start it after e.g. one hour or let it wait until it finishes?
Right now there is a rule that kills the job if it has not finished within a specified amount of time and then it tries this four times. I think it is best to let it wait, because when it gets killed it looses its place in the "line" and when it starts again it has to go to the end of the queue.
Extra info: the jobs are from ETL with analytical queries and the big jobs have many joins on large tables. Tableau also sends queries directly to the server...
Thanks


texts: Clustered Index fragmentation vs Index with Included columns fragmentation

texts:
I have a relative large table (for me)  with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign).
Tip
--------------
Id          int (clustered index)
UserId      int
TipIndex    smallint
Value       binary(8)
LastChanged datetime2(3)

every user has between 1 to 400 tips that will be inserted at random during this period
UserId + TipIndex is unique
I never query Tips directly on the Identity Key (Id)
99% of the times I query on UserId
I need all columns
I often query on 1 UserId (per page view), sometimes on a batches of 10.000s for statistics
this is a high traffic site during this period and it should be able to handle 30.000 queries on UserId per minute
Id is at the moment my Clustered Index, because I read that it leads to the least fragmentation.

So I'm hosting on SQL Azure and Azure already recommended adding an Index with included columns.
I was always hesitant on using UserId,TipIndex as a Clustered Index, as Tips will be added at random. Meaning that I'm afraid of huge fragmentation problems etc.
My questions:

Doesn't an Index with included columns have the exact same problem?
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
How to prevent fragmentation?

I know in the end the answer is always "depends" or that I should measure it. But as I'm a solo developer and not with a lot of resources I am hoping on someone with more experience that has a gut feeling for this, so my first attempts have a higher chance of going in the right direction.


texts:
I've built a script/Agent job that goes and polls our linked servers every 10 minutes to make sure they are online.  Now, when one goes offline, It generates an email and notifies me. See below:

Now, right now it just uses a pass/fail.  What I want to do is, when communication between servers goes offline, to generate not only that it's offline, but also the reason why.  Similar to the read out you get from SSMS when you test a linked server and it's offline:

Is this possible?  I've searched for any additional verbose parameters that may help me accomplish this but haven't been able to find any.  The information gets generated in SSMS so that leads me to believe that there is some way to get it, I'm just not sure how.

Thanks as usual guys!

UPDATED
Had to add some system specific functionality to this as well as my own SQL that I had already built but error reporting is online.  Works with our SQL servers and our iSeries/400 environments.  Here's what it spits out now:

Thanks again for all the help on this one.  Might have to post this out to SSC so others can use it.


texts: Can I set up Replication on a clustered Instance of 2 Nodes. If yes then is it mandatory to have a 3 separate Instances for Distributor, Publisher and lastly Subscriber . If there is a fail over then will the replication break ?


texts: OLEDB provider for linked server reported change of schema version error

texts:
I am trying to run a report but its giving this message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ParamYear'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

I checked in Visual studio and I get this error:

    OLEDB provider for linked server sql10 reported change of schema version between
compile time("182424452472301") and run time ("182454520418731")
for table dbo.stud

Query:
SELECT DISTINCT Funding_Type
FROM            Destinations_1415_1516_Union
WHERE        (Year IN (@Year)) AND (AgeBand IN (@AgeBand)) AND (Completion_Status IN (2, 3))

Kindly let me know how to resolve this


texts: Database design for dynamic Meta fields of an entity that shall be queried intensively

texts:
I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, boolean or string) are dynamically entered by admins.

At the end of the data population, the admin will be able to create queries to filter out the entities based on meta values.

Normally, I would follow wordpress's DB structure of having meta table of the following structure.

Meta => id, entitiy_id, meta_key, meta_data_type, meta_value

However, I'm concerned about the performance and the query-ability of the data since I expect it to be more intensive. Not to mention that I'll be using mysql for the DBMS.

Please advice if there is a better practice than this. If not, is there any pitfalls that I should take note of before I develop this structure.


texts:
I have two tables, one (ARTISTS) with a list of different artists, details and biographies, but no images. I have another table (ARCHIVECATALOGUES), full of auction results, which have the images I need. I want to generate a query which will give me all of the detail from the ARTISTS table, and include an image (does not matter which one) from the other table for each of the artists.

So I need a query which INNER JOINs the two tables. The query below is pulling every result from the ARCHIVESCATALOGUE table, i.e. multiple results per artist. But I only need one result per artist. I used DISTINCT on the artist.ArtistID field, but to no avail. Here is the code:

SELECT DISTINCT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
FROM artist
INNER JOIN ArchiveCatalogues ON (ArchiveCatalogues.ARTIST = artist.surname)
    AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
WHERE artist.surname >= H *
    AND artist.surname < I
GROUP BY artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    ,ArchiveCatalogues.AUCTION
    ,artist.ArtistID
    ,ArchiveCatalogues.IMAGER

Perhaps I should be using OUTER JOIN?


texts: Open encrypted trigger of table in database (MS SQL Server Management Studio)

texts:
Microsoft SQL Server Question for Trigger in database-tables.
Is there a possibility to view Code behind a encrypted trigger of a database table?
I only see a key inside the Icon and rightclick "Change" is Grey.

Need some help please


texts: Output from `pg_dump --schema-only` has inconsistent order

texts:
I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers:
2896c2896
< CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model_run FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
---
> CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
2900c2900
< CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
---
> CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model_run FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');

It seems the ordering is getting mixed up between model and model_run because of the underscore character.  A previous question/answer here seems to indicate the collate value may be the culprit.  A post on nabble.com makes me think this is a somewhat-known issue.

Does the collate value of the DB affect pg_dump's sorting of the schema output (ie, not just the data)?
If I re-create the DBs using lc_collate='C' will that force a standard ordering for non-alphanumeric characters?

Below is the current list of the 2 databases:
gcis=# \l+ gcis_21*
                                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
--------------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 gcis_2180    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 35 MB | pg_default |
 gcis_2190    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 35 MB | pg_default |

Footnote: The schema comparison is actually being done automatically on the fly through Module::Build::Database from CPAN.  The full command being used is
pg_dump -xOs -E utf8 -n $database_schema $database_name
Though utf8 is explicitly passed as the encoding here, the database itself
is also currently UTF8.


texts: How to back out of mass update in SQL Replication

texts:
We have a database which is set up in Merge Replication.

We accidentally updated hundreds of thousands of records in a large published table on the publisher database.

We did is something like this:-

update LargeTable set Field=Field

So basically, every record got updated to be the same - no data was actually changed. However, every record in the table is now being delivered to every subscriber and the whole system has come to a halt. We are very worried this will take days and days to complete.

How can we safely remove data from the msmerge_contents and msmerge_genhistory table? Are there other system replication tables to remove records from? I can see the exact records in these tables that refer to the mass update. I just don't want to remove them without knowing it is safe to do so.


texts:
Do I need a quorum drive for configuring Always On on SQL Server 2014?

I am sure it is not mandatory as the 'last man standing' concept is based on node and disk majority. But I need a little more knowledge on this.