How can I get all data from a given table since my last run? In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked?
My tables typically have a column update_date DATE that holds the date/time last updated, and I am currently using that.  I'm finding this isn't fully reliable, as some other stored procedure could insert data with this column set properly via SYSDATE, then commit the transaction a few seconds later when it completes.  If I ran a query after the DATE column was written but before the transaction was committed, my query will contain rows with later update_date values (by a second or two, usually) but I won't see the newest rows that have a short delay in being committed.
In other words, suppose in  my first run I SELECT all rows, and the latest row I get has an update_date of '2021-01-15 08:01:02'. Then my job keeps track of that value, and on my second run, the job now naively uses WHERE update_date > :latest_changed_date_from_last_run, but such a query won't include data from '2021-01-15 08:01:02' that was committed after my last run, nor even perhaps data from '2021-01-15 08:01:01' or '2021-01-15 08:01:00'--or really any past DATE--that was written before but committed after my previous run.
(I'm not having trouble storing or retrieving this "latest_changed_date_from_last_run" value, but there seems to be no way to use it to get all the data I need. I can only get most of it.)
So is there any way to ensure I can get all rows changed since my last run?  If there's no way to do it with a SELECT query, is there some other kind of Change-Data-Capture-lite technique I should be considering for this simple scenario?
(More context: This is in trying to use a Kafka Connect JdbcSourceConnector with an ordinary query, but I imagine this concept could be needed in any system that periodically checks for any updates/inserts in any table.)

I think you can base your query on the ORA_ROWSCN pseudocolumn.

If two transactions T1 and T2 modified the same row R, one after
another, and committed, a query on the ORA_ROWSCN of row R after the
commit of T1 will return a value lower than the value returned after
the commit of T2

For this use case, the table must be created with ROWDEPENDENCIES (cannot be changed after table creation)

With this feature, each row in the table has a
system change number (SCN) that represents a time greater than or
equal to the commit time of the last transaction that modified the
row.

If you can recreate the table with ROWDEPENDENCIES, then you would swap your latest_changed_date_from_last_run for latest_ora_rowscn_from_last_run and that should do the job.


Unable to Create COMMIT inside PostgreSQL 11.5 Procedure I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor)
    LANGUAGE PLPGSQL
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;
    END;
$$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I've tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I'm doing wrong? Thank you!

The

BEGIN;

is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.

You can end a transaction, which implies that a new transaction is started immediately.

Without the BEGIN;, your procedure works just fine.

The problem must be with the way you are calling it. As the documentation says:

  Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There is probably a SELECT in your call stack.

Another possibility is that you explicitly started a transaction with BEGIN before calling the stored procedure.

This does not work either, which is an undocumented implementation restriction that may be fixed in the future. See this thread for reference.


What does "sample_ms" and "num_of_reads" means in "sys.dm_io_virtual_file_stats". what is the interval time of stats As per MS-Docs it returns the Number of milliseconds since the computer was started, but i'm getting different result when i compare with actual server start time in sys.dm_os_sys_info.

Here are the queries:

select sqlserver_start_time from sys.dm_os_sys_info;

select (sample_ms/1000) sample_sec, file_id from sys.dm_io_virtual_file_stats (DB_ID(), NULL);

select (DATEDIFF (SECOND, sqlserver_start_time, SYSDATETIME()) ) as Server_start_since_sec from sys.dm_os_sys_info;

This is the result:

If sample_ms reflects computer/server start-time not the SQL Instance's start-time (comparing 2nd vs 3rd in result screenshot). Does it mean the statistic values (num_of_reads, num_of_writes etc..) in DMF (sys.dm_io_virtual_file_stats) returns cumulative reads since the computer restart or since the SQL Instance restart or there is different interval time that we need to consider?

Before restart SQL Service

After restart SQL Service (NO OS restart)

First, these start values are not measuring the same thing.

sys.dm_os_sys_info:

  sqlserver_start_time  - datetime -    Specifies the date and time SQL Server
  last started. Not nullable.

sys.dm_io_virtual_file_stats:

  sample_ms - bigint- Number of milliseconds since the computer was
  started. This column can be used to compare different outputs from
  this function.

  Does it mean the statistic values (num_of_reads, num_of_writes etc..)
  in DMF (sys.dm_io_virtual_file_stats) returns cumulative reads since
  the computer restart or since the SQL Instance restart or there is
  different interval time that we need to consider?

It should be cumulative reads/writes on each file ("Total number of bytes read on this file") for the current instantiation of the instance, i.e. since it was last started. The original function that this dmf replaces, sys.fn_virtualfilestats returned a timestamp column to help compare results received over time ("Database timestamp at which the data was taken").

Now that this is a dmf, it appears as though sample_ms fills that void based on its description:

  This column can be used to compare different outputs from
  this function.

In most realistic cases the only thing reading/writing to SQL Server's data/log files is itself, so these would not be incrementing unless the instance was started anyways. Regardless, it does not appear the intent is to use this counter for any purpose other than comparing multiple outputs over time.

I was unable to find a canonical reference saying that all dmvs reset on instance restarting, but some of the index based dmvs' documentation do specify this:

  The counters are initialized to empty whenever the SQL Server
  (MSSQLSERVER) service is started.

Many of the dmvs do not return information that is cumulative, so it doesn't make sense to describe them as being reset on an instance restart. However, this one does appear to fall into that category and should be noted as such.

I've made a pull request to updated the documentation to reflect the fact that it resets on instance startup.


Generated bug data script how not to set the IDENTITY in the insert statements? I'm using SQL Server Management Studio to generate sql script (big one)
to update Azure DB.
The problem is that the script is generated with IDENTITY on and the insert statements are exported with the ID column which as result gives me the errors :

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

My question is how to generate the script right that is will not use  IDENTITY on and the insert.

bcp is probably a better way to achieve this and works well with Azure SQL Database.  It's a command-line tool that enables you to extract and  load data easily.  For example:

bcp "dbo.yourTable" out c:\temp\temp.bcp -S "yourSourceServer" -d "yourSourceDatabase" -T -n

bcp "dbo.yourTable" in c:\temp\temp.bcp -S "yourTargeteServer" -d "yourTargetDatabase" -U someSQLLogin -P somePassword -E -n

Specify the -E switch in the bcp in statement to keep your IDENTITY values.  Replace the table, server and database names in my template above and see if you can get the first statement to run.  Note in the first example I am using Windows Authentication (-T) but SQL Authentication in the second one (-U for User and -P for password).  The main MSDN page for bcp provides some good examples:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

If your table already has some data in it, consider inserting your data into a staging table first, then INSERT to your main table, eg

SET IDENTITY_INSERT yourTargetTable ON
GO

INSERT INTO yourTargetTable ( columnList )
SELECT columnList
FROM yourStagingTable s
WHERE NOT EXISTS
    (
    SELECT *
    FROM yourTargetTable t
    WHERE s.yourPrimaryKey = t.yourPrimaryKey
    )
GO

SET IDENTITY_INSERT yourTargetTable OFF
GO

There are other methods of achieving this.

For example, you could accomplish the same thing using SQL Server Integration Services (SSIS) but there is some overhead to creating an SSIS project.
For example, if your table is not too big, create a copy of your table in tempdb using something like SELECT [all columns except the identity column] INTO tempdb.dbo.scratchTable FROM yourMainTable.  Run the Generate Scripts option against this copy then delete your scratch table.


Alternative tools to export Oracle database to SQL Server? I've got an Oracle database that I need to export (schema and data) to SQL Server.

I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient and very un-user-friendly, e.g. I was having problems connecting to the SQL Server DB during data migration - but it still spent ~5 minutes preparing all the data before attempting a connection to SQL Server, then when it failed, the 5 minutes of preparatory work were wasted.

Right now, I'm just trying to connect to another Oracle DB using this tool, I left it overnight and came back this morning, and it's still stuck on 19% of "Loading objects..."  And this is on a machine with a good 18GB RAM, of which maybe 8.5 GB currently in use.  Task Manager shows me that SSMAforOracle.exe is using 0 CPU, 0 PF Delta, and no change whatsoever in memory usage. In other words: frozen stiff. Absolutely intolerable.

Are there any other tools out there that can migrate an Oracle DB to SQL Server a little more efficiently?


Two queries run much longer when combined into one The first two queries below are very similar. The third is practically the union of the first two:

sql> SELECT sigla_partido, SUM(receita_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     GROUP BY sigla_partido

[2016-03-07 22:54:55] 32 row(s) retrieved starting from 1 in 352ms (439ms total)

sql> SELECT sigla_partido, SUM(despesa_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido

[2016-03-07 22:54:59] 32 row(s) retrieved starting from 1 in 1s 648ms (1s 832ms total)

sql> SELECT sigla_partido, SUM(receita_valor), SUM(despesa_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido

[2016-03-07 22:55:30] 32 row(s) retrieved starting from 1 in 18s 264ms (29s 932ms total)

I expected the runtime of the third query to be close to the sum of the execution times of the first two queries. That did not happen and the third query took much longer than expected. For what reason?

You better combine those queries using Derived Tables;

select sigla_partido, receita_valor, despesa_valor
from
 (
     SELECT sigla_partido, SUM(receita_valor) as receita_valor
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     GROUP BY sigla_partido
  ) as receita
natural join
 (
     SELECT sigla_partido, SUM(despesa_valor) as despesa_valor
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido
 ) as despesas

Btw, check if the slow query actually returned the correct result.


What will happen with cached index or table when it is modified, will it stay in cache? if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?

If by cache you mean shared_buffers, no, it won't be removed from there due to being changed.  shared_buffers is the primary place where it does get changed.  From there it will eventually get pushed out to the kernel, and then from there to disk.


Granting Read access to Replica database (via data guard) but need to restrict the same login in production Database We use Data Guard to replicate production Oracle database. We have a need to grant read access to replica DB and at the same time need to restrict that same login gaining read access to production.

Since the data guard replicates everything (including user/roles) is there any way we can apply this restriction to the production databases? Should I look into to database server inbound port restrictions? Any thoughts on this are greatly appreciated.

I think you can achieve this by using SQLNET.ORA file found under either in $ORACLE_HOME/network/admin or $GRID_HOME/network/admin or anywhere pointed by TNS_ADMIN variable.

tcp.validnode_checking = yes
tcp.invited_nodes =<list the valid nodes here>

Details are well explained in a blog written by Arup Nanda

If it is for application, you can restrict access by defining SERVICE_NAME of a standby database in TNS Name.

You can also create a logon trigger that will check the name of the user and the ROLE of the database. If the database role is not the 'PHYSICAL_STANDBY' then you can raise an error.


Mysql 8 event scheduler timezone im a bit confused about the event scheduler. First off, it is running and working, but the timezone handling confuses me a bit, as it seems it's not what is documented on events metadata

So to start off, they say, mysql.event table holds event data as well. This table does not exist on our system.
It also says in info schema, starts and last_executed are displayed as event timezone, but that doesnt seem to be the case on our system as well.

starts = reported as 'system' time zone, currntly utc -0400, hence i had to make the start time -4 hours to have it run in utc at start of next month (2019-07-31 20:00:00).

I know i can set the session timezone to utc.
While that works, as soon as you edit the event, the old gets deleted and a new create event is triggered by phpmyadmin ( possibly bug? ).
I also know i can run the commands in sql tab with alter event, but i am not the oly one accessing the system, and anyone could basicly use the event interface to edit the event = back to system timezone

timezone reported after adding is system (utc -4)
on an test run, last executed was in utc ( no problem really, but its not what docs say )

Now docs say mysql.event displays the start used to add the event in utc ( again our system not has this table ) and stores the timezone used to prevent wrong timings when changing timezones

What happens when we possibly change the system timezone at a later point? since info schema reports as system and not -0400 what would happen to our event which right now has a starts time of 31.07.2019 20:00:00?

Say we change to utc, would the starts column update to reflect this change? Would the timezone reflect the change?

I'm afraid our event would then possibly run at 8pm in utc instead of -0400, since timezone in info schema might still be 'system' then.

Anyone has some insight on this?
Don't really want to mess with the global timezone to test this out, since system is in production.

Appreciate any help/insights on this,
Seb

https://dev.mysql.com/worklog/task/?id=3698

Currently, all datetime values specified in CREATE EVENT are converted to UTC
time zone internally.


In a database trigger on event create - How do I reference the created object? Given the following trigger:

CREATE OR REPLACE TRIGGER
  TRG_CREATE_TABLE_INITRANS
  AFTER CREATE ON SCHEMA
BEGIN
  IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
  exec 'alter table '||<table_owner>||'.'||<table_name>||' initrans 10'
  END IF;
END;

How do I access the object owner and the object name which triggered the trigger?

If you're creating trigger in a schema then schema name is the object owner/table owner or you can use sys.dictionary_obj_owner and for table name sys.dictionary_obj_name.

    SQL> SET SERVEROUTPUT ON
    SQL> SHOW USER
    USER is "OT"
    SQL> CREATE TABLE test01 (sno number);

    Table created.

SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;   -- before trigger created

DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
        --------------------------------------------------------------------------------

     CREATE TABLE "OT"."TEST01"
       (    "SNO" NUMBER
       ) SEGMENT CREATION DEFERRED
     PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255          -- default INITRANS is 1
     NOCOMPRESS LOGGING
     TABLESPACE "USERS"

    CREATE OR REPLACE TRIGGER
      TRG_CREATE_TABLE_INITRANS
      AFTER CREATE ON SCHEMA

    BEGIN
       IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
          EXECUTE IMMEDIATE 'ALTER TABLE '||sys.dictionary_obj_owner||'.'||sys.dictionary_obj_name||' INITRANS 10';

       END IF;
    END;

    SQL> drop table test01 purge;
    Table dropped.

    SQL> CREATE TABLE test01 (sno number);
    Table created.

    SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;

     DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
        --------------------------------------------------------------------------------
    CREATE TABLE "OT"."TEST01"
     (    "SNO" NUMBER
     ) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255    -- INITRANS 10
    NOCOMPRESS LOGGING
    TABLESPACE "USERS"


Convert multiple rows into column base on another column value I have search through the answer but could not get what i'd like to have. my table is as below.

No  |    Name            | Relation
1   | A                  | P
1   | B                  | GO
1   | C                  | JB
2   | AH                 | P
3   | TY                 | P
3   | DO                 | JB
3   | DO1                | JB
3   | DO2                | JB
3   | DO3                | JB
3   | DO4                | JB
3   | DO5                | GO
3   | DO6                | GO
3   | DO7                | GO
3   | DO8                | GO
3   | DO9                | GO
3   | DO10               | GO
4   | PA                 | P
4   | PA2                | GO
4   | PA3                | GO
4   | PA4                | GO
4   | PA5                | GO
4   | PA6                | GO
4   | PA7                | GO

Each No will have in Relation only one 'P', max 5 'JB' and max 6 'GO'.

I'd like to get below result.

No  | P    | JB1  | JB2 | JB3 | JB4 | JB5 | GO1 | GO2 |GO3 |GO4 |GO5 |GO6 |
1   | A    | C    | NULL| NULL| NULL| NULL| B   | NULL|NULL|NULL|NULL|NULL|
2   | AH   | NULL | NULL| NULL| NULL| NULL| NULL| NULL|NULL|NULL|NULL|NULL|
3   | TY   | DO   | DO1 | DO2 | DO3 | DO4 | DO5 | DO6 |DO7 |DO8 |DO9 |DO10|
4   | PA   | NULL |NULL |NULL |NULL |NULL | PA2 | PA3 |PA4 |PA5 |PA6 |PA7 |

Table and sample data

CREATE TABLE dbo.Data
(
    [No] integer NOT NULL,
    Name varchar(4) NOT NULL,
    Relation varchar(2) NOT NULL
);

INSERT dbo.Data
    ([No], Name, Relation)
VALUES
    (1, 'A', 'P'),
    (1, 'B', 'GO'),
    (1, 'C', 'JB'),
    (2, 'AH', 'P'),
    (3, 'TY', 'P'),
    (3, 'DO', 'JB'),
    (3, 'DO1', 'JB'),
    (3, 'DO2', 'JB'),
    (3, 'DO3', 'JB'),
    (3, 'DO4', 'JB'),
    (3, 'DO5', 'GO'),
    (3, 'DO6', 'GO'),
    (3, 'DO7', 'GO'),
    (3, 'DO8', 'GO'),
    (3, 'DO9', 'GO'),
    (3, 'DO10', 'GO'),
    (4, 'PA', 'P'),
    (4, 'PA2', 'GO'),
    (4, 'PA3', 'GO'),
    (4, 'PA4', 'GO'),
    (4, 'PA5', 'GO'),
    (4, 'PA6', 'GO'),
    (4, 'PA7', 'GO');

Query

The basic technique is shown on the related question:

Need help with SQL Server PIVOT

The only extra trick is to get the numbering right:

SELECT
    P.[No], P.P,
    P.JB1, P.JB2, P.JB3, P.JB4, P.JB5,
    P.GO1, P.GO2, P.GO3, P.GO4, P.GO5, P.GO6
FROM
(
    SELECT
        D.[No],
        D.Name,
        RelationSeq =
            CASE
                -- Add a sequence number for rows with Relation JB or GO
                WHEN D.Relation IN ('JB', 'GO')
                THEN D.Relation +
                    CONVERT
                    (
                        char(1),
                        ROW_NUMBER() OVER (
                        PARTITION BY D.[No], D.Relation
                        ORDER BY
                            CONVERT(integer, SUBSTRING(D.Name, 3, 2)))
                    )
                -- Otherwise just use the Relation
                ELSE CONVERT(varchar(3), D.Relation)
            END
    FROM dbo.Data AS D
) AS S
PIVOT
(
    -- Standard pivot rows to columns
    MAX(S.Name)
    FOR RelationSeq IN
    (P, JB1, JB2, JB3, JB4, JB5, GO1, GO2, GO3, GO4, GO5, GO6)
) AS P;

Demo

Try it on Stack Exchange Data Explorer

Result

If you think this query is more difficult than it should be - you're right. Storing the original data in a more normalized structure would have made it considerably simpler. Poor design often leads to difficult queries.


Getting around Primary Key requiring non-nullable columns Word Problem

I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least with SQL Server 2014) I cannot have a nullable column in a primary key.

Example

CREATE TABLE Test
(
    Id1 INT NOT NULL,
    Id2 INT NOT NULL,
    PRIMARY KEY (Id1, Id2)
)

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

The error is on the Primary Key of Test2:

  A primary key constraint cannot be defined on the nullable column 'TestId2' in the table or user-defined table type ...

My workaround:

CREATE TABLE Test3
(
    TestId1 INT NOT NULL,
    TestId2Calc AS COALESCE(TestId2, -1) PERSISTED NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2Calc),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

But this feels dirty. Is this my only option?

There is no physical difference between Primary Key and a Unique Clustered Index.  So unless you are using some tool that requires a Primary Key, you can do

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    UNIQUE CLUSTERED (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)


Install PFX certificate on SQL 2008 R2 We have a MS SQL 2008 R2 service running on a Windows 7 computer. I've developed a webapplication that runs on a Digital Ocean droplet. This droplet makes a connection with this computer through a subdomain "sql.client-domain.ext" (fictive domain name).
There is a firewall which only allows connections from the droplet to the computer where SQL service is running.
I want an encrypted connection between the droplet and the computer. I've purchased a certificate.
On my local test machine I've installed the PFX certificate in the personal folder (in MMC). I've added the 'NETWORK SERVICE' user with READ permissions for the certificate key.
In the SQL management console, I can't see the certificate anywhere. I tried with service restarts, nothing.
I've also tried to change the hostname to the subdomain and added the subdomain in the hosts file under the Windows directory.
EXEC sp_addserver 'sql.client-domain.ext', local;

Host file: 127.0.0.1 sql.client-domain.ext

I can connect in the SQL client program to sql.client-domain.ext but I can't pick the certificate.
I only need the certificate for TCP connections, not local connections.
Any hints or experienced advice?

Plase try to follow this steps:

Launch the Microsoft Management Console. Select File > Add/Remove snap-in:

Add a Certificates snap-in:

Select “Computer account”:

Use the Local Computer:

With the snap-in now configured, right-click on the “Personal” certificate store folder, select All Tasks > Import:

Use the certificate import wizard to select the .PFX file to import.
In either case, follow the import wizard instructions. Enter the password set when the PFX file was created. Ensure that “Include all extended properties” is selected:

Select “Place all certificates in the following store” and choose the “Personal” store:

Once the certificate has been imported, SQL Server must be configured to use it. The SQL Server configuration manager for SQL Server 2008/R2 has an option to set the certificate associated with an instance, by opening the “SQL Server Networking Configuration,” right-clicking on “Protocols for ,” and opening the Certificate tab. However, this method cannot be used to select any certificate other than a machine certificate for servers that belong to a domain.

Find the “Thumbprint” of the certificate, and copy it to the clipboard. In the certificate management console, it can be found by opening the certificate, selecting the “Details” tab, scrolling to the bottom of the field list, and selecting “Thumbprint”:

After locating the thumbprint, copy it out of the certificate viewer, paste it into notepad, remove all of the spaces, and copy the modified thumbprint back to the clipboard. Be careful – it’s easy to make a mistake.

Open regedit
Navigate to: HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib
Locate the “Certificate” entry
Assign its value to the thumbprint copied out of the certificate, e.g.:

The instance must be restarted for the changes to take effect.


SSMS 2014 working with SQL Server 2005 instances Is there any major complication that one could run into while using the latest (developer) version of SSMS with older SQL Server 2005 instances?

Any way one could affect the data by using this version?

My motto is to always use the most recent version of the tools and always have tools >= highest engine you manage. Generally, most of everything in modern versions is backward compatible going back as early as 2005 instances.

There are a couple of exceptions, in addition to those others have mentioned:

For SQL Server 2000, I always install the 2000 client tools.
If you use DTS/SSIS you will want to have the tools relevant to the version of your packages. If you open a 2008 package in 2014 and save it, you will cause problems for other users still using older versions. This may also be true for maintenance plans.

The nice thing is that you can install both the 2005 version of the tools as well as the most recent version side-by-side. This way, if you come across any compatibility issues, you can fire up the older version.

In general, you should be able to use the newer version for just about everything, and not worry about any negative effects on your data (full disclosure though - I don't use the UI functionality for a whole lot, particularly the buggy designers).


Call function with insert I have a function that needs a string as parameter. It's always a insert. How do I make it work?  The double quotes for the values are not good syntactically

select inversor_insert('INSERT INTO clien(column1,column2) values('value1','value2')')
from dual;

You can try the following;

select inversor_insert(q'[INSERT INTO clien(column1,column2) values('value1','value2')]')

The q'[  ]' construct simply means that Oracle will treat anything within the brackets as it is written. you don't need to worry about quoting the quotes.


What is FT BATCH CMPLETE in the cmd column for sysprocesses? I was trying to figure out what was causing high cpu usage in our sql server and found two processes that were consuming high CPU.  When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column.  Does anyone know what this command is?  Nothing came up when I looked at the sql_handle.

Is it related to full text?  I can't figure out what the process is doing, even tracing it comes up with nothing.  Seems like the process is just idle but yet it has high CPU usage.  We are using SQL Server 2012 SP1 on Windows 2012 R2.

Thanks!

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is?

It is related to Full text catalog.

You can find out more info using sys.dm_fts_outstanding_batches

e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches in progress

SELECT
 OBJECT_NAME(ob.table_id) AS table_name,
 fc.name AS catalog_name,
 COUNT(*) AS outstanding_batches
FROM sys.dm_fts_outstanding_batches ob
INNER JOIN sys.fulltext_catalogs fc
 ON ob.catalog_id = fc.fulltext_catalog_id
INNER JOIN sys.databases d
 ON ob.database_id = d.database_id
GROUP BY
 ob.table_id,
 fc.name;

Also, you can use sys.dm_fts_index_population to get details about full-text index populations currently in progress.

  I can't figure out what the process is doing, even tracing it comes up with nothing.

You can use Adam Machanic's - sp_WhoIsActive to get a more detailed view.

As a side note, sysprocesses is deprecated and is not accurate as per KB - Information on CPU and Physical_io Columns of Sysprocesses

  sysprocesses.cpu is accurate but only updated at certain points. For lengthy operations that do not return any data to the client, it may not be updated for long intervals of time. Some of these operations may include DBCC CHECKDB, CREATE INDEX, database recovery, long-running joins, and so on. From a practical standpoint, it may often appear to be updated only at the end of each command.

If high CPU utilization is a frequent symptom on your server due to FullText, then highly suggest you to look into Improve the Performance of Full-Text Indexes

A good practice would be to baseline your server instance.


select row where a couple of column values is different I have a table A with thoses columns : date, ip, created_by.

I would like to display date, ip and created by of rows where for one ip it exists different created_by.

Thank you

I explain more :

table answers:

date    ip    user_id   something_else    something_else
--------------------------------------------------------

table users:

id  name  siret tel email...
----------------------------

I would like the list of answers from answers table (date + ip + name_of_the_user +  siret_of_the_user) where for the same ip I have different users.

thank you

IPs with different created_by:

SELECT ip
     , Count(DISTINCT created_by) As number_of_created_by
FROM   your_table
GROUP
    BY ip
HAVING Count(DISTINCT created_by) > 1

Alternative method:

SELECT ip
     , Min(created_by) As min_created_by
     , Max(created_by) As max_created_by
FROM   your_table
GROUP
    BY ip
HAVING Min(created_by) < Max(created_by)

Something like this should suffice for getting the details of these "rows":

SELECT *
FROM   your_table As outer_part_of_query
WHERE  EXISTS (
         SELECT ip
              , Count(DISTINCT created_by) As number_of_created_by
         FROM   your_table
         WHERE  ip = outer_part_of_query.ip
         GROUP
             BY ip
         HAVING Count(DISTINCT created_by) > 1
       )


Can SQL Server prevent the pagefile from being moved? On a production server of ours the page file has grown to 30gb+.  The Sys Admin has moved the Page File to a different drive, but for some reason the old one won't be removed.  He thinks that SQL Server may be the culprit.

Could SQL Server prevent the page file from being moved?

I'd love to see a screenshot of the Virtual Memory applet, here's the one from my notebook:

SQL Server does not interact directly with the pagefile.  Only the operating system itself is capable of doing that since the O/S holds an exclusive lock on the file while it is using it.

If SQL Server is the only load running on the machine (it should be for all but the simplest installations), and is correctly configured, the page file should be for most purposes irrelevant.

The SQL Server database engine has some very sophisticated memory management techniques, which can be managed by settings such as lock pages in memory, LargePageAllocator (see http://support.microsoft.com/kb/920093 for details), max server memory and min server memory, etc.

In my opinion, for a machine dedicated to SQL Server, I typically set the page file to a low amount, such as 4GB.  I set max server memory to an amount close to the amount of physical ram in the machine (less than 94% of memory, leaving at least 2GB free), and set min server memory to the same amount.  I then watch performance counters related to the page file, such as pages/sec etc, and SQL Server's Page Life Expectancy (see http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx) to make sure SQL Server is not paging memory to disk.  If SQL Server is paging to disk, or the Page Life Expectancy is low (this is the number of seconds a page is retained in the buffer pool on average) I will request that physical memory be increased in the machine.

For instance, on the last production machine I worked on, we had 192GB of RAM, and had the page file set to 4GB.  The default setting Windows "suggested" would have been 288GB, a clearly ridiculous amount.

For further info on this topic, see:

https://dba.stackexchange.com/a/20492/10832

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

https://dba.stackexchange.com/a/10201/10832


Seek predicate not using all available columns I have a strange query compilation issue which is hard to reproduce. It only happens under high load and cannot be easily repeated.

There is a table T with columns A,B,C,D.
There is a non-unique clustered index on T(A, B, C, D).
There is a query SELECT * FROM T WHERE A=@P1 AND B=@P2 AND (C=@P3 OR
C=@P4) AND D=@P5. The seek condition is on all columns of the
clustered index, the 3-rd column has an OR.

The problem is that the query plan for this query has Seek Predicate only on A and B! The predicate on C and D is an ordinary predicate, so this means that search tree on columns C and D is not utilized.

The data types for all parameters match column data types.

Could anyone provide any hints on why this could be happening? SQL version is 2008 R2 (SP1) - 10.50.2789.0 (X64)

For a parameterised query It can't just do two seeks on

WHERE A=@P1 AND B=@P2 AND C=@P3 AND D=@P5

and

WHERE A=@P1 AND B=@P2 AND C=@P4 AND D=@P5

Because if @P3 = @P4 that would incorrectly bring back duplicate rows. So it would need an operator that removed duplicates from these first.

From a quick test this end it appears to be dependant upon the size of the table whether or not you get that. In the test below 245/246 rows is the cut off point between plans (this was also the cut off point between the index fitting all on one page and it becoming 2 leaf pages and a root page).

CREATE TABLE T(A INT,B INT,C INT,D INT)

INSERT INTO T
SELECT TOP (245) 1,2,3,5
FROM master..spt_values v1

CREATE CLUSTERED INDEX IX ON T(A, B, C, D)

SELECT index_level,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(),object_id('T'),1,NULL, 'DETAILED')

DECLARE @C1 INT = 3,
        @C2 INT = 4

 SELECT * FROM T WHERE A=1 AND B=2 AND (C=@C1 OR C=@C2) AND D=5

 DROP TABLE T

1 Pages / 245 rows

This plan has a seek on A=1 AND B=2 with a residual predicate on (C=@C1 OR C=@C2) AND D=5

2 leaf Pages / 246 rows

In the second plan the extra operators are responsible for removing any duplicates from @C1,@C2 first before performing the seek(s).

The seek in the second plan is actually a range seek between A=1 AND B=2 AND C > Expr1010 and A=1 AND B=2 AND C < Expr1011 with a residual predicate on D=5. It still isn't an equality seek on all 4 columns. More information about the additional plan operators can be found here.

Adding OPTION (RECOMPILE) does allow it to inspect the parameter values for duplicates at compile time and produces a plan with two equality seeks.

You could also achieve that with

;WITH CTE
     AS (SELECT DISTINCT ( C )
         FROM   (VALUES (@C1),
                        (@C2)) V(C))
SELECT CA.*
FROM   CTE
       CROSS APPLY (SELECT *
                    FROM   T
                    WHERE A=1 AND B=2 AND D=5  AND C = CTE.C) CA

But actually in this test case it would likely be counter productive as having two seeks into the single page index rather than one increases the logical IO.


Differences of latch and lock Recently when i was trying to find information about latch_ex wait type, i come across one blog as below which stated about latch and lock.

After reading this blog , i m just curious in one thing. When app submit a request, SQL server will look for the information in buffer cache first and if the page not in buffer cache ,then only it will read from disk and put it in buffer cache before send info to app. My question based on the screenshot as above where it stated latch and lock need to avoid two thread updating same page. Basically all request come to SQL server will go to buffer cache first, if the page in buffer cache is busy updating, another thread will have to wait. It wont go back to disk because the page already in memory. Then what is the purpose of lock since every request will be done through memory and there is latch to protect the page

I don't really understand what you don't understand in the quote you included (strangely, as a screenshot), as it explains the difference quite clearly, in my opinion.

Locks and latches have different scopes and lifecycles. Locks apply to what you might call database physical model elements -- tables, rows, index entries. Latches protect various memory structures the database server uses when executing SQL statements or performing its housekeeping tasks.

A transaction might hold one, multiple, or no locks at all on the objects it is processing, which signals to other transactions what access they can have to those objects. An object protected by a lock doesn't have to be "in memory"; for example, a table protected by a table-level lock may not even have any of its pages present in the bufferpool.

Worker threads acquire and release latches to strictly prevent other concurrently running threads (that may be executing tasks within the same transaction, other transactions, or on behalf of some server background process) from simultaneously accessing certain memory areas. For example, two transactions may hold locks for different rows on the same bufferpool page, which would not prevent them from concurrently accessing their respective rows, if it weren't for the page latch that ensures the entire page remains consistent for all readers and writers. And then there is a lazy writer process, which couldn't care less about any of those locks but still must acquire a latch before it can write a consistent page out to disk.

In other words, locks are a transaction synchronisation mechanism while latches help synchronise processes or threads.


Update using select in DB2 I have 2 tables

Table A
address_id email
12341      aba@mail.com
12342      abb@mail.com
12343      abc@mail.com
12344      abd@mail.com
12345      abe@mail.com
12346      abf@mail.com
12347      abg@mail.com
12348      abh@mail.com
12349      abi@mail.com

Table B
address_id email
12342      xyz@mail.com
12344      xyy@mail.com
12349      xyx@mail.com

I want to update emails in table A with emails in Table B where the address_id matches.

I have tried the below query :

update TableA A
set email=(select email from TableB B where A.address_id=B.address_id)

But its updating all the emails to null where the address_id is not matching in TableA.

Appreciate your help..

An UPDATE without WHERE clause will update all the rows of the table. For those rows that don't have a matching address_id in table B, the subquery returns an empty result set, so the value is updated to NULL.

There's probably a more elegant way to do it but this should only update the matching rows:

update TableA A
set email = (select email from TableB B where A.address_id = B.address_id)
where exists
      (select 1 from TableB B where A.address_id = B.address_id) ;

Another option is to use MERGE:

MERGE INTO TableA AS A
USING
  ( SELECT address_id, email
    FROM TableB
  ) AS B
  ON A.address_id = B.address_id
WHEN MATCHED THEN
  UPDATE SET email = B.email ;


Getting around the constraint "column must appear in the GROUP BY clause or be used in an aggregate function" I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function.

Lets say I'm modelling peoples' cars, and I want to work out a person's name, license number, and how many cars they have. Here's my example as an SQL Fiddle.

I would have the following schema:

CREATE TABLE person(
  id integer PRIMARY KEY,
  name text
);

CREATE TABLE license(
  person_id integer REFERENCES person(id),
  expiry_date date
);

CREATE TABLE car(
  owner_id integer REFERENCES person(id),
  registration_number TEXT
);

Here's the query:

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.id;

I know, because of my own business logic, that one person can only have one license, so when I join to the person, even though it's GROUP BY'd, I should be able to find their license number. However because it isn't part of the GROUP BY statement and doesn't use an aggregate function, I can't access it.

How should I write this query in an idiomatic way. I've seen vague things about LATERAL JOINs, window functions, and subqueries, but I'm not sure the best way to answer this question.

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;


Oracle 12.2 : Is there a way to copy pdb from one server to another in production env From time to time, customers need to send their database to us for analysis. We like the PDB feature of Oracle 12.2, and we are willing to use it for shipping our customer's database for analysis

Can a customer create the db file while the database is up and running? We need them to keep the database up while they create the back up file.

The hot cloning of PDBs is a new feature introduced in 12.2.

Cloning a PDB

  Cloning of a pluggable database (PDB) resolves the issue of setting
  the source system to read-only mode before creating a full or snapshot
  clone of a PDB. With this feature, you can now clone your production
  PDBs and create point-in-time copies for development or testing
  without any application outage.

  This feature eliminates the need to create application outages for the
  purpose of cloning.

So the PDB in question can be easily cloned without downtime, then the customer can send you the clone PDB.


Oracle partitioning scenario with primary key absolutely required Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?

Yes, reference partitioning requires a primary key on the parent table.

  Reference partitioning enables the partitioning of two tables that are
  related to one another by referential constraints.

  The partitioning key is resolved through an existing parent-child
  relationship, enforced by enabled and active primary key and foreign
  key constraints.

If you try to do this with a unique key instead of a primary key, you will get this error:

ORA-02268: referenced table does not have a primary key


Save Transaction: Is the name local to the stored procedure or not? Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused.

There is a subset of stored procedures that preserve state on failure. That is, if the procedure raises an error, it anyway can be committed safely. This is done with save transaction.

Now, my initial understanding was that the savepoint name you set for the transaction is local to the stored procedure that uses it. And in a nested scenario, if the inner stored procedure saves under the same name, it is actually a different name, and all is fine.

To make sure this understanding of mine was correct, I set up a test case:

create table dbo.[footest] (
  v varchar(50) NULL
);
go

insert into dbo.footest(v) values ('Nothing'); go

create procedure dbo.[foo_test_tran_inner]
as
begin
  set nocount on;

  declare @foo int;

  begin tran;

  update dbo.footest set v = 'Inner, before savepoint';

  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Inner, after savepoint';

    set @foo = 1/0;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;

  commit tran;

  set @foo = 1/0;

  return 0;
end;
go

create procedure dbo.[foo_test_tran_outer]
as
begin
  set nocount on;

  begin tran;

  update dbo.footest set v = 'Outer, before savepoint';

  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Outer, after savepoint';

    exec dbo.foo_test_tran_inner;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;

  commit tran;

  return 0;
end;
go

begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

This yields "Outer, before savepoint". Which means, the savepoint names were local to the procedure and were properly rolled back in a nested scenario.

In production, those state-preserving stored procedures have exactly this schema. There is much more code in them, but if you remove it, only leaving saves, rollbacks and commits, it will be exactly what is shown above.

But. It didn't work in production. Instead, each nested savepoint with same name appeared to overwrite a previous savepoint, made from the calling procedure. And when the outermost code after receiving an exception executed a commit (believing the inner rollbacks are properly done), the database was left in a half-screwed state. If applied to the example above, that would mean the select returns Inner, before savepoint.

I stepped through every step in the production procedure with a debugger, and confirmed that execution properly went through all expected save tran a_name and rollback tran a_name.

To fix the problem in production, I replaced this:

save tran the_constant_name;
...
rollback tran the_constant_name;

with this:

declare @savepoint varchar(32) = replace(newid(), '-', '');
save tran @savepoint;
...
rollback tran @savepoint;

and that fixed it immediately.

So what gives? Is the savepoint name local to the stored procedure or not?
If it is, then why the production code did what it did, and was successfully fixed as shown?
If it is not, then why the test example above does what it does?


Simple SELECT taking too long to run I am running this simple query:

SELECT *
FROM ABC_TABLE
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'

There are 64 columns and we need all of them.

There are a total of 2,066,381 rows for year 2018. Total row count is 2,549,803.

There is one index which has INVOICE_NUMBER, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, ITEM_ID columns.

I am using SQL Server 2014.

I am unable to understand why it is taking more than half an hour.

select count(*) from ABC_TABLE was executed in a fraction of a second.

Even if I use few columns in the select statement it takes really long to respond like the below one (>3 mins):

select COUNTRY_ID,
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID,
sum(s_cost)
from abc_table
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'
group by COUNTRY_ID,
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID

Basically, you have 3 options, using regular indexes (b-trees):

Create a clustered index on DAY_DATE. But chances are that you already have a clustered index on something else, and you cannot, for some reason, change that.

Create a non-clustered index on DAY_DATE. Whether or not this will be used by the optimizer depends on how many rows you return for that condition (2,066,381) compared to number of rows in the table (unknown to us). For each row, SQL Server would need at least one page access to go fetch that page.

Create a non-clustered index that covers the query with DAY_DATE as the key column and the other columns that you query refers to as included columns. Since you have to do SELECT *, then you would need all columns in that index, making it as big as the data it self (a bit bigger, even). Quite likely not doable.

A columnstore index might help, depending on the compression level you achieve in the end. But, again, that index would need all columns!

Bottom line: Do you really need that many rows and that many columns?


Server side trace to database How can I set up a server side trace that dumps to a database instead of a trace file?

I set-up and tested a trace configuration I want with Sql Server Profiler that dumps only needed info straight to a separate database (without a .trc file).
When I export this trace as sql statement however, I see it is configured to save to .trc file instead of a database.

Either my googling-fu is letting me down or nobody is interested in this.

I highly doubt no one is interested! :-)

Here's my link collection that I used to get into the topic:

http://www.toadworld.com/platforms/sql-server/w/wiki/10400.the-server-side-trace-what-why-and-how.aspx
http://www.mssqltips.com/sqlservertip/1035/sql-server-performance-statistics-using-a-server-side-trace/
http://consultingblogs.emc.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx

Hope those help you as well !

Update:

Ok, to clear things up:

in order to create a server-side trace, you need a trace definition script
to create that trace definition script, the easiest approach is to use SQL Server Profiler to define what you want to trace - which events, what properties
you then use SQL Server Profiler to create that server-side trace definition script for you (doing it manually, from scratch, is a major hassle)
but then, to execute - you don't use SQL Server Profiler (that's putting too much load on your server), but you just create and run the trace on the SQL Server


Update xml based off datatype I need to update a value in my xml datatype column, currently I am able to pull specific values using

Select AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') AS AssetId...

My data in the column looks like this, because this is from Sharepoint and I cannot modify the xml format in any way.

<bit1>0</bit1>
<nvarchar1>1995</nvarchar1>
<nvarchar3>Ford</nvarchar3>
<nvarchar5>White</nvarchar5>
<nvarchar6>Van</nvarchar6>
<nvarchar7>123456789</nvarchar7>
<nvarchar9>1234</nvarchar9>
<nvarchar13>623</nvarchar13>
<nvarchar20>Company</nvarchar20>
<nvarchar22>Dept</nvarchar22>
<nvarchar23>Section</nvarchar23>
<nvarchar24>User</nvarchar24>

I have tried to piece together a statement from other questions much like the example with no success and no working where clause as shown below.

update AllUserData
set tp_ColumnSet.Modify('replace value of (nvarchar1)[1] with "1996"')
where AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') = 623

How can I modify the the "Year" by designating what AssetID it is with a where clause?

Msg 227, Level 15, State 1, Line 20 "Modify" is not a valid function,
  property, or field.

XML is case sensitive and in SQL Server so are the functions used on the XML datatype. Change Modify(... to modify(....

If you have untyped XML you will still get an error.

  Msg 2356, Level 16, State 1, Line 21 XQuery
  [AllUserData.tp_ColumnSet.modify()]: The target of 'replace value of'
  must be a non-metadata attribute or an element with simple typed
  content, found 'element(nvarchar1,xdt:untyped) ?'

To fix that you need to specify the text() element of the node you want to update.

You could also make use of the exist() function in the where clause instead of extracting the value.

update dbo.AllUserData
set tp_ColumnSet.modify('replace value of (/nvarchar1/text())[1] with "1996"')
where tp_ColumnSet.exist('/nvarchar13/text()[. = "623"]') = 1;

From comment:

  The XQuery modify method is not allowed on sparse column sets.

You need to update the value using the regular column instead of the column set.

update dbo.AllUserData
set nvarchar1 = N'1996'
where nvarchar13 = N'623';


How to move database from Db2/Linux/System-z to Db2/Linux/Intel our company has decided to migrate database:

from source Db2 Enterprise Server Edition v11.1 fixpack 5 on Linux/System-z (this is still Linux/Unix/Windows type of database despite running on System-z mainfraime)
to Db2 Enterprise Edition v11.1 fixpack 5 on x86_64 (Intel/AMD).

Linux/System-z is big endian system and Linux/x86_64 is little endian system. There is no backup/restore option between bin/little endian Linux system.
The other option that I have read a lot about is combination of: db2look/db2move and a plenty of manual tasks. This seems pretty dangerous operation, because one little mistake and something may go terrible wrong when applications start using database on target system.
On PostgreSQL system there is build-in pg_dump tool. With this tool one single command dumps whole database (all objects: create database, create table, triggers, foreign keys, primary keys, etc including all of the data) into one single huge file, that can be easily transferred   to target system and then one single command is executed on target system to get the database up and running. There are no manual interventions, tricks, procedures etc. Just two commands, one on source and one on target system. Is there something similar on Db2?
Is there any other way to move database between two Linux/endian systems except db2look/db2move? Some sophisticated tool or something?
Regards

Check IBM Data Movement Tool. Link https://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/


ORA-01017 error when conecting to Oracle Database Standard Edition 2.12.2.0.1.0 for Microsoft Windows x64 (64-bit) I've installed and created non-CDB database (single tenant) database. Created a user account using the SYS user, granted it the CONNECT and CREATE SESSION roles. When I attempt to login using the newly created account I get the "ORA-010170: invalid username/password; login denied" error.

I'm I missing something with respect to the Oracle 12c Standard database?? Since I've created a "normal" database I should be able to login with a non SYS account.

Thanks!!

Check the netca config. There needs to be an entry in tnsnames.ora.


Background Index Creation on SQL Server I have no maintenance plans yet I see indexes being created in the background (low spid). I have no idea what indexes are being created or why. Using Standard SQL Server 2017 - a VM on Azure with SQL Installed.

I checked and did not see anywhere with automatic index management.  You can configure backups, etc. but I cannot find a spot for indexing.

I tried EXEC sp_WhoIsActive @show_system_spids = 1; and got this result:

(542701ms)RESOURCE_SEMAPHORE

I think what is happening is I have a "messed up index" and the system is trying to fix it.  I cannot drop the index whatsoever or rebuild it.  At least that is my guess.

This sounds a lot like the Automatic tuning features of Azure SQL Database.  In particular, one of the options is:

  CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.

You can view these settings here:

You mentioned that you are on SQL Server 2017, though - so this doesn't apply to you per se.

Is this a Managed Instance perhaps?  The docs currently state that automatic index management is not available in MI, but that may have changed from what I hear.

Since you are on an Azure VM, this won't be automatic index tuning.  You're best bet is to try and capture more information about this process by running the following sp_WhoIsActive command while this is occurring (something that was suggested in a similar Q&A from several months ago):

EXEC sp_WhoIsActive @show_system_spids = 1;

In your particular case, it seems index creation was being rolled forward by the database startup/crash recovery process. You would have seen the database in a non-online state, and recovery progress messages in the error log.


Why is row-level access needed when views can limit a person's access to rows and/or columns? So I've always in general been confused about what the difference between the security of a vew and of Row Level Security.
so I guess my general question is
Why is row-level access needed when views can limit a person's access to rows and/or columns?

I'm assuming you're asking this question in the context of Microsoft SQL Server since Row-Level Security is a feature they offer. While there are similarities between using a View to implement a mock row-level security and using the Row-Level Security feature of SQL Server, there are a few differences as well:

Separation of Concerns - Row-Level Security abstracts out the security portion from your entities to allow for cleaner and simpler code.

Manageability - With Row-Level Security, you create a Policy entity that Users can be added to. That Policy leverages a Function where you define your logic for the security Policy. This eliminates the need for custom Table(s) to store your users and their access roles.

Reusability - You can use the same Policy for multiple Tables without the need of coding repeated logic.

Flexibility - You can easily turn the Policy ON and OFF for any Table by setting the STATE of that Policy.

Performance - Generally the custom logic used to emulate security at the Row-Level in Views can negatively affect performance if the predicates are too complicated. I've seen great performance improvements by removing this logic from your View's predicates and creating an Inline Table Valued Function with the same exact logic used in a Policy for Row-Level Security. (I don't fully know why but by looking at the Execution Plan in both scenarios, there is definitely a difference which I think is related to the security logic being abstracted out and applied separately from the predicates in the View.)

For more information you can read on Microsoft's Row-Level Security docs.


Mysql multiple check and multiple update queries i have question about checking and updating.
The problem is that i create a very basic mysql select and update , and
two times in day i get file where php reads it. In that file have more than 14k-60k records. Database checks if that id exists then make update for 2 columns but if that record doesnt exist then insert.

foreach($product_list as $id => $var) {if(mysql_num_rows(mysql_query("SELECT product_id FROM products WHERE product_id = $id")) == 0) { // insert}else{//update}}

problem is that, that script is taking very much resources and execute very long.
Is it possible to speed up this process or somehow make it in few queries? I understand that there is going more than 60k queries in this code.
P.s this is simple example with mysql but in real code im using PDO but system is like the same as this code.

I can suggest little more longer method, but work fine:

Load file into table, for example TableB (original in this case TableA), TableB has only PK - id
Update - before INSERT

UPDATE TableA t1 INNER JOIN TableB t2 ON t1.id = t2.id

Insert

INSERT INTO TableA
SELECT *
FROM TableB t1
WHERE NOT EXISTS
      (SELECT id FROM TableA t2 WHERE t2.id = t1.id)

I prefer this way, because:

it fast
it flexible - steps in-dependent and logic for UPDATE/INSERT could be
changed separately


Move database files if database runs is possible, is there internal procedure to moving with database files if database runs, is not detached on Sql Server 2012/2008, .... EDIT.... database is online, accesible for users, services running in Native OS
moving from one disk to another, initialized in Windows OS as HDDs
or only is possible to create a replica in another SQL instace and if done then to switch between DB files
my question is only about how to shorten the time until the instance is unavailable (detach, copy, attach, testing of consistency)

If you have multiple data files for a database and you are trying to move one from one disk to another, I believe you can add new data file on the new drive to your filegroup, and then use ShrinkFile with the EmptyFile option on the file to be moved to migrate data from that file into the other files in the filegroup (SQL Server will distribute across the files automatically).  The only problem with this is that you cannot do this for the first file of the database's primary filegroup, which I'm betting will be an issue for you.


SQL server Audit- Does it cause overhead If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb? Does that increase the .ldf log size of the database its monitoring?

Any issues this can cause that I should be aware of?

If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb?

To directly answer your question, yes. Auditing always has overhead. If you have to write down what you've done and call extra code that wouldn't normally be called then by definition it'll have overhead.

Whether that overhead is appreciable enough to cause you an issue we can't say. This is completely dependent upon:

How the auditing is implemented
What you are auditing
Where you are holding your audit information

There will be a different in performance using some 3rd party solution utilizing detours/iat/eat hijacking/whatever versus a trace vs security (extended events). Your mileage will vary with all of these (not all supported) choices.

  Does that increase the .ldf log size of the database its monitoring?

If, for some reason, you keep the audit records in a table in that database then yes... otherwise, no.


Copying datadir directory enough for backing up a database with InnoDB tables? I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer.

I used to copy database with only MyISAM tables in Windows version of MySQL and it worked perfectly. Now that I'm copying a database directory with a mix of InnoDB and MyISAM tables from Linux to Windows, would this approach work as well?

I'm currently doing this right now but I want to know if I'm missing anything? Ain't InnoDB tables data spread across various places such as in ibdata1? I don't have to backup ibdata1 or anything right?

Please tell me you are not (and not even thinking of) using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-)

It's not very clear from your post (correct me if I'm wrong), but you're now running your database on Linux? If so, then use Percona's XtraBackup for your InnoDB tables. This tool performs non-blocking hot backups of InnoDB tables.Take a look at my answer here for some MySQL backup options. For MyISAM, you could look at MyDumper if mysqldump isn't performant.

If your server is still on Windows, then you're out of luck. XtraBackup doesn't work on Windows, nor does MyDumper (AFAIK). You could check this out (disclaimer - never used).

Again, I'm not totally clear on what, exactly, you mean here.

  Now that I'm copying a database directory with a mix of InnoDB and
  MyISAM tables from Linux to Windows, would this approach work as well?

If you mean that you are copying the mysqldump from Linux to Windows, then yes, that will work as any file copy. If, on the other hand, you mean that you are copying (using the file system) data files on a running server and tranferring those copies from Linux to Windows, then your "backups" will be useless on both systems.

  Ain't InnoDB tables data spread across various places such as in
  ibdata1? I don't have to backup ibdata1 or anything right?

If you are backing up the database using mysqldump, then that is sufficient. If you are simply copying files from one location to another on a running database server, then it really doesn't matter what you do or don't copy, because your backup will be useless.

[EDIT] in response to the original poster's comments.

From here (correct answer)

  mysqldump -uuser -ppass --single-transaction --routines --triggers
  --all-databases > backup_db.sql

Just substitute your schema name for --all-databases. You say that your "table" is InnoDB - is the rest of the database/schema InnoDB? If not, you should make it so. You seem to think that the datadir has to be copied in some shape or form - it DOES NOT. The dump contains the necessary data to reconstruct your database.

In the event of a problem, you restore your database from the last BACKUP (i.e. the dump). There are other solutions which allow PITR (Point In Time Recovery), but they are more complex and replication can also address this issue.

See also these posts (1, 2).

My take on the content of these posts (check the reputations of the posters) is that with InnoDB, the above command will allow for a backup while at the same time permitting reads and writes to the database - note that performance may be affected.


How to query for 'First showed up as' Given the following table:

 Date       Trd (INT(10))   Code (VARCHAR(10))  T-Date
 1-Jan-14   1               B                   1-Nov-13
 1-Jan-14   2               B                   3-Oct-13
 1-Jan-14   2               B                   3-Oct-13
 2-Jan-14   1               A                   1-Nov-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   3               A                   2-Jan-14
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   3               A                   2-Jan-14
 3-Jan-14   4               C                   3-Jan-14

How do I SELECT, to get the following result:

 Date       Trd (INT(10))   First showed up as Code   In Date       T-Date
 3-Jan-14   2               B                         1-Jan-14      3-Oct-13
 3-Jan-14   3               A                         2-Jan-14      2-Jan-14
 3-Jan-14   4               C                         3-Jan-14      3-Jan-14

So for a given Date (in this case Jan-3rd), GROUP BY Trd and find out with which Code it first showed up, and provide the Date that happened.

I have no idea where to start.

My solution using SQL Server is as below. I could not test it in mysql as my ubuntu server is down. Also, I am not sure if mysql implements cte and window functions yet. In such case you may have to re-write the query as a stored procedure.

--Table definition

create table test
(f_date date,
trd int,
code varchar(10),
t_date date);

--Populate table

insert into test
values
 ('1-Jan-14',   1,          'B',                  '1-Nov-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('2-Jan-14',   1,          'A',                  '1-Nov-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   4,          'C',                  '3-Jan-14');

--Quey code

 ; with cte as
 (select RANK()over(partition by trd order by f_date) as rank_no, trd, code, f_date
 from dbo.test)
 select distinct test.f_date as 'Date', cte.trd as 'Trd', cte.code as 'First showed up as code',
 cte.f_date as 'In Date', test.t_date as 'T-Date'
 from test
 join
 cte on  test.trd = cte.trd
 where test.f_date = '20140103'
 and cte.rank_no = 1;

--Results

Date    Trd First showed up as code In Date T-Date
2014-01-03  2   B   2014-01-01  2013-10-03
2014-01-03  3   A   2014-01-02  2014-01-02
2014-01-03  4   C   2014-01-03  2014-01-03


Postgres Large Objects & Multiple Users Problem

Dynamically created users are not able to query a table that contains Large Objects as it did not originally create it.

Background

I have an Java Atom Hopper application deployed to AWS across 2 instances, both using a Postgres 9.5.2 database hosted in RDS.

Vault Credentials

The database credentials are stored in Vault, which generates a new login and password for the databases in Postgres. This user is created with the following permissions.

CREATE ROLE "{{name}}" WITH INHERIT LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';GRANT ALL PRIVILEGES on DATABASE "DATABASE_NAME" to "{{name}}";GRANT databaserole to "{{name}}";

This means each time the Java application starts, it sends a request to Vault, to get a new username and password.

eg. username: token-1234-5678
=> \du

       Role name     |                  Attributes                   |   Member of
    -----------------+-----------------------------------------------+----------------
    token-1234-5678  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 1)
    token-abcd-efgh  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 2)

The tables inside of the database are all owned by databaserole

Schema |          Name           | Type  |     Owner
-------+-------------------------+-------+----------------
public | table1                  | table | databaserole
public | table2                  | table | databaserole
public | entries                 | table | databaserole

Inserting Data

When I insert a new row into table entries, one of the fields is too large and therefore converted into a Large Object, but its owner is set to the login of the app that inserted it, rather than database role.

=> \lo_list
            Large objects
  ID   |     Owner       | Description
-------+-----------------+-------------
 17286 | token-1234-5678 |

As the Java application on instance 2, has a different login to instance 1 eg. token-abcd-efgh, it means it cant execute select * from entries; as it is not the owner of the Large Object.

I am aware that I can change the owner of the object after it has been created, but this is not feasible due to the number of insertions.

Question

How can I set it so that any Large Object has the owner of databaserole rather than the login of the application that created it?

TLDR

As I have an multiple instances of an app, with unique and dynamically created logins. How can I setup Postgres so that the Large Objects always have the ownership of the role's member parent, so that any login that is a member of this parent role can view the object?

SOLVED

I added another transaction to run the follow sql everytime hibernate tries to insert on that table.

SET ROLE databaserole

One option is to use SET ROLE command after you open a connection from your application to the databaserole:

SET ROLE databaserole;

In such way any object (including large objects) created within this session will be owned by databaserole instead of token-XXX.

Another option is to use LOCAL and make it work only within the transaction used to create the large object:

BEGIN;
SET LOCAL ROLE databaserole;
-- create and insert the large object
COMMIT;

Alternatively, you could just set the role directly as the user setting:

ALTER ROLE "{{name}}" SET role TO 'databaserole';

I kind of dislike that option as it can become a bit obscure to others how it is working, and if you manage other roles to your user it won't inherit them (although that doesn't seem like a problem to you, as with dynamically created user you should have a single role that it directs inherit from).


Select data based on records created_at column I have two tables Keywords, ProjectReports:

http://sqlfiddle.com/#!15/06ae3/1

As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know how.

In the position column I would like to display only the smallest value and just one value instead of that array. ex: [1, 2, 3]  will be 1
As you can see there is the keyword with id 4 which doesn't have any data in the project_reports table, in this case I would like to add the value 0 in the position column and add the current day date in the CREATED_AT column.
Also I would like to display the position value for the same keywords, where created_at column from project_reports table had the value now() - interval '1 day'; now() - interval '1 week'; now() - interval '1 mon', if there is no position for those days add 0.

Expected output:

Keywords:

 ID: 1 name: test
 ID: 2 name: test1
 ID: 3 name: test2

Project_reports:

 ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
 ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'
 ID: 3, keyword_id: 1, project_id: 1, position: 3, created_at: '2014-07-10'
 ID: 5, keyword_id: 1, project_id: 1, position: 4, created_at: '2014-07-10'
 ID: 6, keyword_id: 2, project_id: 1, position: 1, created_at: '2014-07-09'

After query

k_name: test, k_id: 1, p_id: 1, position 3, yesterday_position: 1, created_at: '2014-07-10'
k_name: test1, k_id: 2, p_id: 1, position 0, yesterday_position: 1, created_at: '2014-07-10'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-10'
k_name: test, k_id: 1, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test1, k_id: 2, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-09'

Note the slightly modified schema of my test in the fiddle.
Using actual primary keys and proper column names instead of id.

Also, you seem to be operating with dates exclusively. So I suggest to convert your timestamp columns to date.

Items 1 and 2

SELECT k.keyword_id
     , k.name
     , pr.project_id
     , COALESCE(min(pr.position), 0) AS pos
     , COALESCE(pr.created_at, now()::date) AS created_at
FROM   keyword             k
LEFT   JOIN project_report pr USING (keyword_id)
GROUP  BY k.keyword_id, pr.project_id, pr.created_at
ORDER  BY keyword_id, created_at
;

In Postgres 9.1 or later the pk column covers the whole table in GROUP BY.
Use COALESCE to replace possible NULL values.

A guess at item 3

WITH cte AS (
   SELECT k.keyword_id
        , k.name
        , pr.project_id
        , COALESCE(min(pr.position), 0) AS pos
        , COALESCE(pr.created_at, now()::date) AS created_at
   FROM   keyword   k
   LEFT   JOIN project_report pr USING (keyword_id)
   GROUP  BY k.keyword_id, pr.project_id, pr.created_at
   )
, x AS (
   SELECT DISTINCT ON (keyword_id, project_id) *
   FROM   cte
   ORDER  BY keyword_id, project_id, created_at DESC
   )
SELECT x.*
     , COALESCE(y.pos, 0) AS yesterday_pos
     , COALESCE(w.pos, 0) AS week_pos
     , COALESCE(m.pos, 0) AS month_pos
FROM   x
LEFT   JOIN  cte y ON y.keyword_id = x.keyword_id
                  AND y.project_id = x.project_id
                  AND y.created_at = x.created_at - interval '1 day'
LEFT   JOIN  cte w ON w.keyword_id = x.keyword_id
                  AND w.project_id = x.project_id
                  AND w.created_at = x.created_at - interval '1 week'
LEFT   JOIN  cte m ON m.keyword_id = x.keyword_id
                  AND m.project_id = x.project_id
                  AND m.created_at = x.created_at - interval '1 month'
;

Explain

In CTE cte produce daily aggregates per (k.keyword_id, pr.project_id).
In CTE x pick the latest day per (k.keyword_id, pr.project_id).
In the outer query LEFT JOIN the latest day x to cte multiple times to retrieve past values from the same (k.keyword_id, pr.project_id) for 1 day / week / month earlier.

SQL Fiddle.


Display all employee i want to display all employee from tbl_Employee_Master ,weather emp_Id is exit or not in  #tbl_assign_Allowance_Deduction table
I developed a query,but it fetching those record where asd.sal_ID is null,but i want to fetch all employee from tbl_Employee_Master table,when ever this query is run'
Create Table #tbl_Designation (Designation_ID int, Designation_Name varchar(50));
CREATE TABLE #tbl_Employee_Master (emp_ID INT,emp_Name Varchar(50),emp_salary int,Designation_ID

    int,emp_OT int );
CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT,DA_Name Varchar(50), DA_TYPE_ID Varchar(50));
CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT,EMP_ID int ,DA_ID int, Assign_amt int,Sal_ID int);

Insert into #tbl_Designation values
(1,'officer'),
(2,'Executive')

Insert into #tbl_Employee_Master values
(1001,'Irfan',10000,1,20),
(1002,'Khan',10500,2,20),
(1003,'Akhter',1200,1,20) ,
(1004,'Ali',8000,2,20),
(1005,'Jhain',3300,1,20)

insert into #tbl_Type_allowance_Deduction values
(1,'Loan','Deduction'),
(2,'Medical','Allowance'),
(3,'Rent','Allowance'),
(4,'Conveyance','Allowance'),
(5,'Advance','Deduction'),
(6,'Mobile','Allowance')

insert into #tbl_assign_Allowance_Deduction values
(111,1001,1,500,111),
(112,1001,2,500,111),
(113,1002,3,540,111),
(114,1003,4,200,null),
(115,1003,6,400,null)

 SELECT  (M.Emp_ID) , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation,
       SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt)ELSE 0 END) Loan,
        SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt)ELSE 0 END) Advance,
           SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt)ELSE 0 END) Mobile,
               SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt)ELSE 0 END) Conveyance,
               SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt)ELSE 0 END) Medical,
                  SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt)ELSE 0 END) House_Rent,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour',asd.Sal_ID
