The answer is you cannot shrink log file if there is no free space available.

Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

Can you run DBCC LOGINFO(DB_name) and see what is last value in status column. If value is 2 you cannot shrink if value is 0 you can because 0 means VLF is truncated and no active transaction is using it. You can read how shrinking of Transaction Log works

A log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended. It wont give you failure message but wont even shrink. I also think there is no free space in log file which can be freed and you are just forcing it to free it.
What is the query you are using to shrink database log file can you please show it.

You must read This BOL article on shrinking a log file. What is output of below query

Use Db_name
GO
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Next question would be why are you shrinking Log file every day is it really causing issue. Shrinking of log file is also a evil operation because when log file grows process running has to wait for it to grow and since instant file initialization is not there for user database log files every time log file grows it has to Zero out space and write the the information there. This could be a time consuming on busy system where disk I/O is heavily utilized so moral is don't shrink log files. by shrinking you are just allowing it to grow again and it will grow again because SQL Server has to write information in log files related to queries running on system

You can pre-size it and provide necessary space to log files so as to avoid autogrowth events.

Make sure you keep proper value for Autogrowth setting don't keep it in percentage as it will cause unnecessary growth. More about autogrowth can be read from this link. See by how much SQL Server is growing and you would allocate that much space for a month to database log file so as to avoid and autogrowth events.


Receiving several deadlocks with simple queries when ran concurrently from different nodes

I would use an UPDATE with an OUTPUT instead of two completely separate statements.

ALTER PROCEDURE dbo.getnextsessionID
  @id varchar(15) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @t TABLE(id varchar(15));

  ;WITH NextID AS
  (
    SELECT TOP (1) sessionID,used
      FROM dbo.uniqueids
      WHERE used = 0
      ORDER BY ID
  )
  UPDATE NextID SET used = 1
    OUTPUT deleted.sessionID INTO @t;

  SELECT @id = RTRIM(LTRIM(id)) FROM @t;
END
GO

Also if the table is large (and as the set where used = 1 grows), I might suggest a filtered index instead, simply because it's much cheaper to maintain:

CREATE INDEX session_used
  ON dbo.uniqueids(ID, sessionID, used)
  WHERE used = 0
  WITH (DROP_EXISTING = ON);


How should I (re) index the table while keeping the transaction log small?

DBCC REINDEX is minimally logged in the SIMPLE or BULK_LOGGED recovery model.  If the database is currently in the FULL recovery model, consider toggling it to BULK_LOGGED for the index maintenance.

See http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


How do you find home information for Oracle RAC on Unix?

First of all, I should thank you for all other steps that you mentioned above.
As an addition to your points, I have this one which I've been using so far.

Solaris and Linux

$ pgrep  -lf _pmon_
12586 ora_pmon_ee10204

ORACLE_SID is ee10204

$ pwdx 12586
12586: /u01/oracle/10.2.0.4/ee1/dbs

ORACLE_HOME is /u01/oracle/10.2.0.4/ee1

HPUX

$ ps -ef | grep pmon
ora1024 25611     1  0  Mar 21  ?         0:24 ora_pmon_itanic10

ORACLE_SID is itanic10

$ pfiles 25611 | grep  bin
25611:                  /opt/ora1024/app/db_1/bin/oracle

ORACLE_HOME is /opt/ora1024/app/db_1

AIX

$ ps -ef | grep pmon
ora1024   262314        1   0   Mar 23      -  0:12 ora_pmon_mercury

ORACLE_SID is mercury

$ ls -l /proc/262314/cwd
lr-x------   2 ora1024  dba  0 Mar 23 19:31 cwd -> /data/opt/app/product/10.2.0.4/db_1/dbs/

ORACLE_HOME is /data/opt/app/product/10.2.0.4/db_1

Personally, I haven't seen any environment that tries to confuses the person who will maintain the system.
Most of the time, I've always seen the normal /u01/app/oracle for the ORACLE_BASE.


Access view based on table in another database without account in that other database

This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this:

Stored procedure security with execute as, cross database queries, and module signing

Permissions in triggers when using cross database certificates

The difference for this particular question is that it deals with a View, and Views cannot be signed. So, You will need to change the View into a multi-statement Table-Valued Function (TVF) as those can be signed and can be accessed just like a View (well, for SELECT access).

The following example code shows doing exactly what is being requested in the question in that the Login / User "RestrictedUser" only has access to "DatabaseA" and is yet able to get data from "DatabaseB". This works only by selecting from this one TVF, and only due to it being signed.

Accomplishing this type of cross-database access while still using a View, and not giving the User any additional permissions, would require enabling Cross-Database Ownership Chaining. That is far less secure because it is completely open-ended for all objects between both Databases (it cannot be restricted to certain objects and/or Users). Module Signing allows just this one TVF to have the cross-DB access (the User doesn't have the permission, the TVF does), and Users that cannot SELECT from the TVF have no access to "DatabaseB" at all.

USE [master];

CREATE LOGIN [RestrictedUser] WITH PASSWORD = 'No way? Yes way!';
GO

---

USE [DatabaseA];

CREATE USER [RestrictedUser] FOR LOGIN [RestrictedUser];

GO
CREATE FUNCTION dbo.DataFromOtherDB()
RETURNS @Results TABLE ([SomeValue] INT)
AS
BEGIN
    INSERT INTO @Results ([SomeValue])
        SELECT [SomeValue]
        FROM   DatabaseB.dbo.LotsOfValues;

    RETURN;
END;
GO

GRANT SELECT ON dbo.[DataFromOtherDB] TO [RestrictedUser];
GO
---

USE [DatabaseB];

CREATE TABLE dbo.[LotsOfValues]
(
    [LotsOfValuesID] INT IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK_LotsOfValues] PRIMARY KEY,
    [SomeValue] INT
);

INSERT INTO dbo.[LotsOfValues] VALUES
    (1), (10), (100), (1000);
GO

---

USE [DatabaseA];

SELECT * FROM dbo.[DataFromOtherDB]();

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

All of the steps above recreate the current situation: the User has access to DatabaseA, has permission to interact with an object in DatabaseA, but gets an error due to that object in DatabaseA accessing something in DatabaseB where the User does not have any access.

The steps below set up the Module Singing. It does the following:

creates a Certificate in DatabaseA
Signs the TVF with the Certificate
Copies the Certificate (without the Private Key) to Database B
Creates a User in DatabaseB from the Certificate
Grants SELECT permission to the Table in DatabaseB to the Certificate-based User

Module Signing setup:

CREATE CERTIFICATE [AccessOtherDB]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing other DB',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO dbo.[DataFromOtherDB]
    BY CERTIFICATE [AccessOtherDB]
    WITH PASSWORD = 'SomePassword';

---
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'AccessOtherDB')), 1);

SELECT @CertificatePublicKey AS [Cert / PublicKey]; -- debug

