texts: Why won't Oracle alter the size of a column that is used for sub partitioning?

texts:
I am trying to resize a varchar2 column, when I get an ORA-14265 error:

ORA-14265: data type or length of a table subpartitioning column may not be changed

With Oracle providing the unhelpful:

// *Cause:  User issued ALTER TABLE statement attempting to modify
//          data type and/or length of a column used to subpartition the
//          table named in ALTER TABLE statement, which is illegal
// *Action: Avoid modifying data type and/or length of table
//          subpartitioning column(s)

So I get that Oracle doesn't want me to alter the column. I presume the hash used for the subpartition would be broken by this. How do I get around this though?
The only suggestion I have found so far that seems plausible is to rename the existing table, and recreate the original with updated schema, then select the data from into the recreated table.
I don't particularly like this idea, for reasons of effort and likelihood of human error:

We have lots of partitions, and behind the scenes I'd have to manually ensure the new table matched up identically, so that supporting maintenance scripts could continue along nicely.
Then there is disk space. We keep 9 rolling months of data, and I don't have room to suddenly double up on this one table.

Any suggestions would be much appreciated. I hope I'm just missing something easy. Also, if anyone wants to let me know why oracle can't rearrange the data on disk and create a new subpartition hash automagically that would be great.


texts: Why does SQL developer suggest a different format for a procedure that works?

texts:
This is my original procedure that works beautifully:
create or replace PROCEDURE EXTRACT_0_CPP AS
    CURSOR c_data IS
        SELECT cpp,
               rfu1,
               rfu2,
               mean_rfu,
               charge_ph7_4,
               hydropathy
        FROM   cpp
        ORDER BY LENGTH(cpp);
    F1 UTL_FILE.FILE_TYPE;

BEGIN
    F1 := UTL_FILE.FOPEN( location => 'EXTRACT_DIR',
                          filename => '0_cpp.TXT',
                          open_mode => 'w',
                          max_linesize => 32767);
    FOR cur_rec IN c_data LOOP
        UTL_FILE.PUT_LINE (F1,
                            cur_rec.cpp || ':' ||
                            cur_rec.rfu1 || ':' ||
                            cur_rec.rfu2 || ':' ||
                            cur_rec.mean_rfu || ':' ||
                            cur_rec.charge_ph7_4 || ':' ||
                            cur_rec.hydropathy);
    END LOOP;
    UTL_FILE.FCLOSE(F1);
END;

But SQL Developer gives me a squiggly red line under SELECT and suggests I change it to this:
create or replace PROCEDURE EXTRACT_0_CPP AS
    CURSOR c_data IS
        SELECT
    "A1"."CPP"         "CPP",
    "A1"."RFU1"           "RFU1",
    "A1"."RFU2"           "RFU2",
    "A1"."MEAN_RFU"       "MEAN_RFU",
    "A1"."CHARGE_PH7_4"   "CHARGE_PH7_4",
    "A1"."HYDROPATHY"     "HYDROPATHY"
FROM
    "C##ELLIE"."CPP" "A1"
ORDER BY
    length("A1"."CPP");
    F1 UTL_FILE.FILE_TYPE;

BEGIN
    F1 := UTL_FILE.FOPEN( location => 'EXTRACT_DIR',
                          filename => '0_cpp.TXT',
                          open_mode => 'w',
                          max_linesize => 32767);
    FOR cur_rec IN c_data LOOP
        UTL_FILE.PUT_LINE (F1,
                            cur_rec.pk_cpp || ':' ||
                            cur_rec.rfu1 || ':' ||
                            cur_rec.rfu2 || ':' ||
                            cur_rec.mean_rfu || ':' ||
                            cur_rec.charge_ph7_4 || ':' ||
                            cur_rec.hydropathy);
    END LOOP;
    UTL_FILE.FCLOSE(F1);
END;

My question is (why) is this better? What is "A1"?


texts: Online Schema migration in MySQL : When to start?

texts:
I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database.

We're having a single MySQL db, and besides many really small tables, we have two tables with approx 500k-1.5m rows.

Currently, when performing DDL queries (adding new columns to these tables), we have to deal with quite a long execution time (around 4-5 mins).

I started to search for online schema migration tips and found the following:

Alter table on live production databases
mysql change schema on the fly
PT Online Schema Change
OpenARK: Online Alter Table
Facebook's Online Schema change

Although these tactics are worth investigating, I started to feel unconfortable at all: I don't think, that tables with such a low number of rows have to use such advanced methods. (What about really huge tables then?)

So I was wondering, if anyone of you have tips regarding the mysql.cnf itself, or other performance tips for the ddl queries?


texts:
I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB.

Currently I double click the package and execute it. There are times when there is a lot of data to load.

My question is there a way I can optimize the SSIS package so that it loads faster?


texts: mysql replication missed quite a lot SQL statement

texts:
I have set up a master/slave with mysql-5.1.73
The master's binlog format is "statement".
The master and slave seemed running very well with slave status:

         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

And when I modified the content on the master manually, whether it was select or update or insert, or alter table, the modification will be synchronized to the slave instantly.

However, after running for several days, I found the slave missed a lot of insert statements, these insertion statements didn't violate any PRIMARY key rule. More ever, when I tried to redo the binlog on another slave with:

mysqlbinlog mysql-binlog.00000X | mysql

Those missed statement were missed again with no warning or error.

Have you ever met such a situation, what should I do to restore all the changes to the slave? (There are quite a lot of  missed changes, so I could not restore them one by one).

I dug into this matter to find that, the relay log on the slave contains all the insertion statement, which means the binlog is transmitted to the slave correctly. However, the binlog on the slave missed some of the insertion statement, so this issue appeared to occurred during the redo process of the slave.

Any suggestions to diagnose into this issue or work around it?


texts: How to set identity_insert to on in SQL Server

texts:
I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

  Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on;

but I get the following error:

  Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS
-Databases
--System Databases
--mySchema

Does anyone know a solution to this problem?


texts: SQL - Get the values of another column for values in a given column

texts:
I am working with a large data table. I want to check the individual items with their destination and the count of destinations if there are more than 1 destination for a given item.
So in the below image, in my result, I should not get Items Grapes and Rice since there is only one destination for them. I want to filter by the destination as well. I need to remove the items which are going to the same destination. So here, only Apple and their respective destinations are the ones I need. Orange should be removed because its going to the same destination.
The query I have written is below. However it does not serve the purpose.This does not filter out same destinations. Any help please ?
select Item,
listagg(Destination, ',') within group (order by Destination) as Destination
from ProductsTable
group by Item


texts: SQL Server Express Instance Memory vs Memory Limit

texts:
SQL Server 2014 express edition is published as having a memory limit of 1 GB.

What do the SQL Instance Memory Properties do with respect to this limit?
For example, I can set the minimum and maximum memory limits within the properties to 4GB and according to the task manager that instance will 'use' 4GB of system memory.

Am I better off limiting the memory properties to 1 GB or is there advantage in allocating >1 GB memory here.


texts: Oracle index with REGEXP_REPLACE is it a good idea?

texts:
I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone.

Trying to avoid a fullscan I decided to create a new index in this column, but the trick part is that the data in this column is not normalized. I can find telephones separate by spaces, by "-", and even a lot of different formats. (ex: (418) 123-1234, 418 123-1234, 4181231234, 55(11)12321-1234, ...)

Found some articles saying to use REGEXP_REPLACE to take care of that... just removing everything and comparing always with numbers. So, my query for test would by like that:

select * from tb_contc WHERE REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','') = '1234567777';

First problem I've found is that that is not a valid index to create. Trying to create it returns me a " ORA-01450: maximum key length":

CREATE INDEX IX_06_TB_CONTC ON TB_CONTC REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','');

Apparently this kind of function in the index would create a index that is worst than making a full scan in the table... The next step was to add a SUBSTRING in the index, that allows to create it by limiting the size of the VARCHAR returned from my REGEX

CREATE INDEX IX_06_GR_CONTC ON GR_CONTC (substr( REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+',''), 1, 20));

Things got real ugly... Besides, to force the use of this index I need to add the exactly same query in my select.

Wouldn't be bad if it it had a good performance, but instead what I got is:

Without the index: 15 seconds the first time I execute the query, all the others would take basically nothing
With the index: 2 seconds every time.

Wraping up: Am I doing a good thing by using this kind of index with REGEX? Is there a better way?


texts: Efficiently perform multiple similar updates

texts:
I'm trying to extend some existing SQL (SQL Server) that builds a table of all items to act on using a number of queries. Some of these are very similar and costly, and I was hoping to get some advice on how to either combine them or rework this section to avoid doing the hard query multiple times. My SQL experience is a bit shallow, so maybe there is an obvious solution to this.

--Move Messages if moveMessages=1
UPDATE t1
SET t1.foo = temp.newFoo,
    t1.bar = temp.newBar
FROM Table1 t1
INNER JOIN @TempTable TEMP ON TEMP.baz = t1.bar
                          AND TEMP.moveMessages = 1
INNER JOIN Table2 t2 ON t2.type = 'M'
                    AND t1.thingId = t2.thingId

--Move Documents if moveDocuments=1
UPDATE t1
SET t1.foo = TEMP.newFoo,
    t1.bar = TEMP.newBar
FROM Table1 t1
INNER JOIN @TempTable TEMP ON TEMP.baz = t1.bar
                          AND TEMP.moveDocuments = 1
INNER JOIN Table2 t2 ON t2.type = 'D'
                    AND t1.thingId = t2.thingId

There are more repetitions of the above, but the only difference between each one is which column in TEMP is checked, and which letter is referenced for t2.type. So for example, for any rows in TEMP that have moveMessages=1, move all the 'M' types. For each row in TEMP that has moveDocuments=1, move all the 'D' types, and so on.

Is there a way to do all of this in one statement? Is this a situation when I should use another temporary table?


texts:
I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition.  I am trying to understand how the permissions work.

What I can see is (via the properties of many of these entities)

My Server Login can be linked to a Database User
My Database User can have one or more Database roles
One of the Database Roles is db_datawriter which owns schema db_datawriter

However at that point the trail goes cold.  Schema db_datawriter has a permissions page under its properties which is blank.

What defines precisely what schema db_datawriter's permissions are?


texts: How is localhost resolved by Oracle listener?

texts:
When I o an Oracle test installation on a Windows server accepting mostly the defaults, I end up with a listener.ora like

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

THat never seems to enable the listener, to accept requests from remote machines.
I have to add a line with the actual IP address or DNS-name of the server. Giving something like

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 169.254.239.75)(PORT = 1521))
    )
  )

