How can I tell what database/procedure is using a linked server?

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects.

/*********************************
    Name: Text Searcher
    Author: Jonathan Fite
    Created: 1/16/2015

    Purpose: To enable one to more easily search for objects by an indicated string.

    Usage:
        - Update the "Where To Search" section to turn on or off various sections.
        - Craft an insert statement to populate "@ObjectsToSearchFor" with the text you
            want to look for.
        - Execute.

    NOTE: This works recursively (in a fashion).  The first execution gets just what you are
    looking for.  But if you execute it again without explicitly dropping the "#SearchResults" table
    then it will run again, but this time looking for references to the objects it found in the first pass.

    This is useful when you are trying to find out what is using a linked server.  So you get the name of the
    linked server (and it's host name) and run a search on that.  It tells you a handful of objects.  You then run
    it again, but this time looking for those objects (say, in other stored procedures or in SSIS/Jobs).

    CAUTION: If you want to use the recursive functionality, you will need to save off the results FIRST.

        DROP TABLE #SearchResults

    -Linked Server Query (finds all references to linked servers as configured, as well as all variations
        that would allow for accessing a remote server without using a linked server.

        INSERT INTO @ObjectsToSearchFor
        (TextString)
        SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME
        UNION
        SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
        UNION
        SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
        UNION
        SELECT 'OPENROWSET'
        UNION
        SELECT 'OPENQUERY'
        UNION
        SELECT 'OPENDATASOURCE'

**********************************/

--To hide row counts and make the print statements easier to see.
SET NOCOUNT ON

/** Where To Search **/
DECLARE @SearchSSIS BIT = 1
DECLARE @SearchObjectNames BIT = 1
DECLARE @SearchObjectDefinition BIT = 1
DECLARE @SearchAgentJobs BIT = 1

/** What are you looking for? **/
DECLARE @ObjectsToSearchFor TABLE
    (
    TextString VARCHAR(200) NULL
    )

INSERT INTO @ObjectsToSearchFor
(TextString)
SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME
UNION
SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
UNION
SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
UNION
SELECT 'OPENROWSET'
UNION
SELECT 'OPENQUERY'
UNION
SELECT 'OPENDATASOURCE'

/*************************************************************************************************************************
    Shouldn't need to mess with anything below this line.
**************************************************************************************************************************/

/** Handle Recursion on #SearchResults.
    Remember to save your data first!

    If you don't want recursion, explitly drop #SearchResults between executions.
    **/
IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
BEGIN

    DELETE FROM @ObjectsToSearchFor

    --This allows for successive iterations to search for objects which reference objects which reference....
    INSERT INTO @ObjectsToSearchFor
    (TextString)
    SELECT DISTINCT ObjectName
    FROM #SearchResults
    WHERE LocationFound IN ('sys.sql_modules')
        AND ObjectName IS NOT NULL

    SET @SearchObjectNames = 0

    DROP TABLE #SearchResults

END

CREATE TABLE #SearchResults
    (
    DatabaseName SYSNAME NULL
    , ObjectName SYSNAME NULL
    , LocationFound VARCHAR(100) NULL
    , MatchedOn VARCHAR(200) NULL
    , Misc VARCHAR(100) NULL
    )

/** Various declarations to hold results. **/
DECLARE @SearchTerm VARCHAR(200)
DECLARE @DatabaseName SYSNAME
DECLARE @Query NVARCHAR(4000)

-- Loop through search terms.
DECLARE curSearch CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT O.TextString
    FROM @ObjectsToSearchFor O

OPEN curSearch

FETCH NEXT FROM curSearch
INTO @SearchTerm