EXEC (N'USE [DatabaseB];
CREATE CERTIFICATE [AccessOtherDB] FROM BINARY = ' + @CertificatePublicKey + N';');
---

EXEC (N'
USE [DatabaseB];
CREATE USER [AccessOtherDbUser] FROM CERTIFICATE [AccessOtherDB];

GRANT SELECT ON dbo.[LotsOfValues] TO [AccessOtherDbUser];
');

---

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
-- Success!!

SELECT * FROM [DatabaseB].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

IF ACCESS NEEDS TO BE THROUGH A VIEW, for whatever reason, then you can simply create a View that selects from the TVF shown above. And, in that situation, SELECT access does not need to be granted to the TVF, only to the View, as demonstrated below:

GO
CREATE VIEW dbo.[DataFromTVF]
AS
SELECT [SomeValue]
FROM   dbo.DataFromOtherDB();
GO

-- Remove direct access to the TVF as it is no longer needed:
REVOKE SELECT ON dbo.[DataFromOtherDB] FROM [RestrictedUser];

GRANT SELECT ON dbo.[DataFromTVF] TO [RestrictedUser];

And now to test it:

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'DataFromOtherDB',
database 'DatabaseA', schema 'dbo'.
*/

SELECT * FROM [OwnershipChaining].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

SELECT * FROM dbo.[DataFromTVF];
-- Success!!

REVERT;

For more info on Module Signing, please visit: https://ModuleSigning.Info/


Postgres Error: could not read block 167593 in file... SQL state: XX001

However, after searching for similar issues, all I can see from other
  people is this error for 'of relation base', not 'in file'. How is 'in
  file' different, and what does this mean? Is it more likely that I
  have a hardware issue?

No, it's the same issue. The error message used to refer to the relation in older versions of PostgreSQL, but it was changed at some point (version 9.0, probably) to refer to the corresponding file instead.

I think the switch to the new message format happened here:

  commit 23dc89d2c385e8e362cb4b8186b4d4ad02242ac0
  Author: Heikki Linnakangas
  Date:   Wed Aug 5 18:01:54 2009 +0000

Improve error messages in md.c. When a filesystem operation like open() or
fsync() fails, say "file" rather than "relation" when printing the filename.

This makes messages that display block numbers a bit confusing. For example,
in message 'could not read block 150000 of file "base/1234/5678.1"', 150000
is the block number from the beginning of the relation, ie. segment 0, not
150000th block within that segment. Per discussion, users aren't usually
interested in the exact location within the file, so we can live with that.

To ease constructing error messages, add FilePathName(File) function to
return the pathname of a virtual fd.


Setting READ UNCOMMITTED when reading DMVs

As one of the guys writes demo DMV queries that way, I'll explain why.

Does it matter if you're only querying DMVs?  No.  But sooner or later, you're going to take one of your DMV scripts and tack on a join to sys.databases or sys.tables or some other system object in order to get more information about what you're looking at.  If you don't have read uncommitted on there, you can be blocked by other queries, and block other queries.  I've been burned by that repeatedly, so I just use READ UNCOMMITTED by default whenever I'm doing any diagnostic work whatsoever.


How to find repeating records effectively?

Is this sql the fastest way to find out if the user registered or not?

It's close enough.
If the table is indexed on englishname (and, preferably, idcard as well) then yes.  Indeed, if those two uniquely identify each row, then they really ought to be the Primary Key of the table.
If the table is not indexed, then it really ought to be.
However ...
Never use "select *" in Production code.
Never use raw, User-supplied values directly in dynamically generated SQL.
Doing so is one of the quickest ways to get your table and, possibly, your whole database well-and-truly fouled up.  The problem is known as an SQL Injection Attack and your code is wide open to one.
Obligatory XKCD Reference: Little Bobby Tables.
Your best plan would be to move to using Prepared/Parameterised Statements but, failing that, you must "escape" the values from the User so that they form valid and safe SQL.


If I understand you correctly, you want to use functions.

Those are stored (with their code), their execution plans (may be) cached and so on.  Plan caching is not so simple as it first sounds.  Read https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING and the linked page about PREPARE.

For the additional questions from your comments, PostgreSQL functions don't necessarily protect you from SQL injection.  Building dynamic SQL statements the wrong way (using concatenation without proper quoting) can still open the possibility for such attacks.


Try this, it should help put you in the right direction.

SELECT l.StaffID,
       l.FirstName,
       l.Surname,
       m.ModuleDescription
 FROM dbo.Lecturer as l
 RIGHT OUTER JOIN dbo.Module as m
   ON l.StaffID = m.ModuleConvenor
WHERE m.ExamWeight = (
            SELECT AVG(m2.ExamWeight) AS 'Avg ExWeight'
              FROM dbo.Module as m2
             WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL)
 AND m.CWWeight =  (
    SELECT AVG(m3.CWWeight) AS 'Avg CWWeight'
     FROM dbo.Module as m3
    WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)

I did the following

Updated your where clause syntax
Update your aggregate function typo from AVE to AVG
Updated your alias being used in the aggregate function in both subqueries

You may need to post some sample data and table ddl information. It is not clear to me how the module and lecturer tables should be joined based on your query. This answer helps correct your syntax, it may not be full solution to what you are trying to achieve.


I assume your table schema is similar to next one:

CREATE TABLE ConstructorEmployee
(
    EID int PRIMARY KEY
);

CREATE TABLE Project
(
    PID int PRIMARY KEY
);

CREATE TABLE ProjectConstructorEmployee
(
    PID int,
    EID int,
    CONSTRAINT PK_PCE PRIMARY KEY(PID, EID),
    CONSTRAINT FK_P FOREIGN KEY (PID) REFERENCES Project (PID),
    CONSTRAINT FK_CE FOREIGN KEY (EID) REFERENCES ConstructorEmployee (EID)
);
GO

With two FOREIGN KEY on ProjectConstructorEmployee table.

Now let me add some data 3 employes and 3 projects, but Employee=1 has worked only on the first Project.

INSERT INTO ConstructorEmployee VALUES (1), (2),(3);
GO

INSERT INTO Project VALUES (1), (2), (3);
GO

-- EID = 1 worked in PID 1 only
INSERT INTO ProjectConstructorEmployee VALUES
(1, 1), (1, 2), (2, 2), (2, 3), (3, 2), (3, 3);
GO

Next query returns a list of employees that has worked in only one project, in this case PID=1

-- Employees working on PID=1 that didn't work in any other project
SELECT EID
FROM   ProjectConstructorEmployee
WHERE  EID IN (SELECT EID FROM ProjectConstructorEmployee WHERE PID=1)
GROUP BY EID
HAVING COUNT(*) = 1
GO

| EID |
| --: |
|   1 |

  I need to create a trigger that...

No, don't use a trigger, let me suggest to use an STORED PROCEDURE, it's always more clear for your and for any further developer. Or if you want to use a trigger, use a BEFORE DELETE trigger that calls this procedure.

CREATE PROCEDURE DeleteProject(@PID int)
AS
BEGIN
    BEGIN TRY

        DECLARE @employee TABLE (EID int);

        BEGIN TRANSACTION;

        -- save a list of employees that only worked in this proejct
        -- due to FOREIGN KEYS you can't delete ConstructorEmployee table
        -- until you have deleted the other both.
        INSERT INTO @employee
        SELECT EID
        FROM   ProjectConstructorEmployee
        WHERE  EID IN (SELECT EID
                       FROM ProjectConstructorEmployee
                       WHERE PID = @PID)
        GROUP BY EID
        HAVING COUNT(*) = 1;

        DELETE FROM ProjectConstructorEmployee
        WHERE  PID = @PID;

        DELETE FROM Project
        WHERE  PID = @PID;

        DELETE FROM ConstructorEmployee
        WHERE EID IN (SELECT EID FROM @employee);

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Your error handler

        THROW;

    END CATCH
END
GO

Ok, let me try it by deleting projects 1 and 2:

EXEC DeleteProject @PID = 1;
EXEC DeleteProject @PID = 2;

SELECT * FROM ConstructorEmployee;
SELECT * FROM Project;
SELECT * FROM ProjectConstructorEmployee;
GO

Remaining employees:

| EID |
| --: |
|   2 |
|   3 |

Projects:

| PID |
| --: |
|   3 |

and ProjectsEmployees:

PID | EID
--: | --:
  3 |   2
  3 |   3

db<>fiddle here

But...are you sure you want to delete all this information?

You will lost all the Project's history, employees and projects will disappear of your database. Maybe you could set a flag like Active(Yes/No) a let this information available for whichever want to recover later.


Identify Non matching records in Join- Oracle

Assuming that the combinations of values in CD, SEQ, DEF in the MASTER table are unique, you could write a cross join which gives you all "valid" combinations for the INFO table - see Query1.  Then you could this query, along with a query that returns all rows from INFO, and the set operators MINUS and UNION ALL to find the rows you are looking for (see Query2).

Query1

select sid, cd, seq, def
from
  ( select unique sid from info )
, ( select cd, seq, def from master )
;

SID CD  SEQ DEF
1000    1   1   A
1000    1   1   B
1000    1   1   C
1000    2   1   A
1000    2   1   B
1000    2   1   C
1000    2   1   D
2000    1   1   A
2000    1   1   B
2000    1   1   C
2000    2   1   A
2000    2   1   B
2000    2   1   C
2000    2   1   D

Query2

select *
from (
  (
    select sid, cd, seq, def, 'missing from info' as comments     -- Query1
    from
      ( select unique sid from info )
    , ( select cd, seq, def from master )
    minus
    select sid, cd, seq, def, 'missing from info' from info       -- INFO + comment
  ) union all (
    select sid, cd, seq, def, 'in info (not in master)' from info -- INFO + comment
    minus
    select sid, cd, seq, def, 'in info (not in master)'           -- Query1
    from
      ( select unique sid from info )
    , ( select cd, seq, def from master )
  )
) where cd = 2 and seq = 1
;

Result

SID   CD  SEQ DEF  COMMENTS
1000  2   1   D    missing from info
2000  2   1   A    missing from info
2000  2   1   E    in info (not in master)

Caveat: In your question, you are saying (among other things) that
"SID 2000 using the same CD,SEQ All master DEF records exist"
although the combination (cd, seq, def) 2-1-A is missing from info.  Query2
will report this combination to be missing.

Dbfiddle here.  Table comparison technique described here.


Efficiently finding the ultimate parent in multi-level one-to-many tables

I simplified a bit by allowing only one price for a given room in the rates table.

CREATE TABLE users (
  user_id INTEGER NOT NULL
, CONSTRAINT pk_users PRIMARY KEY (user_id)
);

CREATE TABLE properties (
  property_id INTEGER NOT NULL
, user_id     INTEGER NOT NULL
, CONSTRAINT pk_prop PRIMARY KEY (property_id)
, CONSTRAINT fk1_prop FOREIGN KEY (user_id)
                 REFERENCES users (user_id)
);

CREATE TABLE rooms (
  room_id     INTEGER NOT NULL
, property_id INTEGER NOT NULL
, CONSTRAINT pk_rooms PRIMARY KEY (room_id)
, CONSTRAINT fk1_rooms FOREIGN KEY (property_id)
             REFERENCES properties (property_id)
);

CREATE TABLE rates (
  room_id INTEGER NOT NULL
, price   INTEGER NOT NULL
, CONSTRAINT pk_rates PRIMARY KEY (room_id)
, CONSTRAINT fk1_rates FOREIGN KEY (room_id)
                  REFERENCES rooms (room_id)
);

Some data to test

INSERT INTO users (user_id)
VALUES (1), (2), (3) ;

INSERT INTO properties (property_id, user_id)
VALUES
(11, 1), (12, 1), (13, 1),
(21, 2), (22, 2),
(31, 3) ;

INSERT INTO rooms (room_id, property_id)
VALUES
(1101,11), (1102,11),
(1201,12), (1202,12),
(1301,13), (1302,13),
(2101,21), (2102,21),
(2201,22), (2202,22), (2203,22),
(3101,31), (3102,31), (3103,31) ;

INSERT INTO rates (room_id, price)
VALUES
(1101,110), (1102,110),
(1201,120), (1202,120),
(1301,130), (1302,130),
(2101,210), (2102,210),
(2201,220), (2202,220), (2203,220),
(3101,310), (3102,310), (3103,310) ;

Update price for room_id = 2102 to 100, if and only if it belongs to user_id = 2.

UPDATE rates      AS t SET price = 100
  FROM rooms      AS r
  JOIN properties AS p ON p.property_id = r.property_id
  JOIN users      AS u ON u.user_id     = p.user_id
 WHERE t.room_id = r.room_id
   AND r.room_id = 2102
   AND u.user_id = 2
;

Note that all joins are on PKs, so this is fast. Also there are no multiple trips to the app and back.

Try it on dbfiddle or visualise the explain!


MS SQL Server: Replication Monitor Transactional Replication Publisher Log Reader Agent Latency Too High?

It depends. But from your screenshot it seems like you have small workload with 6 seconds on log reader agent..

you can use the below script to check for the latency of your logreader agent:

USE [distribution]
SELECT la.name, delivery_latency / 1000, [time]
--AVG(delivery_latency / 1000) AS delivery_latency_Sec
FROM MSlogreader_history as lh
INNER JOIN MSlogreader_agents as la ON lh.agent_id = la.id
--WHERE la.name = ''
ORDER BY [time] DESC
--GROUP BY la.name

What you can do/check:

check you publisher database t-log. How big is your t-log? How many VLF it got? Make sure your t-log is properly pre-size.
How often you are doing reindexing? This can also bloat your t-log and slow down logreader agent reading your t-log. Read it here. So be careful on your index maintenance.
Do you have large batch processing? (from your screenshot seems like no but be aware of large batch processing as it can also slow down the logreader reader/write thread)
Slow IO latency on publisher db t-log?
blocking/locks on distribution database? (highly unlikely for small workload)

Note that there's no magic number for the log reader agent latency. It is going to be depending on how you maintain your publisher database t-log. It should not be cause of concern IMO.


Use a cross reference table to link characters to media, as in:

CREATE TABLE dbo.Characters
(
    CharacterID INT NOT NULL
        CONSTRAINT PK_Characters
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CharacterName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.MediaTypes
(
    MediaTypeID INT NOT NULL
        CONSTRAINT PK_MediaTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaTypeName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Media
(
    MediaID INT NOT NULL
        CONSTRAINT PK_Media
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaName VARCHAR(100) NOT NULL
    , MediaTypeID INT NOT NULL
        CONSTRAINT FK_Media_MediaTypeID
        FOREIGN KEY
        REFERENCES dbo.MediaTypes(MediaTypeID)
);

CREATE TABLE dbo.CharactersMedia
(
    MediaID INT NOT NULL
    , CharacterID INT NOT NULL
    , CONSTRAINT PK_CharactersMedia
        PRIMARY KEY CLUSTERED
        (MediaID, CharacterID)
);

INSERT INTO dbo.MediaTypes (MediaTypeName)
VALUES ('TV Show')
    , ('Movie');

INSERT INTO dbo.Characters (CharacterName)
VALUES ('Don Juan')
    , ('Don Johnson');

INSERT INTO dbo.Media (MediaName, MediaTypeID)
VALUES ('Miami Vice', 1)
    , ('Don Juan', 2);

INSERT INTO dbo.CharactersMedia (MediaID, CharacterID)
VALUES (1, 2)
    , (2, 1);

SELECT m.MediaName
    , c.CharacterName
    , mt.MediaTypeName
FROM dbo.Media m
    INNER JOIN dbo.CharactersMedia cm ON m.MediaID = cm.MediaID
    INNER JOIN dbo.Characters c ON cm.CharacterID = c.CharacterID
    INNER JOIN dbo.MediaTypes mt ON m.MediaTypeID = mt.MediaTypeID;


I would guess "yes".  But I would not ask anyone, I would simply try it.  It is non-destructive, etc; trying is much faster and easier than waiting for an answer.


SQL Server 2008 R2 - Weekly / Monthly Statistics

SQL Server doesn't track things like table access and concurrency.

You could do this kind of thing yourself if you do some or all of these things:

audit logins
force all data access via stored procedures (and perform custom logging)
add triggers to capture insert/update/delete activity if you can't force data access through stored procedures
capture concurrency for activity by taking snapshots of DMVs (e.g. sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests)
server-side trace / extended events for anything not captured above


SQL update multiple rows in destination table with same id but different values from other table

If you don't care which timestamp is used for which target row, you can match the two tables by joining them on a generated "row number" using e.g. this:

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
select *
from pnr
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id;

This joins the rows based on the ordering of the ID values in the tables creditnote and paymentmodeamount. If you want you can also order the creditnote table by generated_at.

Given your sample data this would return something like this (this is only to demonstrate what the above join does):

id    | invoice_id | generated_at | rn | id    | invoice_id | generated_at        | rn
------+------------+--------------+----+-------+------------+---------------------+---
63725 |        111 |              |  1 | 10101 |        111 | 2018-02-28 14:42:39 |  1
98236 |        111 |              |  2 | 23982 |        111 | 2018-03-30 11:11:11 |  2

Now the above query can be used as part of an UPDATE statement to bring the rows together.

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
update paymentmodeamount p
   set generated_at = cn.generated_at
from pnr
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id
where p.id = pnr.id
  and p.generated_at is null;

This assumes that paymentmodeamount.id is the primary key of the table.

Note that this won't be very fast for large tables.

Online example: https://rextester.com/YXAK25669


Left Join Not returning rows when date is in range of longer than 1 day

All these nested derived tables (subselects inside subselects) are useless. You can easily remove all except 1 or 2.

SELECT
    dev_hour.hour,
    rtdogs.delivery_date,
    rtdogs.delivery_hour,
    rtdogs.total_offer
FROM
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0),
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0),
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0),
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer,
          delivery_hour,
          delivery_date
      FROM rtdog
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15'
      GROUP BY delivery_date, delivery_hour
    ) AS rtdogs
  ON dev_hour.hour = rtdogs.delivery_hour ;