From Technet I know, that Windows has ways to resolve localhost others than reading from  etc/hosts. And typing

ping localhost

shows this.

Each time I do a test installation I have to manually edit listener.ora and tnsnames.ora files and replace the localhost by actual data.

Is the problem to resolve localhost caused by having more than one network adapters?

My normal test configuration is a virtual machine with one adapter connected to the internal network and another connected to the internet via NAT.

Is localhost in those files thought as template to be replaced by a real dns-names or IP-adresses or are there true server-configurations, where the files can be used as generated by DBCA?


texts: Why can't this mysql user log in with password?

texts:
In phpmyadmin I created a user called "edward" with password and all privileges:

Yet when I try to log in via phpmyadmin, it says "Cannot log in to the MySQL Server".
When I try to log in on the command line (Windows 7), it won't allow me either:

But if I login in without a password, I get in:

and shows me only two of the tables.
I'm sure the password is correct and I've created multiple users which all can't log in with their passwords no matter how many rights I give them.
How can I get this user to be able to log in only with password and see all databases?

Addendum:
Here is a screenshot from phpmyadmin showing that "edward" has just as many rights as "root":


texts: Filesystem errors when installing Windows service from command-line

texts:
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld -- install
2019-07-30T15:08:27.660210Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.17) starting as process 1720
2019-07-30T15:08:27.670665Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2019-07-30T15:08:27.670899Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2019-07-30T15:08:27.671342Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to 'C:\Program Files\MySQL\MySQL Server 8.0\data\' (OS errno: 2 - No such file or directory)
2019-07-30T15:08:27.695923Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-07-30T15:08:27.696325Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.17)  MySQL Community Server - GPL.


texts:
Rather urgent one.

Have created an index on one of our tables and for some reason all data going into that table has stopped.

Can't see any blocking, but when I'm trying to drop or disable the index with either

ALTER INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] DISABLE
DROP INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] WITH ( ONLINE = OFF, MAXDOP = 1)

I get a report of deadlock preventing it, but nothing is being reported to sp_readerrorlog

Any clues as to what's going on?


texts: Create a monthly job to export from SQL Server 2014 to Excel

texts:
There are a lot of questions here asking "how to export from SQL Server to Excel", but every answer, together with stuff I know, is giving me problems (64bit system here).

We have a developer that monthly, runs a query 6 times (changing the year from 2010, 2011 etc.). Then, he copies and pastes the results into an Excel spreadsheet.

I'm having problems exporting this data from SQL Server 2014 to Excel.

This is the query:

SELECT
R.numero,
R.ano,
R.data 'data abertura',
R.datahora_conclusao  'data conclusão',
R.datahora_emissao 'data emissão',
S.[status],
(ins.INSTITUTO +
            ' - ' + dir.DIRETORIA +
            (CASE WHEN NOT nuc.NUCLEO IS NULL THEN  ' - ' + nuc.NUCLEO  ELSE '' END) +
            (CASE WHEN NOT equ.EQUIPE IS NULL THEN ' - ' + equ.EQUIPE ELSE '' END)) AS UNIDADE,
N.codigo 'codigo natureza exame',
Case E.cod_estadopreservacao when 1 then 'Preservado' when 2 then 'Não Preservado' else 'Laborátorio' end 'Local Exame',

u.nome 'Perito Designado'
FROM tbReps R
LEFT Join tbExames E on E.cod_rep = R.cod_rep
LEFT JOIN tbUsuarios U ON U.cod_usuario = R.cod_perito_primeiro
LEFT JOIN tbUnidades               AS uni WITH(NOLOCK) ON R.cod_unidade = uni.cod_unidade
LEFT JOIN lstUnidadesInstitutos AS ins WITH(NOLOCK) ON uni.cod_instituto = ins.cod_instituto
LEFT JOIN lstUnidadesDiretorias AS dir WITH(NOLOCK) ON uni.cod_instituto = dir.cod_instituto AND uni.cod_diretoria = dir.cod_diretoria
LEFT JOIN lstUnidadesNucleos AS nuc WITH(NOLOCK) ON uni.cod_diretoria = nuc.cod_diretoria AND uni.cod_nucleo = nuc.cod_nucleo
LEFT JOIN lstUnidadesEquipes AS equ WITH(NOLOCK) ON uni.cod_nucleo = equ.cod_nucleo and uni.cod_equipe = equ.cod_equipe
LEFT JOIN lstNaturezasExame N ON R.cod_naturezaexame = N.cod_naturezaexame
LEFT JOIN lstStatus S ON S.cod_status = R.cod_status
WHERE  year(r.data) = 2016 and r.data <= '2016-04-30 23:59:59'
order by data

I'm trying to think of something to import this directly into an Excel file, separated by year. I thought it would be a good idea to create 6 tables (2010...2011...2012 ) and use the Task > Export process, and then delete them. But I think there's a better (and more correct) way to do this.

Which OLE.DB provider should I use? Is there a script to do this? Some procedure that I don't know?

I'm using with (nolock) everywhere for test purposes only.


texts:
This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time.

When should I use an array and when to use values instead?

Is there any way to reduce a sequential search?

EXPLAIN ANALYZE
SELECT DISTINCT gu.user_id
FROM eb.groupusers gu
JOIN eb."group" g ON gu.group_id = g.id
    AND lower(g.group_code::text) = lower('HLVL1GS'::text)
JOIN eb.userexams ue ON ue.user_id = gu.user_id
JOIN eb.examresult er ON er.userexams_id = ue.id
WHERE NOT (gu.user_id IN (SELECT DISTINCT ha.user_id
                          FROM eb.hgsresults_archive ha))
ORDER BY gu.user_id

Unique (cost=928649.65..928658.50 rows=1769 width=4) (actual time=62327.900..62327.915 rows=39 loops=1)"

Sort (cost=928649.65..928654.08 rows=1769 width=4) (actual time=62327.899..62327.902 rows=108 loops=1)"
Sort Key: gu.user_id" Sort Method: quicksort Memory: 21kB"

Hash Join (cost=23790.71..928554.23 rows=1769 width=4) (actual time=62123.575..62327.842 rows=108 loops=1)"
Hash Cond: (ue.id = er.userexams_id)"

Hash Join (cost=14238.04..918083.16 rows=2374 width=12) (actual time=1968.672..62220.437 rows=1033 loops=1)"
 Hash Cond: (gu.user_id = ue.user_id)"

Nested Loop (cost=1425.18..903442.04 rows=567 width=4) (actual time=129.328..62065.633 rows=886 loops=1)"

Seq Scan on "group" g (cost=0.00..34.95 rows=5 width=8) (actual time=0.243..0.700 rows=1 loops=1)"
Filter: (lower((group_code)::text) = 'hlvl1gs'::text)"

Index Scan using "PK_groupusers" on groupusers gu (cost=1425.18..180678.29 rows=250 width=8)

(actual time=129.078..62064.642 rows=886 loops=1)" Index Cond: (group_id = g.id)" Filter: (NOT (SubPlan 1))" SubPlan 1"

Materialize (cost=1425.18..2048.78 rows=32974 width=8) (actual time=0.003..1.344 rows=17128 loops=24017)" ->

HashAggregate (cost=1425.18..1754.92 rows=32974 width=8) (actual time=16.277..20.815 rows=32974 loops=1)" "

-> Seq Scan on hgsresults_archive ha (cost=0.00..1342.74 rows=32974 width=8) (actual time=0.013..9.586 rows=32974 loops=1)" "

Hash (cost=7741.83..7741.83 rows=291683 width=12) (actual time=115.483..115.483 rows=291683 loops=1)" Buckets: 4096 Batches: 16 Memory Usage: 741kB"

Seq Scan on userexams ue (cost=0.00..7741.83 rows=291683 width=12) (actual time=0.013..58.304 rows=291683 loops=1)"

Hash (cost=5985.63..5985.63 rows=217363 width=8) (actual time=85.998..85.998 rows=217363 loops=1)" Buckets: 4096 Batches: 8 Memory Usage: 855kB"

Seq Scan on examresult er (cost=0.00..5985.63 rows=217363 width=8) (actual time=0.006..46.276 rows=217363 loops=1)" "Total runtime: 62330.811 ms"


texts: Exposing SQL Server database to internet

texts: I have a POS application that is used by a business. The application has a SQL Server backend and there’s a local machine that acts as a server for the DB. The other computers within the office connect to this server. This happens over TCP/IP. I am wondering if it is possible and/or how difficult it would be to expose this database and allow read access to an offsite application. Essentially I want to be able to pull data from a local database into AWS on a regular automated schedule. If anyone has any ideas that could help point me in the right direction, that would be great. This is not an application I’m a developer of, so I don’t have access to the application code or anything like that. I guess what it boils down to would be the equivalent of an off-site backup. Thanks in advance!


texts:
I'm trying to benchmark Read/Write speed in MySQL 5.5. Currently I have a stored procedure that insert rows with random numbers to a table and I'm measuring time of execution before and after configuration changes.

Here's my procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRand`(IN nor INT, in minr int, in maxr int)
begin
        declare i int;
        declare lastid int;
        set i =1;
        start transaction;
        while i <= nor do
        insert into tbltest(Value) values (minr + CEIL(rand() * (maxr-minr)));
        set lastid = (select MAX(id) from tbltest);
        set i = i +1;
        end while;
        commit;
    end

Do you know any good way to benchmark this ? Maybe my procedure needs some improvement?