WHILE @@FETCH_STATUS = 0
BEGIN

    --Begin Searching.
    PRINT ('Looking for: ' + @SearchTerm)

    /** This will loop over every database, only do it if you need it. **/
    IF(@SearchObjectDefinition = 1 OR @SearchObjectNames = 1)
    BEGIN

        DECLARE curDatabases CURSOR LOCAL STATIC FORWARD_ONLY
        FOR SELECT D.name
            FROM sys.sysdatabases D
            ORDER BY D.name

        OPEN curDatabases

        FETCH NEXT FROM curDatabases
        INTO @DatabaseName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            --Look for object name matching...
            IF(@SearchObjectNames = 1)
            BEGIN

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', O.name'
                    + ', ' + QUOTENAME('sys.objects', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.type_desc'
                    + ' FROM sys.objects O (NOLOCK)'
                    + ' WHERE LOWER(O.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')

                EXEC sp_executesql @Query

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', C.name'
                    + ', ' + QUOTENAME('sys.columns', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.name + O.type_desc'
                    + ' FROM sys.columns C (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = C.[object_id]'
                    + ' WHERE LOWER(C.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')

                EXEC sp_executesql @Query

            END --Looking for object names.

            --Look for definitions.
            --Find objects that have the string in question listed, but ignore where the name is what is being searched for.
            IF(@SearchObjectDefinition = 1)
            BEGIN

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', O.name'
                    + ', ' + QUOTENAME('sys.sql_modules', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.type_desc'
                    + ' FROM sys.sql_modules M (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = M.[object_id]'
                    + ' WHERE LOWER(M.[definition]) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')
                    + ' AND NOT(LOWER(O.name) = LOWER(' + QUOTENAME(@SearchTerm, '''') + '))'

                EXEC sp_executesql @Query

            END --Looking for object definitions.

            --Get next database
            FETCH NEXT FROM curDatabases
            INTO @DatabaseName

        END

        CLOSE curDatabases
        DEALLOCATE curDatabases

    END

    --Look in SSIS packages.
    IF(@SearchSSIS = 1)
    BEGIN

        ;WITH SSISFolders AS
        (
        SELECT pf.folderid
            , pf.parentfolderid
            , CAST(pf.foldername AS VARCHAR(MAX)) AS foldername
        FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
        WHERE pf.parentfolderid IS NULL
        UNION ALL
        SELECT pf.folderid
            , pf.parentfolderid
            , Parent.foldername + '\' + ISNULL(CAST(pf.foldername AS VARCHAR(MAX)), '') AS foldername
        FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
            INNER JOIN SSISFolders Parent ON Parent.folderid = pf.parentfolderid
        )
        INSERT INTO #SearchResults
        SELECT NULL
            , ISNULL(F.foldername, '') + '\' + CAST(ISNULL(S.name, '') AS VARCHAR(MAX))
            , 'msdb.dbo.syssispackages'
            , @SearchTerm
            , NULL
        FROM msdb.dbo.sysssispackages S (NOLOCK)
            LEFT OUTER JOIN SSISFolders F (NOLOCK) ON F.folderid = S.folderid
        WHERE LOWER(CAST(CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS VARCHAR(MAX))) LIKE ('%' + @SearchTerm + '%')

    END

    --Look in SQL Agent Jobs
    IF(@SearchAgentJobs = 1)
    BEGIN

        --Job Name
        INSERT INTO #SearchResults
        SELECT NULL
            , J.name
            , 'msdb.dbo.sysjobs'
            , @SearchTerm
            , 'Job Name'
        FROM msdb.dbo.sysjobs J (NOLOCK)
        WHERE LOWER(J.name) LIKE ('%' + @SearchTerm + '%')

        --Job Description
        INSERT INTO #SearchResults
        SELECT NULL
            , J.name
            , 'msdb.dbo.sysjobs'
            , @SearchTerm
            , 'Job Description'
        FROM msdb.dbo.sysjobs J (NOLOCK)
        WHERE LOWER(J.[description]) LIKE ('%' + @SearchTerm + '%')

        --Step Name.
        INSERT INTO #SearchResults
        SELECT NULL
            , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
            , 'msdb.dbo.sysjobsteps'
            , @SearchTerm
            , 'Step Name'
        FROM msdb.dbo.sysjobsteps S (NOLOCK)
            INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
        WHERE LOWER(S.step_name) LIKE ('%' + @SearchTerm + '%')

        --Step Code.

        INSERT INTO #SearchResults
        SELECT NULL
            , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
            , 'msdb.dbo.sysjobsteps'
            , @SearchTerm
            , 'Step Code'
        FROM msdb.dbo.sysjobsteps S (NOLOCK)
            INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
        WHERE LOWER(S.command) LIKE ('%' + @SearchTerm + '%')

    END --End Looking at Jobs.

    --Get next search term.
    FETCH NEXT FROM curSearch
    INTO @SearchTerm

END

--Cleanup.
CLOSE curSearch
DEALLOCATE curSearch

/***************** Display Output **********************************/

--Display search terms.
SELECT O.TextString
    , COUNT(S.MatchedOn) AS MatchCount
FROM @ObjectsToSearchFor O
    LEFT OUTER JOIN #SearchResults S ON S.MatchedOn = O.TextString
GROUP BY O.TextString

--Display search results.
SELECT DISTINCT * FROM #SearchResults


use OpenDS with MySQL cluster to implement LDAP

I think this will help a bit...


Documentation for selecting table name returning csv of the row

For each table that is created, Postgres creates a corresponding composite type:

  Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

When you use the table name in the select list, you are essentially using a single column of that composite type with several fields as part of the record (this is a good example on the difference between row and record and column and field).

The "CSV style" just happens to be the default text representation of a composite type.

A row constructor is closely related to this.

This is also the reason why select (a,b,c) from some_table is something different than select a,b,c from some_table in Postgres.


ALTER TABLE x drop constraint fk12345; take long time because the parent table (user) in transaction(delete,insert update,..) or X table itself in transaction(not commit or rollback), it meant ALTER TABLE x drop constraint fk12345 may not make the User table locked.


Removing the Oracle Instance details from windows machine

First, the pro-forma:  Better to uninstall the application rather than just delete the files.  Yes, it doesn't help, but it is true and someone will bring it up, so saving time...

Now, assuming that you just want the SID to be able to go through.  Normally, the SID is stored in the registry HKEY_LOCAL_MACHINE\SOFTWARE\Oracle.  It can be in several places there.  Please note that several pieces of Oracle software use this key (e.g. Oracle client) so be careful; probably worth backing up with a registry export command first.

You would also need to check the Windows Services (from a command prompt, type "services.msc").  The Oracle services begin with "Oracle" in their name, so they are easy to find.  If you see some with the SID in the name, you probably need to remove them before installing.  I would use SC.exe to install this as explained here (https://stackoverflow.com/questions/330836/how-to-uninstall-a-windows-service-when-there-is-no-executable-for-it-left-on-th).

If you want a more complete purge, you are in for tons of registry editing, as Oracle often leaves other stuff in the registry.

Good luck, and I hope that this helps,


Are linked servers in any way required by availability groups?

Absolutely not needed by SQL Server for Availability Groups!

  Anything wrong with just doing normal due diligence and changing the linked server user to use non-sa and also changing the sa password?

No, I agree with your approach as long as it doesn't break the business requirements. However, if the developers are using it to query around then I could argue that they should be changing their software to do this, rather than a linked server.

I would tend to agree that it's most likely used to automagically sync logins and jobs, but again there is no need for an actual linked server to do this though I could see why they would use a highly privileged account so they wouldn't have to worry about permissions.


Reporting Services service restart fixes slow reports

I believe problem was that the report definitions no longer matched the stored procedures that were called. After I refreshed all of my reports the problem seems to be resolved.

Unfortunately I can not say for sure that is the final answer as it could be up to 90 days between the last restart and as little as 4 days, so I am still waiting to remove this from my task list.

These are old reports that were originally in version 2005.


How can I reinitialize some MyISAM tables without restarting MySQL?

Absolutely !!!
Suppose you are moving a MyISAM table with the following characteristics:

MyISAM table is called mydb.mytable
datadir is /var/lib/mysql
Moving From ServerA to ServerB

You can move it to another folder without disturbing any other DB activity
Here is how
Step01) Create the Target Database on ServerB with this command
CREATE DATABASE mydb;

Step02) Copy the table to the mydb database on ServerB
Run this rsync on ServerA
rsync -av --progress /var/lib/mysql/mydb/mytable.* ServerB:/var/lib/mysql/mydb

That's it. The table should be immediate registered in the information_schema without a restart. To verify that the rsync worked, login to mysql on ServerB and run this:
SELECT COUNT(1) TableExists FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT table_rows TableCount1 FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT COUNT(1) TableCount2 FROM mydb.mytable;

If TableExists is 1 and  TableCount1 =  TableCount2, CONGRATULATIONS !!!
If you want to double check the rsync, run thi command on both ServerA and ServerB
CHECKSUM TABLE mydb.mytable;

Both checksum values should be identical
Give it a Try !!!


16 MB is the maximum size of single document, e.g. one user.

The size of the collection is limit by maximum database only.

https://docs.mongodb.com/manual/reference/limits/

So logging to the database should be no problem. In my opinion using sensible indizes including _id is superior to a heap of collections.

It is not clear to me what you actually want to store in your database so I cannot really comment on the structure. Generally speaking there are three ways to handle relations: Using arrays

storing subdocuments or
ids of documents (You would have to retrieve those via a separate database request)
invert the relation by giving all referenced documents a field containing the parent document id

If you give more details, chances are somebody can give you a more detailed answer.


How to auto start oracle listener on Linux with machine restart?

This question has been asked and answered hundreds, if not thousands, of times. A simple google search turns up dozens of hits. When in doubt, refer to the official oracle docs: https://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR140
As an aside, there is no reason for you to manually enter all those commands to set the oracle environment.  That is what the 'oraenv' utility is provided for.  First, as a one-time setup, make sure your database is properly listed in /etc/oratab.  Creating the database with dbca should have done this for you. Mine looks like this (the 'cat' command types the file, piping the output to 'grep -v' filters out the comment lines.
[oracle@vbol83-02 dba]$ cat /etc/oratab | grep -v ^#

cdb:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@vbol83-02 dba]$

The format of the entry is
oraclesid:oraclehome:Y|N

Now before invoking oraenv, let's check our enviornment:
[oracle@vbol83-02 dba]$ env|grep ORA
[oracle@vbol83-02 dba]$ echo $PATH
/usr/local/bin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
[oracle@vbol83-02 dba]$

The 'env' command shows all environment variables. Piping the output to 'grep' allows me to show only those that contain the string 'ORA'.  And you see I have none. Also, you see that $ORACLE_HOME/bin is not in my PATH.
Now envoke oraenv. When prompted, enter the value of ORACLE_SID, as listed in /etc/oratab
[oracle@vbol83-02 dba]$ source oraenv
ORACLE_SID = [oracle] ? cdb
The Oracle base has been set to /u01/app/oracle
[oracle@vbol83-02 dba]$

Now let's check the environment again:
[oracle@vbol83-02 dba]$ env|grep ORA
ORACLE_SID=cdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vbol83-02 dba]$ echo $PATH
/usr/local/bin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/app/oracle/product/19.0.0/dbhome_1/bin
[oracle@vbol83-02 dba]$

And you can see that 'oraenv' set it all for me.
I take it one step further, and put that in by .bash_logon script, so that my environment is set 'automagicly" when I log on to the server:
Relevent contents of my .bash_profile:
#----------------------------------------------------------------------
# 3 Aug 2012 - Ed Stevens
#   - replaced hard coded settings of ORACLE_* vars with use of oraenv
#     utility.
export ORACLE_TERM=xterm
export PATH=/usr/local/bin:/usr/sbin:$PATH
export ORACLE_SID=cdb
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK


How to quote database names with spaces when using queryout with bcp?

The solution I settled on was to specify the database name using the -d flag.

bcp "SELECT char(34) + REPLACE([Code], '""""', '""""""""') + char(34),
    char(34) + REPLACE([Description], '""""', '""""""""') + char(34)
    FROM [Table Name]"
    queryout C:\Users\jwallace\out.csv -c -t ","
    -d "Database Name With Spaces"
    -S "10.0.0.1,1848" -U username -P password -k


Forgot mysql owner/root username and password

You have a variety of options to reset the password http://code.openark.org/blog/mysql/recovering-a-mysql-root-password-the-fourth-solution

UPD:
Following the Max's advice let me elaborate. The easiest way to reset any password is to restart MySQL with skip-grant-tables in my.cnf. Then MySQL won't ask the password. To set new password execute this:

 mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = '<host>'"

where <host> is host where user connects form.

Then restart MySQL again, but without skip-grant-tables option.


This is relatively easy to do, you just need to add some analytic values to your original data set to get it:

with grps as (
select t1.*
     , trunc((row_number() over (partition by Center_Code, PaperCode order by Roll_No)-1)/4) grp_id
     , roll_no - row_number() over (partition by Center_Code, PaperCode order by Roll_No) rng_id
  from your_data t1
), rngs as (
select center_code
     , papercode
     , to_char(min(roll_no))||'-'||to_char(max(roll_no)) roll_nos
     , min(roll_no) ord
     , count(*) students
     , grp_id
  from grps
 group by center_code, papercode, grp_id, rng_id
)
select center_code
     , papercode
     , LISTAGG(roll_nos, ', ') WITHIN GROUP (ORDER BY ord) roll_nos
     , sum(students) students
  from rngs
  group by center_code, papercode, grp_id;

In the first subfactored query (grps) I'm calculating a grouping id (grp_id) to put the roll_nos into groups of 4, and range id (rng_id) so that each group of 4 roll_nos can be grouped into contiguous ranges.

In the second subfactored query (rngs) the sub ranges of roll_nos are consolidated and counted and an ordering column (ord) is generated.

In the final output query the sub ranges are accumulated with listagg and the final group size is calculated.


Should I stop an awfully inefficient MySQL DELETE statement?

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field.

I have a much better method.

DROP TABLE IF EXISTS keys_to_keep;
CREATE TABLE keys_to_keep
(
    id INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field;
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
SELECT B.* FROM keys_to_keep A INNER JOIN mytable B USING (id);
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

Check mytable. If it has only the last occurrence of field, then you can

DROP TABLE keys_to_keep;
DROP TABLE mytable_old;

GIVE IT A TRY !!!

I have recommended this technique before

Oct 20, 2014 : Deleting data from a large table with replication
Dec 20, 2013 : Massive MySQL delete from CSV file
Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?

I have also discussed doing soft deletes as an alternative (require maintaining an additional mapping table of ID marked deleted or an extra column to flag a row deleted):

Feb 15, 2012 : How to implement soft deletes?
Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Nov 07, 2012 : Can I do anything about a very long running KILL command?
Aug 07, 2013 : How to delete duplicate records


Can the account name of 'distributor_admin' be changed?

No it can't be renamed.  The good news is that the account only exists on the distributor, which doesn't really contain any user data.

You can safely disable the account when you aren't setting up new replication publishers. If you need to setup a new publisher, enable the account setup the publisher then disable the account again.

It does need to have sysadmin rights. It needs to be able to create linked servers.


Backup / Export data from MySQL 5.5 attachments table keeps failing!

Try increasing your net_write_timeout (probably a default value of 60secs is too small in your scenario of blob and binary values)

Ref:

net_write_timeout

and in general:

net_read_timeout


How to query Job Activity Monitor to get a list of all jobs

This gets you just about everything you mention and you can modify further to suit your needs:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
--AND msdb.dbo.sysjobservers.last_run_outcome = 0


what happens to user connections when an AG fails over?

what controls the switching of connections to the new primary node?

The Availability group (AG) listener directs incoming connections to either primary or read replica depending on how the AG is configured (whether it allows read requests or not).

When a failover occurs, a role reversal happens and a secondary replica transitions to the primary role and the former primary will transition to a secondary role.

During a failover, all client requests are terminated. This is your application downtime. Now, after the failover, when a client reconnects to the AG listener, the listener will reconnect to the new primary (except for read intent connect requests).

  Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?

If your application supports :

MultiSubnetFailover = TRUE, then you will have a perf gain (as described in my answer.)
MultiSubnetFailover = False, then you should lower the HostTTL settings to a lower value e.g. 5 secs vs 300 secs of default.

Ref:

How the Connection Access Configuration Affects Client Connectivity
Connection Timeouts in Multi-subnet Availability Group


Innodb: When bulk inserting or updating - how to disable transactions?

You cannot turn off transactions.

It is assumed that ROLLBACK is the exception, not the rule.  Are you trying to make it the "normal" path?

5 days vs 2 hours -- this is far more extreme than I would expect.  Perhaps you are running out of RAM?  Or swapping?

Please provide more details on the task; we may be able to help you 'solve' the problem in a way other than "turning off transactions".


Is it possible to setup multiple RW Replicas for a database in an AlwaysOn Availability Group?

No, it is not possible to have multiple read/write replicas in an Availability Group. For your requirement, you might consider Merge Replication which allows writes at multiple databases and merges the data across all databases involved in the replication.

This doesn't allow for writing to that same DB, but the replication architecture works to keep both databases synced.


View the output of Adam Machanic's sp_WhoIsActive

You need to use a product which understands that the XML represents a SQL Server execution plan.

Make sure you're using the latest version of SQL Server Management Studio (v17.5 as of this post) and, when you click the XML for the plan returned by sp_whoisactive, you should get an extra window showing the execution plan in graphical representation.

I'd strongly suggest downloading the latest version of SentryOne Plan Explorer which is now completely free.  The latest version has SSMS add-in support such that, when you see the graphical plan in SSMS, you can right-click and choose 'View with SentryOne Plan Explorer' from the context menu and the execution plan will be displayed in SentryOne Plan Explorer. See the SentryOne post Announcing Plan Explorer Add-In Support for SSMS 2016.


Applying single quotes across multiple lines using ssms code snippet

Yes. You can write your own snippets code in a .snippet file and then import it into SSMS.
To import the folder containing the .snippet file:

Open SSMS
Click on Tools ->Code Snippets Manager
Click on Add and select the folder where your custom snippets are written.

I believe this answers a part of your question. You can further follow the below links to get more details.

Adding Custom Snippets: http://www.sqlservercentral.com/articles/SSMS/138994/

About Snippets: https://www.mssqltips.com/sqlservertip/2411/getting-started-with-code-snippets-feature-of-sql-server-2012/


Does our application work with sql-server AlwaysOn?

First, let's clarify terms. AlwaysOn is a Microsoft marketing term which includes both SQL Server Availability Groups and SQL Server Failover Cluster Instances. You should stop using AlwaysOn and be specific for all future communications.

If you are referring to FCIs: the configuration should be completely transparent to your application. They should configure the connection to the virtual network name of the FCI and your application will not notice any difference.

If you are referring to AGs: With AGs, they have the option of configuring an AG Listener or not.

If they are not using a listener then all is well and your application won't notice any difference.

If they are using a listener, in a single subnet, then it is transparent.

If they are using a listener, with multiple subnets (multiple IPs for the listener), then your application will need to support the MultiSubnetFailover=TRUE connection parameter or they will need to modify the RegisterAllProvidersIP Windows property. If one of those aren't used then they will have issues with DNS caching the wrong IP address and might blame the application for failing to connect.

See this article regarding the MultiSubnetFailover parameter.

See this article regarding the RegisterAllProvidersIP property.


You just need to add to the where statement to filter for those that are the primary level

SELECT *
FROM foo AS f1
WHERE id = parent_id
AND id IN (
  SELECT f2.parent_id
  FROM foo AS f2
  WHERE f1.id = f2.parent_id
    AND f2.id != f2.parent_id
);


Shortcut to select all SQL text in Access

MS-Access 2016

Create a new SQL view, write some text and then press F2.


UPDATE articles a JOIN (
  SELECT a2.ID,COUNT(*) AS C
  FROM articles a2 JOIN tag_map b2 ON a2.id=b2.id
  WHERE a2.tags IS NULL
  GROUP BY a2.ID
) b ON a.ID=b.ID
SET a.Tags=b.C
WHERE a.Tags IS NULL;


Eliminating duplicate records in data cleansing

Using T-SQL & PL/SQL, you can use ROW_NUMBER() OVER (PARTITION BY) to find and delete duplicate records in a Table.

Refer to some best examples Removing Duplicates from a Table in SQL Server
and How can I remove duplicate rows? as well as Oracle 'Partition By' and 'Row_Number' keyword


Shared Lock issued on IsolationLevel.ReadUncommitted

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

  Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) =
   N'DECLARE @C INT;
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.


You could setup a job that reads from a table containing a list of logins and their expiration dates. For every login that is expired in this table it could just drop it. There you could also automatically deal with users associated with and databases own by this login.


One way is to look at the sys.dm_db_index_physical_stats output. The min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes will give you the sizes you want.

If you want to see the size of a particular record, another way is to find the physical row location, see SQL Server 2008: New (undocumented) physical row locator function. You can then dump the row physical structure, see Anatomy of a Record.

Finally you can use the product documentation to estimate the size:

Estimate the Size of a Clustered Index
Estimate the Size of a Nonclustered Index
Estimate the Size of a Heap


Auto-incremented ID with sequence and trigger: How to handle manual overrides (causing non-unique error)

Instead of trying to address what happens after someone updates the PROJECT_ID column, have you tried denying updates of the PROJECT_ID column in the first place?

Perhaps revoking update privileges on the PROJECT_ID column? Or perhaps ... yuck ... a trigger to rollback (and generate an error) any attempts to update the PROJECT_ID column?

Since you're ok with them getting an error when they attempt to generate a duplicate PROJECT_ID, I'm assuming you'd be ok with them getting an error when attempting to update PROJECT_ID ... and after a couple such attempts ... and perhaps a "WTF!?!?!" email to the DBA (you?) ... they'll get used to the fact that they can't update the PROJECT_ID column.

Or do you really want to allow them to update the PROJECT_ID column?


update value in row based on value in another row

You just need to use the FROM clause along with the query you already have:

UPDATE test set col3 = prev_col1 + prev_col2
FROM (
  SELECT t_stamp,
      lag(col1) OVER (ORDER BY t_stamp ASC) prev_col1,
      lag(col2) OVER (ORDER BY t_stamp ASC) prev_col2
  FROM test) prev
  WHERE prev.t_stamp = test.t_stamp;


GO is not a Transact-SQL statement, instead it is a command
recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD
and OSQL utilities. These utilities send all statements after the
previous GO statement and before the current GO statement as one
Batch to the Sql Server engine for execution. It should work in a
scheduler
It looks like your transaction failed, got rolled back and there is nothing to commit.

This will NOT run

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO

BEGIN TRAN;
INSERT Test
VALUES
('a');
GO
COMMIT TRAN;

  Msg 245, Level 16, State 1, Line 6 Conversion failed when converting
  the varchar value 'a' to data type int. Msg 3902, Level 16, State 1,
  Line 9 The COMMIT TRANSACTION request has no corresponding BEGIN
  TRANSACTION.

This will run without a problem

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO

BEGIN TRAN;
INSERT Test
VALUES
(5);
GO

COMMIT TRAN;

A good article on transactions  http://www.sommarskog.se/error_handling/Part1.html


Inserting data with SELECT into new column

You already solved your main problem!

But I want to suggest you to create a new table for sub and maybe also for fullpath, because you store the same values again and again in this row.

Something like this:

CREATE TABLE path (id integer, name varchar(100));

Then populate it with a SELECT and add the ID to the main table!


Can a role be granted roles+privileges in oracle all at the same time?

Yes, you can grant as many roles or privileges to a role as you want.
The order or priority does not matter because a role or privilege always permit something. Unlike for example Windows file permissions you don't have "allow write" and "forbid write" - where the priority/precedence matters.

Either the role gets a privilege or he does not.

However, you can create ROLES as use the ROLE as marker in PL/SQL at DBMS_SESSION.IS_ROLE_ENABLED(...), there is would be possible to "restrict by granting" and the order and priority could matter. But such design would be quite ugly and is not recommended.


Handling sql column with mixed varchar and float data types

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport


SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records

An example with CASE statement:

select
  ename, sal, job,
  case
    when comm is null or comm = 0 then 'No data found'
    else to_char(comm)
  end as comm
from
  emp
;

The UNION [ALL], INTERSECT, MINUS Operators

  The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).


How should I represent a latitude and longitude in Postgres without using PostGIS?

You can use the builtin POINT datatype without postgis.


Can postgres make use of both GIN and BTREE indexes in a single query?

To answer your title question, PostgreSQL can make use of multiple indexes for a "bitmap" scan, but only with Boolean logic.  It can't use one to order and the other to filter within the same table scan.  It is an interesting idea, though.


How to restrict access to current user record and its rights in postgres

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year.

For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can just:

CREATE VIEW my_user AS
SELECT *
FROM "user"
WHERE "user"."user" = current_user;

Then GRANT the SELECT and UPDATE rights on it to the restricted user, and REVOKE ALL ON "user" FROM restricted_user.

They'll be able to see and update their record through the view, but nobody else's.

If you want to avoid the need for a different table name, you can use schemas and the search_path, e.g.

CREATE SCHEMA restricted_user;

CREATE VIEW restricted_user."user" AS
SELECT *
FROM public."user" u
WHERE u."user" = current_user;

GRANT USAGE ON SCHEMA restricted_user TO restricted_user;

PostgreSQL will automatically put a schema with the same name as the username first in the search_path, so this will cause connections as the PostgreSQL user restricted_user to see the view instead of the underlying table.

Note that this is a bit leaky, but it's safe unless you let restricted_user run arbitrary SQL against the database. Alternately, you can wait to update to 9.4, which has updatable security_barrier views, preventing the leak.

Personally, I think it's probably going to be easiest to do this at the application level until PostgreSQL 9.5's row security is available. Not least because Rails doesn't tend to play well with database features and I have no idea how its various caching systems, etc, will cope with rows that are sometimes visible and sometimes not.


How can I pass a list of databases to sp_MSforeachdb?

You can't. Also you shouldn't use this undocumented, unsupported, buggy procedure in situations where it seems to work, either. I have written a better replacement here and here.

In fact, the procedure I wrote does support a parameter @database_list, in the form 'db1, db2, db3' (note no need to fuss with embedded single quotes at all), so adopting it for your needs - rather than reinventing the wheel - seems logical.

If you need help with syntax that builds a delimited string from a set for other purposes, please create a different question. If you really need to have each database name enclosed in its own quotes, the last string element in your REPLACE command should be ''',''' and you need to surround the end result with a single quote at beginning and end too.


Let's define a couple of variables to make the rest easier to copy/paste (using names from the original question)

old_db=myapp
new_db=myapp3
db_dump_file="backups/myapp_2018-05-27.pg.dump"

The following assumes that your backup was created with the "custom" format like this:

pg_dump -U postgres -F custom "$old_db" > "$db_dump_file"

To restore $db_dump_file to a new database name $new_db :

dropdb   -U postgres --if-exists  "$new_db"
createdb -U postgres -T template0 "$new_db"

pg_restore -U postgres -d "$new_db" "$db_dump_file"


Cast as INT(11) instead of DECIMAL 11,0 in a view

Decimal(n,0) still treated as integer in mysql 5.0.3 and above.

Decimal (n,0) is a fixed point that did not has fractional part.
So it is safe.

The only different int(11) with decimal(11,0) is the maximum range of number. where as the decimal has bigger range than integer

See MySQL numeric type overview


Is it ever harmful to set a database owner in SQL Server?

You should be using DDL instead of backward compatibility stored procedures:

ALTER AUTHORIZATION ON DATABASE::dbName TO sa;

And the owner of the database (never mind sa) should probably not be the account that your applications use, so this should not really have any effect on your applications. If it does then you should update the connection strings your application uses (which shouldn't be a major change) and make sure the account has sufficient - but not God - privileges.


pacemaker and semi-synchronous replication

We do this at my company. We run thousands of MySQL master-master pairs that use this method. We developed a service that performs the switch you describe. We call this a "successover" because that has a more positive connotation than failover. We can and do run this anytime of day, without even notifying the app team that we are doing it.
Here are the general steps:
Assume:
"Master1" is the current writeable master MySQL instance, where the VIP is defined.
"Master2" is a replica, in read-only mode.

Make Master1 read-only.
Remove VIP from Master1.
Start killing any outstanding queries on Master1. Continue killing any queries in a loop, in case the apps using it try to run new queries. We shouldn't rely on the prior steps terminating queries or connections. I.e. clients might have bypassed the VIP and connected directly to the physical IP address.
Wait for replication lag on Master2 to reach 0. Ideally, compare slave status on Master2 to master status on Master1. If the failover occurred because Master1 is down, then you can't do that comparison, but you can compare if slave status on Master2 shows SQL thread is caught up to the IO thread.
Make Master2 writeable.
Add VIP to Master2.
Stop killing queries on Master1.

When it's automated, this results in less than a second of downtime, provided there is no significant replication lag. Moving a VIP is a lot faster than updating DNS.
Then the apps must reconnect to the VIP, and thus get access to the new primary instance and re-run any queries they need.
Obviously apps should not use the standby MySQL instance, because the point is to allow it to be taken offline for updates, config changes, etc. This also gives you a quick way to respond if there's a problem with the host server that MySQL is running on. We get a few host crashes or failed disks per week, so we do this switch quickly to make sure the app can continue.
It's unavoidable that this causes a brief "blip" where the apps have their connections dropped and have to reconnect, but it's more brief of an interruption than any other solution. Still, apps have to be designed to detect a dropped connection and reconnect. Our biggest issue is educating the app developers to do this. They keep complaining that they get alerted for a lost connection, and we tell them, "we've already documented what you need to do -- it shouldn't alert for a single blip."
This system has been working for several years, but we now have a mixed environment where we have many cloud MySQL instances. We need a new solution, because we can't create VIPs using BGP in the cloud.
So we have prototyped using Envoy as a proxy to MySQL. We believe this works, but we need to develop a service to notify the Envoy proxy of the change when we do a successover. Envoy supports GRPC protocol, so we can send it a message dynamically and it'll start routing traffic to a different target MySQL instance. This proxy-based solution should work identically in the cloud as it does in the datacenter. But this solution is probably more work than you had in mind.
We could also use ProxySQL to do something similar, but Envoy has a lot of adoption within our company for service-to-service traffic already, so if we can leverage that instead of a new type of proxy we have one fewer pieces of technology to use.

Update re comments:
Step 4 in the above list waits for replication to catch up while both instances are read-only. So there are no new updates allowed on Master1 during that time, and Master2 only has to execute a set of remaining updates that were committed before Master1 was set to read-only. Hopefully, this is a very brief wait, unless Master2 was already lagging behind by a lot.
The service that runs the successover refuses to even begin the operation if it detects that Master2 has high replication lag. The user is encouraged to try again later.
Of course, in the real world sometimes you have to override that sort of restriction because of urgent circumstances, so there's an option to force the successover. But this comes with a risk, because if you make Master2 the primary and start allowing new queries directly on it, while there are still outstanding updates to process because of replication lag, you end up in a split-brain situation: You could make an update which will then be replaced by an event from the binary log that actually occurred in the past.
So in theory you could enable the VIP on Master2, but leave Master2 read-only until it catches up fully with respect to replication. This at least performs part of the successover and allows clients to read data, but not update, temporarily. This might be acceptable for some apps for a short time, but it depends on the app's requirements.
In practice, our implementation of successover doesn't do this temporary read-only mode. We just try to be very reluctant to use the forced-successover option, because a split-brain is extremely difficult to clean up (it may not be possible). We'd rather try the successover when there is no replication lag.


Why do we still need VIP when using SCAN?

Clients connect to the scan listener(s) listening on the scan vip(s). The scan listener forwards the request to the appropriate local listener listening on the node vip. So the node vip is still used for connecting.

The listener must run on the node you end up connecting to spawn your server process. The scan listener running on rac1 can not spawn a process on node rac2. You need a local listener that listens on a local address. That local address, even if it is just virtual and not fixed, is the node vip.

Overview of Virtual IP Addresses

  A typical connect attempt from a database client to an Oracle RAC
  database instance can be summarized, as follows:

  The database client connects to SCAN (which includes a SCAN VIP on a public network), providing the SCAN listener with a valid service
  name.
  The SCAN listener then determines which database instance hosts this service and routes the client to the local or node listener on
  the respective node.
  The node listener, listening on a node VIP and a given port, retrieves the connection request and connects the client to the an
  instance on the local node.

The original point of having the vip was to provide the ability of quickly returning a response in case of a node failure.

If rac1 goes down and a connection attempt is initiated using its address, the client must wait for a tcp timeout before getting any response. If the connection attempt is made using a virtual address, even if the vip is running on rac2 and it does not serve requests, the client gets an immediate response and can go and try another address listed in its connection string, thus making the overall time to connect lower.

Overview of Virtual IP Addresses

  If a node fails, then the VIP address fails over to another node on
  which the VIP address can accept TCP connections, but it does not
  accept connections to the Oracle database. Clients that attempt to
  connect to a VIP address not residing on its home node receive a rapid
  connection refused error instead of waiting for TCP connect timeout
  messages.

Also some old clients are unable to use SCAN.


Update field in SQL Server Table Dynamicaly

If your table is test, try this:

SELECT t1.*
    ,New_FileName = t1.FileName + CASE
        WHEN t2.seq > 1
            THEN '_' + ltrim(str(seq))
        ELSE ''
        END
FROM test t1
JOIN (
    SELECT ID
        ,Memeber_Number
        ,FileName
        ,seq = ROW_NUMBER() OVER (
            PARTITION BY FileName
            ,Memeber_Number ORDER BY ID
            )
    FROM test
    ) t2 ON t1.id = t2.id;


Remove duplicate rows from select query using group by

take off shelf at the end of your group by

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site,  bldg, room

Note:
As mentioned in a comment the sites are different (and not highlighted in yellow), but I'm assuming you want newly grouped row output when that happens...

if not I would not include the site data:

select instance_oid, bldg, room, min(shelf) group by instance_oid, bldg, room


what is difference between oracle instant client and oci?

Oracle Instant Client is a light-weight software which helps to run OCI, OBDBC, Pro*C, JDBC without installing full Oracle client.  In other hand the Oracle Call Interface (OCI) is an application programming interface (API) that allows applications written in C to interact with one or more Oracle Servers.
For details:
What is Oracle's instant client and how do I install it?
Introduction to Oracle Call Interface


Postgres COPY with on conflict ignore - possible?

No.  Maybe in some future version.  Now about the best you can do is use file_fdw to map the file in as a foreign table, and then INSERT INTO...SELECT...ON CONFLICT DO NOTHING


'USE db_name' takes too long to change when login remotely

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the following parameter skip_name_resolve for testing to my.ini or my.cnf under [mysqld] section.


What is a statistical database?

At a high level, it's just a type of database that only stores statistical data. An example is a census database. Typically, access control for a pure SDB is straightforward: Certain users are authorized to access the entire database.

It's about the data not the engine

Keep in mind, this is about the data and not the type of relational database management system you chose such as MySQL, SQL Server, Oracle, etc. Any database can be a statistical database if you store statistical data in said database such as census information and so forth.

Typical structures for statistical databases

That being said, most statistical databases are Online Analytical Processing (OLAP) which is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations (e.g.: derived aggregated information and not derived individual information).

OLAP vs OLTP

Can statistical databases be created using SQL

Yep. Any database engine can be used to create a statistical database if the data is one that provides data of a statistical nature, such as counts and averages and the users accessing the database are querying aggregate, or statistical, data from said database as opposed to individual records on the users the statistics are ultimately based on.

Being most of the popular database engines use a form of SQL to get data to and from the underlying system, then the answer to the question is, "yes".

Why is individual information restricted?

This greatly depends on the business or organization using the statistical database for their needs. In general, individual records, what the statistics are based on are restricted for privacy reasons. For example, medical statistics based on medical records from the local  hospital.

Therefore, security is a big concern for statistical databases in order to help prohibit users from unearthing individual information from the use of aggregated statistical information.

Examples of how individual records can be compromised with statistics

  A statistical user of an underlying database of individual records is
  restricted to obtaining only aggregate, or statistical, data from the
  database and is prohibited access to individual records. The inference
  problem in this context is that a user may infer confidential
  information about individual entities represented in the SDB. Such an
  inference is called a compromise. The compromise is positive if the
  user deduces the value of an attribute associated with an individual
  entity and is negative if the user deduces that a particular value of
  an attribute is not associated with an individual entity. For example,
  the statistic sum(EE· Female, GP) = 2.5 compromises the database if
  the user knows that Baker is the only female EE student.

Hope that helps!


Managing the Service accounts in large environment

A separate AD service account for each service on each instance is the most secure model and if you have some good password management software it shouldn't be a problem. The work involved in creating all the service accounts and entering them into your password software will be quite time consuming depending on how big your environment is.

Some people sacrifice some security by using the same service account for all the services on a SQL instance. The problem with this is if the account gets locked out then all the services including the SQL service account will be locked out. This problem becomes even worse if you use the same AD account across SQL instances as one instance can be affected by another. Also obviously if an account is compromised, the more services it is used for then the more services that are compromised.


Can I change the default settings for SQL Server Replication

there is no default server configuration setting to achive this task. Replication is article based and that can done after creating replication.
you can add number of articles once by add dialog wizard and then enable property by "set properties of all table articles".

Make sure reinitialization of subscription is required after changing the property.


What is the difference between TCP port and TCP dynamic port?

The reason both work for you is due to the SQL Browser Service running in your services (start - run - services.msc).  The default instance of MS SQL Server will listen to port 1433, but named instances will use a 'dynamic' port and the SQL Server Browser service will map you to it.  This is not good in large environments where you have security concerns and want to keep using the same ports so you can configure your ACLs.

To test this, you will want to review the links at the bottom, which makes you remove the '0' in the dynamic ports in all of your network interfaces in SQL Server Configuration Manager, and then set a hard coded port in the 'port #' section at the very bottom.  You will then need to restart the MS SQL Server service and stop the SQL Server Browser service.

After doing this you can connect to the port number, NOT the instance name.  So for example it would change from:
SQLServer1\InstanceName

to:

SQLServer1,41450 (whatever custom port number you gave).

You can also make it pretty and clear by:

SQLServer1\InstanceName,41450 (include both the name and port # but it will always go to the port number, not the name).

Configure SQL Server To Listen In On  A Specific Port #

SQL Server Browser


sp_BlitzIndex v 4.5 (2016-11-15) failing with collation error

We've been working together on this error over here:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607

Turns out that the problem is contained databases. If you have a contained database, sp_BlitzIndex fails. (I've never actually seen a contained database in the wild before, so that's kinda amazing - wonder how long this has been broken.)


AlwaysOn Read Only Routing Configuration for Different Listeners

But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

There are two different things going on here that I believe are being confused. The first is dealing with data that has not yet been sent or received by the replica, the second is the data being available to be read by transactions because it has been redone and following the rules of snapshot isolation.

Synchronous commit does not guarantee that the data is immediately available, both sync and async must go through redo - and while sync commit does acknowledge when the data has been hardened on the remote replica, it does not mean the data is immediately available to be read - it still needs to go through redo and follow snapshot isolation requirements.

Really, data loss comes into play during a forced failover event - which should be very infrequent.

  I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the SQL Server load balance routing configuration for different listeners?

Read only routing is configurable per replica per availability group, it doesn't matter if there is one listener or three listeners.

To do this, you're going to want to use a hardware load balancer (or DNS round robin, etc.) to create these specific requirements


According to sp_executesql documentation you need a @ParamDefinition.

Declare @sql nvarchar(4000)
Declare @ParamDefinition nvarchar(400)
Declare @Name varchar(100)
Declare @NameID nvarchar(25)
SET @SQL = N'Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
SET @ParamDefinition = N'@Name varchar(100), @NameId nvarchar(25) OUTPUT'
EXEC sp_executesql @SQL, @ParamDefinition, @Name='Foxtrot', @NameID = @NameID OUTPUT;
Select @NameID

You can check it here: http://rextester.com/WTCOZ80449


What postgres sync solution should I use to duplicate production, but read it from another application?

It sounds like streaming replication with a hot standby is the solution you're looking for.

Streaming Replication details the steps of how you go about setting it up. The hot standby option that they go over (they have it put to "on" which is what you will want) will allow you to run SELECTS against the 2nd database. Under this setup, however, your production application would only write everything to the master database ... postgres itself would then stream those changes to the 2nd database.


There may be various causes for this difference, but the first thing that comes to mind is that the second configuration is tuned to use less memory (e.g. innodb_buffer_pool_size, tmp_table_size). Therefore, it may need to use more disk IO to process the same amount of data. CPU has to wait for disk operations to complete before it may proceed to the next chunk of data and hence it becomes underutilized if there are lots of them.

You may try to confirm this conjecture by establishing disk performance monitoring approximately like described in https://blogs.technet.microsoft.com/askcore/2012/03/16/windows-performance-monitor-disk-counters-explained/ .

And I would say 100% utilization of one CPU core is a normal thing with bulk operations in MySQL. The engine doesn’t have much parallelization so it generally can’t use more than one core per session. Lower utilization during bulk operations (e.g. mass insert) means there’s non-CPU bottleneck that needs addressing (because it makes the same operation take longer). You may try to figure out if that’s the case by comparing the time it takes the test script to complete in the two environments.


Why is the built-in distance function so much slower than my custom one?

SQL Server 2016 improved the perf somewhat, but the basic reason is the STDistance is accurate, but simpler formulas can be faster, if you understand the errors they produce.
See eg Geographic distance can be simple and fast, and a description of the algorithm used in STDistance:

This paper outlines some geometric algorithms that are used in
Microsoft SQL Server for processing geospatial data in a way that is
independent of any cartographic projection.

Geometric algorithms on an ellipsoid earth model
eg
declare @lat1 real = 33.1
declare @lon1 real =-117.1
declare @lat2 real = 39.6
declare @lon2 real =-98.7

declare @source geography = geography::Point(@lat1, @lon1, 4326);
declare @target geography = geography::Point(@lat2, @lon2, 4326);

declare @distance float
declare @i int = 0
declare @iter int = 1000 * 1000
declare @start datetime2

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

outputs
--------------------------
STDistance: 2367ms

---------------------------
calculation: 1098ms

--------------------------
STDistance: 2347ms

---------------------------
calculation: 1105ms


Does restoring a database break external synonyms targeting objects in that database?

This process should not invalidate the synonym. As per the docs:

  The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.

You can test this behavior with the following.

use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:\temp\Synonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:\temp\Synonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go


How can I choose the best autogrowth option for my new database?

I believe the log file is seeing that large size because during the backup from production the size there itself was 3.6 GB approx.

As mentioned below by experts, excluding the part of my answer recommending a fix size. Therefore,to analyse more on how to decide the size you may want to keep, check on the autogrow size of data and log file:

select DB_NAME(files.database_id) database_name, files.name logical_name,
CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],
[next_auto_growth_size (MB)] = case is_percent_growth
    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
end,
is_read_only = case is_read_only
    when 1 then 'Yes'
    when 0 then 'No'
end,
is_percent_growth = case is_percent_growth
    when 1 then 'Yes'
    when 0 then 'No'
end,
physical_name
from sys.master_files files
where files.type in (0,1)
and files.growth != 0

Also It is not recommended to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase.


Intellisense not working for one connection but works for others

Have you installed Visual Studio 2010 SP1?

If so, look at this Microsoft Support article:

FIX: The IntelliSense feature in SSMS 2008 R2 may stop working after you install Visual Studio 2010 SP1

The fix was first released in Cumulative Update package 7 for SQL Server 2008 R2 RTM. It is included in all later updates and service packs. If you have indeed applied Service Pack 3 for SQL Server 2008 R2, you already have this fix.


Postgresql vs MySQL - Which is better for join queries & writing data(inserts)

The databases are different.  In general, the answer depends very much on your specific queries.  As far as a general use case, I would expect PostgreSQL to perform better for a few reasons, but there are cases where I would expect MySQL to perform better.

In PostgreSQL all tables are heap tables.  In MySQL all innodb tables are btree indexes with the tuple in the payload.  This means that primary key lookups are faster on MySQL but general queries are faster on PostgreSQL.  It also means you typically need more indexes on MySQL which will slow down writes.

For example, the following query I would expect to perform better on MySQL than PostgreSQL:

SELECT u.username, p.*
  FROM users u
  JOIN preferences p ON u.id = p.user_id
 WHERE u.id = 123;

Provided that both tables share the same primary key (u.id and p.user_id), there are thousands of rows in both tables, and so forth.

On the other hand, I would expect the following query to perform better on PostgreSQL than MySQL in a db too big to fit in memory, non-cached data, proper indexes, decent-sized tables, etc:

SELECT c.legal_name, a.*
  FROM company c
  JOIN address a on a.company_id = c.id
 WHERE a.zip_code like '95%' and country = 'us';

In this case you are having to utilize other indexes, which means a lot of extra random disk I/O on MySQL.

The second issue I would expect would be write performance.  I would expect PostgreSQL to generally win here due to the fact that heap tables allow inserts wherever it is convenient, and fewer indexes maintained would be helpful too.


Filtering down specific criteria for all rows and return only one row for each column with the same value

Must be missing something, but isn't this simply what you want?

SELECT house_lease_id, MIN(date_start)
FROM house_lease_terms
WHERE date_end > NOW() OR date_end IS NULL
GROUP BY house_lease_id

The earliest term that hasn't ended yet.


Can I find all JSON scalars in PostgreSQL 9.3?

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column?
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.


Trigger actions on database creation or attach

You didn't copy the full example and are concatenating NULL into the SQL command string which causes it to be NULL.  If you do this:

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
        , @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
        , @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');

    IF @DatabaseName = 'MyTestDatabase'
    BEGIN

    SET @SQL = '
        USE ' + @DatabaseName + ';
        EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
        EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';

    -- PRINT @SQL;
    EXEC (@SQL);
    END

GO

CREATE DATABASE MyTestDatabase;
GO

SELECT name, value
FROM MyTestDatabase.sys.fn_listextendedproperty(default, default, default, default, default, default, default)

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE MyTestDatabase;
GO

It works correctly on my system.


Map auto increment ids on multi-row insert

Don't use MAX(id), that could be wrong if to another connection doing inserts.  last_insert_id, on the other hand, is local to the session, so it is unaffected by other connections.

INSERT INTO animals (name)
    VALUES  ('dog'),('cat'),('cow');
SELECT last_insert_id();  -- Let's say you get 34
-- Then dog=34, cat=35, cow=36

But -- beware of innodb_autoinc_lock_mode -- the setting of this can affect what I discussed above.  This is especially true if, for whatever reason, some of the rows fail to insert.  (Example: you have UNIQUE(name) and already have cat in the table.)  In at least one of the modes, 35 is 'burned'.

The following goes beyond what you have asked for...

I decided is the best way to deal with a similar problem involves two steps.  First, let me explain what is going on.  I am 'normalizing' the names of animals.  Instead of using "dog", I want to use the id associated with dog.  I have lots of data coming in, and I want to batch insert a bunch of animal names into the table, plus get the ids for them.  I am very likely to have lots of dups, but I don't want to 'burn' an id for every dup.

So...

INSERT ... SELECT ... LEFT JOIN ...  -- insert only the 'new' animals.
UPDATE ... JOIN ...  -- pull the ids into another table.

The details are spelled out here .


Selecting top 10 from indexed field of a big table takes too long

In your sample code above, the index is explicitly created as NULLS LAST and the query is implicitly running NULLS FIRST (which is the default for ORDER BY .. DESC)  so PostgreSQL would need to re-sort the data if it used the index. As a result the index would actually make the query many times slower than even the (already-slow) table scan.

rds-9.6.5 root@db1=> create table performance (id integer, installs integer, hour timestamp without time zone);
CREATE TABLE
Time: 28.100 ms

rds-9.6.5 root@db1=> with generator as (select generate_series(1,166530) i)
[more] - > insert into performance (
[more] ( >   select
[more] ( >     i id,
[more] ( >     (random()*1000)::integer installs,
[more] ( >     (now() - make_interval(secs => i))::timestamp installs
[more] ( >   from generator
[more] ( > );
INSERT 0 166530
Time: 244.872 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour desc nulls last);
CREATE INDEX
Time: 67.089 ms

rds-9.6.5 root@db1=> vacuum analyze performance;
VACUUM
Time: 43.552 ms

We can add a WHERE clause on the hour column so that using the index becomes a good idea - but notice how we still need to re-sort the data from the index.

rds-9.6.5 root@db1=> explain select hour from performance where hour>now() order by hour desc limit 10;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit  (cost=4.45..4.46 rows=1 width=8)
   ->  Sort  (cost=4.45..4.46 rows=1 width=8)
         Sort Key: hour DESC
         ->  Index Only Scan using hour_idx on performance  (cost=0.42..4.44 rows=1 width=8)
               Index Cond: (hour > now())
(5 rows)

Time: 0.789 ms

If we add an explicit NULLS LAST to your query then it will use the index as expected.

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc NULLS LAST limit 10;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.526 ms

Alternatively, if we drop the (non-default) NULLS LAST from your index then the query will use it as expected without modification.

rds-9.6.5 root@db1=> drop index hour_idx;
DROP INDEX
Time: 4.124 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour desc);
CREATE INDEX
Time: 69.220 ms

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc limit 10;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.725 ms

Note that you can also drop the DESC from your index; PostgreSQL can scan indexes both forwards and backwards and on single-column indexes it's generally unnecessary to reverse them.  You only need to be careful about having the right combination of order and nulls first/last.

rds-9.6.5 root@db1=> drop index hour_idx;
DROP INDEX
Time: 3.837 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour);
CREATE INDEX
Time: 94.815 ms

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc limit 10;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan Backward using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.740 ms


OK, so I found AN answer eventually, though I'd quite like a more elegant solution.

What I'm doing is performing an iif statement to check whether there are any [Person].[Person] children available in the current context. If there aren't, then assume we're looking at this by individual [Person].[Person] members and use currentmember instead.

The tuple formula looks like this:

AVG(iif(Count([Person].[Person].children, EXCLUDEEMPTY) = 0,
    [Person].[Person].currentmember, [Person].[Person].children),
    [Measures].[Hours])


SQL Server - Physical Table Design - Column Order - Null and Non Null Columns

I am posting this here as an answer so that I have more room to post resources and links, as well as explain a bit.

To answer the specific question about NULLABLE vs NON-NULLABLE columns and their relative cardinality, Kimberly Tripp has an article here: https://www.sqlskills.com/blogs/kimberly/column-order-doesnt-matter-generally-but-it-depends/

Unfortunately I cannot find the reference that supports my statement about having variable width columns at the end of the column order list, but the reasoning was that updating a variable width column at the end of a row would be less likely to cause fragmentation (and off-page spill) than one in the middle of the column order.

But as I've previously said, these are hard to get right and unless you know exactly your data access patterns you will probably get it wrong.  There are many other low hanging fruit to tune over worrying about column order.  Switching to properly sized static column lengths would be an easier way to get better memory estimates and avoid fragmentation.

Indexes with proper fill factors, statistic updates, stored procedures for data access (so you can more tightly control execution plans) and more.  If you are on enterprise edition then you have more options, table compression will allow you to fit more data into memory and that's a far easier (and more widely applicable), partitioning will allow you to do really cool things on management side (although using it to accelerate your reads is a challenge (partition elimination is another hard thing to get right)).

Note, when I say hard to get right, I usually mean that it's hard to get SQL to use them to best advantage, so getting ROI for time spent optimizing is usually much better spent elsewhere.

To answer the last section of the question in the EDIT section.  Yes, pre-loading the column with primary keys, foreign keys and the other join columns would probably help.   But if you have indexes on those then it shouldn't matter as the index will be used to determine which rows to retrieve and then a keylookup performed.  I just don't think spending architecture time on it is worth the impact.  You are probably going to have an index on those columns in any case.


Auditing - sp_send_dbmail from server-level triggers failed

Well, the ideal solution would be to implement module signing along the lines of:

Create a Certificate in master
Sign the DDL Trigger with the Certificate
Create that same Certificate in msdb
Create a User in msdb from that Certificate
Add that Certificate-based User to the DatabaseMailUserRole Database Role

Unfortunately, however, it seems that Database-scoped Triggers (DDL) and Server-scoped Triggers (DDL and Logon) cannot be signed (so says Microsoft in this MSDN Forum thread: Error Signing a DDL Trigger).

But, I did find that simply moving the EXEC msdb.dbo.sp_send_dbmail logic into a stored procedure in master allows this to work. Apparently, both master and msdb are both, by default, enabled for Cross-Database Ownership Chaining, and a stored procedure is Schema-scoped, unlike a DDL Trigger. So, as long as the stored procedure in master is in the dbo Schema, it should work. You will just need to GRANT EXECUTE on this stored procedure to those same folks who you made members of the dbcreator fixed server role.

I have created a stored procedure and modified the DDL trigger to call the stored procedure as follows. Please note that I changed the variable datatypes to NVARCHAR and sysname as all of the values are truly NVARCHAR.

Stored Procedure

USE [master];
GO
CREATE PROCEDURE dbo.SendDdlEmail
(
  @EventData XML
)
AS
SET NOCOUNT ON;
DECLARE @Results NVARCHAR(MAX),
        @SubjectText NVARCHAR(MAX),
        @DatabaseName sysname;

SET @SubjectText = N'New database created - ' + @@SERVERNAME + N' by ' + ORIGINAL_LOGIN();

SET @Results =
  @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', N'NVARCHAR(MAX)');

SET @DatabaseName = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');

EXEC msdb.dbo.sp_send_dbmail
  @recipients = 'DBA_GROUP@SomeCompanyName.com',
  @body = @Results,
  @subject = @SubjectText,
  @exclude_query_output = 1;
GO

GRANT EXECUTE ON [dbo].[SendDdlEmail] TO [xxxxxxxxxxxxx];
GO

Trigger

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON;

DECLARE @EventData XML;
SET @EventData = EVENTDATA();

EXEC [master].[dbo].[SendDdlEmail] @EventData;
GO

FYI: I just added a Microsoft Connect Suggestion requesting the ability to sign non-Schema-scoped Triggers:

Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE


How do automaticly delete rows from other tables

Whatever you are trying to do can easily be done by

USING ON DELETE CASCADE AND ON UPDATE CASCADE(IF Required)

While creating the foreign keys on your table.

For your reference have a look at FOREIGN KEY Constraints.


So I tweaked the code a bit by testing it in phpMyAdmin, and this is what worked:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName`
FROM `stocks`
GROUP BY `stock_ticker`, `stock_simpleName`
ORDER BY CASE
    WHEN `stock_ticker` LIKE 'MO%' THEN 1
    WHEN `stock_simpleName` LIKE 'MO%' THEN 2
    ELSE 3
END
LIMIT 5

I just removed the WHERE Statement entirely. It works exactly like I needed it to. I thought WHERE was always required for any statement that succeeds the FROM table.column part of the query. `


Unknown source of high number of connections

Sounds like a hacker.
Based on your graphs, I suggest that max_connections = 4000 is unreasonably high.  It looks like 20 might be reasonable.
You might want to set max_user_connections to some reasonable value (instead of 0 for "unlimited").


Strange behaviours with SET options in SSMS

Among the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time.

From SET Statements (Transact-SQL) (emphasis mine):

  Considerations When You Use the SET Statements

  All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER. These statements are implemented at parse time.

Confirmed in SET QUOTED_IDENTIFIER (Transact-SQL):

  Remarks

  […]

  SET QUOTED_IDENTIFIER is set at parse time. […]

So, in your case QUOTED_IDENTIFIER gets reset at parse time and is, therefore, OFF by the time CREATE INDEX is executed. The engine complains about it as expected.

To resolve this, you don't have to resort to dynamic SQL, it is enough to put a GO line just after the CREATE INDEX statement to make it a different batch from the subsequent SETs, which would cause it to be parsed separately from them:

…

CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

GO

-- make SET options the opposite of what is required
SET ANSI_NULLS    OFF;
SET ANSI_PADDING  OFF;
SET ANSI_WARNINGS OFF;
SET ARITHABORT    OFF;
SET CONCAT_NULL_YIELDS_NULL   OFF;
SET NUMERIC_ROUNDABORT    ON;
SET QUOTED_IDENTIFIER OFF;

…

Note, however, that SET Statements (Transact-SQL) also includes the following (emphasis mine):

  Considerations When You Use the SET Statements

  […]

  When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

  If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

Which means that the uncommented SET statements would still break your script, only not at CREATE INDEX but at INSERT.


How should we give privileges/permissions to group to access different db's

Here is one thing, there is no difference between regular PostgreSQL and RDS PostgreSQL when it comes to ROLES,GRANT, PRIVILEGES, and GRANT. In your case, the user must be connecting to other than y DB. Maybe he connection default DB Postgres.  Tested in my rds instance.

GRANT USAGE ON SCHEMA public TO dev_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

with this working fine for me, ref.docs:

y=> \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | table1 | table | postgres
 public | table2 | table | postgres
(2 rows)

and when I connected to other than y DB,

postgres=> \dt
No relations found.

Since you didn't give permission here.


There is no reason why Oracle would perform badly on Ubuntu (though Oracles OEL kernel has some Oracle-specific optimisations), it's all about enterprise support.

Certification is an expensive and time-consuming task, as is training support workers in each flavour of Linux - that's why the RDBMS is only officially supported on a select number of releases.


Why the owner of a database can't alter the tables of this database that are owned by other owners?

It is a security consideration. One way to get around it is to have all objects created by specific user(s) have default rights.

In my opinion you should limit what user(s) can do DDL in a database for a number of reasons. Primarily for security and controlling who/what user can do DDLs. For a small company / personal setup may not matter, but the second you have multiple people working on a DB can get pretty chaotic if you don't control it.


Getting unique names when the IDs are different (DISTINCT ?)

Here is the query

SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;

Here is some sample data

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('John'),('John'),('Peter'),('Mike'),('Mike');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | John  |
|  3 | Peter |
|  4 | Mike  |
|  5 | Mike  |
+----+-------+
5 rows in set (0.00 sec)

mysql> SELECT id,name FROM
    -> (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
    -> ORDER BY id DESC LIMIT 2;
+------+-------+
| id   | name  |
+------+-------+
|    5 | Mike  |
|    3 | Peter |
+------+-------+
2 rows in set (0.00 sec)

mysql>


Creating a mongoDB backup/dump on remote server via local CMD/Shell

On Linux and Mac, arguments for command-line tools normally have one of these two formats:
--option1 value1 or  -o value1.

On Windows, the standard is different:
/option1 value1 or /o value1.

mongo, mongodump and other command-line MongoDB programs actually preserve the Linux way of passing arguments. But the Windows cmd.exe shell will still recognize the character / as a beginning of a command-line option.

Also, in UNIX systems, / is used to separated parts of a file system path, e.g. /data/db. On Windows, \ is used instead: C:\Data\DB.

Your problem is this part of the command: --out /backup/mongodump-2015-11-13. You kept the backup path in the UNIX format, and / was recognized as a starting character of a command-line option.

You should specify a real file system path on your hard drive instead. For example:

--out C:\backup\mongodump-2015-11-13

Also, your data path (C:\Program Files\MongoDB\Server\3.0\bin) contains a space. Because of the space, cmd.exe will treat C:\Program and Files\MongoDB\Server\3.0\bin as separate parameters. To prevent this, surround the path with quotes:

-d "C:\Program Files\MongoDB\Server\3.0\bin"

And as a final note, do not use the bin folder to store your data files (as you seem to be doing judging by the path you're passing to -d).


Approach to Handling Errors/DataCorrections in Aggregated Data

In accounting practice values are never altered once written. Instead secondary compensating transactions are created. In your circumstances this would mean one new row with a value that is the negative old, incorrect value and a second new row with the new, correct value.

To work each data row will need its own datetime column so these compensating transactions can be post-processed into the correct buckets.

Averages cannot be altered in post processing. Instead the sum and count are stored and average calculated at run time.


How do I collapse schemas in SQL Server Management Studio?

I did a bit of hunting after @LowlyDBA gave his answer, and came across an extension to SSMS that does exactly what I was looking for!

SSMS Schema Folders


From the same documentation:

  Beginning in SQL Server 2014, readable secondary replicas can remain
  online even when the primary replica is offline due to user action or
  a failure. However, read-only routing does not work in this situation
  because the availability group listener is offline as well. Clients
  must connect directly to the read-only secondary replicas for
  read-only workloads.

So, you can go to 2014 because the article exists only for 2016.


How to perform builk update within mysql stored procedure

Assuming that _batch_request_detail_ids = "1,2,3", try this :

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
     /**************** Set statement added***********/
    SET @sql =concat("UPDATE batch_requests_detail
    SET processing_status = ",_processing_status,"
    WHERE batch_requests_detail_id  IN (",_batch_request_detail_ids,");");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    END$$

DELIMITER ;


Seeking help to test a use case on sql server memory

SQL Server does not page, not in the sense the OS pages.

Data does not need to be paged becase the memory is just a cache of the data files. Data can simply be removed from memory and read back when needed.

Caches (eg. Procedure cache) are not paged either. Entries are removed and, if needed, they can be recreated from scratch (eg. compile).

The closest thing to paging are spills. These are operators that need intermediate data that doesn't fit in memory. They spill to tempdb. The typical example is the sort operator.


Find and return items based on matching strings across 17 tables

Is the application going to search for these one by one?  Is the performance of this query important?  Easy (ish) solution would be to create a stored procedure that accepted some arguments and then returned the results from searching each table.

Results from the stored procedure could include the table it found a match on, the primary key and the field that matched.  Then the caller of that stored procedure could do more work based on that result set.

EDIT - Below is a very basic sample procedure for you.

CREATE PROCEDURE dbo.sp_FindCircuit
    (
    @Search NVARCHAR(100)
    )
AS
BEGIN

--Hold Results.
DECLARE @Results TABLE
    (
    TableName VARCHAR(100) NULL
    , FieldName VARCHAR(100) NULL
    , KeyValue INT NULL
    )

INSERT INTO @Results
        (TableName, FieldName, KeyValue)
SELECT 'Table 5'
    , 'Circuit ID'
    , <PrimaryKey>
FROM [Table 5]
WHERE CircuitID LIKE ('%' + @Search + '%')

--Repeat for each table.

--Return data.
SELECT TableName, FieldName, KeyValue
FROM @Results

END


Is there any way to retrieve "Messages" in SQL Server through JDBC?

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that.

In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop:

Statement stmt = ...;
stmt.execute("some_procedure");

SQLWarning warning = stmt.getWarnings();

while (warning != null)
{
   System.out.println(warning.getMessage());
   warning = warning.getNextWarning();
}

I have seen some messages also being returned on the Connection instance rather than the Statement. If you don't see anything, try the above code with your Connection instance.


SYSDBA privileges of viewing users table

This is what Oracle Database Vault was made for.

http://www.oracle.com/us/products/database/options/database-vault/overview/index.html

Note that, it is an extra cost option for Enterprise Edition.


mysql logging activity from specific user or ip

You could use the general log. In fact, you should try using the MySQL table version of the general log.

If you run this:

SHOW CREATE TABLE mysql.general_log\G

You should see something like this:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.14 sec)

mysql>

The user_host column will record the MySQL user and the IP Address/DNS Name the command came from.

You are probably saying right now, "That table is a CSV file. I would have to parse it." That's true, you would have to. However, did you know that you can convert it to a MyISAM table? I have actually tried this out for one of my employer's DB Hosting Clients, and I wrote a post explaining it (See the Feb 24, 2011 post down below).

Here are the basic steps:

Make the mysql.general_log table MyISAM

Run the following:

CREATE TABLE mysql.general_log_original LIKE mysql.general_log;
ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Enable the general log

Add the following to /etc/my.cnf

[mysqld]
log-output=TABLE
general-log

If you also want the text version of the general log, add this:

[mysqld]
log-output=TABLE,FILE
general-log
general-log-file=/var/log/mysql_general.log

Restart mysql

Simply run service mysql restart

I have many posts about using the general log and the slow log as MyISAM tables:

Jan 22, 2013 : How often does the slow_log table get updated in MySQL?
Nov 10, 2012 :  Redirecting MySQL errors and slow logs into another database
Jan 08, 2012 : How to enable MySQL general log?
Dec 27, 2011 : How to do MySQL User Accounting
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?
Feb 24, 2011 : Audit logins on MySQL database

CAVEAT

MySQL 5.5.28 has recently introduced the audit log plugin and how to install it.

Here are the options

audit-log-buffer-size
audit-log
audit-log-file
audit-log-flush
audit-log-policy
audit-log-rotate-on-size
audit-log-strategy

These are new to me so I have nothing pros or cons at this time on the Audit Log Plugin.