Further improvements:

The ORDER BY delivery_date is useless inside a derived table.
Don't use single quotes for quoting table and columns aliases (eg. 'total_offer'). Use backquotes if you have to, if the alias has spaces or other non-allowed characters. In this case, they are not needed at all.

Now the real issue of the question: the result is only 24 rows, no matter what you have in the BETWEEN condition.

The reason is that the delivery_hour table has only 24 rows (it's a guess but judging from the results, it's probably correct.) Therefore, if you want multiple dates in the results, the solution is to use another (say delivery_date) table, with the date values needed or supply one as a derived table. Then CROSS JOIN it to the delivery_hour, so you have all combinations of dates and hours that you need (say 4 days x 24 hours = 96 combinations/rows).

We can then LEFT JOIN to the rtdogs summary result as before, only changing the ON clause to take dates into account as well:

SELECT
    dev_date.date,                         -- add the date to the result
    dev_hour.hour,
    rtdogs.delivery_date,
    rtdogs.delivery_hour,
    rtdogs.total_offer
FROM
    ( SELECT DATE('2016-01-15') AS date UNION ALL
      SELECT '2016-01-16' UNION ALL
      SELECT '2016-01-17' UNION ALL
      SELECT '2016-01-18'
    ) AS dev_date                           -- our added "dates" table
  CROSS JOIN
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0),
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0),
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0),
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer,
          delivery_hour,
          delivery_date
      FROM rtdog
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-18'
      GROUP BY delivery_date, delivery_hour
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date       -- changed here
  AND dev_hour.hour = rtdogs.delivery_hour
ORDER BY                                         -- if we need ordering,
    date, hour ;                                 -- it's done here

You can either build the dates list dynamically (in your PHP/Java/Python code) or have a permanent "dates" table and use it. With a permanent dates table, the FROM would be like:

FROM
    dates_table AS dev_date                      -- the permanent "dates" table
  JOIN
    delivery_hour AS dev_hour
  ON dev_date.date BETWEEN '2016-01-15' AND '2016-01-18'
  LEFT JOIN
    ( SELECT
          -- nothing changes here
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date
  AND dev_hour.hour = rtdogs.delivery_hour
ORDER BY
    date, hour ;


Upgrade SQL Server 2012 Express to Evaluation Edition

You can not Upgrade from SQL Server Express Version to SQL Server Evaluation.

See this msdn link for all upgrade path...

Supported Version and Edition Upgrades...

Install them side by side and they you copy wizard to move DB.


You have not aliased the OPENQUERY result set as "PS" in your supplied query. Without the alias "PS", SQL Server does not know to what this refers, so PS.TXN_ID is meaningless.

Try this:

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ') ps
WHERE NOT EXISTS (select TXN_ID from PA_TRANSACTIONS PT WHERE PT.TXN_ID =PS.TXN_ID)

Alternatively, try using the EXCEPT operator:

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ')
EXCEPT
SELECT TXN_ID FROM PA_TRANSACTIONS

EXCEPT Operator


Restricting database access per IP addresses

I think the only pure Oracle way to do this would be to run 2 separate listeners on different ports out of 2 different ORACLE_HOMEs, rather than just using the one.

A much more sane way of approaching this would be to use other security measures... Separate Usernames and passwords are quite common for this task - I guess you must be using LDAP or something that prevents this. Another option would be a login trigger on each database that checks the remote IP and disconnects the session if it's not come from the correct machine.

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

... will give you the remote IP address.

A trigger such as:

CREATE OR REPLACE TRIGGER DENY_LOGIN
AFTER LOGON ON DATABASE
DECLARE
  foo varchar2(128);
BEGIN

  IF ( sys_context('userenv','ip_address') <> '192.168.100.130' )
  THEN
    raise_application_error( -20001, 'Connection not authorised' );
  END IF;

EXCEPTION
WHEN OTHERS THEN
  RAISE;

END;

... will do the job for DB WORKSHOP.

(Untested, but should be OK)

Probably best to add a clause that allows DBAs to login from localhost too, or just exclude them completely :)


In a Serial 3-Server Replication Scenario (A>B>C), can server B preserve binlogs that server A has purged?

No problem.

A (as a Master) writes to its binlog
That stuff is copied (usually immediately) to the "relay-log" on B (as a Slave).
That stuff is written to B's binlog
That stuff is copied to the relay log on C.

If the network from A to B goes down for more than 10 days, you will lose data.  For B to C, you have 30 days to repair the network, even though some stuff may no longer be in A's set of binlogs.

Another thing to note:  I think the purging of old binlogs does not occur until the current binlog exceeds its limit.  If that limit is large (in comparison to the activity), it could take days to roll over.  You might want to shrink the limit to get better granularity.  (And it is OK if A and B have different limits.)


Can we use TDE as an anti-injection technique

The attacker will get the data unencrypted.

