texts: Cast NTEXT to INT for calculation and comparison

texts:
I need to convert an NTEXT column to an INT so I can perform some checks on it.

The first check would be is value = 0, and the second would be if it was an even or an odd number.  I've got this mostly figured out except the conversion of the NTEXT to INT. So far what I have is this:

cast((cast(a.[Value1] as nvarchar(max))) as int) = 0

The column data looks like this '0'.  When I run my query I get:

Conversion failed when converting the nvarchar value ''0'' to data type int.

What are my options to get around this; is there a way to make it ignore the quotation marks?


texts: Postgres 9.3 changes the standard_conforming_strings value

texts:
I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3

In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default value in 8.1: off.

So, I create the databases using following in the schema:

SET standard_conforming_strings = off;

But a pg_dump of the database reveals that Postgres has changed the value to on.

SET standard_conforming_strings = on;

How can I prevent this?


texts: Prepared statement execution with variable number of parameters to be bound

texts:
Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter binding for the values that are to be part of the interpolated criteria.

Since the criteria added to the prepared statement and thus the number of parameters to be bound may differ depending on the user input, I devised the method below to determine which variables will be passed to the EXECUTE statement. This works, but it seems inelegant.

CREATE PROCEDURE foo (IN mandatory INT, IN optional INT, IN optional2 VARCHAR(20))
  BEGIN

    SELECT
      0, '', '', mandatory, optional, optional2
    INTO
      @params, @sql, @where, @m, @o1, @o2;

    IF (@o1 > '' AND @o1 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field = ?');
      SET @params = @params + 1;
    END IF;
    IF (@o2 > '' AND @o2 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field2 = ?');
      SET @params = @params + 3;
    END IF;

    SET @sql = CONCAT('
      SELECT id, bar FROM table
      WHERE
        baz = ?
        ', @where
    );
    PREPARE STMT FROM @sql;
    CASE @params
      WHEN 0 THEN EXECUTE STMT USING @m;
      WHEN 1 THEN EXECUTE STMT USING @m, @o1;
      WHEN 3 THEN EXECUTE STMT USING @m, @o2;
      WHEN 4 THEN EXECUTE STMT USING @m, @o1, @o2;
    END CASE;
    DEALLOCATE PREPARE STMT;

  END$$

I'm aware of alternatives:

The binaries that would call these stored procedures have a function that attempts to identify potential SQL injection by passing the user supplied strings through a regular expression.
A user-defined function could be used to dynamically construct the EXECUTE statement given a dynamic number of inputs.

However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.


texts: Converting a graph database schema to a relational database schema

texts:
I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL.
My concern is that I have lots of relationships between entities (since this is what a graph database is structured as) and I'm not sure the best way to handle these when moving to a relational database.
For example, I have tasks {id, projectid, scheduleid, name, desc, ...} that have a 1:m relationship with:

activities {id, name, startat, data, ...}
other tasks
materials {id, name, desc...}
invoice line items {id, price, qty, ...}
alerts {id, type...}
files {id, name, url}
messages {id, name, createdby, ...}
contacts {id, name, email...}
tags {id, name, ...}
equipment {id, name, ...}

Using the graph database I can start at one or more task nodes and then query all of the directly connected nodes which allows me to grab all of the associated data.
What's the strategy for modeling such highly connected data in a relational database without having to do 15 JOINs to get everything?  I need all the data in order to populate my views and I really don't want to do one query to get the tasks and then 15 more queries to get the rest of the data.
Considerations
I'm considering creating a task_relationship table with {task_id, target_id, target_type, target_name, target_desc} which would contain links to anything that can be related to a task along with some basic denormalized data.
However, I would still need to do JOINs to get additional data for some things and other 1:1 relationships that would be stored with the task such as project information and schedule information.
I also wouldn't be able to use cascading updates when data changes so I would have to handle that via triggers or in code I think.
Questions
Is this a sane approach or is there a better way?
Also, if I have a server with 24 cores and 128GB of memory backed by a 8 disk SSD array - how many JOINs of million row tables can I do before queries start taking more than 50ms?  I know that's impossible to answer but is it more:

“with only a few million rows you don't really need to worry about that”, or
“with a few million rows doing more than three or four JOINs will start to slow things down without very careful schema planning and optimizations”?


texts: Scramble the data output of a stored procedure

texts: I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day without having to manually change the where clause? For examples today's data is : 1,2,3 tomorrow's will be :3,2,1 the day after 2,1,3 .. Same data different order daily.


texts: What are PostgreSQL Security Labels and how/where to use them compared to GRANT?


texts:
I am not getting index seek in the following scenario.
Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the where clause filters on `value()`?, insert 100k rows with this xml <SomeText>NiceText</SomeText> and similar amount of rows of this <SomeText>MoreText</SomeText>. Doesnt need to be 100k. Just needs to be many.
Then add index

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
    pathXQUERY = '/SomeText' as xquery 'xs:string' maxlength(8) singleton
);

And secondary index

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

Then do a count

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1;

Notice that it doesnt use index seek and is "slow". Can take several seconds with millions of rows.
If i insert same values in a standard column and add an index to it and do a

select count(id)
    from dbo.T as T where SomeTextColumn = 'MoreText'

i get results instantly.
All tests done on sql server 18.3.1

The question is, how can i make counting by xml as fast as counting by column?

Thanks


texts: Executing 'GRANT SELECT' in a trigger possible?

texts:
Trying to grant select privilege on table USER1.AAA to USER2 when table USER1.AAA is created, using system trigger.
The trigger below seems to be processed when table AAA is created but it receives errors ORA-00604, ORA-30511.

create or replace trigger aaa_trg
after create on schema
begin
    if sys.dictionary_obj_name = 'AAA' then
        execute immediate 'GRANT SELECT ON AAA TO USER2';
    end if;
end;
/

It works fine when I execute 'INSERT' to AAA.
Is it possible to execute 'GRANT SELECT - ' like this ?

DB is Oracle 12c R2.

Thanks.


texts: Updating a view on multiple joined tables

texts:
Since MSDN does not say much, what happens exactly if I execute the following query?

update claims set status='Awaiting Auth.'
where status = 'Approved'

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key?

The view itself queries multiple tables, the main table is tabData, which I also want to update with above query. I want to change fiClaimStatus in tabData from the FK that means Approved in the referenced table dimClaimStatus to Awaiting Auth.. Does it work this way?

Only one view row is possible for each row in tabData.

Here's the view:

CREATE VIEW [dbo].[Claims]
AS
  SELECT mu.MarketUnitName AS MarketUnit,
         c.CountryName     AS Country,
         gsp.GSPName       AS GSP,
         gsp.WCMSKeyNumber AS GspNumber,
         sl.SLName         AS SL,
         sl.WCMSKeyNumber  AS SlNumber,
         m.ModelName       AS Model,
         m.SalesName       AS [Model-Salesname],
         s.ClaimStatusName AS [Status],
         d.Work_Order      AS [Work Order],
         d.SSN_Number      AS IMEI,
         .... more columns ....
         idData,         -- PK of main table tabData
         fiSL,
         fiModel,
         fiClaimStatus  -- FK to dimClaimStatus
  FROM   tabData AS d
         INNER JOIN locSL AS sl
                 ON d.fiSL = sl.idSL
         INNER JOIN locGSP AS gsp
                 ON sl.fiGSP = gsp.idGSP
         INNER JOIN locCountry AS c
                 ON gsp.fiCountry = c.idCountry
         INNER JOIN locMarketUnit AS mu
                 ON c.fiMarketUnit = mu.idMarketUnit
         INNER JOIN modModel AS m
                 ON d.fiModel = m.idModel
         INNER JOIN dimClaimStatus AS s
                 ON d.fiClaimStatus = s.idClaimStatus
         INNER JOIN tdefProductType
                 ON d.fiProductType = tdefProductType.idProductType
         LEFT OUTER JOIN tdefServiceLevel
                      ON d.fimaxServiceLevel = tdefServiceLevel.idServiceLevel
         LEFT OUTER JOIN tdefActionCode AS ac
                      ON d.fimaxActionCode = ac.idActionCode

Update

Since the table contains 20 million customer records, I wanted to know first what will happen before I execute it. After the comments and answers, I have executed it now. Immediate result was:

(1 row(s) affected)

Which is surprising, because there were several thousands of records in this status which seem to be updated now.

Update 2

Actually it didn't work as expected and the suspicious (1 row(s) affected) was right. Only the referenced table was updated. So now the status Approved changed to Awaiting Auth..

Conclusion:

It seems to be the best way to just avoid using a view to do updates. This works in my case:

UPDATE tabData
SET fiClaimStatus = (SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Awaiting auth.')
WHERE fiClaimStatus=(SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Approved')


texts: RapidMiner Not Connecting To SQL Server Database Connection

texts:
I recently installed RapidMiner and tried connecting the application to my MS SQL database connection via the Manage Database Connections feature. I have followed video tutorials on integrating SQL Server but i keep getting errors on connecting to localhost, and port 1433.

I have checked to see if SQL Server browser was running. It is. My TCP/IP connection is enabled, and also my default port is 1433. So what can be the problem?

I use mixed authentication with SQL Management Studio, i added the same in the Manage DB Connection form. But i keep getting an error:

  "The TCP/IP connection to the localhost, port 1433 has failed. Error:
  Conection refused. connect"

I tried using Microsoft SQL Server (JTDS) as DB system (I downloaded JTDS from Sourceforge),  but i keep getting a

  "Network error IOException: Connection refused:connect"

Please, I'd really appreciate help because it's left me frustrated as i need this application to work for some project i am undertaking.

Thanks


texts: Cannot connect from elsewhere than a console

texts:
I am quite a beginner with MySQL, but I already got problems with setting up a connection. I tried to use MySQL Workbench, but I can't connect to the server. I use SparkyLinux, a Debian-based distribution. Here is what I did so far :

I created a user and checked that I could log in a console :

adrien@sahelantropus:~$ mysql -u adrien -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.23-MariaDB-9+deb9u1 Debian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

MariaDB [(none)]>

(maybe it is interesting to notice I can either give my password or not, and log in with exactly the same output)
I tried to setup a connection with MySQL Workbench, but when I test it, I get the following output :

  Failed to Connect to MySQL at 127.0.0.1:3306 with user adrien
  Access denied for user 'adrien'@'localhost'

I tried to use MySQL Connector in Python, and got the following exception :

  1698 (28000): Access denied for user 'adrien'@'localhost'

I am sorry if something obvious is going on and I cannot see it, and I thank anyone who could help me.


texts: how to resolve this insurance company database

texts:
Company offers 3 types of personal motor insurance to vehicle owners. It
includes the third party insurance, fire and theft, and comprehensive cover. All vehicle
owners need to purchase a motor insurance which covers their vehicles, be it a motorcycle, a
car, or a lorry, in case of accidents or thefts. All motor insurance is identified by a unique
policy number, date of purchase, date of expiry, coverage amount, premium amount and
type of insurance, vehicle identification and owner identification. The third party insurance
covers the liabilities to third party for injury, death and damage, while fire and theft covers
an additional loss or damage to own vehicle due to accidental fire or theft. Lastly, the
comprehensive insurance will cover all components of the previous two plus damage to own
vehicle due to accident.

Is this correct?if not how can I modify it?


texts: Performance of JSONB vs. integer field storage in PostgreSQL

texts:
I need to store some data with values like 0, 1 (true / false). I am considering grouping the different integer fields into one JSONB field with a structure like this:

{
  "status": 1,
  "approved": 0,
  "notified": 0
}

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?


texts:
When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema.

The issue is that our site-specific users currently do not "see" these other schemas even though they are in the db_owner role. So somewhere there is a permissions setting missing which would grant them the ability to read and write against those tables.

This issue has occurred when migrating to a new SQL server. Going from 2005 Workgroup Edition to 2008 Web Edition.


texts: Why does json_equal use so much PGA memory and how to avoid it?

texts:
I have an Oracle 19c database and a schema with two tables (let's call them L and R).  They have the same structure:  Two VARCHAR2 columns I'll call T and J.  The second one has an IS JSON STRICT constraint.  Both tables have almost five million rows.  The largest text in T is 13 characters long and the largest JSON documents in J has 169 characters.
I need to find all rows which exist in L but not in R.  Unfortunately are the fields in the JSON objects not always in the same order.  So I can't compare them for equality but have to use json_equal.  I tried various ways of specifying the query but I always get a ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT.
For example, this query should give me the right answer but runs for a while and then runs into that issue:
select
  L.T,
  L.J
from L
left join R
on
  L.T = R.T
  and json_equal(L.J, R.J)
where R.J is null

OTOH, this one is quick but obviously has some false positives:
select
  L.T,
  L.J
from L
left join R
on
  L.T = R.T
  and L.J = R.J
where R.J is null

I couldn't find anything on this topic in the Oracle JSON Developer's Guide.  I tried adding a fulltext search index to the J columns but that didn't change anything.
I wonder why this query requires so much PGA memory.  It probably has to create temporary JSON objects internally but should be able to free them again quickly.  I do have a trigger which executes a
update L
set
  D = json_mergepatch(D, :NEW.D)
where (
  T = :NEW.T
  and json_equal(J, :NEW.J)
);

and doesn't run into this issue (yet?).
And finally and more importantly, how does one avoid this issue apart from upgrading to Oracle 20c and using the new JSON data type?


texts: SpringData MongoDB - infinite getmore on mongo logs

texts:
Doing 2 queries on MongoDB through Spring Data on around 160 000 rows, query 1 is executed in few seconds (about 20 results) but query 2 never finish. I'm getting this line over and over on mongodb log:

Sun Apr 27 13:59:06.507 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 1 locks(micros) r:255198 nreturned:16 reslen:4264353 133ms
Sun Apr 27 13:59:16.125 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 3 locks(micros) r:242018 nreturned:16 reslen:4264832 131ms
Sun Apr 27 13:59:25.748 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 4 locks(micros) r:240837 nreturned:16 reslen:4264885 135ms
Sun Apr 27 13:59:35.496 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:195126 nreturned:16 reslen:4265303 114ms
Sun Apr 27 13:59:45.418 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 4 locks(micros) r:230168 nreturned:16 reslen:4265473 126ms
Sun Apr 27 13:59:55.007 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:228439 nreturned:16 reslen:4265817 135ms
Sun Apr 27 14:00:13.490 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:180845 nreturned:16 reslen:4265940 102ms
Sun Apr 27 14:00:32.978 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:132410 nreturned:16 reslen:4265572 106ms
Sun Apr 27 14:03:06.571 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:205062 nreturned:16 reslen:4266662 107ms

Query 1:{ annualPercent: { $gte: 10, $lte: 1000000 } }

Query 2:{ annualPercent: { $gte: 5, $lte: 10 } }

I'm doing a big update of every rows just before theses queries, should I leave time to mongodb to re-index all ?

What the logs means ?

And how to solve that ?(I'm not using any special features of mongo driver or spring data)


texts: How SQL Server transaction works with log records

texts:
My textbook says:

Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it looks real to you, only you can see that
result. Anyone else attempting to look, they could see the old value, or if they’re daring they could get a dirty read.

I am confused, so I will use a picture below to raise some questions:

and the t-sql code is

BEGIN TRAN
UPDATE checking
 SET Balance = Balance - 1000
 WHERE Account = 'Sally'    // original balance is 2000
--------------a checkpoint occurs ---------
UPDATE savings
 SET Balance = Balance + 1000
 WHERE Account = 'Sally'
COMMIT TRAN

and we know that a new log record describing the COMMIT will only be created in the log buffer after 'COMMIT TRAN' has been executed.

Q1- Why the result is only visible for me before COMMIT?
let's say a checkpoint happens after checking for sally has been minus -1000, and let's the the updated record is in page 4(in memory), since it hasn't been commit yet, so there won't be a log record in the log buffer, therefore there won't be a corresponding log record in disk(.ldf), but the cache page 4 will be written to disk (.mdf), so sin't that any user can see the latest change for the record?

Q2-How transaction control maintains atomicity in this case?

As I have discussed, when the checkpoint happens,  page 4(in cache, and the containing record's latest balance value: 1000) will be written to disk, and if there is a system failure immediately. After SQL server restarts, how does it know how to restore the record's balance(currently 1000) back to its original value(balance is 2000), since there is no log record in .ldf file?


texts: Filter rows by range dates and only get the rows in a range of hours

texts:
I want you to filter the rows between a date range but I only want you to return the rows of a time range of each of the days included in the previous date range.

For example, you would like to filter out all the rows between March 1 and March 3, 2020 and also only those between 14:00 and 18:00.

So far I can filter between two dates or between two hours but it doesn't work when I put the two things together. I don't get any records.

and BEGIN_INTERVAL_TIME BETWEEN to_date('19/04/2020', 'DD/MM/YYYY') AND to_date('20/04/2020','DD/MM/YYYY')
and BEGIN_INTERVAL_TIME BETWEEN to_date('15:00:00', 'HH24:MI:SS') AND to_date('16:00:00','HH24:MI:SS')

Regards,


texts: Using xtrabackup-v2 as SST method in MariaDB Galera cluster

texts:
I have configured mariadb galera cluster as docker swarm service using official docker image:
during cluster startup rsync (default SST method) works fine.
Wanted to change it to xtrabackup or xtrabackup-v2, so used the following configuration in my.cnf according to docs:

[mysqld]
wsrep_sst_auth = <wsrep_sst_user>:<password>
wsrep_sst_method = xtrabackup-v2 (or xtrabackup)
datadir = /var/lib/mysql/

[client]
socket =/var/run/mysqld/mysqld.sock

donor server starts fine, but getting the errors below during joiner server startup:

  2018-05-07 13:08:26 139750451541760 [Warning] WSREP: Gap in state sequence. Need state transfer.
  2018-05-07 13:08:26 139750232831744 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  '' '
  WSREP_SST: [INFO] Logging all stderr of SST/Innobackupex to syslog (20180507 13:08:26.937)

  2018-05-07 13:08:29 139750249850624 [Note] WSREP: (fdead878, 'tcp://0.0.0.0:4567') turning message relay requesting off

  2018-05-07 13:10:07 139750232831744 [ERROR] WSREP: Failed to read 'ready ' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  ''
          Read: '(null)'

  2018-05-07 13:10:07 139750232831744 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  '' : 32 (Broken pipe)

  2018-05-07 13:10:07 139750451541760 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
  2018-05-07 13:10:07 139750451541760 [ERROR] Aborting

  Error in my_thread_global_end(): 1 threads didn't exit

Other wsrep values:

wsrep_on=ON
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm:// (for first node to start, and virtual_ip of the first node on 2 other nodes)

How to configure xtrabackup as SST method in MariaDB correctly?


texts: Does SQL Server / T-SQL support line-continuation to break up long strings?

texts:
I sometimes have a SQL script that has one or more super-long (sometimes even stupid-long) strings. Typically these are VARBINARY literals / constants that represent files / Assemblies, but occasionally they are text.

The primary problem with really long strings is that some text editors do not handle them all that well. For example, I have a VARBINARY literal that I use in a CREATE ASSEMBLY [AssemblyName] FROM 0x.... statement, and the Assembly itself is just over 1 MB in size, which equates to just over 2 million characters in a text file since each byte requires two characters to be represented in hex notation (e.g. 0x1F = a 1 and an F). SQL Server Management Studio (SSMS) does not handle this well and hangs for several seconds as I try to scroll over that line. And in fact, some versions (not sure if this still happens) will even display a warning about long lines when opening a script that has at least one line over a certain length.

A secondary issue is that it complicates the formatting when either using in an editor without word-wrap enabled, or posting online. The problem here is that the slider for the horizontal scroll bar is very narrow and moving it even just a little bit usually scrolls the non-super-long text out of view.

Now, T-SQL doesn't terminate commands with newlines or even semi-colons (though semi-colons are preferred / recommended, starting with SQL Server 2005). So since SQL Server knows how to parse each statement such that it knows when it ends, it seems like splitting the long line across multiple lines, separated only by a newline / carriage-return + line-feed, doesn't seem unreasonable. But this doesn't work in either case.

PRINT 'Line1
Line2';

returns (in the "Messages" tab):

Line1
Line2

And that makes sense enough as the newline is within a literal / constant. But doing this for a VARBINARY also doesn't work.

PRINT 0x1234
5678;

gives me an error.


texts: High disk usage by Windows Server when executing SSMS

texts:
Every time we execute SSMS on Windows Server, there is a high disk usage System process for ntuser.dat.LOG, as seem at Resource monitor:

The write rate is between 2 MB/sec and 8 MB/sec.

Why do we have such high disk usage by Windows system process when running SSMS?

SSMS version: 11.0.3368.0 (SQL Server 2012 Standard)
SQL Server engine: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)   May 22
2013 17:10:44    Copyright (c) Microsoft Corporation     Standard Edition
(64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)
Operating system: Microsoft Windows 2012 R2 Standard 6.3.9600


texts: MS SQL, fastest way to delete 99% of rows from 100+ million rows table to deduplicate data

texts:
Due to an error in our application, one of the tables in our MS SQL has been flooded with duplicate rows differing only in PK identity column and timestamp

+--------+---------------------+--------------------+
| id     | time                |       value        |
+--------+---------------------+--------------------+
| #10003 | 2014-02-26 12:00:00 |   uniq_pattern_1   |
| #20005 | 2014-02-26 13:00:00 |   uniq_pattern_1   |
| #30018 | 2014-02-26 14:00:00 |   uniq_pattern_1   |
| #40022 | 2014-02-26 15:00:00 |   uniq_pattern_1   |
 ....................................................
| #10005 | 2014-02-26 12:00:00 |   uniq_pattern_2   |
| #20008 | 2014-02-26 13:00:00 |   uniq_pattern_2   |
| #30020 | 2014-02-26 14:00:00 |   uniq_pattern_2   |
| #40040 | 2014-02-26 15:00:00 |   uniq_pattern_2   |
+--------+---------------------+--------------------+

There is 100+ million rows with only about 50k unique patterns spread across entire table (every existing pattern was re-inserted every hour, with a few new patterns added to the mix). Table only has clustered PK index. I need to delete all duplicates leaving only earliest occurrence of each pattern.

I plan to do the following:

Grab earliest row for each uniq pattern into temp table.

INSERT INTO #MyTempTable
SELECT [value], min([time])
FROM [MyBigTable]
GROUP BY [value]

Truncate [MyBigTable] then populate it from #MyTempTable.

Is this a good way to do this?


texts: How to select nodes where all children is satisfied?

texts:
I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.

The relation between the nodes in the tree structure is represented by table A:

Table A:

  node_id integer
  child_node_id integer

Table B represents the nodes in the tree:

Table B:

  id integer
  state boolean

The state of table B represents the states true = on and false = off.

Question:
I would like to select all the light bulbs which are turned off AND has all immediate children turned on.

This is probably very simple, but I can't seem to get my head around it.


texts: Oracle full expdp/impdp but skip data in some tables

texts:
I'm trying to export a schema and then import it, but there are some huge tables that I must skip all the data from (that is, I need the tables and all stuff related to them, just not the rows).

So I thought I'd do

1) expdp SCHEMAS=X CONTENT=METADATA_ONLY DUMPFILE=meta.dmp
2) exportSCHEMAS=X CONTENT=DATA_ONLY EXCLUDE=TABLE:"IN \('FOO','BAR'\)" DUMPFILE=data.dmp

and then

4) impdp SCHEMAS=X EXCLUDE=REF_CONSTRAINT DUMPFILE=meta.dmp
5) impdp SCHEMAS=X DUMPFILE=data.dmp

The plan is in step 4 to get all the meta data, and then in step 5 import all data but the rows in the large tables FOO and BAR,
But how can I get the foreign keys?

Is this a way to do it, or are there other ways?


texts: looking for missing indexes in the cached execution plans

texts:
I have the following query that shows me what is cached in sql server:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 1=1
  --AND OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
  AND query_plan IS NOT NULL
ORDER BY ExecutionCount DESC

Is there a way I could query the queryPlan field looking for missing indexes?


texts: DB2 (LUW) equivalent for Oracle "drop user cascade"

texts:
This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows)