texts: How to write function in sql which accept table as input and return result back as table?

texts:
I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in the function.

Something like this:

CREATE FUNCTION fn_CalculateListing(@ListingData TABLE(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE
AS
BEGIN
    DECLARE @result TABLE
    (
        result1 INT,
        result2 INT
    )

    INSERT INTO @result(result1,result2)
    SELECT value1*value2, value1/value2
    FROM ListingDate

    RETURN @result
END

But the above syntax give me an error near declaration @ListingDate TABLE as incorrect syntax.

Is above syntax correct, or should I use another one?


texts:
I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this is becoming quite problematic as the database keeps growing.

Query:

select distinct(scanid)
from misspelled
where word = ?

knowing that the table 'misspelled' can grow up to a dozen million rows, is there any way to make this work ?

Thanks, here are more details:

Query:

select distinct(scanid)
from misspelled
where word = ?

Explain Analyze:

HashAggregate  (cost=76018.68..76018.79 rows=11 width=8) (actual time=38359.639..38359.823 rows=1071 loops=1)
  Group Key: scanid
  ->  Bitmap Heap Scan on misspelled  (cost=1014.12..75918.78 rows=39960 width=8) (actual time=49.946..38319.196 rows=37993 loops=1)
        Recheck Cond: (word = 'Facebook*'::text)"
        Heap Blocks: exact=28920
        ->  Bitmap Index Scan on test_scanid  (cost=0.00..1004.13 rows=39960 width=0) (actual time=40.541..40.541 rows=37993 loops=1)
              Index Cond: (word = 'Facebook*'::text)
Planning time: 197.561 ms
Execution time: 38360.440 ms

Also available at: http://explain.depesz.com/s/7ttn

Table definition:

CREATE TABLE misspelled
(
  word text,
  url text,
  occnum BIGINT,
  proper BOOLEAN,
  scanid BIGINT,
  CONSTRAINT misspelled_scanid_fkey FOREIGN KEY (scanid)
      REFERENCES scans (scanid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT misspelled_word_url_scanid_key UNIQUE (word, url, scanid)
);

CREATE INDEX test_scanid
  ON misspelled
  USING btree
  (word COLLATE pg_catalog."default", scanid);

http://pastebin.com/D6jrT2NC

Version 9.4.0

If you need anything else please let me know.

Thanks


texts:
I have a server that hosts a SQL Server 2017 Entreprise Edition database. I changed domain on the server (e.g. attds.mg in fdsqq.mg).
The problem is that I can't connect my application to the database. The account I used as a SA administrator for SQL Server does not even work.
I checked the SQL Server Configuration Manager, all services were stopped. So I can't connect to my DB.
Would someone have a solution to reactivate the services? How could I connect to the database?


texts: Does leaving a connection open for a matter of seconds cause deadlocks?

texts:
I have been testing a system application and have noticed that it is slow when it connects to the database overseas.
This is to be expected to some extent, but there is a big difference with the domestic version, and after investigation realized that after each function the connection is closed.
So, for one operation the connection is opened and closed around 30 times causing considerable lag.
I asked why can't the connection be left open and closed at the end.
The reply was, "It causes deadlocks".

How true is this, and if there is any advice please provide some.
(System application is used by few hundred people but not constantly in use by everyone.)


texts: Get count of relationship rows between three tables that are not duplicates

texts:
I have a (local) MySQL database, which is a Drupal 6 export. It has these relevant tables:

node - Contains articles, plus also information about their department and sections. I'm interested in nid and title columns.
content_field_department - Has the relationships between article nodes and department nodes. I'm interested in nid (the article node) and field_department_nid (the department node) columns.
content_field_section - Has the relationships between article nodes and section nodes. I'm interested in nid (the article node) and field_section_nid (the section node) columns.

I'm trying to get an accurate COUNT for how many times a given article node is related to each section or department node (performance is not relevant for me at this time, since it's local and it can take as long as it needs to without causing problems). That is, I'd like data like this:

+-----------------+-------------------+
| Section Name    | Count of Articles |
+-----------------+-------------------+
| Department Name | Count of Articles |
+-----------------+-------------------+

The issue

One problem is when the interface was originally built, a department choice for an article also included all of the sections. A section choice, though, never included the departments. So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.

My current try

My current attempt is like this:

To get a department COUNT:

SELECT DISTINCT d.field_department_nid as tid, n.title as name, (
        SELECT COUNT(DISTINCT nid, field_department_nid)
        FROM content_field_department d2
        WHERE d2.field_department_nid = d.field_department_nid
        AND nid NOT IN (
            SELECT s.nid from content_field_section s
            LEFT OUTER JOIN node n2 ON s.nid = n2.nid
            WHERE field_section_nid IS NOT NULL
        )
    ) as drupal_department_count
FROM content_field_department d
LEFT OUTER JOIN node n ON d.field_department_nid = n.nid
ORDER BY drupal_department_count DESC

To get a section COUNT:

SELECT DISTINCT s.field_section_nid as tid, n.title as name, (
    SELECT COUNT(DISTINCT nid, field_section_nid)
        FROM content_field_section s2
        WHERE s2.field_section_nid = s.field_section_nid
        AND nid NOT IN (
            SELECT d.nid from content_field_department d
            LEFT OUTER JOIN node n2 ON d.nid = n2.nid
            WHERE field_department_nid IS NOT NULL
        )
    ) as drupal_section_count
FROM content_field_section s
LEFT OUTER JOIN node n ON s.field_section_nid = n.nid
ORDER BY drupal_section_count DESC

I had also tried without the NOT IN additions, but the COUNT is too high, I think because the same node combination can be COUNTed in both tables.

What direction do I need to go with this?


texts: Grouping Selecting unique values in a single column


texts: forcing innodb recovery of a corrupted database

texts:
A poorly designed script selected records from two existing tables and added records to a newly created table for 24 hours until I killed it by terminating the MySQL 5.6 command line client and restarting the server.  I estimate that the script inserted over 70 million rows into the new table.  The result is that service.msc will not start the MySQL service.  I took various steps to address this problem, including deleting the log files and the myservername.err file, then restarting the server.  The new myservername.err file contains a long stream of repetitive warnings which state that I need to force an innodb recovery because the database is corrupted.  How can I accomplish this?

I want to:

1.) start the corrupted database, and then
2.) do a rollback to the status of the database before the script was run.

I could either select a time point, or simply delete the newly created table because the only activity in the database since things worked well has been the running of the poorly designed script.  What steps do I need to take to restart the corrupted database and then rollback the database to its precorrupted state?

I uploaded the error log to a file sharing site.  You can read it by clicking on this link.

What I imagine is that I need to add the following line to my.ini, immediately after the [mysqld] line:

[mysqld]
innodb_force_recovery=?

But what number do I use instead of ??  And then what code do I use to do the rollback?  Do I simply write drop table new_table_name?  Or do I do a rollback command to a specific date?  (I moved the log files to a different folder, so would I need to reimport the log files?)  What would this look like in code?

EDIT:

I finished mysqldump, then moved the contents of the /data directory into a backup location.  I then moved /data/MySQL subdirectory contents back into the /mysql subdirectory of the otherwise empty /data directory.  But when I try to start the MySQL windows server, I get error 1067.  I uploaded the contents of the newly created myservername.err file to a file sharing site.  Here is the link to view the error log.


texts: Updating rows in tables concurrently using Multithreading in Java

texts: I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be possible if I divide batches of 100 rows in excel file and assign different batches of rows to different threads to concurrently update the rows in table from multiple threads ?


texts:
While i am trying to connect with with database by the command
sqlplus /nolog

than the error message is coming

"sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows"

Earlier it was working fine i think some path issue is there so can any one tell me how to resolve the issue or how to set the path on windows machine


texts: SELECT gives different results with/without FOR UPDATE in same transaction

texts:
I'm sorry but I havent't found a better wording for the title.

Given the table a:

CREATE TABLE a (
    id INT NOT NULL auto_increment PRIMARY KEY,
    ref VARCHAR(10) NOT NULL,
    val INT NOT NULL
);

INSERT INTO a(ref, val) VALUES ('ten', 10);

And two transactions Tx1 and Tx2 with default isolation level, whose queries are issued in the following order, is the result shown in step [*] of transaction Tx2expected?

Tx1: START TRANSACTION;

            Tx2: START TRANSACTION;

            [^] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "10"

Tx1: SELECT * FROM a WHERE id='1' FOR UPDATE;
===>
    "id"    "ref"   "val"
    "1"     "ten"   "10"

            Tx2: SELECT * FROM a WHERE id='1' FOR UPDATE;
            (waits)

Tx1: UPDATE a SET v=20 WHERE id;

Tx1: COMMIT;

            Tx2: (locked on 'SELECT * FROM a WHERE id='1' FOR UPDATE')
            (unlocks)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "20"

            [*] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "10"

[*]: Shouldn't this give the updated row value 20 for column val?

UPDATE: If I remove the query [^], then [*] yields the value 20.

This is on 5.6.39; queries were issued using third party client - HeidiSQL - though I guess that shouldn't matter.


texts: Bandwidth comparison between log shipping and transactional replication

texts:
Which technique uses more network bandwidth:

Log shipping
Transactional Replication

Can some one share any benchmarks for the same ?

What would be the Memory and I/O impact on the Primary server when we use any one of the technique ?

Thanks,

Piyush Patel


texts: Track row origin in MySQL multi source replication

texts:
Hi I would like to create a multi source replication topology where a few different master databases replicate to one big slave database.
I want to know for each and every row on the slave from which master it came from.
My first instinct would be to add a "system_id" column on each master table but I wonder if there is a better way to track the origin of the rows.

Thanks


texts: 3 Node mongo db authentication setup fails

texts:
I have 3 node mongodb replica set as below

Primary node Secondary node Arbiter node

Replication set is enabled for all three. I am trying to set up authentication for these nodes and i followed the below steps to configure it.

Step 1: Created a root user, added him to role: root with password in the primary node