The T in TDE stands for "transparent".  The user will never see encrypted data.  The database transparently decrypts it when it is read from disk and transparently encrypts it when writing to disk.  If your application is insecure, TDE doesn't help you plug those application security holes.  You need to fix those in the application (so use bind variables!)


Automatically invoke `SET ROLE` when connecting to PostgreSQL

This isn't something PostgreSQL can do on its own

You want to do this in your connection pooler

Right after you get a connection from the pool, call SET ROLE

Right after you release a connection to the pool, call RESET ROLE

Not a Ruby guy, so can't help you much with that, but here's how you'd do it in Java:

public class SetRoleJdbcInterceptor extends JdbcInterceptor {

    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        if(authentication != null) {
            try {

                /*
                  use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec.

                  Or use a whitelist-map approach
                */
                String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());

                Statement statement = pooledConnection.getConnection().createStatement();
                statement.execute("set role \"" + username + "\"");
                statement.close();
            } catch(SQLException exp){
                throw new RuntimeException(exp);
            }
        }
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

        if("close".equals(method.getName())){
            Statement statement = ((Connection)proxy).createStatement();
            statement.execute("reset role");
            statement.close();
        }

        return super.invoke(proxy, method, args);
    }
}


pg_restore showing errors when specifying schema when backing up with pg_dump

As Postgres does exports with if exists in both commands, but you can make the database be dropped before inserting the backup
use
When you create the dump
-c
--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

The same exists for the pg_restore, but if you used it in pg_dump you don't need it here
-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)


How to find out unused port on Linux server

netstat -an | grep LISTEN | grep 5444 will show if there's a process listening on that port.

netstat -an | grep LISTEN will show you all ports with a process listening on them. You can then use lsof to see which processes are actually using a given port.

There's no such concept of "find out the application port even the application was shutdown", as anything can listen on any port (with certain restrictions if not running as root). The closest thing to this is the /etc/services file, which documents ports that are "well known"/assigned by IANA. Also see http://www.iana.org/assignments/port-numbers. But, as I said, there's nothing to stop anything from running on one of these ports.

To be honest, you should know what's running on a production box that you have root access to?


What is a "Reporting Task for Subplan" task in SSIS, and how can I create one?

Please open them in SSMS Maintenance Plans instead of using BIDS to open them & you won't have to worry about the GUIDs. The task you're asking about is created automatically when you make a maintenance plan in SSIS.


Which options to enable to guarantee full recovery in SQL Server Express 2005?

One answer to add to help explain Recovery models in a nutshell, the terminology (especially with Full also being a type of backup) confuses people.

We'll skip bulk logged which is a hybrid recovery model that is ideal when you are doing certain bulk operations and can programatically change your recovery model as part of the processing.

Quick Background - SQL Server maintains a write ahead transaction log. In the example of your application making an update - the update is written to the log first (and HAS to be written for the transaction to be considered committed). The write will eventually make it to the data files at certain points but it doesn't need to be in the event of a restart/crash of SQL Server. Because it is hardened to the log file, SQL has the right info to recover or "replay" that transaction if it was all done before the crash or roll it back or "undo" the transaction if it wasn't all done. This log file continues to grow and grow and grow depending on your recovery model:

Full Recovery Mode --> You are telling SQL Server "I really care about being able to restore to a point in time." So this means, essentially, that you want SQL to keep it's transaction log records even beyond what is necessary just for that crash recovery but you want to keep the log records, back them up on a schedule and have the ability to restore to a specific point in time. Under this recovery model your log file continues to grow until you take a backup and then it can be truncated (physical size stays where it has grown to but some portion of the log file can be reused based on some other rules)

Simple Recovery Mode --> "I don't need to go to a specific point in time, my last good full backup is fine." You are telling SQL Server to just truncate that log file or reuse the space within it as SQL sees fit - when the records are no longer needed for that crash/restart recovery, basically.

Under either mode above you have the ability to fully recover your database, if you mean - restore it to a known good point. Simple mode means your recovery can only be the last good full backup. Full mode gives you the ability to restore to a specific point of time if your log file is intact at the time of failure or to the last good log backup if not.

So the question is - what are your RPOs/RTOs promised and agreed to with your business unit (http://www.straightpathsql.com/archives/2011/02/can-you-restore-sql-server-sqlu/) to determine which mode. Just remember, if going to full it is imperative that you take transaction log backups or face log file growth that just keeps going. Also remember that when you switch from simple to full, you must take a full backup to initialize your change before you are able to start taking log backups.

More on SQL Server Recovery Models - http://msdn.microsoft.com/en-us/library/ms189275.aspx


There is no direct option to disable the generate scripts function. This option comes with the ability to VIEW DEFINTION. What level of access does the user have? What do they really need to do?


Problem with MySQL multiple join on same table with condition

I tweaked the fiddle you provided, and I believe I now get the results you wanted. Here's the updated fiddle.

The updated query is:

SELECT `c`.*, `t`.`check_name`,`al`.`check_area_id` as 'loc_check_area_id',`l`.`area_name` AS 'location_name',`ad`.`check_area_id` as 'dept_check_area_id', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN (`check_assigned_areas` `al`
             INNER JOIN `areas` `l` ON `l`.`area_id` = `al`.`check_area_id` AND `l`.`area_level` = '3'
          ) ON `al`.`check_id` = `c`.`check_id`
LEFT JOIN (`check_assigned_areas` `ad`
            INNER JOIN `areas` `d` ON `d`.`area_id` = `ad`.`check_area_id` AND `d`.`area_level` = '4'
          ) ON `ad`.`check_id` = `c`.`check_id`
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'
;

Here's the results (truncated to just show the location and department data).

 check_id | ... | loc_check_area_id | location_name | dept_check_area_id |  dept_name
----------+ ... +-------------------+---------------+--------------------+--------------
   19358  | ... |       8091        |  Luton Office |        8092        | Development
   19357  | ... |      (null)       |     (null)    |       (null)       |   (null)

Since there are two separate check_assigned_areas rows for the check that has a valid location and department, you would logically get two rows for that check. One row represents a location, one a department. So, you got separate rows for those values.

I changed the query, so instead of separate LEFT JOINs for check_assigned_areas, areas that are locations, and areas that are departments, you have a LEFT JOIN for check_assigned_areas and areas that are locations, and a separate LEFT JOIN for check_assigned_areas and areas that are departments. In both of these LEFT JOINs, check_assigned_areas and that set of areas rows are INNER JOINed together.

This means that you'll get one (set of) row(s) for the location(s) that tie to this check, and one for the department(s).

Note that there are two check_assigned_areas.check_area_id values involved, so I show the check_area_id from the location and the check_area_id from the department as two separate columns.

Also note that, if there are multiple areas that are locations tied to the check, and/or multiple departments, you would see a row for each possible combination of location and department found; that's why the previous paragraph is written to allow for such.

However, if there's zero or one locations, and zero or one departments, you'll get one row with the location and department found.


PostgreSQL pg_basebackup from several port number

Using physical replication, no, that isn't possible.  You would have to use the new-ish logical replication feature to consolidate the data from multiple clusters to one cluster.


Which is better: more rows or more columns in a student attendance table?

There are several disadvantages to using the "month-based row" approach, and I do not see any advantage.

Using a row to represent an instance of attendance for a student makes logical sense. Using a row to represent a month's worth of attendance is arbitrary--why a month instead of a year or a 6-week cycle?
Think about how you'll be querying this data, or how you'll write a parameterized report for your users--when you're asked to find how many students were absent on a specific day, you'll need to parse out the month to find the right record, then look at different columns depending on the day of the month. Needless complication.
Right now, it looks like you want to record whether they are absent and the reason, but that will change eventually. You'll need to add a comment to explain the reason, whether it was approved, or some such, and you don't want to add 31 columns to your table every time that happens.


one node in cluster got crashed ,how to recover it?

Ok solved my issue..

My hard drive failed so mysql log got hosed, end of story..

I needed full state transfer and to remove everything except the mysql database with all those myisam tables full of permissions..

Stop the broken node completely..

root@my-galera-clustercontrol:~# s9s_galera --stop-node -i 1 -h10.0.1.111

Rsync out the mysql database folder from mysql data folder..

root@my-galera-percona1:/mnt/data/mysql# rsync -av mysql ../mysqlback/

Delete everything include bad logs from mysql data folder:

root@my-galera-percona1:/mnt/data/mysql# rm -rf *

Restore the mysql data folder:
    root@my-galera-percona1:/mnt/data/mysql# rsync -av ../mysqlback/mysql .

Start the node back with a in cluster control causing a full state transfer:

root@my-galera-clustercontrol:~# s9s_galera --start-node -i 1 -h 10.0.1.111 -d 10.0.1.112

From there you can watch crazy io with iftop until eventually the node came back online as synced..


How can we manage cross-database dependencies across environments?

2. Databases on a remote server only accessible to production

  ... every environment should be identical (with the exception of configurations such as credentials and connection strings)...

With regards to Linked Servers, this one is actually not so difficult. You just need to classify them correctly: they themselves are a component of the system and should be identical across environments, BUT their definition is a configuration that is often different between environments. Meaning, what needs to be consistent across environments is the fact that the Linked Server exists, and its name. This way any and all references to the Linked Server(s) can be identical across environments.

For non-Production environments, you just need to set up an Instance to be a dummy target of the Linked Server so that validations made against it when code is compiled will succeed. This can be achieved with SQL Server Express if need be. You don't need any data in this Instance, just the Schema that is explicitly referenced in your code. You can share one of these dummy targets between environments, but you should probably have two of them: one for the current structure, and one for changes being made in those remote resources that you will need to test against before they go live (assuming that you are given notice of upcoming changes).

And in case it is not obvious, these dummy targets are shared resources, regardless of whether devs are developing against private, local Instances on their workstations or against a shared Instance.

The Schema for the dummy targets is not mixed in with your app / DB code. It is either considered configuration or as a component of your build process / CI process.

  1. Databases on the same server

  (quoted out of order) While bootstrapping developer environments, we must take care to create these in a particular order in order to successfully apply DDL and DML-scripts, lest we face dependency errors... it is causing so much headache that some of our developers have simply given up on local developer environments and do all of their development in a shared database.

I understand the idealism behind developing against private DBs to prevent external interference, but in practice I am not sure how necessary it is. I worked in an environment that had easily 20 DBs for 7 - 10 products, over 1000 tables, over 3000 stored procedures, etc. We had easily 7 product teams. Some of the DBs were dedicated (i.e. isolated) to a particular product, and some where shared across multiple products. And there were other complexities that I am leaving out, but the point is that it was a more complex environment than what you are describing. We had shared DBs in all environments: dev, QA, staging, etc. In the 6 years I worked there I can think of maybe a handful of times where there was cross-team interference. And when there was, we just discussed the situation with each other and came up with a way to not impede each other. Sometimes we rearranged the order in which we worked on particular stories / tasks so get the dependency done sooner, or if that was not possible, we might stub something out / make a partial change for something that would be done soon but didn't have time to get to right then. I don't remember there being a time when a team couldn't proceed at all (and if I am forgetting, then it was still quite rare). If the dependency was that large of a factor for the story / task, then we usually discussed it before planning the sprint so that we would know if our story could even proceed, or if it had to be punted to the next sprint while the other team made their changes.

All of that to say, getting this to work on dev workstations for private development sandboxes is a bit of work and it might not be worth the investment of time in getting this working. Especially because it ain't just boot-strapping that you need to handle. If it was merely an issue of initial setup, then I would still recommend restoring the most recent backup of the shared dev Instance (because you will still have one of those anyway, right, for when code is checked in? just like there should be a shared app server that gets deployments from all teams to make sure that everything compiles correctly before migrating up to QA). I don't see any benefit in running SQL scripts as that still leaves the private Instance with no data. And how much time do you want each dev spending on adding rows to tables so that they can test their change? Not only is that a waste of time, but it is highly error prone as we all know that they won't go through the app (and hence the app logic), but will simply do direct inserts or use SSMS to edit the table(s) to add rows. And they will add the bare minimum number of rows with only ideal conditions. So you will end up getting more instances of code that doesn't functionally work against real data, along with the dev saying, "well, it works on my box".

But it's not just a matter of initial population of structure (and hopefully data). You still need to push down committed changes from others. And that might even be more likely to fail now since another team might have completed their project that either breaks when being pushed down to the dev workstation, or changes from a dev workstation can't merge into the shared DB because the environment changed. In a shared Instance, these interdependencies are found and accounted for much earlier in dev cycle.

All of that to say, if your developers are not currently blocking each other semi-frequently, then I recommend sticking with your current setup of a shared Instance.

  ... These are databases with separate repositories, deployment pipelines, ...

Separate repositories, libraries, etc is not a problem. But separate deployments could certainly complicate things, the degree to which depends on what direction(s) the dependencies are in. Do dependencies ever go both ways between projects, or always from one project/DB to another?

I do not put much faith in fully-automated database deployments. Checking in code and having a process blindly take it all and do something with it works fine for app code since app code is just that: code. You take the committed / checked-in files, compile them, and you have your finished product (typically). If there are dependencies between libraries then they are handled by building the projects in the correct order.

But databases, by their very nature, are more than just stateless code: they also contain state. And so I don't see how one gets around custom deployment scripts (outside of singular databases and/or not much data). I know some folks are able to use automated migration scripts, but I have never worked in an environment that could. So as much as I strongly advocate for keeping all code in source control, I would never consider generating deployment scripts from it.

Due to our complex structure (and the number of servers / DBs we were deploying to — same schema deployed to anywhere from 4 up to 20+ instances, depending on the product, and multiple DBs per each Instance — we had a custom deployment system. It was multi-threaded and quite nice, actually (mostly using nAnt and CruiseControl, I believe). Our process was to have a separate release script folder (per each release), in the repository (separate from Trunk) that the build system processed, triggered by checkins. We had folders for each project, and within each project folder, we had folders for Schema (Tables, Views, and Triggers) and Data, Functions, Stored Procedures, etc. When we updated stored procedures and functions (i.e. code) we checked the object script into both our team branch and to the appropriate release folder. All release scripts were required to be idempotent (i.e. re-runnable). The build process processed the folders in the same order each time, and ran the Schema and Data folder before the "code" folders. For Schema and Data changes, we checked the object script (basic CREATE statement) into the team branch, but a custom migration script into the release script folder. This allowed us to properly handle transactions, dependencies, etc. Also, per each release script folder, all scripts were prefixed with a 3-digit order number: 001 - date - this change.sql, 002 - date - shortDescription.sql, etc. This way the release was processed in the same way each time it ran, and it ran a couple of hundred times over the course of the 3-week sprint. If we were working in a DB that was a shared resource and we had a lot of changes to make, we would simply "reserve" the number of slots we needed by checking in empty .sql scripts with the first and last order numbers we needed. Everyone always did an update before committing anything, so we always knew what the next available order number was. Worst case scenario, we renamed a script to adjust the order number if two people checked in the same number at roughly the same time (but again, this was quite infrequent, even with 10 of us constantly checking stuff in).

One tricky thing was managing the Linked Servers. We had one DB were all Linked Servers existed (the rule was that they would only ever be in that one DB). And, to make it even better, we were using Replication and had to ensure that schema changes were made at the subscriber before data changes were made. To make this work, we had two release script folders for this one DB: one of them was the first release script folder to process, the other was the last release script folder to process. Schema changes went into the "first" folder so that the changes were in place for other DBs with dependencies to it, and data changes went in the "last" folder since by this time, the publisher and subscribers had their schemas updated.

Hopefully this gives you some direction and ideas for handling these complexities of dependencies and Linked Servers, etc.

Good luck.


How do they do it

Fisrt step, select first substring of N characters in reverse mode.

REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition))<