On oracle, we have

an os user (on linux) called oracle,
an admin oracle user called    sys or system
a fixed predefined oracle user that is the    application object
owner, let's call it appowner
an oracle user that is the application user, ie has limited
previleges on the    objects owned by appowner, let's call it
appuser

whenever there is a need to initialise the database, ie to start from scratch, we first connect to oracle using sys or system oracle user and then issue this command:

DROP USER appowner CASCADE;

and then we go about recreating the user and the objects it owns from scratch. We also grant some previliges to appuser on the objects created by appowner

The application always logs in as appuser rather than appowner unless there are any administrative tasks to be performed on the database.

Now we are porting this application to db2, and this is where we are flummoxed.

For starters, db2 creates these os users, which are also db2 users:

dasusr1
db2inst1
db2fenc1

How do these three users map to sys/system, appowner and appuser?

I believe dasusr1 is the rough equivalent of sys/system,
db2inst1 is the rough equivalent of appowner, and
db2fenc1 is the rough equivalent of appuser

(please correct me if I'm wrong, and I fully appreciate that the mapping will not be exact)

That being the case, if I have to remove all objects owned by db2inst1, do I login as dasusr1 and drop the user db2inst1?

There isn't really a one to one mapping between the oracle users and db2 users, because

db2inst1 can create multiple databases whereas appuser is mapped to one database in oracle
within a db2 database, there can be multiple schemas, whereas in oracle, one users maps to one schema

So it's a little confusing. It would help if someone with experience in porting applications from oracle to db2 could throw some light on the equivalence between the db2 and oracle users, which would finally lead to finding out the db2 equivalent for oracle drop user cascade.


texts: Multi-row insert in many to many relationship in PostgreSQL

texts:
I have got three below tables:

create table r_users(id int primary key, name varchar(30));
create table r_codes(id int primary key, name varchar(10));
create table user_code
(
user_id int,
code_id int,
constraint pk_user_code primary key(user_id, code_id),
constraint fk_user foreign key(user_id) references r_users(id),
constraint fk_code foreign key(code_id) references r_codes(id)
)

Now I have some users and codes in the tables:

insert into r_users values(1, 'John'), (2, 'Roy'), (3, 'Sam'), (4, 'Lewis');
insert into r_codes values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'K');

