texts: Ola Hallengrens - Output File cleanup does not delete files

texts:
We run Ola Hallengrens backup,dbcc & index jobs but we have not scheduled Output File Cleanup for some reason. Noticed this today and when we tried to run it on a server that had 2 or more instances it doesn't delete old logs.
We have for example set up 1 server with 2 instances and it's set up with AvailabilityGroups.
When running the script this is the output: (I've deleted a few rows so it didn't take up the whole page but it deleted a few files)

  Date      2017-07-04 10:57:55 Log     Job History (Output File Cleanup)

  Step ID       1 Server        SERVER1183\INST01 Job Name      Output File Cleanup
  Step Name     Output File Cleanup Duration        00:01:54 Sql Severity   0 Sql
  Message ID    0 Operator Emailed   Operator Net sent   Operator Paged
  Retries Attempted 0

  Message Executed as user: VT\sqladmin-internaltes.
  ...8C0E25584E08C4CB393567C2C66083E_1_20170301_132833.txt"  del
  "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x18C0E25584E08C4CB393567C2C66083E_1_20170304_200000.txt"
  del
  "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x6DFC82E95...
  Process Exit Code 0.  The step succeeded.

After checking in the Logs folder which is located: E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log we still have 12500 files.

The jobs are set up as standard when adding Olas jobs, with output file config:

$(ESCAPE_SQUOTE(SQLLOGDIR))\IndexOptimize_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

We have not changed the job command at all which is:

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P
"$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if
EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del
"$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del
"$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"

The script works fine when it's a single instance or default instance.
It's sql 2014 if that's relevant but we have this problem on 2008 & 2012 aswell.

Anyone got this figured out or know how to fix this?


texts:
Not sure what sort of platform AWS runs 9.x Postgres on. What's the maximum value for the process pid?

Im looking into an issue around the use of pg_backend_pid in a trigger.


texts: General Database Optimizations - correct data types - why to avoid NULL?

texts:
Oracle recommends:

Choose the correct data types and size to:
– Avoid NULLs
– Improve performance
– Protect data
– Use data compression where appropriate

I don't understand why I need to avoid NULL. Please explain.


texts: How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

texts:
We have a table X in postgres v11, where it has 5 columns like A B C D E.
We all columns are indexed and there are all integer values.
We have around, 100 milion records, but soon will turn into 800 million records.
Now we usually make queries like:

select E
from Table X
where A=.. and B=..

depending on different data patterns, sometimes this query results in 0 or 1 record response and sometimes even more than 50,000 results.

When combination of A and B is frequent, queries take too much time to reply us.

Of course we can use Count(*) or Count() or Count (1) to find number of results without the need to fetch all 50,000 possible answers.

But usually such all queries take between 500ms to even 28000ms...

What we need to achieve is to be able to find a simple HIT answer to know number of possible results for above query in less than 5ms. we make queries to this table like 200 times per second for our application, so we need to make sure if such queries has any result or not..and if yes, what is the number of results.

Usually in Big data database performances, there is a keyword like HIT which means how many possible answers are available for a query.

We need exactly similar thing in postgres and we dont know if any function, extension or module exists that we can install to achieve hit results in a fraction of mili seconds. I am sure there must be a solution for that, but still we are looking for it. I mention again that we only need to know if query has any result and number of results, we don't need to fetch the results.


texts: Second instance of query designer for a single table in SSMS (Express)?

texts:
Forgive me if this is the wrong exchange site for this question. It seems like the right one.

I have been a fan of writing queries manually for a long time, but recently I've got into the habit of using the query designer in Management Studio for one reason - it allows editing of data in-place.  (There's probably a way of doing this in the manual query window but I haven't found out yet)

Anyway,  today I wanted to create a new instance of it for a table, and preserve the one I already had open.  It wouldn't let me.  When I clicked 'open table' on the object explorer it just took me to the window I already had open.  This meant that if I want to query the data in a different way I have to abandon the query I had set up.

Is there a way of getting a second (or more) query designer window for the same table?

It occurs to me that I can just open a second instance of the entire app (SSMS) but that seems overkill for what must be a simple/common need.


texts:
I am installing a RHEL server with nginx and php that can connect to a db2 database, download and install the "IBM Data Server Client" package, also install the pecl library through:

  pecl install ibm_db2

however when I do a connectivity test me responds with the following error:

  [IBM] [CLI Driver] SQL10007N Message "0" could not be retrieved.
  Reason code: "3". SQLCODE = -1390

the code that I'm testing is:

<?php
$database = 'mydb';
$user = 'usr';
$password = 'pass';
$hostname = 'ipaddr';
$port = 9501;

$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" . "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
$conn = db2_connect($conn_string, '', '');

if ($conn) {
    echo "Connection succeeded.";
    db2_close($conn);
}
else {
    echo db2_conn_error();
    echo db2_conn_errormsg();
}

am I missing something?

Edit:

searching in the web I found in phpe-ditors this:

  If you created a DB2 instance named db2inst1 in /home/db2inst1/, for
  example, you can add the following line to php.ini:

  ibm_db2.instance_name=db2inst1

  If you do not set this option in php.ini, you must issue the following
  command to modify your environment variables to enable access to DB2:

  bash$ source /home/db2inst1/sqllib/db2profile

  To enable your PHP-enabled Web server to access these functions, you
  must either set the ibm_db2.instance_name configuration option in
  php.ini, or source the DB2 instance environment in your Web server
  start script (typically /etc/init.d/httpd or /etc/init.d/apache).

The problem is that I don't know whats the value for ibm_db2.instance_name

Any idea ?


texts: What happens if sys.aud$ is full in Oracle?

texts:
I am not seeing this in the documentation, but what happens when the Oracle 10.2 audit trail is full? Does it just not making any more audit records? Overwrites the oldest records? Shuts down the database? And is there any way I can change that?

Yes, I know 10.2 is not supported, but I don't have control over that. And yes there are procedures in place that should never need me to find out first-hand. But in cases of emergency failure of the auditing operations, what is the default behavior if the audit trail becomes full?


texts: How to block external connections for an Oracle database?

texts: I have an oracle database (11g-r2) on my (windows) computer for development, and I am trying to forbid any outside connections. Is there a way to do this?


texts: Avoiding "Row by row" fetch method when dealing with source LOB columns

texts:
I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying:

  'Row by Row' fetch method is enforced because the table has LOB column(s). Column content is LOB

The thing is, none of the column really need to be LOBs. There's a few that are TEXT types, but could fit easily within a varchar(max). Even stranger, though, most already are varchars, but it seems anything over varchar(128) is being treated as if it was a LOB (in advance properties, the data type is DT_NTEXT).

I event tried doing a manual SQL command where I explicitly casted every string type to a varchar of an appropriate length in the select statement, and they're still being set as DT_NTEXT in the ODBC source.

I'm not a DBA, so it's entirely possible I'm doing something really stupid. I would just like to know the best way to ensure that the types end up as varchars so I can batch fetch. Any ideas?

In case it matters, I'm using SSIS-BI 2014 inside Visual Studio 2013.


texts: "ERROR: timestamp out of range" converting epoch stored as bigint to a timestamp

texts:
I'm getting ERROR:  timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table):

select to_timestamp(1527012834506374);
ERROR:  timestamp out of range: "1.52701e+15"

Other approaches to conversion don't work either:

select 1527012834506374::abstime::timestamp;
ERROR:  cannot cast type bigint to abstime

select 1527012834506374::integer::abstime::timestamp;
ERROR:  integer out of range

It's a valid epoch; https://www.epochconverter.com/ tells me 1527012834506374 is equivalent to 2018-05-22 06:13:54.506 UTC

How can I do the conversion using SQL in Postgres?


texts: SQL Server query takes too long to execute. What could the problem be?

texts:
There are two tables

Table 1 :

Year     char(4)
MonthKey char(2)
ComplaintCount int

Table 2 :

FileCode  varchar(50)
StartDate varchar(10)
EndDate   varchar(10)

I wrote a query and the query takes a long long time to execute. Of course there is no result and its still executing and i think it is in a loop maybe, which is not expected!

The query is:

SELECT dbo.GetLastDayOfMonth(c.Year,c.Monthkey) AS DateKey,
       ComplaintCount,
       Count(FileCode)
FROM Table1 C
INNER JOIN Table2 F
    ON dbo.GetLastDayOfMonth(c.Year,c.Monthkey) = dbo.GetLastDayOfMonth(LEFT(f.FileFormationDate,4),SUBSTRING(f.FileFormationDate,6,2))
GROUP BY dbo.GetLastDayOfMonth(c.Year,c.Monthkey), c.ComplaintCount

The function GetLastDayOfMonth() returns the last day of month according to the Persian Calendar. There are 3,000,000 records in Table 1 and 3 records in Table 2. Even running the JOIN by itself (without the GROUP BY) is still executing and until now i can see 1,000,000 records in the Results pane.

What could the problem be and how can I fix it?

This is the code for the function:

CREATE FUNCTION [dbo].[GetLastDayOfMonth] (
    @year CHAR(4)
    ,@Month CHAR(2)
    )
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @result VARCHAR(10)

    SET @result = (
            SELECT max(datekey)
            FROM StageArea.Common.DimTime
            WHERE [Year] = @year
                AND MonthKey = @Month
                AND LastDayOfMonthKey = 1
            )

    RETURN @result