Second one, find first space position in the reversed string, that is the beginning of the selected word:

SELECT @pos = CHARINDEX(' ', @Str);

Then reverse it again, get left part of the string and add a separator '/':

LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';

Last step by calculating next initial position, according to the beginning of the selected word:

@InitPos + @CharPosition - @Pos + 1;

The whole function:

CREATE FUNCTION [DescFormatter] (@Text nvarchar(max), @CharPosition int)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Pos int = 0;
    DECLARE @Str nvarchar(max) = '';
    DECLARE @Out nvarchar(max) = '';
    DECLARE @InitPos int = 1;

    WHILE @InitPos + @CharPosition < LEN(@Text)
    BEGIN
        SELECT @Str = REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition));
        SELECT @Pos = CHARINDEX(' ', @Str);
        SELECT @Out = @Out + LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';
        SET @InitPos = @InitPos + @CharPosition - @Pos + 1;
    END

    SELECT @Out = @Out + SUBSTRING(@Text, @InitPos, LEN(@Text) - @InitPos + 1);

    RETURN @Out;
END
GO

How to use it:

SELECT dbo.[DescFormatter]('Deluxe and spacious 1 King Bed NS in room Whirlpool Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor', 47);

This is the result:

Deluxe and spacious 1 King Bed NS in room /Whirlpool Computer Wet Bar Refrigerator Mini /Bar Desk Safe WIFI Warm Decor

dbfiddle here


MySQL: Data pagination according data on another table

METHOD #1
You could create a temp table called itemID_paginator and paginate this
CREATE TABLE items_paginator AS SELECT itemID from Items WHERE 1=2;
ALTER TABLE items_paginator ADD PRIMARY KEY (itemID);
INSERT INTO items_paginator SELECT itemID from Items;

You then paginate against itemID_paginator and join it to Ads
SELECT A.*,B.* FROM
(
    SELECT itemID FROM itemID_paginator ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) PG
INNER JOIN Items A ON PG.ItemID = A.ItemID
INNER JOIN Ads   B ON A.ItemID  = B.ItemID;

METHOD #2
If you prefer not to use a temp table, then perform LIMIT directly to the Items table
SELECT A.*,B.* FROM
(
    SELECT * FROM Items ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) A INNER JOIN Ads B ON A.ItemID  = B.ItemID;

I suggested these methods before in the following posts:

Jan 31, 2017 : Slow query while selecting a higher offset from millions of rows
Dec 21, 2013 : Using OFFSET in a large table by a simple sub-query
Feb 21, 2013 : Does MYSQL join before LIMIT?
May 16, 2011 : Fetching a Single Row from Join Table

GIVE IT A TRY !!!


When the number of users increases, the site is slowed down

MyISAM.  Say no more.  The "table locks" are probably what causes the performance to decline as you get more 'users'.

Some tips on the conversion to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb


Separate Address Table, Latitude, Longitude

The right answer is choice #5: use PostGIS. An example from OpenGeo:

  "Note the magic <-> operator in the ORDER BY clause. This is where the magic occurs. The <-> is a “distance” operator, but it only makes use of the index when it appears in the ORDER BY clause. Between putting the operator in the ORDER BY and using a LIMIT to truncate the result set, we can very very quickly (less than 10ms on a 2M record table, in this case) get the 10 nearest points to our test point."

(emphasis added)


SSIS package with log provider configured using expression - creates multiple log files, fails if I change original target folder

I believe it has to do with the timing and interpretation of System Variables.

Using this expression to build a filename with _.log I had been using:

@[User::PathROOT]+"edw_logs\\"+ @[System::PackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", @[System::StartTime]),2)  + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", @[System::StartTime]) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", @[System::StartTime]),2) +".log"

and two files were created once on validation and once when it ran.

Using just User variables, they are evaulated at runtime and only one file is created.  See this:

@[User::PathROOT]+"edw_logs\\"+ @[User::MyPackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", GETDATE()),2)  + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", GETDATE()) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", GETDATE()),2) +".log"


How do I get more control over backup file retention using Ola Hallengren's DatabaseBackup solution?