Now I want to insert the codes A, B, C, K to users John, Roy and Lewis such that the mentioned codes are inserted for each of the mentioned users as below:
The desired result is:

user_id | code_id
1       |     1
1       |     2
1       |     3
1       |     5
2       |     1
2       |     2
2       |     3
2       |     5
4       |     1
4       |     2
4       |     3
4       |     5

Any help is appreciated in advance.


texts: Does oracle client 12c requires Java on windows

texts:
if I install Oracle 12c client on window server, does it get Java installed?

can we install oracle driver/client without Java?


texts: What can cause a static SQL query against a static data set take vastly different amount of time to complete?

texts:
I have this very strange issue on one of the production servers - a parameterless stored procedure is either taking 1-2 minutes or 45+ minutes to finish, and it seems completely random and not following any pattern. Here are the specific things:

I am doing all testing on my own copy of the production database, on the production server. Which means that the data is completely static, there are no other processes against the database, the query itself is also completely static (no parameters). The data set it is working with are 3 tables, 24 rows, 80000 rows and 4.5 million rows. The query was working perfectly fine on the old server, which had half the memory and CPU of the current one, and was running 2008 R2 (current build is 2014). The databases are exact copies of the old ones.

What I have tried so far:

Create additional indexes, rebuild the ones I have, update statistics
Set the compatibility level to 100
Force the query to use the cardinality estimater of 2008 R2
Force the query to run with different MAXDOP settings - 0,1,4,8,16,28 (we have 28 physical cores with hyperthreading, so 56 logical)
Compared execution plans for when it runs for 1 minute and when it runs for 45 minutes - they are completely identical, word for word, value for value.

I am completely out of ideas at this point and I am starting to believe that I am missing something very simple. Unfortunately, I cannot provide you with the query as I believe it is confidential.. Is it possible to give me any ideas without the query? As my last resort, I thought of forcing the query to use the execution plan, that is created when it runs for 1 minute.

UPDATE: Now other stored procedures started behaving like that, however I can now see a common thing. Here is the "theme" of all failing SPs:

They consist of three statements, all of which update 3 columns.
Statement 1 - bunch of nested loops, a correlated subquery, etc. - finishes every time
Statements 2 and 3 - absolutely the same as statement 1, however using a different currency rate and most importantly - they are updating only NULL values, which the previous statement left. I tried creating a filtering index, but it didn't help again.


texts: STRING_AGG Not Recognized on V15.0.2 of SQL Server LocalDB

texts:
I recently coded a stored procedure on SQL Server 2019 (14.0.3192.2) in AWS that utilized the function STRING_AGG with no issues. When I moved the code to my local test db it complained about that method.
Upon research I proceeded to update my local sql server db service from 14.x version to the latest 15.0.2 and tried to install the sproc again; to the failure

Incorrect syntax near '"'',STRING_AGG(ActionDATE, ''","''),''"'.

This feature has been viable since SQL Server 2017...so being missing in the local version of 2019 is somewhat puzzling.
Question
What recourse do I have besides installing SQL Server 2019 Developer edition to resolve this issue?

Intention
Note that the failure line with STRING_AGG is trying to create a JSON sub array of dates such as
[ '01/24/2020', '01/28/2020' ]:
Code
CREATE PROCEDURE [hcwfba].[GetOperationsAndActionDates]
    @operationId int,
    @locationId int
AS

WITH  [DATA] AS (SELECT DISTINCT id_Operation, id_Location , CONVERT(varchar, ActionDATE ,101) AS [ActionDATE] FROM OperationDetail)

SELECT distinct
         PD.id_Operation
       , PD.id_Location
       , JSON_QUERY((SELECT CONCAT(''["'',STRING_AGG(ActionDATE, ''","''),''"]'') FROM [DATA] DT2 WHERE DT2.id_Location = PD.id_Location)) As Dates