END


texts: Setting the SMTP_OUT_PARAMETER in 10G at runtime

texts: I am writing a procedure that will send mail from oracle 10g. I have installed the UTL_MAIL and UTL_SMTP packages and configured the SMTP_OUT_SERVER parameter. However the requirement is that the SMTP server address be chosen at runtime. How do I resolve the issue.??


texts:
I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him.

I want to insure that the host you assign to the domain belongs to the user that domain is assigned to.

Is it something that I have to insure in my application logic?

If I set domains.user_id foreign key to reference host.user_id and user_id.id does it eliminate my issue?

By the way can anyone suggest me a better design for my schema?


texts: Help with a logic inside WHERE or ORDER BY

texts:
I Have this query :

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))

And the result is this one:

| Faturamento | TotalFatura | Sequencial | CdCodigoBarras | Linha | ValorRecebido |
|-------------|-------------|------------|----------------|-------|---------------|
| 2458874     | 320,90      | 5783       | 141            | 18393 | 320,90        |
| 2458874     | 320,90      | 5783       | 141            | 18394 | 320,90        |
| 3283899     | 840,50      | 5783       | 141            | 27140 | 840,50        |
| 3283899     | 840,50      | 4397       | 142            | 4010  | 840,50        |
| 3901364     | 89,44       | 21442      | 139            | 23595 | 89,44         |

Long story short, if you notice, there are some equal "faturamento".

What I need is a distinct of:

1) If Faturamento is the same, we need the biggest sequencial. Ok with this, I can order by 3.

2) If faturamento and sequencial is the same, I need the biggest cdCodigoBarras. Ok, again, I can do it with Order by 3,4.

3) If everything is the same, but valorRecebidois the same, I need to get the biggest Linha. but if ValorRecebido is different ( and faturamento, sequencia, cdcodigobarras is the same ), I need the biggest ValorRecebido.

Is this something that devs should do in the source code? I really can't think how I can do it with a Query.

I've spend some time tryint to do something with count, but even thinking I'm in the right way, I can past this when vlaorRecebidois the same, get biggest Linha, or if it's different, the biggest vlaorRecebido.


texts: Oracle - PFILE or SPFILE when instance is down

texts:
This may be a stupid question, but I can't find any information out there on this.
Is there a way to tell if the database uses a pfile or spfile when the instance is down?
For example if an Oracle database went down that I was not familiar with and had no documentation on, and I was tasked with getting it back up and running. How would I know if it used a PFILe or SPFILE? Startup vs Startup pfile=
Thanks in advance


texts: How to join by most recent preceding date?

texts:
Imagine I have a database with historic price data and a second table with dates

Item | Price | Date               Customer | Item | date
-------------------------         ---------------------------
   A | 2.49$ | 2014-09-01                1 |    A | 2014-08-27
   B | 1.29$ | 2014-09-01                2 |    A | 2014-09-02
   A | 2.99$ | 2014-08-25
   B | 1.39$ | 2014-08-26

A new row was entered into the price history table every time the price changed.

How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?

SQLFiddle


texts: Import Oracle .dmp with error 1659, unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM

texts:
I'm trying to importing an Oracle DUMP file (v10.2) into Oracle Express 11.2.0.2.0. I was getting some schema errors and resoled them by following other posts here. Now I'm getting this error and I'm not able to understand it enough to troubleshoot it:

C:\>imp system/pass FROMUSER=DWH TOUSER=IMPORT_MAN file=e:\LRT_ARCHIVE_170911.dmp