This can occur when you have variations in the runtime of your backups. If your backup job executes faster one day it may be less than 48 hours since the oldest file was created, so it will not remove it and leave you with 3 files.

Example:

Backup1 created on 2019-06-17 at 21:01 - deletes backups created on or before 2019-06-15 21:01
Backup2 created on 2019-06-17 at 21:03 - deletes backups created on or before 2019-06-16 21:03
Backup3 created on 2019-06-17 at 20:59 - deletes backups created on or before 2019-06-17 20:59 which does not include Backup1

The easiest solution I've found for this is to subtract a few hours from the CleanupTime value. Setting to 40 or even 45 in the example above would mean Backup1 was deleted after Backup3 was taken.


Does EnterpriseDB support Oracle RAC to Postgres replication?

The difference between Oracle RAC and Postgres cluster architectures does not in itself prevent you from setting up replication between an Oracle database (RAC or no RAC) and a Postgres database (Advanced Server or not).


Entering multiple entries in SQL where the count increases by one

As I'd suggested on comments, this is a trigger in action BEFORE INSERT to autogenerate a thing_id with the UID given.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
BEGIN
SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
END
$$
DELIMITER ;

Note: Change the database and table to yours.

Test:

mysql> SELECT * FROM test.Things;
Empty set (0.00 sec)

mysql>
mysql> USE `test`;
Database changed
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
Query OK, 0 rows affected (0.00 sec)

mysql> USE `test`$$
Database changed
mysql> CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
    -> BEGIN
    -> SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
    -> END
    -> $$
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER ;
mysql>
mysql> SET @UID='71420150404',@thing1='First Thing.',@thing2='Second Thing.',@thing3='Third Thing.';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO test.Things(UID,thing)
    -> VALUES (@UID,@thing1),(@UID,@thing2),(@UID,@thing3);
Query OK, 3 rows affected, 1 warning (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql>
mysql> SELECT * FROM test.Things;
+-------------+----------+---------------+------------+
| UID         | thing_id | thing         | thing_date |
+-------------+----------+---------------+------------+
| 71420150404 |  0000001 | First Thing.  | NULL       |
| 71420150404 |  0000002 | Second Thing. | NULL       |
| 71420150404 |  0000003 | Third Thing.  | NULL       |
+-------------+----------+---------------+------------+
3 rows in set (0.00 sec)

mysql>


noexpand hint for non-enterpise edition and performance

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead.

My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the base tables. And it runs more consistently too

YMMV of course :-)

So, to answer, it will (should?) work the same based on what I've seen. Other folk may have different experiences and I'm interested in other answers

To avoid using the hint everywhere you can wrap the indexed view in another view with the hint: hints propagate inwards to all outer queries will automatically have NOEXPAND.


OVER and PARTITION BY for db size change

It's the daily change, if the snapshots are taken daily (snapshot_time column is where I assume you're getting the snapshot time data).

Partitioning restarts computation each time a unique value for the column (or combination of columns) specified in the PARTITION BY is found. Even though you are sorting (ORDER BY instance, db, snapshot) you aren't aggregating data across the partitions in any way without an aggregation function preceding OVER().


Schedule suggestion and index rebuild / database integrity progress for Ola Hallengren’s Maintenance Script

We can't answer the first two since that is for you to decide. Or rather, your Recovery Point Objective. How much data can you afford to lose? That is the deciding factor for the first two.
As for IndexOptimize, You might end up defragmenting indexes without seeing much gain from it. If it doesn't hurt you, then sure do it every day. But if it does, then perhaps less frequently. As for defrag, some consider not doing it at all on modern disk subsystems (I've wrote about this here: http://sqlblog.karaszi.com/fragmentation-the-final-installment/).
Updating stats might be more beneficial than defrag. However, Ola's default job focuses on defrag and not update stats (you'll only get new stats for the indexes where you have more than 30% fragmentation as a side effect of index rebuild). OTOH, some consider updating stats not being smart since you'll end up with new plans after that and plan generation costs, and you might get a worse plan than before. OTTH better stats can give better quality plans - so there's no one consensus on how to do these tings.
You can create a secondary job to update statistics only.
Ola logs what we does in the CommandLog table, checkin in on that is probably the best progress you can get, aside from sys.dm_exec_requests (some commands will show progress in this DMV).


Oracle Enterprise Manager 11g dbconsole "Internet Explorer cannot display the webpage"

This is broken due to this patch: http://technet.microsoft.com/en-us/security/advisory/2661254

As per this link http://trinidba.wordpress.com/2012/10/11/internet-explorer-cannot-display-the-webpage-when-connecting-to-oem/ you can fix it with Oracle Doc ID Doc ID 1489557.1


Install Oracle Database 12c Error unable to check whether the location specified is on CFS

there is two solutions for this:
1- install Oracle as administrator
2- create a folder in C or E drive then right click it, go to security tab, and change permission for the user (grant him owner access on that folder)


How to make trigger code execute only for a specific application?

While not a good design, it is doable. You'll need to put logic in the trigger so it causes the code to only be executed when the correct application is connected.

Create trigger...
As
    If app_name() = 'something'
    begin
        put code here
    end


How to add 'root' MySQL user back on MAMP?

MySQL's suggested procedures

Assuming you went with a generic install of MySQL, my procedure for MAMP is:

1. Edit your my.cnf, probably located at: /usr/local/my.cnf, adding the lines:

skip-grant-tables
skip-networking

2. Restart mysqld

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit

3. Change root privs

`mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;`

4. Remove the skip lines from the my.cnf

## skip-grant-tables
## skip-networking

5. Restart mysqld again

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit


What causes lost updates being observed in my SQL Azure database?

The problem was because of "other interpretation" lost update - when data is first read into local memory and then the table is updated with that data. There was another code that was intended for resetting balances of some users very rarely. It was something like this:

DECLARE @BalancesToReset TABLE
(
   UserId uniqueidentifier,
   PriorBalance int,
   NewBalance int
)

INSERT INTO @BalancesToReset( UserId, PriorBalance, NewBalance )
    SELECT UserId, AvailableMoney, dbo.ufn_ComputeNewBalance(some params)
    FROM UserBalances WHERE SomeIncorrectConditionHere

UPDATE UserBalances SET
    AvailableMoney = BR.NewBalance FROM
       @BalancesToReset BR INNER JOIN UserBalances
       ON UserBalances.UserId = BR.UserId;

 INSERT INTO UserBalanceChanges( PriorBalance, NewBalance )
    SELECT PriorBalance, NewBalance
    FROM @BalancesToReset WHERE NewBalance < PriorBalance;

and this code would run concurrently with the code in question. The code was intended to INSERT from SELECT only a small fraction of user balances and only occasionally but because of an incorrect condition in the SELECT part it would select a huge portion of balances and very often. The balances that were unintentionally selected had NewBalance equal to PriorBalance and so the final INSERT from SELECT would not log the changes.

The UPDATE in the middle of this code would forcibly reset the AvailableMoney for many users to a precomputed value and this would introduce a "second interpretation" lost update which is possible in SQL Server.


How to Create key (to be used in join) using concatenation?

You don't need to (and shouldn't) concatenate a string just to join on multiple columns.

You can use

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON t1.accountID = t2.accountID
                  AND t1.state = t2.state
                  AND t1.product = t2.product;

Or - if the columns are nullable and you want nulls treated equal.

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON EXISTS (SELECT t1.accountID,
                                 t1.state,
                                 t1.product
                          INTERSECT
                          SELECT t2.accountID,
                                 t2.state,
                                 t2.product);

Similarly you can create a composite PK on all three columns without this repetitious concatenated string.

(Assumes that all are NOT NULL and no duplicates exist)

ALTER TABLE dbo.Table1
  ADD CONSTRAINT YourPK PRIMARY KEY CLUSTERED (accountID, state, product );


Uniqueness-like constraint but with limit greater than 1

Not all things can be easily achieved in SQL.  I think the best approach is to do the test in the application.  In pseudo code:

START TRANSACTION;
$ct = SELECT COUNT(*) FROM booking WHERE ...
if ($ct >= 5)
    error to user
else
    INSERT ...
COMMIT;

OK, I guess this could be encapsulated in a Stored Procedure, plus error handling in your application.


Run a Stored Procedure in DB2 (which has dynamic DDL) under the Definer rights

In general, dynamic SQL statements can be executed with definer (binder) rights. However, certain DDL statements, including ALTER TABLE can only be executed dynamically under DYNAMICRULES RUN.

I'm not sure why you chose to run the statement dynamically. DB2 allows static execution of DDL statements, in which case they execute with the definer authority.

A simple example:

db2inst1@blusrv:~> db2 "create procedure test begin create table blah (f1 int); end"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 "call test"

  Return Status = 0
db2inst1@blusrv:~> db2 describe table blah

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
F1                              SYSIBM    INTEGER                      4     0 Yes

  1 record(s) selected.


How to pass variable to PL/pgSQL code from the command line?

The manual for psql:

  Variable interpolation will not be performed within quoted SQL literals and identifiers.

The body of a DO statement (or function) is a quoted literal. Dollar-quoted in this case, but all the same:

$$
DECLARE
 _now_date timestamp := :now_utc;
BEGIN
  --
END
$$

Insert text with single quotes in PostgreSQL

To enable SQL interpolation, do it outside the string and then concatenate - which gets tedious quickly ...

One way to make it simpler is to let Postgres do the string processing and then execute the result using \gexec. Call from psql:

SELECT format($$
DO
$do$
DECLARE
   _now_date timestamp := %L;
BEGIN
  RAISE NOTICE '%%', _now_date;
END
$do$;
$$, :'now_utc')\gexec

NOTICE:  2019-06-14 00:41:53.040879
DO

(% has a special meaning inside format(), using %% to get a single % in the string.)

Also note the syntax :'now_utc' to get a quoted string.

How can I quote a named argument passed in to psql?
How to concatenate psql variables?

To just use the current UTC timestamp, you don't need all this complication:

DO
$do$
DECLARE
   _now_date timestamp := now() AT TIME ZONE 'UTC';
BEGIN
  RAISE NOTICE '%', _now_date;
END
$do$;

DO statements are not meant to take parameters. It's simpler to create a (temporary) function and pass value(s) as function parameter(s) in the call:

CREATE FUNCTION pg_temp.foo(_now_date timestamp)
  RETURNS void AS
$func$
BEGIN
  RAISE NOTICE '%', _now_date;
END
$func$  LANGUAGE plpgsql;

Call with SQL interpolation in psql:

SELECT pg_temp.foo(:'now_utc');

Finally, for passing values, you can also (ab)use a Postgres "customized option" (as session variable):

SET myvars.now_date TO :'now_utc';

DO
$do$
BEGIN
  RAISE NOTICE '%', current_setting('myvars.now_date', true);
END
$do$;

Note that the the value is stored as text and you may need to cast.

Details:

Declaring variable for a whole script

But then you might skip the psql variable and set the option in Postgres directly ...


Concatenate in MS Access - how to omit space and comma if first value is missing?

Thank you Akina for your suggestion.

As I am really a newbie, it took me some time to work out how to use IFF.

At the end here's the working string to remove comma and space at the beginning if the first value is null (empty):

=([Method].value) & iif (IsNull([Method].value) ,"",", ") & + [Experiment]


All the Log Shipping jobs run on their fixed schedule, while the number of files they process each time may be variable.  The Copy jobs perform a "best effort" copy of all files that haven't been copied yet and aren't currently in use.  The Restore jobs perform a "best effort" restore of all the files available that haven't been restored yet.  However, that is contingent on adhering to any other rules you may have specified (ex. Setting a restore delay of 2 hours so you can possibly retrieve data off your Secondary if someone does an unintentional change/delete of data on the Primary).

Essentially, you can think of each job as taking a kind of Work Queue approach that first creates a list of work to perform before processing each item on that list.  If a transaction log backup is still writing to file DBTranLogBackup.bak at the time the Copy runs, that particular file will not make it onto the list of work that can be performed and will get processed during the next run of the job.

Hope that helps.


How to change the instance name of SQL Server Express 2008?

There is no way to change the instance name without re-installing, sorry.....

Update: OK, seems there are a few ways of doing this - not sure how well these work, though. I have not ever heard of anyone doing this successfully, though :-( The easiest way to do this is re-installing with the correct name. Especially with the SQL Server Express versions, it's really no big deal to reinstall - done in a breeze, and you can of course backup and restore your data - so no loss there, either....

Also, it seems that method is limited to SQL Server 2000 only - when searching for the appropriate topics for SQL Server 2008 (sp_dropserver, sp_addserver), it seems those are used for renaming a server (computer) to a new name - not changing the SQL Server instance name, in my opinion. There's no more mention of renaming an installed SQL Server instance with that method.


These options indicate the location where the package is stored. /SQL indicates the packages is stored in the msdb database and /DTS indicates the package is stored in the SSIS Package Store.

See here for more information.


Moving MongoDB shards from one server to another

You are not copying the contents of the config servers and effectively creating a new sharded deployment rather than restoring a backup of an existing one. Config servers include essential information about where data lives in a sharded deployment, so you must backup & restore the config server data along with the shards.

Note: there have been changes to sharded cluster configuration in successive releases, so make sure you are using documentation for the correct MongoDB release series. The links below are specific to MongoDB 3.0.

For supported backup approaches see the MongoDB 3.0 Backup & Restore a Sharded Cluster tutorials.

  sh.addShard("localhost:27010")

  sh.addShard("localhost:27011")

If you've restored a sharded cluster from a backup, the shards will already be configured. If host names have changed from the original deployment, you will need to update the config.shards information  via a mongos rather than adding new shards (and then restart the mongod instances in your sharded cluster to ensure the new names are used). See the MongoDB 3.0 Restore a Sharded Cluster tutorial for more specific details.

  sh.shardCollection("log", {"id" : 1, "eventId": 1})

If a sharded cluster is already configured,  sharding an existing sharded collection will return an error. Since you ran this command starting from fresh config server data, the sh.shardCollection() command will be unaware of how existing data is distributed on the shards and assume that the primary shard for a database with an unsharded collection has the full range of data.


Function is accepting varchar when input is set to INT

This is how MySQL liberally performs implicit type conversion. Seeing that the function wants an integer parameter, the server tries to convert whatever you supply (a character string) into an integer. If the string only contains digits, the conversion is trivial. If the string contains digits first and then some non-digits, those latter are truncated at the first non-digit, and the remainder is converted to an integer. If the string begins with non-digits, it is converted to a 0.

dbfiddle


Can't remote access MySQL server running on Mac OS X

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf files all of which specified bind-address=0.0.0.0. I even removed and reinstalled MySQL following these instructions and then reinstalled using brew install mysql. Still no remote connections allowed.

It wasn't until I ran ps -ax | grep mysql and noticed that the bind address was being passed in the launch command (thus overriding any my.cnf files) that I dug some more and found out that Homebrew binds MySQL to 127.0.0.1 by default.

Editing ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist to change --bind-address=127.0.0.1 to --bind-address=0.0.0.0 solved my problem (the latter should be changed to a specific IP address if this isn't just a development machine).

I feel this is a vital piece of information that was lacking from most of the resources I consulted so hopefully posting this here will help someone else!

EDIT: As LeandroCR indicated in the comments, running brew services restart mysql will overwrite the plist file in LaunchAgents with the default one, leading to MySQL mysteriously refusing connections again. So better advice than what I originally wrote is the following:

Edit /usr/local/Cellar/mysql/<yourversion>/homebrew.mxcl.mysql.plist and replace --bind-address=127.0.0.1 with bind-address=* or --bind-address=0.0.0.0 (see MySQL documentation on bind-address)
Restart mysql using brew services restart mysql

Then MySQL should continue to accept non-local connections from then on - until you reinstall it, presumably.

Edit (Sep 2019)
Timothy Zorn points out that this problem no longer occurs for MySQL 8.x installed and run via Homebrew, so my answer above, written in 2016, may only be relevant to 5.x.


Point in time recovery from the same database - what does it mean?

The process of restoring a database only ever goes forward in time, never backward 1. So restoring a database to a point in time involves starting from a full backup, and then applying transaction log backups up to/containing the point in time to which you wish to restore.

The idea is that the ability exists to revert the database to a specific state that existed previously. This could be required if the entire database is lost (disaster) and it has to be reconstituted from nothing but the backups; or, "something bad" could have happened, either maliciously, or due to corruption, and it has to be restored in-place.

If you're having issues with getting this to work, we'll need more information about how you're going about doing it.
As mentioned in the comments, a database in FULL or BULK_LOGGED remains in SIMPLE mode internally until a full backup is taken, which gives a "base" from which the database can be restored. I've seen this called "pseudo-simple" mode.

The behaviour you saw with regards to the log file size was likely due to the above, although it depends on how the rows were INSERTed. In SIMPLE (and pseudo-simple) recovery, if the log file starts getting full, a CHECKPOINT will be initiated, which will internally free space in the log file as part of the process. So if the INSERTs were done in separate transactions, this would not grow the log. Doing all the INSERTs in a single transaction would grow the log, as the log records required to roll back the whole transaction cannot be cleared until the transaction commits or rolls back.

1 Not counting things like restoring in STANDBY mode, crash recovery, etc.


How can I find the shortest path between two currencies?

You could use this recursive query:
WITH RECURSIVE c AS (
      SELECT c1, c2 FROM currency_pair
   UNION
      SELECT c2, c1 FROM currency_pair
), curr_path AS (
      SELECT ARRAY[c1, c2] AS path
      FROM c
      WHERE c2 = 'usd'
   UNION ALL
      SELECT c.c1 || p.path
      FROM c
         JOIN curr_path AS p
            ON c.c2 = (p.path)[1]
      WHERE c.c1 <> ALL (p.path)
)
SELECT path
FROM curr_path
WHERE path[1] = 'eth'
ORDER BY cardinality(path)
LIMIT 1;

First, I calculate the symmetric closure.
Then I construct arrays of "conversion paths" by starting with the pairs we have, then prepending new currencies to arrays that can be converted to the first array element and do not yet appear in the array.
Once I have got all conversion chains that end up in 'usd', I pick the shortest chain that starts with 'eth'.


innoDB row locking - rows locked one by one or atomically

Atomically cannot always be possible.  Suppose you have a transaction that does an UPDATE, SELECTs some stuff, then decides on what to UPDATE next.  If it had not locked as it went along, the state could change, etc, etc.

Suppose you have two transactions proceeding in parallel.  Any of these might happen:

The locks taken out do not conflict.  In this case each transaction proceeds rapidly to a conclusion.
One xaction tries to lock some row that is already locked.  In this case, it waits to see if the other xaction will finish.  Either it will eventually release the lock allowing resumption of activity, or a "timeout" (see innodb_lock_wait_timeout) will cause the blocked xaction to abort.
A deadlock is discovered.  At this point one of the xactions is picked to be killed.

Any killed transaction will have any actions (update/delete/insert) undone.

Note: The above applies to InnoDB tables in a single server.  Galera (and other clustering), NDB, and MyISAM abide by different rules.


what is the difference between db_datareader and db_denydatawriter?

I am trying to assign a SQL Server database role to a user who just
  needs to be able to see data across all tables.

You should only make him a member of db_datareader.

By default, any newly created user has no permissions at all (unless you grant something to public or the corresponding login has some permissions at the server level: for example, view any definition at the server level implies view definition at database level once the login is mapped to a database).


If the databases are independent from each other, there is no problem if the internal, automatically generated primary keys are different in different databases.

If you want to exchange data, it could be an advantage to use the same keys. One way of doing this is to use the name itself as the primary key, but that means that these names cannot be changed.

Other than that, you'd need a central service that deals out id numbers for regions.


In the Article Properties, you can set the publication to not replicate deletes.
On the Article Properties page, just scroll down to Delete delivery format and change it to "Do not replicate..."
Note that if you're not replicating deletes, you risk a scenario where a row or update errors and causes issues. This will only happen if a deleted row has a value that is reused on the primary key or other unique constraint. If you're using an IDENTITY column for PK (and no other unique indexes on the replicated data), this would only happen only if the ID is reseeded or someone uses IDENTITY_INSERT. It's a more common problem to encounter when using a natural key. If you anticipate this being a problem, you can put instead of triggers on the replicated copy to convert inserts to upserts


Optimize queries from MySQL SP/Triggers/Functions

You have actually asked two questions:

"If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file"

Answer: it will not be logged in standard MySQL server. Do take a look at the Percona Server extension for the slow log: in particular look at the log_slow_sp_statements configuration variable. It does what you want: the slow queries within routines are logged. Note that this does not apply to triggers.
"Do we need to optimize each SP/Triggers/Functions/Events individually?"

Answer: well, of course. Each and every query needs to be as optimized as you can, depending on your requirements. A routine is not a "magic solution" that causes inner queries to run faster...


Table size in Azure SQL Managed Instance vs On Premise SQL Server

The Accelerated Database Recovery (ADR) feature introduced as part of SQL Server 2019 is enabled automatically on SQL Server Managed Instances.

Part of the way this feature is implemented is to include a 14 byte row versioning tag with each row.

14 bytes per row * 10,000,000 rows = 140,000,000 bytes = 133.5 MB of extra data to support ADR.

This explains the entirety of the difference in the approximate numbers provided (336 MB - 198 MB = 138 MB).

You should see the size of the SQL Server 2019 database match the Managed Instance size if you enable ADR prior to loading the 10,000,000 rows:

ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;


Try the query below.

Query:

with ord as(
    SELECT ParRecId
        , m = ROW_NUMBER() over(partition by ParRecId, Created order by RecId)
        , r = DENSE_RANK() over(partition by ParRecId order by Created desc, RecId)
        , Created, [Status]
    FROM @data
)
SELECT o1.ParRecId
    , [START] = o1.Created
    , [END] = CASE WHEN o2.Status <> 'CANCEL' THEN o2.Created END
    , [STATUS] = CASE WHEN o2.Status IS NULL OR o2.Status = 'CANCEL' THEN 'OPEN' ELSE 'CLOSED' END
FROM ord o1
LEFT JOIN ord o2
    ON o2.ParRecId = o1.ParRecId
        AND o2.r = o1.r-1
WHERE o1.[Status] = 'CANCEL'
    AND (o1.Created <> o2.Created OR o2.Created IS NULL)
ORDER BY o1.ParRecId, o1.Created ASC

Your data:

declare @data table(RecId int, ParRecId char(1), Created date, [Status] varchar(10));
INSERT INTO @data(RecId, ParRecId, Created, [Status]) values
    (17,'A', '2015-11-26', 'CANCEL')
    , (16, 'A', '2015-11-26', 'INPROGRESS')
    , (15, 'B', '2015-11-25', 'Active')
    , (14, 'B', '2015-11-25', 'CANCEL')
    , (13, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'E', '2015-11-25', 'CANCEL')
    , (11, 'D', '2015-11-25', 'ACTIVE')
    , (10, 'C', '2015-10-19', 'CANCEL')
    , (9, 'C', '2015-09-19', 'ACTIVE')
    , (8, 'C', '2015-09-12', 'CANCEL')
    , (7, 'C', '2015-09-05', 'ACTIVE')
    , (6, 'A', '2015-09-01', 'CANCEL')
    , (5, 'B', '2015-08-29', 'INPROGRESS')
    , (4, 'C', '2015-08-29', 'INPROGRESS')
    , (3, 'B', '2015-08-12', 'CANCEL')
    , (2, 'B', '2015-08-03', 'ACTIVE')
    , (1, 'A', '2015-06-01', 'ACTIVE');

Output:

ParRecId    START       END         STATUS
A           2015-09-01  NULL        OPEN
B           2015-08-12  2015-08-29  CLOSED
C           2015-09-12  2015-09-19  CLOSED
C           2015-10-19  NULL        OPEN
E           2015-11-25  NULL        OPEN


How do I restore a plain text postgres .backup file

run:

psql -U your_user_name your_db_name < your_dump_file


You should be able to just do the restore. Did you try it?

SQL Server will create the mdf and ldf files for you, with the same size they had when the backup was produced. And if the backup came from an express, then it shouldn't be possible to have a more than 10 GB size mdf - and there's no restriction on the ldf.

So just try the restore command (possibly using the GUI) and if it doesn't work, let us know what command you executed (you can script it in SSMS) and the error message(s) you got.


SOA + Fusion + Weblogic + Oracle 11g - STUCK threads

Should be addressed by patch 12573835.  Hopefully fixed for good in 12.x.

Edit - patch 12573835 is moderated & password-protected on support.oracle.com.  Its recommended only for BPM customers, which we are not, so no support has been given.  We have applied the January SOA critical patch and are still seeing the issue under load ("load" meaning 2 requests per minute, not much of a load but whatever, we can disagree).  So, there is no acceptable solution at the moment.


So your current situation is a full drive where you have two TempDB's that you want to modify the old one's size and the newly created one is having difficulties being removed?

  There are limitations for use of the DBCC SHRINKDATABASE command on
  the tempdb database. The target size for data and log files cannot be
  smaller than the size that is specified when the database was created
  or smaller than the last size that was explicitly set by using a
  file-size-changing operation such as ALTER DATABASE that uses the
  MODIFY FILE option or the command. Another limitation of BCC
  SHRINKDATABASE is the calculation of the target_percentage parameter
  and its dependency on the current space that is used.

Try running:

dbcc shrinkdatabase (tempddev, '70')

This should set your file to ~54GB which should be a safe size considering I am assuming your size or model was 30GB. Adjust the % accordingly.

Microsoft - How to shrink tempdb

Now for the second file, give this a try:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev2', EMPTYFILE)
GO

USE [tempdb]
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

If the issue persists, you'll have to restart SQL Server to remove the file as TempDB is in use.

Microsoft Forum - File in use

SQL Authority - Shrink and remove Tempdb file

One last note, you typed this:

  ALTER DATABASE tempdb
  ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData**tempdb3**.ndf', Size=30GB);

Your screenshot shows tempdb2, make sure your accurate with all the names and double check them. Just in case there is a mistype and that is causing your headaches.

There are some cases where running

DBCC FREEPROCCACHE

can help alleviate the pain and allow you to shrink the file.

Ozar - When TempDB won't shrink.

If you are still having issues, see what is out in your tempdb:

select * from tempdb.sys.all_objects
where is_ms_shipped = 0;

This will let you see your size of the tempdb files.

USE [tempdb]
SELECT
   [name]
   ,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2))                                 AS [Size]
   ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))            AS [Used]
   ,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2))      AS [Unused]
