texts:
In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space.
References are stored in text type columns by hibernate so using the tool as is is not an option.
Looking at vacuumlo sources ( vacuumlo.c) I have created a vacuum_l table like so:
CREATE TABLE vacuum_l AS SELECT oid AS lo, false as deleted FROM pg_largeobject_metadata;

and proceded to delete all rows from vacuum_l table wich oids are referenced by user tables in my database.
Now is safe to call unlink(lo) for all items remaining in the vacuum_l table.
Unfortunately the table is very big and, as reported in the vacuumlo.c source code:

We don't want to run each delete as an individual transaction, because the commit overhead would be high.  However, since 9.0 the backend will acquire a lock per deleted LO, so deleting too many LOs per transaction risks running out of room in the shared-memory lock table. Accordingly, we delete up to transaction_limit LOs per transaction.

So far I tried to unlink all oids with this sql function:
CREATE OR REPLACE FUNCTION unlinkOrphanedLobs() returns void AS
$BODY$
DECLARE
count_variable int;
begin
    loop
        SELECT COUNT(*) INTO count_variable FROM vacuum_l WHERE deleted=false;
        EXIT WHEN count_variable <= 0;
        UPDATE vacuum_l SET deleted = true WHERE lo IN (SELECT lo FROM vacuum_l where deleted=false limit 500);
        PERFORM lo_unlink(lo) FROM vacuum_l where deleted=true;
        delete from vacuum_l where deleted=true;
    end loop;
END
$BODY$
LANGUAGE plpgsql;

however this end up with the error:

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

Why, is clearly explained by the code comments reported above.
This function wont close the transaction at the end of the loop.
How would it be the correct way to do it?
Thanks for any help.


texts: MySQL: Does the order of the ON condition of a LEFT JOIN matter?

texts:
I have two tables, employees and unionMembers. The people in unionMembers are a small subset of the people in employees. I want to do a SELECT query using a LEFT JOIN like so:

SELECT    wage, unionDues
FROM      employees
LEFT JOIN unionMembers ON employees.ID = unionMembers.ID
WHERE     employees.ID = 'ID001';

Does it matter if I do ON employees.ID = unionMembers.ID or reverse the order and do ON unionMembers.ID = employees.ID?

Let's say I have 100 employees and 5 of them are union members. When doing the LEFT JOIN, ideally it would have to do 5 comparisons instead of a 100. Would the order of the equality test make any difference?


texts:
I'm doing a pokemon sql table and created a new column named capacity_difference to determine the collectability of each pokemon in the table. However, when I want to use CASE query to categorize them, the outcomes only show the ELSE condition for every log. Can someone please tell me how to fix the query?
My queries are as follow:
ALTER TABLE pokemon add capacity_difference INTEGER;

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference from pokemon;

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference,
  CASE
    when capacity_difference > 90 then "collect asap"
    when  capacity_difference < 90 and capacity_difference > 50 then "good"
    when capacity_difference > 0 and capacity_difference < 50 then "okay"
    when capacity_difference > -10 and capacity_difference < 0 then "bad"
  ELSE "worse"
  END AS Collectability
FROM pokemon;


texts: fn_trace_gettable empty default trace rollover files

texts:
For some time now I've been using fn_trace_gettable to automatically collect and report certain events on the server.

Function is being called with following parameters:
::fn_trace_gettable('C:\MSSQL\Log\log.trc',0)
Which should return results from all 5 default trace files. And it used to work just fine, but recently I discovered that the script started to return empty results even though I knew for sure that those events took place.

When inspecting the cause, I discovered contents of log dir looks like that:

WriteTime    Length    Name
---------      ------    ----
2018-10-01     1 048 576 log_242.trc
2018-10-02     1 048 576 log_243.trc
2018-10-03     2 097 152 log_244.trc
2018-10-26             0 log_247.trc
2018-10-27             0 log_248.trc
2018-10-28             0 log_249.trc
2018-10-29     1 048 576 log_250.trc
2018-10-30     9 024 000 log_251.trc

A few things are wrong here:

More than standard 5 rotating files
Some files are empty
Inconsistent rollover numbering

I tested the function (0 is the same as default and should return results from selected file and all files up to the newest active):

::fn_trace_gettable('C:\MSSQL\Log\log.trc',0)
::fn_trace_gettable('C:\MSSQL\Log\log_242.trc',0)
::fn_trace_gettable('C:\MSSQL\Log\log_247.trc',0)

First two calls return results only from first 3 files. Seems like this is because there are no 245 file. Third call throws an error because the file is blank.

I solved this by deleting all trace files except most recent 250 and 251 and ::fn_trace_gettable('C:\MSSQL\Log\log.trc',0) now works as expected,  returning results from both files. New files should be created in the future with correct rollover numbers and old files should be deleted. But this situation may happen all over again.

Questions:

Why those empty files were created in the first place? No server restarts around dates when trace files were created.
Why there are no 245 and 246 files? I suspect that they were deleted as there are already 5 newer files.
Why the server lost track of old 242,243,244 files and didn't delete them upon creating new ones?
How to prevent this behaviour in the future?


texts: How to have a one-to-many relationship with a privileged child?

texts:
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as questions on this site, children as answers, and favorite as the accepted answer.) For example,

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

The way I see it, I can either add the following column to TableA:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

or the following column to TableB:

    IsFavorite    BIT NOT NULL

The problem with the first approach is that it introduces a nullable foreign key, which, I understand, is not in normalized form. The problem with the second approach is that more work needs to be done to ensure that at most one child is the favorite.

What sort of criteria should I use to determine which approach to use? Or, are there other approaches I am not considering?

I am using SQL Server 2012.


texts: A few questions regarding Sql Server Utility