FROM #tbl_Employee_Master M
 inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
   left   join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
   left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
 group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID

I developed a query,but it fetching those record where asd.sal_ID is
null,but i want to fetch all employee from tbl_Employee_Master
table,when ever this query is run'

The asd.sal_ID IS NULL criteria in the WHERE clause will exclude all employees with non-null deductions. Move that criteria the the ON clause of the allowance deduction LEFT JOIN to include allowance deduction rows with NULL Sal_ID values without excluding employees with non-null values too:
SELECT
      M.Emp_ID
    , M.EMp_Name
    , M.EMP_Salary
    , D.Designation_Name AS Designation
    , SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt) ELSE 0 END) Loan
    , SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt) ELSE 0 END) Advance
    , SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt) ELSE 0 END) Mobile
    , SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt) ELSE 0 END) Conveyance
    , SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt) ELSE 0 END) Medical
    , SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt) ELSE 0 END) House_Rent
    , ISNULL(M.emp_OT,0) AS emp_OT
    , 0 AS 'txtpresent'
    , 0 AS 'txtabsent'
    , 0 AS 'txtsalary'
    , 0 AS 'lbOT_Amount'
    , 0 AS 'txthour'
    , ASD.Sal_ID
FROM #tbl_Employee_Master M
INNER JOIN #tbl_designation D ON D.Designation_ID=M.Designation_ID
LEFT JOIN #tbl_assign_Allowance_Deduction ASD ON asd.Emp_ID=M.Emp_ID AND ASD.sal_ID IS NULL
LEFT JOIN #tbl_Type_allowance_Deduction T ON T.DA_ID=ASD.DA_ID
GROUP BY
      M.Emp_ID
    , M.EMp_Name
    , M.EMP_Salary
    , D.Designation_Name
    , M.emp_OT
    , ASD.Sal_ID;