FROM [DATA] PD
WHERE PD.id_Operation = @operationId
AND PD.id_Location = @locationId
FOR JSON AUTO;

RETURN @@ROWCOUNT

JSON Result
[
    {
        "id_Operation": 13,
        "id_Location": 8675309,
        "Dates": [
            "12/03/2018",
            "08/27/2018",
            "10/22/2018"
        ]
    }
]


texts: Version Store usage and entries in dm_tran_active_snapshot_database_transactions

texts:
So I'm trying to understand why are having occasional problems with tempdb filling up with version store entries.
I'm closing in on the problem, and am now seeing entries in dm_tran_active_snapshot_database_transactions, but I don't understand why the entries are there.
The databases in question do not have ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT enabled.
Checking the transaction via dm_tran_active_transactions I can see the following properties:

transaction_begin_time (90 minutes ago)
transaction_type 1
transaction_state 2
transaction_status 0
transaction_status2 258
dtc_state 0
dtc_status 0
dts_isolation_level -1

Any ideas how to troubleshoot this in a methodical manner?


texts: Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

texts:
In the official documentation of Boolean Full-Text Searches:

Boolean full-text searches have these characteristics:

They do not automatically sort rows in order of decreasing relevance.

However, when running full-text searches IN BOOLEAN MODE (mysql v8.0.23), they are sorted by relevance in order of decreasing relevance without having to use ORDER BY score
SELECT product_category_id,
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE) as score
FROM product
WHERE
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE);

Result:
+---------------------+--------------------+
| product_category_id | score              |
+---------------------+--------------------+
|                  97 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |

What have I missed here?


texts: Does the Postgres 9.4.9 update fix MultiXactId "does no longer exist" in addition to "has not been created yet" errors?

texts:
Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine):

Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew Gierth, Álvaro Herrera)
The usual symptom of this bug is errors like "MultiXactId NNN has not been created yet -- apparent wraparound".

I am currently chasing errors in PG 9.4.5 which read:
2019-10-19 15:37:17 EDT [13024]: [1-1] ERROR:  MultiXactId 1074790449 does no longer exist -- apparent wraparound

Does the above fix in PG 9.4.9 address the error I am getting?


texts: Heidi SQL 'Cannot close tab with running query'

texts:
I have a HeidiSQL query tab which I cannot close, where the execute button is greyed out.

I have searched for this and the recommendations are to show process list and then kill the queries found in it. However when I run show process list all it shows is the process list query itself. Also the status bar in the bottom right is Idle, which according to other answers I've read should mean nothing is running.

Any help would be appreciated.


texts: Maintenance plan to backup differentials hourly each to a new file name in SQL Server 2012

texts:
In addition to a nightly full backup, I'd like to set up an hourly differential backup maintenance plan in SQL Server 2012, where each differential backup file should receive a different name. It seems this isn't possible. Am I overlooking something simple?

In the GUI interface, my choices are either to append to the target differential backup file, or to overwrite it. Is there a way to append  _{YEAR}{MO}{DY}{HH} to the differential filename?


texts:
I am using merge replication with SQL 2012.

I am writing a custom business logic handler, and would like to know if there is a way to get a session id?

For instance if the custom business logic handler catches an insert into a specific table, and then the custom business logic handler catches an insert into another table, how can you tell if they are part of the same session?

Also by session_id I am referring to something like the session_id which is in the MSMerge_sessions table.


texts: ETL Job Issue - Works for One Server and Not for other

texts:
I have an ETL job which connects to 2 Servers.

Recently it is acting strange with ETL running successfully for 1 server and not for 2 server.

This is with one particular code which has link up to few tables. Runs successfully for one server and not updating for other server.

Please help us to understand what might me gone wrong way.

Here are my observations:

No Blockings
No Tasks running parallel .
It has proper linked server permissions (Read access to all DBs)

Here are the wait type values for OLEDB for server 1 and 2.

Server 1 :

Server 2:


texts: Many to Many Link Table Return full chain from one record

texts:
I am looking to run a query on a many to many link table which chains records together and displays all the linked records. I've included a simplified data set for copy/paste as an example.

I have a table of objects that include a code and a description which are linked to each other using a link table. I want to pass in a code from one object and return all the objects in the chain that includes it. I can pull back results directly linked to the input, but I am struggling to extend this to include all links in the chain. Any help would be much appreciated.

Thanks, D.

-- Create Tables
DECLARE @TestObject TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Code NVARCHAR(8) NOT NULL,
Description NVARCHAR(30) NOT NULL
)

DECLARE @TestRelationships TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Link1 INT NOT NULL,
Link2 INT NOT NULL
)

-- Populate Object Data
INSERT INTO @TestObject VALUES ('TEST1111', 'Test Object 1')
INSERT INTO @TestObject VALUES ('TEST2222', 'Test Object 2')
INSERT INTO @TestObject VALUES ('TEST3333', 'Test Object 3')
INSERT INTO @TestObject VALUES ('TEST4444', 'Test Object 4')
INSERT INTO @TestObject VALUES ('TEST5555', 'Test Object 5')
INSERT INTO @TestObject VALUES ('TEST6666', 'Test Object 6')
INSERT INTO @TestObject VALUES ('TEST7777', 'Test Object 7')
INSERT INTO @TestObject VALUES ('TEST8888', 'Test Object 8')
INSERT INTO @TestObject VALUES ('TEST9999', 'Test Object 9')

-- Populate Link Chain 1
INSERT INTO @TestRelationships VALUES (1, 2) -- Links 1 AND 2
INSERT INTO @TestRelationships VALUES (1, 3) -- Links 1 AND 3
INSERT INTO @TestRelationships VALUES (1, 4) -- Links 1 AND 4
INSERT INTO @TestRelationships VALUES (3, 5) -- Links 3 AND 5
INSERT INTO @TestRelationships VALUES (5, 6) -- Links 5 AND 6

-- Populate Link Chain 2
INSERT INTO @TestRelationships VALUES (7, 8) -- Links 7 AND 8
INSERT INTO @TestRelationships VALUES (8, 9) -- Links 8 AND 9

-- Select Records (inc. some results)
-- TEST1111: Shows 1, 2, 3, 4 (Required to show 1, 2, 3, 4, 5, 6)
-- TEST9999: Shows 8, 9 (Required to show 7, 8, 9)

DECLARE @input NVARCHAR(8)
SET @input = 'TEST1111'

SELECT DISTINCT O1.* FROM @TestObject O1
JOIN @TestRelationships TR
ON O1.ID = TR.Link1
OR O1.ID = TR.Link2
JOIN @TestObject O2
ON (O2.ID = TR.Link1 AND O2.ID <> O1.ID)
OR (O2.ID = TR.Link2 AND O2.ID <> O1.ID)
WHERE O1.Code = @input OR o2.Code = @input


texts: Setting MAX memory for SQL servers depending upon the model of CPU

texts:
I have a general question regrading setting up the MAX SQL memory settings-

For our current CPU with below specs, server team bumped up OS memory from 400GB to 1TB:

Intel® Xeon® Processor E5-4640 v2 - It's a 4 socket 10 core per processors with HT enabled so total of 80 logical processors 4 NUMA nodes

Looking at the specs on intel site here max memory supported seems to be 768.

So what does that exactly mean in terms of SQL space? even OS has 1 TB should one be setting MAX memory settings based on 1 TB or 768 GB? We can see issues from SQL when trying to bump up the MAX memory settings lets say over 850 and when we lower it back to 400 goes back to normal.

Issues when bump up SQL MAX memory - App time outs for queries running over 3 secs based on time out settings.

Tested this couple of times and looks to be same. Need to understand why

Appreciate your inputs on this.


texts: galera cluster how to run an ALTER without using RSU/TOI

texts:
I need to run an alter statement but would like to prevent locking/stall the cluster. The Galera docs advised using:

SET wsrep_OSU_method='RSU';

Then Run the ALTER statement, once finich:

SET wsrep_OSU_method='TOI';

Problem is that this needs to be run in every node, and I have only access to one node behind a load balancer, any alternatives or workarounds?


texts: Can I replicate a database from SQL Server 2012 to SQL Server 2014 (with 2014 distributor)?

texts:
I am planning to have database replication running from SQL Server 2012 (publisher) to SQL Server 2014 (both distributor and subscriber) -- pull replication.

Has anyone done this type of cross-version replication and knows if there are any issues with it?

Thank you!


texts: what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database?

texts: i have 2 database files under a primary file group, one is set to restricted auto growth (35 GB), the second database file from a same file group but storing in different drive from first one and kept in  unrestricted auto growth. is this bad practice ? is both should be symmetric in properties ?


texts: InnoDB internals - checkpoint, LSN, dirty pages

texts:
I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage.
However there isn't enough information to answer my question.

When I run show global status, I can get Innodb_buffer_pool_pages_dirty and  Innodb_buffer_pool_bytes_dirty.

I want to know where this information comes from. Does any memory (such as buffer pool or information schema) keep this information and just show them when a query comes? Or does a logical object calculate them whenever a query comes? If so, what is the basic information which could be the materials to calculate?

When I run SHOW ENGINE INNODB STATUS\G, mysql shows the following information:

LOG
---
Log sequence number 9682004056
Log flushed up to   9682004056
Last checkpoint at  9682002296

I also want to know where this information comes from. Especially the "Last checkpoint at". As I researched, the log sequence number is related to the "Log Block Header Number" of InnoDB log blocks in the redo log files. Is this right? Then how does MySQL convert it to a log sequence number and how does it get the base information for them?


texts:
I have a table having datetime column partitioned by RANGE

  (DAYOFYEAR(datetime_col))

By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week inserts.

I.E. I use Mysql 5.6.19-67.0, the partitions are by week