Import: Release 11.2.0.2.0 - Production on Wed Sep 20 11:01:48 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by DWH, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing DWH's objects into IMPORT_MAN
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "LRT_ARCHIVE_170911" ("EVENTTIME" TIMESTAMP (6) WITH LOCAL TIM"
 "E ZONE, "TTR_ID" NUMBER(10, 0), "STATUS" VARCHAR2(3), "ISEMPTY" VARCHAR2(1)"
 ", "TDP_IDCURR" NUMBER(6, 0), "TNP_IDLASTPOS" NUMBER(6, 0), "STORINGREASON" "
 "VARCHAR2(20), "ISPROCESSED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 "
 "MAXTRANS 255 STORAGE(INITIAL 1824522240 FREELISTS 1 FREELIST GROUPS 1 BUFFE"
 "R_POOL DEFAULT)                   LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM
Import terminated successfully with warnings.

Would really appreciate if someone can point out why this is happening and how to resolve it.

Also, I was running the command with the full=yes command-arg but that was not running at all and reported that I cannot use full with FROMUSER

Background:
This is a freshly installed instance of Oracle Express. I got the dump and am tasked to restore it. When I tried originally, I followed the warning about a schema/user that actually created/exported the dump so I created a new user with same name DWH. I think that was a non-issue. Apart from that, I have not done much in the two hours I have spent on this.


texts:
I have a postgres database linked to SQL Server.  I have found you can use OpenQuery to query postgres from the Sql Server side, but one caveat I have found is that when creating views all fields are converted to char despite the actual data type from postgres.  For example, if you look at my query below, userid is an int, and userlogindate & userlastlogindate are date fields in postgres but when the view is created in Sql Server if I expand the plus sign beside the view, and expand the plus sign beside columns, all of the columns are listed as char()?

What do I need to do in order for the Sql Server view to keep the same field type as what postgres has them set-up to be?  Cast/Convert each one?

SELECT      *
FROM            OPENQUERY(LinkedServer,
                     'Select
                      userid As "userid"
                      ,userlogin As "userlogin"
                      ,userlogindate As "userlogindate"
                      ,userlastlogindate As "Lastlogindate"
                      From syscatalog')


texts:
I have an database with the name "build", and when I try to execute "USE BUILD" the MySQL server just crashes, it also crashes when I try to show the tables with phpMyAdmin and MySQL Workbench. I do know that some of the tables inside that database is corrupt, but not all of the tables are corrupt.

An external software is able to access most of the tables there without crashing the MySQL server, but if it tries to access one of the corrupted tables it crashes instantly.

Some say I should use "REPAIR TABLE corrupt_table", but to execute that command I need to access the database before executing it which is impossible in my case because it crashes the MySQL server.

I just want the corrupted tables away, they are bothering me. It does not matter if I lose the data which is inside the corrupted tables, but I cannot lose the whole database. Is there a way to repair or, perferably force delete them?


texts: copy a sqlite3 table preserving custom _id

texts:
I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)

I'd like to copy the table preserving the labelling 1,2,5,7, as opposed to 1,2,3,4 that is given by AUTOINCREMENT below.

What is the correct way to do it?

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (key_remote_jid)
    SELECT key_remote_jid
    FROM android.messages;

I can achieve that by

CREATE TABLE messages (_id INTEGER PRIMARY KEY, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;

but then such table is not included in sqlite_sequence.


texts:
I have couple questions about auto stats in SQL Server:

I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific index's auto stats is disabled?

SELECT DATABASEPROPERTYEX('Databasename','IsAutoUpdateStatistics')

If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues in SQL Server?

In the following link there is a statement that you can disable auto stats on a database level https://msdn.microsoft.com/en-us/library/ms188775.aspx


texts: Parallel LOAD DATA to different memory tables very slow compared to single one

texts:
I'm using a fairly simple bash script to execute mysql load data infile to 11 different MEMORY engine tables.

Example:

load.sh
echo "start $@ $(date)"
mysql -p"mypass" -e "use db; set bulk_insert_buffer_size = 1024 * 1024 * 256;
   alter table testLoadData$@ disable keys;
   LOAD DATA INFILE '/var/db/mysql_secure/clearedhalf$@.log' INTO TABLE testLoadData$@ FIELDS TERMINATED BY '\t' lines terminated by '\n';
   alter table testLoadData$@ enable keys;"

echo "end $@ $(date)"

Now, multi.sh:

for i in {0..10}
do
    /usr/local/bin/bash /root/loadinfile/load.sh $i &
done
wait

On a single table this runs about 12 seconds for a 200Mb file with around 1 million lines in it.

However when I run 10 of them in parallel it looks like they are locking at some point, mysql only uses one core and HDD is used about 6Mb/s (we do have raid10 ssd on this box), and it takes about 2 minutes for some reason.

My question is - do I need to change/tweak some settings to allow mysql use all of my resources and perform all of these loads simultaneously?


texts: How to use SET operation in CASE clause in SQL?

texts:
I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error:

  Incorrect syntax near SET

Query:

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT;

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond =
        CASE
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird =
        CASE
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

It would be better if there is another way to store the result of the expression into @TrendValue so that there is no need to calculate that expression each time.

It would also be good to avoid division by zero errors (that's the reason I added the various R.TtlNOfCmpSale7to12 != 0 checks).


texts: How to Create key (to be used in join) using concatenation?

texts:
I want to inner join tables in SQL Server 2008, however both tables have been set without Primary Keys. How to create a key using concatenation of existing columns?

I tried the below but it only results in one record & not the entire table.

declare @lookup as varchar(255)
Select  @lookup = accountID + state + product
From Table1
Print @lookup


texts:
Attempting to set up a linked server to a Oracle 11g database via MSSQL 2016.

Unfortunately I don't seem to have any data providers to choose from in SSMS. Initially thought it was an issue with configuring the oracle client, however from my research I believe there should be some providers here by default.

I can see and set up the oracle data provider in the windows ODBC 64 bit Data Sources configuration window - so believe this is configured correctly.

SQL Server Version is:
"Microsoft SQL Server 2016 (SP2-CU2-GDR) (KB4458621) - 13.0.5201.2 (X64)   Aug 18 2018 07:38:15   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3  (Build 9600: ) (Hypervisor)"

Output of EXEC sp_MSset_oledb_prop returns no result rows.


texts: ORA 01722: Invalid Number caused by different comparison

texts:
The following causes the above error:

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING > 1;

Here's the weird thing, the following query DOES work, the only difference being whether rating needs to be larger than or smaller than 1.

Suffice it to say that I'm utterly perplexed. Is this a bug? I'm on 11.2.0.4.0.

Could it be something to do with JARO_WINKLER?

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING < 1;

I partially solved it: PHONE is actually a VARCHAR2 column. However, this doesn't at all explain why comparing in one direction versus another works or doesn't work, when I make that mistake. So I'm still very curious.


texts: Adding a Group ID to a query where the sort by is not based on the Column your grouping by

texts:
I am trying to add a groupID column to a query where the column the grouping is based on is sorted randomly

DECLARE @t TABLE
(
  ID INT primary key IDENTITY(1,1),
  ProductID INT,
  QuestionID INT
)

INSERT INTO @t (ProductID,QuestionID)
SELECT  ProductID
        ,QuestionID
FROM    dbo.Products p WITH(NOLOCK)
JOIN    dbo.Questions q  WITH(NOLOCK) on p.StudyID = q.StudyID
WHERE   p.StudyID = @StudyID
ORDER BY FIRST_VALUE(CRYPT_GEN_RANDOM(4)) OVER (PARTITION BY ProductID ORDER BY ProductID),
        ProductID,
        QuestionNumber

SELECT ID,ProductID,QuestionID
       , DENSE_RANK() OVER(ORDER BY ProductID) as ProductGroupID
FROM   @t t
ORDER BY ID

Every time the query is run there is a random sort off the ProductID Column.
I trying to get the GroupID to be 1,2,3 etc...  no matter what ProductID is first.

This is my goal
Example:

     ID  | ProductID   | GroupID   |  QuestionID
    -----|-------------|-----------|-------------
     1       22             1              2
     2       22             1              3
     3       22             1              5
     4       11             2              2
     5       11             2              3
     6       11             2              5
     7       44             3              2
     8       44             3              3
     9       44             3              5

If you re-query it should be:

     ID  | ProductID   | GroupID   |  QuestionID
     ----|-------------|-----------|-------------
     1       44             1              2
     2       44             1              3
     3       44             1              5
     4       11             2              2
     5       11             2              3
     6       11             2              5
     7       22             3              2
     8       22             3              3
     9       22             3              5

and again:

     ID  | ProductID   | GroupID   |  QuestionID
    -----|-------------|-----------|-------------
     1       11             1              2
     2       11             1              3
     3       11             1              5
     4       44             2              2
     5       44             2              3
     6       44             2              5
     7       22             3              2
     8       22             3              3
     9       22             3              5


texts:
I have Visual Studio 2010 SP1 with SSDT (with Dec 2012 update). I have created a new SQL Server project and imported objects from a database on a SQL Server 2008 R2 instance. The database has a compatibility mode of 80, can't change this at the time.

Anyway. When I do a build, which has to be clean in order for me to do a schema compare, it is throwing SQL70590 errors on the variables although SQL Server itself has no problem with them. Looking at them it seems it is because of the case sensitivity of the variable (e.g. declared as @VARIABLE but referenced as @Variable).

The database collation is set to Latin1_General_BIN, is this why the error is being thrown? I can't see why it sees it as an error yet the procedure code runs without error on the database.


texts: Why can't SQL Server find a seek plan? Is this a bug?

texts:
create table Test1 (Id int not null, H char, primary key (Id), index i1 unique (H))
create table Test2 (Id int not null, H char, primary key (Id), index i2 unique (H))
insert into Test1 values (1, 'A'), (2, 'B')
insert into Test2 values (1, 'A'), (2, 'C')

this query fails Query processor could not produce a query plan because of the hints defined in this query. (if I remove the forceseek hints then it runs but it will scan one of the two tables - even if it's extremely large)

select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where a.H = 'A' or b.H = 'C'

this equivalent query runs fine:

select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where a.H = 'A'
union
select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where b.H = 'C'

and gives plan

..I don't understand why SQL Server doesn't run the first query optimally. Is this a known issue? Does it have a name?


texts:
I would like to display the list of students who have passed at least 3 courses at ENSA with more than 60.

db.etudiants.insert({
    nom: 'emile',
    prenom: 'josé',
    cours_reussis: [{
        nom_cours: 'SO2',
        periodes: NumberInt(60),
        note: 70,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: 'ENSA'
    }, {
        nom_cours: 'IBD',
        periodes: NumberInt(60),
        note: 80,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: 'ENSA'
    }, {
        nom_cours: 'GEB',
        periodes: NumberInt(60),
        note: 65,
        date_reussite: NumberInt(2016),
        dispense: true,
        nom_ecole: 'ENSA'
    }]
})

db.etudiants.insert({
    nom: "larhemouchi",
    prenom: "zouhair",
    cours_reussis: [
      {
        nom_cours: "PAI",
        periodes: NumberInt(60),
        note: 68,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: "ENSA"
      },
      {
        nom_cours: "GEB",
        periodes: NumberInt(60),
        note: 78,
        date_reussite: NumberInt(2014),
        dispense: true,
        nom_ecole: "ENSA"
      }
    ]
})

In truth I tried and I am a bit ready to answer but I do not know how to have the students who have successfully 3 subjects.

here is my answer

  db.etudiants.aggregate([
  {
    $project: {
        _id: 1,
        'nom': 1,
        'prenom': 1,
        'cours_reussis': 1
    }
  },
  {
    $unwind: '$cours_reussis'
  },
  {
    $match: {
        'cours_reussis.note': {
            $gt: 60
        },
        'cours_reussis.nom_ecole': "ENSA"
    }
  },
  {
    $group: {
        '_id': {
            'nom': '$nom',
            'prenom': '$prenom',
            'cours_reussis.note': '$cours_reussis.note',
            'cours_reussis.nom_ecole': '$cours_reussis.nom_ecole'
        },
    }
  }
])

I would just like the students who have successfully taken 3 courses. In our database, the student who has successfully completed 3 courses is "emile josé".


texts:
There are tons of articles out there recommending RAID/ LUN/ drive configurations for SQL Servers, but most leave me still questioning as they usually state "it depends" and for this reason I'd like to provide some specifics and get some more direct recommendations:
I have a client who already installed SQL Server on a Cybernetics SAN and here are the specifics:

•   SQL Server 2008 R2 installed on a virtual server
•   Storage:
 o       Cybernetics miSAN-D8/T16 SAN iSCSI storage appliance
 o       4+1 GbE ports
 o       8 GB SAN controller cache
 o       USB and eSATA ports
 o       Two miniSAS device
 o       8 drive bays all filled with 600 GB SATA-II 7.2K drives (5.4 TB total)
 o       All 8 drives set up in RAID 6
 o       Three logical drives:
       1. C (156 GB) – for system and apps (IIS, web app, SQL, SSIS, CA ArcServe backup app, VIPRE antivirus)
       2. E (1.64 TB) – for data, logs and tempdb
       3. F (1.80 TB) – for backups

The SQL Server is more read intensive, with data bulk loaded nightly like a data warehouse and the web app used for looking up and reporting on the data.
Periodically the client experiences sluggishness, sometimes severely, for short periods with the web application.  I’ve also noticed Brent Ozar’s Blitz script reveals slow writes to the E drive at times.
I am wanting to suggest the client reconfigure their SAN as follows:

C (  156 GB, raid 1) – for system and app files.
D ( 1200 GB, raid 10/5) – for sql data files.
L (  444 GB, raid 1) – for log files.
T ( 1200 GB, raid 5) – for tempdb data files.
Backup to a different san or network location and archive to tape.

But at the very least I believe the client should do the following:

Move backups to a different SAN or network location, in order to free up the F drive for SQL use as well as for better DR.
Move log files and tempdb data files to the F drive.

Suggestions?


texts: Table with only a single constant value?

texts:
I use C# entity framework code first.

My knowledge regarding databases is almost nothing. I don't know if it's common or good practice so I wanted to ask you guys how I should do it.

Say I have a table of letters and a table of stations.

I develop an application that sends letters from one station to other stations. Each letter can be sent to different stations - it's a many to many relationship.

Each letter that is sent is associated with a station - the source station of the letter (a letter cannot be sent to it's own source station, etc..).

Each database instance represents a station.

On application startup I know which station is my source station and want to save that information in the database.

How should I save which station is the source station? Should I have a flag in the stations table in each row indicating if it is the current instance station? This sounds bad to me.

Is there a way to have a table with only 1 value? For example that will be called InstanceStation that will contain only one row with a single column - StationId? Is this a good practice?

I tried to be as clear as possible, I hope my situation is clear.


texts:
We have a MySQL backup. Did some changes on our database and some data was lost. In the mean time new records were added.

We have to restore the mysql backup over our latest data.
Some of the tables have auto increment columns which means that we will end up with the same primary key but different values in some tables. The majority of the tables are from a many-to-many or one-to-many relationships.

What is the best approach to resync the same schema between the backup and the current data?


texts: Is it correct to get SQL Server read write ratio from sys.dm_os_buffer_descriptors?

texts:
I'm reading a book and the author taught us a trick to calculate the read write ratio by using sys.dm_os_buffer_descriptors.

I suspect the correctness of this method. After a page has been read into memory, we could read from and write to it many times by different transactions. Before a checkpoint, the page will stay dirty in memory and the author just count it as a single write. Is this the right way to calculate SQL Server read write ratio? If not, how can we do that?


texts:
I started my course and have been working on the ER diagram.

Instructions provided are

Minimum requirements and business rules for the College database
a) The college is organized into Departments.
- Each department has a number, name, location, phone number, budget, and an employee who manages the department.
- Keep track of the start date of the each department manager.
b) Employees work for a department and each employee works for a designated project.
- Each employee has a number, last name, first name, address, city, phone number, rank and salary.
c) Faculty members work for a department.
- Each faculty has a number, last name, first name, address, gender, birth date, area of expertise, and salary.
- Each faculty works for one department but may work on several research projects.
d) Each department controls several research Projects.
- Each project has a name, number, duration (in months), budget, and a primary investigator.
e) Each faculty teaches several Courses.
- For each course, keep the course name, number, credit hours, type (lecture, lab, distance learning, etc.), and cost per credit hour.
- A course can be offered by different faculty members.

