texts: Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

texts:
Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc
  0000:8d8f7911) started at Aug 14 2018  6:14PM in database 'cola' at
  LSN (2599205:10500:111) failed. The statement has been terminated. Msg
  596, Level 21, State 1, Line 4 Cannot continue the execution because
  the session is in the kill state. Msg 0, Level 20, State 0, Line 4 A
  severe error occurred on the current command.  The results, if any,
  should be discarded.

While moving some indexes to a different filegroup, and at the same time applying compression= PAGE, I get the error message above.

This is an example of one of my statements:

        CREATE NONCLUSTERED INDEX idx_applicationID_activityDate
    ON [dbo].[tbl_applicant_activity]
    (  [applicationID] ASC  , [activityDate] ASC  )
    INCLUDE ( [activityTypeID] , [applicantID])
    WITH (  PAD_INDEX = OFF, FILLFACTOR = 100,
 SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
    DROP_EXISTING = ON,DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

It is too late in the day today, but for tomorrow, out of working hours my plan is to try to run these statements in batches and find the culprit, if any.

The database is part of an AG.

QUESTION:

What does this message really mean, and how does it interfere in my index creation?


texts: How to generate time in milliseconds in PostgreSQL stored procedure?

texts:
I have been reading the Postgres documentation about timestamps, but I am still a little bit confused.

I need to store moments in times, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC (like those defined in Java, but I am not using Java).

If I read the documentation properly, I believe I have to use CURRENT_TIMESTAMP, because it includes the timezone. Correct?

Can someone explain how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?

Motivation: I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows, but this conversion would be more expensive than storing the time in milliseconds. I am ready to store my moments into a bigint. It would be faster to compare moments or to compute time differences.


texts: Calculating the value of a column based on consecutive values of rows in another column

texts:
It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on consecutive rows values in other columns?

My database consists of points location coordinates (consecutive points), and for each point, I want to determine that point's bearing change from reference (true north). Here's more detail about the question:

TABLE:
+-----+-----+---------+
| lat | lon | bearing |
+-----+-----+---------+
| x0  | y0  |   b1    |
| x1  | y1  |         |
| x2  | y2  |         |
+-----+-----+---------+

Now I want to compute the bearing column(e.g. b1). From my research, bearing between two points is determined this way:

Then bearing B of (x1,y1) from (x0,y0) is calculated as:

B = arctan(b,a)          .......(1)

where

b = sin[y1 - y0] * cosx1 .....(2)

a = cosx0* sinx1 - sinx0 * cosx1 * [y1 - y0]   ......(3)

Thus, to compute the bearing of row1 in the above table (b1), both  quantities a and b in equations 2-3 require some value from row2 in the table i.e. x1,y1, and addition to x0, y0

This is the computation I would like to do on the database, I have the latitude and longitude columns for the consecutive geolocations stored. I created this fiddle so need to alter the table by adding a 5th column for bearing, but I am not sure how I can use values from next row for the current row in the bearing column.


texts: How does combining columns save disk space?

texts:
I am creating a table in SQL server and inserting records. Three columns from this table are AAA int, AAB int and AAC int. If I merge AAB and AAC into one column Comb1 char(2), it consumes 20% less disk space compared with three original columns. But if I merge all three into one column Comb2 char(3), it still saves 20% disk space.

Can anyone explain this? And does the machine sort a combined column faster than sorting two columns independently?

Many thanks!


texts: Remove the last character from Variable String

texts:
I have a few variable data in the column "email".

For example:

roy2087@gmail.com123
sg123@yahoo.compori

How do I get rid of the characters after .com?

I used Replace(email,'.com%',gmail.com), however, we cannot use wildcard characters in Replace.

Any Suggestions?


texts: MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

texts:
I'm not sure whether this belongs to SE DBA, but it sounds more logical ...

Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses utf8_bin for all of their tables. This has been since start.

I read utf8_bin is (at least in theory) faster since no conversion and/or normalization is done, but are these quite different UTF8 thingies fully compatible ? If they are, is there performance hit or improvement ? The tables I'm planning switching from utf8_unicode_ci to utf8_bin contain integers and dates, so not having to bother with much larger UTF8 should drop at least 1ms when dealing with these large ASCII-only tables.

Before I had all tables not already using UTF8 Unicode Ci or UTF8 Binary I was using ASCII Binary for these tables, but decided to switch to UTF8 Unicode Ci for full compatiblity with Server collation (and of course a bit of performance improvement).


texts: Recursive self relational via join column

texts:
Having this specific simple many to many self referential structure

An item owns other items through the joins table;
trying to retrieve the whole tree structure as json while querying a given ite.

For example, while querying the item with id 1 :

SELECT it.*, fulltree from items where id = 1;

Outputting the following :

{
  id: 1,
  title: "PARENT",
  children: [
   {
     id: 2,
     title: "LEVEL 2",
     children: [
      {
        id: 3,
        title: "LEVEL 3.1",
        children: [
          {
            id: 4,
            title: "LEVEL 4.1"
          },
          {
            id: 5,
            title: "LEVEL 4.2"
          }
        ]
      },
      {
        id: 6,
        title: "LEVEL 3.2"
      }
     ]
   }
  ]
}

I've digged into json capabilities postgres offers, managed such output by repeating nested query (simple but ugly, and limitated to the amount of repeats :/)

I've found out about recursive queries. The examples found here and there not being that simple, I'm having had times finding an entrypoint to understand it correctly and adapt those to my needs

Example Data

CREATE TABLE items (
    id    serial primary key,
    title VARCHAR(255)
);

insert into items (title) values ('PARENT');
insert into items (title) values ('LEVEL 2');
insert into items (title) values ('LEVEL 3.1');
insert into items (title) values ('LEVEL 4.1');
insert into items (title) values ('LEVEL 4.2');
insert into items (title) values ('LEVEL 3.2');

CREATE TABLE joins (
  id serial primary key,
  item_id INT,
  child_id INT
);

insert into joins (item_id, child_id) values (1,2);
insert into joins (item_id, child_id) values (2,3);
insert into joins (item_id, child_id) values (3,4);
insert into joins (item_id, child_id) values (3,5);
insert into joins (item_id, child_id) values (2,6);

I hope the example here will be simple enough to find help from experienced users; in advanced thx a lot


texts: Postgres FATAL: could not open relation mapping file

texts:
I installed postgress.app on mac os
when I try to connect to console :
psql -h localhost
I got this problem .

  psql -h localhost psql: FATAL:  could not open relation mapping file
  "global/pg_filenode.map": No such file or directory

Any ideas how to solve this ?


texts: What are the different ways to keep track of active and archived data?

texts:
I'm looking for different ways to keep track of both active and archived data so I can pro and con them.

The system:
I have a computer with a database on it. The database has several tables in it; one of which contains a list of users that can use the computer; and several tables for auditing (user 1 did this, user 2 did that, etc). This database is a slave of a master database in which a Content Management System is used to say, add a new user and see reports on what user did what.

Example:
As stated above, I have a table (lets call it users) that keeps track of all the users that are allowed to use the computer. As time goes by users will be added and removed. The problem is the audit tables keep track of a user ID so if the user is removed I lose the user information because the rows can't be joined. One idea I had was to use MySql's triggers so that if a user is added, an insert trigger is triggered and inserts a copy of the data to an 'archived' user table (lets call it users_archive). That way the computer can use users to determine if the user has permission to use it and reports can use users_archive for reports.

This seems like the easiest and most simple way to do it, but I can't find any other ways via google search to see if there are any other ways to do something like this.


texts: How to grant permissions on a table-valued function

texts:
Am I doing it right...?

I have a function that returns money...

CREATE FUNCTION functionName( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  RETURNS money AS BEGIN

  DECLARE @v_dint money   set @v_dint = computation_here
     set @v_dint = round(@v_dint, 2)

  RETURN @v_dint
END
GO
Grant execute on functionName to another_user
Go

Im just wondering if this is possible to be converted to iTVF?

I've tried doing this but I got an error:

CREATE FUNCTION functionName ( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )
RETURNS TABLE AS
RETURN SELECT returnMoney = computation_here
GO
Grant execute on functionName to another_user  Go

ERROR:

  Msg 4606, Level 16, State 1, Line 2
      Granted or revoked privilege EXECUTE is not compatible with object.

This function is used like this:

update table_name set interest = functionName(col1,col2...) where...

Thanks in advance!


texts: SQL Server Agent Job Next Run DateTime Stuck In The Past

texts:
The job is enabled, the schedule is enabled, the agent service is running, but the job is not running as it is supposed to. Looking in the Job Activity Monitor, it shows a Next Run that is actually in the past.

Running the job manually will cause the Next Run to advance to the correct scheduled time for a new run, but when the time comes, the job still won't run.

How is this odd issue fixed?


texts: Load balancer to distribute READ and WRITE queries to SLAVE or MASTER instance respectively

texts:
Currently we keep two different connections to master and slave in our project code and send queries to specific one depending on whether it is a read-only or a data modification query.

Is there a load balancer which distributes queries based on R/W criterion automatically between PostgreSQL cluster nodes (including functions)?


texts: How can I get the actual data size per row in a SQL Server table?

texts:
I found this script
sql-server-2005-reaching-table-row-size-limit
that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 (whatever MS recommends)


texts: MySQL: Unique constraint on large column

texts:
I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.

MySQL appears to enforce constraints using an index. In InnoDB, index sizes appear to be limited to 767 bytes - not nearly enough for the VARCHAR(3071) column that is holding the data.

Any thoughts on how to have the database enforce uniqueness of the data, without compromising on maximum data length or the usage of InnoDB?


texts: Insert into select into multiple related tables using INSERT_IDENTITY

texts:
Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source.
So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...

If I were to do this...

INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable

I'd get the ID of the last inserted record into Table1.

Is a CURSOR or WHILE loop the only ways to do this?


texts: The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package

texts:
I have a remote access in SSMS, when I try to execute an SSIS package I get, the operation can not be started by an account that uses SQL Server Authentication

How to fixed it?

Note: I can not use Windows Authentication remotely.


texts: Is this substring_index behaviour documented or a bug?


texts: How well, if at all, does Oracle NoSQL database integrate with Oracle RDBMS?

texts: I currently have an application which runs on Oracle RDBMS and an area of new functionality which would be better supported by a NoSQL store.  I am wondering if Oracle NoSQL Database may be a good place to store this information and more concretely, can a single query span Oracle NoSQL and Oracle RDBMS.


texts:
Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.


texts: Backing up and restoring all databases in MySQL on linux

texts:
I plan to dump all databases on one computer and restore them on another using the following 2 commands:-

mysqldump -u username -p --all-databases > dump.sql

mysql -u username -p < dump.sql

My query is : will the second command overwrite an existing table with the same name as on the first computer ? Also, will the second command delete all tables which are NOT there on the first computer?

Can someone please clarify?


texts: (mysql) Database replication between 2 DB while 3rd DB as backup

texts:
I am not an advanced DB admin or designer. I am sure there are articles on this kind of scenario, probably too many. So I got confused and not sure which solution is appropriate/best for me.

I am setting up two DB on different region. Users will access/write to the nearest one. So these 2 DB should be 100% identical. Any insert, update, delete on a DB replicate same on the other instantly.
(better if table structure on a DB can also alter other DB table)

See the attached diagram. Clients can use any of these DBs.

There is the 3rd DB. Clients won't ever access it or write to it.
It's for backup purposes only.
Any changes but "delete" from other DB will be replicate to this one.

Any suggestion?


texts:
My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?

The query:

select TIME_FORMAT(start_time,'%H:%i') start_time,
       TIME_FORMAT(end_time,'%H:%i') end_time,
       rule_id, day_of_week, long_message,
       short_message, rotation_weight, mode
from timed_delivery_messages order by rule_id asc;

Explain Plan:

+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | timed_delivery_messages | index | NULL          | PRIMARY | 4       | NULL |   22 |       |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

Table:

mysql> show create table timed_delivery_messages\G
*************************** 1. row ***************************
       Table: timed_delivery_messages
Create Table: CREATE TABLE `timed_delivery_messages` (
  `row_create` datetime DEFAULT NULL,
  `row_mod` datetime DEFAULT NULL,
  `rule_id` int(11) NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `day_of_week` varchar(7) DEFAULT NULL,
  `rotation_weight` varchar(50) DEFAULT NULL,
  `mode` varchar(10) DEFAULT 'active',
  `long_message` varchar(256) DEFAULT NULL,
  `short_message` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


texts: PL/SQL block fails in job but succeeds in SQL*Plus

texts:
I created a PL/SQL block which gathers certain data from tables in my Oracle 12c database and writes them into a .csv file.
I cannot share the whole PL/SQL block but can outline the code

--create directory
str := q'{create directory DIR as '/tmp/tmp_dir'}';
execute immediate (str);

--get filename
select 'tmp_stat' || '.csv'
into filename
from dual;

--open file
tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);

--write header
UTL_FILE.put_line (tmp_file, 'col1;col2');

-- gather and write data
for stat in ([select statement]) LOOP
UTL_FILE.put_line (tmp_file, stat.dat);
END LOOP;
--close file
UTL_FILE.fclose (tmp_file);

When I run my code in SQL*Plus it works fine. But when I turn it into a job with the following options

Raise Events: None
Maximum Run Duration (minutes): None
Priority: Medium
Schedule Limit (minutes): None
Maximum Runs: None
Maximum Failures: None
Job Weight: 1
Instance Stickiness: TRUE

When I try to run the job with the exact same code (except the / to mark the end of the block), I get the error

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation ORA-06512: at line 61

I don't have a line 536 in my code, but line 61 is

tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);

Any suggestions on why the error is raised when executing the code is a job?


texts: Can I skip major versions in pg_upgradecluster?

texts: I'm upgrading postgresql 9.3 to 9.5 on ubuntu/xenial and I would like to know if it is possible to run pg_upgradecluster to go directly from 9.3 to 9.5 or whether I need to go through 9.4 in between?


texts: Are there any professional fulltime PostgreSQL DBA's?

texts:
For my job I work on a JavaEE application with PostgreSQL as the database. Although we have a sysadmin for our productions servers, who also manages our database servers, we have no full-time DBA which makes me wonder if there are any. I would imagine any full-time dedicated DBA would work exclusively with Oracle database. Am I overlooking something or am I correct in assuming that there are no dedicated Postgres DBA's?

PS: I'm just asking this out of sheer curiosity.

PPS: I wanted to tag this question with DBA but apparently that would be a new tag. Could someone make this for me?


texts: Shrink configuration & content database in Sharepoint DBs

texts:
There was some argument between me and a college about for how we should shrink DBs. We knew that shrinking should be manual and only if required.

We run in a case that configuration DBs reached to 10 GB. My debate question here is shrinking to 10 mb is good choice , or leave it to 1 GB.

Thank you.


texts:
I have a table of Users:

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

and Levels

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

And I'm looking for a query to get the level for each user. Something along the lines of:

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

Such that the results would be:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Does anyone have any ideas or suggestions on how could I do this without resorting to cursors?


texts:
I need to launch a scheduled job at 12 o'clock at night and keep this job running for a period of 8 hours. What happens now is that the job is running for more than 12 hours and I really need to avoid running for so long. I have used the Max Run Duration option but it does not have the desired effect for my purpose. I would appreciate if someone could give me an example of how it should be. Database is Oracle 12c. Here I put the script:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.POB_TB_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2019/02/10 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SELLER.FILL_TB_S1'
      ,comments        => NULL
    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'MAX_RUN_DURATION'
     ,value     => TO_DSINTERVAL('+000 05:00:00'));

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'number_of_arguments'
     ,value     => 4);

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 1
     ,argument_value       => '30');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 2
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 3
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 4
     ,argument_value       => '1');

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.POB_TB_JOB');
END;
/