(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (71) ENGINE = InnoDB,
...
....
 PARTITION p49 VALUES LESS THAN (344) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (351) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Now I want to retain the structure by removing 4th week prior DATA ONLY . But I want to see the structure there WITHOUT data for that partition.

  1st Option: alter table t1 truncate partition p4

  2nd Option: Drop the partition and add partition to recreate it.

Issue in 1st Option: I'm afraid to issue this, as this doesn't drop the partition and recreate it. It goes for delete from partition where datetime_col < '2013-11-12 00:00:00'. Not sure how much CPU or undo buffers this will create in a 500G partition table.

Issue in 2nd Option: No issues, but it just won't work.

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

Anyother recommendations are most welcome!!


texts: MySQL insert into different databases tables based on PK value

texts:
Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and Id%2=1 goes to db2.user_feedback.

Is there a condition in the INSERT statement that can allow me to do this? How can i achieve simply achieve this, both if the two databases are located on the same MySQL server, and when they both reside on a different MySQL server?

** UPDATE **
Ok, what i am trying to achieve is to have several tables in the same MySQL server to shard data and limit one table from becoming too big, hence slowing up queries on this table. The table is expected to grow very large (billion records) with heavy writes. That is the reason we have decided to resort to sharding. The table does not currently exist, but we want to create this shard solution in advance before we hit production with this table.


texts: mongodb moving value from NumberInt to NumberLong

texts:
We are trying to preallocate documents in a collection. Usually NumberInt for our values is sufficient. When we get 1million writes into that document (it is one doc per day time series, so 1 million is possible), then the value moves from NumberInt to NumberLong.

We are planning to disable padding, so any single byte movement will move the document.

The question is, whether mongodb will move the document if the NumberInt changes to NumberLong


texts: Character Strings related : string_units db cfg changed from SYSTEM to CODEUNITS32

texts:
To handle few special characters, we changed string_units database configuration parameter from default SYSTEM to CODEUNITS32.

Based on IBM DB2 Knowledge Center, there are three methods to achieve this.

1.Set the NLS_STRING_UNITS global variable

2.Update the string_units database configuration parameter

Alter the column by defining new character set ( CODEUNITS16 or CODEUNITS32)

We have used the second option and restarted the instance. Still it's not working as expected. Have we missed any steps?

Is the reorg required on the table? What happens to the already existing data ?

db2level - 10.5 fp5
OS : Windows


texts: Linked Server along Oracle 32 and 64 bit Oracle Client

texts:
As we all know that to connect from SQL Server to Oracle we need to Install Oracle Client at SQL End. But since I am stuck in one issue which has made me wondered about how things work behind the scenes in case of Linked Server. I have few questions and wanted clarification

Scenario: we have SQL Server 2008 64 bit running on machine. Also we have 32 and 64 bit Oracle client installed. We have a SQL Job under which a DTS package is running using 32-bit environment. Inside package, SQL is connecting to Oracle. Our job is failing. My questions are:

So when I run the job and it is connecting Oracle Server, will it use Linked Server or will directly use Oracle 32-bit client?
If linked server is failing will that can be the reason for job failure. Error message when Testing Linked Server is:
ORA-12154: TNS: could not resolve the connect identifier specified
Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?
After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server?

I can provide more inputs for more clarifications. Any help is appreciated.


texts: Select which has max date or latest date

texts:
Here are two tables.

SCHOOL_STAFF

SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
=================================================================
ABE           Principal         24-JAN-13               111222
ABE           Principal         09-FEB-12               222111

PERSONS

PERSON_ID + NAME
=================
111222      ABC
222111      XYZ

Here is my oracle query.

SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID
FROM SCHOOL_STAFF
WHERE STAFF_TYPE_NAME='Principal'
GROUP BY SCHOOL_CODE, PERSON_ID
ORDER BY SCHOOL_CODE;

which gives this results

LAST_UPDATE SCHOOL_CODE PERSON_ID
===========+===========+=========
24-JAN-13   ABE         111222
09-FEB-12   ABE         222111

I want to select the first one for the school which has latest date.

Thanks.


texts:
I have 2 SQL Server Databases A and B, and I want to synchronize (continuously, every 10 minutes, max) the data from 70-80% percent of the tables from A to B (just A -> B, B is a read only database), but with some constraints:

The schemas of the tables will 99% the same - tables from database B will have and extra DeletedDateTime column. This means that deleting an entry from a table in database A should result in a soft delete in that same table in database B.
There some transformations might be needed on some cells before moving the data: like changing the reference system of some latitude and longitude values
Any change in database A should be reflected in database B in 5 - 10 minutes
I have to use SQL Server 2014 Standard Edition, so features like "Change data capture" aren't available. Upgrading to Enterprise edition or using a newer version of SQL server isn't an option unfortunately.

Now, I've thought of possible ways of doing this, but given that SQL Server isn't really my area of expertise, I'm looking for some suggestions:

Use SQL Server Change Tracker along side SSIS. This seems like it's doable.
Any commercial products that would do this pretty much out of the box? Red Gate, ApexSQL, etc.?
Custom .NET implementation with Windows Services - this one I don't really want to do.
Any other suggestions?

This doesn't sound like such an exotic scenario and I'm pretty sure someone has done it before.

I'm just looking to be pointed in the right direction :)

Thank you


texts: Mysql Incremental Backup and point in time recovery

texts:
I'm writing a shell script using the concept of binary logs for point in time recovery.

Now, the scenario is such that a full-backup will happen every alternate day and Incremental backup should be done in between.
Suppose full-backup happens on Monday and Wednesday, incremental backup should happen on Tuesday.
For point in time recovery, I'm using mysqlbinlog --starttime --stoptime binary-logs > backup.sql
In the shell script I need to use starttime and log position of the previous full-backup to specify the log files in the statement, how can I retrieve this in a shell script and record the changes done during that time.
Is there anyway I can do this, please help me in this regard.


texts: SQL View for Items not sold last six months

texts:
I want to create a view where I would like to get a list of Items that are not sold in last 6 months. I tried couple of approaches but none is working correct.
Please guide me. Here are 2 approaches:
I cannot make temp tables as this is a view?

select distinct a.ItemID, a.TranDate Into #Itemslast6months
from SalesDataMart a
where TranDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

Select distinct m.ITMNO_0 into #ItemsNotSoldLast6Months
from ITEMMASTER  as m
Where not exists (select ItemID
                    from #Itemslast6months as B
                    where m.ITMNO_0 = B.ItemID)

Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]

2nd Approach:

 WITH ItemsSoldLast6Months (ItemID, TranDate)
      AS
      (
        select distinct a.ItemID, a.TranDate
        from SalesDataMart a
        where TranDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

      )

  WITH ItemsNotSoldLast6Months (ItemNO)
      AS
      (
      Select distinct m.ITMNO_0
from ITEMMASTER  as m
Where not exists (select ItemID
                    from ItemsSoldLast6Months as B
                    where m.ITMNO_0 = B.ItemID)

    )

Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]


texts:
I want to use regexp_replace() to replace the string

  name1-01.name2-02.1

to

  name1.01-name2.02.1

How to use the regex here?


texts: Delete relations efficiency in PostgreSQL

texts:
I've got a collection of duplicate relations of relations that I need to tidy up.

The table might be described like this

posts
id: 1

impressions
id: 1, post_id: 1, created_at: 2020-01-02
id: 2, post_id: 1, created_at: 2020-01-02
id: 3, post_id: 1, created_at: 2020-01-03

demographic_distribution
id: 1, impression_id: 1
id: 2, impression_id: 1
id: 3, impression_id: 2
id: 4, impression_id: 2
id: 5, impression_id: 3

I intend to remove the impression with id 1, but due to foreign key constraints, I would need to remove the related demographic_distributions first.

From demographic_distributions I would want to remove ids 1 & 2.

The following query works, but it's not very efficient. I have around 78 millions rows in the demographic_distribution table, and I have other similar sized related tables.

DELETE FROM demographic_distribution
WHERE impression_id NOT IN (
  SELECT id
  FROM impressions AS i1
  WHERE id IN (
    SELECT MAX(id)
    FROM impressions AS i2
    WHERE i2.post_id = i1.post_id
      AND i2.created_at::date = i1.created_at::date
    GROUP BY i2.post_id
  )
);


texts:
How do I group a result set from a query into n groups?

In my particular case, I want to spit the result into 10 groups so I can do some SUM and AVG on each group.

For example, if I ran this query:

SELECT
    `user`,
    COUNT(`user`) AS `times_banked`,
    SUM(`token_change`) AS `tokens_banked`,
    AVG(`token_change`) AS `average_single_bank`
FROM `wp_tokens_log`
WHERE `action` = 'yeokens_banked'
    AND `day` BETWEEN '2018-05-31' AND '2019-05-31'
GROUP BY `user`
ORDER BY `times_banked` DESC

In my case, it returns ~250K rows and I want to group the top ~25K rows then the next ~25K rows and so on.

Ideally, I'm looking for a query that doesn't have to be changed based on the number of rows in the set and bonus points if a query can take any number (n) to do the grouping. For example, if I wanted to now get 20 groups rather than 10.


texts: Trying to disable xp_Cmdshell and rpc_out and and receiving mixed results

texts:
I'm trying to disable xp_Cmdshell and rpc_out and when I run the commands on query analyzer it shows it's disabled.

But after this I need to run a security scan report which provides me the following report that it's has not been disabled. Can anyone help me?

  5 Microsoft SQL Server Database Link Crawling Command Execution

  QID:
  19824

  Category:
  Database

  CVE ID:
  -

  Vendor Reference
  -

  Bugtraq ID:
  -

  Service Modified:
  02/20/2013

  User Modified:
  -

  Edited:
  No

  PCI Vuln:
  Yes

  THREAT:
  Microsoft SQL Server is exposed to a remote command execution vulnerability.

  Affected Versions:
  Microsoft SQL Server 2005, 2008, 2008 R2, 2012 are affected.

  IMPACT:
  Successful exploitation could allow attackers to obtain sensitive information and execute arbitrary code.

  SOLUTION:
  There are no solutions available at this time.
  Workaround:
  Disable RPC_Out and xp_cmdshell for this issue.

  COMPLIANCE:
  Not Applicable

  EXPLOITABILITY:
  There is no exploitability information for this vulnerability.

  ASSOCIATED MALWARE:
  There is no malware information for this vulnerability.

  RESULTS:
  C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe Version is 2009.100.4000.0


texts: Import data tier application in Managed instance

texts:
I am using SSMS 18.0 Preview 6 and wanted to import the .bacpac file we have locally stored and accessible. However, seems the function is not supported in the GUI . Any updates/suggestions are welcome.