This is what I have done using ERWin 3.5

I am stuck and know that this diagram is wrong.
Can someone help me with this please.
Thanks in advance


texts: What is the difference between logical schema and physical schema?

texts:
While going through Database architecture I came across Schema's . I am very much confused with:

Logical schema

Physical schema

How these two schema exist in the Database?

How can these be manipulated according to need?

How Logical schema and physical schema are related?


texts: With SSMS on my local machine, is it possible to login to a remote SQL server on my VPN network via my Active Directory credentials?

texts:
I'm trying to login to a SQL server on my work network with a VPN connection to the network and by using my AD credentials.
When I'm directly on my office machine, I use "Windows Authentication" as the Authentication method in SSMS and that works fine.

When I'm VPN'ed onto the network and using my local machine, Windows Authentication doesn't work (understandably) but if I try to use the "Active Directory - Password" Authentication method (I was hoping I could put in my work's AD account to connect to the remote server) I get the following error:

It sounds like it can't find the server, but when I ping the server from my local machine, I'm getting a response.

Note: "Allow remote connections to this server" is checked.


texts: Looking for a utility to move data between databases (prod to dev) SQL Server 2012

texts:
I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a couple hundred tables so the process is a bit arduous.

Have you seen any third party utilities that will do this?

I haven't tried SSIS for a few years, but it has historically been problematic and more time consuming to setup than it was worth. I don't need to do anything except copy the data into the dev tables (not an append, but a drop all the rows and insert the data). All the schema already matches.

Yes -- I have searched for the answer already... that's why I'm posting :)

Let me know and thanks.

---------UPDATE
So how about this? Why not just link the servers; truncate the dev table and do a select into from prod to dev? I can script this out for the majority of the tables and not have to worry about a gui.

Downside?


texts: Why is the result cache always disabled in Oracle 11g

texts:
I'm trying to enable the result cache in Oracle 11g. I have used Enterprise Manager to change the result_cache_max_size and result_cache_max_result values to '1M' and '100' respectively (maybe these aren't optimal settings, but I'm just trying to get it to work!).

After I make these changes I use the following query to see if the changes have really been made:

select * from V$PARAMETER where LOWER("NAME") like '%result_cache%';

I can see that both have been set correctly. However, when I run queries with the result_cache hint, I can see from the Autotrace that the cache isn't being used. If I then run the following query:

SELECT dbms_result_cache.status() FROM dual;

I can see that it's status is 'DISABLED'. Restarting the database just makes the result_cache_max_size parameter go back to 0 (disabled).

How can I enable the result cache?


texts: why there is no performance differences if I use where clause inside of inner join?

texts:
Basically I have two different types of query in BQ.
the first one:

select q2.name, q1.* , q2.val1 from table1 as q1
inner join
(select name,val1, val2 from table2) as q2
on q1.name = q2.name

and second one is:

select q2.name, q1.* , q2.val1 from table1 as q1
inner join
(select name,val1, val2 from table2  where val1 = "X") as q2
on q1.name = q2.name

so as you can see there, the only difference is filtering X value. When I filter the X value inside of table2, size of table2 is being almost half. So my expectation is seeing a reduction of bytes of data, when I apply the "where" clause. However when I run --dry_run in the BQ cli. I got exactly the same bytes of data.

Query successfully validated. Assuming the tables are not modified, running this query will process 6958332498714 bytes of data.

So can somebody explain why I got exactly the same processed data size?


texts:
I am trying to convert the following logic in C# to a SQL Server stored procedure:

C# logic

// Filter by the search term if the first OR last name is null or empty
// || is an 'OR' operator that won't evaluate the next arguments if the first is true
if (String.IsNullOrEmpty(firstName) || String.IsNullOrEmpty(lastName))
{
        return result.Count(c => c.FirstName == searchString ||
                c.LastName == searchString || c.Brn == searchString);
}

// Filter by the first and last name if they are both present.
// Note: The first name doesn't have to be an exact match, but the last name does
// && is an 'AND' operator that won't evaluate the next arguments if the first is false
return result.Count(c => c.FirstName.StartsWith(firstName) && c.LastName == lastName);

This is what I have so far.

SELECT COUNT(*)
FROM [Services] s
INNER JOIN .[Clients] c ON c.Client_Id = s.Client_Id
INNER JOIN .[AdHoc_Referrals] r ON r.[Referral_Id] = s.[Referral_Id]
WHERE r.[ReferralTypeId] = @referralTypeId
  AND s.[Service_Delivery_Type_Id] IN ({0})
  AND s.[Inital_Service_Assignment_Date] IS NOT NULL
   OR ( -- This clause is meant to match the first lambda expression
       c.[Firstname] = @searchString
        OR c.[Surname] = @searchString
        OR r.[Reference_Number] = @searchString)
  AND (GETDATE() >= s.Inital_Service_Assignment_Date
       AND @visitDate <= DATEADD(dd, 1, COALESCE(s.Actual_Discharge_Date, s.Inital_Service_Assignment_Date))
      )


texts: Does switching from INT to BIGINT entails significant downtime?

texts:
Hypothetically, if your INT column already has 4 billion rows, how long is the process of converting that column to BIGINT? I know this will depend on server resources but I just want to have a general idea, say server has 8GB RAM 4CPU.
If you are certain you will hit INT's limit at some point, although years away, does it make sense to go straight to BIGINT?


texts: Primary keys, clustered indexes and partitioning

texts:
We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of conflicting advice and resistance, and don't have all the facts to decide on ways forward. Below is essentially what all 4 tables look like.