Step 2: Created a key file using the command - openssl rand -base64 741 > mongodb.key in the primary node and copied the key file to all the 3 nodes

Step 3: Added the below configuration to all three nodes.

Security: authorization: enabled keyFile: /pathtokeyfile

Step 4: sudo service mongod restart, on all three nodes

After completing above steps i tried accessing the DB with username and password in the primary node and i could read the DB and files

When i try the same in secondary and arbiter nodes i get the below error

  rsss0:SECONDARY> show dbs
  2019-03-06T09:10:40.884+0000 E QUERY    [thread1] Error: listDatabases failed:{
      "operationTime" : Timestamp(1551863438, 9),
      "ok" : 0,
      "errmsg" : "not master and slaveOk=false",
      "code" : 13435,
      "codeName" : "NotMasterNoSlaveOk",
      "$clusterTime" : {
          "clusterTime" : Timestamp(1551863438, 9),
          "signature" : {
              "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
              "keyId" : NumberLong(0)
          }
      }
  } :
  _getErrorWithCode@src/mongo/shell/utils.js:25:13
  Mongo.prototype.getDBs@src/mongo/shell/mongo.js:67:1
  shellHelper.show@src/mongo/shell/utils.js:860:19
  shellHelper@src/mongo/shell/utils.js:750:15
  @(shellhelp2):1:1


texts: Big Data vs Relational Database on making structure of Data-ware house for telecom data analysis

texts:
I'm currently doing my final year project on telecommunication mining. On starting project I got confuse on choosing database. As data on mobile communication is going on increasing so I think Big data is useful on reducing time complexity of the project. But I stuck my head on finding better option. I had no experience on Big Data except its general concept but I'm quit familiar with relational database (oracle).

So my question is:- Which data will be best option on doing such project and why? So I appreciate any suggestion regarding the database selection and design.

  Project Background:-
  We have 100's of thousand of data of call details of customer along with their personal information and we have to perform various mining technique for pattern analysis.


texts: MYSQL to other DB migration using MYSQL workbench

texts: is it possible to migrate MYSQL to other database platform like MS SQL 2012 using the MYSQL workbench migration wizard?. Everywhere the only destination DB is MYSQL, the reverse process is not explained at all.


texts: Economic Database Architecture Possibilities w/ Data of Varying Length

texts:
I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, such as percentage changes, are fractions of a percent; others, such as national debt figures, are 14+ digits long. Furthermore, we have a business requirement that states some data points need to be correct to the 6th decimal place.

We currently have over 500,000 rows in the legacy database, but anticipate growing that number substantially in the future, as we are building the new database with point-in-time considerations and, for the most part, will not be deleting or updating rows, only adding new rows and superseding the old rows.

All potential tables containing this economic data will be structured as follows:

id | country_id | period_id | [economic_data] | data_type | date_created | date_superseded

My question is this:

Is it best to :

Break out all of these individual economic data series into their own tables, given that the data comes in varying sizes, or
Combine all this data into one massive table, given the identical structure of all economic data tables and the simplicity it would offer for writing queries?

We collect over 200 data series and are planning on increasing that number every year, so option 1 would require creating and maintaining 200+ tables.

Option 2 seems the easiest to develop and maintain, but I wonder what the implications might be on query performance and storage.

Any thoughts or suggestions?


texts: NEXT VALUE FOR SEQUENCE inside a CASE Statement

texts:
I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year )

However you can not use NEXT VALUE FOR inside a CASE statement. Which normally makes sense. However I am hoping that I have a legit edge case.