We will use the sqlpackage.exe, hoever wanted to know if GUI supports this.


texts: Why does RDS PostgreSQL always recover on restart?

texts:
I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...].

I have found that after issuing a stop from the RDS console followed by a start the database always reports that the database system was not properly shutdown, requiring recovery. This behavior has been independently verified by at least one other RDS PostgreSQL user (https://forums.aws.amazon.com/message.jspa?messageID=809401#809401).

The query

select name, setting
from pg_settings
where name in ('fsync', 'wal_sync_method', 'synchronous_commit');

reports that fsync = on, wal_sync_method = fdatasync (correct for a Linux system), and synchronous_commit = on.

I see the following in the logs after issuing a stop:

2017-10-12 16:37:36 UTC::@:[3464]:LOG: received fast shutdown request
2017-10-12 16:37:36 UTC::@:[3464]:LOG: aborting any active transactions
2017-10-12 16:37:36 UTC::@:[3515]:LOG: autovacuum launcher shutting down
2017-10-12 16:37:36 UTC::@:[3512]:LOG: shutting down
2017-10-12 16:37:36 UTC::@:[3512]:LOG: checkpoint starting: shutdown immediate
2017-10-12 16:37:36 UTC::@:[3512]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=0.007 s, sync=0.002 s, total=0.145 s; sync files=1, longest=0.002 s, average=0.002 s; distance=16384 kB, estimate=16402 kB
2017-10-12 16:37:36 UTC::@:[3464]:LOG:  database system is shut down

And the following after issuing a start:

2017-10-12 17:05:33 UTC::@:[3293]:LOG: database system was interrupted; last known up at 2017-10-12 16:37:50 UTC
2017-10-12 17:05:33 UTC::@:[3293]:LOG: database system was not properly shut down; automatic recovery in progress
2017-10-12 17:05:33 UTC::@:[3293]:LOG: redo starts at 165/1160
2017-10-12 17:05:33 UTC::@:[3293]:LOG: unexpected pageaddr 164/D2000000 in log segment 000000010000016500000003, offset 0
2017-10-12 17:05:33 UTC::@:[3293]:LOG: redo done at 165/20000A0
2017-10-12 17:05:33 UTC::@:[3293]:LOG: last completed transaction was at log time 2017-10-12 16:50:53.823582+00
2017-10-12 17:05:33 UTC::@:[3293]:LOG: checkpoint starting: end-of-recovery immediate
2017-10-12 17:05:33 UTC::@:[3293]:LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.029 s, sync=0.002 s, total=0.046 s; sync files=2, longest=0.002 s, average=0.001 s; distance=49147 kB, estimate=49147 kB
2017-10-12 17:05:33 UTC::@:[3293]:LOG: MultiXact member wraparound protections are now enabled

Given my understanding of PostgreSQL's (fast) shutdown and startup sequences, the above log messages seem to indicate that PostgreSQL is writing and completing a final checkpoint before shutdown, and then successfully shutting down.

However, based on the code at xlog.c line 6023 and xlog.c line 6503 (https://github.com/postgres/postgres/blob/ca9cfed883333d5801716eb01cf28b6b5be2b5cd/src/backend/access/transam/xlog.c; can't post more than 2 links), corresponding to the database system was interrupted [...] and database system was not properly shut down [...] log lines, respectively, it would appear that at least the pg_control file was not flushed to disk. This worries me, as I'm expecting Amazon have taken care to make sure that the volumes on which PostgreSQL data and log files are stored are fit-for-purpose (i.e. they don't report fsync completion until it is really complete).

Is there another explanation for this behavior?


texts: SQL Connector for Azure Key Vault - Cannot use more than one key vault

texts:
I am using SQL Server 2016 Enterprise Edition with SQL Connector for Azure Key Vault. I am trying to attach more than one Key Vault to my SQL instance but it is not working.

I went through these instructions to set up the EKM provider and create a credential, create a new database encryption key from the asymmetric key, etc. for the first key vault - testkeyvault1. I am trying to repeat these same actions using a second credential which is configured for the second key vault - testkeyvault2

However, I keep getting the following error:

  Msg 33049, Level 16, State 2, Line 70
  Key with name 'test2rsakey0' does not exist in the provider or access is denied. Provider error code: 3112.  (Provider Error - No explanation is available, consult EKM Provider for details)

SQL Server is clearly looking at the wrong key vault. Looking at the event viewer shows this as well:

Vault Name: testkeyvault1.vault.azure.net
Operation: getKeyByName
Key Name: test2rsakey0
Message: [error:112, info:404, state:0] The server responded 404, because the key name was not found. Please make sure the key name exists in your vault.

If I add testkeyvault2 first, and then repeat the process for testkeyvault1, the results are reversed. So it seems that SQL Connector looks for keys only in the first configured key vault. I even checked the registry and ensured that keys exist for both key vaults in the following path: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider\Azure Key Vault\

I need to be able to use multiple key vaults since some customers want to provide access to their own key vault to encrypt the database stored on our servers.

Is there any way around this? Any alternative solutions to use multiple key vaults, or to allow customers a BYOK model?


texts: MySql sort conditional based on column values

texts:
I have a table with products where there's a column for price and stock.

I want the products to be display sorted by the following rules:

All products with price = 0 go to the bottom;
Then the products with price are sorted by:

if stock is greater or equal to 100 then they are sorted by price ASC;
else they are sorted by stock DESC and if there's a draw then sorted by price ASC.

So far I came up with price = 0, stock >= 100 DESC, price ASC, stock DESC
which seems to work correctly when there's products with stock over 100 but falls apart when there are none.

SELECT *
FROM products
ORDER BY price = 0, stock >= 100 DESC, price ASC, stock DESC

How can I make this work?


texts: Modify function to use it in multiple triggers

texts:
I have tables that have almost the same columns. The first table has the current values and the other table has the old values and an additional column to store the date data is inserted. The second table is named as the first table with the prefix _bit. Example:

CREATE TABLE x (
  id serial PRIMARY KEY,
  f1 integer NOT NULL,
  f2 character varying(10) NOT NULL,
  cuando timestamp without time zone NOT NULL DEFAULT now()
);

CREATE TABLE _bitx (
  id integer,
  f1 integer,
  f2 character varying(10),
  cuando timestamp without time zone,
  cuandobit timestamp without time zone DEFAULT now()
);

I have a trigger function that copies the OLD values of a table to another:

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
   BEGIN
      INSERT INTO _bitx VALUES((OLD).*);
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql

The trigger:

CREATE TRIGGER trigbitx
  BEFORE UPDATE
  ON public.x
  FOR EACH ROW
  EXECUTE PROCEDURE public.bitacoratabla();

All this works fine but I must do this for multiple tables that follow the same rules.

I modified the function in order to create the name of the table where I will insert data but I am having problems with that and with (OLD).*

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
   DECLARE
      tabla text := '_bit' || quote_ident(TG_TABLE_NAME);

   BEGIN
      EXECUTE 'INSERT INTO ' || tabla || ' VALUES(' || (OLD).* || ')';
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql

I am new to PL/pgSQL and thought I did not have to do more. Any ideas?

UPDATE

As all you suggest it is better to use format() and pass values with USING keyword. Thank you. This is what I did:

CREATE OR REPLACE FUNCTION bitacoratabla() RETURNS TRIGGER
AS $$
   BEGIN
      EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME) USING OLD;
      RETURN NEW;
   END
$$ LANGUAGE PLPGSQL;


texts: Matching multiple rows in a MySQL database

texts:
A custom built eCommerce store has the following two tables:

orders

| order_id | user_id | amount | shipping_info | date_time           |
|----------|---------|--------|---------------|---------------------|
| 222      | 7       | 999    | XXX           | 2017-07-20 05:54:45 |
| 333      | 5       | 2599   | XXX           | 2017-06-18 22:34:25 |
| 444      | 9       | 1859   | XXX           | 2017-07-01 15:07:54 |

ordered_products

| order_id | product_id | amount | quantity | product_type |
|----------|------------|--------|----------|--------------|
| 222      | 6546       | 53     | 2        | A            |
| 222      | 8979       | 85.99  | 1        | B            |
| 444      | 9879       | 87     | 2        | A            |
| 444      | 4756       | 25     | 2        | A            |
| 333      | 3215       | 129    | 2        | B            |

I want to list order ids which

contain product_type "A" but NOT "B"
contain product_type "B" but NOT "A"
contains both product_type "A" and "B"

I can't find a way to do this without using multiple queries and PHP loops. Is this possible using only SQL queries?


texts: Posible to obtain history ledger(balance) with sql query?

texts:
Not sure if this is the place to ask but here it goes. I am using a Postgres server and to make things simpler, I'll try to show a brief scenario of what I need.

CREATE TABLE operations(id,type,item,price,amount)
 AS VALUES
  ( 1::int, 'buy' , 'item1',  2, 10::int ),
  ( 2::int, 'buy' , 'item1',  3,  4::int ),
  ( 3::int, 'sell', 'item1',  4, 12::int )
;

If I go through a FIFO cost basis the ledger or balances after each order should look like:

id  |  price   | amount |

1   |      2   |     10 |

2   |      2   |     10 |
    |      3   |      4 |

3   |      3   |      2 |

One thing that I've tried is to have a table like operation_balances in which for each processed operation I insert the corresponding balance. This works however there's a problem with the huge amount of balances that get inserted for large number of operations so it's not a valid solution. I was wondering if it's possible to obtain the balance part for each order as part of a sql query. It may take a little while to run but it would save a lot of database space.


texts:
I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea.  I have been reading a couple of ideas and still not sure as this is a non default setting.

Any further advice?


texts: Format function returning minutes instead of months?

texts:
While exploring the new function FORMAT I have come across the following issue, would like to know if any one else has faced a similar problem and found the fix for this, or if it is a bug?

SELECT FORMAT(GETDATE(),'mm/dd/yyyy','en-US');
SELECT GETDATE();

Output:

02/02/2013
2013-06-02 12:02:54.067


texts: select from database with special characters

texts:
I am using php

select * from `outstanding` where `lcocode`='PN007\00128'

select * from `outstanding` where `lcocode` LIKE 'PN007\00128'

kindly suggest code for fetching data with backslash

I used above code but did not got results.


texts: Unable to connect to DB server from another machine in the same private network

texts:
I was setting up a Postgresql DB using Ansible and I was able to successfully install the DB.
My problem is that the DB machine is inside a private network (10.0.0.0/16), but when I try to connect to DB I get always "Connection refused".
This is strange because I am able to ping the DB machine from another (e.g. using ping 10.0.0.2).
I was thinking that it was an issue with UFW but I believe that my firewall is set correctly:
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere
5432                       ALLOW IN    10.0.0.0/16
5432/tcp                   ALLOW IN    10.0.0.0/16
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)

