How to limit the number of sessions for a specific login

If all programs are using the same credentials in the connection string, you might be able to achieve what you're after by using a Logon Trigger to limit the number of sessions for a specific login. For example, in the following code (taken from the documentation), the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;


Look into using Schemas. Each User can have (and own) their own Schema for their objects that they have full control over. Then you can grant SELECT access for them to other Schemas.

When doing this, you need to make sure that both of the following conditions are met:

Each Schema "owner" (i.e. AUTHORIZATION) is for the intended User
Each User has their default Schema set to their Schema

As the schema owner, each user will have CONTROL permission over the schema and all objects within. SELECT permission can then be granted on other schemas as needed:

CREATE SCHEMA User1 AUTHORIZATION User1 ;
GO
CREATE SCHEMA User2 AUTHORIZATION User2;
GO
CREATE SCHEMA User3 AUTHORIZATION User3;
GO
ALTER USER User1 WITH DEFAULT_SCHEMA = User1;
ALTER USER User2 WITH DEFAULT_SCHEMA = User2;
ALTER USER User3 WITH DEFAULT_SCHEMA = User3;
GO
GRANT SELECT ON SCHEMA::User2 TO User1;
GRANT SELECT ON SCHEMA::User3 TO User1;
GRANT SELECT ON SCHEMA::User1 TO User2;
GRANT SELECT ON SCHEMA::User3 TO User2;
GRANT SELECT ON SCHEMA::User1 TO User3;
GRANT SELECT ON SCHEMA::User2 TO User3;
GO

Please see:

CREATE SCHEMA
Security: Securables
Schemas for application boundaries (written about SQL Data Warehouse, but pertains to your scenario)
Ownership and User-Schema Separation in SQL Server


Parse and compile is the time it takes to validate syntax and object validity, then compile an execution plan. Elapsed time is how long it takes the query to execute and finish returning rows to the client (SSMS). If you're returning a lot of rows, the query itself may be done long before rows are done rendering in SSMS.


What are the minimum persmission required to restore mysql dump

For normal database restore operations, the MySQL database user (which you pass as an argument into the mysqldump command) will needs privileges related to:

STRUCTURE (Schema)
DATA manipulation

For common STRUCTURE operations the user typically will need:

DROP
CREATE
ALTER
TRIGGER

DATA privileges typically should be

SELECT, INSERT, UPDATE and DELETE.

Importantly database structure administration privelege GRANT can be revoked.
By starting with this limited set and then adding privileges on an as needed basis you are improving the containment policies.


SQL Server Instance Root Directory & Shared Feature Directory on Partition with 64k Allocation Unit Size?

No it does not make any practical difference for the binaries. Unlike the data files, the binaries are largely loaded to memory on start up and not read from disk again during normal operation.


With a sharded cluster, you need to insert data via a mongos process. A mongos process acts as a query router: it directs queries/updates to the appropriate shard(s) based on the current cluster configuration and metadata.  Typically you deploy one mongos per application server.

From your driver point of view, you can update your connect string and replace the address of your standalone server or replica set seed lists with one or more mongos servers to use.

For more information, you're best starting with the Sharding Introduction in the MongoDB manual.


Query SQL Server Alert System Mail Session Settings

I use this code to validate Database Mail profile configuration:
/*
    Confirm the Database Mail account and profile is configured correctly
*/
DECLARE @ProfileName        varchar(255);
DECLARE @AccountName        varchar(255);
DECLARE @AccountFromAddress varchar(255);
DECLARE @AccountReplyTo     varchar(255);
DECLARE @SMTPServer         varchar(255);
DECLARE @SMTPPort           varchar(255);
DECLARE @DatabaseMail       varchar(255);

SELECT
      @ProfileName          = smp.name
    , @AccountName          = sma.name
    , @AccountFromAddress   = sma.email_address
    , @AccountReplyTo       = sma.replyto_address
    , @SMTPServer           = sms.servername
    , @SMTPPort             = sms.port
FROM msdb.dbo.sysmail_account sma
    INNER JOIN msdb.dbo.sysmail_profileaccount smpa
        ON sma.account_id = smpa.account_id
    INNER JOIN msdb.dbo.sysmail_profile smp
        ON smpa.profile_id = smp.profile_id
    INNER JOIN msdb.dbo.sysmail_server sms
        ON sma.account_id = sms.account_id;

