texts: On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?

texts:
we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to poorly designed db architecture) that, each, suck away dozens of gigabytes of RAM from our servers and have execution plans in the range of megabytes stored in SQL cache.

We would like to restrict certain users from using SORTs and HASH MATCHes, specifically ORDER BY, SELECT DISTINCT and JOINs of any kind. Our intention is to let users pull their data, but work on it only on their side.

Could this be achieved in SQL Server?
Is there any DB engine that supports that?
What would be your thoughts about this?

I know this would beat the principle of a relational database, but we're looking for some way to enforce that the engine is properly used and not abused by uneducated users.

We are already considering the implementation of Resource Governor to allocate a certain amount of machine resources for these bad queries.


texts: MySQL: Recover Master from Slave after failover

texts:
I have replication running between two MySQL instances on two separate servers. Let's say M is the master and S is the slave.  It's setup as one way replication M->S.  I currently have S as read only and writing binary logs with the --log-bin option.  Suppose I have to failover to S because M goes down. What are the steps to recover M such that it is in sync with S again? And so S can become the SLAVE again?

I have seen the following thread but they depend on circular replication:
https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave
https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave

Steps I have so far to promote S to master:

STOP SLAVE IO_THREAD;
SHOW PROCESSLIST; check 'has read all relay log' status
STOP SLAVE;
SET GLOBAL read_only=0;
UNLOCK TABLES;
RESET MASTER;

UPDATED LIST OF COMMANDS:

On Master:
CHANGE MASTER TO MASTER_HOST=slave.com, MASTER_LOG_FILE=MASTER_LOG_FILE, MASTER_LOG_POS=MASTER_LOG_POS, ....
START SLAVE;
# Wait till caught up;
STOP SLAVE;
RESET MASTER;

On Slave:
CHANGE MASTER TO <original slave settings>;
START SLAVE;
USE mydb;
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only=1;


texts: Oracle not able to add Default value in PL/SQL

texts:
I trying to create table in PL/SQL, the problem is the at DEFAULT ' '.

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||' (STORE VARCHAR2(4) DEFAULT ' ' NOT NULL)';

END CREATE_TABLE ;

Error:

   PLS-00103: Encountered the symbol " NOT NULL)" when expecting one of the following:

  * & = - + ; < / > at in is mod remainder not rem return
  returning <an exponent (**)> <> or != or ~= >= <= <> and or
  like LIKE2_ LIKE4_ LIKEC_ between into using || bulk member
  SUBMULTISET_
  The symbol "*" was substituted for " NOT NULL)" to continue.

But i can add the default if i do it in sql tools. How can i add the default value in pl/sql?

CREATE TABLE Inventory
(
  STORE VARCHAR2(4) DEFAULT ' ' NOT NULL,
)


texts: Prepare statements plan invalidation in postgresql

texts:
Postgresql caches plans for prepared statements and their subsequent executes. see:
https://www.postgresql.org/docs/current/sql-prepare.html
https://stackoverflow.com/questions/7142335/how-does-postgresql-cache-statements-and-data

The question I have is what will destroy/re-write/clear postgres has cached? Naturally, any DDL statement (such as drop, alter etc.) would do. But are there other things?


texts: Shouldn't DISCARD PLANS re-evaluate every function definition in postgres?

texts:
Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view.

So far so good.

The I decided that the table column "booking_date" should not be a timestamp, but instead a date:

ALTER TABLE booking ALTER booking_date TYPE DATE;

This booking table had views over it that I previously dropped (also triggers, but those I kept as it had no effect).

Then, when I try to recreate the materialized view (that uses those above mentioned functions), I get the following error message:

Message    : ERROR: date out of range for timestamp
  Where: PL/pgSQL function calcfee(booking) while storing call arguments into local variables

But if I execute a second time, everything works as expected.

Then I read about Plan Caching, and it starts to make sense - the functions are evaluated at session start (in this case, flyway running migrations), and since the underlying table has changed, the cache should be invalidate.

Hence, I tried (as described here):

DISCARD PLANS;

To no result.

However, this brute-force approach did the trick:

CREATE OR REPLACE FUNCTION public.recreate_functions()
  RETURNS void AS
$BODY$
declare f record;
BEGIN
  -- query postgres for 'user functions definitions'
  FOR f IN select pg_get_functiondef(pp.oid) as fdef from pg_proc pp inner join pg_namespace pn on (pp.pronamespace = pn.oid) inner join pg_language pl on (pp.prolang = pl.oid) where pl.lanname NOT IN ('c','internal') and pn.nspname NOT LIKE 'pg_%' and pn.nspname <> 'information_schema' LOOP
    EXECUTE f.fdef;
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- DISCARD PLANS;
SELECT recreate_functions();

Why is that? What am I missing here?

Thanks


texts: Will Postgres handle the Unix Epoch problem?

texts:
I was wondering if Postgres will be handling the unix epoch problem coming in 2038? I have read about this and am wondering.

It's about a productivity thing obviously because it is so far away, but I am curious.


texts: Hi, I have sitution where my Database has corrupted but some how we have recovered it but while restoring the .bak file it shows an error

texts:
this is the error show while restoring the corrupted file .. Pls help if any one know
Thanks


texts: Postgresql group by and count() problem, missing row with 0 result

texts:
I have a problem with group by and count().
I need a result table looks like below.
Tried I guess all the variations of JOIN.

There are two types of wrong result:
- first when the row "3 C 0" is missing,
- the second when it is present but with a false value "3 C 1"

Table1:

id name
1  A
2  B
3  C
4  D

Table2:

id
1
1
2
1
4

result should look like this:

id name count
1  A    3
2  B    1
3  C    0
4  D    1

Any ideas?


texts:
I have a FrontBase database that I am trying to restore. This is a database system I have no experience up until now so this may be a basic question. I am issuing the following command to restore

c:\usr>FrontBase\bin\FrontBase.exe -console -create -restore=c:\usr\B_2017_03_08-20_36_14 MyDB

And get the error

2017-03-16 11:28:38 FrontBase Server - 8.2.8 64-bit on Windows NT/2000
2017-03-16 11:28:38 Cannot determine keys identity, Wrong magic number - not a backup file
2017-03-16 11:28:38 Failed to restore database from the backup

The backup was actually created on a Mac, I am not sure if the format is portable.

If anyone has experience with Frontbase I would love to hear from them, it doesn't seem to be a common DB.


texts: How to remove database from explorer after deleting .MDF and .ldf files

texts: I wanted to know if I can delete or remove the database shown in the object explorer after someone  has manually deleted the MDF and ldf files from the database folders.


texts: Sql Server 2016 errror Incorrect syntax near 'EXTERNAL'

texts:
I have two SQL Server 2016 VMs in Azure. I am trying to connect these two sql server instances using External datasource. I can safely connect the two instances from my local pc, remotely.
While querying using external table/data source, I am getting this:

Kindly note

i) I have used both RDBMS/SHARD_MAP_Manager

ii) Credential is valid credential

iii) I know, i can use Linked Servers, but i don't want it. As i am planning to move these databases to Sql Azure Db. External Table and datasource is not mandatory. You can suggest alternatives other than LinkedServers.

So i need a query or part of query that will work with both, Sql Server(Virtual/Physical server) and Sql Azure(Db as a Service)

Update

Same query ran flawlessly on Sql Azure db.


texts:
I can't understand why the document say "PostgreSQL is an object-relational database management system"?Can somebody tell me the reason, or give me some article about this question.

Thanks a lot.

My experience:

I'm a beginner of database(and also beginner of postgresql. :D）.
I implemented a service that provide several restful APIs for app. The logic of APIs all implement in plpgsql function, and app access them through nginx.


texts: Data lost when enabling bi-directional master/slave on MariaDB

texts:
I have master/slave setup with two MariaDB 10.x instances. They were bidirectional but for the past two months it was only one direction.

When re-enabling the bi-directional it looks as if the master (where slave was being enable) lost data on some tables for exact period of time it was not setup as a slave. These are InnoDB tables, replicated with statements.

The master, where slave was being enabled, appears to be rolling back to an earlier snapshot - on a table by table basis. The resultant effect was a set of record disappearing from the table up to a certain PK as well as PK counter was set back to the same position.

The exact period of time lost for the effected tables was two months. The binary logs were expiring after 3 days.

I haven't found any documentation which shows this possibility, maybe somewhere in the replication code?


texts: SQL Server 2019 (RTM) - 15.0.2000.5 period has expired after upgrade to CU 8

texts:
Hi i have MS SQL with version
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

When i use this version and performed Log shipping, there is an error and i check on microsoft doc, the error because bugs in sql and i need to upgrade the sql server with Cumulative Update that i download in here https://www.catalog.update.microsoft.com/Search.aspx?q=sql%20server%202019
i use the latest CU. at first everything was fine until about 60 days i get error that says SQL SERVER Evaluation period has expired. And this happened in 3 servers (Production, DR, UAT). after several weeks after upgrade to CU8, got period has expired.
is it normal? because when doing installation, what i do is next and next


texts: How can I verify that all possible FLOAT(53) values convert to unique BINARY(8) values?

texts:
Part of our application code converts data types to BINARY for the purposes of hashing. For example, we convert BIGINT values to BINARY(8). The documentation warns that conversions may change between versions of SQL server:

  Conversions between any data type and the binary data types are not
  guaranteed to be the same between versions of SQL Server.

As a defense against that, whenever we start supporting a new version of SQL Server we try to run tests to validate that all of our conversions are still valid. For something like BIGINT, we check the length of the converted value for the minimum and maximum values allowed for BIGINT. That hopefully means that we would know if, for example, SQL Server 2019 started to require BINARY(9) to fit all possible values of BIGINT.