texts: I just stumbled across the Sql Server Utility (don't ask) and was just wondering what the overhead of the collect and upload process is like? How does this compare with other monitoring/health tools like Idera's Sql Diagnostic Manager?


texts: Table constraint, number of row taken by a fk key

texts:
Is it posible to add a table constraint, to prevent an insert if there are already 3 row of the same fk key

My table: ProjectTitles (id(pk), project_id(fk), title_id(fk))

Check if project_id has less then 3 rows, if true insert if not show error


texts:
I have these tables:

calls: rows [ 'created_at', 'user_id']

users: rows ['id', 'username']

And I need to get all user without calls from date:
How can I get it? Or what way is better and why?

I know three ways:

first:

SELECT id
FROM users
WHERE id NOT IN (SELECT user_id FROM calls where calls.created_at >= date)

second(I think it is not right, but I am not sure):

SELECT id
FROM users
LEFT OUTER JOIN calls
   ON calls.user_id = users.id
WHERE calls.created_at >= date AND calls.user_id ISNULL

last:

SELECT id
FROM users
WHERE id NOT IN (
   SELECT user_id
   FROM calls
   INNER JOIN users AS call_users
      ON call_users.id = users.id
   WHERE calls.created_at >= date

DB: PostgreSQL


texts:
I have a large table with millions of rows. Each row has an array field tags. I also have the proper GIN index on tags.

Counting the rows that have a tag is fast (~7s):

SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY['t1']::varchar[]);

However counting the rows that don't have a tag is extremely slow (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @> ARRAY['t1']::varchar[]);

I have also tried other variants, but with the same results (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

How can I make the "not in array" operation fast?


texts: SQL AlwaysON. keeping Agent Job in sync, but with same JobID?

texts:
Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists?

I have a software from a vendor that uses the Job ID to reference jobs instead of using the Job Name. As far as I know (and in my testing) - when a job is created the Job ID is random. The vendor has a solution to rerun their Job Creation Tool when you failover. Is there a way to skip this step and make the Job ID the same between AG replicas?


texts: Data loading problem with csv file from staging server to Database

texts:
I am trying to load data into oracle database from csv file using below statement
CREATE TABLE EDWOPS.SAD_344_478_Excld_list
(
  MSISDN  VARCHAR2(1000 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY STAGING_HW_EDWOPS
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE  FIELDS TERMINATED BY ',' optionally enclosed by
        MISSING FIELD VALUES ARE NULL
        )
     LOCATION (STAGING_HW_EDWOPS:'DS_AT_47_20_AND_DS_AT_D_34_Nov_20_1_Exclude_list.csv')
  )

Error I am getting is  given below
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "missing": expecting one of: "double-quoted-string, hexprefix, single-quoted-string"
KUP-01007: at line 2 column 9

My csv data format is like
1234567
2338848
2338888
.......

There are almost 6M entry in my csv file.


texts: Running Excel Macro Code by SSIS package configured with SQL Server Job agent

texts:
I am trying to execute an Excel macro. It executes successfully on Visual Studio Data Tools 2012. But When I configured this package with SQL Server Job agent and try to execute this, it throws an error.

  Executed as user: *****. Microsoft (R) SQL Server Execute Package
  Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft
  Corporation. All rights reserved.    Started:  1:07:32 AM  Error:
  2016-05-20 01:07:32.99     Code: 0x00000001     Source: Trigger Macro
  in BAC_HE Excel      Description: Exception has been thrown by the
  target of an invocation.  End Error  DTExec: The package execution
  returned DTSER_FAILURE (1).  Started:  1:07:32 AM  Finished: 1:07:32
  AM  Elapsed:  0.297 seconds.  The package execution failed.  The step
  failed.

And Error logged in log file is

  OnError,CEAGPDS0102,*****,Trigger Macro in BAC_HE
  Excel,{1aed82a9-3da5-4044-b1f1-573385d7c8b7},{8AAF675E-DA4F-454D-8CF7-61E6B511C0AA},5/20/2016
  6:57:18 AM,5/20/2016 6:57:18 AM,1,0x,Exception has been thrown by the
  target of an invocation.


texts:
I'm trying to extract 456 from the string :123:456: as follows:

select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual

However, this query returns null. What am I doing wrong?


texts: Get Data for user excluding another user blocked with its time interval

texts:
Two tables

Message

  ID | Message | FromUserId | ToUserId  | DateCreated
    1  | M1      |     A      |   B       | 2017-10-05 12:00:00
    2  | M2      |     A      |   B       | 2017-10-05 12:05:00
    3  | M3      |     B      |   A       | 2017-10-06 12:05:00
    4  | M4      |     B      |   A       | 2017-10-06 12:05:00
    5  | M5      |     A      |   B       | 2017-10-07 12:05:00
    6  | M6      |     A      |   B       | 2017-10-07 12:05:00
    7  | M7      |     B      |   A       | 2017-10-08 12:05:00
    8  | M8      |     B      |   A       | 2017-10-08 12:15:00
    9  | M9      |     B      |   A       | 2017-10-08 12:18:00
   10  | M9      |     B      |   A       | 2017-10-09 12:05:00

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00
3  |    A       |   B         | 2017-10-09 11:55:00  | NULL

Now when B tries to retrieve messages it gets whole messages of Message table

but when A retrieves messages, its result should be

Message

ID | Message | FromUserId | ToUserId  | DateCreated
1  | M1      |     A      |   B       | 2017-10-05 12:00:00
2  | M2      |     A      |   B       | 2017-10-05 12:05:00
5  | M5      |     A      |   B       | 2017-10-07 12:05:00
6  | M6      |     A      |   B       | 2017-10-07 12:05:00
8  | M8      |     B      |   A       | 2017-10-08 12:15:00
9  | M9      |     B      |   A       | 2017-10-08 12:18:00

I tried with below query

Declare @UserId int = A
SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
    and bu.FromUserId = @UserId
  )
;

I am stuck with the condition for user being blocked and another user.

I took reference of question:get data excluding time intervals

but could not figure


texts:
I have a around 400 CSV files which have been exported from an old Paradox database, with each CSV representing a table within the old database.  Each CSV has a different number of columns and different column headings.

I'm looking for suggestions on an easy way to bulk import all of these files into SQL Server, with a new table created for each of the CSV files.  No relational structure is required.

What options do I have for quickly getting creating the 400 tables and importing the data?


texts: Delayed durability for reporting database

texts:
I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday.
The Logic in one of the SPs is that it uses alot of update statements. I can see the transaction log grows by many GBs when it runs. The database is in simple recovery mode. So my diagnoses is that all the update statements are making it slow.
My question is that will changing the setting to delayed durability have an effect on the speed of the SP?


texts: Understanding what does it mean when we say certain rule ensures conflict serializabiliy

texts:
Database book by Korth explains validation protocol as follows :

To perform the validation test, we need to know when the various phases of transactions took place. We shall, therefore, associate three different timestamps with each transaction Ti:

Start(Ti),thetimewhenTi started its execution.

Validation(Ti), the time when Ti finished its read phase and started its validation phase.

Finish(Ti),the time when Ti finished its write phase.

The validation test for transaction Ti requires that, for all transactions Tk with TS(Tk) < TS(Ti), one of the following two conditions must hold:

Finish(Tk) < Start(Ti). Since Tk completes its execution before Ti started, the serializability order is indeed maintained.

The set of data items written by Tk does not intersect with the set of data items read by Ti,andTk completes its write phase before Ti starts its validation phase (Start(Ti) < Finish(Tk) < Validation(Ti)). This condition ensures that the writes of Tk and Ti do not overlap.Since the writes of Tk do not affect the read of Ti, and since Ti cannot affect the read of Tk, the serializability order is indeed maintained.

Doubt
How second point says "writes of Tk do not affect the read of Ti"?
Start(Ti) is the timestamp when Ti started its execution.
Validation (Ti): is the timestamp when Ti finishes its read phase and starts its validation phase.
I believe that mean read phase start at Start(Ti) and ends at Validation(Ti) ?
Also Finish(Tk) is timestamp when Tk finished its write phase.
So, I believe Start(Ti) < Finish(Tk) < Validation(Ti) means write of Tk ovelaps with read of Ti and hence write of Tk can affect read of Ti.


texts:
I've designed a table as below for users who register a quiz:
CREATE TABLE `registered_quiz` (
 `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` smallint(5) unsigned NOT NULL,
 `iq_id` smallint(5) unsigned NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `is_registered` char(0) DEFAULT NULL,
 `delivered` char(0) DEFAULT NULL,
 `final_point` float NOT NULL DEFAULT '0',
 `total_rank` smallint(5) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`rq_id`),
 UNIQUE KEY `user_id_2` (`user_id`,`iq_id`),
 KEY `user_id` (`user_id`),
 KEY `iq_id` (`iq_id`),
 KEY `date` (`date`),
 CONSTRAINT `registered_quiz_ibfk_1` FOREIGN KEY (`iq_id`) REFERENCES `inja_quiz` (`iq_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `registered_quiz_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

And I have a query as below:
SELECT final_point,total_rank,rq_id FROM registered_quiz WHERE user_id=1111 AND final_point<>0 AND total_rank<>0

Do I need the part  KEY user_id (user_id) ?
I was wondering if  UNIQUE KEY `user_id_2` (`user_id`,`iq_id`) would be enough!

Do I have to index final_point and total_rank to get faster select results just because they are in the where clause?


texts: I gave 3 large Excel files need to be loaded in Single SQL table

texts:
I have 3 large Excel files each sized 56896 KB, I need to load all 3 files  into same SQL table. what is better way to handle?
since My excel has more data, my excel hang up when I open and search any data.

I tried to create a ssis package that had excel source and OLDEB destination to load into table.  But I create EXCEL source by selecting one file atleast, the Visual studio hangs up. I could not see the preview of my sheet also.

Please let me know how can we proceed? I need to load every month 3 large files to do some analysis.


texts: Unexpected behaviour with InnoDB Transaction retry

texts:
This is going to be difficult to reproduce but hopefully someone can shed some light on my problem based on the logic involved.  After having some intermittent issues with deadlocks during a transaction I implemented a retry strategy that goes goes something like this (PHP):
public function execute_query($sql) {
    $try     = 1;
    $log_msg = '';
    while (true) {
        $query     = $this->link->query($sql);
        $error_no  = $this->link->errno;
        $error_msg = $this->link->error;
        if (!$error_no){
            if ($try > 1) {
                $this->debug_log->write('Deadlock Succeeded after ' . $try . ' Tries ::: ' . $sql);
            }
            return $query;
        } else {
            $log_msg = 'Error: ' . $error_msg . ' ::: Error No: ' . $error_no . ' ::: ' . ($try > 1 ? $try . ' Tries ::: ' : '') . $sql;
            $this->debug_log->write($log_msg);
            if ($error_no == 1213 && $try < self::DEADLOCK_RETRY) {
                // retry when deadlock occurs
                sleep($try * 2);
                $try++;
            } else {
                throw new ErrorException($log_msg);
                exit();
            }
        }
    }
}

This seems to have had the exact opposite affect that I'd hoped for - which is that it appears the first half of my transaction gets rolled back but then the second half commits.
For example:
START TRANSACTION;
Query 1
Query 2
Query 3 (deadlock occurs, query gets retried and succeeds on 2nd attempt)
Query 4
Query 5
COMMIT;

At the end of all of this I'm left with Query 3, 4, and 5 running successfully but Query 1 and 2 do not.  I don't understand how this is possible but it's happened twice now and I haven't been able to reproduce the deadlock to test or develop a working strategy.
Can anyone explain why retrying a failed query within an InnoDB transaction would cause half of the transaction to get rolled back and the other half to commit?


texts: SQL Server Autonomous transaction logging

texts:
Our developer who is familiar with Oracle, has asked for the SQL Server equivalent of autonomous transaction logging. We run SQL Server 2014 and 2017.

After extensive searching, nothing really seems like the obvious choice and almost every post is for SQL Server 2008 and earlier. We would appreciate any type of help.


texts: Installing Oracle XE 11gR2 on Fedora 15 - linux group permission deadlock

texts:
During installation of Oracle XE 11gR2 on Fedora 15, I am presented with:

  The install cannot proceed because ORACLE_BASE directory (/u01/app/oracle) is not owned by "dba" group.  You must change the ownership of ORACLE_BASE directory to "dba" group and retry the installation.

This is frustrating because I have set oracle:oinstall as the owner, and dba is in the oinstall group.

When I change the owner to dba it just complains about the oracle user.

  dba gid=oinstall groups=oinstall,root,oper
  oracle gid=oinstall groups=oinstall,vboxsf,dba  (running in virtualbox)

What am I doing wrong?


texts: Simultaneous calls to the same function: how are deadlocks occurring?

texts:
My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'—a simple variant of an upsert).

My understanding of the docs is that other calls to new_customer should simply queue until all previous calls have finished:

  LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released.

Why is it sometimes deadlocking instead?

definition:

create function new_customer(secret bytea) returns integer language sql
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex')
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

error from log:

2015-07-28 08:02:58 BST DETAIL:  Process 12380 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12379.
        Process 12379 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12380.
        Process 12380: select new_customer(decode($1::text, 'hex'))
        Process 12379: select new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST HINT:  See server log for query details.
2015-07-28 08:02:58 BST CONTEXT:  SQL function "new_customer" statement 1
2015-07-28 08:02:58 BST STATEMENT:  select new_customer(decode($1::text, 'hex'))

relation:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

edit:

I've managed to get a simple-ish reproducible test case. To me this looks like a bug due to some sort of race condition.

schema:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

bash script run simultaneously in two bash sessions:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

error log (usually a handful of deadlocks over the 1000 calls):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edit 2:

@ypercube suggested a variant with the lock table outside the function:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

interestingly this eliminates the deadlocks.


texts: Replace/upgrade mirrored server hardware

texts:
We are deploying new VMs for a mirrored database that is in high safety mode with automatic failover and also going from SQL Server 2008 to SQL Server 2008R2.

Most of the documentation I've come across covers a rolling upgrade, but that only covers upgrading the SQL version, not an OS version change let alone changing servers out.

The steps as I see it are: I have to break the mirror and then recreate with the new secondary server, failover, break it again, and then recreate it with the new primary and witness.

How have you managed migrating servers and databases to new OS and new version with an existing mirror?


texts:
If I need a table to hold Point of Sales transactions, and am told I need to store:

Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields

Now, in this case, the uniqueness would be:

Country ID, Store Number, POS Terminal Number, Transaction Date, Item Code

I am always unsure if it's best to have a identity colmn as the primary key - in this case, maybe TransactionID INT NOT NULL PRIMARY KEY, and then a unique constraint across the unique fields.

Or, should a primary key be created across all the unique fields?

The benefit of the TransactionId I guess would be, joins. To join back to a transaction, you just use the single field. I can't see the benefit of the PK across a number of fields. (Save space of an extra column?).


texts: Grant Windows logins to read a secondary database of log shipping?

texts: The following link, Creating Users on secondary server in log shipping, shows how to create a database login on the secondary server. How to grant Windows logins on the secondary server to access the database? The primary server and secondary server are not in the same domain so I cannot create the Windows domain user on the Primary server.


texts:
I am about to make a reporting/alerting mechanism that will periodically examine state of one of my databases that is constantly updated and send reports/fire alerts if needed depending on the data in the tables.

I am not familiar with MS BI stack. I played a bit with reporting services but as far as I can see it can only send periodic reports. I need some quite moderately simple logic before sending a report that will decide whether the report needs to be sent at all.

Should I go with custom external solution, some core SQL Server stuff (triggers + CLR support?) or there is an existing tool in BI stack that will suit my needs?


texts: How to trim the log file that stores information about job history execution?

texts:
I was having a look at my sql server jobs and proxies on an specific server in the production environment.
I pretty much have an script to check the status of the jobs but in case I have to troubleshoot why a job failed on this particular server it has been using the following settings:

log to table
append output to existing entry in table
include step output in history

But no file has been specified, so when I click on View to check that has been going on, I get and out of memory exception, as the table is too big.

the way I find out the size of this log file (which I am not sure where is stored):
    use msdb
    go

    declare @job_id    UNIQUEIDENTIFIER
           ,@job_name  sysname

    select @job_name= N'DBA - my job name'

    exec sp_help_jobsteplog @job_name=@job_name

there is basically one for each step in the job as you can see on the picture below:

===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.Smo)

Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.JobStep.EnumLogs()
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.ReadStepLogToFile(JobStep step)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.viewTableLog_Click(Object sender, EventArgs e)
TITLE: Microsoft SQL Server Management Studio
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.Smo)