Is there any way around this (which wouldn't need elevated permissions in prod)?

Perhaps with some indirection, wrapping each NEXT VALUE FOR in some UDF and then call them in a CASE statement?

Or is that just crazy talk?


texts: SQL Server restore full backup + partial differential backups

texts:
I created a full backup of my database using SQL Server Management Studio wizard, and then I perform differential partial backup each day via cmd to the same file.

SQL Server version is 2008 R2.

Here is the command I use to perform backup:

BACKUP DATABASE [db_name]
TO DISK = N'path_to_backup\db_name.bak'
WITH DIFFERENTIAL,
     DESCRIPTION = N'Differential backup of the staging database',
     NOFORMAT, NOINIT,
     NAME = N'db_name-Differential_Backup', SKIP, NOREWIND,
     NOUNLOAD, STATS = 10
GO

I can't figure out how to restore the entire database (full backup and all of the partial backups).

I tried restoring full backup with NORECOVERY and PARTIAL options:

RESTORE DATABASE [db_name]
FROM DISK = N'path_to_backup\db_name.bak' WITH
FILE = 1,
  MOVE N'db_name' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name.mdf',
  MOVE N'db_name_log' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name_1.ldf',
  NORECOVERY, PARTIAL, NOUNLOAD,  STATS = 10
GO

This works ok, but then I try to restore the partial backup using the following command:

RESTORE DATABASE [db_name]
FROM DISK = N'path_to_backup\x12parsedb.bak' WITH
FILE = 2
GO

This also completes without errors but it overwrites the data from the full backup and I'm left with only small fragment of data that was in this partial backup.

All of my backup data is stored in a single file, and there are around 130 partial backups.

I've tried to use Management Studio wizard but the result is the same.


texts: How to represent directed graph with multiple parents?

texts:
http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table.

I'd like to model a similar data structure, except that nodes may have multiple parents.

Given:

If we remove [B, C] I expect to end up with:

and if we remove node B I expect to end up with:

However, if you use the author's algorithm for removing links or nodes you will notice that it tags [D, C, 1] for deletion, which is undesirable.

What I've tried so far

I have tried adapting the original data structure by adding a references column that indicates how many ways there are to travel between two nodes. In the above example, you can travel from A to C either through B or through D. The idea would have been that when B gets removed, the path from A to C gets kept and the reference count decreases from 2 to 1. It was nice in theory, but I couldn't figure out how to get the implementation working and now I wonder if it's at all possible (the data structure might not contain enough information to figure out which rows to remove).

What I'm asking

How would you adapt Closure Tables to support multiple parents? What alternative data structures would you recommend? https://stackoverflow.com/q/4048151/14731 contains an exaustive list of such data structures, but it's not clear which ones support (or are best for) multiple parents.


texts: How do you manage service account passwords?

texts:
The security folks want all AD passwords to expire every three months. I'm really not excited about this, since I definitely won't remember, and I'll likely be on my boat, drunk, when they expire.

Question How often do you/should you change the passwords on your sql service accounts?


texts: Does PostgreSQL PDPG support Yakkety (Ubuntu 16.10)?

texts: The official repository does not have Yakkety (16.10 packages). It seems it stops at Xenial.


texts: how to query SSISDB to find out the errors in the packages?

texts:
I have seen this question
SSIS 2012 - How to Query Currently Running Packages in T-SQL?

It gives me the following script:

SELECT
    E.execution_id
,   E.folder_name
,   E.project_name
,   E.package_name
,   E.reference_id
,   E.reference_type
,   E.environment_folder_name
,   E.environment_name
,   E.project_lsn
,   E.executed_as_sid
,   E.executed_as_name
,   E.use32bitruntime
,   E.operation_type
,   E.created_time
,   E.object_type
,   E.object_id
,   E.status
,   E.start_time
,   E.end_time
,   E.caller_sid
,   E.caller_name
,   E.process_id
,   E.stopped_by_sid
,   E.stopped_by_name
,   E.dump_id
,   E.server_name
,   E.machine_name
,   E.total_physical_memory_kb
,   E.available_physical_memory_kb
,   E.total_page_file_kb
,   E.available_page_file_kb
,   E.cpu_count
,   F.folder_id
,   F.name
,   F.description
,   F.created_by_sid
,   F.created_by_name
,   F.created_time
,   P.project_id
,   P.folder_id
,   P.name
,   P.description
,   P.project_format_version
,   P.deployed_by_sid
,   P.deployed_by_name
,   P.last_deployed_time
,   P.created_time
,   P.object_version_lsn
,   P.validation_status
,   P.last_validation_time
,   PKG.package_id
,   PKG.name
,   PKG.package_guid
,   PKG.description
,   PKG.package_format_version
,   PKG.version_major
,   PKG.version_minor
,   PKG.version_build
,   PKG.version_comments
,   PKG.version_guid
,   PKG.project_id
,   PKG.entry_point
,   PKG.validation_status
,   PKG.last_validation_time
FROM
    SSISDB.catalog.executions AS E
INNER JOIN
    ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN
    SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id
                                 AND P.name = E.project_name
INNER JOIN
    SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id
                                   AND PKG.name = E.package_name;

But it does not answer my quest. I am investigating the reasons why packages fail and I need to get hold of the error messages.

Where can I find it?

I would like to use T-SQL to query for the error message.

I have also this script below, that takes me near, but not quite:

SELECT
    q.*
FROM
    (SELECT em.*
     FROM SSISDB.catalog.event_messages em
     WHERE em.operation_id = (SELECT MAX(execution_id)
                              FROM SSISDB.catalog.executions)
       AND event_name NOT LIKE '%Validate%') q
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
WHERE package_name = 'InfoGroup Feed.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC

This is the email I would like to tackle, how did they get to that error message:

  Any information as how to troubleshoot SSIS errors is welcome.


texts: Count numbers in scientific notation (E-notation)

texts:
I'm trying to check if a part number field has scientific notation values and came up with this simple solution:
SELECT count(*) as overall,
       count(*) filter (where partnumber ilike '%E+%') as matches
FROM   mytable
WHERE  partnumber ilike '%E+%';

Some of these strings are not scientific number. For example:
WS-X4748-UPOE+E=

How can I identify scientific numbers?
I tried this:
WHERE partnumber ilike '[0-9]%E+%[0-9]'

But it didn't seem to work.


texts: Get database creation timestamp from PostgreSQL?

texts:
Is there a way to get a date and time of when a particular database was created in Postgresql?

The \list command does not provide a timestamp.


texts: Setting a column's default value by adding a constraint

texts:
I wrote this query:

ALTER TABLE test2 add constraint setting_value33 default male FOR namee;

But I get this error when running it:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default male FOR namee' at line 1


texts:
So, I have this query working great

SELECT u.username,  SUM(kort) as total
FROM user as u
LEFT JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
order by total DESC

But I want to remove those which the total is 0.

I tried this without any result

WHERE r.date >= '$date' AND total > 0

But am I not allowed to use WHERE on the result of SUM?


texts: Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows

texts:
I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why there is such a big difference between actuall plan and the query result?
Query plan is here https://www.brentozar.com/pastetheplan/?id=rJWX327pB


texts: any possible to mongo restore multiple database with a single command in Version 4.4?

texts:
I am trying to restore a bunch of application databases, currently using one command for each one database restoration. is there batch restore with mongorestore command?
thanks,


texts:
When I call SCOPE_IDENTITY in Sybase, I get

  [Error] Script lines: 1-6 --------------------------  Function 'SCOPE_IDENTITY' not found. If this is a SQLJ function or SQL
  function, use sp_help to check whether the object exists (sp_help may
  produce a large amount of output).  Msg: 14216, Level: 16, State: 1
  Server: DGTSYBASEDEV101, Line: 3

This is my code:

INSERT into IVR_SESSION (PHONE_NUMBER,IVR_TYPE, CALLING_COUNTRY,ADD_DATETIME,UPDATE_DATETIME)
values ("464545454", "asdd", "AUS",getdate(),getdate())
SELECT SCOPE_IDENTITY()


texts: Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

texts:
Can anyone help settle what I thought was a simple question.

A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically.  He posited that a good reason to bring them as current as possible was to reduce security vulnerabilities caused by them.  I countered that formerly SPs and currently CUs and hotfixes would surely patch those vulnerabilities, i.e. running a database in 2005 compatibility mode in a 2014 SQL Server instance surely does not revert that database back to 2005 SP4 security levels/vulnerabilities.

I've been searching for documentation to back this claim up but I've been unable to find any.  Does anyone know of any authoritative documentation that would show me either correct or in error?


texts: Oracle database link to MySQL database on RedHat Linux

texts:
I have an Oracle 11g database on a RedHat server and also have MySQL database on another RedHat server. I want to create a link to the MySQL server from the Oracle which allows me to execute queries in MySQL from Oracle.

Is this possible?


texts: Unable to read master log position while setting up MySQL slave

texts:
Hi I am trying to setup a data replication server.
I am using MariaDB and phpMyAdmin to interface with the 2 DB's

I have set up my master with adding the following configuration to my my.ini file

server-id=691391
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=mytestdb

I have then restarted the MySQL server so that phpMyAdmin now shows that it is set up correctly as a master as seen in the screenshot below:

I have then logged into my slave DB with phpMyAdmin and tried to connect it as a slave. When I enter in the details of the slave user that I have already created, I get the error message on the slave

Unable to read master log position. Possible privilege problem on master

Does anyone know what the issue could be here as I cant seem to work it out.

Thanks


texts:
I've been researching MongoDB and trying to see if it would be a fit for my company's product catalog.

I came across database references: http://docs.mongodb.org/manual/reference/database-references/ and had some questions about how exactly to link documents together.

Assume we have two db.categories and db.products collections... I have documents in db.products that need to link to their categories in db.categories.

I have a "category" field in db.products that I will store the objectID of the category document in. However, I do not see a way to specify that this objectID references the db.categories collection.
Do I need to use DBREFs?

Below is a brief example of my data...

Document in db.categories:

 {
        "_id" : ObjectId("546b72a47ea1d03593000158"),
        "name" : "Category1",
        "type" : "type1 of category1",
        "spec_columns" : [
            {
                "field" : "capacity",
                "display" : "Capacity (tons)"
            },
            {
                "field" : "weight",
                "display" : "Weight (lbs)"
            }
        ]
    }

Document in db.products:

{
    "_id" : ObjectId("546a575f7ea1d03593000154"),
    "model" : "NER003H",
    "category" : ObjectId("546b72a47ea1d03593000158"),
    "capacity" : "1/2",
    "weight" : 10
}

Any help, suggestions and advice is welcome and greatly appreciated.

Darius


texts: Postgres. Generate continuous series for multiple categories

texts:
I have a table that looks like this:

update_date  start_date   end_date   category_1 category_2    value
2013-03-04   2013-05-11  2013-06-08     a1           b1        23
2013-02-19   2013-03-16  2013-09-21     a2           b2       112
..

Now i want to transform the above table to this format:

date   category_1   category_2   value   update_date

For each pair of (category_1, category_2) I want to have a continuous series such as date is between start_date and end_date for each pair of (category_1, category_2) and keeping the respective value and update_date.

For example, for (a1, b1) I would have:

date       category_1  category_2   value   update_date
2013-05-11    a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
2013-06-08    a1          b1          23     2013-03-04

and this table would continue for each pair of (category_1, category_2).

I reckon there'd be duplicates of values in date column but i'm OK with that as long as there are no duplicates of (category_1, category_2) pair.

Anybody knows how to solve this?

Thank you in advance.


texts:
I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has to be in a specific format, but there is one catch: there can be multiple PseudoIds in the same row, separated by a semi-colon.

I have already figured out how to write the LIKE statement for 1 PseudoId, but I have some trouble to write the following query: retrieve all the Id's of A where all the PseudoIds (can be 1 or more) are in the correct format. So for 1, this is easy, but for more a little more tricky.

What I thought I had to was 'ungroup' my table A so that I return a new table A' where the following applies: if there is only 1 PseudoId on a row, then just return the row. Else, split the row on semi-colon and return the rows, which will all be identical but have a different PseudoId.

So for example:

ID | PseudoId
-------------
 1 | ABC
 2 | DEF
 3 | GHI;JKL
 4 | MNO
 5 | PQR;STU;VWX

'Ungrouping' this should yield:

ID | PseudoId
-------------
 1 | ABC
 2 | DEF
 3 | GHI
 3 | JKL
 4 | MNO
 5 | PQR
 5 | STU
 5 | VWX

I would probably use a table-valued function, but not entirely sure how to accomplish this... Much appreciated.


texts: How many databases can an Amazon Aurora instance hold?

texts:
When the word database is defined for table-families,

Can an Aurora instance hold more than a database?

Does any Aurora instance even has the concept of separated database(table family)?

Thanks


texts: Database design for roles for members of organisations

texts:
I'm developing an IDP providing the same REST-api for a number of organisations. Each user is a member of at least one organisation and it is based on OAuth2 and OpenID Connect.

Every user of every organisation is either an admin or a regular member, but more roles may be added in the future.

After some research I've come to the conclusion that there are two ways in which I can solve this. Both implies that there are some kind of policy-service that authorizes each request, such as OPA.

Solution 1, global roles
Every user should have a role in each organisation of which he/she is a member, but there will also be global endpoints such as POST /orgs which will create a new organisation. These endpoints should not be available to anyone but myself in this point in time. Therefore I need to create roles that are independent of organisations, see the SYSTEM resource type in the role-table.

However, this will require me to either skip the org_membership table completely and rely solely on roles when it comes to list members of the organisations or keep org_membership and role synchronized.

Solution 2, organisational roles
The second solution is to skip the role-table altogether and instead add the role-field to the org_membership table. This ensures that every member has a role but now I have to find another way to authorize against system-wide endpoints.

Which would be the preferred solution for my case or is there anything else I should consider?


texts: Create multi-key JSON object with integers or floats as values

texts:
I have a table of text and double precision type. From it, I want to build a JSON object that looks like this: {"a": 1.0, "b": 1.2, "c": 3.4} (edit: to clarify, the keys are the text column in my table, and the values are the double precision column). Note that there are no quotes around the values because they are floats, not strings, which the JSON standard supports.

Using Postgres 9.5. There's the json_build_object function that takes two parallel text arrays and builds a JSON object with text keys and text values... also works with NULL values, turning them into null (no quotes) in JSON. So I'd get {"a": "1.0", "b": "1.2", "c": "3.4"}. But what if I want integer or float values? It won't take any numeric type arrays.

I got around it for now by making my own function to handle this in Python! Is there a way to do this without defining a new function?

CREATE OR REPLACE FUNCTION json_build_object_float_values(keys TEXT[], vals DOUBLE PRECISION[]) RETURNS JSONB AS
$$
    assert len(keys) == len(vals)
    import json
    d = {}
    for i in range(len(keys)):
        d[keys[i]] = float(vals[i])
    return json.dumps(d)
$$
LANGUAGE 'plpython3u' IMMUTABLE;

(note if you use this, it returns JSONB, but you could change it to JSON)

Edit, here's test input and expected output:

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);
-- Here's my solution using my custom function...
SELECT json_build_object_float_values(array_agg(str), array_agg(num)) FROM test;
-- expected output:
--  json_build_object_float_values
-- --------------------------------
--  {"a": 1.0, "b": 2.3}
-- (1 row)

Simpler one:

SELECT json_build_object_float_values(ARRAY['the', 'quick'], ARRAY[1, 3]);

 json_build_object_float_values
--------------------------------
 {"the": 1.0, "quick": 3.0}
(1 row)


texts: Can I use a script to restore multiple SQL databases from .bak files?

texts:
I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp directory. What I'm looking for is a way to mass restore those .bak files so that I don't have to spend all weekend doing it manually.

I found a decent stored procedure here : SQL Restore

I put it in place and tested and I'm getting the following error:

  Msg 3118, Level 16, State 1, Line 1
  The database "TF058" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I'm running SQL Server 2012 and the databases are in FULL recovery mode.


texts:
in psql if i do \d+, I get something like

 Schema |          Name           |   Type   | Owner  |    Size    |     Description
--------+-------------------------+----------+--------+------------+-------------
 norsar | Routing Result          | table    | morten | 16 kB      |
 norsar | Routing Result_id_seq   | sequence | morten | 8192 bytes |