texts: Read access to a login in MS SQL schemas

texts: We have a MS SQL Server login and created users belonging to that login in several databases. We want to grant read access to all tables in the databases to these users. What query can we run to grant the read access?


texts: How can I *actually* defragment my InnoDB tables?

texts:
After a recommendation by mysqltuner I've run mysqlcheck --optimize --all-databases successfully (all of the tables reported "note : Table does not support optimize, doing recreate + analyze instead" and "status   : OK"), but my tables are still fragmented. I ran this query:

SELECT engine,
       table_name,
       ROUND(data_length/1024/1024) AS data_length,
       ROUND(index_length/1024/1024) AS index_length,
       ROUND(data_free/1024/1024) AS data_free
  FROM information_schema.tables;

and found that about half of my tables are still fragmented. Several of them are even more than 100% fragmented (i.e., data_free > data_length + index_length). Why would they still be fragmented, and how can I ensure defragmentation without recreating the entire database?

ibdata1 is just 82 MB (the entire DB is > 100 GB), and I've got innodb_file_per_table = on set. I also tried ALTER TABLE schema_name.table_name ENGINE=InnoDB; on one table to check whether mysqlcheck was at fault, but this didn't change the file size noticeably.

Running MariaDB 5.5.


texts: I need to replicate data from a DB2 database into a SQL Server Database (along the lines of about 80 tables). I can create an SSIS package, to just flat copy the data from one to another pretty easy, but the amount of records is a couple million, so doing it once takes a while, but for the initial setup its fine. However, I need to do "almost" real-time updates, so this method won't be viable. What would be the best method, and how, to do just add change and delete replication from DB2 to SQL Server?


texts: Simple Implementation of SQL Server 2008 Encryption

texts:
I am looking at using some of SQL Server 2008's encryption.

I have read the following article on MSDN:

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Q1a: Is the master key password created per DB instance?

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
GO

Q1b: If I create a master key password on Server1 and encrypt a column on a table. When I backup that DB (.bak) will I be able to restore the DB to another Server2 or will I have to create a master key on Server2 with the same password?

Q2: Do I need to backup the certificate, master key or symmetric key if I want to restore to another server?

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

Q3: When should the symmetric key be opened? Should it be done before each select and then closed? i.e. opened at the start of a stored procedure and closed afterwards.

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

Q4: Should I worry about who can open and close the symmetric key? I use SQL Server 2008 for ASP.NET web applications. The DB's are hosted on Windows Server 2008 VPS's. I generally create one DB user of type SysAdmin and supply the connection details from the web.config file. I would be the only person with access to the VPS so I do not need to factor in other developers accessing the DB. However, if the VPS/Windows security was breached and an attacker gained access to the VPS and/or DB. Could they compromise my DB and information?