Oracle outer join by (+) to Postgres conversion I migrated from the Oracle database to Postgres, but some SQL scripts crashed. I investigated the errors and I found out that Postgres doesn't support outer joins using by (+) convention.
I tried to overwrite this Oracle statement:
SELECT
       r1.fibre AS root_edge_sid,
       sp.sid AS node_sid,
       sp.fid AS node_fid,
       sp.ftid AS node_ftid,
       f.sid AS edge_sid,
       f.fid AS edge_fid,
       f.ftid AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID AS sid, f.FID AS fid, f.FTID AS ftid FROM TABLE_C f) f
WHERE r1.splice_point = sp.sid
  AND sp.sid = r2.splice_point(+)
  AND r2.fibre = f.sid(+)
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

to Postgres statement:
SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID  AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID  AS sid, f.FID  AS fid, f.FTID AS ftid FROM TABLE_C f) f
left outer join r2
on sp.sid = r2.splice_point
left outer join f
on r2.fibre = f.sid
WHERE r1.splice_point = sp.sid
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

according to Postgres Wiki but in console I get this error: relation "r2" does not exist
Is possible to make joins between sub-select statements?
Or is in the script any error?

All those derived tables are useless (even in Oracle).
As far as I can tell, this should be equivalent:
SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM table_a r1
  JOIN table_b sp ON r1.splice_point = sp.sid
  LEFT JOIN table_a r2 ON sp.sid = r2.splice_point
  LEFT JOIN table_c f ON r2.fibre = f.sid