and so on. The last field, "Description" is always blank. Where does that field read its content? - and thereby, how can I set a description on a table? In some cases that could be quite useful - the same thing for description on columns that are shown if i do \d+ <tablename>.

I have tried \set ECHO_HIDDEN ON, but that didn't make it much clearer.


texts: Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

texts:
I have setup Automated backup in SQL Server 2012 Enterprise Database backup through 'Maintenance Plan'. I have also configure AlwaysOn High Availability to that server .There is 5 databases in the server, where there is 3 databases has added "Availability Database" in Availability Groups and properly configured with 'secondary replica' & where 'Availability Mode' is 'Synchronous commit' between both server replica.

The 'Maintenance Plan' backup is configure with 'copy-only backup' . Because i have added 3 databases in 'Availability Database' in availability group and which 'backup type' is 'full'.
I am also attaching the screen shot here

Whenever i am not selecting 'copy-only backup' in 'Back Up Database Task' window , as per microsoft alert message ' at promtly windows shows the message like ' This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. For information , see http://go.microsoft.com/fwlink/?Linkid=208213.

whenever i am selecting 'For availability database,ignore Replica Priority for Backup and Backup on Primary Settings'. The message shows like that 'This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. Note: Ignoring the backup priority and availability group settings may results in simultaneous parallel backups if the maintenance plan is cloned on all replicas. for more information, see: http://go.microsoft.com/fwlink/?Linkid=208213

So, I have finally select the option like 'copy-only Backup' due to copy-only backup in 'secondary replica' .

Note:- The 'Availability mode' of server is 'Synchronous commit' mode ( from 'Primary Replica Server' to 'Secondary Replica Server').

The schedule backup is running successfully , and 2 backup files are generating in (H: drive).Because out of 5 database (3 database are in full recovery mode, which are added in 'Availability Databases' and other 2 database are in 'simple recovery model'. Only 'Simple Recovery model' database backup file is generating. As microsoft BOL Here and MSSQLTIPS Blog documentation Here At the time of this writing, only transaction log backups and full backups with COPY_ONLY are supported on secondary replicas. But the'text file report' is not generating.

I am attaching the 'Log file viewer' history screen shot here

I am also attaching the backup preferencesscreen window here.

Note:- The Schedule Backup location drive is 'Network Drive'. In mine case here, it is (H:) Drive. As shown in picture.

I have configure the folder path of 'Reporting and logging' of my network drive. Here it is (H: Drive). I am attaching the screen shot of that window here

My MS SQL Server 2012 Enterprise version is

  Microsoft SQL Server 2012 - 11.0.2325.0 (X64)     Jun  8 2012 18:54:43
    Copyright (c) Microsoft Corporation     Enterprise Edition: Core-based
  Licensing (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

-----------------After Edit ---------------

Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.2325 Report was generated on "Primary Replica instace ID".
Maintenance Plan: AlwaysOn Backup
Duration: 00:00:01
Status: Succeeded.
Details:
Back Up Database (Full) (Primary Replica instance ID)
Backup Database on Local server connection
Databases: Database1,Database2,Database3,Database4,Database5
Type: Full
Append existing
Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:- Here i have edited my Server instance id as well as Database Name . Because this is my Live server database. Here only Database4 and Database5 file is generated after execution of schedule backup. Which time is showing like this in mail message. Which is

Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:-I am not including TSQL script here.

My concern is that What i am committing the mistake here for 'text file report' generation in 'Primary Replica Server'.
Any tips or suggestions will be appreciated.


texts:
We have a database with an owner 'dbowner'. Our install procedure drops the login for user 'dbowner' and creates it again.

On any other version of SQL Server the following SQL query succeeds:

USE [master]
DROP LOGIN 'dbowner'

On SQL Server 2014 the same query returns the following error:

  Login 'dbowner' owns one or more database(s). Change the owner of the database(s) before dropping the login.

Can anyone explain why this would be different on SQL Server 2014? Dropping of login works on all other versions of SQL Server (2005, 2008 and 2012).

Is there any setting to force the same to work on SQL Server 2014?

Update:

I can replicate this with an example that uses Windows users. Any ideas?

CREATE LOGIN [comp\winuser] FROM WINDOWS;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO [comp\winuser];
GO
DROP LOGIN [comp\winuser];
GO

Update 2:

Thank you for your comments. I agree that dropping the login of owner is not desirable (but works with Windows user everywhere except on SQL Server 2014). However our installation scripts currently depend on it and the extremely complex database installation procedure was made by some guy 15 years ago (he no longer works with us). It is an area that we like to avoid changing if possible (as long as it works).

As I see it, we have three options:

Make SQL 2014 to act like the previous versions and leave the scripts as they are. We prefer this option and I posted here to see if this could be done.
Change the owner to some other user, drop and create owner login and move the owner back. This is a hack but might work.
Change the way we handle users and stop dropping login of the owner. This would be the correct way but also the biggest change.


texts: Creating report based on the sql statement

texts:
I would like my report to display something image like as below

so can i know is it how my sql for table has to be when creating it

    create table attendance
    (
       Student_ID VARCHAR2 (15) primary key,
       Student_Name VARCHAR2 (50) not null,
       "Date1" VARCHAR2 (15) not null,
       "Date2" VARCHAR2 (15) not null,
       "Date3" VARCHAR2 (15) not null,
       "Date4" VARCHAR2 (15) not null,
       "Date5" VARCHAR2 (15) not null,
       Present number (10),
       Late number (10),
       Absent number (10),
       Total_Attendance number (10)
    );

for present, late, absent and total attendance i will be using like countif but this i have to research on it how to use in oracle. I would like to know is this correct the way it should be used for sql in creating table so that i will get a report the looks as in the picture


texts: Cannot remotely connect to oracle express

texts:
I have installed Oracle Express in a oracle-6.5-x64 virtual machine (let's called it host). The installation went fine, I am able to connect and run commands using SQLPlus from the host.

Now I'm trying to use SQLPlus from my Mac notebook (let's call it client) to access that remote database. I have installed the client packages and set the proper environment variables. I have tried connecting using EZConnect:

sqlplus system/<password>@<host-ip>[:1521]/XE

and also with TNS:

sqlplus system/<password>@XE

Both commands give me the following message:

ERROR:
ORA-12541: TNS:no listener

For the last option I have created a tnsnames.ora file in the client and exported a TNS_ADMIN variable pointing to the folder containing that file. The contents of tnsnames.ora are:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I have tried using the host's IP instead of the hostname:

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.33.1)(PORT = 1521))

But that failed as well.

The lsnrctl status command on the host returns the following:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2014 11:39:22

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Estabelecendo conexão com (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS do LISTENER
------------------------
Apelido                   LISTENER
Versão                    TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Data Inicial              09-JUL-2014 11:31:24
Funcionamento         0 dias 0 hr. 7 min. 58 seg
Nível de Análise     off
Segurança              ON: Local OS Authentication
SNMP                      OFF
Serviço Default           XE
Arq. Parâm. Listn.    /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Arq. Log Listener    /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/oraclehost/listener/alert/log.xml
Resumo de Atendimento...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Resumo de Serviços...
O serviço "PLSExtProc" tem 1 instância(s).
  Instância "PLSExtProc", status UNKNOWN, tem 1 handler(s) para este serviço...
O serviço "XE" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O serviço "XEXDB" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O comando foi executado com êxito

As a last resource I have changed added oraclehost to the client's /etc/hosts file:

192.168.33.1    oraclehost

So I am able to successfully ping the host machine from the cliente by IP or hostname.


texts:
I am trying to import a dump file from an 11g database into a 19c database instance.  I am running the job as SYSTEM as recommended by Oracle. The job starts but quickly devolves into failing SQL errors that all share the same:

ORA-54002: only pure functions can be specified in a virtual column expression.

I have done some research on that error and have found some indications that this was an issue or bug solved after Oracle 12. The following blog addresses the issue:
Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2
...and discusses a workaround that invloves recoding the SQL.
I am creating a DDL file from the dump to give me all the code to work with.
My questions are:

Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?
Is that even a possibility?
Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?


texts:
A table has CSV data and I want to do a SELECT DISTINCT ID on that table, how should I proceed?

+-----------+
| id        |
+-----------+
| 111,1487  |
+-----------+
| 462       |
+-----------+
| 2492      |
+-----------+
| 3184      |
+-----------+
| 3181,3184 |
+-----------+
| 3181      |
+-----------+
| 440       |
+-----------+
| 1436      |
+-----------+

I've got a function which I usually use that can split varchar csv string of INTs into a one-column table which I use as follow:

SELECT number from INTLIST_TO_TBL('123,234,345,4566')

It works well but I can't use it in this case as there are many rows and it will return multiple rows in certain cases.

Should I try using a cursor that inserts into a temporary table then proceed to the next step by querying this temporary table ?

I've been told cursors are synonym of poor performance which is why I always resort to asking here first before going down that road.


texts: I/O errors backing up to an SMB Share from MSSQL Server 2014

texts:
I'm having some issues with SQL backups which are being written to an SMB share on a AWS Storage Gateway Appliance from an EC2 SQL Server. I'm consistently getting the same error in the SQL Event log for two SQL instances saying:

  BackupIoRequest::ReportIoError: Write failure on backup device
  'Path_To_Backup' Operating System Error 59 (An unexpected network
  error occurred)

This causes several DB backups to fail at exactly the same time to the second, resulting in an invalid backup file. This happens 2 hours into the backup of this 150-200GB database at around 1AM. The weird part is I have other servers backing up to this path which do not experience the failure. I've been working with AWS Support and they've been unable to find a related network or Storage Gateway Error. I have scoured the Windows/SQL Logs but can't find any correlated events. What errors I do have seem to be dead ends for my particular error.

Is there anything I can monitor in SQL server to collect more information about the backup failure than I'm getting from the default Windows/SQL Logs? Is it a valid backup strategy to backup to a share when you're writing ~600gb a night from many SQL instances?


texts: How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle?

texts:
How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle (press F5)?

select * from employees;

select * from department;

As much I worked with PgAdmin, it allows us to execute only one selected statement or last statement per time, is there any way or any setting to execute multiple dml statement together?


texts:
I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error.
I consider to install new instance.
Questions:
1) Can I restore/recover PLSQL code from "dead" oracle database?
2) Where is the PLSQL stored?