FROM [sys].[database_files]

From your post we see that you have files used in TempDB and there is space free. SQLSunday by Daniel Hutmacher has a good link on shrinking your DB with additional methods that were similar to Ozar, I would recommend to give this a try as well with caution.

There is a post by Mike Good - SQL Central on shrinking your TempDB by adjusting the snapshot level of TempDB with a rollback period to kill any connections that may have lingered on your db.


Mongodb - sharding + replicating an existing collection

Yes, you can do it just like that. Shards in the cluster don't need to be replica sets, but it is of course recommended. And later, it is possible to convert those nodes to single node replica sets and after that add more nodes to those replica sets. However, after making node as replica set, you need to do little "under the hood" updates, so that config server (and mongos) will know that shard(s) are now replica sets and not single nodes anymore.


Transactional Replication not sending all commands

Examine the output of sp_helpsubscriptionerrors. One issue I have seen is with DDL commands; they are treated differently in replication so if manual schema changes are being made to the Publisher, they can sometimes fail. There may also be specific DML commands failing on the subscriber due to transactional inconsistencies. If this is the case, you can use the distribution agent profile called "Continue On Data Consistency Errors" which will ignore duplicate key and other errors.

Latency could be causing the problem (according to MS, latency and throughput are the most important factors in transactional replication); you can track that with sp_posttracertoken. Both the Log Reader and Distribution agents have parameters which can be tweaked to compensate for that. Try lowering these values, particularly on the Log Reader.

Distribution Agent (from MSDN):

MaxDeliveredTransactions
Is the maximum number of push or pull transactions applied to Subscribers in one synchronization. A value of 0 indicates that the maximum is an infinite number of transactions. Other values can be used by Subscribers to shorten the duration of a synchronization being pulled from a Publisher.

CommitBatchThreshold
Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.

Log Reader Agent (from MSDN):

MaxCmdsInTran
Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.


SQLServer - Multiple PIVOT on same column

Group by Header and Subheader and take the sum like below
SELECT Header, SubHeader,
  Sum(Regular), Sum(Irregular),
  Sum(FRegular), Sum(FIrregular),
  Sum(MrRegular), Sum(MrIrregular)
  FROM (SELECT Header,
               SubHeader,
               Type, 'F'+Type AS FType,
               'Mr'+Type as MType,
               'Ap'+Type AType,
               'Ma'+Type as May,
               January], [February], [March]
  from [CashFlow].[dbo].[Details] ) p

  PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
  PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
  PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT
Group by Header, SubHeader


Converting dbo file from windows to linux

You said you have reloaded the file into a MySQL database.
A simple way is:

use mysqldump to backup the the data into a file
transfer the file to the linux system
use command source filename.sql to load the data into the MySQL database on the linux server.


Recover Oracle Database until cancel and noresetlogs

If you want to cancel, you have no way of using noresetlogs. You have create a new incarnation of your database.
Ps: I don't know what your problem was with switching to sqlplus, but you can execute sql statements (alter database.....) in rman .


What should we call a table that stores changes to another table?

In our company, we use to suffix the table name with _history. So, for the persons table you might have a table named persons_history.