WHERE r1.fibre = 123456
AND r1.fibre <> f.sid;

I think the condition r1.fibre <> f.sid in the WHERE clause was wrong as it turned the outer join back into an inner join.


Select with fallback and default? Can the following code be converted to database queries?
In our application we have a dictionary, where each value is given by in a table like:
CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)

Insertion is like:
INSERT INTO public.dictionary VALUES
    ('yes_button', 'en-us', 'yes'), ('yes_button', 'nl-nl', 'ja')

Now to get a translation one would do like
SELECT key, "en-us"
FROM dictionary
WHERE key in ('yes_button', ...)

Now the problem we have is that we wish to have "paths" to get to the default value, so if the value doesn't exist (or is NULL) it should follow a given path to lookup and get the nearest ancestor.
Our current solution is to do this calculation on the backend, but this basically means that we get the full translation table at once, or at least the full path to the root tree.
IE paths would be:
nl-be -> nl-nl -> en-us
en-gb -> en-us
fr-be -> fr-fr -> en-us

With the above insertion if I select nl-be as locale it would hence find "nl-nl", and thus return "ja". While if I select fr-be it would collapse to "yes".
Is it possible to provide such lookups given the above table directly? Or will this always be a task of the backend code and I should just get all translations for a given path?

Well this was fun. Usually I control language strings in the application layer rather than the database, but this was a good opportunity to learn how to use WITH RECURSIVE.
First let's define the tables that will be used: locale and dictionary:
CREATE TABLE locale (
    code           varchar(6)     NOT NULL    ,
    description    varchar(64)    NOT NULL    ,
    fallback       varchar(6)         NULL    ,
    PRIMARY KEY (code)
);