EDIT: My situation is described here: ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"


texts: Login failed in Linking two servers - SQL Server

texts:
I want to connect 2 database which are on 2 different server.

SV1 -> SQL Authentication
SV2\SQLSERVER -> Windows Authentication

LinkedServer query

EXEC master.dbo.sp_addlinkedserver
@server = N'serverB',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'serverB',
@catalog=N'MyDatabase',
@provstr='Integrated Security=SSPI;'

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true

But I am getting a login error which executing SELECT query.
This is the error which I am getting:

Login failed for user 'sqladmin'.
OLE DB provider "SQLNCLI11" for linked server "SV2\SQLSERVER" returned message "Invalid connection string attribute".

I am new to this, please someone guide me.
PS:
Lets assume my login credentials for SV1 are:
Username : admin
Password : admin123


texts:
I am having issues with this question:

  Find the cities that have direct (non-stop) flights to both Honolulu and Newark.

The answer should be Dallas, since its the only location that has destinations to both Honolulu and Newark. I'm trying to use the AND statement but it wont work.

SELECT FLIGHTS.source_city
FROM FLIGHTS
WHERE (FLIGHTS.destination_city = 'Honolulu') and (FLIGHTS.destination_city = 'Newark')

I am using MySQL.


texts: May I Convert Non RAC db to an RAC DB and add on a Node?

texts: Currently my work env as 1 node none RAC database 11gR2, we are getting another machine (Linux), so I need to add on this node to our currently env and becoming a RAC db. What is the best way/route to do it? Advanced thanks!


texts: Can I restore a differential backup to the 2nd newest full backup?

texts:
At 12:30 pm we pulled a full backup and shipped it, and set all users in read-only mode. At 11pm that night another full backup was triggered.

Assuming no changes occurred during that time span, at least none that I care about, can I apply a differential I pull now to the [restored] 12:30pm backup I created...or will it not work/error out because another full backup occurred since it?


texts:
I have 2 servers with SQL Server 2008 R2: INT & CRM.

I need to run an SSIS package on INT, which uses data from both an INT database and a CRM database. It's painfully slow.

I tried this to test a simple query (on INT):

EXEC sp_addlinkedserver @server='CRM',
                        @srvproduct='',
                        @provider='SQLNCLI',
                        @datasrc='<IP of CRM>'

EXEC sp_addlinkedsrvlogin
                        @useself='FALSE',
                        @rmtsrvname='CRM',
                        @rmtuser='<username>',
                        @rmtpassword='<userpassword>'

I also gave <username> ddl_admin role on the CRM server, as recommended on this answer:
SQL performance issues with remote query across linked server

I then run this query:

UPDATE [CRM].DB.dbo.table1
SET field = 1
WHERE id = (SELECT id FROM [CRM].DB.dbo.table2 WHERE secondary_id = 9999)

This query takes 40 ms to run when on CRM itself. It takes 30 seconds to run in the above example. There are no results returned, it can't be network issues, so why does it take so long?


texts: Why are duplicate permissions appearing on the securables GUI in SSMS?

texts: Why are duplicate permissions appearing on the Securables GUI?


texts: bind psql client on specific network interface

texts:
I'm trying to test a postgresql database connectivity using psql command line client. There are several network interfaces on the server and several network interfaces on the client. Each network interface corresponding to a network plane. In pg_hba.conf on server, all connections are denied by default and the clients (actually other servers) who are authorized to connect are granted access using "host" records.
Those "host" records only correspond to one network plane.

When I try to connect from client using psql, I had the following error message:

psql: FATAL:  no pg_hba.conf entry for host "1.2.3.4", user "FOO", database "BAR", SSL off

As this is a test environment, I added new "host" records with the hostnames / ip addresses corresponding to the other network plane and it worked. The default route on client uses a network interface that is not the postgresql network plane and this is what I want from a system point of view.

That said, to ease testing, is possible to keep my current routes on client and only the right hosts in pg_hba.conf and bind postgresql client on a non-default network interface? I checked psql man, but found nothing.

I found a thread mentioning a "%interface_name" at the end of -h host parameter for ipv6. I tried with my hostname (resolved to an ipv4 address), but it didn't work:

psql: could not translate host name "...%eth0" to address: Name or service not known

Thank you


texts:
I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state with other vendors. It seemed like a self-join was the thing to do.

Only hints if possible, please.

The Vendors table is:

Vendors(VendorID P, VendorCity, VendorState, VendorName,...)

This is what I have:

Select VendorName, VendorCity, VendorState from Vendors AS V1 where
         VendorCity, VendorState NOT IN (Select VendorCity, VendorState FROM
          Vendors AS V2 where V2.VendorID <> V1.VendorID)

This is the error message I get:

  Msg 4145, Level 15, State 1, Line 2 An expression of non-boolean type
  specified in a context where a condition is expected, near ','.

I don't see why there is a reference to Boolean types, since this is not an EXISTS or other related query.


texts: Is it possible to recover from a LOST_EVENTS incident from an RDS read replica?

texts:
I was setting up a read replica of an RDS database for a reporting project, and issued an incorrect GRANT statement when configuring a user.

My RDS read replica stopped, with an error message on the slave:

The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

It seems that the recovery solution is to:

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

Except that the "root" user for an RDS read replica doesn't have rights to issue the stop slave command.

Is there a way to recover without deleting & recreating the read replica?


texts: Is it possible to restrict SQL Server DB_Owner when connecting using SQL Server Mgt Studio?

texts:
Here is the back story. A developer created an EXE program. The exe program has the ability to access a SQL Servre database. In order to do that we create an Active Directory group. Let's call this group AppAdmin. The EXE program use windows integrated security to connect to the SQL Server database.

In order to use this EXE program, the user must belong to the APPAdmin group. The AppAdmin group then now set the membership as DB_Owner for the database. This setup has been like this for a few years. and it is running fine. The user is only limited to what the EXE program can do.

Now here is the complication. One user one day decided to install SQL Server Management Studio Express. Let's call this guy JOE. and JOE is a member of APPAdmin group.

Now I am assigned to find away to protect the database from JOE breaking it using SQL Server Management Studio.

I am wondering if there is a way to restrict JOE when he is connecting using SQL Server Management Studio?

What are you suggestion ??

Changing this EXE program seems to be out of the question because it is big and I might break it if I start changing how the EXE program connect to the SQL Server database

Thank you


texts:
I am using MariaDB version 10.5.9.0 on Windows version 10.0.19041.928 with HeidiSQL version 11.0.0.5919
I have exported the database structure which includes the tables, stored procedures and data.  I also want to export the user I created to access the database.
In HeidiSQL I select the Tools top line menu option, then Export database as SQL, I check the database I want to export in this case called training, in the SQL export tab I check the Drop checkboxes for Database(s) and Table(s), the Data: drop down is set to Insert, Output: is Single.sql file, Filename: is supplied the location and name to export to.
From the mysql schema I check the user table, then I click the Export button.
The SQL file created contains the structure of the user table but no inserts present where as the training database contains all the inserts and the stored procedures.  Is there a reason why the user inserts are missing?
I want to use the SQL file as a complete set-up.


texts: How to know which record caused an error

texts:
I have a table named TEMP1 with 5 columns that are all NVARCHAR(100)

I want to transfer the contents of TEMP1 to a table named FINAL, which has the same columns, but this time with targeted data types (DECIMAL, BIT, INT, etc.).

Right now, I do the following:

insert into FINAL
select * from TEMP1

Due to the implicit casting from nvarchar to another type, the statement may fail. If it fails, is there a way to know which line(s) caused the error? I was thinking about doing a WHILE loop to identify the lines in error. Is there a better/more efficient way?


texts: Is it possible for increasing the SQL Server max memory limit to degrade performance?

texts:
As far as I can tell, raising the SQL Server max memory limit has no drawbacks, provided that sufficient memory is left for the OS and other applications.

Assuming this is the case, can there be any detrimental effects to raising the max memory limit?

The only thing I have seen is this KB article from Microsoft, which states,

  When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

but it only applies to old versions of SQL Server (<=2008).


texts:
I have a correlated sub query, which is taking 16 Minutes to execute.  How can I convert it to join or optimize it.

SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM  Stock AS SSF
    WHERE SSF.SM_StockCode = (
        SELECT SM_StockCode
        FROM  Stock AS SSFAI
        WHERE SSFAI.ST_StockCode = SSF.ST_StockCode
        ORDER BY SSFAI.ST_ItemSize ASC
        LIMIT 1
    ) GROUP BY SSF.SM_StockCode

UPDATE : Added Explain Plan

+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+
| id | select_type        | table | type  | possible_keys     | key              | key_len | ref  | rows  | Extra                                        |
+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY            | SSF   | ALL   | NULL              | NULL             | NULL    | NULL | 45180 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | SSFAI | index | ST_StockCode_Indx | ST_ItemSize_Indx | 9       | NULL |     1 | Using where                                  |
+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+

UPDATE : Table Structure