BUTTONS:
OK
My question would be:
How do I trim this log file ?
I know I could simply set the long to an external text file, but that would only be my second option.


texts: Performance: Select count(*) where... VS select column_name where

texts:
I'll keep the scenario simple:

There is an app, and there is a database.
In the database, there is a table with a large amount of records
I feed the app these records regularly

Now, one reload can imply some of these changes:
  i. New records in set
  ii. Updated records in set
  iii. Deleted records in set

Finally, for records that remain the same I can make use of a diferent process that works as much as 100 times faster than reading the table through ODBC.

Knowing this, I am trying to reduce the reloading time by using the ODBC as little as possible and midway through my algorithm I encounter a question that I have not been able to solve.

**How much faster is a select count(*) where [condition] opposed to select columnName where [condition]

Why? Because just by reloading the new and updated records, I can calculate whether there have been deletions or not by counting records depending on [condition]. Then, if there have been deletions, I'll have to read from the database those that remain. Nornally, the amount of new and updated records will be a small portion of the whole set.
If a count is considerably faster than a normal select, I might want to use it as a fast check, since deleting records is not a common activity.


texts: Should I use conventional or computed columns for trivial metrics in a fact table?

texts:
A fact table can often have measures which are basically trivial.  For example Orders.NumOrders will probably be "1" for every real record (synthetic records representing "no order" may populate this field with a zero).  Assuming that such a measure even needs to be instantiated in SQL at all, I see two ways to do so:

Include an INT column which is always populated with "1" (except for the exception described).
Include an AS 1 or AS CASE WHEN [IsARealOrder] THEN 1 ELSE 0 END calculated column.  Obviously there's no point in persisting this calculation, or you're back to #1.

The advantage of approach #1 is simplicity.  The advantage of approach #2 is self-documentation, and reduced record size.  Given that databases tend to be IO-bound, it seems that adding a trivial calculation to save four bytes x millions or billions of rows could be worthwhile.

I've used both approaches in the past, but have not done any profiling.  Are there any important reasons to favor one over the other?

Kimball's toolkit implies method #1, but does not address the matter specifically.


texts: SQL Server - How to get corrupted rows id

texts:
I have a corrupted SQL Server database.

Running command

dbcc checkdb repair_allow_data_loss

tells me there would be data loss.

Error:

  Msg 8964, Sev 16, State 1, Line 1
  Table error: Object ID 165575628, index ID 1, partition ID 72057594120568832, alloc unit ID 72057594040483840 (type LOB data). The off-row data node at page
  (1:269), slot 4, text ID 1116865298432 is not referenced. [SQLSTATE 42000]

How can I get the corrupted rows id before to run that command?

Thanks


texts: Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?

texts:
I am using a MySQL database and perform many select like these:

SELECT MyField FROM MyTable WHERE MyKey = 'some_value';

The purpose is to retrieve a field a value if the corresponding row exists. MyKey is the only key in the table and it is indexed.

My question is: is there any benefit or drawback to adding LIMIT 0,1 to my statement?


texts: Primary and secondary replicas on same server

texts:
We're considering different options for HA/DR with SQL Server 2016.
AlwaysOn Availability Groups are obviously an option.

I understand we can read from secondary replicas, unlike DB mirroring for example.

Can we spread the write load during normal operations, by using each server in the cluster to be a primary database/replica for some databases, and a secondary database/replica for others?

Lets say we have 2 servers, sql01 and sql02, and 10 databases to protect.
Can we set this up:

sql01 holds db01-db05 as primary, db06-db10 as secondary.
sql02 holds db06-db10 as primary, db01-db05 as secondary.

During failover, each server can take over as primary for all DBs.

Are there additional options when using AlwaysOn to spread write operations?


texts:
I am trying to connect to a SQL2005 running on a Windows 2003 Server from a C# application running on Windows 2016, and am having trouble. The connection uses OLE DB, not ODBC.

When I try to connect, the application throws an exception. The same exception is thrown when I try to connect using PowerShell:

  Exception calling "Open" with "0" argument(s): "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

Background

This is required as part of a migration project to get my clients off the unsupported, legacy server. The plan was to move to the new version of the application, and then move to the new SQL server.

Constraints

Moving the data to another server before moving the application will be difficult.

There is no test environment: I'm working directly on production servers (I know), so I need to be very careful about what I try next.

What I've Tried Already

The first issue was incompatible TLS versions, but this has (I believe) been resolved by a colleague, who disabled newer SSL versions on the Windows 2016 server. We'll switch them back on once the migration is complete, but in the mean time we're not worried about the security implications of doing this.

I've tried adding a SyncAttackProtect key to the registry on the Windows 2003 server, but this had no effect.

I can connect to a SQL Server database on a SQL2008 server from the Windows 2016 server that won't connect to the SQL2005 server.

I can connect to both the SQL2005 server and SQL2008 servers from my Windows 7 PC.

What I Need

Suggestions on what I can look at next.


texts: How to import DB with .sql extension into SQL Server 2012?