CREATE TABLE [Example].[MasterRecordTable](
    [rowDateTime] [datetime] NOT NULL CONSTRAINT [rowDateTime] DEFAULT (GETDATE()),
    [recordID] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NOT NULL,
    [field1] ...,
    ...
 CONSTRAINT [PK_MRT] PRIMARY KEY CLUSTERED
(
    [recordID] ASC,
    [date] ASC
)

CREATE TABLE [Example].[ChildTables](
    [recordID] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NOT NULL,
    [field1] ...,
    ...
 CONSTRAINT [PK_CT] PRIMARY KEY CLUSTERED
(
    [recordID] ASC,
    [date] ASC
)

All tables use a clustered primary key that covers recordID and date, even though at no point while processing do we JOIN on or include date in WHERE clauses, and it's only recordID that needs to be a primary key. To me date should not be present in the primary key or clustered index.
All tables carry the date field, is this necessary if you consider my question about archiving/partioning below? The only reason it's in all tables is to assist with our manual archiving process.
We do not make use of foreign keys, and I'd like to know if this is worth reconsidering
We need an archiving strategy. At the moment we have tables with the same structure as above, with _Archive added to the name and placed in a separate file-group and hard drive. We then manually move records WHERE date <= @aYearAgo for each table over to it's _Archive equivalent, daily. This, as well as developing queries that bridge both tables, is tedious and time-consuming. We're busy evaluating partitioning, and I'd like to have some well-informed answers as to what setup is ideal considering the table structure above. It makes sense to partition and archive based on the date value. We'd like to be able to move older data over to progressively slower hard-drives, then deleted after 5 years.
Would it not be more optimal to have a clustered primary key on recordID alone, does the partitioning field (date) have to be apart of the clustered index?
The only time we need date is when we report on these tables. Depending on your answers above, I imagine this can be done best with an NC index on the Master table, which is then joined to the Child tables via recordID.

Please let me know if anything is unclear. This is a dump of the concerns off the top of my head, I'm keen to work with someone knowledgeable on indexes, key relationships, and partitioning. Thanks!


texts: How to take offline a database in Oracle?

texts:
We have direct functionality of Take Offline to SQL

but how to do same functionality in Oracle Sql Developer


texts: Is there a way to convert a custom format Pg dump file to a directory format?

texts:
We create a "custom" format (-Fc) dump file of our production DB each night. One of the things this is used for is restoring into a test DB.  We then run a number of update queries to anonymise the data, but because there are millions of rows this is rather a time-consuming task.
I had the idea that if we used the "directory" format (-Fd) dump then I could run some scripts to directly modify the data in that dump. Then instead of the two step (pg_restore+updates) we'd simply be restoring anonymised data in a single step.
However I still need the custom dump file for feeding into other processes, and I'd prefer to avoid dumping twice if possible.  So I was wondering if it's possible to take a custom format dump file and convert it into a directory format dump.


texts: Removed privileges from root user and can't access database

texts: New to databases and accidentally removed privileges from root user; only privileges I currently have are USAGE and PROXY. This is a MySQL database that I access through HeidiSQL. Currently cannot access my data tables; is there a way to get these privileges restored? Did not create any other user accounts.


texts: Which Plan is Better? WHERE EXISTS Instead of INNER JOIN

texts:
Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.

Logical reads plummeted, but plan cost and memory performance seem worse. Duration and CPU are slightly better with the WHERE EXISTS

Curious if this new plan (with the WHERE EXISTS) is superior to the one with the INNER JOIN. If so - why technically would a WHERE EXISTS would be a better option?

Old Plan
New Plan


texts: How to get the result of MySQL explain query into a file?

texts:
I am unable to get the results of the explain query in its normal tabular format or json format into a file.

select * from table_name into outfile 'file.format';
select trace from information_schema.optimizer_trace into outfile 'file.json' lines terminated by '';

The above queries work, and I get the file. But the following queries do not create the file.

explain select * from table_name into outfile 'file.txt';
explain format=json select * from table_name into out 'file.json' lines terminated by '';

Is there any way to achieve this? It is preferable if the tabular explain results are written in text file with column names and table borders.


texts: MultiSubnetFailover=true for DBMSes besides SQL Server

texts:
A connection string for SQL Server can contain MultiSubnetFailover=true, to handle multiple DNS A records, letting the client try to connect to every IP address of a domain (or subdomain) name, to access the current master (of Always On availability groups).
Does this work with other DBMSes, like MySQL?  If not, is there an equivalent?


texts: SQL Server 2019 System.Data.SqlClient namespace query

texts:
I am using the .NET classes in the System.Data.SqlClient namespace on a SQL 2019 Enterprise Edition and have successfully run TSQL commands via the creation of a function in PowerShell.
function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

SqlQuery $Server $Database "SELECT * FROM Staff"

I have some quite large pieces of T-SQL code stored in discrete files that I would like to execute against this instance.  Is it possible to utilise the function to call a T-SQL script stored in a filesystem file?


texts:
I have setup with FCI+AG solution but with independent disks. There is no listener, and the applications are connecting to the FCI. Is this setup even reasonable to use? The only option I see here is manual fail over. If I need to make any changes, what can be done? Can I add a listener to the AG because both the nodes are in sync? . I did not build this solution but inherited it when I joined the company I work for.

From a storage perspective was this supposed to have a Shared SAN instead of independent disks? I went through the below given link but my doubt has still not being clarified

Do you have to use a shared disk with AlwaysOn FCI?


texts: Oracle - Connecting as a proxy user via connection string

texts:
I have been granted access to a specific schema in an Orale database via a "proxy user" / "connect through" .

In SQL Developer / TOAD, I give my username as "MyUser[SomeSchema]", and after login I am now able to query all tables etc. in SomeSchema. I can also login using just "MyUser", but then I am not able to query any tables in SomeSchema (not even if using the schema.table syntax.)

So my question is: How do I "replicate" the MyUser[SomeSchema] approach when connection from code using a connection string (using ODP.NET on .NET)?

UPDATE 1
I found that if I run the following SQL in Toad, I actually get the result I want.

connect MyUser[SomeSchema]/{password for MyUser};

My current schema is changed to SomeSchema and I can query it's tables.

Question is, whether this can be achieved via ODP.NET directly?

UPDATE 2
I finally figured out how to piece together the connection string. See my own answer below.

Research

I've googled quite a bit, and tried the following with no luck.

Supplying MyUser[SomeSchema] as "User Id".
Supplying the "Proxy User Id" and "Proxy Password" parameters in the connection string. However, I do not have a proxy user password.
Changing the schema after login using the "ALTER SESSION" syntax: "ALTER SESSION SET CURRENT_SCHEMA = SomeSchema". This does not fail, but I am still not able to query any tables in the schema.
Using SQL Tracker on Toad itself, to see if it ran any SQL to connect to the proxy, but that seems to happen "before" SQL Tracker can pick anything up. Triggers?


texts:
I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 is the PRIMARY and Server2 is the MIRROR. Server2 was purchased for the purpose of mirroring so no live DBs yet. But Server1 is already in production. So here's the complete detailed setup for both.

Created a domain\user account and used it for both Servers' SQLService

Principal Server Configuration

Name: SERVER1.domain.com
NIC1 : 172.xx.xx.80
NIC2 : 172.xx.xx.81 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

Mirror Server Configuration

Name: SERVER2.domain.com
NIC1 : 172.xx.xx.82
NIC2 : 172.xx.xx.83 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

NO WITNESS YET

Static A records in DNS server

172.xx.xx.81 dbm-pri.domain.com and 172.xx.xx.83 dbm-mir.domain.com

Here's what's done on both servers:

checked SQL Server Config Manager and made sure TCP is enabled, then added the ports to be used in IPAll field
restarted the SQL Service (at one point of despair I restarted the servers itself, yes even the one running on production)
run netstat and confirmed that the endpoint ports are LISTENING, (sqlserver.exe), also run ping and tracert all tests are good
from both PRINCIPAL and MIRROR, ran telnet FQDN port, confirmed that I can connect from/to both
created the endpoints on both servers

PRINCIPAL

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.81))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

MIRROR

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.83))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

granted CONNECT permission to the domain\user account

CREATE LOGIN [domain\user]
FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

GRANT CONNECT on ENDPOINT::INSTANCE1_EndPoint TO [domain\user]
GO

backed up and restored the DB and logs with NO RECOVERY
ran alter db set partner on the mirror server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-pri.domain.com:6025'

ran alter db set partner on principal server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-mir.domain.com:6025'

Error:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://dbm-mir.domain.com:6025" can not be reached or does not exist...

tested netstat, ping, tracert and telnet on both Servers, all are successful

I've tried re doing this so many times now and I'm lost for ideas


texts: Is it worth partitioning a Table by single value in Oracle?

texts:
Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in advance? Let's say you have a table AUDIT with TenantId column which could only containt values: 1, 2.
All SELECT statements would have a WHERE clause with a TenantId parameter.

So, would it be beneficial to partition this table by TenantId? If so, would you also create an index on TenantId column?

partition by list (TENANTID)
(
  partition TENANT1 values (1),
  partition TENANT2 values (2)
)

I have done a little experiment: inserted 1M records with TenantId randomly generated, thus creating:

499652 records with TenantId=1
500348 records with TenantId=2

Here are the query plans for statement:

SELECT * FROM table1 WHERE TENANTID=2

Plain Table (no indices, no partitions)

Bitmap Index:

Partitions:

Btw, in case where I have both, the index and partitions, query plan uses partition and not index, hence the plan looks exactly the same as the 2nd one shown above.

Clearly the partition wins, but does it?
Apparently, the cost column in an execution plan is not a reliable way to judge the real costs of a SQL statements response time.

So, what is the best way then? How to choose one over another?


texts: SQL Server 2017: OR and LIKE Interaction

texts:
I am using SQL Server 2017 (i.e. v14).
I could use one of the following equivalent criteria in a query:
x IN ('104','504','554')
(x='104' OR x='504' OR x='554')
(x='104' OR x Like '5[05]4')

If I use either of the first two, the query takes around ten times longer to execute than the third one.  Originally, different query plans were produced.  However, as I stripped the query down, I managed to create simple queries which produced exactly the same plan for all three.  However, only the query using the third statement ran quickly and those using the other two ran slowly.
Since the query plans are identical, it leads me to believe that there is something special about a formulation with an "= OR Like" structure.
I have also tried these similar but non-equivalent alternatives:
x Like '[15][05]4'
(x='104' OR x='504')

They took a long time to run, and their query plans were slightly different.
I would like to understand what is going on so that I can adapt my query writing appropriately!  (At least, until I have a new version of SQL server to play with and retest this peculiarity.)
Edit: query plan attached.


texts: Only do log backup if necessary in SQL Server

texts:
We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup?

Some of the databases don't have any changes during the night, for example. But we'll still get many tiny log backups for these databases every time the log backup script runs.

I already tried scripting something based on the previous backup's last_lsn recorded in msdb and using fn_dblog to see if there were new committed transactions. I was not successful so far.