How can we do this type of analysis for FLOAT(53)? Right now we think that all possible values map to unique values that fit in a BINARY(8), but I don't know how to validate that. This may not be as simple as just checking the number of bytes required for the data type. For example, the TIME data type requires 5 bytes for storage but must be converted to a BINARY(6) to avoid errors. Perhaps this is irrelevant, but it also makes me nervous that BINARY cannot be converted back to FLOAT. I admit that I might be overthinking the problem, so I welcome frame challenges as answers.

How can I write code to validate that all possible inputs for FLOAT(53) do not overflow a BINARY(8) and that two different FLOAT(53) values do not convert to the same BINARY(8) value?


texts: Add user to Server Role in a transaction

texts:
Situation

One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning database roles, etc.

Now that we are implementing another feature, we would like to assign each new user the pre-defined server role bulkadmin. For this, there exists a wonderful SP sp_addsrvrolemember.

When executing this, though, we get the error

The procedure 'sys.sp_addsrvrolemember' cannot be executed within a transaction.

The webapp that creates the other webapps uses transactions for each SQL query it executes and it would involve a lot of customizations and exceptions to handle this.

Question

Is it in another way (or in this way, with some modifications) possible to achieve this, i.e. granting a user the bulkadmin right?

The sp_addsrvrolemember has hard-coded that @@trancount should be 0, so that's not going to give me a solution. Google and SO have not been really helpful either, or I haven't been able to find it. Usually the answer is "don't use transactions", which is not an option in our case.


texts: Specify different log file locations with Hallengren scripts

texts:
I appreciate Ola Hallengren scripts.  Being new to SQL Server, they've made my life so much easier.  Thank you Mr. Hallengren.

I have 5 user databases that I need to back up at separate times, so instead of using USER_DATABASES, I'm providing the database name.  When I created the stored procedures with the script, I specified the Output File Directory as something like G:\Logs\Backups. The log files are there, but it would be great to either have the database name in the logfile name, or pass the logfile path as a parameter.

I'm also logging to dbo.CommandLog. It would be nice to identify the log file that I'm looking for without looking inside.

Is there a way to do this, without changing maintenance_solution.sql?

I don't like the idea of modifying because when an update comes out, those would be gone.  To me, the scripts are quite complicated, as I'm coming up-to-speed with MS SQL and T-SQL.

Any insights are appreciated.

Sherrie


texts: Can someone explain the result set from sys.dm_tran_locks?

texts:
I am trying to understand if the result I am seeing is normal for a long running process or an indication of a problem.

When I run
select * from sys.dm_tran_locks

I am getting over 60k rows showing all from different session ids.

resource_type = PAGE
resource_description = same value
resource_associated_entity_id = same value
request_mode = S
request_status = GRANT

I'm trying to understand if this means that the query currently running needs tuning or if it is normal to see this much rows showing different 'request_session_id' with a shared lock on the same object?


texts:
Why search by date field requires engine to touch all records? Is there any way to avoid it?

Following query

EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ;

shows that amount of rows required to generate the result is 2725 which is higher than total amount of records in the table (2349).

Question #1: Why amount of rows to touch is higher than amount of records?

Question #2: Why amount of rows to touch is so high even though there is an index on the 'realdate' field? Is that really means query will touch all records? Should I optimize it? If yes, how?

Please advise! Any feedback is welcome! Thank you!

P.S. I use MYSQL 5.1.43 version, my table has realdate field, its type is 'DATE', table has totally 2349 records.

P.P.S. Answering to one of comments: selecting only realdate field reduces amount of touched records to 1362... if I vary date value used in search, amount of touched records changes depends of amount records left in the selections...

Hope, that additional information will help.


texts:
I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    -- Some assignment to v_ip_address

    v_tmp_host := v_ip_address::scan.location.alias%TYPE;

Since the code was working for a long time, I investigated and found out, that "scan" is a schema in the database this trigger exists in and someone created a database called "scan" some days ago. Since now "scan" is a db as well as a schema, pg seems to use the db instead of the schema.

My question is, how can I avoid such problems in future? How should I write this code to don't get broken by creating additional relations, databases or schemas?

Or in other words: How can I force pg to use the schema and not the db in this situation?


texts: Postgresql on AWS RDS JDBC Connection Refused, psql connection succeeeds

texts:
I have an Amazon RDS Postgres instance running. I can see the data from my local machine (whitelist office IP) and our web app can connect (running on EC2 instance).

On that EC2 instance, these things happen:

[ec2-user@ip ~]$ nc -zv mydb.us-east-1.rds.amazonaws.com 5432
Connection to mydb.us-east-1.rds.amazonaws.com 5432 port [tcp/postgres] succeeded!

[ec2-user@ip ~]$ psql postgres -h mydb.us-east-1.rds.amazonaws.com -U userguy
Password for user userguy:
psql (9.5.4, server 9.6.1)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \q

[ec2-user@ip myjars]$ java -jar MyJar.jar param1
Connecting to jdbc:postgresql://mydb.us-east-1.rds.amazonaws.com:5432/postgres
org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

I know this EC2 instance can connect to the DB. The nc command works and the psql command works. We have a Glassfish application server that connects as well.

Same parameters are in this JAR file and it does not work! Any thoughts? Any ideas to diagnose? (as I type this, I feel it will be some file version or mistyped value in the JAR file, since everything else is working)


texts: Ignore login mappings during Dacpac compare

texts:
We're trying to automate the deployment of databases through development, testing and production. Our approach, using SSDT, is to compare DACPACs from the different environment to generate update scripts, and as a final step, map the correct windows logins to the correct users.

/p:IgnoreUserLoginMappings

Looking at the documentation for SqlPackage.exe there is a property IgnoreUserLoginMappings that seems to imply that it would ignore mappings, so that they are not included in the dacpac and hence not updated during the comparison and script generation.
But, when we use this property during our extract the users generated are WITHOUT LOGIN, like this in the dacpac:

<Element Type="SqlUser" Name="[Domain\Account]">
  <Property Name="IsWithoutLogin" Value="True" />
    <Relationship Name="DefaultSchema">
      <Entry>
        <Annotation Type="PersistedResolvableAnnotation" Name="[Domain\Account]">
                <Property Name="TargetTypeStorage" Value="SqlSchema" />
        </Annotation>
         </Entry>
    </Relationship>
</Element>

which is NOT what we want! The subsequent generated update looks something like this:

CREATE USER [Domain\Account] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Domain\Account];

Are we doing something wrong or is this a bug, I've searched and can't see much info on this property, is anyone using it that can help, or is there another approach?

Related question: here


texts: Union implemented with Hash Match operator

texts:
I was reviewing the SQL Server physical operators listed on TechNet (don't judge, you know you've done it) and read that the Hash Match physical operator is sometimes used to implement the UNION logical operator.

I have never seen that done and would like to learn more.  An example query would be great.  When is it used and when is it better than the alternatives?  (Those are usually the same, but not always.)


texts:
I have a MySQL db.t2.small in AWS, the server isn't in my country, but I get 70ms. Now I need to insert over 7000 records in a table, but it takes a lot, over 6min. I tried many configurations:

-query_cache_size=16777216
-query_cache_type=1
-net_write_timeout=300
-max_allowed_packet=536870912
-innodb_lock_wait_timeout=3600
-innodb_flush_log_at_trx_commit=2 (Also I tried setting it to 0).

I tried sending the requests 1 by 1 and also using bulk, but the result is the same, almost 20 insert per sec.

What am I missing?

My Java bulk code:

try {
        conn = bd.getConectionAWS();
        PreparedStatement stmt = null;
        if (conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
        try {
            String query = "INSERT INTO CONTRATO(Codigo,Nombre,IdEstado,DesEstado,FechaInicio,IndActivo,Activo,Descripcion,Cliente,Responsable) VALUES(?,?,?,?,?,?,?,?,?,?) \n"
                    + " ON DUPLICATE KEY \n"
                    + " UPDATE Nombre=?, IdEstado=?, idEstado=? ,DesEstado=? ,FechaInicio=?, IndActivo=?,Descripcion=?,Cliente=?,Responsable=?";
            stmt = conn.prepareStatement(query);
            for (Contrato contrato : listaDatos) {
                stmt.setString(1, contrato.getCodigo());
                stmt.setString(2, contrato.getNombre());
                stmt.setInt(3, contrato.getIdEstado());
                stmt.setString(4, contrato.getDesEstado());
                stmt.setTimestamp(5, contrato.getFechaInicio());
                stmt.setString(6, contrato.getIndActivo());
                stmt.setString(7, contrato.getActivo());
                stmt.setString(8, contrato.getDescripcion());
                stmt.setString(9, contrato.getCliente());
                stmt.setString(10, contrato.getResponsable());
                stmt.setString(11, contrato.getNombre());
                stmt.setInt(12, contrato.getIdEstado());
                stmt.setString(13, contrato.getDesEstado());
                stmt.setTimestamp(14, contrato.getFechaInicio());
                stmt.setString(15, contrato.getIndActivo());
                stmt.setString(16, contrato.getActivo());
                stmt.setString(17, contrato.getDescripcion());
                stmt.setString(18, contrato.getCliente());
                stmt.setString(19, contrato.getResponsable());
                //          stmt.executeUpdate();
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
        }

I took a few things from https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

My "1 by 1" is the same, just replaced addBatch for executeUpdate();.

A SELECT 1; query takes 0.059sec.


texts:
I am using MySQL database with PHP's Yii Framework, It is an old version of MySQL, It can't be updated to latest one due to some reasons.
I have created this query to get Current month records of current year. This query was working fine on my local system, I have around 10,000 records, But when i made it to production server, it was also working fine but after 1 month, Records reach upto 1 million in that table, So query is taking more than 10 seconds, when there is huge traffic on the app, its makes the server slow, mysql is taking high CPU. But when records are less, The query is working fine, Otherwise query is taking so much time to return records, sometimes it takes like 25-26 seconds.
Here is my query
SELECT SUM(`coins`) as coins, subscription_id
FROM `user_rewards` `t`
WHERE YEAR(date_awarded) = YEAR(CURRENT_DATE)
  AND MONTH(date_awarded) = MONTH(CURRENT_DATE)
GROUP BY subscription_id
ORDER BY coins DESC LIMIT 10

Is it possible to optimize this query more in a better way?
This is my user_rewards table
id, subscription_id, coins, date_awarded

A single subscription_id can have multiple records thats why I sum and do group by in my group...
Below is table schema.
CREATE TABLE `user_rewards` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_awarded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `subscription_id` bigint(20) NOT NULL,
 `coins` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_sub_id` (`subscription_id`),
 CONSTRAINT `fk_sub_id` FOREIGN KEY (`subscription_id`) REFERENCES `subscription_info` (`subscription_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12907 DEFAULT CHARSET=latin1


texts:
RMAN gives me this error how should i resolve this issue:

    ORA-19815: WARNING: db_recovery_file_dest_size of 7516192768 bytes is 100.00 used, and has 0 remaining bytes available.

Thanks in advance


texts: How can Excel communicate with a remote SQL Server that is disconnected from the Internet?

texts:
My setup involves three computers:
1. An application sits on an Internet-connected Windows Server in the Cloud.
2. This application is connected to a SQL Server instance on another Windows Server in the Cloud, which is disconnected from the Internet
3. I have an Excel sitting on a third computer.

How can Excel send data to the Windows Server database?

One working option is to use Dropbox to synchronize text files between the Excel and the Internet-connected Windows Server, and then to use sqlcmd on the Internet-connected Windows Server to run these text files on the database.

However this solution is not ideal for a number of reasons!

Any better way? As far as I know I can't open up SQL Server to the Excel - using such approaches as described in https://leansoftware.net/Help/Excel-Database-Tasks/Worked-examples/How-to-connect-Excel-to-Remote-Web-Server-SQL-Data.aspx - because my SQL Server is not connected to the Internet.

Note that the SQL Server is the Express edition (we hope to upgrade to Azure in the not-too-distant future)


texts: Insert with different key values to avoid contraint violation

texts:
I have a set of Inserts traced of a database and I want to issue them into another similar one.
The problem is that I'm getting contraint violation errors due the similar database already has the keys i'm trying to insert.

INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");

Error:

  *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
             For Trusted Oracle configured in DBMS MAC mode, you may see
             this message if a duplicate entry exists at a different level.
  *Action:   Either remove the unique restriction or do not insert the key.

My question:
Is there a way to avoid this problem and tell the database to insert another key value if it already exists? It's important also to keep the values of other columns..

I have searched already for some possible solutions and the best approach would be to use the PL/SQL Exception to handle that. I just can't see how to do that until now.

DECLARE
BEGIN
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
exception
   when DUP_VAL_ON_INDEX then
   --do the insert. but how?
END;

Any suggestions?
Thanks in advance.


texts: MySQL/MariaDB stored procedure inserts even with syntax error

texts:
createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail?

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      (SELECT
        id
      FROM
        Role
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      createdAt <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    ) ;
END $$

DELIMITER ;


texts:
I have a SQL Server 2014 table that looks like the following:

OrderId     int           not null IDENTITY --this is the primary key column
OrderDate   datetime2     not null
CustomerId  int           not null
Description nvarchar(255) null

Some folks on my team have suggested that the clustered index should be on OrderId, but I think that the CustomerId + OrderId would be a better choice for the following reasons:

Almost all queries will be looking WHERE CustomerId = @param, not OrderId
CustomerId is a foreign key to the Customer table, so having a
clustered index with CustomerId should speed up joins
While CustomerId isn't unique, having the additional OrderId column
specified in the index will ensure uniqueness (We can use the UNIQUE keyword when creating the clustered index on those 2 columns, to avoid the overhead of not having uniqueness)
Once data is inserted, the CustomerId and OrderId never change, so these rows wouldn't be moving around after initial write.
Data access happens via an ORM that requests all columns by default, so when a query based on CustomerId comes in, the clustered index will be able to provide all columns without any additional work.

Does the CustomerId and OrderId approach sound like the best option given the above? Or, is OrderId on its own better, since it's a single column that's guaranteeing uniqueness by itself?

Currently, the table has a clustered index on OrderId, and a nonclustered index on CustomerId, but it's not covering, so since we're using an ORM and all columns are requested, it's extra work to retrieve them.  So with this post, I'm trying to consider improving performance with a better CI.

The activity on our DB is about 85% reads and 15% writes.


texts: Postgresql after 'for each row' triggers don't actually execute after each row?

texts:
If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted or updated.

Why is that? And is there a way to prevent it from happening?

begin;

create table foos (state text not null);

create function f() returns trigger as $$ begin
  -- This seems to be ran after all the rows are inserted or updated?
  -- expected it to run once for each row as it was inserted or updated.
  raise notice '%', count(*) from foos where state = 'initial';
  return null;
end $$ language plpgsql;

create trigger f after insert or update on foos for each row execute procedure f();

insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';

Expected Output:

psql:/tmp/t.sql:14: NOTICE:  1
psql:/tmp/t.sql:14: NOTICE:  2
psql:/tmp/t.sql:14: NOTICE:  3
psql:/tmp/t.sql:14: NOTICE:  4
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  4
psql:/tmp/t.sql:15: NOTICE:  3
psql:/tmp/t.sql:15: NOTICE:  2
psql:/tmp/t.sql:15: NOTICE:  1
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Actual Output:

psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Edit:

Using a before trigger behaves differently (and how I would expect it to behave):

begin;

create table foos (state text not null);

create function f() returns trigger as $$ begin
  raise notice '%', count(*) from foos where state = 'initial';
  return new;
end $$ language plpgsql;

create trigger f before insert or update on foos for each row execute procedure f();

insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';

Output:

psql:/private/tmp/t.sql:14: NOTICE:  0
psql:/private/tmp/t.sql:14: NOTICE:  1
psql:/private/tmp/t.sql:14: NOTICE:  2
psql:/private/tmp/t.sql:14: NOTICE:  3
psql:/private/tmp/t.sql:14: NOTICE:  4
INSERT 0 5
psql:/private/tmp/t.sql:15: NOTICE:  5
psql:/private/tmp/t.sql:15: NOTICE:  4
psql:/private/tmp/t.sql:15: NOTICE:  3
psql:/private/tmp/t.sql:15: NOTICE:  2
psql:/private/tmp/t.sql:15: NOTICE:  1
UPDATE 5


texts:
When running show warnings in mysql, sometimes the results give a row number, such as "Warning | 1265 | Data truncated for column 'identifier' at row 12343".

I'm interested in doing something like:

select identifier from tablename where rownum = 12343

but of course this fails because there is no column named rownum. The table does not have an auto_increment column. I tried:

alter table tablename add column rownum int auto_increment unique first

but when I ran the select query again, the result did not seem to match the warning (it was a 9-character value in a varchar(12) column).

What's the best way to see the data referenced by mysql warnings? In this question I'm not so much asking about why the error is occurring, but am asking about how to view the data that is triggering the warning.


texts: Possible to query all SQL Server Indexes which have page locks enabled?

texts: Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?


texts: Suddenly have to rebuild indexes to prevent site from going down

texts:
I have been having this problem for a while:
https://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column

It seems all is good and when I suddenly get a lot of writes to the table the index seems to die and queries take up to 2 seconds. This always happens when I've got a lot of traffic so the knock on effects pretty much bring my database server down.

If I rebuild the index all is good again. What could be causing these sudden 'breaks' in the index? How can it be prevented?

table def:

+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | int(11) unsigned      | NO   | PRI | NULL    | auto_increment |
| user_id       | mediumint(8) unsigned | YES  | MUL | NULL    |                |
| created       | int(11) unsigned      | YES  | MUL | NULL    |                |
| track         | int(11) unsigned      | YES  | MUL | NULL    |                |
| prop          | int(11) unsigned      | YES  | MUL | NULL    |                |
| sequence      | text                  | YES  |     | NULL    |                |
| location      | varchar(255)          | YES  |     | NULL    |                |
| type          | tinyint(1) unsigned   | YES  | MUL | NULL    |                |
| flagged       | int(11) unsigned      | YES  | MUL | 0       |                |
| status        | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| featured      | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| recommended   | tinyint(1) unsigned   | YES  |     | 0       |                |
| rendered      | tinyint(1) unsigned   | YES  |     | NULL    |                |
| fms_id        | varchar(32)           | YES  | MUL | NULL    |                |
| comments      | text                  | YES  |     | NULL    |                |
| tv            | tinyint(1) unsigned   | YES  |     | 0       |                |
| performers    | varchar(255)          | YES  |     | NULL    |                |
| comp_34_votes | int(11) unsigned      | YES  |     | 0       |                |
| comp_35_votes | int(11)               | YES  |     | 0       |                |
| comp_36_votes | int(11)               | YES  |     | 0       |                |
+---------------+-----------------------+------+-----+---------+----------------+

and the query that suddenly gets slow (if i remove the orderby it's not slow):

SELECT sql_no_cache `p`.`id` as performance_id, `p`.`performers`, `t`.`name` as track_name, `p`.`location`, `p`.`fms_id`
FROM (`performances` p)
JOIN `tracks` t ON `p`.`track` = `t`.`id`
WHERE (p.status = 1 OR (p.status != 2 && p.flagged < 3))
AND `p`.`prop` IN ('1', '2', '3', '4', '5', '6', '8', '10', '11', '13')
AND `p`.`track` IN ('17', '9', '5', '15', '2', '3', '8', '6', '12', '4', '1')
AND `p`.`type` IN ('1', '0', '2')
ORDER BY `p`.`created` desc
LIMIT 11, 12

and the explain:

+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref                            | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | p     | index  | track,prop,flagged,status,type | created_desc | 5       | NULL                           |   45 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                        | PRIMARY      | 4       | db_123.p.track |    1 | Using where |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+


texts: Changing hostname IP address in mongodb configuration

texts:
My mongodb sharded cluster is set with 1-member replica set for config server with 6 shards, 1 mongos. All these components are hosted on a single server with mongos on 0.0.0.0:27017 and rest on 127.0.0.1. All addresses have been configured through hostnames in /etc/hosts i.e 127.0.0.1 as configrs , 0.0.0.0 as mongos and bindIP in mongo config holds these hostnames and not IP addresses.

I want to host a mongos on separate server. Since config server was hosted on 127.0.0.1:27018, to make it reachable I stopped the config mongo, updated /etc/hosts contents from 127.0.0.1 configrs to 0.0.0.0 configrs and restarted the config mongo. But now, rs.status() returns

  "state" : 10,
    "stateStr" : "REMOVED",
    "uptime" : 12,
    "optime" : {
        "ts" : Timestamp(1521449062, 2),
        "t" : NumberLong(3)
    },
    "optimeDate" : ISODate("2018-03-19T08:44:22Z"),
    "ok" : 0,
    "errmsg" : "Our replica set config is invalid or we are not a member of it",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"

How do I move my config server from 127.0.0.1 to 0.0.0.0? I went through hostname change documentation for mongo, but it lists down steps to change hostnames whereas I am not changing hostname.


texts: Is there a way to execute SQL statement based on SQL Server version?

texts:
I would like to create computed index on a table if SQL Server is 2008 or newer and a simple index if SQL Server is 2005 or older:

-- check for sql server version
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10
        CREATE unique nonclustered index ix1_table
            ON table (column1, column2)
            WHERE column1 is not null and column2 is not null
        ELSE
            CREATE nonclustered index ix1_table
                ON table (column1, column2)

The problem is that the whole statement is evaluated and on SQL Server 2005 this throws an error:

  Incorrect syntax near the keyword 'WHERE'.

Is it possible to somehow create different index based on SQL Server version?


texts: MySQL: with CREATE_USER_PRIV, GETTING "Access denied" when attempting to change another user's password

texts:
In MySQL 5.6.27, user 'bmcrae' has create user privileges and is able to create a new user.  However, when trying to change another user's password:

 SQL: SET PASSWORD FOR 'newuser' = PASSWORD('***************');

I am getting an "access denied" error:

  Access denied for user 'bmcrae'@'%' to database 'mysql'

I have re-read the relevant MySQL documentation and not found anyone else having such issues.  Any advice?


texts: Create stored procedure inside try-catch block

texts:
I'm using sqlserver 2014 and trying to create a StoredProcedure inside try-catch block like this:

BEGIN TRY
  CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT ''
END TRY
BEGIN CATCH
  ...
END CATCH

It fails to run with the error of: Incorrect syntax near 'SELECT' Expecting EXTERNAL.
Then i changed sp like: CREATE PROCEDURE [ammeghezi1] (@w INT) AS BEGIN SELECT '' END (cover sp with BEGIN-END block), But the error did not change. I also add GO after BEGIN TRY statement and it just got worst.
I'm getting to conclude that creating sp inside TRY_CATCH block is not practical.

Is this even possible to create sp inside TRY_CATCH block? And how?


texts: SQL DBA data type is changed after upgrade

texts:
We have recently upgraded our SQL Server Database from SQL 2005 to SQL 2014.
We are experiencing slowness in both SSMS and application as well while executing a SP in SQL 2014. After checking through the sql profiler.
This is the difference.

Source Server : SQL 2005
JDBC VERSION  : 4.1
Source Data type : Big Decimal
Conversion on DB side : It is converted as Numeric (Checked through sql profiler)
Execution Time : 1 Second

The below target is the server we have upgraded from 2005.

Target Server : SQL 2014
JDBC VERSION  : 6.2
SOURCE Datatype : Big Decimal
Conversion on DB Side : It is converted as Decimal
Execution Time : 25 Seconds.

Any idea what I can do to fix this?


texts: the cache hit rate using pg_statio_user_tables

texts:
I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure that 20% is accessed in the cache (main memory) as opposed to disk. Ultimately, you want to have a cache hit rate of 99%. The blog provides this command to find the cache hit rate:

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;

Ok, so I am expecting a number close to 99%, but now this is the number I got:

#   pg_statio_user_tables;
   heap_read   |   heap_hit    |         ratio
---------------+---------------+------------------------
 2214811975866 | 1791164728361 | 0.44712310145763220493
(1 row)

0.44? That is not even close to 99%. That must mean that most of the hits are happening on disk and the cache is virtually nonexistant. What could be causing this issue?


texts:
Our InnoDB/Mysql5.6 database is growing at a fairly steady pace of doubling in size every year, currently at 22Gb. This is sales data; we have a number of merchants each generating millions of rows, all stored in one logical instance. The report queries are served by a few read replicas for load balancing and redundancy with the queries distributed randomly.

The database is hosted in AWS/RDS. We outgrew the 4Gb instance, then 8gb and currently reside in a 16Gb node. We expect to last until the end of the year. While AWS offers "memory optimized" instances we feel this will just defer the problem but not solve the bigger picture. Instead of scaling up, we would like to use a number of smaller instances and serve merchant specific data. We can partition the data the "hard way" where instance A only holds merchant A data and instance B only holds merchant B data and so on. The biggest drawback of this is that in case of an instance downtime we cannot just route traffic to another instance since there is no cross-account data sharing.

So I want to ask if anyone can comment on "logical partitioning" which means that all servers still hold all the data (disk space is not a problem) but queries related to one account are "sticky" i.e. routed to the same server (or server pool) to maximize cache hits. That way if a DB instance, zone or region goes down any other instance elsewhere can pick up the work of the down-instance since the same data was distributed everywhere.

In your opinion, would logical partitioning based on repeated queries to the same set of instances reduce the need for memory, assuming all the queries use indexes where the account ID is the first field in every composite key?


texts: Slow MySQL InnoDB performance of table with 100 million rows

texts:
I have the database in this format.

CREATE TABLE `dna` (
 `p_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dna_bits` bit(32) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`p_id`),
 KEY `dna_bits` (`dna_bits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Currently, I have 100 millions rows and I need to query quickly by dna_bits which I have an index on.

My query looks like

select CONV(dna_bits, 16, 2) as bits, user_id from dna where dna_bits in (b'010101', b'0101011', ...)

Searching 10000 dna_bits in where clause took me over 8 seconds.

EXPLAIN on my shorten* query showed this.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  dna range   dna_bits    dna_bits    4   NULL    77  Using where

I'm running MySQL server on my iMac 3.2 GHz Intel Core i5 with 16GB RAM.

What can I do to speed up the process?

Thanks.


texts: How to prevent different connections treating bytea in same query differently?

texts:
I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.
A query that demonstrated the behaviour is this:
INSERT INTO "TestTable" ("Input") VALUES (decode('74657374', 'hex'))
74657374 is hexadecimal for 'test'. In one client, 'test' is inserted into the "Input" field, whether that field is text/varchar or bytea. That is the behaviour I desire. In another client, '\x74657374' is inserted into the "Input" field, whether it is text/varchar or bytea. This string is the postgresql literal representation of the bytea bytes of ASCII 'test'. But the sql literal syntax itself is inserted. I do not desire this behaviour.
I use a single piece of hand-written SQL, the bytea value only occurs "within" the query (if column "Input" has type Text then none of literals nor the receiving column have bytea type), and it seems unlikely to me that either client is parsing then rebuilding the query. Therefore it seems that the difference must be happening on the server where the query is executed. That means there must be some connection specific configuration setting which is altering the server behaviour.
Can anyone please tell me what connection specific settings could be altering this behaviour?
I know the queries are really behaving differently and it is not a display issue etc., because I can see the rows in the same table having different values ('test' and '\x74657374'). I have tried various alternative bytea handling methods, but they are all affected by this problem. For those who are interested, the "good" client is pgAdminIII and the "bad" client is Ruby PG gem. Though for the reason I gave above I believe there must be some built-in feature of postgresql supporting this behaviour.


texts: Does the SQL-1992 standard restrict naming identifiers to 18 characters?

texts:
In this draft of SQL:1992, I found…

Section 5.2

  Leveling Rules

  ....

  The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:

  a) No <regular identifier> or <delimited identifier body> shall contain more than 18 <character representation>s.

…and…

  Annex A.1

  Subclause 5.2, "<token> and <separator>":

  a) In conforming Intermediate SQL language, a <regular identifier> or a <delimited identifier body> shall not comprise more than 18 <character representation>s.

  b) A <delimiter token> shall not be a <bit string literal> or a <hex string literal>.

  c) Conforming Intermediate SQL language shall contain no <identifier body> that ends in an <underscore>.

For implementors of SQL, or for users of SQL?

I searched the document multiple times, but could never get a sense of the meaning of identifier.

Is the spec referring to labeling within an implementation of SQL (such as PostreSQL or Oracle)? So labels used within the SQL product cannot contain identifiers that are over 18 characters or end in an underscore.
Or is the spec referring to users of a SQL implementation? So we who are naming our schemas, tables, columns, and other such database objects cannot use names over 18 characters or end in an underscore?


texts: How can I generate a script to choose only top 10 items from each table in SQL database?

texts: I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements?


texts: Connect to Database Engine Prompt on Script Object

texts: Every time I right-click an object in SSMS 2016 and click Script Object As > CREATE To > New Query Editor Window, I get the prompt below. Not sure if I changed a setting or what, but was hoping someone knew how to make it so it doesn't prompt to open a new connection each time.


texts: Index Strategies on Text or NVARCHAR(MAX) Fields

texts:
I have the following query (simplified for question) I'm trying to speed up for a read only DB ...

SELECT
 [sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)
,[From]=[from_addr]
,[To]=[to_addr]  --I'm a very long Text or NVARCHAR(MAX) Field
,[Subject]=[subject]
,CASE WHEN [attach] = 1 THEN 'Yes' ELSE 'No' END AS 'Att'
,[Code]=[ccode]
,[Staff]=[staff]
,[MatNo]=[mat_no]
FROM dbo.[email]
DYNAMIC WHERE CLAUSE ON ANY OF ABOVE

I've tried adding some indexes including covering indexes I can't include the to_addr the way it is (as text or NVARCHAR(MAX) col), and the query optimizer ends up using the clustered index because the to_addr field is not included.  What are some ways to handle a situation like this ?  Unfortunately I' limited to 2005 on this.

Edit

Tried adding Full_Text For to_addr still does a table scan.  However if I comment out that line out it will use the index.  : ( Damn you Text Data !


texts: MS SQL Server 2012 Data Quality Services (DQS) installation failure

texts:
I tried to install MS SQL Server 2012 DQS using DQSInstaller.exe, but installation failed. In the console output started by the install tool (DQSInstaller.exe) I found this line:

Password validation failed.
The password does not meet Windows policy requirements because it is too short.

It was a bit odd, since my password was 15 characters long, but who knows. I used a different one and made it sure that it is 30 chars long. That didn't help and the installer insisted that the password is still too short.

Next thing was upgrading my SQL Server 2012 to SP1. That didn't help either.

The Installer is started with admin privileges. The database instance is NOT a default instance, but I see the script finds that out by itself.

What could be the problem?


texts: Data sync from different SQL Server versions databases

texts:
So I have a SQL Server 7.0 database that can not be upgraded. it is only allowed to have data read and written in it.
I created a SQL Server 2008 R2 database which is the same structure. I want to synchronize the data between both so whatever happens on the SS7.0 happens in the SS2008 R2, and vice versa, is there a way to do so?
I'm not asking for a ready answer, I just want a trail to follow, tips and such. So far I only managed to export a copy of the data with bcp, but I can't do it each time


texts: Change the value of a cell based on the hidden value of another cell

texts:
On a 2012 SSRS report I have the following:

You can see that "Site", 0098110, has a total of 13 "tests" associated with it. This is what I want to show while the group is in the collapsed state.

When I expand the group you can see the number of specific services - 9 "ABC" services, and 4 "XYZ" services. This accounts for the total of 13. Naturally there is a row grouping of Site Name, Service Type, Site Fee and Tests.

What I want is for the Total column to remain visible, but for its value to be set to blank (null) when a specific group is expanded like below instead of the image of the expanded group above:

...but the total for the group should reappear when the group is again collapsed, like in image 1. If I can simply make the Total cell blank based on the hidden value of the Service Type cell I'd be good.

I've combed the site and can't find anything specifically like this problem. Any advice appreciated.


texts:
I know there are still some SQL Server 2000 installs that exist out there.  With Microsoft no longer supporting these versions, how can I check to see what vulnerabilities remain unpatched?

This website lists some of the vulnerabilities but I am unable to determine which have fixes/patches to address the vulnerabilities. http://www.cvedetails.com/vulnerability-list/vendor_id-26/product_id-251/version_id-3147/Microsoft-Sql-Server-2000.html


texts: Allow entering application while in transaction

texts:
We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails...
Simple we have something similar to:
BEGIN TRY
        BEGIN TRANSACTION;
            --run some sprocs
            EXEC [dbo].[sproc1];
            EXEC [dbo].[sproc2];
            EXEC [dbo].[sproc3];
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        -- Re-raise error
        EXEC [dbo].[reraise_error];
        RETURN -1;
    END CATCH

These sprocs are pretty long running processes.  However, I noticed the team who developed this application was running this job at 10pm and have a step before this job that takes down the site completely.  They wanted to ensure no one edits any of this data so they have the site down while this process happens and then once its done the site is automatically brought back to life.  This has been fine for that past 3 years until we had a customer join us from overseas...they noticed the site was down  (due to this process) and now has requested a change.
I thought simple I could just disable the taking down of the site and handle edits in the front end of my application.  The problem is the database seems to be locked while running this above transaction, for instance, a user trying to get to the site gets a spinning wheel until this transaction completes.
As I ran tests if I comment out the BEGIN TRANSACTION and just run the sprocs (outside of a transaction) then I no longer have any issue as the tables aren't locked.
But I cannot do that as we want this process to either pass / fail completely.  If it fails we want the whole transaction to fail (as we have a rollback transaction).  The customer still wants the site not to lock up and continue this process so I played around with adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But this did not help in any way as it still seems users cannot do anything in the application until after the transaction is complete.
Am I doing something incorrectly?  What can I do so I do not have to lock up the application but continue this process in a transaction?  I would of thought changing the isolation level would help me out but I get the same results.


texts: SqlPackage.exe unnecessarily drops and recreates indexed view with schemabinding

texts:
Currently SqlPackage.exe used in conjunction with BitBucket for the deployment of the database schema changes.

Recently a pretty large indexed view has been created based on 3 source tables, which are key to the application. The process of creating the clustered index on the view and the accompanying downtime took around an hour to complete.

Lately a column had to be added to one of the source tables of the indexed view. Although the view has schemabinding, this shouldn't be a problem because the new column isn't used in the definition of the view (duh!). Although if we look at the script that has been generated by SqlPackage, it chooses to drop the indexed view to cope with the schemabinding although this is completely unnecessary. It seems to me that SqlPackage is unable to check the dependencies and therefore drops the view out of precaution.

If I simulate a similar situation on my local machine (SQL Server 2017 Dev edition), comparing DacPacs always result in the dropping and recreating of the view as well.

Now the question: does anyone know any workaround or any way to prevent SqlPackage dropping the view? I've checked all properties, but there doesn't seem to be any viable option there.


texts: [MongoDB]See Collections (and Files) created in a Shard on the Cluster

texts:
My structure:

A Sharded Cluster with multiple Shards. Each Shard contains 1 Collection, which is pinned to it by tags. Every Shard is at the same time a ReplicaSet Secondary. It receives data from a Replica Set server.

The situation:

A lot of PCs will push data into my database. I want to spread the load by using shards. Every PC will push only his own collection into my database and shall not know any data of other PCs. Every PC itself holds a MongoDB server. Sadly I can't ensure, that every PC will have connection to my Cluster all the time, so they have to store everything in their own database first, which is meant to replicate its content to my cluster, when the connection is stable again.

The goal:

Having a centralized big database on my end, which contains every PCs collection, without having a PC knowing any data from another PC.

The problem:
I set up a Cluster with Shards and every Shard has got a Replica Set as Primary. Whenever a Replica Set pushes Data into the Shard (via standard replication), the Cluster sees only the database, that has been created by the Replica Set.
Is there a way to let the Cluster know the data inside of the Shard? Or is there even a better way to handle this situation?

I'm free to use any version of MongoDB.

Thanks in advance!

Edit:
Whenever my Cluster, the Shard and the Replica Set contain the same Collection, I get the error:

WriteResult({
        "nInserted" : 0,
        "writeError" : {
                "code" : 193,
                "errmsg" : "Cannot accept sharding commands if not started with --shardsvr"
        }
})

upon interacting with the collection from the Cluster (In this case I tried to write files from the Cluster to the collection).


texts: How to determine if postgres database contains GiST indexes and what type?

texts: Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?


texts:
I'm trying to get the syntax correct on my query but I'm having issues.

Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row shows the HHValue again and the DirectValue. It's splitting the results between two rows and I want it all on one row.

However, when I try to change my CASE statement, I am getting errors. I think I'm just confusing myself but I need one row for each household with HHValue, NationalValue and DirectValue.

This is what I have in my query so far:

SELECT Household.Name
     , FinancialPlanner.LastName
     , PlanFirmSpecCode.SpecialCode
     , SUM(Asset.Value) AS HHValue
     , SUM(CASE WHEN financialinst.name = 'National Financial Services'
                THEN Value ELSE 0
           END) AS NationalValue
     , SUM(CASE WHEN financialinst.name <> 'National Financial Services'
                THEN Value ELSE 0
           END) AS DirectValue
FROM PlanFirmSpecCode
JOIN FinPlanSpecCode
    ON PlanFirmSpecCode.PlanFirmSpecCodeID
     = inPlanSpecCode.PlanFirmSpecCodeID
RIGHT JOIN Household
      JOIN Account
          ON Household.HouseholdID = Account.HouseholdID
      JOIN Asset
          ON Account.AccountID = Asset.AccountID
      JOIN FinancialPlanner
          ON Household.FinancialPlannerID
           = FinancialPlanner.FinancialPlannerID
      LEFT JOIN FinancialInst
          ON Account.FinancialInstID = FinancialInst.FinancialInstID
    ON FinPlanSpecCode.FinancialPlannerID
     = FinancialPlanner.FinancialPlannerID
WHERE Account.Closed <> 'Y'
  AND Asset.Value > '0'
  and Account.Managed <> 'N'
GROUP BY Household.Name
       , FinancialPlanner.LastName
       , PlanFirmSpecCode.SpecialCode
       , CASE WHEN FinancialInst.Name = 'National Financial Services'
              THEN 'NFS' ELSE 'Direct Business'
         END
Order by household.Name


texts: mysqldump file restore while sql is still being dumped

texts:
Im using MaridaDB 10, but my question applies to all mysql variants.
I have large dataset (70GB uncomressed when dumped to file).

The mysqldump process takes 2-3hours. The restore process takes even longer 10-14 hours.

I have a nfs share I use for syncing data between many servers. My question is specifically.. If I have mysqldump process running and outputting data to /nfs/file.sql and is at say ~35gb (50% complete). Can I start a restore process (mysql < /nfs/file.sql) on a different server and have it work as expected? (finish through the end of file when its complete).

Im not worried about restore process getting ahead of mysqldump. With even a 20% buffer, its unlikely.


texts: Always Encrypted after restoring an old database backup using C#

texts:
I have a Windows Form application. It allows users to restore a SQL Express database.

Latest version of SQL Server in app now is SQL Server 2017. Customer can restore an old backup of a SQL Server 2008 R2 database.

I want to enable Always Encrypted (AE) after performing the restore using C#.

Could anyone please help me to achieve this?

There is no issue enabling AE for a new database. I would like to have AE after restoring an old database into the application. The restore happens via a T-SQL command.

I tried to enable AE using PowerShell. I am getting sqlserver module errors. Is there any way to add AE using T-SQL?

The error is:

  System.Management.Automation.CommandNotFoundException:
  'The term 'Get-SqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.'

This errors in PowerShell; however, I really want to do AE without PowerShell in C#.

I tried:

Alter table test3
    alter column C3 varchar(50) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL

This command doesn't work when there is data in the table.


texts: How to separate values from one column into two columns in SQL Server

texts:
I have the below data-set where a user has more than 1 Contact No associated to their Name.  The below is just a small sample of my data-set.

Name     Contact No
-------  ----------
Deepika  75865558
Deepika  207586558
Rnveer   746655558
Rnveer   946655558
vaibhav  2356841
vaibhav  6655558

I want to split the Contact No into their own columns to consolidate the information as shown below.

Name     Con 1      Con 2
-------  ---------  ---------
Deepika  75865558   207586558
Rnveer   746655558  946655558
vaibhav  2356841    6655558

How can I get data that is only located in a single table, and in the same column to output in 2 columns?  See the above example.


texts:
I am trying to configure JavaMail in my Oracle 11g XE(11.2.0.2.0) database, which requires loadjava utility. But after some research I came to know that XE version of Oracle doesn't provide Java/JVM as some restriction in Free version of database.
I can not install Licence Version of Database, but want to install/configure Java service in my XE database if possible.
I was following this post How To Install Oracle JVM, but not able to locate /javavm/install/initjvm.sql in my oracle directory.
if anyone knows some solution, help would be appreciable.


texts: Why does querying for records that violate a foreign key return incorrect results?

texts:
While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant inserts on the tables in question.

The FK would be from MessagePatientIdentifier.MessageID to Message.MessageID.

The query we used was as simple as could be:

select * from MessagePatientIdentifier as mpi
where
    MessageID not in (select MessageID from Message)

The issue that we are seeing, is that rows are being returned from MessagePatientIdentifier

MessagePatientID                     | MessageID
553bde76-47d4-4ec3-96d1-b5d2e98931e1 | 7d45464d-8cc4-4a2e-8828-020722165b39

in this case and when you then select * from Message where MessageID = 7d45464d-8cc4-4a2e-8828-020722165b39, the record does in fact exist.

We then moved on and tried the same query, but on a table that DOES have a FK in place, referencing Message table in the same way. The same results...the query is reporting that Child table records exist without parent the corresponding parent table (Message) record.

MessageRecipID                       | MessageID
26d6d632-87b3-407e-aeb0-04552981e5f8 | 750f0fb4-3e6c-485d-996e-f061f8caa360

Then, once again if you select * from Message where MessageID = 750f0fb4-3e6c-485d-996e-f061f8caa360, this returns the record.

This data comes in via stored procedures from a Mirth Server as well as through a BizTalk WCF-SQL send port. The proc inserts the message record, gets the new uniqueidentifier as an output variable and then uses that to call auxiliary stored procs to insert into both the MessagePatient.MessageID and MessageRecip.MessageID.

Is this expected behaviour and I'm just not educated on the inner workings of SQL? Technically I believe BizTalk runs everything in a transaction, so it shouldn't get out of sync, even if it did where would it get the MessageID value if not from the Message insert?

What am I missing here?


texts: oracle regexp_replace to remove duplicate duplicate strings and double separators

texts:
Been using the following regexp_replace to create a comma separated string for those values in 'col_name' and remove any duplicates.

REGEXP_REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,col_name,',').EXTRACT('//text()')).GetClobVal(),','),'([^,]+)(,\1)+', '\1')

Works ok but with 1 exception - if 'col_name' has empty string values. What I am ending up with is:

value1,value2,,value3,value4,,value5 etc.

Looking for that elusive regex that can remove any duplicate values and those repeating commas.

Thanks

--- updated ----

        col-name
-----------------
row1    value 1
row2    value 2
row3
row4    value 2
row5    value 3
row6    value 1
row7

Expected Result: value1,value2,value3


texts: Change AWS's Read Replica's streaming replication to trigger based replication

texts:
We have a Postgres RDS instance for which we have created a read replica instance. The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it doesn't complete giving a conflict error. The reason for this conflict is that there was an update on the main RDS, which was getting reflected to Read Replica as it is following streaming replication approach.
For this very reason and also due to new requirement, we need to make this replication process daily, i.e. instead of continous updation we need a trigger based approach(something like a daily backup approach).

I saw in the AWS's console,there are various parameters we can set. Is there some parameter which I can set to achieve my requirement? Or the only way out for me is to delete the read replica instance and use a trigger based replication tool like Bucardo and use an EC2 instance for this?

I would really prefer the approach through which I could tweak some parameter and achieve my objective without having to delete the Read Replica instance.


texts: SQL Server 2014 SP1 Setup Freezes Removing Temporary Files On Windows 10 x64

texts:
I want to install SQL Server 2014 Enterprise Edition with Service Pack 1 on Windows 10. But when I try, the installation stops every time at the part the screenshot below shows, and I wait for hours. Finally, I have to cancel the setup process.

I did some research into the issue, then installed Entity Framework 3.5 and Windows PowerShell 2.0**. I needed no pre-requisites.

Please note that I also could not install Cumulative Update for Windows 10 for x64-based Systems (KB3081438). This update fails to install on my computer the same thing every time. Maybe this problem can be resolved by this update; because I heard that this update is also related to Entity Framework?

What is the reason for this problem? What should I do to solve it?


texts: Finding the right pattern for creating indexes,redesign query structure

texts:
I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables.

Because the logic of the initial query was to bring data based on some conditions, not all the joins were needed if a certain condition is satisfied.

For example : The query has 5 different conditions, each condition satisfies a certain scenario where only some table joins are required.

INITIAL QUERY EXAMPLE :

Select
  -- fields
From table1 t1
Join table 2 t2 ON t1.id = t2.id
...
Join table n tn+1 ON .....
Where
(t1.field1 = 1 and t1.field2 = 0) AND (
(t2.type = 1 and somefield = value and somefield2 = value ... somefield n+1 = value )
OR (t2.type = 2 and (somefield3 = value or somefield4=value or .... somefield n+1 = value) )
.......
OR (t2.type = n and (somefield n...n+1 = value)))

Note: The fields on which the conditions are written are never changed, the conditions are exactly as those written in the example above.

My thoughts are to redesign the structure of the query by splitting it into multiple queries joined together with UNION ALL. By doing this, I would get rid of the entire amount of joins for each condition.

I'm not sure if this could bring any real benefits mentioning the fact that in some cases when a certain condition is being satisfied , some of the table joins are on null values because the type of the conditions makes the query to bring a certain data which doesn't have the data the join needs.

The real questions would be :

Splitting the query into UNIONS would be better/faster design ?
Which is the pattern in the query for making an INDEX? If so, how should I create the index, which fields from the given example?


texts: Dedicated SQL instance vs multiple database same server instance

texts:
Looking for best practice advice for a sprawling network. I inherited a bunch of SQL servers. The practice has been to just stand up a new VM instance any time a database was needed or add a new SQL instance for every database. The administration challenge is huge so was hoping to hear some feedback on approach.
I guess the simple question is, would it be better to run multiple instances per machine or 1 instance with multiple databases assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)?


texts:
I'm trying to decrease execution time of the a query. Using SHOW PROFILE, FULLTEXT initialization takes about 0.3, which is too high. Other values are at normal level.

How can I decrease FULLTEXT initialization time? Any suggestions?

Once I figured out FULLTEXT initialization is the problem i created this question. Please see MySQL fulltext selection performance for more information


texts: Trouble recovering data from corrupted PostgreSQL tables

texts:
I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble.

When I do select count(*) One table returns:

  ERROR:  could not access status of transaction 2619431010
  DETAIL: Could not open file "pg_clog/09C2": No such file or directory.

The other one returns:

  ERROR:  invalid page header in block 94206 of relation
  base/11974232/11975439

Is there a way I can work around and get my data back?


texts: T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008

texts:
After creating roles and granting privileges to them, I want to grant the privileges of a specified role to a user with grant option so that the user can grant privileges to others, how to do with it?

EXEC sp_addrolemember 'db_owner', 'sabertazimi'

Doing like this can't make 'sabertazimi' to grant his privileges to others.


texts: Data Warehouse Modeling: Sales Order Charges

texts:
I have two databases systems that I'm trying to dimensional model a sales order business. Both operational data stores have a Sales Order header and detail table. However, one of them has an additional table for Additional Charges that are charges that can be adjusted a either at the sales order header level OR at the line level. The relationships between the tables are:

Header to Detail = 1 to Many
Header to Charges = 1 to Many
Detail to Charges = 1 to Many

I have a few competing dimensional model design thoughts:

Create a Sales Order header + line level granularity fact table and then a separate fact table for adjustments. This keeps the granularity clean, but might repeat many dimension key columns between the two tables.
Flatten all three tables into one single fact table (with allocated measures) for one of the sources and then leave the Charges related set of columns blank for the other source. This technically contains different granularities in a single fact table, a big Kimball no-no.
Another option might be to create some sort of bridge table, maybe? Thought this would make the Charges act as a dimension than a fact which might be not very efficient...

Can someone guide me as to which of these options is the most apt design?


texts:
I am using sql server 2008 and when trying to restore .bak file I'm getting this error:

The fulltext catalog upgrade failed because of an inconsistency in metadata between sys.master_files and sys.fulltext_catalogs for the catalog ID 5 in database ID 8. Try to reattach this database. If this fails, then the catalog will need to be dropped or recreated before attach.

This is the query  I'm trying to execute to get the details:

RESTORE FILELISTONLY
FROM DISK = 'D:\XXX\mydatabase.bak'
GO

Result:

And when trying to restore as shown it throws me an error. Can anyone help me out with this error ?

RESTORE DATABASE mydatabase
FROM DISK = 'D:\XXX\mydatabase.bak'
WITH MOVE 'mydatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf',
MOVE 'ProjectManagement_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mydatabase.ldf'

EDIT:

Use master
RESTORE DATABASE mydatabase
FROM DISK = 'D:\XXX\mydatabase.bak'
WITH MOVE 'mydatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf',
MOVE 'ProjectManagement_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mydatabase.ldf',
MOVE 'sysft_fulltextCatalog' TO 'D:\mylog'
GO


texts:
When new databases are created the Differential and transaction log backup maintenance plans start failing and sending alerts as full backup is not taken. What could be the best solution to handle it.

I tried to do full backup on database created trigger but that is failing as backup cannot be taken in the same transaction.


texts: Parameter evaluation against harcoded string always return false

texts:
In Mysql workbench I am trying to make query whit parameter.

But I have strange behavior when I try to compare parameter with static strings.

Why I do not get any rows form query like this ?
When I run query , I do get header rows, but I do not get any row.
I am sure that there is rows in table calls.
Forgive me if this too obvious but I in MsSql I did this million times without issues.

set @kontakt ='fo' ;
select
*
from calls
where (@kontakt = 'fo');


texts:
I am reading about Oracle and I am confused with the two names: service_name and database name.  Do they both refer to the database name used while creating a database with the CREATE DATABASE xxxxxx command?

Can the service name be different from the database name? If yes, how do we map the service name to the database name on the server? Because as a client, we use the service name to connect to the database, how does the server map the service name to the database name?


texts: Fill in Ending Balance from previous months balance

texts:
I need to fill in the Ending Balance of each Year / Period where it is 0 (zero) but a previous period for that year and company has an ending balance - basically carrying forward the ending balance for that company for the year.

This may be less complicated that I makiang it to be , but I can get my TEMP table all the way down to this, but need an update statement

reviewing the attached below

(In Yellow) Company 400 - Period 01 has an ending balance of -35460601.79, this needs to be that beginning balance of company 400 - Period 02 and then that Period 02 beginning balance is to set as the Period 02 Ending balance - then moving down through Company 400 until you get to a new Company/Fiscalyear.

Company 401 is good and no action is required

Company 500 needs to do what Company 400 did but Company 500 starts at Period 04/05 - filling down until it reaches the next company.
This will process will step the entire table By Year / By Company
I really appreciate your expertise in helping me
Stephen


texts: User Defined Aggregate Functions without DLL

texts: Is it possible to create a aggregate function without dll? Every aggregate function have to reference a dll? Thank you.


texts: How can I achieve a unique constraint with two fields?

texts:
I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all have IsDeleted asd true.

How would I write this check constraint ?


texts:
I have a weird problem. I am currently using mysql and have defined 100M of max_binlog_size. However, I have hundreds of files created every day and each file is of no more then 2MB or 3MB each and sometime less.

I do not understand why is this so the file should be around 100M each?

Can someone have insights to it.


texts: Where can I find the algorithm used in Oracle Range Partitioning

texts:
I am new to database and would like to know where can I find the resource to study and understand about different algorithms used in table partitioning

For example, what algorithm is used between range and hash partition to find the partition that a data to be inserted.


texts:
I have a table called person setup like this

(person_id SMALLINT UNSIGNED,
fname varchar(20),
lname varchar(20),
gender  enum('M','F'),
birth_date date,
race enum('B','W','A','J'),
ethnicity varchar (60),
origin varchar(20),
profession varchar(40),
CONSTRAINT pk_person PRIMARY KEY (person_id));

and another called words setup like this

(person_id Smallint unsigned,
word_id smallint unsigned,
word Varchar(20),
format varchar(40),
title varchar(40),
CONSTRAINT pk_words PRIMARY KEY (word_id),
CONSTRAINT fk_words FOREIGN KEY (person_id)
    REFERENCES person (person_id));

and i tried to import first like this

LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'

and got

error 1366 incorrect integer value: '""' for column 'word_id' at row 3

and

error 1452 cannot add or update a child row: a foreign key constraint fails ('words', CONSTRAINT 'fk_words" FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))

but one value was inputted, and could be if i just added one at a time.

so then i tried

LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r'
(person_id, @worder, word, format, title) set word_id =  if(@worder = "", null, @worder);

but got the same errors in a different quantity.

Here's what my data looks like. Any idea whats wrong? I'm new to SQL

"person_id","word_id","word","format","title"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"

Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine


texts: how to extract difference between timestamps

texts:
SQL greenhorn alarm:
I have a table with timestamp information (44 rows) and I now would like to calculate and extract the delta between any two consecutive rows. How can I do this? I select my data with a query like:

SELECT * from "table" where "RecognitionTimestamp" >= '2018-10-03 00:00:00' and "RecognitionTimestamp" <= '2018-10-04 9:00:00'

I'm using pgAdmin4 to submit the query and look at the data.


texts: Will my SQL query use stale data? How can I prevent it?

texts:
I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record for zero or more SJobDependent records.

Here is my SQL query:

-- Return any records that are active and have no unsatisfied dependencies.
SELECT * FROM SJob
LEFT JOIN SJobDependent
    ON SJob.job = SJobDependent.job
    AND SJobDependent.satisfied = 0
WHERE SJobDependent.jobDependentID IS NULL
AND SJob.state = 'active'

Here is the Actual Execution Plan from SQL Server Studio:

Due to the way the code is written:

// Pseudo-code:

// SJob record is added with SJob.state = 'ready'.

// Related SJobDependent record(s) are added.

// SJob record is updated to SJob.state = 'active'.

I fear that this may happen when the SQL query runs:

Scan SJobDependent.
SJobDependent record(s) inserted.
Start scan of SJob. SJob.state is 'ready'.
SJob is updated. This blocks reading of SJob?
End scan of SJob. SJob.state is 'active'.

The problem I fear is that my SQL query returns SJob records found in the "active" state (SJob.state = 'active'), but fails to see the related SJobDependent records.

Is this problem capable of happening, or am I over-analyzing the SQL query?

If this is a legitimate problem to worry about, what can I do to solve it? I'm open to solutions.

One idea I've had is to force the scan of SJobDependent to occur after the scan of SJob. Is this even possible? What are the implications/consequences of doing this?

Do the scans shown in the Actual Execution Plan occur in a particular order or is it always random from call-to-call?

NOTE: As noted in AMtwo's answer, Repeatable Read isolation level will probably not solve my problem, due to the fact that it only takes effect when the read starts.


texts: Group by checks constraints are possible?

texts:
I have a table with the following columns:

TEAM_ID integer,
NAME varchar(64),
IS_LEADER char(1) not null default 'Y'

I want to apply the constraint that, in the same team, only a leader can be possible.
This constraint applies only on a subset of rows, not to the whole table as, of course, multiple teams can have their leader.

How to write this kind of constraint in oracle?


texts: How do I query to pull all data in row based MIN and MAX service date?

texts:
I'm very new to Oracle SQL especially in the Pentaho report builder.  It lets you do some things and block others.  PL/SQL is not available so I must do this in a query.

I need to be able to pull all the data in each row for a client for their MIN(service_date) and their MAX(service_date).  Below are the table data and the result I'd like to get.

Table Data

client_id | service_date  | other_stuff
---------------------------------------
        1 | 2/24/2010     | Bob
        1 | 3/23/2010     | Jane
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 4/27/2005     | Leisha
        3 | 5/29/2005     | Tonay

Result data

client_id | service_date | other_stuff
--------------------------------------
        1 | 2/24/2010     | Bob
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 5/29/2005     | Tonay


texts: Enabling page compression on 1.5TB table

texts:
I need to come up with the best approach possible to enable page compression on a table 1500 GB in size, with 363,957,740 rows. The database size itself is 1.71 TB and holds archive data.

If I understand correctly, it needs disk space (possibly the same amount, just to be on the safer side) so that it can create a copy of the table with page compression enabled and release the space. This is in FULL recovery model so, it will be heavily logged.

I have spoken to my capacity planning resource and he agreed to give additional temporary required space for this maintenance, and take back the space once this activity is complete. Having said this, do you think the best approach to be:

Take a full backup
Restore the backup onto new temp drives
Change the recovery model to SIMPLE
Enable page compression
Take a full backup after compression and restore original database
Change recovery model to FULL

Also, instead of enabling page compression. After Step #3, truncate the largest table, enable page compression and then do a

SELECT * INTO ReplicaDB.dbo.ReplicaTbl

Does this enable page compression on existing indexes?

I do not have Test environment to test the above steps. Alternatively, if any better approach is available then please let me know.

The goal is to minimize the future disk space required at its current growth. We are an ERP software company and we have licensed Enterprise Edition. This table is only for archive when some checks are performed and all the data resides in this table. I have 2 indexes (1 CI, 1 NON-CI). None of the columns are VARCHAR (MAX), they are either NVARCHAR, int or date type.


texts:
I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering):

SELECT * FROM (
    SELECT discovery.id, discovery.resource AS url, discovery.event_datetime, whitelisted.keyword
    FROM discovery
    INNER JOIN whitelisted
    ON discovery.`resource` LIKE concat('%', whitelisted.`keyword`, '%')
    LEFT JOIN `logs`
    ON `logs`.discovery_id = discovery.id
    WHERE
    discovery.event_datetime >= NOW()
    AND
    discovery.provider = 'XXX'
    AND
    `logs`.id IS NULL
    AND
    discovery.resource NOT LIKE '%SOMETHING-TO-EXCLUDE%'
    LIMIT 0, 20
) logless_resources
GROUP BY logless_resources.url
ORDER BY logless_resources.event_datetime ASC

discovery table: 143k rows

id UNSIGNED INT AI PRIMARY
resource VARCHAR(1024) INDEX
provider ENUM
event_datetime DATETIME
created_at DATETIME

whitelisted table: 535 rows (not thousands, just 0.5k)

id UNSIGNED INT AI PRIMARY
keyword VARCHAR(128) INDEX
provider ENUM
created_at DATETIME

logs: 585k rows

id UNSIGNED INT AI PRIMARY
discovery_id UNSIGNED INT FK to discovery.id

Right now this takes 90 seconds - can it be made faster?


texts: How to set column text when creating a view in SQL?

texts:
By default, when creating a view, for the Text of each column, if the column is a direct selection from a table/physical file then it will use the Text of that table/physical file's column. If it's not (e.g. a CASE statement), then it's blank.

How can I override it to set what I want for the view's columns' texts?


texts:
I have an application that is installed world wide that uses SQL versions from SQL Server 2008 Express to SQL Server 2017 Enterprise edition.

I have tried several ways to create an index (3 columns with an include of 3 columns) on a big table where the Database is usually in simple recovery model but can be as Full recovery model... Hardware can vary drastically from one customer top another:

Alter table create index...
Create a tmp table with the new
    index and copy the data with an "insert into" followed with a drop
    table of the original table and a rename of the tmp table to replace
    the original one.
Create a tmp table with the new index and use a
    PowerShell script to copy the data followed by a drop table of the
    original table and a rename of the tmp table to replace the original
    one.

The quickest of the 3 was the 1st option (the traditional way of creating a non-clustered index) which took 1h15m for a bit over 106 million records (the table has 200 columns... not ideal but it's what I'm working with) the other options took triple the time and more)

Unfortunately, I can't use the online feature since there are a variety of SQL Server versions and the online is only good for enterprise edition.

An hour and 15 minutes in my lab is too long and I would like to try to find another way to bring that down, specially that in one of our next application updates, we will need to create 23 indexes... and I haven't seen an update that takes 5-6 hours nor will customers want to wait 5-6 hours for an update of a software.

Also, the update will require no activity on the database during the update.

All I'm looking for are ideas I can try to get my indexes created in a reasonable amount of time.  No code! just need theoretical ideas.

Any idea would be greatly appreciated.


texts:
I have a SQL Server 2008 database of size around 1 TB. I want to migrate it to SQL Server 2012. I have few queries:

To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012?
To leverage full potential of 2012 after migration, what considerations to take during migration. After migration all 2012 new features and engine improvements should be available for the migrated database.


texts: This simple mysql query is taking 2-3 seconds

texts:
I'm using sqlalchemy and trying to do a query on an m2m table.  I have a tag and want find all the events that match that tag:

SELECT * FROM events
WHERE EXISTS ( SELECT 1 FROM events_tags, tags
               WHERE events.id = events_tags.event_id
               AND tags.id = events_tags.tag_id
               AND tags.id = 617)
LIMIT 50;

This is taking 2-3 seconds on my web server.  On my laptop, it's faster(though my laptop is more powerful and has fewer rows.)  The web server has about 300,000 rows in this table.

Here is an explain:

+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
| id   | select_type        | table       | type   | possible_keys  | key     | key_len | ref                      | rows   | Extra       |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
|    1 | PRIMARY            | events      | ALL    | NULL           | NULL    | NULL    | NULL                     | 310172 | Using where |
|    2 | DEPENDENT SUBQUERY | tags        | const  | PRIMARY        | PRIMARY | 8       | const                    |      1 | Using index |
|    2 | DEPENDENT SUBQUERY | events_tags | eq_ref | PRIMARY,tag_id | PRIMARY | 16      | timeline.events.id,const |      1 | Using index |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+

I have a feeling this is simple, but my google-fu is failing me.

Create Table Syntax:

 | events | CREATE TABLE `events` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `title` varchar(255) DEFAULT NULL,
   `context` varchar(255) DEFAULT NULL,
   `text` mediumtext,
   `wikidata_id` bigint(20) DEFAULT NULL,
   `start_day` smallint(6) DEFAULT NULL,
   `start_month` smallint(6) DEFAULT NULL,
   `start_year` bigint(20) DEFAULT NULL,
   `end_day` smallint(6) DEFAULT NULL,
   `end_month` smallint(6) DEFAULT NULL,
   `end_year` bigint(20) DEFAULT NULL,
   `is_number` tinyint(1) DEFAULT NULL,
   `version` int(11) DEFAULT NULL,
   `number` bigint(20) DEFAULT NULL,
   `start_name` varchar(255) DEFAULT NULL,
   `end_name` varchar(255) DEFAULT NULL,
   `subject_title` varchar(255) DEFAULT NULL,
   `object_title` varchar(255) DEFAULT NULL,
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=327132 DEFAULT CHARSET=utf8 |

edited: my bad, it was missing the "from events"  I guess I'm wondering where I would want an index, I would think on the "id" column, which should have an index.


texts: Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF

texts:
I experimented with various configurations of SQL Server databases and ended up setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF.

I noticed that when enabling this, many queries got a lot faster. I'm still using the default READ COMMITTED isolation level to connect to the database.

What is actually happening here? I would think that when ALLOW_SNAPSHOT_ISOLATION is OFF, setting READ_COMMITTED_SNAPSHOT to ON wouldn't have any effect... I'm still not actually using the snapshot isolation, or am I? Can anybody please explain? I'm confused.

I tried researching this topic online, but whenever I see READ_COMMITTED_SNAPSHOT being used, it's always together with ALLOW_SNAPSHOT_ISOLATION, which I didn't enable.


texts: When normalizing a relational database, should multiple "type" tables be combined into one?

texts:
Before making substantial changes to the content, functionality and design of my website (I'll be refactoring everything, almost a complete rewrite), I'm setting up a new PostgreSQL database to hold all the data previously in a MySQL database. I also have a heavily-customized WordPress blog that I'm going to move over to this website, effectively merging the two sites. Apart from the front-facing content for the website, there is also a lot of other information that I store in the database for my own use, such as client information, sales history, invoices, event income, etc. I'm taking a lot of time thinking things through carefully and trying to re-design the database properly with both data integrity and performance in mind.

The front-facing website will have many different "content" types: pages, events/gigs, multimedia samples, sheet music/charts (some of which will be available for sale in PDF format on the site), blog articles, etc.

One of the changes I've made is to the way I store venues/places. I used to have a "venues" table that referenced a "venue type" table as well as a "cities" table, which referenced a "states/provinces" table, which referenced a "countries" table. Most of this information was used by the "events", but then I added client information which needed to reference the cities table. Further still, all of my blog articles are geotagged, so they'll need to reference these places too. Since all the tables had a very similar schema, what I've done is create a new "places" table with "id" and "parent_id" columns so that places can be stored in a hierarchal fashion. I also have a "places_type" table so that I can enforce that the parent_id for each row has to be of a higher hierarchal type than the place itself. (So for example, a city can have a state or country as its parent type, but a city can't have a restaurant as its parent type.)

In doing this, I've created a lot of other tables with the exact same schema (id, parent_id, name) to hold "types" of other content in a hierarchy. Genres, payment methods, chart types, event types, media types, invoice types, etc.

Since all the tables have the exact same schema, would it be better to combine all these tables into a single "types" table and add a "category" field to the table?

I think it would be much easier to manage a single "types" table than eleven similar tables. I've calculated that this "types" table would hold only about 60 rows or so, for now.

I'm wondering what would be the best practice for this sort of thing in terms of database design? The data will be retrieved far more frequently than it will be updated/added.

At the same time, since this one "types" table would hold all the types for everything, it will need to be joined (via a view) many times to many tables. For example, to retrieve event information, I already join an "events" table to the "places", "artists" and "media" tables, but would also need to join these tables to the "types" table to get event types, place types, artist types and media types. So would it be best to keep the type tables separate, or combine them into one? Why?