/*
    Confirm SQL Server Agent is configured to use Database Mail correctly
*/
DECLARE @res TABLE
(
      [Value] varchar(255)
    , [Data] varchar(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail';
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile';
IF (
        SELECT COUNT_BIG(*)
        FROM @res r
        WHERE r.[Value] = 'UseDatabaseMail' AND r.[Data] = 1
    ) = 1 AND
    (
        SELECT COUNT_BIG(*)
        FROM @res r
        WHERE r.[Value] = 'DatabaseMailProfile' AND r.[Data] IS NOT NULL
    ) = 1
    SET @DatabaseMail = 'Configured'
ELSE
    SET @DatabaseMail = 'Not Configured';

/*
    Combine results into a single row
*/
SELECT
      ServerName            = @@SERVERNAME
    , ProfileName           = @ProfileName
    , AccountName           = @AccountName
    , AccountFromAddress    = @AccountFromAddress
    , AccountReplyTo        = @AccountReplyTo
    , SMTPServer            = @SMTPServer
    , SMTPPort              = @SMTPPort
    , AgentDatabaseMail     = @DatabaseMail;

The output looks like:
╔══════════════╦═════════════╦═════════════╦═══════════════════════╦════════════════════════╦════════════════════════╦══════════╦═══════════════════╗
║  ServerName  ║ ProfileName ║ AccountName ║  AccountFromAddress   ║     AccountReplyTo     ║       SMTPServer       ║ SMTPPort ║ AgentDatabaseMail ║
╠══════════════╬═════════════╬═════════════╬═══════════════════════╬════════════════════════╬════════════════════════╬══════════╬═══════════════════╣
║ servername   ║ DBA         ║ SMTP        ║ sqlserver@company.com ║ dbasupport@company.com ║ mailserver.company.com ║       25 ║ Configured        ║
╚══════════════╩═════════════╩═════════════╩═══════════════════════╩════════════════════════╩════════════════════════╩══════════╩═══════════════════╝
Various parameters used by SQL Server Agent are stored in the registry.  I use this code to get those details:
DECLARE @res TABLE
(
      [Value] varchar(255)
    , [Data] varchar(255)
);

INSERT INTO @res
EXEC master.dbo.xp_instance_regread
         @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'Software\Microsoft\MSSQLServer\SQLServerAgent'
       , @value_name   = N'DatabaseMailProfile';

INSERT INTO @res
EXEC sys.xp_instance_regread
         @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
       , @value_name   = N'UseDatabaseMail'

SELECT r.[Value]
    , r.[Data]
FROM @res r;

The output:
╔═════════════════════╦═══════════════════╗
║        Value        ║       Data        ║
╠═════════════════════╬═══════════════════╣
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ UseDatabaseMail     ║ 1                 ║
╚═════════════════════╩═══════════════════╝


Management Studio 2012 Backward Compatibility

I have had no problems with it.
No problems there either. Intellisense works against 2008 (not 2005 of course).


PostgreSQL isn't listen on 5432 in Windows 7

i have the same problem. I disabled all my third party antiviruses and firewalls and reinstall PostgreSQL again and its now working fine... :)


I would not be worried about a circular reference: they are common in data modelling (any tree/forest/graph structure for instance) so your tools should cope with it.

The "property of the contact" method is probably what I would default to, as it is the sort of thing where the rules are likely to change (if a company decides two people should get all important comms they might want to list two preferred contacts) and this method feels more flexible in that respect.

Other than that, from a design point of view as your rules currently stand (at least the ones that you've mentioned) either works as well from an integrity point of view as both almost enforce the "at most one per company rule" (though "property of a contact" requires an extra constraint/index to do so) but in either case you need to wrap custom some logic either as triggers or functions in other layers of your application logic, to enforce the rest of the rule (enforcing that there should be at least on preferred contact if any contacts exist, dealing with people who move between companies if the model considers that (rather than treating such a move as a delete and a new person being added), and so forth).


What is the best way to analyze free space in a postgres database and the grow of usage?

Postgres' tablespace concept is completely different to Oracle's. Postgres doesn't use container files the way Oracle does. Each table is stored in one or more individual files. A tablespace is essentially just a directory where files are stored. It is not container file like in Oracle.

If a table gets dropped or truncated the space is immediately released on the file system level.

For a single table you could measure the size of the file(s) on disk and the actual size of the data which can deviate substantially depending on the write pattern of the application. This is caused by Postgres' implementation of MVCC.

The Postgres Wiki has several queries to investigate that:
https://wiki.postgresql.org/wiki/Show_database_bloat


Should I use data type SERIAL for table id columns?

The only real concern would be size.

If a table is really small, why bloat it ? For example, if you have a table will never surpass 255 rows, use TINYINT UNSIGNED for id. No need to bloat the table's column up to 8 times bigger. This would also apply to the primary key.

After loading a table with data, you should run this

SELECT id FROM tablename PROCEDURE ANALYSE();

For any table < 4,294,967,296 rows, PROCEDURE ANALYSE() will never recommend a BIGINT. In terms of byte width, smaller numeric keys are always faster to process than larger numeric keys.

What will be further victimized would be indexes. Why ?

In the Clustered Index (a.k.a. gen_clust_index), row data and BTREE for Primary Key occupy the same space
InnoDB has a fixed size for an InnoDB Page

In light of these two things, the larger the Primary Key, the more space is needed and the more likely extra InnoDB pages are needed.

Therefore, for a populated table, my suggestion would be to let PROCEDURE ANALYSE() tell the correct dataytpe. For an empty table, try to forecast the number of value to expect and this set the data type to the following:

id < POWER(256,1) (256), TINYINT UNSIGNED
id < POWER(256,2) (65536), SMALLINT UNSIGNED
id < POWER(256,3) (16777216), MEDIUMINT UNSIGNED
id < POWER(256,4) (4294967296), INT UNSIGNED


Unable to delete a SQL Server 2012 publication

Looks like you have an object owned by a user that the service account can't validate.  you can figure out what object it is, and change the owner of the object to sa.  If it's the database, run this:

exec sp_changedbowner 'sa'


SQL Server AD groups membership seems cached - how to flush to test?

AG Groups are only updated when the user gets a new token from the domain controller.  The only way to speed this process up that I know of is to log off the machine and log back in.

I would recommend using a Virtual Machine to do this so that you don't want to kill yourself.


Use of SET INTEGRITY for DETACH PARTITION

The COMMIT is fairly obvious; it's committing the effects of the prior ALTER TABLE DETACH... statement (and any statements prior to that and the preceding COMMIT/ROLLBACK - transactions are implicitly started in DB2). I assume there's a bit more to that DETACH statement but that it's omitted for brevity.

The SET INTEGRITY statement (I'd strongly recommend reading that page, especially the Notes section) is more complex. In this case it's bringing the table out of check-pending status by telling the database engine that the table's data still conforms to all the constraints on the table, but that the engine shouldn't bother checking that itself. This is as opposed to SET INTEGRITY ... IMMEDIATE CHECKED in which the database engine would check the data is correct.

The table has presumably been left in check-pending state by some earlier operation (I don't think detaching partitions causes check-pending, although attaching them certainly does - see ALTER TABLE for more information).

As the Notes section of the SET INTEGRITY reference page states:

  The fact that the table was taken out of the set integrity pending
  state without performing the required integrity processing will be
  recorded in the catalog (the respective byte in the CONST_CHECKED
  column in the SYSCAT.TABLES view will be set to 'U'). This indicates
  that the user has assumed responsibility for data integrity with
  respect to the specific constraints...

I'd strongly recommend reading the rest of the Notes section for more info on using IMMEDIATE UNCHECKED.


Is SQL Server 2012 predicate precedence overridden by all-at-once concept?

I believe I understand what the authors are trying to teach now (thanks to everyone's comments). I believe they are stating that if I am writing SQL that contains logic that depends on short-circuiting then the logic might be flawed due to the all-at-once concept which will evaluate predicates based on cost and not by order of precedence. So, the answer to this question is yes. The all-at-once concept does override predicate precedence.

I am reading the book right now in preparation for the 70-461 exam and I wanted to confirm with other writers of SQL if I truly did not have control over the order in which the predicates of my SQL statements are evaluated. It turns out that I do not. A CASE does not even guarantee the order according to this Connect item: Aggregates Don't Follow the Semantics Of CASE.

The Microsoft Technet article All-at-Once Operations in T-SQL by Saeid Hasani provides a more detailed explanation of the all-at-once operations concept. The Technet article does not state that the cost of a predicate is the reason why some predicates are evaluated before others which is the reason given in the book Querying Microsoft SQL Server 2012.


As previously mentioned by @Jack Douglas, Oracle Application Server (a J2EE container) is installed. You may want to do a tcpdump to see what applications are running on conflicting ports. You can find a list of ports used by Database Control here: Managing Oracle Port Numbers.

Some ports used by Enterprise Manager Database Control:

1158 - Http Port for Oracle Enterprise Manager Console.
3938 - Oracle Management Agent.
5520 - RMI port for Enterprise Manager Console.
5540 - JMS Port for Enterprise Manager Console.


Postgres foreign key with ON DELETE SET NULL for only some of its columns

Currently there is no way for the CASCADE action to set only some of the columns to NULL. So the only options I see would be:

have all the 4 columns defined as NULL and use ON DELETE SET NULL action. Seems like this is not an option in your case.
add another table to hold this relationship (where all the columns are not nullable) and use ON DELETE CASCADE.
a trigger.
create a procedure (function in Postgres) to handle deletions of table c. The function would first modify the columns (c,d) in table a and then delete from table c. Then you have to make sure that all users/applications use this function and do not directly delete from table c.


Why is my transaction file growing when db is in simple mode and there are no waiting transactions

Your file is probably large but empty.

In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty.

You probably had a large process that grew the file to 160GB. You should probably shrink your file and then resize it to 160GB again manually (to reduce fragmentation), and then monitor how full it is using PerfMon counters.


How to show all users of all databases in mongodb?

You can find information about all users in all databases in system.users collection of admin database. See MongoDB documentation for system.users collection.


Automatically restore SQL Server database from file from another server

Since all the details on backups are maintained in the msdb database, you should just extract the backup file name from the source server.

You could create a linked server from your Dev Server to access the Production Server's msdb database. Or you can use OPENQUERY to query the same data.  (OPENQUERY may be faster since the query is actually being run on the Production Server.)

For example:

SELECT * from OPENQUERY([LinkToPRD],
     'EXEC database.dbo.ExecDailyRestore');

This shows running a stored procedure with no parameters, which might suit your daily restore plan.

If you search for "auto generate sql server database restore scripts" you will find many scripts.  An example from Paul Brewer is sp_RestoreGene that you might use as is or as a basis to create your own ExecDailyRestore stored procedure.

https://paulbrewer.wordpress.com/sp_restoregene/

Here are the parameters supported by sp_restoregene:

    @Database SYSNAME = NULL,
    @TargetDatabase SYSNAME = NULL,
    @WithMoveDataFiles VARCHAR(2000) = NULL,
    @WithMoveLogFile  VARCHAR(2000) = NULL,
    @FromFileFullUNC VARCHAR(2000) = NULL,
    @FromFileDiffUNC VARCHAR(2000) = NULL,
    @FromFileLogUNC VARCHAR(2000) = NULL,
    @StopAt DATETIME = NULL,
    @StandbyMode BIT = 0,
    @IncludeSystemDBs BIT = 0,
    @WithRecovery BIT = 0,
    @WithCHECKDB BIT = 0,
    @WithReplace BIT = 0,
    @UseDefaultDatabaseBackupPath BIT = 0,
    @Log_Reference VARCHAR (250) = NULL,
    @LogShippingVariableDeclare BIT = 1,
    @LogShippingStartTime DATETIME = NULL,
    @LogShippingLastLSN VARCHAR(25) = NULL

And here is a sample script:

RESTORE DATABASE db_workspace
FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE,
FILE = 1,CHECKSUM,NORECOVERY, STATS=10
, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf'
, MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'


Using an intermediate table is the proper way to do this.  With proper indexes and good maintenance it won't hurt performance much at all.

Avoid the multi-column solution (refid1, refid2 etc.) - that's repeating groups and not properly normalised.  Plus your queries will degenerate into a dog's breakfast of OR clauses and CASE statements, which will hurt performance.

You may get some benefit from subtyping your data rows so most use your current arrangement but those with multiple reference use a different one.  Your queries will UNION ALL the two sets to get the total.  I really doubt the overhead from this will compensate for the mostly-redundant join to data_reference.

The subtyped schema would look like this:

create table data (
    id int identity not null,
    ref int not null,       -- set to a standard value for rows with >1 reference.
    is_single_reference bit not null
);

create table reference (
    refid int not null
);

create table data_reference (
     dataid int not null,
     refid int not null
);

The query would be something like:

select
    <whatever>
from data as d
inner join reference as r
    on d.ref = r.refid
where d.is_single_reference = 1

union all

select
    <whatever>
from data as d
inner join data_reference as dr
    on dr.dataid= d.id
inner join reference as r
    on r.refid = dr.refid
where d.is_single_reference = 0;

This is fairly ugly.  You'd be lucky to avoid two scans of data.  Ideally you'd want filtered indexes but IIRC these aren't availble until SQL Server 2008.  The applicaiton would have to know how many reference rows there were per data, set flags accordingly and maintain those if the number changed.  Just use the n:m approach.


This was due to a version mismatch between the license file and the ODBC driver. The licence .lic file has ProductVersion=10.5 in text in the body. Using the matching driver version has fixed the issue for me.


Ok so this isn't perfect put it works well enough for me.
Note this is a development database where we're trying to make life easier for the developers rather than a production database.

What i did was create a TablesOnly user that people must login with when they're generating entities.
I had 2 scenarios that I had to deal with - Current tables and tables that will be created in the future.

First I ran this cursor to allocate the current tables to my 'TablesOnly' user

DECLARE @username VARCHAR(50) -- username
DECLARE @tablename VARCHAR(50) -- database name

SELECT @username = 'tablesOnly'

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.tables

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

        EXECUTE ('GRANT SELECT ON '+@tablename+' TO '+@username)
        EXECUTE ('GRANT VIEW DEFINITION ON '+@tablename+' TO '+@username)

        FETCH NEXT FROM db_cursor INTO @tablename
END

CLOSE db_cursor
DEALLOCATE db_cursor

Then I created this trigger on a table create that automatically allocated the permissions that I need to the user

CREATE TRIGGER UpdateTablesOnlyUser
ON DATABASE

    FOR CREATE_TABLE
    AS
        set nocount on

        DECLARE @data XML, @ObjectName sysname
        set @data = EVENTDATA()
        SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

        PRINT 'GRANT SELECT ON '+@objectName+' TO tablesOnly'
        PRINT 'GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly'
        EXECUTE ('GRANT SELECT ON '+@objectName+' TO tablesOnly')
        EXECUTE ('GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly')


How to keep history for a one-to-many relationship?

Suppose we have 3 tables - one for employees, one for tasks, and one for "task_assignments", like so:

create table employees(
  employee_id number primary key
, employee_name varchar2(64)
, additional_information varchar2(64)
);

create table tasks(
  task_id number primary key
, task_details varchar2(64)
);

create table task_assignments(
  employee_id number references employees(employee_id)
, task_id number references tasks(task_id)
, start_date date not null
, end_date date
, unique (employee_id, start_date)
);

The task_assignments table stores "historical" data.  We assume that each employee can only work on one task at a time (as stated in your question).  The task has a start_date (or time) and end_date.  We can use a trigger for preventing INSERTs if an employee already has an "open" task (ie the end_date of the task is null).  NOTE: the example trigger does not cover UPDATEs.

create or replace trigger one_active_task_only
before insert on task_assignments
for each row
declare
  open_tasks number := 0 ;
begin
  select count(*) into open_tasks
  from task_assignments
  where employee_id = :new.employee_id
    and end_date is null ;

  if open_tasks >= 1 then
    raise_application_error (-20500,'This employee already has an open task');
  end if;
end one_active_task_only;
/

alter trigger one_active_task_only enable;

Testing and more details etc: see dbfiddle


How can I prevent SSMS from re-writing my code when saving a view?

Don't use the SSMS wizards.

just type your CREATE VIEW dbo.Foo AS SELECT ... statement into a new query window and execute it.

This will stop it rearranging the query syntax to semantically equivalent but worse performing alternatives.


Group by unique pairs with no multiplicity

With full_Group_on you can use for example this, if a "_" is not present  your columns
CREATE VIEW EFFICIENCY AS
SELECT SUBSTRING_INDEX(area_driver, '_', 1) as AreaID,SUBSTRING_INDEX(area_driver, '_', -1) AS DriverID, AvgPrice
 FROM (SELECT CONCAT(AreaID,"_",Bookings.DriverID) area_driver, AVERAGE(Bookings.Fare/Bookings.Miles) AvgPrice
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY  CONCAT(AreaID,"_",Bookings.DriverID)) f1


MySQL Lock Wait Time Out Exceeded upon Delete

Do you have INDEX(COMPONENT_NAME, UPDATE_DATE) with the columns in that order?  (Please provide SHOW CREATE TABLE.)

What else was running at the same time?  (SHOW PROCESSLIST before the DELETE times out.)

Let's see that other query; perhaps it can be optimized to run faster.

What is the value of autocommit?  Are you using BEGIN...COMMIT?


Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart?

Query plans? Yes.
Table/index data? Yes.
Statistics? No.

(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created unless there was also some event or condition to trigger that (there are several).)

If you stop the service while a transaction is rolling back, you likely haven't accomplished anything, since rollback may just start again from where it left off (or it may have to start over completely, depending on the type of activity that is getting rolled back).

If you restarted the service because you got impatient waiting for a rollback to complete, that's probably an expensive lesson you'll only have to do one or two times before you stop trying. See:

Why did a SQL Server Restart roll-back seemingly committed transactions?
KILLED/ROLLBACK query doesn't stop running
Rollback taking forever
SQL Server 2008 R2 - Service shutdown - what with running stored procedures?
https://dba.stackexchange.com/a/160293/1186


How to propagate a grandparent relationship throughout a tree?

@Tim you have come to the right place.

It just so happens that I answered a question like this ( Find highest level of a hierarchical field: with vs without CTEs  ) back in Oct 24, 2011.

I wrote Stored Procedures that traverse a table with id and parent fields. I also included sample data.

However, you may not have to resort to the Stored Procedures.

Here is what I mean: Let's start with the sample data

USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;

To reach any grandparent, you could just perform a JOIN of the pctable against itself:

SELECT P.id,GP.parent_id FROM pctable P
INNER JOIN pctable GP ON P.parent_id = GP.id;

To reach any great-grandparent, you could just perform a JOIN of the pctable against itself twice:

SELECT P.id,GGP.parent_id FROM pctable P
INNER JOIN pctable GP ON P.parent_id = GP.id;
INNER JOIN pctable GGP ON GP.parent_id = GGP.id;

Since your depth is no more than three, doing these JOINs should be quite enough.


Deleting all records on a table except the latest 10 for each user


SQL Server 2012: stored procedures permissions

You just need to deny EXECUTE permissions on any object that you don't want the app login to see.  Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as that login is concerned.

DENY EXECUTE ON SchemaName.ProcName TO [AppLogin];

Once the DENY has been run, the following won't return anything for that login:

SELECT OBJECT_ID('SchemaName.ProcName');

SELECT *
FROM   sys.objects so
WHERE  so.name = 'ProcName';


how to insert the remote table data into another table in my local computer in mysql?

Try this from your local machine:

mysqldump -u username -p password -h remote_host --databases remote_database --tables remote_table --where="id=757575" | mysql local_database -u username -p password


FastTrack Data Warehouse -E startup parameter

The option only takes effect during bulk loads to tables, so you would need to re-load your data which seems a bit pointless, or accept it's only relevant for new data loads.  The KB Article is fairly clear:

  The -E option must be used before the original data load because it does not affect existing data, only new allocations.

I would also say this is one of those 'Formula 1' tuning options that I would not enable unless I had already proved it had a sufficiently positive effect using a representative test of my actual business data and did not have a negative impact on other key workloads.  The recommendation also comes with a caveat:

  ...data warehouse applications that have a limited number of users running index or data scans.

Is this you?

NB The KB Article also ends with a strongly worded warning:

  Because the -E option may bring performance benefits only under the specific circumstances described earlier, we do not recommend that you use this option on a regular basis. Running SQL Server with the -E option set on may decrease the performance of multiuser workloads with frequent allocations because of increased CPU consumption.


Should Data and Indexes be split in separate file groups and files

Improvement (or not) will depend on your storage system. Multiple files can help performance if it increases IOPS and bandwidth with additional HBAs and drives. Multiple filegroups allows more granular placement of database objects. That will generally help in specialized use cases, such as separating sequential and random IO patterns on spinning media.

That said, I don't expect storage tuning to address the symptoms of "massive deadlocks and poor performance". Query and index tuning is the first thing I suggest you look at.


How to find when change tracking was enabled?

Run the following query in the context of the database in question in order to get the Change Tracking table names used on the database. (Query via Kendra Little on BrentOzar.com)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
    select sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
    sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
    it.object_id = ps1. object_id
    and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
    sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
    sot2.object_id = ps2. object_id
    and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210);
GO

Find the create_date for the change_tracking table(s) found in the first result set by querying sys.internal_tables in the context of the change tracking database.

SELECT * FROM sys.internal_tables;


AWS RDS MyISAM tables from mysql and information_schema and good snapshots

The MyISAM tables in the mysql schema are fully supported in RDS snapshots.  It's the MyISAM tables elsewhere that are potentially problematic.

Never alter the tables in the mysql schema.  That will leave you with a server that may never start up again.

  Important

  Do not convert MySQL system tables in the mysql database from MyISAM to the InnoDB type. This is an unsupported operation.

  https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html

The exception for the tables in mysql is because RDS takes care to flush and lock those during a snapshot, so they come out okay on the other side.  This isn't documented but can be observed.

The tables in information_schema are not real tables.  Leave those alone as well.

The data in MEMORY tables is transient, and not persisted to disk, so they won't survive a snapshot or even a restart.  Their definitions should be okay but RDS is really intended for use only with InnoDB.  (The FEDERATED storage engine is disabled.)


The link that you have provided is related to Oracle Application not the database. The solution being explained on that website is the way to audit changes from application side.

In Oracle database, we don't have such tables to hold changes.

  Is audit trail tables still the way to go or is this built into unified audit?

No, in unified auditing there is only one view called UNIFIED_AUDIT_TRAIL to display all the data related to the audit.

  Can we enable it through some sql statements, and if so, is there a guide for that? Or do we need to enable it through a tool?

Again, the link that you have referenced is related to Oracle Application not the Oracle database and there is way to do it in it. Audit Installations Window.

Better approach, from database side, is using Oracle Flashback Data Archive.

This article from Oracle Magazine explains why Flashback Data Archive is a better way as compared to manual auditing using audit tables and triggers to track changes.

A Fresh Look at Auditing Row Changes


Are there any in-memory NoSQL databases?

The issue with storing your database in memory is if you have any sort of memoery issue or server has to be restarted or anything of that issue all your memory will get flushed.

That is the reason people don't store their database in memory.

Now, there are caching tools which are in-memory and can work as a very simple database like memcached. That may meet your needs. If you look in to tmpfs and ramfs you can create a folder that exists in memory and move your files in there normally.

So, if you are working with MongoDB, mysql or whatever you work with, you can have the data folder live in the RAM folder. This will give the database super fast read and writes. Everything will be really fast. You will be limited to how much RAM you have minus the size of your OS and other things running.

Also, just be careful: MongoDB likes to store writes in memory until the disk has a chance to write, it so you may want to turn that feature off because it will be the same speed.

My recommendation is to work with memcached and then mix it with a normal database that lives on disk. The concept is done with PHP sessions on some systems.

http://mickeyben.com/2009/12/30/using-nginx-as-a-load-balancer.html

The basic way it works is, if your record is found in memcached, then it will not check the database. If it is not found, then do three(3) things:

check the db
send the data to memcached
send the data to calling function

:)


Are there security-issues with a read-only-account on Oracle-DBs?

Yes, there are.

every account puts load on the server and has the potential to bring it down by overloading it with silly queries or sometimes just bad luck.
more and more important: privacy issues. When a company leaks data they can get serious fines.
company secrets can be stored in the database. Leaking them does not help their case.

control and audits are getting more and more important.
A solution can be to have a copy system in place that generates an adhoc query database that is de-personified enough so privacy concerns can be remedied. It also alleviates the production host from the load caused by the research queries.
A controlled reporting tool can also be helpful, I just don't see how that mitigates privacy concerns other than having predefined reports that just don't show personal data.


Could too many idle connections affect PostgreSQL 9.2 performance?

I seriously doubt idle connections are your issue.  PostgreSQL is very capable of handling idle sessions since they take almost no resources.  Active sessions on the other hand are much more likely the cause of slowness.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections for reference on the number of database connections.


migrate MySQL database with MySQL Workbench CE failed on table with blob data

You can see max_allowed_packet to 1G in your session

SET max_allowed_packet = 1073741824;

You can do so because MySQL will not immediately allocate it. A packet can grow. Please note how it mentions this in the MySQL Documentation:

  The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

  You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

You should go ahead and add max_allowed_packet to my.cnf

[mysqld]
max_allowed_packet=1G

That way it is globally available for all incoming connections when you restart mysql. You don't have to restart mysql right now. You can just run:

SET GLOBAL max_allowed_packet = 1073741824;

Give it a Try !!!


I'm not sure either of these are really great options.  Here's my thoughts:

  pk|A ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

With this scenario you have redundant data.  Unless your individual PK tables are just staging tables, I think you could run into some serious issues here, especially with different consumers pulling data from different tables.  I wouldn't chance that whatsoever with this design.

Not to mention that if PK:A, PK:B, PK:C, and PK:D are all 1:1 relationships, I don't see any normalization benefit out of this either.

  T (PK,A,B,C,D) DATA SOMETHING like (1,a1,NULL,NULL,NULL) run update statement for all columns B,C,D sequentially and get all the data T(1,a1,b1,c1,d1)

I'm not sure I like this design either, although I'd put it at a higher preference then the former.  The reason behind this is because what happens if business dictates that columns A, B, C, and/or D really aren't NULLable.  Now you can run into a big problem of data conflicting with business constraints.

I think the real solution here may be to take a step back and analyze your data modification.  Instead of having a step-based data insert, is there no way to do a single insert of all the required and necessary data into this table?  In the application, retrieve all of the required data before each item's insert.  That way you won't be defying any business rules, having redundant data, or additional operation overhead of the designs above.

In other words, if data columns A, B, and C are required, then you shouldn't be pushing that data out to this table until you have it.  Without all of those required columns you wouldn't have compliant data, and it shouldn't live in that table until it is.  And if D is nullable and optional data, then sure that's fine if it doesn't appear until a later time with a different DML operation.


How to prevent duplicate or similar data from getting processed?

Try adding And t1.ID > t2.ID to the where clause , it will make sure that the join happens once .
To the following query

SELECT t1.place_name, t1.region_name, t2.place_name, t2.region_name, loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance
FROM @postal1 as t1 CROSS JOIN @postal2 as t2
WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude


Why can I drop table when logged in as not owner?

Superusers are always exempt from lowly permission checks. The documentation:

superuser status
A database superuser bypasses all permission checks, except the right
to log in. This is a dangerous privilege and should not be used carelessly; ...

To see whether your current user is a superuser:
SHOW IS_SUPERUSER;

To list all superuser roles:
SELECT rolname FROM pg_roles WHERE rolsuper;

And the schema owner can do it, too. The documentation for DROP TABLE:

Only the table owner, the schema owner, and superuser can drop a table.

To list schema-owners:
SELECT n.nspname AS schema_name, pg_get_userbyid(n.nspowner) AS owner
FROM   pg_namespace n
WHERE  n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
ORDER  BY 1;

About the same as \dn in psql.
And who are you?
SELECT current_user, session_user;


On large tables you may be able to speed it up.

SELECT user_id, user_name
FROM adquisicion_dispositivo AS a1
WHERE acquisition IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM adquisicion_dispositivo AS a2
  WHERE acquisition IS NOT NULL
  AND a1.user_id = a2.user_id
);

You need to show your table with \d adquisicion_dispositivo. You may need an index, @joanolo's suggestion is good switch the order though I would switch the order,

CREATE INDEX ON adquisicion_dispositivo (user_id, acquisition);


Unable to view MySQL db stored procedure

use following command:
GRANT EXECUTE ON dbname.* TO 'user'@'%';


Same query way faster with distinct than without distinct

From the way I read your execution plans, your DISTINCT query is selecting columns only from vSMS_R_System, so the optimizer chooses to read that data first (retrieving the DISTINCT values used in your SELECT) and then uses a NESTED LOOP to join up against the other tables.

The non-DISTINCT query forces the optimizer to assume that many more rows might need to be read from vSMS_R_System, so it chooses a MERGE JOIN to scan the other tables and then a NESTED LOOP to match up against vSMS_R_System.


Determine which trace flags are set as a startup parameter vs. enabled globally?

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup.

select * from sys.dm_server_registry
where
cast(value_data  as varchar(max)) like '%-T%'

Just incase if you or future visitors need the output in one row then, you can use below T-SQL :

select distinct @@SERVERNAME as SERVER_NAME
                ,replace(STUFF((
                        select ' ' + cast(value_data as varchar(max))
                        from sys.dm_server_registry
                        where cast(value_data as varchar(max)) like '%-T%'
                        for xml PATH('') -- select it as XML
                    ), 1, 1, ' ')        -- This will remove the first character ";" from the result
        , '&#x00', '')                   -- This will remove the "&#x00" else you will get  -traceFlag&#x00
    as TRACE_FLAGS_ENABLED_AT_STARTUP
from sys.dm_server_registry
where cast(value_data as varchar(max)) like '%-T%'


MariaDB ran mysql_secure_installation, but seeing remote login attemps in logs

Yes, the server would still be accepting remote connections. And yes, it probably did what it was supposed to do. mysql_secure_installation doesn't set bind-address=127.0.0.1 neither in MariaDB nor in MySQL. It does however allow you to remove root accounts that are accessible from outside of localhost, as you have already done.

If you don't have any database users with host != localhost, then remote login shouldn't be possible. You can of course also block the MariaDB server port in your firewall.


SQL Server execution plan using a view, but no view is inside the stored procedure

So it's an indexed view - SQL Server can automatically choose to use an indexed view if the base table is referenced and the indexed view can (better) satisfy the query.

From MSDN (emphasis mine):

  A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view [...]

Also, as you've seen, the missing indexes algorithm might nudge you and say, "Hey Jose, if you added this non-clustered index to the indexed view, I might have been able to use the index on the view instead, which would have been more efficient for this query." Again, this correlation can happen even if the view isn't explicitly mentioned in the query.

Generally, unless you are throwing indexes on all of your views because you believe they are some magic performance silver bullet, accessing the indexed view will be better than accessing the base table, because - again generally - the indexed view should be a lot smaller than the table on which it is based.


Mysql Workbench crashing when connecting

Looks like a known issue with it not handling an exception error properly and also the wrong version:
https://bugs.mysql.com/bug.php?id=79364

Description:
WB 6.3.5 (and also older release like 6.1.7) can connect to MariaDB.

> How to repeat:
> 1. Download WB 6.3.5 CE 64 bit (32 bit has the same issue)
> 2. Download MariaDB 10.0.22 64 bit windows
> 3. Start MariaDB using mysqld --console
> 4. Create a new connection using WB : testing the connection works
> 5. Connect to the database ( may or may not crash) : a SQL tab opens
> 6. Connect to the same database : crashes : it says MySQL workbench has stopped working : a new SQL tab can not open

[20 Nov 2015 23:32] Godofredo Miguel Solorzano
Thank you for the bug report. Looks like you have the reason for in the log:

15:14:22 [ERR][      WBContext]: Unsupported server version: mariadb.org binary distribution 10.0.22-MariaDB

Are you using containers in your environment?

edit:
For now I solved it this way:from the menu Database => Manage Connections... I have created and saved the connection for the "test" user, so that it appears on the start screen ( when you launch WB, the saved connections appear as a list of gray buttons , and you can launch them directly from there).


Defining a physical strategy for a table with ~110M rows

ip4r Extension

  My plan is to partition the table based on first octet of IP. This will give me 255 partitions. Indexes will be based on start and end ip for the range. Please let me know if there are any more helpful ideas for partitioning and indexing? Any good practices will also help.

This is an ideal case for ip4r, excerpt from the docs.

  [...] the builtin types have no good support
  for index lookups of the form (column >>= parameter), i.e. where you
  have a table of IP address ranges and wish to find which ones include
  a given IP address.  This requires an rtree or gist index to do
  efficiently, and also requires a way to represent IP address ranges
  that do not fall precisely on CIDR boundaries. [... The default types] are variable length types (to support ipv6) with non-trivial overheads, and the authors (whose applications mainly deal in large volumes of single
  IPv4 addresses) wanted a more lightweight representation.

CREATE TABLE ipranges (range ip4r primary key, description text not null);
CREATE INDEX ipranges_range_idx ON ipranges USING gist (range);
INSERT INTO ipranges VALUES ('10.0.0.0/8','rfc1918 block 1');
INSERT INTO ipranges VALUES ('172.16.0.0/12','rfc1918 block 2');
INSERT INTO ipranges VALUES ('192.168.0.0/16','rfc1918 block 3');
INSERT INTO ipranges VALUES ('0.0.0.0/1','classical class A space');
INSERT INTO ipranges VALUES ('10.0.1.10-10.0.1.20','my internal network');
INSERT INTO ipranges VALUES ('127.0.0.1','localhost');

SELECT *
FROM ipranges
WHERE ip <<= range
ORDER BY ip, @ range;


how can i get an accurate count based on max date when joining 3 tables when one of the join fields is many to 1?

mmm based on your sample date your query is not wrong. but your expected results are wrong.
select
    a.task_code,a.sol_id,a.cur_id
    ,b.m_id
    ,c.groupid
FROM
    task_table AS a
    inner join worktable AS b on a.sol_id = b.sol_id
    inner join grouptable AS c on b.m_id= c.m_id
    where
        a.cur_id = 1 and a.task_code is not null
ORDER BY c.groupid, b.sol_id;

select
    c.groupid
    ,count(c.groupid) AS group_count
    ,a.task_code
from task_table AS a
join worktable AS b on a.sol_id = b.sol_id
join grouptable AS c on b.m_id= c.m_id
where
    a.cur_id = 1 and a.task_code is not null
group by
    c.groupid, a.task_code
;

the group_id A76 is linked to the work table with sol_id 23,24 and 42; 23 and 24 are task_code A. 42 is task_code B.
Y23 has two m_id that connect sol_id 45 and 83 to task code A and B.
So the second output in the image is as you expect but has 2 more rows related to the task B.
Maybe the sample date is wrong because a can't see a wrong result to fix.


Please look back at my answer again. Issue #1 might be your one and only problem. Why ?

FACT #1 : 5199 Qcache_total_blocks allocated
FACT #2 : 622 Qcache_free_blocks
FACT #3 : 2269 Qcache_queries_in_cache
FACT #4 : query_cache_limit is 268435456 (256M)
FACT #5 : query_cache_min_res_unit is 4096 (4K)

What can said here ?

There are 2269 queries in the Query Cache using either 2 or 3 blocks (not accounting for fragmentation). Query Cache is 88.0362% (1 - (622/5199)) used. The largest a query can be inside in the Query Cache is 256M, and the smallest is 4K. So, queries are definitely piling up in it when SELECT queries retrieve result sets that's not there before.

If any SELECT query returns more than 256M or less than 4K, none of the data retrieved will be in the query cache. Consequently, running the same SELECT query (that begger than 256M or smaller than 4K) requires reading the data from disk all over again.

All you could really do is lower query_cache_min_res_unit to the minimum value. That's 512, which 0.5K. You can do that dynamically by running

mysql> SET GLOBAL query_cache_min_res_unit = 512;

Then, go monitor Qcache_hits. If this solves it for you, go add this to my.cnf

[mysqld]
query_cache_min_res_unit = 512

You don't have to restart mysql since you changed it dynamically.

If this does not change anything, then you need to examine the nature of your app. If your app retrieves data by some SELECT query and you never run that same SELECT query again, Qcache_hits cannot be anything other zero. If Qcache_hits is always going to be 0, that's why I had to bring up Issue #2. No sense in having a query cache on if you never cache anything. It will just nominally slow down InnoDB looking for any changed data. You can be sure that the data retrieved from SELECTs will be sitting in the InnoDB Buffer Pool.

You may need to spend some time examining the size of all your result sets and tuning the query cache to behave and dwell in peace with InnoDB (Why query_cache_type is disabled by default start from MySQL 5.6?)


There are several things that could cause SSMS to attempt to connect to a certain database at logon:

If the user is in an AD group, and the login for that group has the msdb database set as the default.  If the user belongs to multiple groups that have logins, I don't know which login it uses to decide which database to connect to, but nonetheless it will use one of them.
On the connection dialog in SSMS, click the options button and note that the database to connect to can be specified.  This setting is persistent, so if it was set to msdb in the past, it stay that way for the user until changed.
On the connection dialog in SSMS, click the options button and select the Additional Connection Parameters tab.  This setting is also persistent, and if "database=msdb" was specified there at some point, removing it will resolve the issue.


I know this is a very late answer, but I've only just seen the question. The name of the target and the circumstances under which it appears suggest to me that you are using heterogenous services (or one of the database specific gateway products) in order to connect to a non-oracle database. The configuration of these links requires the creation of a service in the listener.ora on the database server (amongst other things). When an agent attempts to discover databases on a host it follows the following rules.

parse /etc/oratab for databases listed there
parse $ORACLE_HOME/network/admin/listener.ora for statically registered databases
query the listener via lsnrctl to obtain service names.

Stage 2 of this process will pick up any non-oracle targets defined in listener.ora. You probably want to delete them.


How can I handle unwanted, non-erroneous situations better?

Is there a more terse way of returning an error message and returning
  control to the caller for unwanted situations that aren't strictly
  speaking errors?

You can use THROW

  Raises an exception and transfers execution to a CATCH block of a
  TRY…CATCH construct ... If a TRY…CATCH construct is not available, the
  session is ended.

IF @UserName = 'John'
    THROW 50000, 'John, get out', 1;

The above is somewhat more terse as it is just a single statement and doesn't need the RETURN so you can also drop the BEGIN and END


Oracle vs SQL Server, 4k vs 16k block sizes

Oracle logical block size is orthogonal to the storage logical block size (and the file system block size, for that matter). At a minimum your Oracle block size should be a multiple of the disk block size to avoid reading unnecessary data. If your tablespaces have different block sizes, the smallest of them (block sizes) would need to meet this constraint.


Insert into Employee_History
  Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Make sure the column order of the Employee_History table matches the order of the variables listed.  The best practice is to list column names on INSERT statements.

The other possible cause is that the EmployeeName column in the Employee_History is shorter than 100 characters.

Importantly, as suggested in the comments, triggers fire once per statement instead of once per row so must be coded to handle multi-row inserts.  The history table insert should be refactored as INSERT INTO Employee_History(column-list) SELECT column-list FROM inserted  Not related to this error, though.

Also, there are a number of other errors in the trigger.  An UPDATE statement can update multiple columns yet the trigger code identifies only one the potentially changed columns.  The UPDATED function in an UPDATE trigger does not indicate the value has changed, only that it specified in the SET clause of the UPDATE statement.  This may cause erroneous messages in the history table.


Error: "A slave with the same server_uuid/server_id as this slave has connected to the master"

@Michael-sqlbot hit it on the nail. Following his suggestion I reviewed the documentation @rodrigogs/zongji and sure enough there is an option to set the serverId. Like so:

const instance = new MySQLEvents(DBcon, {
        startAtEnd: true,
        serverId: 1,
        excludedSchemas: {
                mysql: true,
        }
});


what are the prerequisite for installing oracle 11gr2

Check this out:
[https://docs.oracle.com/cd/E11882_01/install.112/e47798/reqs.htm#NTDBI002][1]
[http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm][2]
Probably you will get answers for such questions quickly if you google them.


Restoring only differences with SQL differential backups?

The question you asked
No. It is not an option to apply just a differential backup, without the full backup (aka the differential base backup).
You want to essentially undo changes made after the backup, and differentials cannot do this. They are designed to work in the other direction--they "fast forward" changes made since the prior full backup, allowing you to get to the end state quicker, without having to redo the actual changes along the way.
A differential backup contains only the data pages that have changed since the last full backup. Your scenario would require undoing pages that are changed after the differential backup is taken--the differential has no idea what will change in the future, so it likely doesn't have the data to undo those changes. In order to do what you suggest, you need a backup with every data page, so that you can undo any possible changes--the mechanism for this is a Full backup.
The question you should have asked
It sounds like you want to look at database snapshots.
Snapshots are essentially that inverse of differentials that you're looking for. They keep track of changes since the snapshot creation, so that the snapshot shows the original version, regardless of what happens to the real database. Snapshots can also be used to roll back to the point in time when they were created.
From the docs:

In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward

It's important to note that snapshots are not backups. They can be used to revert data changes, but they are not helpful if the original/real database becomes corrupt, or goes offline.
You'll want to read up on reverting to a snapshot to ensure that it does meet your needs, and that the various limitations work for your scenario. In particular, in order to revert to a snapshot, you'll need to drop other snapshots first.
You mention "...myDB_v1, myDB_v2, v3 etc.. and restore between them quickly." Snapshots would allow you to actively query all versions simultaneously, but when you want to revert your "real" database to one of those versions, that process would involve dropping all other snapshot versions.


Initializing Transactional Replication From Backup

Putting the comments into an answer so the question can be marked answered:

@crummel4 says, "See the third section from the bottom of http://replicationanswers.com/NoSyncOn2005.asp titled How "initialize with backup" Works, and How to avoid Pitfalls"

@Kin says, "For step by step instructions, please refer to http://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/ or http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx


MariaDB crashes on mismatched tablespace ID for InnoDB

Suggest you turn off caching.

Often, having a "cache in front of a cache" provides little or no performance benefit.

Meanwhile, upgrade from 10.1; there are likely to be stability fixes in 10.2 or 10.3.


mysql (5.5.) cmd unresponsive when selecting a huge db

The client message should read (emphasis mine)

  Reading table information for completion of table and column names You
  can turn off this feature to get a quicker startup with -A

where "-A" is the shortcut for --no-auto-rehash.

By Default, the MySQL client will, on connection, run queries on the INFORMATION_SCHEMA to discover

all the tables in the current database
all the columns for all the tables found

This is to support "auto completion", so that a human can type

SELECT * FROM EMPLO<TAB>

and the tool will then expand the query text to

SELECT * FROM EMPLOYEE

This feature is very costly when the database is big, and it should be disabled to improve connection time.

See the reference manual:
https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_auto-rehash


How to code something like 'Update on duplicate key update to something else'?

Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in the member table? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) (semi-pseudocode)?


Is there anyway to tell when the last query was made against a database?

You can use the following SQL command to get the last commands that executed on MyDB (as example).

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('MyDB') --note you need to change MyDB to your database name
ORDER BY deqs.last_execution_time DESC

Usfull links:

https://stackoverflow.com/questions/13638435/last-executed-queries-for-a-specific-database

http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/


Import a large MySQL dump 68GB on RDS instance

You may try to edit your mysqldump file to remove indexes. It's just a text file with SQL statements, so you need to somehow find CREATE TABLE statements in it and remove KEY lines from them (except from PRIMARY KEY). But that can hardly help with the speed because InnoDB index creation is quite efficient and doesn't take overhead time compared to separate index creation after the import.

If you have limited time access to the RDS instance, I would recommend to deploy the dump on your local server/PC (it may take even longer), then make a dump of only smaller tables to apply on RDS separately, then think how to move the larger ones. LOAD DATA INFILE may be not much faster than mysqldump so it's worth considering copying tablespaces (e.g. "cold backup method" here: https://dev.mysql.com/doc/refman/8.0/en/innodb-migration.html ).


Use the STUFF(...) T-SQL function to strip the last character off.  First REVERSE the string from the query results, then use STUFF, then REVERSE the result again.
Like this:
SELECT REVERSE(STUFF(REVERSE('this is my string'), 1, 1, ''))

The result looks like:

Results

this is my strin

Here's the Microsoft Docs pages for STUFF and REVERSE.
Actually, looking at your query more closely, you can refine it slightly to eliminate the use of the REVERSE function.  Take the example below, where I'm concatenating the names of all the columns in the database with a semi-colon separator.
SELECT STUFF((
    SELECT N'; ' + c.name
    FROM sys.columns c
    FOR XML PATH (N'')
    ), 1, 2, N'');

I add the semi-colon at the start of the string, then simply use STUFF to replace the first 2 characters with "nothing".
So the results look like:

Results

Column1; Column2; Column3

Newer versions of SQL Server (2017+) support the use of the STRING_AGG aggregate which can significantly simplify this kind of query.  To get the same output as my query above, using STRING_AGG, you'd write it like this:
SELECT STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ')
FROM sys.columns c;

There is CONVERT(nvarchar(max), ...) wrapped around the c.name column to coerce the STRING_AGG function into using a string of sufficient length to contain all the column names in my database.  If you leave that out, SQL Server reports an error:

Msg 9829, Level 16, State 1, Line 10
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.


ORA-16706: Data Guard broker worker process not available

Thanks for comments for my doubts.

As now I have found a root cause and fix.
Please find a fix:

The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low. This can happen even in network choke condition also.
Please provide what the OperationTimeout is set to.
DGMGRL> connect sys/
DGMGRL> show configuration OperationTimeout;

SOLUTION
Increase the OperationTimeout or apply the patch.
If this shows low 30-120, then set it higher, up to 600 (10 mins) :

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=600;

Configuration - Dataguard_ABC

Protection Mode: MaxPerformance
  Databases:
  ABC_P - Primary database
    ABC_F - Physical standby database
    ABC_D - Physical standby database

Then works fine.


MYSQL find if a default value has been set for a table (DateTime Specifically)

Using @a1ex07 answer, I used this query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE (table_schema, DATA_TYPE) = ('my_schema', 'timestamp') AND COLUMN_DEFAULT IS NOT NULL;

That showed that many were using CURRENT_TIMESTAMP instead of UTC_TIMESTAMP.


Can we create a stored procedure to copy detached .mdf and .ldf files from original location to new location?

Well you can use xp_cmdshell, write (or find and use) a CLR routine to do file copies and things like that. For example, the unrecomnended use of xp_cmdshell.

EXEC xp_cmdshell "COPY \\share\path\file.mdf \\othershare\otherpath\file.mdf"

However, you would be better just making a backup (which from SQL Server 2008 R2 supports compressed backups) and you will get a faster transfer than detaching and copying files.  Not to mention the risks of detaching a file and not being able to reattach.

Recommendation: Use BACKUP and RESTORE.


mysqld - cannot connect to socket after changing datadir location

The problem is the mysql client program configuration.  You can specify the --socket=/NEW_DIR/mysql/mysql.sock command-line option or add it under the [mysql] section of your my.cnf file.

Please look at the documentation for specifying options through option files: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

Since changing the socket path affects all the client programs, then it will make sense to update the global my.cnf and set the socket option under the [client] section.


Nested aggregation and grouping on multiple columns in MySQL

This should work:

SELECT
    d.subcategory_id,
    d.vendor_id,
    MIN(d.price) AS price,
    d.inserted_at
FROM product AS d
JOIN (SELECT
        b.category_id,
        b.subcategory_id,
        b.vendor_id,
        a.last_iat
    FROM product AS b
    JOIN (SELECT
            a.category_id,
            a.subcategory_id,
            a.vendor_id,
            a.price,
            MAX(a.inserted_at) AS last_iat
        FROM product AS a
        GROUP BY a.category_id,a.subcategory_id,a.vendor_id
        ) AS a
        ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
WHERE d.category_id=1
GROUP BY d.category_id,d.subcategory_id;

Test:

mysql> SELECT
    ->     d.subcategory_id,
    ->     d.vendor_id,
    ->     MIN(d.price) AS price,
    ->     d.inserted_at
    -> FROM product AS d
    -> JOIN (SELECT
    ->         b.category_id,
    ->         b.subcategory_id,
    ->         b.vendor_id,
    ->         a.last_iat
    ->     FROM product AS b
    ->     JOIN (SELECT
    ->             a.category_id,
    ->             a.subcategory_id,
    ->             a.vendor_id,
    ->             a.price,
    ->             MAX(a.inserted_at) AS last_iat
    ->         FROM product AS a
    ->         GROUP BY a.category_id,a.subcategory_id,a.vendor_id
    ->         ) AS a
    ->         ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    ->     GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    ->     ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
    -> WHERE d.category_id=1
    -> GROUP BY d.category_id,d.subcategory_id;
+----------------+-----------+-------+---------------------+
| subcategory_id | vendor_id | price | inserted_at         |
+----------------+-----------+-------+---------------------+
|              1 |         2 |  9.00 | 2015-07-26 08:00:00 |
|              2 |         3 | 16.00 | 2015-07-23 04:00:00 |
+----------------+-----------+-------+---------------------+
2 rows in set (0.00 sec)

mysql>

Explain:

I used the index recommendation from @ypercube.

mysql> EXPLAIN SELECT d.subcategory_id, d.vendor_id, MIN(d.price) AS price, d.inserted_at FROM product AS d JOIN (SELECT b.category_id, b.subcategory_id, b.vendor_id, a.last_iat FROM product AS b  JOIN (SELECT a.category_id, a.subcategory_id, a.vendor_id, a.price, MAX(a.inserted_at) AS last_iat FROM product AS a GROUP BY a.category_id,a.subcategory_id,a.vendor_id ) AS a ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id) GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at) WHERE d.category_id=1 GROUP BY d.category_id,d.subcategory_id;
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref                                        | rows | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | d          | ALL   | q_ix          | NULL | NULL    | NULL                                       |    5 | Using where; Using join buffer               |
|  2 | DERIVED     | <derived3> | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using temporary; Using filesort              |
|  2 | DERIVED     | b          | ref   | q_ix          | q_ix | 15      | a.category_id,a.subcategory_id,a.vendor_id |    1 | Using where; Using index                     |
|  3 | DERIVED     | a          | index | NULL          | q_ix | 19      | NULL                                       |    5 |                                              |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)

mysql>


DBeaver not showing any Comments/Remarks in MSSQL

The Microsoft JDBC driver does not return that information through the standard JDBC API. I think the main reason for that is, that the driver uses sp_columns internally (at least the last time I checked - which is a couple of years back) and that procedure doesn't return that information.

I guess DBeaver simply has no special code to retrieve the "extended properties" for tables or columns for SQL Server, whereas SQL Workbench/J "knows" those extended properties


SQL Server Partitioning - Parent / Child tables

Well, I'm not saying this is pretty, but it might work.

First, partition the instance table by pseudo-datetime.  Each day/week/month, load the new instance information.  Then assign a new primary key id to this data.  Normally, you would want this to be an identity, but in this case, you have to guarantee uniqueness.  Now, place this into its own partition.

This is almost by date time.  However, other tables that reference instances will be able to join to the correct partition.

Now, if the events and locations are relatively small tables, then perhaps this is enough.

Otherwise, you can repeat for these tables.  However, it gets harder if you add a new event that refers to an older instance.  To handle this, you'll want to assign a new event id range to each partition, but leave a big gap at the end.  Then, when you load event data, you need to determine the correct partition to place it in, based on the partition of the instance id.  You may end up loading events into multiple partitions.

This is an idea.


I assume when you say JOIN you really mean a more general combination of the two tables. JOIN is an operator in SQL, so you should avoid using it when you are referring to a UNION. Anyhow, if I get it right you want a FULL OUTER JOIN between two tables:

select COALESCE(a.filename, b.filename) as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
    ON a.filename = b.filename

COALESCE is a function that return the first value from left to right that is not null.

However, from what I understand SQLite only supports LEFT JOIN so you have to rewrite the query using two LEFT JOINS and a UNION as:

select a.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
     , CASE WHEN b.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)
                      THEN 0 ELSE 1
                 END
       END
from T1 as a
LEFT JOIN T2 as b
    ON a.filename = b.filename
UNION
select b.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
     , CASE WHEN a.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)
                      THEN 0 ELSE 1
                 END
       END