A bit of background:

These log backups are taken every few minutes and saved to Azure Blob Storage. They are not restored to another database (aka log shipping). During a single day we'll get a few hundred log backups per database. In case we ever have to restore something from Azure, I want to avoid having 100s or even 1000s of log files without any user transactions in them lengthening the restore process.


texts: Spliting changes in separate collection/table or not?

texts:
I am using MongoDB to store products, user accounts and stuff on my server and so far it's going nice.

But now I'm stuck deciding how do I "split" things apart, like:

I have a database where products are stored, but now I want to log every change made (who made it, when, etc).

Should I put that log (array) in the same (product) document, or should I separate the log in a separate collection?

products:
{
   _id: 1234
   product_name: "Some product"
}

product_changes:
{
  _id: ...
  ref_id: 1234
  changes: ["...","..."]
}

or

products:
{
   _id: 1234
   product_name: "Some product"
   changes: ["...","..."]
}

I'm afraid that if I attach too much Objects in one document, that the performance will suffer, and if I separate everything, things begin to "fragment".

Of course, in the example, "changes" is just a tiny array, but in real usage it may be a array with a lot of objects each with time, user_id and changes made. And summing all this up, might turn this single document big.

Any help on this is greatly appreciated.


texts:
Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how?

Oracle provides this via database and network encryption. Oracle provides encryption at table, tablespace level. Does PostgreSQL also supports this?


texts: Fetch records of postgres and store it in different table , if one column is null

texts:
I have a table employee(id,empname,salary,dob)
   If the record of id and empname is null..whole record should be removed in different table...

For example.....

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
null | aarush  |200000 |   4
102  | rkr     |2000000|   6
104  |null     |300000 |   3
105  |null     |400000 |   3

If Id column has null value..the record should be fetch and placed in different table say null_table ...same case for empname column
the rest record should be placed in different table say success_table

The output should be

success_table:

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
102  | rkr     |2000000|   6

null_table:

 id  | empname |salary | year
_____________________________
null | aarush  |200000 |   4
104  |null     |300000 |   3
105  |null     |400000 |   3

I am new to postgres...Any suggestions are appreciated...


texts: designing multiple tables while eliminating duplication?

texts:
As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating.

What I'm trying to design is [n] tables that will store subject_names, subject_id, grade_name grade_number and ofcource grade_id. But since the same subject could be taught for different grades; there will be duplication.

For example, Math will be taught for every grade which means there will (10+) columns with subject_name set to Math, but each one will have different grade_id.

The current design I have in mind(which I didn't implement yet) is creating two tables, subjects and grades and subjects table will store subject_name, subject_id and grade_id and grades table will contain grade_id, grade_name and grade_number. Unfortunately this design will contain many duplicates.

So, what's the correct way of creating such a database design?

Thanks in advance.

EDIT:
Sorry for confusion, I'm referring to grade as a year of education.


texts: Propagate where condition into CTE of a view, using index?

texts:
I have written a multi-step view using CTEs.

The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that includes an important id (obj_id); then some analytic functions; then a couple of left joins; and finally the last query adds some more computed values.

One important use case (not the only one) is to only perform this computation for a single obj_id (which corresponds to multiple records in the source_table)

I have tried using a simple where on my view: select * from my_view where obj_id = ? This of course gives the correct results, but an Explain shows that it is not using the index for obj_id in source_table, instead performing a full table scan.

How can I tell Oracle to take the where condition on obj_id (added outside the view) and push it down to the innermost CTE, in order to use the index on the source table?

Is there an index hint that can help me with this?

The obj_id column is passed down the pipeline unchanged, being included by itself in all select, group by, and partition expressions, so it should be logically "evident" that the entire query can be performed for a single obj_id.

This application is currently locked on Oracle 11g, if this makes any difference.

create or replace view MY_VIEW as
with
step1 as (
     select obj_id, begin_date as event_date, ...
       from SOURCE_TABLE
      where ...
  union all
     select obj_id, end_date as event_date, ...
       from SOURCE_TABLE
      where ...
),
step2 as (
     select obj_id, ..., sum(...)
       from step1
   group by obj_id, event_date
),
step3 as (
     select step2.*, ...,
            sum(...) over (partition by obj_id ...)
       from step2
),
step4 as (
     select obj_id, ...
       from step3
  left join ... on ...
  left join ... on ...
)
     select step4.*, ...
       from step4
;


texts: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) on SQL Server 2014

texts:
Have a SQL Server 2014 instance running on Windows 2012 R2 that was running Ola Hallengren's DB integrity script.  This is the error that resulted:

  The operating system returned error 665(The requested operation could
  not be completed due to a file system limitation) to SQL Server during
  a write at offset 0x0001392f6ee000 in file
  'H:\MSSQL12\Data\EDW_Data_6.ndf_MSSQL_DBCC15'. Additional messages in
  the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

I checked both the event log and the SQL Server error log but not getting a lot of additional info that could help. I haven't rerun the DBCC check for this db yet.  This database is pretty active as it is a data warehouse database.  It's currently 10.5 TB.  I've heard that if I rerun the dbcc it may clear out the MSSQL_DBCC15 however I'm hesitant to do that on this server due to the level of activity.  My preference would be to copy the backup to another server and run the dbcc there.  If I do that, is there any way for me to clean up the DBCC15 files left on disk on the primary server?

Any other places I could find more info other than event log and SQL Server error log?

Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.


texts:
We have a master slave replication [MySQL 5.6]. However In slave database 2 rows is missing in one table. I've tried to solve this using Percona Toolkit as suggested here.

  But pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the master, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.

So what if:

Insert the rows directly into the slave database

SET SQL_LOG_BIN=0;

INSERT INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4321, 'some val', ...),
       ( 4344, 'some val2', ...);

Run a REPLACE INTO query in Master database

REPLACE INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4321, 'some val', ...);

REPLACE INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4344, 'some val2', ...);

Can someone suggest which method is safe and reliable. Is this the right way to solve the issue. Is there any risk of doing this?


texts: Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

texts:
I am working on a report which will give information for different segments like

blocking process
slow running queries
disk space etc.

I also want to add one more segment where I can get information related to SQL Server index fragmentation.

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?


texts:
I store s3 bucket's path for files in my database. Files can be either pictures(jpg) or pdfs.

Let's suppose I have uploaded photo.jpg to my database via application, path will be like:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/small/photo.jpg
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main
/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main

For every file, I have 2 root paths:

/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main

I need to get, via where clause only the root = 'main'.

Example:

select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;

The query works, I get file, main, photo.jpg as result.

How can I include that regexp in a there clause?

Example:

WHERE (select REGEXP_REPLACE(path_name, '.*/', '') as col2
from seg_table) = 'main'

http://sqlfiddle.com/#!15/6d65d

There is an example. Please note the col2 returns not only 'main'. I need it to return only main and I need to put it in a where clause. I can't use ~ operator because I will need to do something like:

FROM test1 t1
JOIN another_view AS s
WHERE s.full_path = substr(t1.full_path, char_pos '/file..' to get /filesuser/client/.../attachment/.../) || 'main'

Can't use '/main$'; as it will give me:

/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main

instead of

/{s3bucket}/filesuser/client/27801124/attachment/4511/main


texts: Uniqueness constraint for elements of arrays in the entire table

texts:
In the following table:
CREATE TABLE user (
    email           varchar[],
    ...
);

I'd like to ensure that no two users have the same e-mail. I'm not sure how the UNIQUE constraint interacts with array data types. What's the appropriate way to ensure uniqueness? Or is this an anti-pattern and should I just declare another emails table instead?


texts: Problem forming a query with subqueries in design view? (MS Access 2010)?

texts:
I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql.

That being said, I cant seem to figure out how to whittle this query down properly using subqueries.

So, I am trying to find the users who have an invoice in Quarter 1 of every year since 2012.

I created 4 subqueries containing the user ids that have an invoice for 2012,13,14,15.

After that, I want to now create a main query where it shows the IDs for every entry that has an invoice for every single Q1 of those years.

So, what I did was created a one to many join from my main table to every single subquery and then had a different parameter for each subquery where the condition "is not null"

I keep coming up with zero entries though, and I know that there are foundations that have invoices for every month of every year, so there is something wrong in my execution.

Any ideas as to what I am doing wrong?

EDIT - the text of the query is

SELECT dbo_Name.ID
FROM (((dbo_Name LEFT JOIN Q_invoiceQ1_2014
  ON dbo_Name.ID = Q_invoiceQ1_2014.ST_ID) LEFT JOIN Q_invoiceQ1_2013
  ON dbo_Name.ID = Q_invoiceQ1_2013.ST_ID) LEFT JOIN Q_invoiceQ1_2012
  ON dbo_Name.ID = Q_invoiceQ1_2012.ST_ID) LEFT JOIN Q_invoiceQ1_2015
  ON dbo_Name.ID = Q_invoiceQ1_2015.ST_ID
WHERE (((Q_invoiceQ1_2014.ST_ID) Is Not Null)
  AND ((Q_invoiceQ1_2013.ST_ID) Is Not Null)
  AND ((Q_invoiceQ1_2012.ST_ID) Is Not Null)
  AND ((Q_invoiceQ1_2015.ST_ID) Is Not Null))