texts:
I have a database with .sql extension which is exported from server using putty.

How to import this into SQL Server 2012 in Management Studio?

It is not accepting .sql as database, throwing error.

If I try to restore using

RESTORE HEADERONLY FROM DISK = N'D:\LogFile\accesslog.sql';

I am getting the following error:

  Msg 262, Level 14, State 1, Line 1
  CREATE DATABASE permission denied in database 'master'.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE HEADERONLY is terminating abnormally.

Please help me with this.


texts:
Looking for a advice on table(s) structure for MySQL schema.

Background:
I have several tables that can hold information about my product, customers and their purchases using CC/DC/direct banking etc. It's all working fine so far. Now, I'm planning to offer my customers an option to purchase store credit(using existing payment methods) and accumulate promotional credit by referring friends or using coupons while purchasing credit. Characteristics of credit include:

every credit has an accumulation date
every credit can be used to purchase products (unless expired)
purchased credit will not expire
promotional credit will have an expiry date

I already have a table for offers/coupons, their validity, benefits etc. to be able to offer any promotional credit.

Goals:

A table structure that can hold both type of credits(purchased as
well as promotional). I thought of this - see image.

A way to automatically deduct credits with closest expiry date first and then deduct from next accumulation if short of credits from one accumulation and so on...

Example: Customer has 50 purchased credits(no expiry) and has 15 (promo) credits expiring on 25th Sept. and another 20 (promo) credits expiring on 29th Sept. Now, let's say customer wants to buy a product worth 20 credits from the store. So, instead of deducting all 20 from his second promo accumulation, I'd want to deduct 15 credits that expire earliest from today and then remaining 5 from the ones expiring later (and of course not touching the purchased 50 credit yet).

Another table to hold all transactions from credits (kind of a
ledger)

Any ideas on this plan? I'd also appreciate any complex SQL statements involved for insert/fetch/update operations on the advised plan. I can myself take care of basic to moderate SQL statements (though I'm not very good at GROUP BY and UNION).

I'll give more information, if required.


texts: Connecting dimension tables in a multi-fact star schema database warehouse?

texts:
I am new to data warehouse modelling so please bear with me.
In database warehouse modelling, the star schema is typically a fact table with multiple dimensions connected directly to it. However, what if there are two or more fact tables in the schema and some of the dimensions are related? Please see below as an example:

Is it allow to draw a line to relate the dimension tables in star schema database warehouse modelling? For example, assume dim_a is hotel_rooms and dim_d is customers, can I draw the line between the two table in the star schema since a hotel room has many customers staying?
In all the examples I searched online, the star schema is always connected to dimension tables and these tables are not drawn with a line to connect it.


texts: filter out the records with same id having different values in a column

texts:
Can anyone help me to solution for below issue.

create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,87214,1),
(357491,87214,1),
(343980,87216,1),
(357483,87216,1);

create table [Status1] (Type VARCHAR(100), Status_Id int);
insert into [Status1] values
('New','1'),
('Awaiting Approval','8'),
('New','1'),
('New','1'),
('New','1'),
('New','1');

    select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
--AND B.Type = 'New'
order by A.CartTransaction_Id

Below was the output I am  getting from above query.

Below was the expected result

I need to populate the data only if status type was 'New' for all order_id's in group of CartTransaction_Id

From the giving I need to filter out CartTransaction_Id (87214) as one of the order_id status_type was on Awaiting Approval


texts: MYSQL Created_tmp_disk_tables value increases on almost every query

texts:
Should I be concerned that tmp_disk_table # is growing even when I run a simple query like elect id from table with 1000 rows where id is an integer and is primary key)

I've fiddled with other settings (bumped max_tmp_table memory,max_heap_table_size, etc. bumping them from 128mb to 1 gb etc.) but that number of tmp_disk_tables keeps growing

This doesn't seem right to me but a DBA at my company says it's normal.
Here is what I am using to check that number:

We are running MYSQL 5.5.44-0ubuntu0.12.04.1 on a lunix server:

  SHOW GLOBAL STATUS LIKE 'created_tmp%tables';

Variable_name, Value
'Created_tmp_disk_tables', '1524870'
'Created_tmp_tables', '23686492'

CREATE TABLE mdl_course (
    id bigint(10) NOT NULL AUTO_INCREMENT,
    category bigint(10) NOT NULL DEFAULT '0',
    - other columns removed for formatting issues -
    PRIMARY KEY (id),
    KEY mdl_cour_cat_ix (category) USING BTREE,
    KEY mdl_cour_idn_ix (idnumber) USING BTREE,
    KEY mdl_cour_sho_ix (shortname) USING BTREE,
    KEY mdl_cour_sor_ix (sortorder)
) ENGINE=InnoDB AUTO_INCREMENT=2916
          DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
          COMMENT='Central course table'


texts: should I give dbo permissions to NT AUTHORITY\NETWORK SERVICE?

texts:
We're in the process of assessing a new application that will require access to our MSSQL database

I've set up a user that is a member of a role only giving it readonly access to certain views in the database and the application is fine using this user to access our database.

However it says I still have one thing left to do and that's to give NT AUTHORITY\NETWORK SERVICE dbo permissions on the same database

At the moment this is only a test environment but it will need rolling out onto our live system if we choose to use the application

Am I right in saying it would be insecure to give dbo permissions to this service account?

please let me know if there's anything I'm misunderstanding about the service accounts


texts: SSRS View Report: Change View Report Button Text

texts: Is there anyway to change the View Report Button in SSRS to say something else. Such as "Hello", or "See Data". Is there a place in the SSRS XML RDL, I can edit?


texts:
I've created an extended event to debug a very rare issue in a web application. Very occasionally data from forms is not making it to a stored procedure, so I want to verify that when the SP is being called, it is actually lacking the missing data. I'm using to Extended Event to view the passed parameters.

I went in today to check the logs, and found that the Event has not been capturing data, and indeed, the session was stopped in SSMS.

How do I ensure this even doesn't turn itself off? I've set it to start on server startup. Here is text of the event:

CREATE EVENT SESSION [DB_Name SP execution] ON SERVER

ADD EVENT sqlserver.rpc_starting
(
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34))
),

ADD EVENT sqlserver.sp_statement_starting
(
    SET collect_statement=(1)
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34)
    )
),

ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34)
    )
)

ADD TARGET package0.event_file
(
    SET filename=N'D:\path\to\log.xel',
    max_file_size=(5120)
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)

GO

Edit
I started it when I set it up, and it captured data for two days (8/9 - 8/11). Haven't checked in again until today, found it was off and there has been no data since 8/11. It is currently on and capturing data, but I don't want to have to manually check that it's on every day.


texts: get base urls with different "endings" after the last slash

texts:
Okay, so have a wonderful script that checks the "base" url up to a certain number of slashes, but realized what I really need is checking the "ending" going backwards to the first non-escaped URL slash, and providing the prefixes are identical, display them.

I.e., let's say this is my data:

CREATE TABLE `testtable` (
  `field1` int(11) NOT NULL,
  `field2` datetime NOT NULL,
  `urlTest` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `testtable` (`field1`, `field2`, `urlTest`) VALUES
(1, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1'),
(2, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/'),
(3, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata'),
(4, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata/dada'),
(5, '2010-01-01', 'http://test1.com/singleprefix/randomdata'),
(6, '2012-02-02', 'http://test1.com/newscript/something'),
(7, '2013-02-02', 'http://test2.com/newscript/something'),
(8, '2014-02-02', 'http://test3.com/newscript/something'),
(9, '2014-02-02', 'http://test3.com/');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`field1`);

so - the script should find identical "base" urls with different endings, based on the last "slash" in the URL.

So in this case,

http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1
http://test1.com/identicalprefix/anotherprefix/
http://test1.com/identicalprefix/anotherprefix/randomdata

would both be listed, because they have the same "base" URL (http://test1.com/identicalprefix/anotherprefix/), just with different suffixes, and same dates.

and obviously, "test3.com" would not display, because even though it shares a common base url, and common date - it is not the "same" entire base URL? (ie., "http://test3.com/" versus "http://test3.com/newscript/"


texts:
When I check for price tags for MySQL, I see a clause called "1-4 socket server" or "5+ Socket Server".. what does this mean?

"Socket" in generic term means Inter Communication Protocol. For MySQL, a socket unix file helps to communicate between MySQL server and MySQL client within local server, and there is only one as far as I know per MySQL server instance.

Please correct me if my understanding is wrong. Kindly shed some light what is meant by 4, 5+ sockets as I see cost increases as socket numbers rises.  Also, what are the advantages of the higher sockets?


texts: Is it possible to join MySQL 8.0 to a cluster of MySQL 5.7

texts: I have a MySQL 5.7 (5.7.19-17-57-log Percona XtraDB Cluster) cluster using Galera, but I am in the middle of upgrading to 8.0 -- 8.0.23-14 Percona Server (GPL), Release '14'.  I wasn't sure if it was possible to join the 8.0 box to the clustered 5.7.


texts: What configurations do I need to fix the "unable to begin a distributed transaction" error when trying to run a remote procedure?

texts:
This is the error message I'm receiving:

  Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1]
  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" was unable to begin a distributed transaction.

As a test my stored procedure query is just SELECT 1 AS A and works locally on the server but doesn't work when I call it remotely on a linked server.


texts: How does mysql(innoDB) handle same foreign key for join

texts:
I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key value.

I want to know if MySQL will look up the Y table every time in select using a join even when the value is the same, or does it not have to search the new table a second time when I do the select?


texts: Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

texts:
I created a Extended Event to monitor and tune a Selective Xml Index

CREATE EVENT SESSION [SelectiveXmlIndexing] ON SERVER
ADD EVENT sqlserver.selective_xml_index_no_compatible_sql_type,
ADD EVENT sqlserver.selective_xml_index_no_compatible_xsd_types,
ADD EVENT sqlserver.selective_xml_index_path_not_indexed,
ADD EVENT sqlserver.selective_xml_index_path_not_supported
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

In SSMS when I view live data and then execute the xml queries and views, no results are being returned.

All i see is:

Is there another step I need to acomplish before seeing the results?

I followed:

http://www.mssqltips.com/sqlservertip/2731/managing-sql-server-extended-events-in-management-studio/

http://www.sqlskills.com/blogs/bobb/choosing-paths-for-selective-xml-index-part-2-using-the-sxi-xevents/

http://www.sqlskills.com/blogs/jonathan/sql-server-2012-extended-events-update-3-viewing-target-data/

Edit,

Adding requested Query and Index

Query

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

SELECT

    x.XmlId

    ,a.value('@id', 'int') As ColumnA
    ,b.value('.', 'NVARCHAR(25)') ColumnB

        From
    dbo.XmlTable As X

    Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
    Cross Apply a.nodes('tst:Forms') bb(b)

And Index for the root element and attribute

CREATE SELECTIVE XML INDEX [xsi_XmlTable] ON [dbo].[XmlTable]
(
    [XmlColumn]
)
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/tst.xsd'
)

FOR
(
[1] = '/Root/Stuff' as XQUERY 'node()',
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO

I ultimately want to index the element and the text() of the Forms Element, but the below executes, but is not getting indexed for paths 3 and 4.

[1] = '/Root/Stuff' as XQUERY 'node()',
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON ,
[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms/text()' as SQL [nvarchar](25) SINGLETON

Edit: The below will not work either because I have the same path included and receive the error

Paths with same path expression indexed with selective XML index 'xsi_XmlTable' should have SINGLETON option either specified for all of them or for none of them.

So I am trying to test which will work better if I remove Singleton

[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms' as SQL [nvarchar](25) SINGLETON

Edit, was able to return some results after removing the join from the below query.  I am not sure why the join was not allowing it to return results, but just running the straight xquery I was able to return the results but path 3 and 4 i still not getting indexed.

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

    SELECT

         x.XmlId
        ,c.Content

        ,a.value('@id', 'int') As ColumnA
        ,b.value('.', 'NVARCHAR(25)') ColumnB

            From
        dbo.XmlTable As X

        Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
        Cross Apply a.nodes('tst:Forms') bb(b)

        Inner Join TableC c on c.id = a.value('@id', 'int')

Here is a simple Xml Sample

<tst:Root xmlns:tst="http://tempuri.org/tst.xsd" SchemaVersion="0.1">
  <tst:Stuff id="1">
    <tst:Forms>Sample1</tst:Forms>
  </tst:Stuff>
</tst:Root>


texts: Join two tables brings mismatch values MYSQL

texts:
I have two tables with >4million records i need to make a select query with where two columns match bring both tables value on this match and then i will insert that into a 3rth table:
This is table A: (bitfinex)

This is table B: (Kraken)

I need to do a  SELECT where timestamp and exchange_pair matches, as you can see in this image from table B the same timestamp can have multiple exchange_pair, what i need to do is match those with the other table columns and bring both table content

this was the query i used at once but when analyzing the results i had some mismatch values on the joins ( i did not found the relation, it was not either timestamp or the exchange pair) i wonder if my query syntax is right for the result im looking for.
INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
JOIN kraken AS K2
 ON B.exchange_pair = K2.exchange_pair
WHERE K.exchange_pair = B.exchange_pair AND B.timestamp = K.timestamp ;

im still confused if using inner join or just join, also i added the "Where" clause to make sure exchange pair and timestamp matches.


texts: How to improve performance of a query with an IN subquery

texts:
Get the count of other users having the x tag with order_id in order_id of users having the y tag

table: MyCustomTable (200K rows)

order_id
user_id
type ('x' and 'y') my custom tags

and the query:

SELECT   COUNT(order_id),user_id
FROM     MyCustomTable
WHERE    type='x'
AND      order_id IN (SELECT order_id
                      FROM   MyCustomTable
                      WHERE  type   ='y'
                      AND    user_id='56')
AND      user_id <> '56'
GROUP BY user_id

Create table output

CREATE TABLE `MyCustomTable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `type` enum('x','y') NOT NULL DEFAULT 'x',
 PRIMARY KEY (`id`),
 KEY `customeKey` (`user_id`,`type`,`order_id`),
) ENGINE=InnoDB AUTO_INCREMENT=360806 DEFAULT CHARSET=utf8


texts: How to get TNS connect strings (like tnsping) to check a dblink via a session?

texts:
I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server.

I'm connected to a schema on a database located on any server.
Now, I have some database links, using tnsnames and which are currently not working properly.
I would like to check, which target database is behind this identifier to verify easily, if this can be correct.

If I would have OS access to the db-server, I would be able to read the ./tnsnames.ora or using tnsping.

But the DDL of the dblink just uses the TNSNAME (RDBX in this example):

  CREATE DATABASE LINK "ABC"
     CONNECT TO "RUSER" IDENTIFIED BY VALUES ':1'
     USING 'RDBX';

Of course, I could ask a DBA, but this is not always as easy in our environment and I would like to be able to double-check it myself without having OS-access.


texts: SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect

texts:
When opening Sql Server Management Studio I'm getting the following error message:

Then when I have a look at the Event Viewer the following message is displayed related to this:

Activation context generation failed for "c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe". Dependent Assembly debuggerproxy.dll,processorArchitecture="X86",type="win32",version="1.0.0.0" could not be found. Please use sxstrace.exe for detailed diagnosis.

Also the Installer won't work.

Any solution for this please ?

Thanks


texts:
I am not able to UPDATE table pg_database with user postgres. As you can see below, i am able to run the SQL query, however nothing changes.

postgres=# select datname, datdba, datistemplate, datallowconn from pg_database;
    datname     | datdba | datistemplate | datallowconn
----------------+--------+---------------+--------------
 postgres       |     10 | f             | t
 template1      |     10 | t             | t
 template0      |     10 | t             | f
 my_template_1  |     10 | f             | t
(4 rows)

postgres=# UPDATE pg_database set datistemplate=true, datallowconn=false where datname='my_template_1' ;
UPDATE 1
postgres=# select datname, datdba, datistemplate, datallowconn from pg_database;
    datname     | datdba | datistemplate | datallowconn
----------------+--------+---------------+--------------
 postgres       |     10 | f             | t
 template1      |     10 | t             | t
 template0      |     10 | t             | f
 my_template_1  |     10 | f             | t
(4 rows)

postgres=#


texts: Error while importing a ".sql" file in database

texts:
I am trying to import a .sql file in to my local database. whenever i type the command to import the file, I get an error ERROR at line 1: Unknown command '\S'.

This is the first line of the sql file update product set aka = 'Trojan.gen,Backdoor.Win32.Agent.bisn' where id = 156701


texts: Select for indexed field with >= is much faster than with =

texts:
Execution time for the query

SELECT id
FROM Deals
WHERE is_demo = 0
AND asset_id >= 71
AND exp_time >= UNIX_TIMESTAMP(UTC_TIMESTAMP() - INTERVAL 30 HOUR);

is 0.5s, but if we change asset_id>=71 to asset_id=71 the execution time is about 10 seconds.

Why >=71 if much faster than simple equal operation =71?

Indexes for all fields are used.

CREATE TABLE `Deals`
(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    is_demo TINYINT(1) NOT NULL,
    asset_id INT(11) NOT NULL,
    exp_time INT(11) NOT NULL,
);
CREATE INDEX asset_idx ON `Deal` (asset_id);
CREATE INDEX exp_timex ON `Deal` (exp_time);
CREATE INDEX is_demo ON `Deal` (is_demo);
CREATE INDEX user_idx ON `Deal` (user_id);


texts: SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

texts:
I'm trying to use SSIS (Visual Studio 2017) to insert data into an Azure SQL table with a column that's encrypted via Always Encrypted, and a column master key stored in Azure Key Vault, but I keep getting errors.

Per advice I've found in other posts, I'm using an ADO.NET destination with the ODBC data provider. The user is an Azure Active Directory user that's provisioned in the Azure SQL database so it's able to log in. It has all key privileges granted to it in the key vault's access policy.

My ODBC connection string looks like this:

DRIVER=ODBC Driver 17 for SQL Server;SERVER=[dbs].database.windows.net;UID=[uid@example.com];PWD=[password];Authentication=ActiveDirectoryPassword;DATABASE=[database];ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultPassword;KeyStorePrincipalId=[uid@example.com];KeyStoreSecret=[password]

I expect that to insert into the table, but it results in this error:

[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [CE269] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error 12038 sending request to https://[keyvault].vault.azure.net:443
ERROR [CE263] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error verifying signature of ECEK.
ERROR [CE202] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The keystore provider AZURE_KEY_VAULT failed to decrypt the ECEK https://[keyvault].vault.azure.net:443/keys/[cmk]/[version] with RSA_OAEP.

I'm able to successfully insert into the table via SSMS and also via a custom console app, and verify the encryption works as expected. The error only occurs from SSIS.


texts:
I'm helping a friend figure out some SQL code, but can't figure out why it isn't working like it should.

I have come up with many solutions that work, but I want to know why this one isn't working.

Any help would be appreciated.

SELECT test_id,
    question_id
FROM test
WHERE test_id = 6117
GROUP BY test_id
HAVING question_id = MAX(question_id);

He wants to get the max question id for a specific test.  I don't really understand why he wanted to do it this way - something along the lines of performance and functions and subquery.  So I'm not sure on that part, though it sounds like it will be used with other SQL code.

If you remove the where clause it works for all test ids, but when you try to specify the test id it isn't working.


texts:
I am encountering a tough problem when doing data conversion. My work is to generate fact table in ssis 2005, and there are several data that should be converted to other data type, so I use data conversion to change the data type to expected. My work flow is:
raw data-->dim lookup-->data conversion-->destination OLEDB

All process goes well while stopped in data conversion, and the error is " The conversion returned status value 2 and status text  "The value could not be converted because of a potential loss of data.".

Some said the reason maybe there are NULLs in raw data, so I've changed the NULLs to UNEXPECTED, so no NULLs in raw data, but the same error also exists.


texts:
I have gone through the definition on http://en.wikipedia.org/wiki/Database_engine several times:

  A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

What I do not understand is what is left to do, isn't CRUD all that the databases do?

If the database engine performs these functions, what does the rest of the database do?


texts: Preventing PostgreSQL from starting on boot in Ubuntu

texts:
I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu).

How can I avoid this?

I need to start it manually, and I don't want it to start unattended because I need to look at the console.


texts:
I have a replica set on MongoDB with 3 members - primary, secondary and arbiter. One day I came and saw that primary and secondary have switched. I'd like to find a reason for the failover that caused the elections. Please advise me how and where I can find what caused it. I looked in the logs and I found only this:

  2015-03-03T12:18:41.540-0500 [rsHealthPoll] replset info
  my_server:27017 thinks that we are down 2015-03-03T12:18:41.540-0500
  [rsHealthPoll] replSet member my_another_server:27017 is now in state
  PRIMARY 2015-03-03T12:18:41.540-0500 [rsMgr] stepping down; another
  primary was elected more recently

Thanks


texts:
I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:

MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen.
sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL.
SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows.

Anyone have suggestions?


texts: My Ola Hallengren nightly backup job failed but It works fine when I execute the job manually by right clicking

texts:
I have been using Ola Hallengreen maintenance plan . It has been working great for longtime  . Recently one of my nightly backup job failed repeatedly with an error:

  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in opening server connection

I then execute the job manually by right clicking on it and start at step... and it runs fine. No problem.

The server is SQL Server 2008R2.

Does any one know why the job failed?


texts: Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter

texts:
I am bit confused on checking the metrics for REDO Rate KB/Sec from Always on AG dashboard, which for some scenarios matches with perfmon counter Database replica : Redone Bytes/Sec ( hopefully it's the correct counter for redo rate via perfmon), and sometimes not at all.
Most of the times when there is lot of activity ongoing, my belief is that AG dashboard and DMV shows REDO rate for e.g. 40 MB/Secs at given time which matches with perfmon counter.
However, during less activity time or nothing much to send over to sec, REDO Rate on Dashboard and dmv seems to be showing incorrect values compared to perfmon counter.
Not able to understand which value is correct and how to analyze. Any idea why or is it bug in dashboard?
Screenshot as requested:

No transactions occurred around that time - no major activity on primary end. I am collecting that perfmon on secondary and primary since after failover new secondary we would still need those counters running. However the data pulled in screenshot perfmon data is from secondary.


texts: Get into SQL Server 2008 with no management studio and no sqlcmd

texts: I got office program. This program stores his data in SQL Server 2008 Express, but there is no management studio and no sqlcmd. How can i get into pure SQL tables, to use them in my plugin.


texts: Many-to-one Subselection in single query

texts:
I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.

To create a simple example, lets say T1 is Cars, and T2 is a table of imperfections. So, a car can have 0..N imperfections, and we store these imperfections in T2 as integers.

I would like to select * from only those cars in Cars that contain imperfections i1 AND i2.

Performing an OR instead is pretty easy:

SELECT * FROM cars AS T1
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

I've been trying set logic using intersection, but at this point, I'm wondering if I'm over complicating it.


texts: Is it any potential problem with having 2 constraints suppored by one index in Oracle?

texts:
I have the following table :

CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id);
ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id)
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id)
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
-- I think the line below is irrelevant in the context of my question, but
-- in case I'm wrong...
ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id)
REFERENCES action_type(action_type_id);
-- Since action_type is a lookup added for normalization sake with no updates/deletes,
-- I don't see any value in having an index on FK column.

It's a common parent table for many detail tables that store extra information for a particular action. action_type_id is a part of unique constraint to let detail tables have FK to (action_id,action_type_id) and to enforce that each detail table stores only information it's supposed to store.

I wonder if there is any performance penalty for using the same index in 2 constraints compared to creating 2 indexes... In my opinion it should behave better than 2 indexes, but maybe I'm missing something. I'm using Oracle 10g if that matters.

Thank you for your answers.


texts: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

texts:
I have a job that runs an SSIS package.

Currently the owner is a proxy account. I can run the job manually from a sys-admin account.

Our web service logs in using a limited account. It needs to execute the job.
Currently it is unable to see the job at all(when I try to execute by name, it says it doesn't exist).

I tried changing to owner of the job to the limited account. Now it could see the job, but the job failed execution because it can't run the SSIS package anymore.

There has to be a way to allow the limited account to run a job owned by another account right?


texts: Create two tables with identical superkeys?

texts:
I have an existing table called Realms.  This has the superkey rlmID (just a surrogate key).  We are planning to add tables that group realms in some context -- let's just calll them RealmGroups.  This will require adding a realmGroupID column to Realms.  No problemo.

Not all Realms will be part of RealmGroups, so Realms.realmGroupID would be nullable.  In this case, is it better to have a separate table (RealmsRealmGroups) that has (rlmID,realmGroupID)?  Since the superkey of Realms and RealmsRealmGroups would be the same, I see no advantage to creating a separate table, but my DBA is suggesting that we should use a separate one for organizational purposes.

Are there any technical or practical reasons to use a separate table to store information for the same superkey?


texts: How to determine if Oracle Java classes are being used

texts:
Oracle EE 12.1.0.2

As part of a general clean up of a legacy application prior to a migration I'm trying to determine what objects are actually in use.

There are some 230 JAVA class objects in my database.  I can look at DBA_DEPENDENCIES to see where the dependencies between the java classes exist.  I can look at DBA_SOURCE to find what packages  include the phrase 'AS LANGUAGE JAVA' to find packages that wrap JAVA classes. I am auditing the execution of all the packages owned by our application schema.  But as far as I know you can't audit what procedures are called inside of a package.

Is there a way to audit the execution of the java class itself?  Can JAVA classes be called without being wrapped in a PL/SQL procedure?

I'm not a JAVA programmer (if that isn't obvious).


texts: Copying data factory datasets and working with JSON files

texts:
Is it just me or is data factory absolutely hopeless? I am working with the V1 version of the product and it is so clunky, it's ridiculous.

For instance, we have set up a pipeline which transfers about 20 tables in AWS into Azure. Sounds great. But the setup of all the JSON script takes an age, you have to hand craft all the JSON and insert all the input and output datasets. You then have to handcraft a pipeline activity. Doesn't sound too bad, until you do it and it takes you DAYS to do it.

Now we have to change the frequency of the extract. Sounds simple right? Just update the schedule on the pipeline.  No no no... first you must delete the pipeline. Then you must manually change each datasets "availability" property manually. But you can't because that's not supported. Instead you have to delete each dataset, then recreate.

All just for changing the frequency of the extract! Any other property change is likely to require a full reload again which will take you hours.

Is there a simple way to work with these JSON files I am missing? Some sort of interface other than the Azure portal which makes managing the JSON easier?


texts: Is that possible to group result on 3 months period basis?

texts:
I am trying to group results by 3 months period starting by the current month as shown below:

row1      15 -- This should contain November, September and October
row2      25 -- This should contain August, July and June
row3      5  -- This should contain May, April and March
row4      2  -- This is should contain February and Janvier

I have no idea about how to accomplish this. Any help please?

So far I can group by month:

SELECT MONTH(date), MONTHNAME(date) as month, COUNT(*)
FROM table_name
WHERE MONTH(date) < NOW()
GROUP BY MONTH(date)
ORDER BY MONTH(date) DESC


texts: Looking for a database that supports fulltext but does not lock table on insert or update

texts:
I have a program that needs to insert and update a table very frequently (200+ insert or update queries per minute), and this table should also support fulltext search.

I am currently using MySQL MyISAM, but it will lock the table when some data is inserted. So the fulltext queries go in a queue, slowing them down and eating a lot of memory.

What can I do to get both fulltext indexing and no table locks on inserts or updates?

Database fields:

id | title |  content | url | date | image | source | imagesource


texts: mysql - unique index with nulls - actual solution anyone?

texts:
CREATE TABLE IF NOT EXISTS b2c_constants (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    is_deleted BOOL DEFAULT FALSE,
    UNIQUE (name)
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci

CREATE TABLE IF NOT EXISTS b2c_constant_bindings (
    constant_id INT UNSIGNED NOT NULL,
    company_id INT UNSIGNED NOT NULL,
    object_id INT UNSIGNED DEFAULT NULL,
    property_id INT UNSIGNED DEFAULT NULL,
    value VARCHAR(255) NOT NULL,
    UNIQUE (constant_id, company_id, object_id, property_id),
    FOREIGN KEY (constant_id) REFERENCES b2c_constants (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (object_id) REFERENCES b2b_objects (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (property_id) REFERENCES b2b_properties (id) ON UPDATE RESTRICT ON DELETE CASCADE
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci

The problem is with the unique key in the bindings table. If I have data like:

constant_id   company_id   object_id   property_id   value
1             1            null        null          foo
1             1            1           null          bar
1             1            1           1             baz

You can duplicate the first two rows without any errors infinitely, which is obviously not desirable.

The idea with this structure is to allow to bind constants globally per company, per object in company, and per property in company, but allow only unique constants for each of those.

Is there a way to solve this in the database while keeping the foreign keys and a simple table structure? I know that I could do type ENUM ('company', 'object', 'property'), type_id INT, but with that I lose the foreign keys as well as the required company ID for all constants.


texts: Sum of column per distinct ID and group by different column

texts:
Let's say I have the following table:
create table orders (id int, order_id int, vendor varchar, price int, quantity int);

INSERT INTO orders values
(1, 1, 'nike', 10, 2),
(2, 2, 'nike', 10, 3),
(3, 3, 'adidas', 15, 1),
(4, 3, 'adidas', 15, 5);

I want to get the sum of the price per distinct order_id and total sum of the quantity grouped by vendor. So the answer here would be:
| vendor | sum_price | sum_quantity |
|  nike  | 20        |      5       |
| adidas | 15        |      6       |

Notice how the sum of the price for 'adidas' is only 15, because they both have the same order_id, so it'll only take one. In all cases, the one to choose whenever multiple order_id's are the same doesn't matter because it will always be associated with the same price.
Is it also possible to do this without a nested query?


texts: Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb

texts:
Downloaded a v12 SQL Azure bacpac, and trying to import it into a SQL Server 2014 LocalDB... I get:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not load schema model from package. (Microsoft.SqlServer.Dac)

------------------------------
ADDITIONAL INFORMATION:

Internal Error. The database platform service with type     Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid.     You must make sure the service is loaded, or you must provide the full type name of a valid database platform service. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
BUTTONS:

OK
------------------------------

I have tried installing the Data-Tier Framework Jan 2016 Preview which did not make any difference (both x64 and x86).

Any suggestions?


texts: Aggregating data - Getting data inserted after last time query was run

texts:
On a SQL Server 2008 R2 DB, I'm running an aggregation query every hour or so, on data that's being constantly inserted into a table, and what I'd like to do is pull all the "new" records that were inserted, since the last time that query ran. An identity column can generally do this, since you can assume it's monotonically increasing, but according to this post (https://stackoverflow.com/questions/2828936/does-sql-server-guarantee-sequential-inserting-of-an-identity-column), you're not necessarily guaranteed that when you do the pull, that you've pulled every record before the record with the greatest id.

Question is, how can I do this? A timestamp with default value seems reasonable, but also seems to fail in my mind for similar reasons. Why? Well if the default value of a time column is [CurrentTime], then could it not happen that with parallel inserts, insert #1 has a greater timestamp than insert #2, if insert #1 is slightly delayed? I guess it really depends on when the time is pulled vs when the row is inserted: something which I have no insight into.

The only solution I can fathom that will avoid this issue is if I pull all records from [LastTimeJobRan] - [SomeConstantOffset] up to [CurrentTime] - [SomeConstantOffset]. In other words, assume I run the job at 1:05, I would then pull all records from 12:00 - 1:00, hoping that 5 minutes would be enough of a window to avoid any "stalls" from inserting (obviously a few seconds would suffice here methinks). From a high level, I'm basically staggering/delaying processing of the latest records that are currently being inserted.

The other solution I'm thinking of involves a combination of storing the time of the last run, and marking records as being "processed". The last time run (offset by a bit as well perhaps) is just an optimization to avoid pulling a lot of old rows that have already been marked.

Am I overthinking this problem? Are there guarantees that the DB makes that obviate this entire line of thought?

I'm sure this is a common enough problem, but I couldn't find much relating to this besides the above SO post. Perhaps I'm using the wrong keywords.


texts: After moving a DB to a new disk, the log file isn't dumping data to the DB

texts:
I recently added a new disk to our SQL Server box and followed these steps from Microsoft to move the .mdf file to the new drive. I opted to leave the log file on the original disk for performance reasons.

When I tried to bring the database back online, I was met with a systems permissions exception (admin access was needed). I went into Windows Services and changed SQL Server to log on as the local system account. This appeared to have resolved the problem.

However, two days later, the .mdf file is the same size as pre-transfer (I took it offline and only the timestamp changed). I checked the log, which is usually under 1GB, and found that it has now reached 30GB.

Am I missing something? Why hasn't SQL Server written any of this new data to the DB file itself?


texts: How to find archivelog gaps in the backups

texts:
I manage Oracle databases that are backed up using RMAN. How can I find gaps in the backed-up archivelogs?

I.e., I want to detect when something like this happens:

Take full backup with archivelogs
Back up morning archivelogs and delete input
Back up evening archivelogs and delete input
Someone permanently deletes the backupsets containing the morning archivelogs
Crosscheck so RMAN knows some backupsets have gone missing

Short of doing or simulating a full restore and recovery, how can I detect that there is a gap? How can I see that backupsets are missing that would be needed for full recovery?


texts: Why do I get ORA-27102 ON WINDOWS 64 BIT if I increase the memory to 2.5GB

texts:
BANNER

  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
  Production PL/SQL Release 11.2.0.2.0 - Production CORE    11.2.0.2.0
  Production TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production   Windows Server 2008 R2
  Enterprise
  6.00 GB RAM 64-bit operating system

Current memory settings

*.memory_max_target=1536M
*.memory_target=1024M

When I try to increase the memory_max_target to 2.5 GB I get the below error on startup

  ORA-27102: out of memory OSD-00022: additional error information
  O/S-Error: (OS 1450) Insufficient system resources exist to complete
  the requested

  SQL> startup
  pfile='C:\oracle\app\product\11.2.0\dbhome_1\database\INITorcl.ORA';

Could some one shed some light.


texts: Can I set scale factor to the value that is not listed in manual when using DBGEN to generate TPCH dataset?

texts:
Certain scale factors are given in DBGEN's manual, including 1, 10, 100, 300, 1000, 3000, 10000, 30000, 100000. Manual declares that only these sf values are compliant.
-s --scale factor. TPC-H runs are only compliant when run against SF's
                    of 1, 10, 100, 300, 1000, 3000, 10000, 30000, 100000

Can I set sf to some other values? Such as 200,230,370 etc. I have tried sf=50 and got dataset. But I do not know what is the influence using SF NOT COMPLIANT


texts: DBCC CHECKDB fails after restore to SQL Server 2017

texts:
I have a DB on SQL Server 2014 and after I restore a backup on SQL Server 2017 I get integrity check errors when running CHECKDB. When I restore it on another machine with SQL Server 2014 it works without CHECKDB reporting errors. I need to migrate the database to newer servers, so I can still update things as I see fit.

CHECKDB errors:

Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1737) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1738) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1739) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1740) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1741) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1742) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1743) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 7 allocation errors and 0 consistency errors in database 'DBNAME'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBNAME).


texts:
I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results:

SELECT elements.id, [ ... ],
(SELECT
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0)
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b`
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = elements.id)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

The problem I'm having is that the second nested subquery (the one in the left join) is unable to find the column elements.id from the initial select.

From what I've found searching around SQL only passes values one level deep, and I've been unable to find a suitable work-around.

Is it possible to force SQL to pass a value deeper than one level? Or is there a way of modifying the query to not use another subquery, but still get the same result?

Sorry if I'm doing anything stupid or missed anything obvious, SQL is not my strong point!


texts: MS Access .mdb & .ldb database corrupted

texts:
I am using a A.mdb database. You know when connecting the database Microsoft automatically create a .ldb file. Unfortunately my pc shutdown when connecting the database. As a result after restart the PC I couldn't open the A.mdb file. The message shown A.mdb database is corrupted.

A.mdb database was password lock but after corrupted if I give the valid password the database not open and showing the password not valid.

Now I couldn't open the database. Now it shown A.mdb file with A.ldb file. Please advise me if PC shutdown with the connected .mdb database then why the database not recover after restart? Also i tried with the option Database Repair and Recovery.


texts: How long does a failover for SQL Server Availability Groups take?

texts:
In trying to determine the most appropriate High Availability option we are focused on reducing downtime (either planned or unplanned).   I've been able to gather stats (from reading lots of MSDN documentation and blogs) on Failover Cluster Instances (FCI).
What I haven't found is documentation around the failover stats/times of Availability Groups.
As a comparison (FCI):
Failing over a 2008R2 cluster can range from 30 seconds to 5 minutes (depending on traffic volumes as well as hardware/network setup):

When doing a manual failover, it is completing its writes to the LUNs,
switching the LUNs to the new Active node and starting up the SQL
Server instance on the new Active node.
When doing an automatic
failover, after starting up the node it will do a consistency check on
the databases and roll back any in-flight transactions.

Availability Groups
I understand that Availability Groups should be able to fail over quicker, when you compare the same hardware and traffic volumes.
What I haven't been able to find is any real world actual metrics comparing the two.
Specifically does anyone have any metrics on how long it takes to fail over the primary write node in an Availability Group that is being actively used?
(fail over to a synchronous secondary)
Ideally this would include references to any Microsoft or trustworthy sources.
Not opinion based please, only metrics.


texts: Grouping count by interval of 15 minutes

texts:
I was trying to write a query where I can group the data by 15 minutes interval.

Example:

I want to count the orders by interval.
So Output should be:

How can this be written?


texts: Query across multiple schemas, unknown until execution time

texts:
I have multiple schemas that I need to query across. The schemas are not known in advance, for example:

U111
U222
U333

I need to select across these three schemas from a specific table in each of these schemas. Can I put this query across these three schemas? If so, how can I query across them? Remember, I will not know the schema names ahead of time, so they will need to be built dynamically.


texts:
I've imported data into a new database (about 600m rows of timestamp, integer, double).
I then created some indexes and tried to alter some columns (got some out of space issues), the database is vacuumed.

Now pgAdmin III tells me that the "Size of temporary files" is 50G~+.

What are these temporary files? are these like SQL Server transaction log?
How can I get rid of them, it seems the database is much bigger than it should (the total size of the database is 91 GB)

Using Posgres 9.4.1 on a Windows 2012 server.

A screenshot of the database statistics tab:


texts:
I have a table representing files and the time spans they cover like so:

filename  |     start_time      |      end_time       | format |
----------+---------------------+---------------------+--------|
file1.tar | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | raw    |
file2.nc  | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | nc     |
file3.raw | 2017-07-19 06:00:36 | 2017-07-19 06:01:44 | raw    |
file4.tar | 2018-08-20 00:00:01 | 2018-08-20 23:59:59 | raw    |
file5.cdf | 2018-08-20 00:00:01 | 2018-08-20 11:59:59 | cdf    |

I'm trying to find the timespans for which we have files in raw format, but no file in a non-raw format (basically, any format value except "raw").

Edit:

Given the example data above, the result would look like this:

     start_time     |       end_time      |
--------------------+---------------------+
2017-07-19 06:00:36 | 2017-07-19 06:01:44 |
2018-08-20 12:00:00 | 2018-08-20 23:59:59 |

Here is the logic behind this:

The timeframe from raw file1 is entirely covered by non-raw file2, so
do not include it
The timeframe from raw file3 has no non-raw files, so include it entirely
The timeframe from raw file4 is partially covered by non-raw file 5, so include the uncovered portion

This is a small part of a large hairy query (involving versions, soft-delete status, location, and etc) that I'm trying to narrow down to just the part I'm having trouble with so please let me know if anything else seems strange or unclear.

From the comments:

There are ~29 million rows in the source table
I've removed to version information to make it more clear what the problem is
The format column will be either 'raw' or some other string. For the purposes of the question, we only care about raw or not-raw
The filename will not always contain 'raw' but the format column will for raw files
A result of tsranges is also fine, maybe even preferable

Edit:

This is pretty close, and produces the two "not-fully-covered" rows, but produces the whole time span from 2018-08-20 when I want only the 12 uncovered hours.

with base as ( --create tsranges and collapse the formats
    select filename,
           case when format = 'raw' then 'raw' else 'not_raw' end as format,
           tsrange(start_time, end_time, '[)') as time_span
    from datafiles
), a as ( --compute the running max of the upper bound
    select time_span,
           format,
           coalesce(lower(time_span), '-infinity') as range_start,
           max(coalesce(upper(time_span), 'infinity')) over (partition by format order by time_span) as range_end
    from base
), b as ( --if the previous range_end is earlier than the range_start, there is a gap, start a new range
    select *, lag(range_end) over (partition by format order by time_span) < range_start or null as next_range_start from a
), c as ( --tag group numbers by counting how many next_range_starts have passed
    select *, count(next_range_start) over (partition by format order by time_span) as grp from b
), d as ( --group by group number, make the final range from the highest and lowest in each range
    select format,
        tsrange(min(range_start), max(range_end)) as time_span
    from c
    group by format, grp
)
select * from d raw
where format = 'raw'
and not exists (
    select 1 from d not_raw
    where format = 'not_raw'
    and raw.time_span <@ not_raw.time_span
)


texts: Script to drop all databases except one not working

texts:
I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error:

EXEC sp_MSforeachdb
'
USE [?]
IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'')
BEGIN
ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ''?''
END
'

Here is the error message:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'master'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'tempdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'model'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'msdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ABC'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IMMEDIATE'.

Where should I correct here?
Thank you


texts: Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?

texts:
I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).

An example is the following function:

create function my_table_delete(_identifier int) returns setof int as
$BODY$
begin
    return query
    delete from    my_table
    where          identifier = _identifier
    returning      identifier;
end
$BODY$
language plpgsql volatile strict;

This function accepts an identifier, deletes the corresponding row and returns the same identifier in case the delete was successful.

I am quite unsure if I should implement this as a set-returning function (like I did above) or a scalar function.

My reasoning for preferring set-returning function is the following:

In case of a scalar function, we will have a NULL return value in case the row could not be found (while we will have 0 rows in case of the set-returning function). Having to deal with NULLs is a pain.
I'm working with sets (SQL is all about sets), so it makes sense to just keep doing that.
Also, I prefer returning the identifier (1 row: successful, 0 rows: not successful) instead of a boolean (true: successful, false: not successful), as it would allow constructs such as:

with d(id) as
(
    select * from my_table_delete(1234)
)
insert into log_table(id, action)
select      d.id, 'DELETE'
from        d;

If I were to return a boolean, the statement above could become less elegant.

I'm interested whether those arguments make sense. I'm quite new to the embedding logic into the database, so I'm not sure if I am addressing this correctly.

What do you think?

On a slightly related note, I also wonder if I should define the function as following:

create function my_table_delete(_identifier int)
    returns table (identifier int)
as ...

This gives the output row's column a name, which is even more convenient to use.


texts: Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute

texts:
I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:

SELECT
  (SELECT lead_id FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_id,
  (SELECT CONCAT(first_name, " ", last_name) FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_name
FROM
  text_messages t
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx"
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC;

That query right there takes on average 613ms to execute.

I have indicies on from_id, to_id, sent_to_number, and sent_from_number.

Why is this query taking so long to execute?

EDIT:

Added in EXPLAIN EXTENDED:

+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
| id | select_type        | table | partitions | type        | possible_keys                      | key                   | key_len | ref  | rows | filtered | Extra                                                                            |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
|  1 | PRIMARY            | t     | NULL       | index_merge | organization,from_number,to_number | to_number,from_number | 62,62   | NULL |   83 |   100.00 | Using union(to_number,from_number); Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
|  2 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+

Edit 2:

Server Spec:
Cloud SQL - MySQL 5.7 - 1 CPU - 3.75 GB RAM - 100 GB SSD


texts: Repair Database using .FRM .IBD .OPT Files ( Hard copy mysql folder )

texts:
I had a crash on my server.
The only thing I could do was hard copy the mysql folder
Now I am trying to access my database again through the PHPMyAdmin interface.
Some databases (mostly WordPress) run and run well
But some databases are only viewed and their content is not displayed
For example

I used REPAIR TABLE query but it doesn't help


texts: Error 9002. The transaction log for database is full due to 'NOTHING'

texts:
In the SQL Server Error Log (and Windows Event Viewer) I found the error message:

  The transaction log for database [...] is full due to 'NOTHING'.

The database is in SIMPLE recovery model.
Its log has 126 VLFs.
It is a user database, not a system one.

What does a reason of "NOTHING" mean?

If there is no reason which prevents the log from being reused, then why does this error message exist?


texts: does DBCC ShrinkDatabase do a Reorganize of files?

texts:
does DBCC ShrinkDatabase doa  Reorganize of files?

Basically does it mimic the same process as if i checked Reorganize in the GUI?