CREATE TABLE dictionary (
    id             serial         NOT NULL    ,
    key            varchar(64)    NOT NULL    ,
    value          varchar(128)   NOT NULL    ,
    locale_code    varchar(6)     NOT NULL    ,
    PRIMARY KEY (id)
);

There should be no surprises here. Locale's code can be 6 characters in length, though it will generally be either 2 or 5. If the fallback is NULL, then the record is considered "top level". This will allow you to have multiple top levels if you so choose, as you will see in the examples below.
Next, let's populate these tables:
INSERT INTO locale ("code", "description", "fallback")
VALUES ('en-us', 'English (US)', NULL), ('en-gb', 'English (UK)', 'en-us'), ('en-ca', 'English (Canadian)', 'en-gb'),
       ('fr-fr', 'French (France)', 'en-us'), ('fr-be', 'French (Belgium)', 'fr-fr'), ('fr-ca', 'French (Quebec)', 'fr-fr'),
       ('nl-nl', 'Dutch', 'en-us'), ('nl-be', 'Dutch (Belgium)', 'nl-nl'), ('ja-jp', 'Japanese', 'en-us'),
       ('es-es', 'Spanish', NULL), ('es-ar', 'Spanish (Argentina)', 'es-es'), ('es-bo', 'Spanish (Bolivia)', 'es-es'),
       ('es-cl', 'Spanish (Chile)', 'es-bo'), ('es-sv', 'Spanish (El Salvador)', 'es-cl'), ('es-mx', 'Spanish (Mexico)', 'es-cl');