GROUP BY dbo_Name.ID;


texts:
Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed "A required privilege is not held by the client".

To cut a long story short... I am having trouble getting to much from our server admin guys.  All they would do was create me a new server, added it to an OU with a filter to block the Group Policy and I got it to work (using domain user for agent) but now I can't get anything out of them about the Group Policies being delivered.

So my question is, does anyone know the policy setting that could be blocking the making of a snapshot.

Any help greatly appreciated.
David


texts: Want to know whether an 'UPSERT' operation performed INSERT or UPDATE

texts:
I have a query where I try to insert a record and ready to update in case the record exists.

The question is that I would like to know, once the query is executed, if the operation has been an update or an insert.

Any idea?


texts: Is there a way to achieve Workload Management in MySQL?

texts:
Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)?

By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows prioritization of queries. In particular, I want to give high priority to tactical, short running queries from applications and give low priority to longer running background queries.


texts: How to group by items and calculate the quantities after calculation MySQL


texts: Change recovery mode and enforce a transaction log backup?

texts:
I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2).

The result is a large deletion of millions of blob data from a table by re-creating the whole table.

Reasons:

Remove no more required rows including the (blob) data
Improve performance
Reduce data file size (Regarding 3.: shrinking is not yet a part of the maintenance procedure, maybe later on...)

I plan to change the recovery model to bulk_logged to minimize logging and back to full after the maintenance.

The main operation performed is a SELECT INTO statement followed by creation of (non + clustered) indexes and constraints, defaults + truncating the original table + renaming of the new table.

I have read the suggestions at Considerations for Switching from the Full or Bulk-Logged Recovery Model.

There we are advised to run a Log Backup before and after the process. Unfortunately the regular (15 Min interval) log backup is done by third party software (backupexec). I have no control about it. When I tried to do a log backup using T-Sql I failed because there is

  no device present

I checked sys.backup_devices => nothing.

I want to do it by script because I have to do it on several different servers and don't want to do it in a user interface.

So, questions:

Would you agree to the procedure in general?
How can I enforce the Log Backup?
Is the log backup enough or is a log shrink required as well at any time?


texts:
Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.

I'm getting a lot of these errors:

1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/mysql_tmpfs/#sql_10ff_8.MYI'; try to repair it
141003 14:03:17 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.38/storage/myisam/mi_write.c:226

I have no broken tables.

Also, using iotop, I discovered that MySQL was writing files inside the temp directory at a speed of around 150MB/s, that seems too high to me.

After googling a while I discovered that the problem could be a full /tmp directory, so I've created a 4GB ramdisk mounted on "/var/mysql_tmpfs".

After I setted this ramdisk as MySQL's default temp folder the server was able to slowly process some requests. but still not even an half of what it usually handles.

What can I do to solve the problem?

All the software are updated.
The OS is Ubuntu Server 14.04.1 64-bit.
The machine has 6 cores and 32gb of RAM.

EDIT 1:

I've tried to manually run this query:

SELECT * FROM my_table WHERE ID = 400000

And it takes one second or more to output a result, obviusly I have an index in that column and there are around 450000 rows in that table.

The profiler says that 100% of the time has been spend on "Waiting for Table Level Lock", isn't it strange?


texts: curious case of two log files in sql database

texts:
I was doing a POC(proof of concept) for which I created two log files in a test database. I created a table and inserted 10000 rows. I observed that both my log files were getting used.
I know that logs are written serially, and in my case both log files have space on disk with autogrowth enabled, then why my second log file was getting used? Why not use a single log file and move on to second log file when space is not available. Isn't it supposed to be like this.?


texts: Is it worth checking value with select before updating

texts:
I have a tinyint boolean value in my row that is 0 by default and 1 whenever a specific thing happens. After it's 1, it's always gonna stay 1.
Now that specific thing can happen for each row many times, but I only need to update the value to 1 once.

Is it worth doing a SELECT, check the value and only if the value is 0, trigger the UPDATE?

Also would maybe this

UPDATE table SET lock='1' WHERE id='100' AND lock='0'

be better than

UPDATE table SET lock='1' WHERE id='100'

for my situation?


texts: Unable to turn on compression using innodb

texts:
I am unable to create a new table using compression.

I'm using the following options which from what I understand should allow me to use compression.

default-storage-engine          = InnoDB
innodb_large_prefix             = true
innodb_file_format              = barracuda
innodb_file_per_table           = true

This is on a brand new install of MySQL, and these configuration options have been set before creating the new database, so file per table should be set.

When I try to create a new table I get the following response:

CREATE TABLE IF NOT EXISTS `db_example`.`table_example` (
   `ugid` INT NOT NULL,
   `uid` INT NOT NULL,
   PRIMARY KEY (`ugid`, `uid`))
   ENGINE=InnoDB ROW_FORMAT=COMPRESSED;;

Error message:

Error Code: 1031. Table storage engine for 'table_example' doesn't have this option.

I don't understand why this would be happening since the storage engine is set to innodb, which should have support for compression.

Additionally. If I force the creation of the schema to complete by disabling innodb strict mode, and then try to alter the tables enabling compression:

ALTER TABLE ${table_goes_here} ROW_FORMAT=COMPRESSED;

I get the following:

ERROR 1478 (HY000) at line 1: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'


texts:
I am looking for a solution to execute multiple commands in PSQL.

Lets say I have a file with 2 name for database.

1. slave1
2. slave2

I already have a master template named "master_db".

If I would do it manually, I will do the following:

CREATE DATABASE slave1 TEMPLATE master_db;
CREATE DATABASE slave2 TEMPLATE master_db;

If there were only 2, it would be very easy but in my case there are more than 30, hence I would like to know if we can create a script. I did try to search but couldn't find a solution.

The databases are in the same cluster, if that matters.


texts:
We have a master - slave setup with ROW based replication.
We are seeing huge delay's on the salve even though there is no activity running wither on master or slave.

When we looked in, we observed the SQL thread looks like hanged. It has been in "Reading event from the relay log" state since last 3 hours or more.

baleaf:(none)> show processlist ;
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| Id     | User        | Host      | db   | Command | Time  | State                            |        Info             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| 217159 | system user |           | NULL | Connect |  1039 | Waiting for master to send event | NULL             |
| 217160 | system user |           | NULL | Connect |  10045 | Reading event from the relay log | NULL             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
4 rows in set (0.00 sec)

baleaf:blackarrow_dw> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.001403
          Read_Master_Log_Pos: 95601911
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 12757514
        Relay_Master_Log_File: binlog.001403
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 32820355
              Relay_Log_Space: 75539220
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 7720
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

Shall some one please have a look into this ASAP.


texts:
I have scenario where there is Table T with columns PK,A, B, C,D

TABLE t
(
PK,
A,
B,
C,
D
)

i have to insert data from different sources ie columns A,B,C,D are calculated separately..
and they same primary PK.

I would like to ask if it better to create indivdiaul tables for each column and then join them using PK and insert into final table T

pk|A  ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

or

it is ok to update ie

first have some thing like T (PK,A,B,C,D)
DATA SOMETHING like (1,a1,NULL,NULL,NULL)

run update statement for all columns B,C,D  sequentially and get all the data

T(1,a1,b1,c1,d1)


texts: How to change Zeros ( 0 ) to NULL in SSIS?

texts:
I would like to change some Zeros into NULL using SSIS 2008. In that context I tried "conditional split" where I have set the columns test in question with the following expression:

REPLACE((DT_WSTR,8)test,"0", NULL(DT_STR,10,1252))

but that doesn't seem right. The expression is red.

I get the errors:

  data types "DT_WSTR and "DT_I8" are incompatible for conditional operator...

  Error at create lists [Derived Column 9 [141128]]: Attempt to find the input column named "NULL" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

  Error at create lists [Derived Column 9 [141128]]: Attempt to parse the expression "REPLACE(PLZ,"0",NULL)" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

  Error at create lists [Derived Column 9 [141168]]: Attempt to parse the expression "[PLZ]==0 ? NULL(DT_I8, 10) : [PLZ]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

  Error at create lists [Derived Column 9 [141168]]: Cannot parse the expression "[PLZ]==0 ? NULL(DT_I8, 15) : [PLZ]". The expression was not valid, or there is an out-of-memory error

Sample data:

test     test ideal

 0            NULL
120           120
304           304
100           100
222           222
520           520
0             NULL
0             NULL
10            10
11            11
0             NULL


texts: Postgres incomplete startup packet error

texts:
I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server
* Error: could not exec /usr/lib/postgresql/9.3/bin/pg_ctl /usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main -l /var/log/postgresql/postgresql-9.3-main.log -s -o  -c config_file="/etc/postgresql/9.3/main/postgresql.conf" : [fail]

So I checked the log file with this output:

 2015-01-05 21:50:05 EST LOG:  database system was shut down at 2015-01-05 21:50:03 EST
 2015-01-05 21:50:05 EST LOG:  database system is ready to accept connections
 2015-01-05 21:50:05 EST LOG:  autovacuum launcher started
 2015-01-05 21:50:06 EST LOG:  incomplete startup packet
 2015-01-05 21:51:22 EST ERROR:  syntax error at or near "exit" at character 1
 2015-01-05 21:51:22 EST STATEMENT:  exit;