If I try to disable UFW, nothing changes. I continue getting the same error.
This is the output for netstat -nlt:
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN

Currently I have only another machine at 10.0.0.3 and I have added to listen_addresses in DB configuration.
listen_addresses "localhost, 10.0.0.3"
While I have added this line to pg_hba.conf:
host all all 10.0.0.0/16 md5

What am I doing wrong? The desired outcome is:

be able to connect to DB machine from my other machines inside the same private network
be able to isolate the DB machine, blocking every outside connection that is pointing directly to the server (DB machine should be available only for machines in the same private network)

Thank you all for your precious help!


texts: Fixing an error 9004 without taking the database offline

texts:
My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log.

Error: 9004, Severity: 23, State: 6.
An error occurred while processing the log for database 'MyDB'.  If possible, restore from
backup. If a backup is not available, it might be necessary to rebuild the log.

Is there any way to fix this without taking the database offline?

If not, what would be the fastest way? Transactional data loss would be accepted.


texts:
I use pre-built Oracle 12c r2

when I tried to sign up as sqlplus / as sysdba

I got a ORA-01017 which I solved with unset TWO_TASK

Since then, I can not sign in to the user I created
Because I'm in the root container of the CDB

How to connect on PDB?

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME
--------------------------------------------------------------------------------
    CON_ID   DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
CDB$ROOT
     1  776972821          1 4700A987085A3DFAE05387E5E50A8C7B

PDB$SEED
     2 1737080764 1737080764 51C985B6B6D35149E0530100007FC08B

ORCL
     3 4079644691 4079644691 51C99766D7E2568DE0530100007F4FAE


texts:
I'm having a problem with the merge replication and I want to try and run the job as a script on ssms so I can see the errors (I've tried almost everything else)
Original post

Here is the script

-Publisher [Publisher\Instance] -PublisherDB [Database] -Publication [Publication _name] -Subscriber [Subscriber\Instance] -SubscriberDB [SubscriberDB]   -Distributor [Distributor \Instance] -DistributorSecurityMode 1

i've tried exec and then entering that script but the problem is that this is a Replication Merge step and not tsql step.
Is there any way to run this script using tsql ?


texts: MySQL - trying to combine `GROUP_CONCAT` with `COUNT()`

texts:
I am trying to combine GROUP_CONCAT with COUNT(), and failing.

I have the following data:

CREATE TABLE `tab1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `item` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'mark', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'phil', 'phone');

This gives me:

1   | team1 | bob   | car
2   | team1 | bob   | car
3   | team1 | bob   | phone
4   | team1 | john  | car
5   | team1 | john  | phone
6   | team2 | mark  | car
7   | team2 | phil  | phone

I am trying to get to:

team1   | bob   | car(2), phone(1)
team1   | john  | car(1), phone(1)
team2   | mark  | car(1)
team2   | phil  | phone(1)

The closest I have managed is:

SELECT
A.team,
A.name,
GROUP_CONCAT(DISTINCT CONCAT(A.item, "(", B.count, ")") SEPARATOR ' , ') AS groupings
FROM tab1 A
JOIN (
    SELECT team, name, item, count(item) AS count
    FROM tab1
    GROUP BY team, name, item
    ) B
GROUP BY team, name

But this gives me

team1   | bob   | car(1) , phone(1) , car(2) , phone(2)
team1   | john  | phone(1) , car(1) , car(2) , phone(2)
team2   | mark  | car(1) , car(2)
team2   | phil  | phone(1) , phone(2)

I've tried every possible combination of GROUP BY I can think of, but it still doesn't work. What have I missed please?


texts:
Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed.

How to return a result set so a port does not get overloaded?
One possible solution and set based result would be:

SELECT shipnr, portnr FROM (
SELECT shipnr, portnr, DENSE_RANK() OVER(PARTITION BY portnr ORDER BY shipnr) AS N FROM ships
) AS a
ORDER BY N, portnr

However, this is not what I am looking for since at the end we get three ships arriving at F.
Below is an example I did by hand of the result set I am looking for:

1   A
6   B
10  C
11  D
13  E
16  F
20  F
2   A
7   B
12  D
14  E
17  F
21  F
3   A
8   B
15  E
18  F
22  F
4   A
9   B
19  F
5   A

Since there are many port F it should return in groups of 2 so that specific port is not overwhelmed.


texts: Calculate difference between SUM over last 30 days and the SUM for 30 days preceding this period (days 30-60) in Postgres

texts:
Every day we store the number of jobs posted for hundres of thousands of companies.

We need to query:

The SUM of jobs_posted for the last 30 days for each company;
The DELTA between jobs posted in the last 30 days to the previous 30 day period for each period.

Is there a faster, more efficient way in Postgres 9.6+ to create this query and are there indexes that would be benefitial?

CREATE TABLE    metric_data (
    id              integer PRIMARY KEY,
    company_id      integer NOT NULL,
    jobs_posted     integer NOT NULL,
    recorded_at     timestamp
);

WITH period_previous AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at BETWEEN CURRENT_DATE - INTERVAL '30 day' * 2 AND CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
), period_current AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at > CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
)
SELECT          period_current.company_id,
                period_current.jobs_posted AS current_jobs_posted,
                period_current.jobs_posted - period_previous.jobs_posted AS delta
FROM            period_current
JOIN            period_previous
ON              period_current.company_id = period_previous.company_id


texts: sp_WhoIsActive and get_plans -- Actual Plan?

texts: Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?


texts: Inserting data with SELECT into new column

texts:
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.

How things start:

SELECT COUNT(fullpath) FROM data;

12395

I use the column fullpath a lot so I create an index:

CREATE INDEX "index" ON data (fullpath);

I then add the column:

ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);

Insert values into the Sub column based on the fullpath:

insert into data(Sub)
select case
    when fullpath like '%subA%' then 'subA'
    when fullpath like '%subB%' then 'subB'
    else 'neither' end
from data;

Check the number of rows again:

SELECT COUNT(fullpath) FROM data;

24790

I don't understand the syntax and the operations. Do I need to join or something to respect the index?


texts:
I have two queries written in PHP against a Microsoft SQL Server database (I know this is weird).

I'm querying one table.

I want the records shared by both results sets ONLY.

Here is query #1:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'DONE')

Here is query #2:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'LAPMACPRO15.4')

The results of both queries have some of the same ORDER_NOs between them.

Example Results Set #1:

101
102
103

Example Results Set #2:

105
102
106

My query should only return "102", since it is common in both results sets.

I simply want to compare the results and show a list of ORDER_NOs that are in both results sets. An INTERSECT query would work perfectly for this, but I get this error message when trying to test my query in the SQL Server Manager:

The INTERSECT SQL construct or statement is not supported.

Is there a way to do this with INNER JOINS?

I have tried the following already, which involved joining the table to itself as an alias, but it returned no results.

select a.ORDER_NO,
       b.ORDER_NO
  from X_INVOIC a
  join X_INVOIC b on b.INVOICES_ID = a.INVOICES_ID
where a.ITEM_CODE =  'LAPMACPRO15.4'
  and b.ITEM_CODE = 'DONE'

Thanks.


texts: TempDB Configuration using 8 TempDB Data File

texts:
On a production server with 512 GB RAM , 2 Sockets 24 Cores (Total Logical Processors 48), i have configured to have 8 TempDB Data Files ( on Primary) with each 4096 MB growth of 1024 MB and Log File set to 2048 MB with 1024 MB growth and Trace Flag 1117 configured in the startup.

the OLTP database of approx. 100 GB data file is been accessed by Dynamics AX application. the AX application server is on another server.

The concern is that the AX application is running very slow even after rebuilding/reorganizing indexes and setting the MAXDOP as per the AX Dynamics recommendation.

For which i tried to find the Avg Write performance of all database from
sys.dm_io_virtual_file_stats (io_stall_write_ms / num_of_writes < 20) it shows above 100 ms. though the read performance is well below 10 ms.

The TempDB is stored in the different Disk Drive (which is dedicate cluster storage H Drive - free space 66 GB ) and other  DB Files are stored  on different Disk Disk ( Cluster Storage Drive).

What needs to be done in this case to improvise the write performance.
Do i increase the TempDB Data File size more?

attaching the image of the output from sys.dm_io_virtual_file_stats.

?


texts: Insert performance with Geography column

texts:
I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly.

I started out with no geography column and it took 6 minutes, then I added a geography column and it took 20 minutes (again same data). Then I added a spatial index and it took 1 hour and 45 minutes.

I'm new at anything spatial, but this seems like really bad performance. Is there anything I can do to help speed this up or is this just the performance I'm going to see when dealing with SQL Server spatial data?


texts: If I do a full MySql restore from a dump file, will the user permissions and privileges restore aswell?

texts:
I had some weird stuff happen after rebooting my system. Something changed that I did not do. Now none of my java client applications can connect to my main mysql database - they get the error

  SQL EXCEPTION: ACCESS DENIED FOR USER ''@''...

I've changed permissions, changed root password, everything I can think of and nothing has worked to fix this problem...

So if I do a complete restore, would that bring all settings and data back to its original state?


texts: Generate a file or a script that can be used to reconstruct a database on a different PC

texts: I'm trying to copy a database that I created in SQL server 2008 on a new PC without having to do so manually by creating a database on the new computer and recreating all the tables from the scratch. The copy doesn't have to have any of the data stored just the tables and the diagram, Is this possible? I read about restoring  or exporting the database but neither got me anywhere.


texts: Separating working hours with non-working hours SQL

texts:
Please can someone assist? I need to break up data between working hours and non-working hours (weekends also need to be taken into consideration).

I'm not really sure where to begin, other than:

select *
from Electricalconsumptionhalfhourview
where meterID = 17635382
  and [Date] < '2015-02-28 23:59:59.000'
  and [Date] > '2015-02-01 00:00:00.000'

Thanks for the help guys... I eventually settled on doing this...

select *
into #WorkingHours
from Electricalconsumptionhalfhourview
where meterID = 17635382
  and [Date] > '2015-02-01 00:00:00.000'
  and [Date] < '2015-02-28 23:59:59.000'

select *
into #NonWorkingHours
from Powerstardatahalfhourcacheview
where meterID = 1458194
  and [Date] > '2015-02-01 00:00:00.000'
  and [Date] < '2015-02-28 23:59:59.000'

SELECT *
FROM #WorkingHours
WHERE DATEPART(hh, [Date]) > 8 --Start of working hours
  AND DATEPART(hh, [Date]) < 17 --End of working hours
  AND DATEPART(dd, [Date]) NOT in (1,7,8,14,15,21,22,28)
  AND DATEPART(mm, [Date]) = 2

SELECT *
FROM #NonWorkingHours
WHERE DATEPART(hh, [Date]) < 8  --Start of working ho
   OR DATEPART(hh, [Date]) > 17  --End of working hours
  AND DATEPART(mm, [Date]) = 2
UNION
SELECT * FROM #NonWorkingHours
WHERE DATEPART(dd, [Date]) in (1,7,8,14,15,21,22,28)
  and DATEPART(mm, [Date]) = 2'


texts: SQL Server 2008 R2: Restore latest backup to new database from script

texts:
We have a database for each major version of our system, for each customer, for both development and test. Like so:

WHATEVER44_CUSTOMERA_DEV
WHATEVER44_CUSTOMERA_TEST
WHATEVER44_CUSTOMERB_DEV
WHATEVER44_CUSTOMERB_TEST
WHATEVER44_CUSTOMERC_DEV
WHATEVER44_CUSTOMERC_TEST

Once in a while, we restore the latest backup of each of these to new databases with a higher version number:

WHATEVER45_CUSTOMERA_DEV
WHATEVER45_CUSTOMERA_TEST
WHATEVER45_CUSTOMERB_DEV
WHATEVER45_CUSTOMERB_TEST
WHATEVER45_CUSTOMERC_DEV
WHATEVER45_CUSTOMERC_TEST

We do this in Management Studio by right-clicking on the existing database, Task > Restore.
It automatically selects the latest backup set and all we need to do is change the name of the database to restore to.

We want to automate this with a script that given a list of existing database names creates these new versions of the databases, without user interaction (apart from running the script). Is it possible?


texts: Dividing DBCC CHECKDB over multiple days

texts:
I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days for very large databases, which basically consists of:

Dividing the tables in the database roughly equally between 7 buckets
Running a DBCC CHECKALLOC twice a week
Running a DBCC CHECKCATALOG once a week
Running a DBCC CHECKTABLE on one bucket each day of the week

Has anyone used this technique? Any existing scripts out there?

I'm concerned this may not actually cover everything that CHECKDB does; the Books Online documentation for CHECKDB says that in addition to CHECKALLOC, CHECKCATALOG and CHECKTABLE, it also:

Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. (SQL 2008 only)
Validates the Service Broker data in the database.

So here are my questions:

Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)
If so, are there ways to perform these additional checks separately?
CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?

(Note: this will be a standard routine for thousands of existing databases across hundreds of servers, or at least every database over a certain size. This means that options like restructuring all databases to use CHECKFILEGROUP aren't really practical for us.)


texts: Mail Alert when threshold of 10000 pendingcmds are breached

texts:
I currently have the below running to monitor replication and send a mail when the threshold of 10000 pendingcmds are breached, however the alert mail is generated every 5 minutes regardless of the threshold. I need assistance to re-code the threshold part as this is not delivering the expected results.

        DECLARE @RESULT nvarchar(100)
        DECLARE @cmd varchar(100)

        SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
        EXEC sys.sp_executesql @RESULT
        PRINT @RESULT
        IF @RESULT > '10000'

Code used to generate alert

        USE [TestDB]

        create table #pendingcmds
        (pendingcmdcount int,
        estimatedprocesstime int)

        Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
        select * FROM OPENROWSET('SQLOLEDB',
        'Server=Server\Instance;Trusted_Connection=yes;',
        'set fmtonly off;
        exec distribution..sp_replmonitorsubscriptionpendingcmds
        @publisher=''Server\Instance'',
        @publisher_db=''testproduction'',
        @publication=''Summary_Inventory_TestProduction'',
        @subscriber=''Server'',
        @subscriber_db=''TestReplAzure'',
        @subscription_type=0')

        SELECT * FROM #pendingcmds

        --DECLARE @RESULT nvarchar(100)
        ----DECLARE @cmd varchar(100)
        --
        --SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
        --EXEC sys.sp_executesql @RESULT
        --PRINT @RESULT
        --IF @RESULT > '10000'
        --
        --BEGIN

         /*************************************************************/
         /****************** HTML Preparation *************************/
         /*************************************************************/

        DECLARE @HTML  VARCHAR(MAX),
                @table VARCHAR(MAX)

        SET @HTML = --HTML layout--
                        '' +
                        '
Publisher to Subscriber has exceeded threshold
' +
                        '' +
                        '' +
                        '
' +
                        '
'+
                        'Pending CMD Count	' +
                        'Estimated Process Time
';

        SELECT @table = CONVERT(nvarchar(max) ,
            (SELECT
             td = CAST(pendingcmdcount as varchar(30)), '',
             td = CAST(estimatedprocesstime as varchar(30)), ''
             FROM #pendingcmds
             FOR XML PATH(N'tr'), TYPE));

        SET @HTML = @HTML + @table + CHAR(10) +
                     N'
';

        SET @HTML = @HTML + '

End of Report
';

        PRINT @HTML

        Declare @MailSubject varchar(100)
        SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())

        IF LEN(@HTML) > 10

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

        DROP TABLE #pendingcmds
        --END

        --ELSE
        --DROP TABLE #pendingcmds
        --
        --END


texts:
So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart.

The process is a simple data flow task with default settings for "Rows per batch" and the options are "tablock" and "no check constraint".

The table is fairly large. 587,162,986 with a data size of 201GB and 49GB of index space. The clustered index for the table is.

CREATE CLUSTERED INDEX ImageData ON dbo.ImageData
(
    DOC_ID ASC,
    ACCT_NUM ASC,
    MasterID ASC
)

And the Primary Key is:

ALTER TABLE dbo.ImageData
ADD CONSTRAINT ImageData
PRIMARY KEY NONCLUSTERED
(
    ImageID ASC,
    DT_CRTE_DOC ASC
)

Now we've been having an issue where BULK INSERT via SSIS is running incredibly slow. 1 hour to insert a million rows. The query that populates the table is already sorted and the query to populate takes under a minute to run.

When the process is running I can see the query waiting on BULK insert which takes anywhere from 5 to 20 seconds and showing a wait type of PAGEIOLATCH_EX. The process is only able to INSERT about a thousand rows at a time.

Yesterday while testing this process against my UAT environment I was running into the same issue. I was running the process a few times and attempting to determine what the root cause of this slow insert is. Then all of a sudden it started running in under 5 minutes. So I ran it a few more times all with the same result. Also the number of bulk inserts that were waiting for 5 seconds or greater dropped form hundreds to about 4.

Now this is perplexing because it's not like we had some huge drop off in activity.

CPU during the duration is low.

The times when it's slower there appear to be fewer waits on disk.

Disk latency actually increases during the time frame that the process was running in under 5 minutes.

And The IO was much lower during the times that this process runs poorly.

I've already checked and there was no file growth as the files are only 70% full. The log file still has 50% to go. The DB is on Simple Recovery mode. DB only has one file group but is spread across 4 files.

So what I'm wondering A: why was I seeing such large wait times on those bulk inserts. B: what sort of magic happened that made it run faster?

Side note. It runs like crap again today.

UPDATE it is currently partitioned. However it's done in a method that is at best silly.

CREATE PARTITION SCHEME [ps_Image] AS PARTITION [pf_Image]
TO ([FG_Image], [FG_Image], [FG_Image], [FG_Image])

CREATE PARTITION FUNCTION [pf_Image](datetime) AS
RANGE RIGHT FOR VALUES (
      N'2011-12-01T00:00:00.000'
    , N'2013-04-01T00:00:00.000'
    , N'2013-07-01T00:00:00.000'
);

This leaves essentially all of the data in the 4th partition. However since it's all going to the same file group. The data is currently split pretty evenly across those files.

UPDATE 2
These are the overall waits when the process is running poorly.

This is the waits during the period that I was able to run the process is running well.

The storage subsystem is locally attached RAID, no SAN involved. The logs are on a different drive. Raid Controller is PERC H800 with 1 GB cache size. (For UAT) Prod is a PERC(810) .

We're using simple recovery with no backups. It's restored from a production copy nightly.

We also have set IsSorted property = TRUE in SSIS since the data is already sorted.


texts: Update data of one table from another with comparing columns have same initial strings

texts:
I have two tables:

NewData:

EName               Job        Sal

smith               clerk      2000
allen               sales      2000
jones.domain.com    Manager    6000

OldData:

EMPNO        ENAME                  JOB         HIREDATE       SAL

7369         smith.domain.com       clerk       17-DEC-80      1300
7549         allen.domain.com       sales       01-JAN-81      1800
7645         jones.domain.com       Manager     01-JAN-80      5000

I want to update columns Job and Sal of the table OldData from table NewData by using column EName. I tried it with LIKE and SUBSTR but not getting a good solution using them.

LIKE query-
UPDATE emp_backup
SET emp_backup.sal = (select t1.sal from t1 join emp_backup on t1.ename like emp_backup.ename+'%')

SUBSTR query-
UPDATE emp_backup
SET emp_backup.sal = (select t1.sal from t1 join emp_backup on SUBSTR(t1.ename,1,4)=SUBSTR(emp_backup.ename,1,4));