Pivot on datetime with matching multiple column

Quickly:

select school, state, Act, English, History
from (select school, state, subject, timedate
      from data) as pivotdata
         pivot (max(timedate) for subject in (Act, English, History)) as P

Should work


How to get number of matching colums count in the same table as another column?

With window functions, this would be almost trivial to add as a column in a query:

COUNT(*) OVER (PARTITION BY status) AS status_count

Since MySQL hasn't implemented them (you'll have to wait, they may be in the next version), you'll need something more complex now. It can be done with a derived table - to calculate these counts - and then join to the base table:

SELECT
    t.id, t.box_name, t.diIndex, t.diMode, t.diStatus, t.status,
    g.status_count, t.last_updated
FROM
    tableX AS t
  JOIN
    ( SELECT status, COUNT(*) AS status_count
      FROM tableX
      GROUP BY status
    ) AS g
  ON
    t.status = g.status ;


Create TRIGGER to insert after the insertion of specific value

I think you have the trigger syntax wrong. Try something like (I don't fully understand the scenario so you will have to adjust the logic in the trigger):

delimiter @
create trigger resend
after insert on t1
for each row
    IF NEW.status = 'ERROR' THEN
        INSERT INTO t2 (time,sender,receiver,status,aux_id)
        VALUES (NEW.time,NEW.sender,NEW.receiver, NEW.status, NEW.aux_id);
    END IF @

delimiter ;


How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

Since your thumbprint is being returned as a System.String you actually cannot easily convert it to a System.Byte. I have not gotten a full example that you are doing to work completely but will show you how I convert the thumbprint, and it seems to work:

$encode = [System.Text.Encoding]::UTF8
$r1 = $encode.GetBytes($thumbprint)

$sha = New-Object System.Security.Cryptography.SHA256CryptoServiceProvider
$r2 = $sha.ComputeHash($r1)
[System.Convert]::ToBase64String($r2)

$certPath = 'CurrentUser/My/$($cert.Thumbprint)"
$certProvider = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
$certProvider.EncryptColumnEncryptionKey($certPath,'RSA_OAEP',$r2)

The only thing I am getting right now to figure out is an error on Invalid provider type specified. Which I do have the March Preview of SSMS 2016 on my laptop, not sure if that has something to do with this or not.


Ubuntu/Debian packages for Postgres have their own layer on top of initdb and pg_ctl to control multiple instances and the integration with systemd.
The command that may be used to create an instance with specific options in Debian/Ubuntu is:
pg_createcluster [options] version name [-- initdb options]

use pg_lsclusters to see the list of already existing clusters. Possibly you want to drop the existing default cluster named main using the default segment size that you don't want, in order to have one single Postgres instance with the desired wal segment size.


Using Row_Number to find consecutive row count

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1


How can I efficiently represent inheritance down a tree?

I'd look at your indexed view the other way around:

Have table O for your objects, storing A, B and C, and a level field. Then create indexed views for A, B and C based on queries off O. Perhaps use a hierarchyid field to know the full tree for each record.

There's plenty more you could do, but this could be a useful start.


Editing built-in templates in Management Studio

What I did was create a new folder and then save the changes to that folder. You have to close out of SSMS and reopen to see it; you can use the same name for the template in a different folder.

You could also save as inthe original folder and add the company name or something else (like A_) to put them together at the top of the list.


MYSQL database how to check if database is active and on which port (Linux)?

It seems that your mysql connection is only listening by unix sockets and now by the actual TCP.

To solve that, go to your configuration file, usually on /etc/mysql/ folder with the name my.cnf on Ubuntu(if you are using some other system should be similar).

In the configuration file you should have a section called [client].

make your server to listen on 127.0.0.1:3306 by:

[client]
port        = 3306
#socket     = /var/run/mysqld/mysqld.sock
host = 127.0.0.1

And than restart the server.

Now you should have access to the mysql shell by doing on your command line.

mysql -U TEST -h 127.0.0.1 -p db_name

Or you can use any other client you want.


Can I have Oracle primary 11.2.0.1 and standby 11.2.0.3?

The physical standby database must be physically the same as the primary database and this means the versions must be identical.

A logical standby database can be any version, depending on how it is implemented. With 3rd party tools you can go from v10 to v12 and back. The restrictions here are on the data types that are to be replicated.

If this is for an upgrade, you start with a physical standby database, convert that to a logical standby for the upgrade.


Best approach to run a simple query against all users

select * from dba_users

You need the DBA role or similar access to select from this table.
In Oracle 12 the columns available are

USERNAME    VARCHAR2
USER_ID NUMBER
PASSWORD    VARCHAR2
ACCOUNT_STATUS  VARCHAR2
LOCK_DATE   DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE  VARCHAR2
TEMPORARY_TABLESPACE    VARCHAR2
CREATED DATE
PROFILE VARCHAR2
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2
EXTERNAL_NAME   VARCHAR2
PASSWORD_VERSIONS   VARCHAR2
EDITIONS_ENABLED    VARCHAR2
AUTHENTICATION_TYPE VARCHAR2
PROXY_ONLY_CONNECT  VARCHAR2
COMMON  VARCHAR2
LAST_LOGIN  TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED   VARCHAR2


Seems like a perfect use case for Login Auditing. This will require a restart of the SQL Server.

Then, I would use xp_readerrorlog to view the results. Something like:

xp_readerrorlog, @p1 = 1, @p3 = 'Login'


Will SQL Server check FK restrictions between row deletes in a single DELETE statement?

SQL Server only seems to be checking the FK constraint after all rows to be deleted in a single DELETE statement have been deleted, rather than in between each row deletion. Can I rely on this behaviour?

Yes.  Constraints are checked for the statement, not for each row, and not at the end of a transaction.
Here's the query plan for a DELETE from that table.

The delete occurs on the top branch, but the IDs of the deleted rows are spooled, and after the clustered index delete step, the spooled IDs are used to look up any rows that reference the deleted rows.  If any are found the DELETE will be rolled back (DML statements always run in a nested transaction internally), and a FK error raised.


How can I share values in maintenance plan tasks?

For global parameters we would use package configuration files, perhaps they would work for you as well: Package Configurations


Is this data duplication when two exact pieces of data have different lifespans?

Normalization rules are a useful guideline, not a strict law.

Even if you don't anticipate the currency field in the payments table to ever be different than the currency field for the related bank_account, I can think of any number of reasons to keep both:

What if the account record is ever deleted? (Accidentally or deliberately)
What if the account record changes currency type?
The field could be used as a point of immediate validation before committing the payment
The field could be used after the fact as an integrity/validation audit
It could improve performance for certain queries instead of requiring a join to the bank_accounts table.


Because the database has to run additional recursive queries the first time it encounters a query, to parse it. It has to check if the given SQL is valid, it has to check the optimizer statistics, etc.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  3ghpkw4yp4dzm, child number 0
-------------------------------------
select count(*) from hr.employees

Plan hash value: 3580537945

-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |              |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |      1 |    107 |    107 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------

14 rows selected.

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        220          1

We can clearly see from the plan statistics that it required a single buffer get, yet V$SQL shows 220. But now the query is parsed, if I run it again, buffer gets are increased only by the amount the query really uses:

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        222          3

If you are interested what additional SQLs the database executes when parsing the query, you can enable SQL tracing and view the trace file:

SQL> select tracefile from v$process where addr =
     (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
-------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ss18/SS18/trace/SS18_ora_14811.trc

SQL> alter system flush shared_pool;

System altered.

SQL> exec dbms_session.session_trace_enable(true, true);

PL/SQL procedure successfully completed.

SQL> -- run your query here, then view the trace file


Effect on execution plans when a table variable has a primary key

...is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

Yes it might do. Estimating one row (with the understanding that estimates can be incorrect) is different from knowing that the table contains only unique values. Certain plan space explorations require a key, for example.

A good general rule of thumb is to provide as much information about the data and query task as you can to the optimizer. If there is a key, say so explicitly. It's not as if declaring the key will add much cost (beyond a little keyboard work) in most cases.

I personally rarely use table variables. The lack of statistics (including distribution and density) and cardinality information (all separate considerations) provides less information to the optimizer than an equivalent temporary table. My experience has very much been that table variable plans do not adapt as well to changing circumstances over time.

I only use a table variable when there are special reasons to be sure that it will always be adequate from a query optimization point of view. Only you have enough information about your databases and queries to say whether that is true in your case or not.

The question is rather broad (without a specific example), and so is this answer.


Selecting the highest value in a column?

Beyond ypercube's answer, I would solve this with either of the following queries. I like the first because it's easier for my poor head to comprehend.

The second usually provides better performance but it always takes me a bit to "get it."

-- this approach uses row_number to provide an ordered window based
-- resetting whenever the group id changes
SELECT
*
FROM
(
    SELECT
        P.*
    ,   ROW_NUMBER() OVER (PARTITION BY P.PrntP_PstnGrpID ORDER BY P.ChldLevel DESC) AS rn
    FROM
        dbo.P_PositionGroupFlat P
) D
WHERE D.rn = 1
ORDER BY 1;

-- this approach is my new favorite
-- has shown better performance than the above approach
SELECT
P.*
FROM
    dbo.P_PositionGroupFlat P
WHERE
    P.ChldLevel =
    (
        SELECT
            MAX(PI.ChldLevel)
        FROM
            dbo.P_PositionGroupFlat PI
        WHERE
            PI.PrntP_PstnGrpID = P.PrntP_PstnGrpID
    )
ORDER BY 1;

http://sqlfiddle.com/#!3/3a958/4


How to move a PostgresSQL/ PostGIS to another drive

There are two things:

the software directory containing the binaries like postgres and related files
the data directory that you can find with the SQL statement

SHOW data_directory;

On Debian Linux the configuration files are in a separate location, but since you are speaking about "drive letters", you are probably using Windows, where the configuration files are usually in the data directory.

You can simply copy the software directory and the data directory to your external disk after you have stopped PostgreSQL.

You can start the server with

<software directory>\bin\pg_ctl start -D <data directory>

If you want to start PostgreSQL through a Windows service, you may want to delete and re-create the service:

Delete the existing service:

<software directory>\bin\pg_ctl.exe unregister -N postgresql-11

The exact name to use for this, depends on your installation. You need to look it up in the "Services" management console (right click on the service, then choose "Properties" - make sure you close that dialog before proceeding). Once the old service is removed, create a new one  pointing to the new data directory:

<software directory>\bin\pg_ctl.exe register -N postgresql-11 -D <data directory> -S auto

Make sure you enclose the data directory in double quotes if it contains spaces.


There is no hard limit but you will limited by hardware considering the agents will consume cpu, memory, and i/o. There is also a desktop heap issue that some have run into when running a large number of push subscriptions, ymmv.

I personally have 1 topology I administer which currently has approximately 3,000 pull subscribers.


Create a stored-procedure to insert multiple rows on a junction table and other tables

So what you want is an insert, an update and a delete procedure for each table.  Then you want a "wrapper" procedure that tracks the inserts/updates/deletes and responds accordingly to errors.

A "transaction" would be the batch and "rollbacks" would be explicit.  I put these in quotes because you don't want to encompass all of the transactions into a single transaction.  You can't nest user transactions and a large single transaction would cause some serious blocking.

Pseudo Code for inserts...

Exec usp_NewInvoice
      @purchaseDt=@purchaseDt
    , @fruitID   =@fruitID
    , @driverID  =@driverID
    , @qty       =@qty
    , @price     =@price

--parameters are passed with the variable values created by the app

usp_NewInvoice
      @purchaseDt datetime2
    , @fruitID    int
    , @driverID   int
    , @qty        int
    , @price      money

    TRY
    BEGIN
        @ReturnValue= exec usp_InsertInvoice
                          @purchaseDt=@purchaseDt
                        , @invoiceID=@invoiceID OUTPUT

        if @ReturnValue<>0
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

    BEGIN
        @ReturnValue= exec usp_InsertInvoiceDetail
                          @invoiceID =@invoiceID
                        , @fruitID   =@fruitID
                        , @driverID  =@driverID
                        , @qty       =@qty
                        , @price     =@price

        if @ReturnValue<>0
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

ERROR:
IF @ReturnValue <>0
BEGIN
    --RETURN ERROR
END

For each operation you are making sure if succeeded.  If it doesn't you will need to roll back all previous transactions with other procedures (updates and or deletes).  You'll want to do something similar for the updates and deletes.


I find pl/pgsql tedious for general programming, and slow to program in and slow to execute.  And missing a lot of functionality--some of which is missing because it has to be in order to be a trusted language (no IPC or network), and others just because it hasn't been built and it lacks a mechanism for libraries/modules/packages.
I've used plpython3u get access to chemical intelligence libraries from inside the database.  re-implementing the functionality of those libraries from scratch would be theoretically doable, but utterly impractical.  Doing it in pl/pgsql would be utterly impractical squared.
I've done the same thing with plperlu and plperl, to get access to a variety of CPAN Perl modules.  If those modules happened to have been in python rather than Perl, I would have used plpythonu rather than plperl(u).
I've also used plperl (not plpythonu but just because I prefer Perl over python) to implement functions which I wanted to be available both inside the database through SQL, and outside in our procedural code.  I could have implemented them in pl/pgsql, but that would be annoying to do, and also means they would need two implementations in two languages.  Alternatively I could have just implemented them in the database, and connect to the database from standalone Perl when I want to call the functions from there. But that would sometimes mean establishing a database connection that would otherwise be unneeded in that program, and would be an abuse of one of our more limited resources, as databases are much harder to scale than standalone Perl scripts.  It would also introduce latency.


Some things I'd check:

When you connect from SQL Server Management Studio 2008 on the box with Express, are you using the right server name (and possibly port number)?
Have you got Windows Firewall enabled on any of the machines?
Can you ping the SQL Server?  Do you get the same IP address for it that you get on the other machines?
Do you have any aliases set up in SQL Server Configuration Manager?
Have you disabled any client protocols in SSCM?


Those refer to different logs.

The binlog (log_bin) is for replication and other purposes for capturing writes outside of the server.

The other log controls how securely a "pull-the-plug" crash can be recovered from.  =1 performs an extra write to make crashes 100% recoverable.  =0, or slightly better =2 have less overhead but are "good enough" for many applications.


How to set a search_path default on a psql cmd execution?

I finally found it, its not a psql option, but it fits a oneliner:
PGOPTIONS=--search_path=myschema psql -h myHost -U myUser -dmyDb -p myPort.

If anybody improves it with a working version call for both unix and windows ill approve


Can I use SELECT statement for values in a list partition? Or do I need to explicitly state them?

When in doubt, read the fine manual, which says about partition bounds:

  and partition_bound_spec is:

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

Nowhere does it say "subselect"; only literal values and special keywords that substitute literal values are allowed.


Query for lowest price for product in same row

Of course your data is denormalized, otherwise it would be a simple MIN(price) :-)

If you got luck your DBMS supports a LEAST function:

LEAST(price_one,price_two,price_three,price_four,price_five),

A huge CASE is supported in every DBMS:

CASE
  WHEN price_one <= price_two
   AND price_one <= price_three
   AND price_one <= price_four
   AND price_one <= price_five
  THEN price_one

  WHEN price_two <= price_three
   AND price_two <= price_four
   AND price_two <= price_five
  THEN price_two

  WHEN price_three <= price_four
   AND price_three <= price_five
  THEN price_three

  WHEN price_four <= price_five
  THEN price_four

  ELSE price_five
END

Hopefully there are no NULLs...


Is it possible to JOIN to a Primary database from a Replication instance?

You should use linked server and use four part naming linkedservername.dbname.schema.object

Also refer my answer on efficient way of pulling data when using linked server


Amazon Redshift "cache lookup failed for aggregate" error when using median aggregate

Amazon recently added support for the 'median' window function. If you forget that it's only a window function and try to use it like an aggregate, you'll get this error:

dev=# select median(5);
ERROR:  cache lookup failed for aggregate 2762

The solution is to rephrase the query to use it as a window function:

dev=# select median(5) over ();
 median
--------
   5

(1 row)


I'm not sure I would use the mechanism that you're using to purge old data, but since that's what you want to do, there is a way to work around this.

What you are seeing appears to be an undocumented incompatible change in behavior between MySQL 5.5 and MySQL 5.6 -- although, arguably, the real problem is that the statement should not be valid in MySQL 5.5 either when the table is not partitioned.

I confirmed the behavior you are seeing with MySQL 5.5.30 (no error) and 5.6.10 (error).

  I'm trying to create partitions by procedure

Do you mean in a Stored Procedure?

If that's the case, then you could use a CONTINUE HANDLER specifically written to allow your stored procedure to ignore that one specific error code.

The following works in both MySQL 5.5 and 5.6.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_continue_handler` $$
CREATE PROCEDURE `test`.`test_continue_handler` ()
BEGIN

  -- you may have some code that executes before the ALTER TABLE, here

  BEGIN
    DECLARE CONTINUE HANDLER FOR 1505 BEGIN END;
    ALTER TABLE t1 REMOVE PARTITIONING;
  END;

  SELECT 'that worked okay' AS message; -- remove this, your usual code after the ALTER TABLE goes here

END $$

DELIMITER ;

mysql> call test_continue_handler;
+------------------+
| message          |
+------------------+
| that worked okay |
+------------------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.07 sec)

The BEGIN END; after 1505 is telling the server "when this error occurs, run the code that appears in this empty block instead of throwing an error."  There's no code between BEGIN and END but MySQL doesn't care and this results in suppressing error 1505.

The BEGIN and END; before "DECLARE..." and after "ALTER..." limit the scope of that CONTINUE HANDLER to only the single ALTER TABLE so that any other statements throwing that same error code later in your stored procedure (or in any procedure called by your procedure) will not be ignored.  Note this does not mean statements after the ALTER TABLE but before the next END will not be executed if an error occurs -- they will.  It only means that after the END;, the CONTINUE HANDLER will be out of scope and will not trap subsequent errors.


If you're already comfortable with Powershell, check out dbatools.io, which I think provides many of the functionalities you're looking for.

Some time ago, I wrote a hack that synchronizes logins using linked servers, which you may find helpful.

Regardless of what tool you choose, I would create an Agent job that runs on all nodes and checks if it's running on a primary or secondary. If it's on the secondary, it can pull the latest version from the primary.


Should I use the Infobright database or is there a better aggregation solution?

Almost always (in my experience), the following is the solution to performance with large, log-like, data and semi ad hoc queries.

Characteristics of the data and the application:

continually arriving data
no updates to 'old' data
optionally purging old data (if so use PARTITION BY RANGE(TO_DAYS()))
queries tend to have a date range in the WHERE clause

Solution:  Build and maintain "summary tables".

pick a timeframe (usually day or hour)
after midnight (or top of hour), summarize yesterday's data from the raw (Fact) table into the Summary table.
PK of the Summary table usually includes some dimension(s) and the rounded-down date/hour.
rest of fields include aggregates like COUNT(), SUM(), etc, but not AVG().
AVG is calculated as SUM(sum_foo)/SUM(ct)
"reports" hit a summary table, not the fact table.
1-10 summary tables usually suffice for a given application
Querying the summary table usually gives you 10x performance improvement; I have seen 1000x in rare cases.

I say "semi ad hoc" because you will find out that users don't really query on everything.  Even if they do, you can have summary table(s) that at least help.

I mentioned the straight-forward way of completely summarizing "yesterday".  There are many other flavors.  One is "as needed", with the aid of INSERT ... ON DUPLICATE KEY UPDATE.


Error calling stored procedure from trigger in Oracle Database 11g

Your trigger doesn't need the CALL keyword.

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Generally, you should never use CALL in a PL/SQL block-- just execute the procedure.  I assume that CALL is some ancient leftover syntactic remnant from some ancient version of Oracle.  It allows you to replace the entire PL/SQL block that is the trigger body with a single CALL statement, i.e.

create or replace trigger TRYTABLE_BEF_UPD_ROW
  before update or insert on TryTable
  for each row
  call TryJavaHelperRun(:new.arg1, :new.arg2)
/

Personally, I've never come across a case where there was any benefit to ditching the "normal" PL/SQL syntax in favor of using CALL in a trigger.

This assumes that you can call the TryJavaHelperRun procedure successfully in general.


Backing up SQL Server 2008 database in different recovery model

For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

Your problem is an inadequate maintenance and backup/recovery plan. You are not taking log backups with enough frequency, this is why your log grows through the roof. Start taking log backups to allow truncation and then shrink the log. You'll have a better recovery plan and a smaller database log file.

As for the question: no, there is no way to change the restored database file size(s). The restored database will always have the exact same layout as the original backed up database. Recovery mode has nothing to do with this, your restore would need 60GB on any recovery model. You're only mixing the recovery model in the discussion because you shrink the log by switching to SIMPLE. Read the linked article for more details on this aspect.


switching from normal list partitioning to reference partitioning in oracle 11g

A much faster, way cooler, but also much more complex way would be to use partition exchange.

I would use it only if

there is enough data (say > 1 million rows, or partitions > 1 GB)
you have a test database/schema with identical structure and sufficient amounts of data, which can be easily recreated (in my experience, the scripts usually work only correct after the second try)
you have the time to develope and test the migration script

Given a mockup with minimal tables, data and columns:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, t VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

INSERT INTO parent(id,par,c)
SELECT object_id, mod(object_id,10) as par, object_name FROM all_objects;

INSERT INTO child(id,par,t)
SELECT object_id, mod(object_id,10) as par, object_type FROM all_objects;

First, you create holding tables to park the data:

CREATE TABLE temp_parent_p1 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_parent_p2 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_child_p1  AS SELECT * FROM child  WHERE 1=0;
CREATE TABLE temp_child_p2  AS SELECT * FROM child  WHERE 1=0;

Next, you'll swap out the data, children first, then parents:

ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;
DROP  TABLE child;
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;
DROP  TABLE parent;

Then you'll recreate the new table structure:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

Now the parked partitions need to be indexed. The columns and constraints need to have exactly the same structure as the new tables (but don't worry about the names). Then you can swap the data back in:

ALTER TABLE temp_parent_p1 ADD PRIMARY KEY (id);
ALTER TABLE temp_parent_p2 ADD PRIMARY KEY (id);
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;

After the parent is done, the children can be swapped back in:

ALTER TABLE temp_child_p1 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE temp_child_p2 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;

A little bit of clean up afterwards:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'parent');
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'child');
END;
/
DROP TABLE temp_parent_p1;
DROP TABLE temp_parent_p2;
DROP TABLE temp_child_p1;
DROP TABLE temp_child_p2;


ORA-01562: failed to extend rollback segment number 11

This isn't a coding issue - the rollback segment simply isn't big enough. Speak to your DBA about getting it extended.

Another issue could be other long running jobs hogging the available rollback space. Either way, the DBA should be your first port of call.


Reconcile users with logins using 'sp_change_users_login'

When you run sp_change_users_login, it automatically renames users as explained in Books Online:

  The name of the user will be automatically renamed to the login name
  if the following conditions are true. The user is a Windows user. The
  name is a Windows name (contains a backslash). No new name was
  specified. The current name differs from the login name.

To keep life easy, I'd keep the same usernames on both servers and then you should be fine with sp_change_users_login.


Is this the correct way of moving MySQL data directory, (on Centos)?

Ideally you would keep that folder, and instead of changing it you'd either mount another folder or partition on it prior to starting mysql, or create a symlink elsewhere (there are answers for doing this here and here).

You won't need to rebuild anything for moving your files, but it is a good idea to always make a backup before attempting something like this that may damage your data if anything goes wrong.


Changing the Parametization option in SQL Server 2012 from Simple to Forced

It frees the plan cache for the database that you change the option for, and nothing else.

On my server, I run these queries:

USE StackOverflow;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 2;

USE StackOverflow2010;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 1;

They I use sp_BlitzCache to look at my plan cache:

After that, change one database to use Forced Parameterization:

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED

Give sp_BlitzCache another run:

Only the plan from StackOverflow2010 remains.

Full disclosure: I contribute a lot of mostly working code to the open source sp_BlitzCache stored procedure.


AlwaysON Availability Groups - IP address & subnet change of secondary replica node

How would one approach this?

With a whole bunch of pre-move work :)

  My initial thoughts are: /snipped for brevity

You have the right ideas, let me add a few more.

Pre-Work

The cluster is now Multisubnet, we'll need to add an IP for the cluster name in the second subnet. This will be added to the cluster name resource as an 'OR' dependency as you've stated above. This can be added at any time (I'd add it pre-move). The cluster CAP will now have 2 IP addresses, one for each subnet.
Firewall rules, etc.
The listener (as you've already stated) will become multi-subnet. Another IP address will need to be added in the 'OR' dependency.
Double check the setting of the client access point (CAP [aka VNN, aka network name]) for: HostRecordTTL and RegisterAllProvidersIP to make sure they are setup the way you want them. Note that you may want to use multiple listeners to facilitate clients that use older connection libraries not supporting the new keywords.
Decide if you want to remove the replica from the AG (if it's going to take a while to physically move the server) or to just pause the AG. If the AG is removed, the databases will go back into a "restoring..." state and you'll be able to catch them up with restores at a later point after the server arrives and is setup.
Quorum: There isn't much to say here, it's a 2 node cluster with 1 node getting airline miles. Included because we'll hit this later and it's a common question.

Post-Work

The server should (with a new IP and proper firewall rules) be able to contact the cluster and join.
Run cluster validation wizard. Save this report as the initial move report. We may need to look at it later. This will also verify most cluster related items and runs very quickly.
Verify network latency. Set CrossSubnetDelay and CrossSubnetThreshold appropriately for the latency and health of the connection. No changes may be needed but it's good to double and triple check.
Restore transaction logs/diffs/etc.
Add the replica back into the AG
Revisit Quorum. Even though there are just two nodes in the cluster, we'll want to double check we're not using a disk witness. Depending on the version of Windows we should/could use either a fileshare witness or Azure witness.

There may be additional items specific to your environment but that should be the gist of it.  You've pretty much hit the nail on the head in your original question/post, this just adds in a little filler :)


Catch deadlock events in a background job?

Deadlock events are captured by the system_health Extended Events trace so you don't need to run Profiler interactively or create a SQL Trace to capture the information.
Below is an example query to retrieve the deadlock info from the file target:
--Get xml_deadlock_report events from system_health session file target
WITH
      --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
      all_trace_files AS (
        SELECT path + '\system_health*.xel' AS FileNamePattern
        FROM sys.dm_os_server_diagnostics_log_configurations
        )
      --get xml_deadlock_report events from all system_health trace files
    , deadlock_reports AS (
        SELECT CAST(event_data AS xml) AS deadlock_report_xml
        FROM all_trace_files
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
        WHERE trace_records.object_name like 'xml_deadlock_report'
    )
SELECT TOP 10
      deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
    , deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;


Assuming that you mean that the table is partitioned on CATEGORY and subpartitioned on SUBCATEGORY, the query that includes the CATEGORY predicate will be much more efficient.  Since Oracle cannot know that subcategory A only exists where the category is A, it would generally not be able to eliminate any partitions.  Instead, it would have to search the subpartition of each partition where a SUBCATEGORY of A would be stored in order to fulfill the query (assuming that there is no global index that could be used).  If you include both predicates, Oracle should be able to eliminate all but the partition where CATEGORY A values are stored and then search only the subpartition where SUBCATEGORY A values are stored.


SQL Login keeps losing access to databases

Short answer:

The SIDs for the ‘MyReportAcct’ SQL login are different on each server instance.

Long answer:

The master database houses the security database for each instance of SQL Server (think Active Directory for a database server).  In this scenario we have two different master databases, since it is a mirrored pair.  Each time you create a SQL Login, there is a different SID created along with the account, this is no different than how Active Directory has always worked.

When a mirrored database fails over, the SID in the database matches the SID stored in the master database of the principal server (SERVER1), not the mirrored server (SERVER2).  This natural, and default process of failover causes the SQL Login (MyReportAcct) to become an orphaned account on the principal server (SERVER1).  This orphanhood process happens EVERY time there is a failover.  The start of authority for SQL Logins are the instance that they were created (SERVER1).  The failover to the mirrored server (SERVER2) is now a different start of authority, so the SIDs will be different, hence why the reports fail.

Solution:

Create the SQL Login ‘MyReportAcct’ with the same SID every time.
This process requires using TSQL code in the query analyzer by
someone who is a sysadmin.
Create an Active Directory service account named ‘uic\MyReportAcct’.
This way, the SID is managed by AD and we never have to worry about
the aforementioned issue again.


Retrieve a count from a one-to-many relationship even if there are no rows at the "many"-end of the relationship

Try

select
  C.id
, count( CL.company_id ) likes_count
from companies C
  left join company_likes CL on C.id = CL.company_id
group by C.id
order by C.id ;

See dbfiddle (Postgres 9.6).

-- test tables and data
create table companies ( id int4 primary key ) ;

insert into companies ( id ) values (1),(2),(3),(4);

select * from companies ;

id
1
2
3
4

create table company_likes (
  company_id int4 references companies( id )
);

insert into company_likes ( company_id ) values (1),(4),(4),(4),(4) ;

select * from company_likes ;

company_id
1
4
4
4
4

Query

select
  C.id
, count( CL.company_id ) likes_count
from companies C
  left join company_likes CL on C.id = CL.company_id
group by C.id
order by C.id ;

id  likes_count
1   1
2   0
3   0
4   4


The number of records that is the table shows in .dbf file is not match with the real number of records

Use a hexidecimal editor and open a .dbf file in it. This tool is located at tools\hexedit\hexedit.app.

The header bytes that contain the record count are located in columns 4, 5, 6, and 7 of address 00000000 (topmost row).
Go to the address and then change the data in this column to double zero.
Next, open this file in VFP as a table and note that the record count is zero.

I have found this thread for you https://getsatisfaction.com/impathic/topics/corrupt-dbf-files
where a similar issue is discussed.


Can I limit an index to only contain the newest relevant row?

This is a classic case for partitioning.

When you insert a new record, have a trigger delete the old record from the user_login_session_latest sub-table and insert it into the user_login_session_history sub-table. Have both of them inherit from an empty top-level user_login_session table so you can still query the two transparently. You will need an additional trigger-maintained column like is_latest to partition on, since you can't do constraint exclusion on a condition that refers to other rows.

A partial index will work, but not as well as you might hope; it's hard to write a partial index based on the concept of "latest" (you need an app to set a flag), and it might not boost things as much as you expect. The partial index points at a page, but if that page contains 10 old records and one new record you might not gain that much; you'd need to regularly CLUSTER the table to see much benefit. Partitioning will be a much better option.

Alternately, you can maintain a materialized view of the "latest" users in another table using triggers or application logic. I'd generally prefer to use partitioning in preference to a materialized view for this.

Remember to tune autovacuum to vacuum your tables quite frequently if there's lots of updating/deleting going on.


MySQL allows records longer than varchar(N)

Use CHAR_LENGTH - you presumably have some > 1 byte characters and LENGTH returns bytes.

You can read more about the string functions in the manual:

MySQL 5.5 Reference Manual / Functions and Operators / String Functions


Your syntax for the CASE expression is correct (it's an expression, not a statement!) You can simplify it slightly by removing the second WHEN:

CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END

and the query:

SELECT Ename, Hiredate,
       CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END AS MAXDATE
FROM EMP
  LEFT JOIN (SELECT max(Hiredate) AS MAXDATE
             FROM EMP) m
  ON EMP.HIREDATE = m.MAXDATE ;

And you don't really need the self-join. You could have the same result with a window aggregate function:

SELECT Ename, Hiredate,
       CASE WHEN HIREDATE = max(HIREDATE) OVER ()
           THEN '*' ELSE '' END AS MAXDATE
FROM EMP ;


extract values from different stored procedures and create one report

If the stored procedure only returns one result set, and you know the column names in advance, then you can create a temp table and Insert #TableName Exec ProcedureName. You would then select the value you want into a variable from the temp table, and run the procedure again.

If it only returns one result set but you don't know the column names in advance, you can use this solution (https://stackoverflow.com/a/1228165/401970) to call the procedure within an Openrowset and put the results into a temp table it creates for itself with the returned column names.

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

If the procedure returns multiple result sets then you're not going to be able to do a solution in T-SQL only. You can write a simple PowerShell script to do it, but that's not really going to help if they need to get the result directly from the database.


Is there a system primary key associated with each row?

In practical terms from a code or user perspective, no.

There is one internally (rid) but it is not exposed in any documented system view or function


Confused about behaviour of variable in WHERE statement when value is null

Consider this query:

select count(*)
from my_test_table t
where null is null;

Does it make sense for the optimizer to do a seek there? There's nothing to seek against. Trying to force it with a hint throws an error:

select count(*)
from my_test_table t with (forceseek)
where null is null;

  Msg 8622, Level 16, State 1, Line 15

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

For the query that you have right now you may want an index seek depending on the parameter value. However, an index seek is not valid for all possible parameter values so your cached plan cannot contain a seek. In addition, when the query optimizer builds the plan it does not check the value of the local variable first.

You indicated that the query is complex so likely the best solution will be to upgrade to at least SQL Server 2008 R2 SP2 so you can take advantage of the Parameter Embedding Optimization (PEO). That combined with an OPTION (RECOMPILE) hint allows the query optimizer to sniff the value of the local variable before executing the plan. The query plan cannot be reused by another query.

Let's see it in action. The actual query plan for the following query shows an index seek:

declare @productId int = 320;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

The actual query plan for the following shows a scan:

declare @productId int = NULL;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

If upgrading isn't an option I suppose you could try this:

select *
from my_test_table t
where productId = @productId
and @productId is not null

UNION ALL

select *
from my_test_table t
where @productId is null;

That can will do a seek or a scan depending on the value of the local variable but could have unintended side effects.

If that also isn't acceptable I think your only option is to use dynamic SQL which was already covered in another answer.


Why are you doing this in PL/SQL in the first place?  The most efficient way to do anything in an Oracle database should be to do it in SQL

 UPDATE item_loc il
 SET il.status             = 'D',
    il.last_update_datetime = get_vdate,
    IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
 WHERE EXISTS( SELECT 1
                 FROM dc_item_loc d
                WHERE d.item = il.item
                  AND d.loc  = il.loc );


PostgreSQL, How to keep only one schema?

The documentation of GRANT says the following:

  CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an
existing object, you must own the object and have this privilege for the containing schema.

So let's simply try this.  I created a user for this purpose, and if you look at it, it has the CREATEDB attribute, just for clarifying which CREATE privilege refers to what.

CREATE USER schematest WITH LOGIN CREATEDB PASSWORD 'secure';

REVOKE CREATE ON DATABASE access_test FROM schematest;

GRANT CREATE ON SCHEMA public TO schematest;

These result in the following behaviour:

CREATE SCHEMA other_schema;

ERROR: permission denied for database access_test
SQL state: 42501

So far so good: the user cannot create a new schema.  Let's see if it can create objects in the public schema:

CREATE public.simple_table(id integer);

Query returned successfully with no result in 181 ms.

Hooray!


To begin with: for a simple view like that you don't need a rule (at least not in any current Postgres version).

The reason you get that problem is this:

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, NEW._date);

Now if you don't supply value for mydate when inserting into the view, then NEW._date will be null, and thus you are trying to put an explicit NULL value into that column. You need to take that situation into account:

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, coalesce(NEW._date, current_date));

Unrelated but: you have another problem in your statement: "Mozzart" is a column name, string literals need to be enclosed in single quotes: 'Mozzart'. Postgres should have thrown a column "Mozzart" does not exist error.

For details, please see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Finally: your view definition is wrong:

SELECT (myname, mydate)

selects a single column that is an anonymous structure with two elements. You need to get rid of those useless parentheses, the CREATE VIEW should have thrown an error CREATE VIEW specifies more column names than columns.

You need to use:

CREATE VIEW mytable_view (name, _date)
AS
SELECT myname, mydate --<< NO parentheses!
FROM mytable;


SQL*Developer is a decent tool for this, but can still take some time.

If you're looking at SQL Plus, I'd recommend that you use spool, and make liberal use of SQL Plus's set command to format your output into flat files. For delimiting the flat files, you can make use of the concatenate feature to add in column headers. Here's an example:

select 'col1,col2,col3,col4'
  from dual
union
select col1||','||col2||','||col3||','||col4
  from some_table;

HTH.


Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS

I was using MySQL replication with Mixed mode replication which captures the data with timestamp and timezone.

Later, I changed the MySQL replication to Row based replication which captures the data with time. So what ever the time format it is, it just send the insert/update statements with time, which solved my problem.


How are the RowCounts in SQL Server Profiler calculated?

Generally, you can expect that the row count reported will correspond to the number of rows returned to the caller (and to the (x row(s) affected) message in SQL Server Management Studio in the case of a top-level query).

However, where the statement contains nested calls, such as to a scalar user-defined function, row counts generated in the (repeated) calls to the function's execution plan will be added to the total. This is an unfortunate side-effect of where the counting is done*, and the way non-inline functions are currently implemented. It has been considered not important enough to fix.

In the linked Connect item example (better formatted in the parent blog post by Guy Glanster), the total row count for the query that returns 100 rows is reported as 300, due to the single row affected by each of the two statements in the function, on each invocation.

If the function is reduced to a single statement:

RETURN CAST(@DateTimeValue AS date);

...the count is 200 (100 rows for the outer query, plus 1 row a hundred times for the function).

DBCC UPDATEUSAGE will not affect this. Indexes and fragmentation have no bearing either. It is simply an architectural consequence, and another reason to avoid non-inline functions.

* The details are rather esoteric. Broadly, for a SELECT query, it is the number of rows seen at the root node of the plan. There is a shared memory structure involved, and nothing you can see directly in an execution plan. When a scalar function executes, it does so in a subcontext that happens to share the structure. Perhaps this is by design, it is hard to know for sure without source code access. My feeling (as stated) is that it is an unintended side effect. For DML queries, a particular plan node is assigned the job of maintaining the rows-affected count. This can differ significantly depending on the shape of the plan (for example if an OUTPUT clause is used, or Split-Collapse update processing is employed).


db2rspgn generates TYPE = ese for workgroup server edition, is that normal?

It appears as if ese is used regardless whether it is workgroup or enterprise. At least I can't find any examples where anything but ese is used.

Running:

db2licm -l

after install reveals an un licenced Enterprice Server, but after adding a workgroup license everything looks normal.


designing server side database to store user-specific data

There are 2 scenarios to consider. The first is when you have the user take 1 or more questions without a meaningful context such an exam or formal quiz. In which case you solution works. However, the second scenario is about when the user takes a question as part of a formal session. In this case you have the following rules:

A user takes zero, one or more quizes
A quiz contains 1 or more questions
A user provides an answer for zero,1 or more specific question on a specific quiz

Note:

Table names may be different in the solution than from this diagram

A1 = Standard answer for question 1

UA1 = User provided answer for question 1


Getting difference of a column between first and last row with count number

Another variation. Here the function first_value is used to get both the first and last total_number (different directions):

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , first_value(total_number) over (order by created_at desc) lst
    from tbl
    fetch first 1 rows only
) as t;

An alternative is to specify the window frame with unbounded following and use the function last_value:

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , last_value(total_number) over (order by created_at
                                          rows between current row
                                                and unbounded following
                                         ) lst
    from tbl
    fetch first 1 rows only
) as t;

Perhaps it is worth noting that the default window frame is range between unbounded preceding and current row, so in the last example, we need to override that to truly see the last row. All rows in the sub-select are identical, so we can pick one randomly (used in both examples).

I believe limit 1 now is part of the standard, but I'm used to fetch first ... so I'll stick to that

In this situation, distinct could have been used instead of limit or fetch ...

from (
    select distinct count(1) over () as number_of_rows
         , ...
    from tbl
)


Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet?

By default SQL Server will bind listen to all addresses, so it will be visible from all subnets. You can also explicitly configure SQL Server to listen on only one or the other of the NICs, see CREATE/ALTER ENDPOINT. IT can even listen multiple times on each address (see How to: Configure the Database Engine to Listen on Multiple TCP Ports) usually done for affinitizing clients to hard or soft NUMA (see How to: Map TCP/IP Ports to NUMA Nodes).

Long story short: you don't have to do anything.


RMAN Restore Validate time equals to actual restore time?

RESTORE VALIDATE reads the backups, but it skips writing the files to the disk.
If writing files to the disk during restore is the limiting factor, then it will be faster. Otherwise it will be the same.

Depending on the backup technology in use, this may or may not be a valid method for estimating the time of a real restore. For example, when using tape backups, the placement of backups and the number of currently available drives may vary.


Retrieving data if control file is lost in oracle Database

You may create new controlfiles with a CREATE CONTROLFILE statement including a list of data files and redo logs.

startup nomount

CREATE CONTROLFILE REUSE DATABASE TEST RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
        LOGFILE
          GROUP 1 '/u01/oradata/test/redo01a.rdo', '/u02/oradata/test/redo01b.rdo'  SIZE 500M
        DATAFILE
          '/u01/oradata/system01.dbf',
          '/u01/oradata/undotbs01.dbf'
        CHARACTER SET WE8ISO8859P1
        ;
recover database;
alter database open;

You need to list your actual database files and come up with sane parameters for your environment, including character set, the size of the redo logs and the name of the database.

If you are unable to RECOVER DATABASE you may instead use ALTER DATABASE OPEN RESETLOGS;

Full documentation for CREATE CONTROLFILE can be found in the  Oracle Database SQL Reference


first what You need, check explain plan of query:

EXPLAIN SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code > 200
      AND created_timestamp >= '2015-11-01 23:00:00'
      AND created_timestamp <= '2015-11-30 23:00:00'
   )
ORDER BY
   created_timestamp
DESC LIMIT 25;

It return You information about which indexes from present MySQL will use when run query

Than You will check cardinality of the indexes

SHOW INDEX FROM ws_call_logs

it give You idea - which index better to use in this case

without information about data, general ideas:
 - index for created_timestamp - good candidate

You can or create index separate for created_timestamp only, or create index for 3 columns:
core_id, hit_status_code, created_timestamp - columns in index must be in same order as used in query

and last SELECT * not give ideas about data size, even You request 25 records, but before server must sort records by DESC


SPID === session_id.

What you are seeing is a Cartesian product since you are joining on a field that is not unique within the data set. You currently have:

FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name

You should probably, at the very least, add the following condition to that JOIN:

          AND sp.spid = dt.session_id

Also, please do not use sysprocesses. It was deprecated when SQL Server 2005 was released, and the sys.sysprocesses "table" you are using is just a compatibility view. The MSDN page for sys.sysprocesses even has a note at the top stating:

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You should instead use the new DMV (Dynamic Management Views):

sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_connections

Please note that a Login might need to be granted the server-level permission of VIEW SERVER STATE in order to see more than their own data for sys.dm_exec_sessions and sys.dm_exec_requests, or see anything at all in sys.dm_exec_connections.

If this permission is needed, then the current database when you run the GRANT statement needs to be master as this is a server-level permission.


How to check if a combination of columns exists in another table in SQL Server?

A join in SQL Server is not automatically implemented as a nested loop. For example, a hash join can be used to implement the NOT IN. This means that the query will not necessarily "end up in a number of tuples over 10^18" even if each of the three tables being joined contains over 10^6 rows.

For example, here is a query plan on a reduced data volume that shows that the largest number of rows at any point in the query plan is proportional to the the size of t1 times the size of t2, but is independent of the size of t3.

Even so, there is no getting around the fact that a query that selects 10 trillion rows (presumably to aggregate them, or to insert them into a columnstore table?) is going to take quite some time to execute. Here's the full script, along with further analysis:

Create sample data

DROP TABLE IF EXISTS #t1, #t2, #t3, #batchMode, #results
GO

CREATE TABLE #t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #t2 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #t3 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #batchMode (dummy INT NOT NULL)
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON #batchMode
GO

DECLARE @numRows INT = 32000
INSERT INTO #t1 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 33) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 57) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

INSERT INTO #t2 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 17) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 74) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

INSERT INTO #t3 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 33) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 17) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO

Execute the query

By executing the query with different row counts, we can see that query performances increases quadratically (N^2) with the row count of t1 and t2, but is not significantly impacted by the row count of t3. We can therefore extrapolate the execution time with the full 16 trillion row cross product to be about 217 hours.

-- Perform your query, using aggregates to avoid writing billions of rows to console
-- 4K rows / table      CPU time = 13906 ms,  elapsed time = 1510 ms.
-- 8K rows / table      CPU time = 54563 ms,  elapsed time = 4548 ms.
-- 16K rows / table     CPU time = 192032 ms,  elapsed time = 13683 ms.
-- 32K rows / table     CPU time = 757688 ms,  elapsed time = 50754 ms.
-- Since the time scales quadratically once we have enough rows to efficiently utilize
-- all threads, we can predict 4MM rows / table with the following query:
--      SELECT POWER(4000000 / 32000, 2) * (50. / 3600)
-- 4MM rows / table     Estimated elapsed time = 217 hours
SELECT COUNT_BIG(*), SUM(1.0 * t1.c1 * t2.c1 * t1.c2 * t2.c2)
FROM #t1 t1
CROSS JOIN #t2 t2
WHERE NOT EXISTS (
    SELECT *
    FROM #t3 t3
    WHERE t3.c1 = t1.c1
        AND t3.c2 = t2.c2
)
    /* Enable batch mode hash join to avoid repartition streams on billions of rows
        This reduces elapsed time from 77 seconds to 50 seconds at 32K rows / table */
    AND NOT EXISTS (SELECT * FROM #batchMode WHERE 0=1)
OPTION (MAXDOP 16)
GO

Replace loop join with batch mode hash join

Because this query is very CPU intensive, it is natural to consider whether the majority of the work — the loop join to build out the cross-product of rows between t1 and t2 — can be replaced with an operator that supports batch mode execution. One way to do this is to re-implement the cross join as an equijoin on a single column where all rows in each table have the same value.

This reduces the projected time by about a factor of 10, to about 20 hours to filter and aggregate the 16 trillion intermediate rows generated by the cross product.

-- Add a column that will always have the value 1 to each table, enabling an
-- as hash match implementation of a cross join by matching on this column
ALTER TABLE #t1 ADD one TINYINT NOT NULL DEFAULT (1)
ALTER TABLE #t2 ADD one TINYINT NOT NULL DEFAULT (1)
GO

-- 32K rows / table     CPU time = 61112 ms,    elapsed time = 5507 ms.
-- 64K rows / table     CPU time = 274955 ms,   elapsed time = 19695 ms.
-- Since the time scales quadratically once we have enough rows to efficiently utilize
-- all threads, we can predict 4MM rows / table with the following query:
--      SELECT POWER(4000000 / 64000, 2) * (19. / 3600)
-- 4MM rows / table     Estimated elapsed time = 20 hours
SELECT COUNT_BIG(*), SUM(1.0 * t1.c1 * t2.c1 * t1.c2 * t2.c2)
FROM #t1 t1
JOIN #t2 t2
    /* Add an equijoin logically equivalent to 1=1 in order to enable batch mode
        hash join to replace a loop join for this "cross join" */
    ON t2.one = t1.one
WHERE NOT EXISTS (
    SELECT *
    FROM #t3 t3
    WHERE t3.c1 = t1.c1
        AND t3.c2 = t2.c2
)
    /* Enable batch mode hash join  */
    AND NOT EXISTS (SELECT * FROM #batchMode WHERE 0=1)
OPTION (MAXDOP 16)
GO

Here is the corresponding query plan with 32K rows / table:


Reporting Services DBs not appearing in Management Studio, but can be queried?

You can also use T-SQL

use master;
go

alter availability group [ag_name] add database ReportServer

backup database ReportServer to
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    WITH FORMAT, INIT, COMPRESSION, STATS;
GO

backup log ReportServer to
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak';
GO

Now go to another replica and restore:

restore database ReportServer from
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    with replace, norecovery;
GO

restore log ReportServer from
disk=N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    with norecovery;
GO
alter database ReportServer set hadr availability group = [ag_name]
GO

However, what you really need to do is ensure that you're actually connected to the replica that currently holds the ReportServer* databases before you try adding things to the availability group. If you've somehow stumbled upon the secondary, or if you're connecting to the listener and the AG has failed over to what was formerly the secondary node (i.e. the replica that does not have the databases you need), then you're not going to see the ReportServer* databases.

Finally, please make sure you read the documentation.


Rebuild an index while it is being referenced in Oracle

Index rebuild operation is DDL and thus may lock the underlying table preventing you from updating it. However queries by nature do not lock (unless FOR UPDATE clause is specified) and can read from objects even if they're locked, thus, generally, no problems will arise during the index rebuild while queries are running (maybe except for some performance degradation during rebuild).

If you want to be able to update the table having the index which is being rebuilt, you can specify ONLINE clause:

SQL> alter index table1_pk_idx rebuild online;

Otherwise the session performing DML will wait until the index is rebuilt.

For more info on index rebuild, ONLINE clause, and index rebuild restrictions, read the section "Rebuilding an Existing Index" in Administrator's Guide, and the section "ALTER INDEX" in SQL Language Reference.


How to properly structure multiple user types?

You could turn around the foreign key relationships and add three foreign keys to user, one for each of customer, brand and brand_employee. Then add a CHECK constraint that makes sure that exactly one of the foreign keys is NOT NULL:
CREATE TABLE users (
   id                uuid PRIMARY KEY,
   ...
   customer_id       uuid REFERENCES customer(id),
   brand_id          uuid REFERENCES brand(id),
   brand_employee_id uuid REFERENCES brand_employee(id),
   CHECK (customer_id IS NOT NULL AND brand_id IS     NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS NOT NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS     NULL AND brand_employee_id IS NOT NULL)
);


Designing table for document annotation with future columns

JSON documents are ideal for this purpose. MySQL can store JSON documents via its JSON datatype, and it has a variety of function for extracting information in such documents. However, MySQL doesn't currently have an effective native way of indexing JSON documents for fast search. Googling for "mysql json index" will render several strategies for doing so, some using triggers and external tables, others using indexes on computed columns.

PostgreSQL, however, has very good support for JSON datatypes including indexing, opening up documents as recordsets (using LEFT JOIN LATERAL in combination with jsonb_each is very powerful), and GIN indexes that support JSON documents (for both tags and contents). Unless you're heavily invested in MySQL already, PostgreSQL would be a great option for your application. Alternatively, you may want to consider PostgreSQL instead of MongoDB if you'd use it for this purpose alone. Additionally, PostgreSQL can easily access MySQL tables with via Foreign Data Wrappers, it has good support for regex text manipulation, and you can do all sorts of magic on JSON documents through standard perl libraries, using pl/perl stored procedures.


SHOW TABLE STATUS;
and then run
show warnings;

will show you what all tables are corrupted.


How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted?

What I recommend is separating out the concern of "Who are people we've heard of?" from the concern of "Who can get into the system now and how do they do that?"

What this means in practice is that you should not refer directly to your aspnet_users table to note who did what and when, etc.

Instead, you should have a Person table into which you dump every new user once you add them to the system.  Your transactions should point at this Person table instead of your aspnet_users table.

Now, if you think it's important for any reason, you can have the Person table point to the aspnet_users table.  You can also do things like keep a status column on the Person table to note current status, etc.

However, when you no longer permit access to a given user, you should take them out of the ASP.NET access control tables (and update Person, if appropriate).  Doing this will not interfere with the values in your transaction tables.


'Select @VAR =' and 'Set @VAR =' behaving unexpectedly

The main difference between SELECT and SET is that the former can perform multiple assignments simultaneously, e.g.

DECLARE @var1 int, @var2 int;

-- with SELECT, you can assign them together:
SELECT @var1 = 5, @var2 = 11;

-- with SET, must perform these separately:
SET @var1 = 5;
SET @var2 = 11;

Personally I would do this even more concisely, dropping both SET and SELECT:

DECLARE @var1 int = 5, @var2 int = 11;

Both SET and SELECT as assignment operators have the same restriction that, if you assign the result of a subquery, that subquery must return exactly one row, so you're not forcing SQL Server to try to determine which row you meant. This restriction is loosened up when no subquery is involved (and it works differently according to the standard, not some choice Microsoft made). So your first two cases work as designed and the error message is expected. @var3 and @var4 also both work as I would expect (though personally I would write this differently - e.g. if you want some concept of "last" row, tell SQL Server which "last" row you mean by using ORDER BY, MIN, MAX, etc.). As written, my results are:

@var3
----------------------------------------------------------------------
"Last" company name -- happens to be "last" in clustered index order,
                    -- but this might be different with other indexes
@var4
----------------------------------------------------------------------
Generic Name -- definitely not NULL

@var5 also works as expected. Yes, you assign a literal value first, but the assignment of the result of a subquery - in the absence of any error - overrides the previous value. In the case of an empty set, then it sets the value to NULL.


Issues with TempDB mdf file ever increasing

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes.

Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file initialization enabled. You should really try to pre-size your file so that they're big enough to accommodate your busiest workload, and avoid growth events altogether. This isn't always practical, but in lieu of that, the growth size should be much larger. You really want this to be a rare and isolated event, not one that is occurring constantly. What is the point of keeping tempdb small, when it's going to use more space eventually? Are you going to lease the space out temporarily to the highest bidder, then evict them?

Also, why is there only one tempdb file? This is a common source of contention. Typical wisdom suggests to start with 4 files, even if they're all on the same disk. This can drastically reduce contention especially when multiple concurrent processes are trying to create objects or otherwise use space in tempdb. You may also want to look into enabling trace flag 1117 (which ensures that all of your data files grow at the same time) and trace flag 1118 (which changes extent allocation). Links about these below.

None of this will solve your core issue, of course - lack of (or concern over) disk space. If you don't have enough disk space to support the current usage of your system (whether it's the system or your users), get more disk space, or move the system. You might be able to find some of the culprits (see this answer for some common ones), but you won't be able to squash them all.

You might also find these things useful:

this blog post by Jason Hall
this survey from Paul Randal
this TechNet article on optimizing tempdb
this squashed myth from Paul Randal
this discussion on TF1118
this blog post from Paul White, explaining temporary object caching
this question about determining version store usage
this answer (also linked above) on determining contributors to tempdb log usage, and common causes for tempdb usage in the first place
there are also three "Linked Questions" in the right sidebar of the above answer that may be useful
this documentation on contributors to tempdb size
this question, also about contributors to tempdb size
this tip about tempdb configuration best practices on mssqltips.com

And also, now that we know you are using Service Broker, you may want to read these two pages which might help explain why your conversations aren't clearing out:

http://itknowledgeexchange.techtarget.com/sql-server/writeup-of-a-strange-service-broker-and-tempdb-problem/
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c4414a6-213b-4a69-8dcd-f162bc979b3b/service-broker-filling-tempdb?forum=sqlservicebroker


MSSQL - Limit Query to last 5 records of group by

You can accomplish this using a Common Table Expression (CTE) that assigns a row number to each detector_record_id partitioned on each installation_id and detector_id.

Order it descending and return the top 5 in your outer query. This grabs the most recent 5 records (based on detector_record_id) per installation_id and detector_id. The outer query then sums the detector_record_status and a simple HAVING clause eliminates those that don't meet the "Active" threshold.

As your table grows, it will become important to monitor the inner SELECT performance as it will be returning all rows from detector_record and this could become slow to execute.

Example

;WITH dRecords AS (
  SELECT installation_id,
    detector_id,
    detector_record_status,
    ROW_NUMBER() OVER (PARTITION BY installation_id, detector_id ORDER BY detector_record_id DESC) AS [Rn]
  FROM detector_record
)
SELECT
  installation_id,
  detector_id,
  SUM(detector_record_status) as Activity
FROM dRecords
WHERE [Rn] <= 5
GROUP BY   installation_id, detector_id
HAVING SUM(detector_record_status) >= 3
ORDER BY   installation_id


A more efficient way to write multiple CASE statements

Here is one way to rewrite the query that should give the same results without nested CASE statements. I'm checking for NULLs first, to potentially short-circuit additional testing. I considered using a single WHEN, with an OR between each expression, but OR isn't deterministic so this may perform better. If those columns are mostly null (vs mostly filled), you could try rewriting it to test for IS NOT NULL instead of IS NULL, and see if there is a performance difference:

CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table1.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
        AND Table3.DO = 'Z'
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table4.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table2.COLUMN3 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table3.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
...


Zabbix partitioning autovacuum causes locking

A normal autovacuum process will succumb silently if it blocks another transaction, but if that happens all the time and autovacuum can never finish, you will eventually have old table rows that have never been vacuumed. To prevent the danger of losing data as soon as the transaction ID counter wraps around, PostgreSQL then triggers a less benevolent autovacuum run that is not willing to back down.

Now creating a new partition for a table requires an ACCESS EXCLUSIVE lock on the partitioned table. Since this seems to happen every day, and autovacuum seems to run too slow to finish in one day, you end up getting stuck at midnight, when the ghosts of automatic table partitioning start rattling their chains.

Killing autovacuum is not a solution, because it will only make the problem worse. The work will be lost, and PostgreSQL will start autovacuum again, and even if you manage to silence it, eventually the database will refuse data modifications. Then your only option is to take down the database start it in single user mode and run a manual VACUUM. That causes a longer outage, so you don't want that.

The best solution is to tune autovacuum so that it can run as fast as possible, in the hope that it gets done within 24 hours:

set autovacuum_work_mem as high as you can
set autovacuum_vacuum_cost_delay to 0

Additionally, if you have many partitions, you should increase autovacuum_max_workers somewhat.

Now keep your fingers crossed and hope that autovacuum gets done before midnight.

You can monitor the state of affairs with something like

SELECT s.last_autovacuum, s.last_vacuum, age(t.relfrozenxid)
FROM pg_stat_all_tables AS s
   JOIN pg_class AS t ON s.relid = t.oid
WHERE age(t.relfrozenxid) > 200000000
ORDER BY age(t.relfrozenxid) DESC;

Keep working until that list is empty!

If you cannot get autovacuum to complete a single table in 24 hours, your only choice is to skip creating new partitions for a while (or at least not attaching them to the partitioned table).


How does Log shipping know to keep track

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012)

This is a ONE WAY situation.

When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012.

The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after failing over to sql server 2012, you wont be able to failback.

Note: SQL Server allows you to restore transactions logs from earlier versions of the product to higher vesions but the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation. It has to be in RESTORING mode only.

The only option for failback will be to upgrade the old sql server 2008 to 2012.

  At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours.

Logshipping is an automated process of doing FULL backup, copying it to secondary server and restoring it in NO-RECOVERY state and then taking consequent log-backups from the primary server, copying them to secondary server and restoring them.

Depending on the size of the database backup (whether you are using compression or not) and the network bandwidth, it can take from mins to hours. Other way is to initialize secondary database from the FULL backup of primary database - restore an existing backup of the primary database to the secondary database

  i wish to know: how does the log shipping process knows from when to start the logs it is shipping?

The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

Refer to : Monitor Log Shipping for more details about the tables and stored procedures used to track log-shipping jobs and status.

  does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?

It starts when you initialize the secondary database from the full backup of primary database restored with NO RECOVERY on the secondary server. Logshipping proces relies on the concept called Log-sequence number (LSN's) to link which log backup will be restored after the last log backup.

A picture is more than a thousand words !

Reference

  Can i be sure that no information was lost in these few hours ?

Yes there would be no information lost - unless in these few hours the primary server crashed or the backup becomes corrupted (due to I/O subsystem). As soon as the full backup is restored, logshipping will continue taking log backups on primary and start copying it to secondary server and restoring them.

The minimum backup/copy and restore frequency is 1 min.

Note: Once you configure a database for logshipping, you dont have to take any additional log backups - as this will break the LOG CHAIN and the logshipping will start failing.

Full and differential backups can be taken, but NO log backups.

Read up on more on How to Perform SQL Server Log Shipping BY BRAD MCGEHEE


It doesn't look like MongoDB provides a repo for Saucy: http://repo.mongodb.org/apt/ubuntu/dists/

So if you installed via Ubuntu-provided repository you have a few options:

Remove the RPM and install via binaries
Upgrade to Trusty
Wait for Ubuntu to update its repo.


It should be possible to make the slave read/write by disabling read_only. SET GLOBAL read_only=0, and changing it in the my.cnf configuration file.

Using GRANT you can create a user with SELECT, CREATE VIEW at

GRANT SELECT, CREATE VIEW ON *.* TO bob@localhost;

This should leave the user with enough access to do BI work on a slave without modifying data.


Import poorly formatted CSV/JSON to SQL Server

You can use the technique shown in Loading line-delimited JSON files in SQL Server 2016 on the SQL Server Database Engine Blog, written by Jovan Popovic .

For example, given a file import.txt:

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

and a format file import.fmt:

13.0
1
1 SQLCHAR 0 0 "\r\n" 1 json ""

The file can be bulk imported and parsed with:

SELECT
    Parsed.packageName,
    Parsed.postTime,
    Parsed.systemTime,
    Parsed.offset,
    Parsed.isOngoing
FROM OPENROWSET
(
    BULK 'C:\Temp\import.txt',
    FORMATFILE= 'C:\Temp\import.fmt'
) AS BulkLog
CROSS APPLY OPENJSON(BulkLog.[json])
WITH
(
    packageName varchar(50),
    postTime    bigint,
    systemTime  bigint,
    offset      bigint,
    isOngoing   bit
) AS Parsed;

Giving:


how do i use "?" as variables for my values so that i don't get hacked?

Caution These methods of escaping values only works when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).
In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods:
var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
  if (error) throw error;
  // ...
});

Escaping-Query-Values


How to anonymize pg_dump output before it leaves server?

There are four tough problems to solve when trying to anonymize data. I'm going to summarize an old blog post of mine about it:

1: Anonymized data may grow in size. If you have peoples' names, for example, you don't really want all of the anonymous ones to have exactly the same length as the original data. If you change the data as it goes out, that means the solution needs to understand the original field name's length to avoid growing data out to something too large to fit.

2: Anonymized data has different statistics distribution. Say you've got a date-of-birth field - you could completely randomize the dates, but then it'll be much less meaningful on the other side, especially if you're trying to do performance testing. Plus...

3: Anonymized data breaks referential integrity. I know it's a bad idea, but sometimes applications join on fields that need to be anonymized. I hate that I've seen this, but I've seen folks using email address in multiple tables, and then expecting to join on those, or find related orders.

4: Anonymizing slows down the export. You'll need to run some kind of business logic to determine which columns need to be anonymized, and then generate the new data in its place.

I used to say that if you could solve this problem, you'd be rich - but then you've got some competition. Delphix does data masking for DB2, Oracle, SQL Server, etc, but I don't think they've got Postgres or Heroku covered - yet.


Filters

The simple answer is to use VIEWS for all your replication filters. That way, you can easily modify your filters without having to drop and re-subscribe all subscribers.

  That is golden rule no. 1

DDL

If you modify any tables that are used in merge replication, you have to allow the DDL to apply down to the subscribers.
If you are worried about some tables replicating down at different times, maybe because they are in a different publication, and perhaps the FK will fail during replication inserts, then simply edit the FK's and add 'NOT FOR REPLICATION'.

  That is my golden rule no. 2

If I can impress anything on you, please use views for your filters. They are lifesavers. We have over 100 subscribers to our databases, and they are the best thing since sliced bread. We can easily modify a VIEW and voila, all done, with no pain at all.


Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine

From what I could see, it appears that Microsoft doesn't support LocalDb out of the box in Windows Server 2008. I just got the Web Platform Installer and tried searching for localdb and no entries were found. I went to the download site and grabbed only the LocalDb.msi installer and installed it in Win Server 2008. Created a default instance:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqllocaldb.exe c "v11.0" -s

Then tried to connect to it in my ASP.NET MVC 4 app but it didn't work. This works in Windows 8 RC.

I simply decided to go with SQL Express 2008 since this one the Web Platform Installer found!


Search results mismatch after postgresql update

Your problem is probably due to the difference from 9.3.4 to 9.3.5, see the manual for that, where it states:

  E.2.1. Migration to Version 9.3.5

  A dump/restore is not required for those running 9.3.X.

  However, this release corrects a logic error in pg_upgrade, as well as an index corruption problem in some GiST indexes. See the first two changelog entries below to find out whether your installation has been affected and what steps you should take if so.

and below, the first 2 changelog entries:

  In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce Momjian)

  If you used a pre-9.3.5 version of pg_upgrade to upgrade a database cluster to 9.3, it might have left behind a file $PGDATA/pg_multixact/offsets/0000 that should not be there and will eventually cause problems in VACUUM. However, in common cases this file is actually valid and must not be removed. To determine whether your installation has this problem, run this query as superuser, in any database of the cluster:

WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
  SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
         NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
         NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
         EXISTS (SELECT * FROM list WHERE file != '0000')
         AS file_0000_removal_required;

  If this query returns t, manually remove the file $PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f
  Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)

  This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.

  ...


SSPI error and DotNetMethod exception while connecting to the SQL DB using powershell script

Connection String should instead read:

DRIVER=SQL Server;Server=$SQLServer;Database=$SQLDBName;UID=xyz;PWD=abc;


Execution time high for Query in sp_executesql

The query optimizer is able to use parameter sniffing for the dynamic query as can be seen in the actual plan:

<ParameterList>
  <ColumnReference Column="@p__linq__5" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__4" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2009-06-11 23:59:00.0000000'" ParameterRuntimeValue="'2009-06-11 23:59:00.0000000'" />
  <ColumnReference Column="@p__linq__3" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2008-06-11 00:00:00.0000000'" ParameterRuntimeValue="'2008-06-11 00:00:00.0000000'" />
  <ColumnReference Column="@p__linq__2" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__1" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
  <ColumnReference Column="@p__linq__0" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
</ParameterList>

Those sniffed values can influence the query plan that's chosen. However, the query plan still needs to be safe for all possible values of the input variables. Let's look at one difference between the plans, the access on the IncidentDetailsPage_Header table. In the slower, dynamic plan you get a scan and a hash match which in total takes about 2.4 seconds:

In the faster plan with the hardcoded values you get an index seek instead which takes about 0 ms:

Here's the T-SQL that corresponds to that part of the plan:

 AND (([Extent1].[agencyori] = @p__linq__5)
      OR (([Extent1].[agencyori] IS NULL)
          AND (@p__linq__5 IS NULL)))

Hardcoding the values results in a single use query plan just for those parameter values. With your parameter values, that T-SQL can be simplified to this:

([Extent1].[AgencyOri] = 'MI3300287')

That is eligible for an index seek. The other code is not. Avoid kitchen sink queries when you can.


local login from DBA group member (as sysdba) not working

You have disabled local OS authentication by setting this:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Remove the above line from sqlnet.ora, after that, you will be able to log in to the database with:

sqlplus / as sysdba


The left-join variant:

select n.news_id, rating,
  coalesce(dg.lang_code, de.lang_code),
  coalesce(dg.news_title, de.news_title),
  coalesce(dg.news_details, de.news_details)
from news n
join news_detail de on (n.news_id = de.news_id and de.lang_code = 'EN')
left join news_detail dg on (n.news_id = dg.news_id and dg.lang_code = 'GER')

http://sqlfiddle.com/#!9/01d421/7 - returns only 3 rows because your sample data does not contain the EN translation for all news as your real data are supposed to.

For completeness, there is a version which picks first available translation (in order 'GER', 'EN') by a dependent subquery - http://sqlfiddle.com/#!9/01d421/9 (in this case it picks the German translation where there is no English one in the sample data, so results differ)


Not enough server storage is available to process this command - RESTORE DATABASE is terminating abnormally

The error message is misleading you, it's not talking about disk storage on the machine where you are restoring.

The windows error message states:

  1130(Not enough server storage is available to process this command.)

When you check that error message you will notice that it's not an error during the restore operation but rather during the file copy operation over the network as documented here

  The two causes of this problem are related. The more frequent cause is listed first:
  More files are open than the memory cache manager can handle. As a result, the cache manager has exhausted the available paged pool memory.
  The backup program has tried to back up a file whose size is larger than the backup API can access on that version of the operating system. This has the same result (that is, the paged pool is exhausted).
  Note This second issue is more likely to occur on a Microsoft
  Windows NT 4.0-based computer.

  The resolution for each problem differs depending on whether you
  experience the problem in Windows Server 2003, in Microsoft Windows
  2000, or in Windows NT 4.0.

Or here

  When you access shared items on a Windows NT server from a Windows NT
  client, you may receive the following error message:   Not enough
  server storage is available to process this command.

So as you say, you likely have enough storage to restore the database but you are running into issues retrieving the file over the network.
In any case you need to be looking at some SMB issue

Had you been running into disk space problems during the restore the error message would have been:

  112(There is not enough space on the disk.)

As @Nic correctly pointed out in the comments you should probably try to copy the backup file over using an unbuffered file copy (use ESEUTIL or XCOPY) and restore from there to either solve or at least isolate the issue.

If you still run into issues copying over the file that way I'm not sure that still fits the scope of dba.se.


I found the issue.  It is documented an oracle note (Doc ID 1086519.1).  I used the first workaround listed which is to set CURSOR_SHARING to exact.

ALTER SESSION SET CURSOR_SHARING=EXACT;


Automatically rotate mongo logs in mongod.conf file

If you are using *nix system, you have few choices. You can use logrotate service and send SIGUSR1 signal to mongod/mongos process. Or you can change your config systemLog.logRotate: rename and again send SIGUSR1.

To rotate log you can give command db.adminCommand( { logRotate : 1 } ) too.

How you do it on the windows side, that I don't know.


Oracle Data Guard:When would data rows exist in the view v$archive_gap?

An archivelog gap simply means that the apply service on the standby site cannot continue because there is a missing sequence between the sequence that was applied and the next sequence that is available (either shipped from the primary or copied manually).

In other words, if sequence 57 was applied at the standby site, but the next sequence available is 59, then the apply service cannot continue until sequence 58 is available (and has been applied).

Resolving gaps largely depends on why there is a gap.  This can happen if an archivelog backup is taken on the primary site with the "delete all input" option and those archivelogs have not been sent to the standby site yet.  Since archivelog backups are not automatically sent to the standby site when the shipping process to re-enabled, the gap is detected on the standby site and the gap is then viewable in v$archive_gap.

In the above example, resolving the gap involves (assumes a Windows environment):

Manually copying the archivelog backup to the standby site via OS commands (in this example, I copied the archivelog backups into l:\oracle\archivegaps):

Register the archivelog backups with the standby site:

catalog start with 'l:\oracle\archivegaps';

Unpacking the archive files from the archivelog backup:

RUN
{
  SET ARCHIVELOG DESTINATION TO 'l:\oracle\archivegaps';
  RESTORE ARCHIVELOG FROM sequence 58 UNTIL sequence 58;
}

Register the archivelogs with the standby site (this step may be optional, as unpacking the archivelogs from the backup may register the archivelogs automatically, but I honestly don't remember).

catalog start with 'l:\oracle\archivegaps';

You may also need to restart the apply service before the gap is resolved:

alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

As for your specific questions:

A1: fal_client and fal_server should have no bearing on whether rows are in v$archive_gap.

A2: A gap is detected on the apply side, not technically the shipping side, so a gap can be detected even if shipping is turned off.  Additionally, if a gap is detected while shipping is on, then shipping is turned off, the gap will still be listed in v$archive_gap until the gap has been resolved.


is it possible to set engine for database (not the tables)?

There is no "database" engine setting. A database can have tables using different engines.

There are two variables that define which engine will be used when a table is created with a CREATE TABLE that does not include an ENGINE = EngineName specification. The variables are the (global and session) default_storage_engine. The global is configured at startup (from my.cnf or command line) and the session variable can be setup during each session.


You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:

grant select on acs_activities to your_user;

If you want to do that for all tables, you can use:

grant select on all tables in schema public to you_user;

Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:

alter table acs_activities owner to your_user;