from T2 as b
LEFT JOIN T1 as a
    ON a.filename = b.filename

Note that a and b changed place in the second leg of the union.


Can I get notification when event occur?

Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.

Below is the script that will help you with Deadlock detection and email in real time :

It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.

use dba_db ---- change HERE use find and replace as per your database name !!
go
/********************************************************************
Job Name    :   Monitoring - Deadlock Detector
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled
*********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock  (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Monitoring - Deadlock Detector',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Job Name :   Monitoring - Deadlock Detector
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'INSERT INTO Deadlock (

AlertTime,

DeadlockGraph

)

VALUES (

GETDATE(),

''$(ESCAPE_NONE(WMI(TextData)))''

)',
        @database_name=N'dba_db',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send Email with Deadlock Graph]    Script Date: 10/01/2010 12:01:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''
Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately
''
set @tableHTML = @tableHTML+ N''
'' +
           N'''' +
            N''
RecordId	'' +
            N''AlertTime	'' +
            N''DeadlockGraph	'' +

            N''
'' +
           ISNULL(CAST ( (
                            select  td = '''',
                                    td = RecordId,'''',
                                    td = AlertTime,'''',
                                    td = DeadlockGraph,''''

                     from dba_db.dbo.Deadlock where notified = 0

    FOR XML PATH(''tr''), TYPE

            ) AS NVARCHAR(MAX) ),'''') +
            N'''' +
            N''
'' ;
-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!
exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
-- send email out with the graph attached
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = ''DBMAIL PROFILE'',             ---- change HERE db mail profile !!
            @recipients=''DBAcompanyGroup@companyName.com'', ---- change HERE  email group!!
            @subject = @subject1,
            @body = @tableHTML,
            @body_format = ''HTML'',
            @file_attachments = ''D:\logs\Deadlock.xdl''; ---- change HERE  deadlock graph location!!
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dba_db.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dba_db.dbo.Deadlock',
        @database_name=N'dba_db',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

-- create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Monitoring - Deadlock Detector' ;
GO


Is there any reason that metadata should not be a part of my database schema?

Most DBMS already do store metadata in the database.  This data is general stored in what is broadly referred to as "system tables".

The metadata that a DBMS will store already will be what it needs to operate the database.  Some of the types of metadata you've mentioned, e.g. ownership, permissions, and possibly even formats and descriptive names are already stored in your DBMS's system tables.

Other types of metadata (e.g. date/time of data entry) are record oriented, as opposed to the table or column oriented metadata that you can find in your DBMS's system tables.  Record oriented metadata needs to be stored using a mechanism that you design and implement yourself.  Typically, this means storing the record oriented metadata with your records directly.  In some cases, you might find it helpful to normalize out some kinds of record oriented metadata into a common repository.  An event log that tracks events for multiple tables is an example of such a common repository.

How you handle your metadata depends on what you need it for.  As a matter of best practice, let the DBMS do what it is designed to do.  Don't track metadata manually if the DBMS is doing it automatically.  Only manually track the extra metadata.


How to use an array as argument to a VARIADIC function in PostgreSQL?

Pass the array as is using the VARIADIC key word in the call:

CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[])
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT json_extract_path_text(string::json, VARIADIC params)';

Call:

SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}');

 json_extract_path_text
----------------------
 foo

The manual on VARIADIC:

  Sometimes it is useful to be able to pass an already-constructed array
  to a variadic function; this is particularly handy when one variadic
  function wants to pass on its array parameter to another one. You can
  do that by specifying VARIADIC in the call:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

More details:

Select rows such that names match elements of input array for pgsql function

To be clear: Inside the function, the VARIADIC parameter is just another array, nothing special about it. VARIADIC being used in two separate function definitions makes the case a bit confusing. The solution is to use the same keyword VARIADIC for a related but different purpose in the function call. Don't get confused even more.

Aside: Do not quote the language name, it's an identifier.

Case-insensitive version with citext?

  I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

While the above function works, it's not case-insensitive at all. citext preserves original input, which is restored with the cast to text (or json) and can be mixed-case.

I would not use citext to begin with. Various corner case problems:

Index on column with data type citext not used

For your purpose I suggest calling the built-in json_extract_path_text() with lower(citext_value), which returns lower-case text, and lower-case the 2nd parameter ("path elements") as well, to make it actually case-insensitive:

SELECT json_extract_path_text(lower('{"F1":{"f2":1,"f3":"foo"}}'::citext)::json
                            , VARIADIC lower('{f1, F3}')::text[]);

Note the cast to text[] after lower().


WHERE runs before the window function. So run the whole thing in a derived table.

SELECT t.*
FROM (
  SELECT 1,
    row_number() over (
      partition by lower(X), lower(Y) order by X DESC
    ) AS rn
  FROM dbo.MyTable
) AS t
WHERE rn = 1;


How to add the value of my Sub-query, I want to add the value of Cash,Charge and Other_Charges as Total_payment

You just need to add this as another column in the select list:

  (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL

Giving:

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
      F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
      PREV_POSITIVE,TOTAL_SALES_AMT,
      CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS
      SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS
      TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ,
      (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT
      +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT
      ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-
      F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-
       F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS
       VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-
       F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))
       AS NETSALES,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE,
      (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
                   FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +
               (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                   FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +
               (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                    FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL
 FROM TL_FISCALREPORT   F
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30'
ORDER BY TID


Is it possible to reliably number rows in some order conforming to SQL standard?

MariaDB 10.2.2 has a bunch of Windowing Functions.  In theory they should be 'standard-compliant'.

An example: (also demonstrates a sequence number generator)

SELECT ROW_NUMBER() OVER(ORDER BY seq) AS row_num,
       seq*seq
    FROM seq_10_to_20_step_2;

+---------+---------+
| row_num | seq*seq |
+---------+---------+
|       1 |     100 |
|       2 |     144 |
|       3 |     196 |
|       4 |     256 |
|       5 |     324 |
|       6 |     400 |
+---------+---------+
6 rows in set (0.00 sec)


PostgreSQL does not use all available IOPS on a single operation

ADO.NET (used in the code sample) obviously communicates via network - even if a local Postgres instance is used. Using STDIN therefore results in quite a bit overhead. Before Postgres is able to insert the bulk data, it needs to be:

serialized by C# client
transmitted via network connection (in my case to localhost)
deserialized by Postgres

This basically explains/justifies the described behaviour.

If the C# Client and Postgres are run on the same computer, you can speed up bulk inseration by writing a csv file on a local ssd drive (even better a ram drive) and execute the SQL statement 'Copy MyTable From x:\test.csv'.


So I found one 'solution' for mysql:

SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
SET @SQLCOMMAND = CONCAT('SELECT * FROM cameras WHERE ', @columnID, '= 2');

PREPARE stmt1 FROM @SQLCOMMAND;
EXECUTE stmt1;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
+----------+----------------------+-----------+------------+
1 row in set (0.00 sec)
DEALLOCATE PREPARE stmt1;

As I am using python connector to execute those commands, I had to modify it to different approach. Multiquery was not working for me in this case

cursor = self.conn.cursor(buffered=True)

query1 = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = {0!r}) AND (`COLUMN_KEY` = 'PRI');".format(tablename)
cursor.execute(query1)
columnID = cursor.fetchone()[0]

query2 = "SELECT * FROM {0:s} WHERE {1:s} = {2!r};".format(tablename, columnID, str(idx))
cursor.execute(query2)
single_row = cursor.fetchone()
cursor.close()

(2, 'Canon EOS 5D Mark II', 1, '36x24')


MySQL does not read user specific options in ~/.my.cnf

init-connect is ignored when you connect as the MySQL root user.

Check the file config/database.php for Laravel.  It needs to say something like: 'mysql' => [..., 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', ...]


why when I change a column from DATETIME to DATE, I can't see any storage improvement?

In several cases, you will need to rebuild an index and/or the clustered index in order to observe any freeing up of space.

If this is a heap, you can just say:

ALTER TABLE dbo.MyHeap REBUILD;


Max_packet_allowed setting for Windows 7

The maximum size for a MySQL Packet is 1G according to the MySQL Documentation. See my post Changed max_allowed_packet and still receiving 'Packet Too Large' error  in case you are dealing with big BLOBs.

If you are importing a large database from Windows. see my past posts

Jul 24, 2013 : Moving MySql from windows server to linux (StackOverflow)
Apr 17, 2011 : How can I optimize a mysqldump of a large database?


IBM data studio 4 - how to open a new sql script editor?

Click the icon above....
That's the easiest way!


Is it possible to run query against only part of the Central Managed Servers?

Try wrapping your query inside some conditional logic which checks for the @@version.
It will still access all servers in your Central Managed Server list, but it won't throw the error.  Here is an example you could try.

IF (SELECT @@VERSION) LIKE '%2008%'
BEGIN
    SELECT 'not avail on 2008' AS role_desc
        ,'not avail on 2008' AS NAME
        ,'not avail on 2008' AS replica_server_name
        ,GETDATE() AS checkTime
END
ELSE
BEGIN
    SELECT s.role_desc
        ,g.NAME
        ,r.replica_server_name
        ,GETDATE() AS checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN sys.availability_groups g
        ON r.group_id = g.group_id
END

Regarding your question about saving the results of the CMS query - I'm not aware of a built-in method for doing that.  However, one option (that requires a little extra work) is to:

Run the CMS query, select all of the results grid and choose Copy with Headers.
Head over to DBFiddle and click on the Text To DDL button at the bottom of the page.
Paste the contents of the CMS grid results in the Formatted Text window and enter a table name in the Table Name box.
Click the Preview DDL button which will show you a CREATE TABLE command along with INSERT statements.

You'll have to do some minor modifications to the generated DDL such as removing tick-mark quotes around column names and using brackets around column names that have spaces like [Server Name] or putting brackets around reserved identifiers like [Name].

This will allow you to save the results to a table of your choice for further analysis.


MySQL Permissions Necessary To Reset Password

A user should be able to change his/her own password strictly using SET PASSWORD.

For a connected user to set the new password to mynewpass, just run the following:

mysql> SET PASSWORD = PASSWORD('mynewpass');

According to the MySQL Documentation on SET PASSWORD, if the server is a read-only enabled server, you need SUPER privilege to do this. Otherwise, you can do this any time. There is no need for another user to set someone else's password. If you need a super user to set it you can still use SET PASSWORD.

To set the password of 'someuser'@'10.1.2.30' to hisnewpass, run this:

mysql> SET PASSWORD FOR 'someuser'@'10.1.2.30' = PASSWORD('hisnewpass');

According to the MySQL Documentation on SET PASSWORD, this is the equivalent of:

UPDATE mysql.user SET Password=PASSWORD('hisnewpass')
WHERE User='someuser' AND Host='10.1.2.30';
FLUSH PRIVILEGES;

Using SET PASSWORD does not warrant manipulating mysql.user.


How do I avoid excessive zeroes for numeric fields?

As mustaccio pointed out, this is more of an issue of the displaying of the value on the client side. Your number is originally generated with a lot of unneeded 0s in PHP. The column that stores this data in database has a fixed amount of precision it must adhere to (per its schema definition), so all values are stored in that column with the same precision (and technically normalized with the same amount of 0s). This isn't apparent though until recalled back into the client application. It is the client side that is responsible for formatting.
If you really wanted to avoid this, you could store your results in a VARCHAR column instead and cast your results to a string then use some sort of function in PHP to remove the trailing 0s like RTRIM() or REGEX.
This isn't necessarily recommended from a Normalization standpoint for performance and maintainability reasons, but if you don't need to manipulate these results in SQL or do any aggregation on it after it's stored then it's probably ok.


Alright, so this solution has turned out to be pretty ugly. The end result is that I have to run the powershell command via the Powershell job step type and run it using xp_cmdshell in a sql job step type. Running it in both contexts guarentees the drive is mapped.

I then run a secondary step for both environments in which I test to make sure the drive is mapped using the same logic, ie:

Sql (CmdShell) Check

exec xp_cmdshell 'powershell if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}'

Powershell Check

if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}


Is it possible to have two copies of a table clustered in different arrangements seamlessly?

To have the data held in two different physical sequences one must store the data twice. This can be achieved by defining a second, covering index. A covering index contains all of the columns required by a query. This way the optimiser has no need to refer to the base table to read further values and is unlikely to revert to a base table scan for the query plan. The optimiser performs an index-only scan. As the choice of index is made by the optimiser and not be the programmer no application code need be altered to take advantage during reads. No further objects are required to maintain consistency during writes.

The columns used in the WHERE clause will be the leading columns of the index. The sequence of the other columns is unimportant. As and when PostgreSQL supports the INCLUDE syntax this index can be changed to use it.

The downsides include a) extra disk to store this data b) additional latency during writes to maintain the index c) more system maintenance required for reorgs and such, and d) as queries change the covering index(es) have to change to match e) correspondingly larger and longer backups and restores.