INSERT INTO dictionary (key, value, locale_code)
VALUES ('yes_button', 'Yes', 'en-us'), ('yes_button', 'Very Well', 'en-gb'), ('yes_button', 'Sorry, Yes', 'en-ca'),
       ('yes_button', 'Oui', 'fr-fr'), ('yes_button', 'C''est exact', 'fr-ca'),
       ('yes_button', 'Ja', 'nl-nl'), ('yes_button', 'はい', 'ja-jp'),
       ('yes_button', 'Sí', 'es-es'), ('yes_button', 'Absolutamente', 'es-bo'), ('yes_button', 'Por favor', 'es-sv');

Note:These translations are not at all accurate, but instead intended to show how the final query works and how you might tweak it.
And now the SQL Query:
WITH RECURSIVE c AS (SELECT 1 as level, code, fallback FROM locale WHERE code = 'nl-be'
                      UNION ALL
                     SELECT c.level + 1 as level, lo.code, lo.fallback
                       FROM locale lo JOIN c ON c.fallback = lo.code)
SELECT c.level, c.code, dic.key, dic.value
  FROM c LEFT OUTER JOIN dictionary dic ON c.code = dic.locale_code WHERE dic.key = 'yes_button'
 ORDER BY c.level;

The target language you seek is in WHERE code = 'nl-be', and the dictionary key is within the OUTER JOIN with WHERE dic.key = 'yes_button'.
If you run this, you will receive the following results:
level | code  | key        | value
----- | ----- | ---------- | -----
2     | nl-nl | yes_button | Ja
3     | en-us | yes_button | Yes

Because there is no value for nl-be, the next value is returned. If there is no value for that one, en-us will be returned.
You can see this in action with Spanish as well. Change the language to es-sv and results will come back for three options.
level | code  | key        | value
----- | ----- | ---------- | -----
1     | es-sv | yes_button | Por favor
3     | es-bo | yes_button | Absolutamente
4     | es-es | yes_button | Sí

Note that there is no en-us in the output, nor is there any record for "level" 2. This is because es-es does not have a fallback, and there is no matching dictionary entry for es-cl, so it is excluded.
If this works for you, remember to set LIMIT 1 at the end of the SQL query so that you are given just the most relevant value.

Big hat-tip to Laurenz Albe for his many, many examples of how to write RECURSIVE queries across Stack Exchange. I learned something new and (hopefully) solved a problem today.


Append timestamp to powershell I am trying to capture some data on CPU activity on a SQL instance.

Get-Process | Sort CPU -descending | Select -first 10 -Property ID,ProcessName,CPU

The PowerShell above give me the following output :

How do i modify the powershell to include a timestamp for each row. And output should look something like this.

You can add a calculated property to you output like this

Get-Process | Sort CPU -descending | Select -first 10 @{l="Time";e={get-date}},ID,ProcessName,CPU

The syntax for adding a calculated property is

 @{                  - start a script block
   l = 'label';      - choose a label for the property (not mandatory)
   e = {expression}  - formulate the expression you like evaluated
 }                   - close the script block


How can I use 'system pwd' command with MySql CONCAT() function? How can I use 'system pwd' command with MySql CONCAT() function? I tried

select concat('test', system pwd);

gives me

ERROR 1583 (42000): Incorrect parameters in the call to native function 'concat'

Is there any way? My database version 5.1

system is not a recognized MySQL server command. This will only work on the MySQL command line interface "mysql". With a command like this:

mysql> system pwd
/tmp

you will get the client's current directory, nothing to do with the server. You cannot use that information on server side, for executing queries. If you want to concat the pwd with something else, use a script at client side (bash, php). For example:

$ echo $(pwd)test
/tmptest
$ mysql  -e "SELECT concat('$(pwd)', 'test')" # there is not reason to do this at all
+------------------------+
| concat('/tmp', 'test') |
+------------------------+
| /tmptest               |
+------------------------+

While there is a custom non-standard UDF library to make MySQL server interact with the operating system, I do not see a valid use in your case.

If you want to know the directory where MySQL is installed, where the datafiles are, etc., you can execute:

mysql> SHOW GLOBAL VARIABLES like '%dir';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| basedir                   | /usr/local/mysql         |
| datadir                   | /usr/local/mysql/data/   |
...


Database with many publication, How to make snapshots for publications run faster I have a database that has about 200 filtered publications. When we run snapshots it drags down the merge processes and it takes a long time to recover because of the locks.

Is there a way to create a snapshot on a busy database without slowing down the running subscriptions.
what would be the impact of snapshot isolation. ( I realize its not talking about the actual snapshot)

Here is one thing you can do to keep your database from getting pegged while a snapshot is in place:

Set up master -> slave replication.
Use the slave replica for snapshots.

If you were to provide the flavor of SQL you are using as well as what mechanism you are using to create snapshots (i.e Xen, AWS, etc.), I might be able to provide a bit more context.


MDX Intersection - how to? A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables:

Order (Id, Date);
Product (Id, Name);
OrderRefProduct (Id, OrderId, ProductId);

OrderRefProduct is a many-to-many table since one product can be in different orders and different products can be in one order.

All this is sticked into an OLAP Cube with Orders and Products as Dimensions and OrderRefProduct as Facts. We need to get results like the output of SQL query would provide:

SELECT ProductId, COUNT(*) as [Count]
FROM OrderRefProduct
GROUP BY ProductId

the correspondind MDX query is fairly simple, given the Cube is built correctly:

SELECT
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS,
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS
FROM [Sales]

And the result for both is:

ProductId   Count
1   7
2   7
3   5
4   6

Now here's the problem. I want to select count of orders that have BOTH products 3 AND 4. SQL:

select ProductId, Count(*) as [Count]
from OrderRefProduct
where [OrderId] in
(SELECT S.OrderID FROM
(select * from OrderRefProduct where ProductID=3) as S
INNER JOIN (select * from OrderRefProduct where ProductID=4) as T on T.OrderId = S.OrderID)
Group by ProductId

Result:

ProductId   Count
1   1
2   1
3   3
4   3

Can't reproduce this in MDX query. What I could achieve so far is FULL JOIN, while I need an INNER one. Any help would be appreciated. Test solution can be found here.

You just need to add an MDX where clause. Look at the Basic MDX syntax.

In that where you need to specify the SET to slice on. Have a look at Using Sets in MDX Queries

SELECT
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS,
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS
FROM [Sales]
WHERE {[Product].[Product ID].[3],[Product].[Product ID].[4]}

In my personal opinion it's no use trying to relate MDX queries to SQL queries. Both are an entirely different beast.

You need to stop thinking in tables and joins, but in tuples and sets and members and measures.

One of the best resources in my opinion to get started is the Stairway to MDX series.


I created a sequence but I can't alter the increment number after that Create sequence

CREATE SEQUENCE NEXT
INCREMENT BY 2
START WITH 1000
MINVALUE 10;

Alter sequence

alter SEQUENCE NEXT
INCREMENT BY 3
START WITH 1000
MINVALUE 10;

The starting value of a sequence can not be altered. Use:

ALTER SEQUENCE NEXT INCREMENT BY 3;


Aggregate SQL Rows into single column I would like to aggregate results of a column into a single row using MS SQL.

I have create a sql fiddle which showcases the issue:

http://sqlfiddle.com/#!3/384d5/2

Basically I would like all 'Tag' results on the same line, grouped by the different Tags

So in the example results above, I would expect the results to be like so:

Id | Tag      | OptionText
1  | IsMobile | Yes
1  | Mission  | I am buying this, I am using this

The closest I have got is by using a STUFF function but I can't seem to get it correctly as it outputs all the OptionText values on each row:

select Id, Tag, STUFF(CAST((
    SELECT [text()] = ', ' + [OptionText]
    FROM testing
    FOR XML PATH(''), TYPE) AS
VARCHAR(max)), 1, 2, '')  OptionText
  from testing
  group by Id, Tag

Here are the results for that query: http://sqlfiddle.com/#!3/384d5/5

You need to restrict your sub-query to look for the specific tags of the outer query.

I have aliased your outer table t1 and inner table t2.

 CREATE TABLE #testing
    (
      Id INT ,
      Tag NVARCHAR(250) ,
      OptionText NVARCHAR(250)
    );

 INSERT INTO #testing
 VALUES ( 1, 'IsMobile', 'Yes' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am using this' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am buying this' );

 SELECT
    Id ,
    Tag ,
    STUFF(CAST(( SELECT
                    [text()] = ', ' + [OptionText]
                 FROM
                    #testing t2
                 WHERE
                    t2.Tag = t1.Tag -- Restrict rows returned in subquery
               FOR
                 XML PATH('') ,
                     TYPE
               ) AS VARCHAR(MAX)), 1, 2, '') OptionText
 FROM
    #testing t1
 GROUP BY
    Id ,
    Tag;