texts:
PostgreSQL has a feature to create a snapshot for a transaction using:

SET TRANSACTION SNAPSHOT snapshot_id

Does SQL Server have a similar feature?

I have an application program that uses a SQL Server backend.  It invokes two read-only queries Q1 and Q2 using separate connections.  This means they do not execute in the same transaction.  I need to ensure both connections see the database in the same precise state, as would be afforded via the SET TRANSACTION SNAPSHOT statement in PostgreSQL.

How can I ensure both Q1 and Q2 see the database in a consistent state, identical to both connections?


texts: Transaction log space used in percent - how to clear or empty

texts:
I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications.
So I tried below:
select * from sys.dm_db_log_space_usage;
update dbo.A
set col1 = 'hello'
where id < 500000;
select * from sys.dm_db_log_space_usage;

So now the log file used is 1.8%. How can I clear it? I thought when the transaction is committed it resets to "zero" (and by "it" I mean percent of transaction log used). I have tried `checkpoint' but that did not help.


texts: Where are sql profiler trace files stored?

texts:
Can't seem to find an answer to this..

During a recent sql profile I noticed C:\ was rapidly filling up.

Does anyone know exactly where these files are stored? is it part of SSMS?


texts: Any impact of upgrade db2 from V10.5 to V11.5?

texts:
Good day,

Due to the DB2 v10.5 will end of support on next year April. I would like to change my application database version to v11.5.

Before apply this to production, I do it in my local VM first. The following are the steps I have done:

backup original database.
Install DB2 v11.5, because its same environment, do I cant have same user call db2inst1, so I create another instance call db2inst2.
log in as db2inst2, run restore command to restore the image file I done in step 1 :

db2 restore database mydb from /home/db2inst2 on /home/db2inst2

After this, I configure my Jboss server to point to this new database, application can log in and working fine.

I am thinking that, is there anything I need to take care? or anything I need to avoid ? I am worry that there are something issue unforeseen will happen.


texts: Failed to read from defunct connection (Jupyter notebook, python driver )

texts:
I try to import data into a Neo4j VM in Azure.
This code works:
def create_article(tx):
    tx.run("CREATE (a:ARTICLE)")

session.read_transaction(create_article)

But this code doesn't work:
def create_node_article(tx, id, title, label):
    tx.run("CREATE (a:ARTICLE {id:$id, title:$title, label:$label})", id=id, title=title, label=label)

for index, row in df_article_ids.iterrows():
    session.read_transaction(create_node_article, row['id'], row['cleaned_best_title'], row['label'])

I have the error:

Transaction failed and will be retried in 1.0608892687544587s (Failed
to read from defunct connection Address(host='IP', port=7687)
(Address(host='IP', port=7687)))

I don't know what I have to change or check. I also tried Neo4j Desktop and I have the same error.
Neo4j version: 4.1.3


texts: Mariadb 10.1.13 - log positions similar but "seconds behind master" is behind

texts:
The setup I have is a master/slave replication, both running Mariadb 10.1.13.

On the slave I noticed that the Seconds_Behind_Master parameter is showing a delay due to some form of lagging, but the values for Master_Log_File and Relay_Master_Log_File are the same.

I have checked that the server IDs are different, however both have the same gtid_domain_id.

               Slave_IO_State: Waiting for master to send event
                  Master_Host: X.X.X.X
                  Master_User: repl
                  Master_Port: 3321
                Connect_Retry: 60
              Master_Log_File: binary_.005825
          Read_Master_Log_Pos: 140625151
               Relay_Log_File: mysql20-relay-bin.000145
                Relay_Log_Pos: 138885256
        Relay_Master_Log_File: binary_.005825
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 138884970
              Relay_Log_Space: 1214370105
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 10056
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 21
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-20-34183339144
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative

Any advice on solving the lag between my master/slave setup would be appreciated.
What could the root cause be?


texts: Hourly differential backups with append option

texts: With simple recovery mode, a nightly full backup, and hourly differential backups, if the hourly differential backup is set to "append" will the differential file continue to grow day after day after day, or does the nightly full backup cause the very next differential backup file to be truncated so that the append starts anew with a "clean slate"?


texts: SQL Server 2008 R2 with huge amount of transactions

texts:
I am currently developing a project with a huge amount of database transactions.

My estimate is around 70,000 inserts/updates/deletes/selects every 2 minutes, since I am not there yet, I can only estimate, but in the near future, it'll get there.

Since I am not a DBA, I'd like to hear your opinion.

I am running on a Windows Server 2008 R2 and I thought I can use SQL Server 2008 R2, the questions is, how many processors and what type exactly of SQL Server I need to use? there are plenty like Enterprise, Standard, Web Edition, etc.

Any suggestions folks?


texts: Postgres: "Pivot" unioned table based on one column

texts:
I have a query that does this sort of thing:

(SELECT rel_id, timestmap, y FROM table_1 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

(SELECT rel_id, timestamp, y FROM table_2 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

...

Which results in this form:

rel_id   timestamp    y
1        2013-01-01   a
1        2013-01-02   b
1        2013-01-03   c
1        2013-01-04   d
2        2013-01-01   e
2        2013-01-04   f
2        2013-01-06   g

What I'm doing here is directly selecting from partition tables, which are partitioned based on the ID of a related table (so, the first part selects from table_1, and that is all the rows that have a relationship to another table's row with ID 1, and so on). This works very well, and is fast (and avoids having to get the query planner to figure out which partition it should be looking at).

The problem is, to process it (using python pandas library), I need it in this form:

timestamp   1    2
2013-01-01  a    e
2013-01-02  b    null
2013-01-03  c    null
2013-01-04  d    f
2013-01-06  null g

I know it is possible to select the y column of each sub query as the rel_id (so as to make the name of the column the same as that of the relationship ID) - e.g. SELECT timestamp, y AS 1... - but I don't know how to 'merge' the resulting series of timestamp/values together in this way so that there are null values for a series that is missing a value at a given timestamp index.

The Pandas library can do this really easily, but it is pretty slow (slower than fetching the data in the first place), so I want to see if it is faster to make use of postgres's power...

Is this possible?
Is it likely to be fast?
Is it likely to be faster than processing in Python (my feeling is it will be)?


texts: SQL Server Always On or Mirroring & Log Shipping

texts:
We have two data centers with a 2 ms ping time between them connected by a site to site VPN. In the primary data center we have 2 servers, in the secondary data center we have a single database server. All server are identical in spec and configuration running Windows 2012 R2 and we have two SQL Server 2016 Enterprise licences. One for the main site + fail over and one for the secondary site.

The question is what technology should we use? always on availability groups with synchronous replication between the two nodes in the main data center and asynchronous replication to the secondary data center. The other option is to use mirroring in the main data center and then log shipping to the secondary data center. The only caveat is that we need to use transparent data encryption (TDE) hence the enterprise licences.

Our ops team is currently 2 people and growing but no fully assigned DBA as yet, I know how to setup both technologies but have only used mirroring and log shipping in production.

What would people recommend, the always on seems great but I have read some articles saying you need a lot of DBA resource to look after it where mirroring and log shipping is a lot easier.


texts: Unable to connect to pluggable database using TNS but EZCONNECT is OK

texts:
I installed oracle 12c pn windows following the defaults settings... I end up with ORCL and ORCLPDB...

I can connect to ORCLPDB using EZCONNECT via CONN system/password@//localhost:1521/ORCLPDB . Then I added a service name in my tnsnames.ora like this :

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

But when I try connect system/password@ORCLPDB I get the following error :

  ORA-12154: TNS:could not resolve the connect identifier specified

This is strange because lsnrctl services has the following entry :

  Service "orclpdb" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:0 refused:0 state:ready
           LOCAL SERVER

What should I do ?


texts: MySql - Changing expire_logs_days without restarting the server

texts:
I'm using MySql 5.5.
Is it possible to change expire_logs_days and have the changes take effect without restarting the server?


texts: MS sql query value one table but not another join table where*

texts:
I have [tbl_AllStudentsEmail] where I get my students distribution list.
I also have [tbl_CourseID] where some students are registered (not all).
Students record are registered here along with the COURSEID.

Conditions: A student can be registered in several courses.

My goal is that I want to select all the students who are not registered to the course ID.  How do I go about that?

Select
 [StudentID],
 [StudentName],
 [StudentEmail],
 [CourseID]
from [tbl_AllStudentsEmail]
left join tbl_CourseID
on tbl_CourseID.StudentID = tbl_AllStudentsEmail.StudentID
where
 CourseID = 11 and
 tbl_AllStudentsEmail.StudentID is not null

This statement gives me a problem.  It would give me "dupe" students email when the student is registered to course ID = 9 and CourseID = 10.

How can i get the UNIQUE student email out?


texts:
Okay, a little complex:

One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a different account, and every 10 minutes, it had no errors then. However, it didn't replicate a new row.
I manually created the new record in the subscriber DB table.
Restarting the server ended up working, but now the replication fails because it has the queue of new records it's trying to add and the identity column value already exists in the destination table.

Options: Should I...

Delete the records I added so that the replication can copy down the new records?

Problem: Not sure if the used IDs will be able to be used with the new records coming over in the replication (because of the auto-numbering identity column), and we need those IDs to remain matched.

Somehow clean out the queue so that those records aren't attempted any more (since I already created them) -- not sure how to do this.

Yes, I searched a lot on this stuff, but I haven't seen this problem described in the same way.

Thanks for any help or answer!


texts: Error code is 0x80131500 when applying SP4 to SQL Server 2012

texts:
This is an open request for any thoughts, advice or guidance about an issue I have while attempting to apply SP4 to a SQL Server 2012 installation.

The server is currently the secondary node of a two-node AG and has been running well for several years. The SP file is on a local drive and I am running it from the RDP desktop. The error code is 0x80131500.

Though the things I have found so far about that error code all relate to Windows Store on Windows 10.

When I look in the setup logs for the SP I see a "Network Path Not found" error and it seems to be looking for the Primary node of this AG.

Any assistance is appreciated.


texts:
I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following output

  ResourceID | ReferenceDate | CodeID
 ------------|---------------|--------
         893 | 2018-05-14    |      1
         656 | 2018-05-07    |      1
         656 | 2018-05-22    |      1
         353 | 2018-05-07    |      1
         530 | 2018-05-07    |      1
         541 | 2018-05-07    |      1
         755 | 2018-05-07    |      1
          55 | 2018-05-07    |      1
         382 | 2018-05-07    |      1
         224 | 2018-05-07    |      1
        1115 | 2018-05-07    |      1
         191 | 2018-05-07    |      5
         191 | 2018-05-08    |      5
         191 | 2018-05-09    |      5
         655 | 2018-07-06    |      6
         655 | 2018-07-10    |      6
         892 | 2018-07-06    |      6

I want to group the CodeID's with a sort by ResourceID and then ReferenceDate. With this result I want to get something like the following table

  ResourceID | CodeID | Count
 ------------|--------|-------
         893 |      1 |     1
         656 |      1 |     2
         353 |      1 |     1
  ...
         191 |      5 |     3
         655 |      6 |     2
         892 |      6 |     1

This to get to a result where we can say that ResourceNumber x has y consecutive days CodeID z.

EDIT: The question looks like this one but with the difference (as far as I see it) that I need to have the count combination or ResourceID and CodeID: Determine consecutive occurrences of values

Code to create sample data for testing:

CREATE TABLE dbo.Resources
(
    ResourceID INT NOT NULL,
    ReferenceDate DATE NOT NULL,
    CodeID INT NOT NULL
);
GO

INSERT INTO dbo.Resources
    (ResourceID, ReferenceDate, CodeID)
VALUES
        ( 893, '2018-05-14',1),
        ( 656, '2018-05-07',1),
        ( 656, '2018-05-22',1),
        ( 353, '2018-05-07',1),
        ( 530, '2018-05-07',1),
        ( 541, '2018-05-07',1),
        ( 755, '2018-05-07',1),
        (  55, '2018-05-07',1),
        ( 382, '2018-05-07',1),
        ( 224, '2018-05-07',1),
        (1115, '2018-05-07',1),
        ( 191, '2018-05-07',5),
        ( 191, '2018-05-08',5),
        ( 191, '2018-05-09',5),
        ( 655, '2018-07-06',6),
        ( 655, '2018-07-10',6),
        ( 892, '2018-07-06',6);


texts: How do you query supertypes to get subtype data?

texts:
I'm a bit of a DBA newbie here (coming from a programming background having to fill the role of a DBA), so please excuse by blantant and somewhat basic ignorance.

Using a simple relationship between a supertype VEHICLE and its relative subtypes that are actual vehicles, like cars, boats or planes etc.  If you query the Vehicle table to get some results, say, show me all vehicles that have a colour = RED, then how does the database know how to query the CAR table, seeing as that is the only table in this example that has a column to do with colour.

I supsect I must have some kind of vehicle type lookup type table, which should also point to the table relevant for that subtype, but I'm not sure if I'm over complicating things or if this is even good practice.  Any help by experienced, wiser heads would be much appreciated.

VEHICLE (supertype)

╔════════╦══════════╗
║ VEH_ID ║  FOO_COL ║
╠════════╬══════════╣
║     28 ║ foo      ║
║     52 ║ column   ║
║     18 ║ data     ║
╚════════╩══════════╝

CAR (subtype)

╔════════╦══════════╗
║ CAR_ID ║  COLOUR  ║
╠════════╬══════════╣
║    52  ║ red      ║
╚════════╩══════════╝

BOAT (subtype)

╔════════╦══════════╗
║ BOAT_ID║  SPEED   ║
╠════════╬══════════╣
║    18  ║ VERY FAST║
╚════════╩══════════╝


texts:
At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several developers using one server have had to write procedures that throw around mountains of code such that their log files are growing to 10-20GB (on DBs approx 15-40GB) until they run out of space on the drive volume we have provided for the log files. The databases are all in Simple recovery mode and backups are (almost) never taken.

As a bandaid I've created a SQL Agent Job that can be run by anyone to shrink all user log files on the server.

What are some valid Log File Management strategies that might be used?

For example it is my understanding that as the users generally do a blitz of intensive work that checkpoints are probably being throttled back and that thus issuing manual checkpoints would see no advantage. Is that the case or should we in fact investigate adding manual checkpoints to their code?

And just to be clear it is log file space on disk we are interested in not space within the log file.


texts: How does the use of derived tables speed up sorting?

texts:
In the article 45 Ways To Speed Up Firebird Database, it states that

  Use derived tables to optimize SELECT with ORDER BY/GROUP BY

  Another way to optimize SQL query with sorting is to use derived
  tables to avoid unnecessary sort operations. Instead of

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2

  use the following modification:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N FROM
    (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

I assume T.FIELD_KEY means a primary key column.

In other words, it shunts the sorting to a subquery, and then 'self-joins' to get the rest of the necessary columns.

As I understand it, this will let Firebird do the sorting on a narrower resultset which would use less memory and be more cache-friendly, but surely it shouldn't give that much a boost? Especially when it needs to fetch the other columns afterwards, potentially in a random I/O pattern too.

On the other hand, this reeks of undefined behaviour in that there's no guarantee (in standard SQL) that the engine would retain the order after the join -- it could just join the other way, wouldn't it?

Are my guesses correct? Am I missing something?


texts: Why does copied MySQL database have different data than source?

texts:
I am trying to migrate database A to database B. I have tried various methods for this, including...

Using Navicat, do Tools > Data Transfer, copying structure and data from A to B
Using Navicat, delete all tables from B, then drag and drop all tables (structure and data) from A to B
Using Sequel Pro, export A to SQL file, then import SQL file into B

Option 1 always fails, and I'm not sure why. The error message just says "failed".

Options 2 and 3 seem to work fine, but then some tables have slightly different data, which makes absolutely no sense to me. For example, here is a row of data from database A...

node    book    0   438 2255    und 0   foo NULL    full_html

...and here is the same row in database B...

node    book    0   438 2255    und 0   bar NULL    full_html

The "foo" and "bar" are HTML content for web page #438 at revision #2255. I have no idea how the HTML content for the same exact revision can be different, when database B should be a straight copy of A.

Mysteriously, "foo" represents the most recent version of page #438, and "bar" actually reflects the prior version of page #438. How is this even possible?

Also, if any of you DBAs happen to be familiar with Drupal, note that this is for a Drupal site. Incidentally, when I try to restore a backup on this site (a ZIP or SQL file made with the Backup and Migrate module), I get an unhelpful "site encountered an unexpected error" message and nothing else in the error log. The same ZIP and SQL files restore just fine in other test environments, so something about this one site just seems wonky.


texts: Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

texts:
TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

Following up on my previous question where we found query performance difference between different join styles (ie. nested vs. traditional), I have realized that the nested syntax also modifies the behavior of the query. Consider the following 2 queries.

SELECT  a.*, m.*, n.*
FROM    dbo.Autos a
LEFT JOIN dbo.Models m
  JOIN dbo.Manufacturers n  -- <-- Nested INNER JOIN
  ON n.ManufacturerID = m.ManufacturerID
ON m.ModelID = a.ModelID

This does not have to make the manufactures join in order to include an auto row with a ModelID that is NOT in the Models table.

Using the traditional syntax, we have to change the join to Manufactures to an outer join, like so... but this changes the query plan.

SELECT a.*, m.*, n.*
FROM dbo.Autos a
LEFT JOIN dbo.Models m
ON m.ModelID = a.ModelID
LEFT JOIN dbo.Manufacturers n -- <-- Now LEFT OUTER JOIN
ON n.ManufacturerID = m.ManufacturerID


texts:
I have a table called FoodMaker and another table called Settings. Every FoodMaker has multiple Settings. I also need a 3rd table to record what has been made, when and with what Settings. It might have these fields: id, food_made, date_time, settings_used.

This is pretty straightforward since I can put IDs' of FoodMaker and Settings in the 3rd table to reference them. But the problem is, the Settings may change later but I want the Settings which the food has been made with and not the updated one. So I need to take a snapshot of Settings each time I'm INSERTING into 3rd table.

One easy way could be just to add all of the Settings fields to the 3rd table on each INSERT. But is it not redundant and ugly? Is there a better solution?

I asked this question on SO but then I figured out this might be a better place to post it.


texts:
Hi I am creating a SSIS package that truncates table as part of ETL process. A role needs to be defined with the alter privilege and the service account added to the role

I have done it the following way. Could you let me know if it is correct ? Isnt there a way of only assigning alter permission to role rather than involving table

USE CoreReferenceStaging
exec sp_addrole  'alterpermissions', 'INT\svc-w-corerefdata-de'
exec sp_addrolemember 'alterpermissions', 'INT\svc-w-corerefdata-de'
GRANT ALTER ON [dbo].[theTable] TO [alterpermissions]


texts: Improve sql server lookup table performance

texts:
We have a sql server database (on azure) with one large table c1billion rows. Each row consists of a key char32 which is an md5 hash and a value int. All queries select multiple values from the table using a key in clause. Sometimes we have 1000+ items in the in clause and the query can be very slow.
What are the top things we can do to improve query performance in terms of table structure (partitioning?) and query structure (maybe chunking queries)?


texts: Decomposing table into BCNF that preserves dependencies

texts:
I have a relation like

R(A,B,C)

and dependencies like

A->B , B->C , A->C

I think the relation is in 2NF as candidate key is A. AFAIK when there is only one candidate key then the relation is in 2NF. So to decompose it into 3NF I broke the relation into

R1(A,B) R2(B,C)

and dependencies are

{A->B} and {B->C}

This is also in BCNF but A->C isn't preserved. So do I have to make another relation as R3(A,C) with A->C dependency?


texts: Find minimum record based on two fields, self joins vs. derived tables

texts:
Is there a better way? I need attributes of the oldest SequenceID of oldest DateID for each ThingID. PK is DateID & SequenceID:

SELECT
          ThingID     = F1.ThingID
        , Attribute1  = F1.Attribute1
        , Attribute2  = F1.Attribute2
        , Attribute3  = F1.Attribute3
FROM dbo.Fact F1
INNER JOIN (
              SELECT  DateID      = F2.DateID
                    , SequenceID  = min(SequenceID)
                    , ThingID     = F2.ThingID
              FROM dbo.Fact F2
              INNER JOIN  (
                              SELECT    DateID   = min(DateID)
                                      , ThingID  = ThingID
                              FROM      dbo.Fact
                              GROUP BY  ThingID

                          ) AS F3 ON  F2.ThingID = F3.ThingID
                                  AND F2.DateID  = F3.DateID

              GROUP BY F2.DateID, F2.ThingID

            ) AS F2  ON   F1.ThingID    = F2.ThingID
                    AND   F1.SequenceID = F2.SequenceID
                    AND   F1.DateID     = F2.DateID

FACT TABLE

DateID       datetime  PK
SequenceID   INT       PK
ThingID      INT
AttributeID1 INT
AttributeID2 INT
AttributeID3 INT

edit: SQL 2012


texts: mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

texts:
I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway around this?


texts: Unable to configure MongoDB replication without initial sync

texts:
I have a big MongoDB database on my production server, which I want to set up as a replica set. I understand that if I enable the replica set option on both servers, it will automatically start to sync the data from scratch. But I don't want to sync from the beginning; instead I want to configure it like MySQL replication.

I would like to follow the following process:

Take dump from primary
Restore it on secondary
Enable replica set in config file, on both servers
Restart MongoDB.
Go to primary server and add the secondary node.

Is this possible; or is there any other alternate solution for this?


texts: Can I use SQL Server Developer edition (free license) for Non-Production environmen that is used for training

texts: Client uses SQL Server 2012 Dev environment for Training, is this regarded as a Prod or Non-Prod activity? Please point me in the direction of a Microsoft documentation for this scenario. Thank you.


texts: Grant User Permission To Restore Database And Access that database in sql server?

texts:
I want to create a user having db_create, db_restore server roles and will be db_owner of that database. But there is only 1 server role available, that is db_creator, which can create database  and is also the db_owner of that. But when I restore another database backup that gets disconnected, i.e. not accessible.

In sql server is there any way that can create and restore db and will be dbowner of that database. I don't want to grant sysadmin server roles to that user.

Please help me out.


texts: Merging two 'tables' (function outputs) 1 to 1

texts:
Let's say I have two functions. We'll represent them as tables: order_items, order_details.

CREATE TABLE order_items
    ("order_item_id" int, "product_id" int, "quantity" int)
;

INSERT INTO order_items
    ("order_item_id", "product_id", "quantity")
VALUES
    (10, 1, 1),
    (11, 1, 1),
    (12, 1, 1),
    (13, 2, 2)
;

CREATE TABLE order_details
    ("order_details" jsonb, "product_id" int, "quantity" int)
;

INSERT INTO order_details
    ("order_details", "product_id", "quantity")
VALUES
    ('{"1":2,"2":5}'::jsonb, 1, 1),
    ('{"1":2,"2":6}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 2, 2)
;

I would like to take their outputs and have an output that looks like this:

("order_details", "product_id", "quantity", "order_item_id")
('{"1":2,"2":5}'::jsonb, 1, 1, 10),
('{"1":2,"2":6}'::jsonb, 1, 1, 11),
('{"1":1,"2":4}'::jsonb, 1, 1, 12),
('{"1":1,"2":4}'::jsonb, 2, 2, 13)

Basically, order_details can be associated with any order_item_id as long as the product_id and quantity match. The output must return 4 rows.

I've been trying to do this in plain SQL, but I am starting to feel like it's not possible. Using plpgsql is fine, but I figure there is a faster way than doing a double nested for-loop.


texts:
I have 3 tables (shipment, Supplier, Part).

create table Shipment (
    SID int,
    PID int,
    Qty int,
    );

create table Supplier (
    SID int,
    Sname nvarchar(10),
    Status int,
    );

create table Shipment (
    PID int,
    Pname nvarchar(10),
    city nvarchar(10),
    );

I want to make this condition as a trigger to avoid the transaction.
condition: prevent transactions over 10,000 qty by a supplier who has the status equal or bellow 30.

SELECT        Shipment.Qty, Supplier.Status
FROM            Shipment INNER JOIN
                         Supplier ON Shipment.SID = Supplier.SID
WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30)

I created something like this, but it doesn't work.

ALTER TRIGGER [dbo].[qytcontrolcheck]
   ON  [dbo].[Shipment]
   AFTER  INSERT,UPDATE
   AS
BEGIN
            if exists (SELECT        Shipment.Qty, Supplier.Status
                    FROM            Shipment INNER JOIN
                         Supplier ON Shipment.SID = Supplier.SID
                WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30) )
                                        BEGIN
                    print'NOT ENOUGH STATUS TO REGISTER THIS TRANSACTION'
                    rollback transaction;
                END
END

what am I doing wrong?
thank you in advance for helping me.


texts: Create WAL at interval, but only if data changed

texts:
I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure.

However, this creates a LOT of big files, even if nothing has changed!  Is there a way to tell PostgreSQL to only create these WAL files at the timeout interval IF something has changed?


texts:
We are planning to migrate two servers with MSSQL 2012 servers with configured transactional replication. New servers will have new names, ip addresses and MSSQL 2014. Subscriber is used for reporting and there are extra tables and columns needed for reporting that does not exist on publisher.
What will be best way to migrate those servers? We will have enough downtime to do all needed works.

Some extra columns on reporting server are calculated on daily basis, and we would like to restore it from backup too, if that is possible.


texts:
We have a query that runs on a view and its execution time is about 6 min.

SELECT COUNT(*)
FROM someschema.item_total_violations
WHERE channel_id = 122507833
AND item_date >= TIMESTAMP'2015-02-01 00:00:00';

Its not a materialized view so there is no index on the item_date.

The base query of the view is below:

SELECT i.id
    ,i.title
    ,i.body
    ,i.description
    ,i.item_date
    ,i.created_date
    ,i.channel_id
    ,i.community_id
    ,i.person_id
    ,i.message_type
    ,ch.client_id
    ,upper(p.NAME) AS person_name_upper
    ,upper(ch.NAME) AS channel_name_upper
    ,p.NAME AS person_name
    ,ch.NAME AS channel_name
    ,ch.connector_type AS channel_type
    ,cm.NAME AS community_name
    ,cm.network_id
    ,nvl(ct2.cnt, 0) + nvl(ct4.cnt, 0) AS total_violations_count
    ,nvl(ct1.cnt, 0) + nvl(ct3.cnt, 0) AS quarantined_violations_count
    ,nvl(ct5.cnt, 0) AS quarantined_attributes_count
    ,nvl(lh_info.cnt_lh, 0) AS legal_holds_count
    ,nvl(lh_info.cnt_lh_w_no_date, 0) AS legal_holds_count_w_no_date
    ,nvl(lh_info.max_hold_until, to_date('1970-01-01', 'yyyy-mm-dd')) AS legal_hold_max_hold_until
FROM item i
    ,channel ch
    ,person p
    ,community cm
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct1
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
        GROUP BY pv.item_id
        ) ct2
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY aa.item_id
        ) ct3
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.item_id IS NULL
        GROUP BY aa.item_id
        ) ct4
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(DISTINCT (pv.item_attribute_id)) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.item_attribute_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct5
    ,(
        SELECT lh_rel.item_id AS item_id
            ,count(DISTINCT (lh.id)) AS cnt_lh
            ,max(lh.HOLD_UNTIL) AS max_hold_until
            ,sum(CASE
                    WHEN lh.hold_until IS NULL
                        THEN 1
                    ELSE 0
                    END) AS cnt_lh_w_no_date
        FROM LEGAL_HOLD_RELATION lh_rel
            ,LEGAL_HOLD lh
        WHERE lh.id = lh_rel.legal_hold_id
            AND lh.STATUS = 'A'
        GROUP BY lh_rel.item_id
        ) lh_info
WHERE ch.STATUS = 'A'
    AND i.channel_id = ch.id
    AND i.person_id = p.id
    AND i.community_id = cm.id(+)
    AND i.id = ct1.item_id(+)
    AND i.id = ct2.item_id(+)
    AND i.id = ct3.item_id(+)
    AND i.id = ct4.item_id(+)
    AND i.id = ct5.item_id(+)
    AND i.id = lh_info.item_id(+)

The item table is the biggest and we have tried partitioning the table in chaannel_id but that didn't help our query. I understand that there is no index on item_date in original query and it has effect on performance. Apart from trying out materialized view, is there something that I can improve on the base query of the view?


texts: Microsoft SQL Server Not Showing Server Name in object Explorer

texts:
I Recently Installed Microsoft SQL Server 2014 (SSMS) on my local machine for development learning purposes. I am running Windows 10 32 bit. When i try to connect, Server Name does not appear. Screen Shot is attached.

According to my research, name of the local server should appear in the marked field.
I think that Installation is successful.

I am unable to find the solution to this problem. I installed SSMS 2015 RC0, Problem was not solved. Then i uninstalled it and Installed SSMS 2014. But the problem is not solved.
I remember perfectly that about a  year ago. I installed SSMS and there was no such problem. I can't figure out Why the server name is not appearing. When i try to connect by manually entering Local server name(./), the following error occurs

Any help would be appreciated.


texts:
I have an XML feed that i need to parse daily, i want to store all of the data from the XML feed in a SQL table so then that data can be queried and manipulated by my clients.

I can currently parse the XML data for select pieces of information for example the price of a product, but the pieces of data need to be user defined. I was wondering would it be possible to compare the XML data to a schema to allow me to find the names of each XML values.

Then i would be able to dynamically store the names of the XML values as attributes and the values of the XML data as a detail to that attribute, is there a way i can do this?

Will it have something to do with the schema that goes with the XML feed?

I would be using PHP to do this aswell

Thanks

James


texts: Server-wide Database Permissions in SQL Server

texts:
We have a SQL Server 2014 installation, which is accessible by a subset of our Active Directory users via Windows Authentication.

Currently, when they log into the server for the first time they are given the sysadmin server role. I want to change this so that they belong to a more restrictive role.

I would ideally like them to be able to deal with data (SELECT, UPDATE, DELETE etc), EXEC stored procedures, and CREATE, ALTER and DROP tables, stored procedures and views.

The problem is that those permissions are database-level rather than server-level. Is it possible to have a server-level role that defines a common set of permissions for all databases on the server? If not, what's the best approach here?


texts: Postgres ts_headline inconsistent behaviour

texts:
I have just stumbled over a weird behaviour in postgres ts_headline function (used for highlighting fulltext search results).
First I thought the simple dictionary does not play with highlighting as the below example shows (there should be <b> tags):

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('artificial'));
       ts_headline
-------------------------
 This is artificial text
(1 row)

But a different word works fine...

# SELECT ts_headline('simple', 'some Word in', to_tsquery('Word'));
 ts_headline
---------------------
 some <b>Word</b> in
(1 row)

Does anybody have an explanation for this behaviour?


texts: How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?

texts:
Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer:

Make sure the service is set to start automatically.

Start->Control Panel->Administrative Tools->Services
Find the service (should be named something like postgresql-9.0)
Make sure the Startup Type is set to Automatic

Additionally there was a problem with the password for the user postgres that I had to correct, and everything worked fine - until the 2nd reboot (I installed PostgreSQL on my developer PC, which I switch off every night). I had to correct the password again, and have to redo it ever since after every 2nd reboot.

What can I do?


texts: SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time

texts:
We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of a query, causing sessions to sit in ASYNC_NETWORK_IO wait for minutes or even hours although the application screen displays the result of the query almost instantly. In some cases, this can cause blocking. I want to find sessions that are in that state for a long period of time. Eventually, I may want to create a procedure that will automatically kill them.

For each session that is currently in an ASYNC_NETWORK_IO wait, I want to find out how long it has been waiting. However, dm_exec_requests.wait_time is never as high as 2,000 even though the difference between GETDATE() and last_request_end_time is much longer. last_request_end_time equals GETDATE() minus total_elapsed_time (CalculatedStartTime in the query below) if both are rounded to the nearest second. This is also true of dm_exec_sessions.start_time and dm_exec_requests.last_request_end_time.

In the results of this query:

SELECT  SPID            = s.session_id
        ,Program        = s.[program_name]
        ,StartTime      = r.start_time
        ,ElapsedTime    = CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, r.start_time, GETDATE()), 0), 114)
        ,s.last_request_end_time
        ,r.wait_time
        ,CalculatedStartTime = DATEADD(MILLISECOND, -r.total_elapsed_time, GETDATE())
  FROM  sys.dm_exec_sessions s
        JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
 WHERE  s.open_transaction_count    = 0
   AND  r.[status]                  = 'suspended'
   AND  r.wait_type                 = 'ASYNC_NETWORK_IO'
   AND  r.last_wait_type            = 'ASYNC_NETWORK_IO'
   AND  r.command                   = 'SELECT'
   AND  r.blocking_session_id       = 0
   AND  r.total_elapsed_time        > 10000 -- 10 seconds
 ORDER BY r.wait_time DESC

I must be misunderstanding the definition of some of these DMV columns. Note that in the result, row 9 shows a wait_time of only 629 ms, yet the ElapsedTime (time since the last_request_end_time) is over 28 minutes:

How can I get the results I intend (a row for each session_id that shows how long it has been waiting on ASYNC_NETWORK_IO)?


texts: How to filter multiple many to many relationship based on multiple tags?

texts:
I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like:

Problem table:

----+------------------------------------+--------
 id |                name                | rating
----+------------------------------------+--------
  1 | Special Permutation                |   1600
  2 | Binary String Reconstruction       |   1500
  3 | Special Elements                   |   1500
  4 | Alice, Bob and Candies             |   1300
  5 | K-th Not Divisible by n            |   1200
  6 | Same Parity Summands               |   1200
  7 | Sum of Round Numbers               |    800
  8 | Skier                              |   1400
  9 | Square?                            |    900

Tag table:

 id |           name
----+---------------------------
  1 | constructive algorithms
  2 | dfs and similar
  3 | math
  4 | brute force
  5 | implementation
  6 | two pointers
  7 | binary search
  8 | data structures

Problem tags table:

 problem_id | tag_id
------------+--------
          1 |      1
          2 |      1
          2 |      2
          2 |      3
          3 |      4
          3 |      5
          3 |      6
          4 |      5
          5 |      3
          5 |      7

My question is how can I filter out problems based on multiple tags, i.e. all problems that are tagged math and binary search and brute force; or all problems that are tagged math but not constructive algorithms; or for a more complex one all problems that are only tagged with math and implementation and nothing else?

Currently I've come up with something like this:

Find all problem's ids that are tagged math (joining tag and problem_tags table)
Find all problem's ids that are tagged binary search
Find all problem's ids that are tagged brute force
Get intersection of all above ids
Select problems where their ids is in the above intersection

But my solution lacks when it reaches the second example (only tagged with selected tags) and I think it's not the most optimal and SQL-ish way for doing it.


texts: Execute privilege granted successfully, but I can't execute the procedure

texts:
I am using the database as me.

The procedure exists as an object that belongs to me.

Grant sucessful.

But when I attempt to EXECUTE insert_into_purified; i get
Error starting at line : 469 in command -
BEGIN insert_into_purified; END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "C##ELLIE.INSERT_INTO_PURIFIED", line 5
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges


texts: SQL Server Always-On Availability Groups Patching

texts:
My SQL server farm has been neglected with patching OS level & SQL server level (because they are critical systems, outages are hard to come by).

An option is to patch the secondary nodes of our AOAG clusters up to the latest patch one month, then the next month the business agree to schedule a fail over out of hours.. I can then patch the new secondary (old primary). This will mean the nodes wont be on the same patch level for a month.. is this “a no no”?


texts:
I am in a bind, today our server with the MySQL Server went down and we are having trouble getting it back up. Only change to the system is that we upgraded the ram from 2gb to 4gb. Everything else on the system is working except the MySQL Server. I have included the most recent error section from the error log. Any help would be greatly appreciated.

Update: This is running on a Windows 2008 R2 Server 64bit.

Error Log:


texts: What is the syntax to sp_recompile logon trigger?

texts:
I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax.

E.g. these don't work:

sp_recompile '[Tr_Logon]'

sp_recompile '[Tr_Logon]' ON ALL SERVER ;)


texts: SQL Server - can't import data-tier application from bacpac file (trying to move db + data from Server 2016 to 2014 SP1)

texts:
I'm trying to get a workable method to transfer a database schema and data from a SQL Server 2016 install to a SQL Server 2014 install on a different network. It looks like Import/Export Data-Tier Application would be a perfect solution, and I've eliminated the other candidates.

However, when I try to test on the 2016 install by exporting and then importing, I get the following error on the Importing Database, Creating Deployment Plan and Initializing Deployment stages:

  Error SQL0: Data cannot be imported into target because it contains one or more user objects. Import should be performed against a new, empty database.

I've made sure that the destination Database is one that doesn't already exist (in fact the wizard won't let me try if it doesn't). I've tried exporting a copy of the database and deleting that copy before trying to import. I looked for answers and found an answer for a different version with different files. I didn't see anything for the problem on this version, and I don't know enough to try blindly adapting that answer.

The Extract/Deploy workflow with just dacpacs works fine, but I need to get the data as well.


texts: How to get different metrics for same table with one query?

texts:
I have a lot of queries to the same table. All queries looking like this

SELECT COUNT(*) FROM <sameTable> WHERE <whereClause> GROUP BY <groupBy>

Let's the amount of such queries is 40. So the table is iterates 40 times, I'm trying to reduce amount of iterations. I have tried different approaches and finished with this:

SELECT
        SUM(CASE WHEN ((p.statusId = 9)) THEN 1 ELSE 0 END) as metric1
        ,SUM(CASE WHEN ((p.statusId IN (10, 1088))) THEN 1 ELSE 0 END) as metric2
        ,SUM(CASE WHEN ((p.statusId = 11)) THEN 1 ELSE 0 END) as metric3
        ,SUM(CASE WHEN ((p.statusId = 20)) THEN 1 ELSE 0 END) as metric4
        ,(SELECT TOP 1 COUNT(DISTINCT p.CaseId) FROM vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId
WHERE p.statusId = 12
GROUP BY history.AssignedByUser ORDER BY COUNT(DISTINCT p.CaseId) DESC) as metric5
FROM
    vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId

There are two issues:

Summ works on all records, but some metrics need unique count of ids matching the expression.
The metric5 is subquery, I have to use it because I have failed to use aggregation function to get max value of cases in status 12 by user.


texts: How to schedule jobs in Sql server 2008 for less than 10 sec?

texts:
I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds.

The job is used to insert/update visitor information, and segmentation information into a database which is tracked by google search.

There are up to about 100 rows inserted in a 2 or 3 seconds. That job inserts and update the table in a database. Is there any way to schedule it using sp job scheduling configuration?


texts: A significant part of SQL Server process memory has been paged out

texts:
Around 7:30pm every evening we receive the following message and the server experiences an outage of 2 minutes.

  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1868 seconds. Working set (KB): 2096696, committed (KB): 18715612, memory utilization: 11%.

The message appears in the log 4 times around a 5 minute period.

I have been investigating, and we do not have 'lock pages in memory' activated.
 Also, the min server mem is 0 (default) and max server memory set at around 26GB.

Nothing seems that unusual when looking at perfmon counters ie: Available MB, Free Pages, Page Life, Lazy Writes.

Its possible that there is memory pressure as a result of backing up our DBs around this time.

How can I better check what the problem is / rectify the situation?

Edit: Results of

select TOP 10
    (sum(pages_kb)*128)/1024 as 'sizein_mb',
    type as 'clerkttype'
from
    sys.dm_os_memory_clerks
group by
    type
order by
    (sum(pages_kb)*128)/1024 desc

1903846  MEMORYCLERK_SQLBUFFERPOOL
428635  CACHESTORE_SQLCP
148648  CACHESTORE_OBJCP
22371   MEMORYCLERK_SOSNODE
16802   USERSTORE_SCHEMAMGR
6834    OBJECTSTORE_LOCK_MANAGER
5494    CACHESTORE_PHDR
5246    MEMORYCLERK_SQLSTORENG
4513    USERSTORE_DBMETADATA
4168    MEMORYCLERK_SQLGENERAL


texts: By what policy are SQL Server 2014 updates installed automatically?

texts:
Tonight our server decided to install SQL Server 2014 CU7 for SP1. The update appears in the Windows Update history and I found downtime in our logs for it. The previous patch level was CU3.

I was surprised by this because I am not used to Windows or SQL Server automatically installing CU's. I also never witnessed a service pack being installed automatically. What has changed?

According to sqlserverbuilds.blogspot.de the CU7 was released today. It was installed right away after release.

I am not categorically against this but I would like to understand what policies are driving the automatic update choices.

Strangely, tonight was not a "patch Tuesday". No other updates were processed. The server did not reboot, either. I'm certain this was not a manual action.

So far my plan was to keep the server at the CU3 level and upgrade to 2016 eventually. The new CU being installed caused unnecessary downtime and introduced risk which I normally was not willing to take. In particular I do not wish to install patches at a random time.

So what's the automatic update policy and should I do something about that?


texts: Oracle Optimizer refuses to use my index

texts:
I have a relatively simple query that SELECT data in a table.

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

This generates the following plan :

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

On this table, I have a UNIQUE INDEX that contains all those 3 columns.
But the optimizer prefer doing a FULL SCAN on the table rather than using the INDEX.

Where things become to go strange, is when I try this query without my binds but by replacing the values directly in the query.
Of course the SQL_ID changes, but this time the optimizer decides to use the INDEX and goes from 1104 gets to 6 (which is of course a great optimization).

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

Statistics are up to date and there are no histograms on this table.

Any way to make the optimizer use that index without modifying the query ?

I think the optimizer thinks that the possibility to have 3 NULLs might happen so this disable him from using the index is it right ?

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)


texts: Does Ordering BY primary key help query performance when joining table to query

texts:
Wondering about performance when joining a table with a query. Does the ORDER BY help?
For example if I am joining a user_stat table where the primary key is user_id, order_type with a selection of orders.
SELECT user_stats.*, order_stat.num_orders as orders_in_range FROM
  user_stats INNER JOIN (
    SELECT user_id , order_type, count(order_id) as num_orders FROM orders
    WHERE order_date > '2020-01-01 00:00:00'
    GROUP BY user_id, order_type
    !!! ORDER BY user_id, order_type !!!
  ) as order_stat ON user_stats.user_id = order_stat.user_id AND user_stats.order_type = order_stat.order_type

I understand that there are other ways to write this. I am just wondering if the ORDER BY speeds things up, slows them down or has no effect. Or if there is some way to let the optimizer know that they are in order so can do things faster?
I am using mySQL 5.7


texts: Replication successful but slave skips certain queries

texts:
I am testing a MySQL master-slave replication. Here is the master my.cnf:

log-bin=/var/log/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Here is slave my.cnf:

server-id=3
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/slave-relay.log
relay-log-index=/var/log/mysql/slave-relay-log.index
log-slave-updates=1
replicate-wild-do-table = ads.%
replicate-ignore-db = mysql
replicate-ignore-db = test

Everything works fine but if I execute something like this on master, it won't replicate to slave.

use test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

If I omit use test, the replication is successful.

I would like to skip test db from replication but also need to avoid the above mentioned issue. I have searched a number of posts but couldn't find a solution. Am I missing anything simple?


texts: Change sa password for SQL Server on a GNU/Linux machine

texts:
Please enter a password for the system administrator (SA) account:
    Please confirm the password for the system administrator (SA) account:

    Setting system administrator (SA) account password...
    sqlservr: Debugger.cpp:531: static void Debugger::Print(const void *, unsigned int): Assertion `m_IsAttached' failed.
    find: `/usr/share/icons/Numix/numix-icon-theme-circle-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/rhythmbox-plugins-open-containing-folder-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/alternative-toolbar-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/rb-fileorganizer-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/suspend-plugin-master': Permission denied
    nohup: redirecting stderr to stdout

    Do you wish to start the SQL Server service now? [y/n]: y
    Do you wish to enable SQL Server to start on boot? [y/n]: n

You can use sqlservr-setup --enable-service to enable SQL Server to start
at boot.

Setup completed successfully.

I can make a connection to SQL Server at first, using it with DBeaver for 1 week. Today I wanted to change the sa password, so I ran

sudo /opt/mssql/bin/sqlservr-setup

...but it seems to have failed. I removed the SQL Server package and installed it again, but the password for sa always gives me that kind of error. I can't make any connection again because of password failure.

How can I perform a fresh installation? I have deleted all mssql on /opt but no luck. Or how can I change the current sa password?


texts: Indexes are not visible in a MongoDB replica set

texts:
On a local MongoDB instance i can connect using the mongo client, define an index using this command

db.collection.createIndex( { score: 1 } )

and then view that index and the default _id index using this command

db.collection.getIndexes()

If I connect to a Replica Set and run the same commands to create and view the indexes, all that is shown is an empty array

> db.collection.getIndexes()
[ ]

I know the indexes exist, as they are referenced if I use . explain() on a query.
So,how do i view the indexes when using a mongo Replica Set?

MongoDB shell version: 2.6.10

Mongo DB version
rs0:PRIMARY> db.version()
3.2.17

My local version of mongo was 2.6.10, and after upgrading the local version to 3.x I can now see the indexes in the remote replica set.


texts: How to limit the connections per host in MongoDB?

texts:
I'm running a mongo server which accepts connections from other clients through java driver. The thing I have noticed is after a while some users open too many ports, and this prevents other users to connect to the mongo. They create only 1 mongoClient object, though checking their IP result in watching hundreds of ports.

I came across an example to limit the connection per host in the java driver, but I don't want the client to mess with it. How can I limit the client from my mongod instance?

The instance is 1 mongod that runs on a Linux remote server.


texts:
I Have a table articles with following data

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  7 | ABCD                  | Following things are not upto date       |
|  8 | RockOn                | is the Following                         |
+----+-----------------------+------------------------------------------+
8 rows in set (0.00 sec)

when i write the query as..

mysql> select * from articles where match(`body`) against('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

the result is as expected.

but when i issue the query as

mysql >select * from articles where match(`body`) against('following' in boolean mode);
Empty set (0.00 sec)

why it shows empty set as there is record corresponding to the query.

I have Full text index on body.

mysql> show create table articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


texts: T-SQL - What's the most efficient way to loop through a table until a condition is met

texts:
In got a programming task in the area of T-SQL.

Task:

People want to get inside an elevator every person has a certain
weight.
The order of the people waiting in line is determined by
the column turn.
The elevator has a max capacity of <= 1000 lbs.
Return the last person's name that is able to enter the elevator before it gets too heavy!
Return type should be table

Question:
What is the most efficient way to solve this problem? If looping is correct is there any room for improvement?

I used a loop and # temp tables, here my solution:

set rowcount 0
-- THE SOURCE TABLE "LINE" HAS THE SAME SCHEMA AS #RESULT AND #TEMP
use Northwind
go

declare @sum int
declare @curr int
set @sum = 0
declare @id int

IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
    DROP TABLE #temp

IF OBJECT_ID('tempdb..#result','u') IS NOT NULL
    DROP TABLE #result

create table #result(
    id int not null,
    [name] varchar(255) not null,
    weight int not null,
    turn int not null
)

create table #temp(
    id int not null,
    [name] varchar(255) not null,
    weight int not null,
    turn int not null
)

INSERT into #temp SELECT * FROM line order by turn

 WHILE EXISTS (SELECT 1 FROM #temp)
  BEGIN
   -- Get the top record
   SELECT TOP 1 @curr =  r.weight  FROM  #temp r order by turn
   SELECT TOP 1 @id =  r.id  FROM  #temp r order by turn

    --print @curr
    print @sum

    IF(@sum + @curr <= 1000)
    BEGIN
    print 'entering........ again'
    --print @curr
      set @sum = @sum + @curr
      --print @sum
      INSERT INTO #result SELECT * FROM  #temp where [id] = @id  --id, [name], turn
      DELETE FROM #temp WHERE id = @id
    END
     ELSE
    BEGIN
    print 'breaaaking.-----'
      BREAK
    END
  END

   SELECT TOP 1 [name] FROM #result r order by r.turn desc

Here the Create script for the table I used Northwind for testing:

USE [Northwind]
GO

/****** Object:  Table [dbo].[line]    Script Date: 28.05.2018 21:56:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[line](
    [id] [int] NOT NULL,
    [name] [varchar](255) NOT NULL,
    [weight] [int] NOT NULL,
    [turn] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
    [turn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[line]  WITH CHECK ADD CHECK  (([weight]>(0)))
GO

INSERT INTO [dbo].[line]
    ([id], [name], [weight], [turn])
VALUES
    (5, 'gary', 800, 1),
    (3, 'jo', 350, 2),
    (6, 'thomas', 400, 3),
    (2, 'will', 200, 4),
    (4, 'mark', 175, 5),
    (1, 'james', 100, 6)
;


texts:
I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn't be autoincrement.

Now I need the new table to have its PK as autoincrement. But how can I do that after the PK already exists and have data?


texts:
I store hits. My table looks like this:

    ID |   time   |   Country
--------------------------------
    1  | 01:00:00 |    France
    2  | 01:00:00 |    France
    3  | 01:00:00 |    Brazil
    4  | 01:00:00 |    USA
    5  | 02:00:00 |    USA

This is my query:

SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

It does count how many hits I got and groups it by hours:

    time   |   total_hits
---------------------------
  01:00:00 |    4
  02:00:00 |    1

I want one result grouped by time column and another grouped by countries column.
This is what I need:

1) Grouped by hours like this (same like above)

    time   |   total_hits
---------------------------
  01:00:00 |    4
  02:00:00 |    1

2) AND grouped by countries like this

  country  |   total_hits
---------------------------
 France    |    2
  USA      |    2
 Brazil    |    1

I could do:

SELECT
       COUNT(*)
     , HOUR(time)
     , COUNT(IF( Country = 'France', Country, null)) AS France
     , COUNT(IF( Country = 'USA', Country, null)) AS USA
     , COUNT(IF( Country = 'Brazil', Country, null)) AS Brazil
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

Or alternatively with CASE or SUM(Country = 'France') AS France.

But in the country column there are more than just 3 countries. If I would do this with every country my query would be very long.

I could do this:

SELECT COUNT(*), Country, HOUR(time)
FROM hits
WHERE time >= CURDATE()
GROUP BY Country, HOUR(time)

But the ouput will be something like this:

  time     | country   |   total_hits
---------------------------------------
  01:00:00 |  France   |    2
  01:00:00 |  USA      |    1
  01:00:00 |  Brazil   |    1
  02:00:00 |  USA      |    1

However I need an output like mentioned above.

So basically I need these queries in one query:

1) Group by HOUR (same like mentioned above)

SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

2) AND Group by Country

SELECT COUNT(*) as total_hits
FROM hits
WHERE time >= CURDATE()
GROUP BY Country

Performance is important. The database has millions of entries.
Maybe MySQL is not the best way for this problem?


texts: SQL SERVER 2014 FCI Cluster taking too long to failover

texts:
I support a multi-instance, two node failover cluster (active-active).  Both instances are running SQL 2014, on Windows Server 2008R2.  The nodes each have 3/4 TB of memory and 32 cores (64 with HT).  SQL is configured with a Max memory of 350GB for each instance.  There are between 60 and 100 databases per instance with 3-5 TB of datafiles in  per instance.

I’m having some issues with failover times that I’m trying to resolve.
The issue appears to be centered around shutting down the SQL instance prior to the failover.  When a manual failover is performed, we do the following to speed things up to this point.

Checkpoint all databases just prior to failover to get as many
dirty pages written to disk prior to the beginning of the failover
as possible.  Failovers were much longer prior to adding this step
Manual failover is initiated, SQL goes into SA only mode for 1-2
minutes and then the SQL instance stops.
It takes another 1-2 minutes to release the memory back to the OS before the failover occurs and the resource comes up on the other node.

If the SQL instance has only limited data in memory the failover happens much more quickly.

We looked at I/O and CPU metrics and don’t see any significant issues.

I’m looking for resources and ideas to help reduce the time it takes for this failover to happen.
Thanks,
-Luke.


texts:
Is it possible to define package procedure/function outside of package body block (for Oracle)?

Simplified syntax for defining package body is:

create or replace package body <package_name> IS

  function <function_name>
   <function_body>

  procedure <procedure_name>
   <procedure_body>

end <package_name>;
/

Is it possible to define procedure within package like:

procedure <package_name>.<procedure_name>
 <procedure_body>

I ask this question as I am newbie and don't understand why I need to compile  whole package when I need only to apply changes form a single procedure...

RELATED

What are the benefits of packages over standalone procedures and functions?
https://stackoverflow.com/questions/1812049/performance-standalone-procedure-vs-packaged-procedure-in-oracle

Tom says:

Always use a package.
Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)


texts:
I need to move my distributor server to a brand new server without resnapshotting it. The scenario I have is similar to the one defined in below link.

http://www.sqlservercentral.com/Forums/Topic844095-291-1.aspx

Environment:

1 publisher
1 distributor
4 subscribers
Replication type: Transactional replication with updatable subscription

I want to know if someone has tried the solution given in above link or if you have any alternate solution.


texts: How is an AAG listener implemented? Where does it run?

texts:
I'm fairly new to SQL Server but generally well versed in RDBMS and network topics. I'm trying to wrap my head around AlwaysOn Availability Groups. I've read a handful of articles on MSDN and Googled liberally.

I think I understand that the AAG listener appears like a service on the network, it has a hostname and/or IP address and a port number.  And if you create it using SQL Server Management Studio (at least), you create it "in" the server instance which hosts the primary replica.

What I don't understand is what happens under the covers.  Is the listener a thread in the server instance which hosts the primary replica? Is it a separate process?  If so, does it run on the same server or elsewhere in the WFC? If it runs on the same server as the primary instance, how does the listener survive failure of that node?  (The data may well be replicated to a synchronous secondary, but if I can't reach the listner, I can't be redirected to that data.)


texts: Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses

texts:
The error in the title is produced when add the second condition in my where clause. I understand the error, have tried using subqueries as seen in other posts to fix this but no luck. Does anyone know how I can filter row_ID = 1 successfully?

Here is my query:

SELECT
    a.database_name ,
    a.collection_time ,
    ROW_NUMBER() OVER (
        PARTITION BY
            CONVERT(DATE, SWITCHOFFSET(
                CAST (a.collection_time AS DATETIMEOFFSET), '+00:00'))
        ORDER BY a.collection_time DESC) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE
    a.database_name = 'MDWDB'
    AND ROW_NUMBER() OVER (
            PARTITION BY
                CONVERT(DATE, SWITCHOFFSET(
                    CAST (a.collection_time AS DATETIMEOFFSET), '+00:00'))
            ORDER BY a.collection_time DESC) = 1