CREATE TABLE `Stock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ST_StockCode` varchar(20) NOT NULL,
 `SM_StockCode` varchar(20) NOT NULL,
 `ST_ItemSize` decimal(18,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ST_StockCode_Indx` (`ST_StockCode`),
 KEY `SM_StockCode_Indx` (`SM_StockCode`),
 KEY `ST_ItemSize_Indx` (`ST_ItemSize`)
) ENGINE=MyISAM;


texts: Firebird execute block where External datasource is localhost

texts:
I'm writing a .net application that connect to a remote database server hosted on internet to make a sort of replication.

The database source is on my local firebird server, the destination database is on the Firebird server hosted on the internet

This is the query

execute block as DECLARE panum INT; DECLARE nom varchar(50); DECLARE prenom
varchar(50); DECLARE dna date; DECLARE datemodif date; DECLARE VARIABLE VSQL
VARCHAR(500); begin VSQL = 'select panum, nom, prenom, dna, DATEMODIF from
patient where (datemodif is not null) and (datediff (day from current_date
to cast(DATEMODIF as date)) >= 0) ';

FOR execute statement VSQL ON EXTERNAL DATA SOURCE
'127.0.0.1:C:\BDD\D2018.gdb'
AS USER 'sysdba' PASSWORD 'masterkey' INTO :panum, :nom, :prenom, :dna,
:DATEMODIF DO

merge into patient bb using (select :panum panum, :nom nom, :prenom prenom,
:dna dna, :DATEMODIF datemodif  from RDB$DATABASE ) n on bb.panum = n.panum
when matched
then update set bb.nom = :nom, bb.prenom = :prenom, bb.dna = :DNA,
bb.DATEMODIF = :DATEMODIF when not matched
then insert(panum, nom, prenom, dna, datemodif) values(:panum, :nom,
:prenom, :dna, :datemodif);

suspend;  end;

Both servers run Firebird 3.0.3 on Windows 64 machines.

It fails with this error message :

  Error while trying to open file 12515659 : The system cannot find the
  path specified.  Data source : Firebird::127.0.0.1:C:\BDD\D2018.gdb

I have also tried localhost and my local ip adress as well as server name .

If I flip the direction so my source database is the remote server and the destination is my local server it works.

I know that's a little weird, but I have some constraints for doing it like this.


texts: Setting up replication for MyISAM tables?

texts:
Before setting up a replication within MySQL, the data first needs to be transferred to the slave.

Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, to the slave? Would this be enough to start the replication?


texts:
We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to Zabbix.

Among the retrieved info, I want to get current connections, but I've only managed to do so with the root user. I've created a zabbix user with SELECT and PROCESS permissions* and tried mysqladmin -u zabbix processlist, but I get Access denied; you need the PROCESS privilege for this operation.

show grants for 'zabbix'@'%';
+----------------------------------------------+
| Grants for zabbix@%                          |
+----------------------------------------------+
| GRANT SELECT, PROCESS ON *.* TO 'zabbix'@'%' |
+----------------------------------------------+
SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

How can I get zabbix user to retrieve processlist?

* Yes, I've flushed permissions


texts:
I Have a table called IP_Block in which I created two columns

ID of type Bit Varying(32) as PK
IP Bloc of type CIDR

the construction of the ID is the following :
For example for the block 1.0.0.0/8 I take the numbers of the IP address and transform them into binary string and use the mask to truncate the output of the first transformation and store it in the column ID .

1.0.0.0/8 --> 00000001
1.0.0.0/16 -->0000000100000000

With this construction I am guaranteed that each block has a different and unique ID and its size differs depending on the IP address.
My question is when I query the IP block that has an ID=00000001 how does the postgres engine know exactly how many bits to read in the ID column in order to extract the appropriate data. Is it encoded somewhere in the system to say that you need to extract this ID that has the following length (that is calculated when I pass it in the query)  ?
In other terms how the bit Varying type impacts a query ?


texts:
I've created few different instances, one instance is of Amazon Aurora PostgreSQL and other instances are of PostgreSQL, MySQL etc. How do I find our the Postgres instance is running within an AWS Aurora cluster or not, from CLI? When I say from CLI this means terminal not AWS CLI.

Thanks.


texts: Oracle live data updates for frequently updated table: poll or CQN?

texts:
My team is looking at getting live updates from our Oracle database from a frequently updated table. I have discovered CQN and configured a test environment to use UTL_HTTP to POST to a webhook when the table changes. However, in the 'Best Practices' section of the documentation for CQN:

Register few queries—preferably those that reference objects that rarely change.

And in other articles for instance:

if you know the table is subject to a lot of change, then your apps will be better off simply polling the table and avoiding any CQN overhead. Note that CQN was designed to be used for relatively infrequently updated tables.

CQN had seemed like a great solution until I read these disclaimers. The table we want to get live updates from has about 1000 inserts per second during busy time bursts.
The alternative to CQN appears to be polling to query this table every second. Is polling like that the best solution for getting live updates from a table, or is there something I am missing? I am working with Oracle 11g.
Thank you.


texts:
I have this index schema: CREATE index comments_idx ON comments (books)

How can I retrieve column name ("books") through SQL query?


texts: Combining multiple rows into a single row with multiple columns

texts:
I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id.

This is the structure of table:

--------------------------------------------------------
meta_id | post_id | meta_key                | meta_value
1       | 100     | _wp_attachment_metadata | a:6:{s:5:"width"}
2       | 100     | _thumbnail_id           | 23
`

This is the result I am trying to achieve:

--------------------------------------------------------
post_id | _wp_attachment_metadata | _thumbnail_id
100     | a:6:{s:5:"width"}       | 23


texts: Enable user access to all DBs and tables in MS SQL server managment

texts:
I create a new user in MS SQL via MS SQL server managment in / SECURITY / LOGINS part.

User is set and can connect , however dispite the fact he see all databases he can get data just from one of them and from rest he can get just structure.

Is there some special settings to enable full access to specifc user to all DBs?
Thanks


texts: Is there a rule of thumb for including the value of a foreign key in a table to avoid large joins?

texts:
Example:

We have a table with 500 rows in table "Cars". There is also a table "Colors" which has roughly 20 rows. Table "Cars" has a foreign key reference to table "Colors". To get the color of the car we would need to inner join on table "Colors":

select car.Name,
color.Name as Color
from dbo.Cars as car
inner join dbo.Colors as color on color.ID = car.ColorID

The result would look something like this:

+-------------+--------+
|    Name     | Color  |
+-------------+--------+
| Rolls Royce | Red    |
+-------------+--------+

But lets say you have 10,000,000 rows in Cars and 2,000,000 rows in Colors; the inner join would cause quite a performance hit. It would probably be better to include the column "Color" and "ColorID" in table "Cars" to avoid this inner join. So you query would look like this:

select Name,
Color
from dbo.Cars

Is there a rule of thumb to rather include the foreign key value inside your table once your table reaches an x-amount of rows? Or would it be better to remove the foreign key completely and just have a column called "Color" in table "Cars" thus causing table "Colors" to be redundant?


texts: Connecting to SQL Server 2017 from Oracle SQL Developer

texts:
Pretty much as the title says, has anyone tried connecting to SQL Server 2017 via Oracle SQL Developer?

I downloaded the latest jTDS (jtds-1.3.1-dist) but this only seems to support SQL Server 2012 (not too surprising as the latest jTDS was released in 2013).

There doesn't seem to have been a release since then, so when I try to connect to SQL Server I just get a 'Driver version not supported' message.


texts: postgresql data warehouse structure - hierarchy (coming from sql server)

texts:
I'm building a data warehouse in postgresql and I'm struggling with choosing a "right" structure for my datasets. Coming from a similar product in SQL Server, things are a lot more complicated, as cross-database queries are not supported (I know there's an extension for that, but I'm trying to avoid it).
Initially I thought of storing each major dataset in a database and have separate schemas for each lifecycle stage (ingest, process, store, publish etc.). There will be a major dataset containing reference data (very complex + geographical data) which MUST be available from all other datasets. Without cross-db support though, this won't work.
I then thought to have all the data in one database and use schemas to define the datasets (reference, demographics etc) but then I lose the lifecycle functionality.
What's a good way to replicate something like this in postgresql and be able to execute queries across all elements?
db: Reference
    - schema: ingest
        - table: dataset
        - table: another dataset
    - schema: process
        - table: processed dataset
        - table: another processed dataset
    - schema: store
        - table: stored dataset
        - table: another stored dataset

db: Economics
    - schema: ingest
        - table: gpd
        - table: inflation
    - schema: process
        - table: processed gdp
        - table: processed inflation
    - schema: store
        - table: stored gdp
        - table: stored inflation

Basically the data on economics should be able to access reference data for analysis purposes. This is the current setup in SQL Server and it works a charm. How can this be ported to postgresql in a sensible way?
Thank you


texts:
I wrote a stored procedure in sql to take a number from table.
it has lots on records which has only column.
whenever a call it , is should take the first record and return it after that delete the record.
it works properly when we have just one thread,the problem appears when I use multithreading in my program and the procedure return the duplicate number.
the procedure is like below:

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      SET @subscribeNo = -1;

      BEGIN TRAN T1;

      UPDATE UNIT_SBS_NOS
      SET    Free_SBS_NO = Free_SBS_NO
      WHERE  Sbs_Trf_Type = @Sbs_Type;

      SELECT @subscribeNo = (SELECT TOP(1) FREE_SBS_NO
                             FROM   UNIT_SBS_NOS WITH(nolock)
                             WHERE  Sbs_Trf_Type = @Sbs_Type);

      DELETE FROM UNIT_SBS_NOS
      WHERE  FREE_SBS_NO = @subscribeNo;

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END


texts: Interpreting Page Life Expectancy metric

texts:
I need advise on interpreting PLE metric of my server which:

is SQL Server 2008 R2
with 4 NUMA nodes
with 128 GB RAM

Daily full backups start at 02:00 AM and takes around 1 hour to complete.

Various metrics for the past 24 hours is as follows:

My questions would be:

What should alert threshold for PLE be?
What might the dips and peaks on the graph indicate?

Thank you


texts: Need best way to to avoid Replication Re-Configuration

texts:
I need the best way for the scenario because of which I got irritated.
I do have 3 Production database Servers Srv1, Srv2 and Srv3.
I configure transactional replication among these servers.
Srv1 is Publisher and Srv2 and Srv3 are subscribers.

On production server everything running fine without any problem.

I have QA environment same as production replication configured.
On request of QA team, I need to refresh QA with production databases weekly (almost), which cause to fail replication in QA environment.

Because I have 1000 tables dropping and re-creating publications takes too much time on Srv1.

If I refresh database without dropping publication and recreate publication using backup script I get The process could not execute ‘sp_repldone/sp_replcounters’

Thanks