"Incomplete startup packet" seems to be the culprit but  I am having trouble finding info out there on what might be going on, so I thought I would ask. Thanks in advance.


texts: PGAdmin III Server Status Error: Could not read directory "pg_log" No such file or directory

texts:
Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7.

I tried Tools->Server Status and I get an error:

  "Could not read directory "pg_log" No such file or directory"

I don't see any answers online. The server is mostly setup with default settings (for logging). The user I'm connecting with through pgAdmin is a superuser level account.

Purpose of use: monitoring what will become a live system.


texts: Auditing authentication issues in a distributed environment

texts:
I'm trying to get a better handle on what locked out a specific user when multiple Oracle databases are involved using database links.

Setup:

sqlplus kjohnston@db2

create user testuser identified by "Dummypass1";
grant create session to testuser;
exit;

sqlplus kjohnston@db1
create public database link testlink using 'DB2';
create user testuser identified by "Differentpass1";
grant create session to testuser;
exit;

sqlplus testuser@db1

Run the following a few times until testuser@db2 is locked.

SELECT * FROM dual@testlink;

On DB2, I can query on the following:

SELECT users.username, users.lock_date, aud.os_username, aud.userhost, aud.sessionid, aud.os_process
  FROM dba_users users
  JOIN dba_audit_trail aud
    ON aud.username = users.username
   AND aud.timestamp = users.lock_date
 WHERE users.account_status in ('LOCKED', 'LOCKED(TIMED)')
   AND aud.action = 100
   AND aud.returncode = 1017;

USERNAME    LOCK_DATE           OS_USERNAME USERHOST    SESSIONID   OS_PROCESS
TESTUSER    09-MAR-17 09.39.14  SYSTEM      VM1         10484322    404:5808

In this case, it's obvious that this lock originated from a database link since the OS_USERNAME is SYSTEM and the USERHOST is VM1, which is a database server (so few people have access to it anyways) where database DB1 is running from.

However, i'm unsure where to go from here in my analysis.  Sure, I have the SESSIONID and the OS_PROCESS from dba_audit_trail (as shown above), but I've been unable to successfully query the appropriate table on DB1 in order to narrow down what was running and who was running what on DB1.

I've tried querying v$session (on SID and AUDSID columns) and v$active_session_history (on SESSION_ID) on both DB1 and DB2 in an attempt to get more information.  However, all attempts to query these tables on either database using either SESSIONID or OS_PROCESS values have failed (0 results returned).

Since there are no private database links on DB1 that connects directly to testuser, it looks like this probably originated from a public proxy database link, but i'm unsure how to find what testuser@db1 was actually running at the time that testuser@db2 got locked out.

Also, consider the possibility that testuser doesn't equate to a specific person, but it is an application schema that many different programmers may have access to.

Also, the logged in user that ran the dblink connection may not even be TESTUSER@DB1 since a user with create any procedure privilege could have:

create procedure testuser.testproc
  v_var varchar2(1);
begin
SELECT dummy
  INTO v_var
  FROM dual@db2;
end testproc;

exec testuser.testproc;

Any ideas how I can track down what locked out testuser@db2 (the SQL that was running, preferably) given my supplied example?


texts: Transactional replication - Table / Stored Procedure changes coming over, but not new Tables / Stored Procedures

texts:
I've set up transactional replication between my on-prem database (SQL 2017) as a publisher and Azure as a subscriber.

My CHANGES (insert / update, alter stored procedure, alter table, etc) are coming over, but my NEW stored procedures aren't.  In SSMS 17, I went into Replication > Local Publications properties and checked the new stored procedure in the [Articles] tab, but it's still not making it over.

It's not clear what I need to do to get the new stored procedure to come over.  I looked at sp_addarticle but it looks like it would be the same that I did with the checkboxes in the GUI.


texts: Implement a two sided zero to many relationship?

texts:
How does one implement the following relationship?

One car may belong to zero or many persons
One person may own zero or many cars

Ideally I could have a junction table that has a zero to many relationship to both Car and Person tables. Would that be the correct table organization?


texts:
there is a sql query that joins both example1.dbo.table1 and example2.dbo.table2
User1 has no select permission on example1 DB.
User1 has select permission on example2 DB.
User2 can run that query
how can User1 run that query ? I dont want to give select permission to User1.
Is there any possibility of that ?
I created this query as procedure and added "execute as user2" inside of it but still couldn't run for User1.


texts: Deleting all records on a table except the latest 10 for each user

texts:
I have an activities table that has a foreign key reference to a users table. A user can have many activities, and I have a job that will populate their activities on a daily basis. After that job runs, I want to run a SQL query that will delete the oldest records per user and keep the latest 10. This is the schema:
users
id name
1  Jimmy
2  Johnny

user_activities
id user_id activity date
1  1       foo      2020-08-07 00:00:00
2  1       bar      2020-08-08 00:00:00
3  1       baz      2020-08-09 00:00:00
4  2       foo      2020-08-07 00:00:00
5  2       bar      2020-08-08 00:00:00
6  2       baz      2020-08-09 00:00:00

How can I write a query to truncate records up to the latest 10 for each user?


texts: Completely disable all support for a certain CHAR SET in MySQL?

texts: Is it possible to completely disallow certain charsets in MySQL and force an error and/or force everything into the server's chosen default charset?


texts:
So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one.

I have this statement to get the vid column order proper:

SELECT vid, COUNT(id) AS votecnt
  FROM votes
WHERE status = 1
GROUP BY vid
ORDER BY votecnt DESC

And then I have to filter the stories themselves, and I want the to be ordered just as they are in the first clause.

SELECT vid, body, timestamp
  FROM stories
WHERE lv = 1
  AND status = 1

The thing is, it's the the order that I need, because the first statement does not return all of the rows that the second one does. It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

Any ideas how to join all this into an SQL query? Or should I use PHP in between?


texts: I have SQL Server 2008 R2 running on a dedicated server together with a web site hosted on that same server.  We now wish to access the SQL Server databases from an Azure Web API, however for security reasons we require that the databases remain on the dedicated server and aren't moved to SQL Azure.  I have been able to expose the SQL Server using SSL only external connections.  I have also been able to use a firewall rule to lock down the port so that only I can connect from my IP address for testing it out.  Is there a way to lock it down so that only my Azure Web API (or potentially multiple instances of it) can connect to the databases?


texts: MongoDB 4.2 Shard Cluster: what is db.cache.chunks.DB and how to flush it

texts:
Here is my MongoDB cluster (I didn't draw about Mongos but exist) I made "news_cat" db and created some collections and I did sharding.
I delete DB ("news_cat") in Mongos using db.dropDatabase() but in the replica set2 in config DB
"news_cat", which I had already deleted, exists also in collection too.
db.cache.chunks
rstest02:PRIMARY> db.
Display all 3234 possibilities? (y or n)
db.adminCommand()
db.cache.chunks.news_cat.col1
db.cache.chunks.news_cat.col2
db.cache.chunks.news_cat.col3
...

and I also checked about log in config and too many logs exist caused by "news_cat":
   2020-09-08T14:25:29.566+0000 I  SH_REFR  [ConfigServerCatalogCacheLoader-7] Refresh for database news_cat took 0 ms and failed :: cau
sed by :: NamespaceNotFound: database news_cat not found

https://jira.mongodb.org/browse/SERVER-50241 also same symptom.
I thought if there's some issue about dropDatabase() command I checked in the documentation https://docs.mongodb.com/v4.2/reference/method/db.dropDatabase/
I did flushRouterConfig but it doesn't work.


texts: How to calculate end_date from start_date and duration in SQL Server Query

texts:
I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.

I am providing the start_date and duration as the input to calculate end-date.

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020.

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

Please help me on this.


texts: SQL Server 2012 Standard server with 50 instances won't uninstall instances

texts:
I suspect this is an issue because we hit the 50 instance max limit of SQL Server. At the limit it won't install more instances, naturally. However, it seems it won't UNinstall them either. That's worse.

It hangs at the "Select Features" dialogue when you hit next after selecting the SQL Engine for the instance.

I didn't find a lot of info about this. The most interesting hit was this:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/24a1e5f3-25f3-48c8-973a-4c6a18578e42/trying-to-uninstall-1-of-50-sql-server-2008-r2-express-hangs-on-please-wait

It also suggest using setup.exe with some parameters to uninstall the instance, but I can't get it to do so without starting the GUI and into the normal uninstall which fails.

Elevation does nothing. Reboot doesn't help. Logs only say "canceled by user", because I have to kill it in the end. Nothing obvious prior to that point. Validations are all green.

The setup says it is SQL Server 2012 SP1 - all instances are at 11.0.2100.

I' choosing to to answer/close my own question since Microsoft has closed the ticket with the status of "Won't fix."

Hopefully they will have fixed this for SQL Server 2014, but I have not and do not plan to test that myself, nor do they provide any additional comment on closing the ticket.


texts:
I'm moving my website to new domain, need to replace all references of old domain to a new, in fact actually replace one string on another in all tables of MySQL DB.

Please help me build the query to do this.

Thanks.