Powershell function to drop table(s) from SQL Server database

Here's how I'd go about this:

function Get-IfSqlTableExists {
    param (
        [Parameter(Mandatory = $true)]
        [string]$SqlServerName,

        [Parameter(Mandatory = $true)]
        [string]$DatabaseName,

        [Parameter(Mandatory = $true)]
        [string]$TableName
    )

    $ConnectionString = "data source = $SqlServerName; initial catalog = $DatabaseName; trusted_connection = true;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandText = "select count(*) from sys.tables where object_id = object_id(@table_name);"

    $TableNameParam = New-Object System.Data.SqlClient.SqlParameter("@table_name", [System.Data.SqlDbType]::NVarChar, 512)
    $TableNameParam.Value = $TableName
    $SqlCmd.Parameters.Add($TableNameParam) | Out-Null

    try {
        $SqlConnection.Open()
        $Return = $SqlCmd.ExecuteScalar()
    }
    finally {
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }

    if ($Return -eq 1) {
        return $true
    }
    else {
        return $false
    }
}

function Delete-SqlTable {
    param (
        [Parameter(Mandatory = $true)]
        [string]$SqlServerName,

        [Parameter(Mandatory = $true)]
        [string]$DatabaseName,

        [Parameter(Mandatory = $true)]
        [string]$TableName
    )

    if (!(Get-IfSqlTableExists -SqlServerName $SqlServerName -DatabaseName $DatabaseName -TableName $TableName)) {
        Write-Warning "$TableName doesn't exist in $DatabaseName on $SqlServerName"
        return
    }

    $ConnectionString = "data source = $SqlServerName; initial catalog = $DatabaseName; trusted_connection = true;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    # commented out the actual drop table command text to
    # prevent accidental running
    #$SqlCmd.CommandText = "drop table $TableName;"

    try {
        $SqlConnection.Open()
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    finally {
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }

    Write-Verbose "Successfully deleted $TableName in $DatabaseName on $SqlServerName"
}

$SqlServerName = "localhost\sql2012"
$DatabaseName = "TestDatabase"
$TableNameList = "table1", "table2", "table3", "notarealtable", "table4"

foreach ($TableName in $TableNameList) {
    try {
        Delete-SqlTable -SqlServerName $SqlServerName -DatabaseName $DatabaseName -TableName $TableName -Verbose
    }
    catch {
        Write-Error "Error attempting to drop table $TableName in $DatabaseName on $SqlServerName"
        # or handle however you want
    }
}

It looks like a lot of code, but it would scale.  It does things like checking to see if the table actually exists before attempting to delete.

My output on my test instance (with verbose output) looks like this:

VERBOSE: Successfully deleted table1 in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table2 in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table3 in TestDatabase on localhost\sql2012
WARNING: notarealtable doesn't exist in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table4 in TestDatabase on localhost\sql2012

You could (and should) form this type of logic in any way you want.  This is just one approach with a style I typically like to take.

Note: I commented out the actual DROP TABLE command from the function to prevent accidental running.


The alert log never has been under ORACLE_HOME/log.

It is in $ORACLE_BASE/diag/rdbms/<sid>/<sid>/trace and is named alert_<sid>.log

If you don't have $ORACLE_BASE, this typically is equivalent to $ORACLE_HOME/../../..

This is no different in 12c than it was in 11g (or 10g if I recall correctly).

You can also use the ADRCI tool: http://docs.oracle.com/database/121/SUTIL/adrci.htm#SUTIL1474


Since you only want to fire the trigger if certain fields are updated, you will probably want to wrap the content of the trigger with

IF UPDATE([field name])
BEGIN
[trigger actions here]
END

That at least will limit the number of executions.  I agree with the cautious statement by @LowlyDBA but since this will only fire 20 or so times a day, it's doubtful it would be a problem.

Once you've avoided unnecessary executions, I believe simplifying rest of the email contents in your SET statement can be resolved using a case statement.  Something like

CASE WHEN i.value = d.value THEN '' ELSE 'email string value' END


SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x

The way the cardinality estimation is derived certainly seems counter-intuitive to me. The distinct count calculation (viewable with Extended Events or trace flags 2363 and 3604) is:

Notice the cap. The general logic of this seems very reasonable (there can't be more distinct values), but the cap is applied from sampled multi-column statistics:

DBCC SHOW_STATISTICS
    (BigFactTable, [PK_BigFactTable])
WITH
    STAT_HEADER,
    DENSITY_VECTOR;

That shows 2,980,235 rows sampled out of 3,439,431,721 with a density vector at the Col5 level of 3.35544E-07. The reciprocal of that gives a number of distinct values of 2,980,235 rounded using real math to 2,980,240.

Now the question is, given sampled statistics, what assumptions the model should make about the number of distinct values. I would expect it to extrapolate, but that isn't done, and perhaps deliberately.

More intuitively, I would expect that instead of using the multi-column statistics, it would look at the density on Col5 (but it doesn't):

DBCC SHOW_STATISTICS
    (BigFactTable, [_WA_Sys_00000005_24927208])
WITH
    STAT_HEADER,
    DENSITY_VECTOR;

Here the density is 9.266754E-10, the reciprocal of which is 1,079,126,528.

One obvious workaround in the meantime is to update the multi-column statistics with full scan. The other is to use the original cardinality estimator.

The Connect item you opened, SQL 2014 sampled multi-column statistics override more accurate single-column statistics for non-leading columns, is marked Fixed for SQL Server 2017.


Postgres plpgsql - Using a variable inside of a dynamic create statement

In addition to what @filiprem wrote, here is how you do this properly:

...
DECLARE
   tbl_var text := 'myTable';   -- I would not use mixed case names ..
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tbl_var) || '(
   foo integer NOT NULL,
   bar text NOT NULL)';
...

Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.


Validate application to access the database

No matter what safeguards you put in place on the server side, it will always be possible for a client to fake a connection. This is an inherent limitation of the client-server model - the server can only gather information about the client by querying the client, and the client can always be configured to lie.

You cannot completely protect against this without the use of middleware. If you only allow the middleware to connect directly to the database e.g. by ip blocking, then you can have complete control of what commands can and can't be sent to the database.


Absolutely, when you run setup for SQL Server 2008 Express choose the instance you want to upgrade. By doing so you only upgrade that instance to SQL Server 2008 R2. Other instances will stay on 2005 however any client tools like Management Studio Express will be upgraded to 2008 R2.


Latency appear to be an issue with linked server

The queries are simple selects from single tables.

There are a number of details that you need to include that may have an effect here. Are they many small resultsets (a few rows or small rows) or small numbers of large results (many rows and/or large columns like BLOBs)? If it is a mix can you separate them out and test the performance separately?

How are you making SELECTS to the remote end? Direct inserts into a local table with something like INSERT localtable SELECT stuff FROM server.db.schema.table or are you reading the results into your own code for further processing before INSERT? If the latter than you might need to look into which cursor options you (or the framework you are calling through) are using and see if there are less latency sensitive options that are applicable (assuming the framework you are using allows you this level of control). If the options used mean that each row is request separately then latency will be far more of an issue then if they allow the rows to be read over the network in bulk and navigated locally.

  Setting this up in SSIS improves performance to an acceptable level

If you are using exactly the same statements to retrieve the data in SSIS as your previous method then that could imply one of three things, or a mix of them:

The cursor options as mentioned above (SSIS is more likely to be favour latency friendly options which may in other circumstances be sub-optimal, because it is more often used to work with more widely distributed servers)
Connection pooling which your current method might not be making as much advantage of (again, here we need more details to do more than guess). SSIS will use the same connection rather than renegotiating for each query, your other process may not be doing this.
Possibly concurrency: is your workflow in SSIS allowing it to submit several queries at the same time, so it can submit new queries to the remote end without first having to wait for the previous ones to complete, where the workflow in your current process is entirely sequential?


Go into my.cnf (or my.ini).  Increase (or set) max_user_connections in the [mysqld] section.

Also check max_connections; the 197 may be bumping up against that.

Do you really have a single user connecting more than 40 times?  Or is the "connection pooling" inflating things?


How to access different MySQL databases in a same instance in Amazon RDS?

Well, I was living in a lie.

The simple reason why I couldn't access my table on RDS was because RDS runs on Linux and MySQL table names are case-sensitive on Linux, while my local DB runs on Windows where MySQL table names are not case-sensitive.

About multiple databases in a single RDS instance: RDS does accept multiple databases. It's just that, for an unknown reason, only the initial database of the instance is always displayed in the dashboard. This still doesn't make sense to me because this database has nothing more than other ones, apart being the first one.


select customer_id,sum,case
when pct_row<=0.10 then 1
when pct_row>0.10 and pct_row<=0.20 then 2
when pct_row>0.20 and pct_row<=0.60 then 3
when pct_row>0.60 then 4
end as customer_label from (
select customer_id,sum,(@curRow := @curRow+1)/c as pct_row
from temp t
jOIN (SELECT @curRow := 0) r
JOIN (SELECT @curRow2 := 0) r2
join (select count(*) c from temp) s
order by sum desc) p;

sqlfiddle :- here

I don't know whether this is efficient method or not but work fine for small data set.


Disable autocommit for DB2 connection in Oracle SQL Developer

Check whether auto-commit is really off (or if it's a glitch in the gui), by (for example):

1. create table T (x int);
2. rollback;
3. select * from t;   -- should result in an error


Using a Having clause with an outer join

This is not my forte, but I am sure that you need a GROUP BY in there.

Also, out the ORDER BY line at the end of your statement.  Something like this :

SELECT
  I.CARD_BIN BIN
FROM
  GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION P ON I.CARD_BIN = P.CARD_BIN
LEFT OUTER JOIN GE_PAYEE PP ON PP.PAYEE_CODE = P.PAYEE_CODE
group by I.CARD_BIN
HAVING COUNT(*) > 1
ORDER BY I.CARD_BIN;


(mySQL Aggregate function, JOINS, and GROUP BY) sakila database project

The problem is you are joining unrelated tables.  If you don't join them, as in

SELECT NOW() AS 'Dorjan Masrussen',
        s.store_id,
        (select sum(p.amount)
           from payment p
           where p.rental_id in (
             select r.rental_id
               from rental r
               join inventory i
                 on i.inventory_id = r.inventory_id
               where i.store_id = s.store_id)) total_sales,
        (select count(*)
           from customer c
           where c.store_id = s.store_id) num_customers,
        (select count(*)
           from inventory i
           where i.store_id = s.store_id) count_inventory,
        (select count(distinct film_id)
           from inventory i
           where i.store_id = s.store_id) count_titles,
        (select sum(f.replacement_cost)
           from inventory i
           join film f on f.film_id = i.film_id
           where i.store_id = s.store_id) inventory_cost,
        (select count(*)
           from rental r
           join inventory i
             on i.inventory_id = r.inventory_id
           where i.store_id = s.store_id) num_rentals_out
  from store as s

you get:

+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| Dorjan Masrussen    | store_id | total_sales | num_customers | count_inventory | count_titles | inventory_cost | num_rentals_out |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| 2020-03-31 17:32:18 |        1 |    33679.79 |           326 |            2270 |          759 |       46205.30 |            7923 |
| 2020-03-31 17:32:18 |        2 |    33726.77 |           273 |            2311 |          762 |       46415.89 |            8121 |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+

Total sales and num_rentals_out differ from your correct output.  I wonder who is right.   The Sakila structure diagram, if I'm not mistaken, has it's crow's feet inverted.

The same result can be obtained, while querying inventory  only once, using a CTE, the Common Table Expression "WITH", as follows:

WITH idata as (
  select i.store_id,
         count(*) num,
         count(distinct i.film_id) num_films,
         sum(f.replacement_cost) repl_cost,
         sum((select count(*)
                from rental r
                where r.inventory_id = i.inventory_id)) num_rentals,
         sum((select sum(p.amount)
                from payment p
                where p.rental_id in (
                  select r.rental_id
                    from rental r
                    where r.inventory_id = i.inventory_id
                ))) sum_rentals
    from inventory i
    join film f on f.film_id = i.film_id
    group by i.store_id
)
SELECT NOW() AS 'Dorjan Masrussen',
        s.store_id,
        idata.sum_rentals total_sales,
        (select count(*)
           from customer c
           where c.store_id = s.store_id) num_customers,
        idata.num count_inventory,
        idata.num_films count_titles,
        idata.repl_cost inventory_cost,
        idata.num_rentals num_rentals_out
  from store as s
  join idata on idata.store_id = s.store_id

Querying the store table can also be dropped, since all we need is the store_id, which we also find in inventory.


Optimal drive configuration for SQL Server 2008R2

Variants of this question come up semi-regularly:

SQL Server build
Help me choose a RAID level combination for a SQL Server 2008 instance
How should I configure these disks on a SQL Server for a BI configuration?

There are also occasional bun fights about the data/log separation "best practice".

Placing Transaction Log on Separate Volume [solid state]?
SQL Server - Separating data, log, and TempDB files on a SAN

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

RAID 1 for OS
RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.


Migrating from MongoDB Atlas to self managed Mongo (EC2) server

These appear to be database files, if you move them to the Mongodb data path, then start the mongod process/service, should work.

If the database is not inside sharded clusters, you can use mongodump/mongorestore.


Compare Two Rows and Find the Difference Between Them Which is In The Second Column?

You could rephrase it like this:

  Return every row for which another row exists with the same Name and a different LastName

which is very easy to translate to SQL if you are familiar with the EXISTS predicate:

SELECT
  *
FROM
  YourTable AS t1
WHERE
  EXISTS
  (
    SELECT
      *
    FROM
      YourTable AS t2
    WHERE
      t2.Name = t1.Name
      AND t2.LastName <> t1.LastName
  )
;


SQL Server 2000 to SQL Server 2008 linked server issue

I've had this problem before, and while I didn't find a fix (as it appears from the error message, SQL Server seems to 'lose' the brackets when it's processing the query), I did find a couple of workarounds:

Using OPENQUERY will allow you to use the linked server name as-is.
(My preferred solution): create a synonym for the linked server and use that in your query:

CREATE SYNONYM MyLinkedTable FOR [DBNAME.DATABASE.ORGANIZATION.COM].DBName.schema.TableName

Then use it in place of that huge mess:

SELECT * FROM MyLinkedTable


Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query

You can use RIGHT either inside your CASE statement or outside it, depending on whether you want to retain the NULL value when ':' isn't found in the string.

Inside CASE

SELECT
  CASE
    WHEN CHARINDEX (':', Ref) > 0 THEN RIGHT('0000' + (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1)), 4)
    ELSE NULL
  END
FROM Data

Outside CASE

SELECT RIGHT('0000' + ISNULL(
  CASE
    WHEN CHARINDEX (':', Ref) > 0 THEN (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1))
    ELSE NULL
  END, ''), 4)
FROM Data

The difference here is that NULL values from the CASE statement are replaced with four zeroes in the Outside CASE example, but in the Inside CASE example they remain NULL. You can see the different results in this db<>fiddle.


No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL:

DECLARE @sql nvarchar(max) = N'',
        @cmd nvarchar(550) = N'ALTER LOGIN $$$ DISABLE;'
                           + CHAR(13) + CHAR(10);

SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(userid))
  FROM dbo.tbl_user;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

When you're happy with the output, uncomment the EXEC.

Note that if you have a lot of users in that table, you won't be able to see the whole command using PRINT, since there are limitations in output length. See this tip for workarounds.

Also, there is no error handling here, so you should probably actually either add TRY/CATCH or join to sys.server_principals to make sure you are disabling logins that actually exist:

SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(p.name))
  FROM dbo.tbl_user AS u
  INNER JOIN master.sys.server_principals AS p
  ON p.name = u.userid;


Best practices for generating unique multi-column keys for weak entities?

1. is the least error-prone, simplest and fastest.
Trigger solutions like in 2. or 3. are subject to subtle race conditions under concurrent write access.

Make item_number a serial column and also the PK for order_item in this case. Stick with the default values drawn from the underlying sequence and never update the column.

Auto increment SQL function

Create a multi-column index on (order_id, item_number) for performance of typical queries. (Might as well be UNIQUE, but does not have to be.) In a typical setup (order_id and item_number can both be plain integer), the multicolumn index happens to be just as small and fast as an index on just order_id:

Is a composite index also good for queries on the first field?

(Like I commented:) Typically, the only important role of an item number is to be unique (and immutable). If you need a stable sort order among items, you might just rely on the serial value of item_number. Be aware that those numbers are not necessarily in order of transaction commits. It may be useful to add the transaction timestamp current_timestamp (or possibly statement_timestamp() or clock_timestamp()) to the row. Depends on requirements and access patterns.

You can add a VIEW for the human eye, with item-numbers per order_id starting from 1, dynamically generated with row_number(), ordered by above criteria. But operate with the unique, immutable item_number internally.

Create unique sequence per set of rows sharing the same foreign key value


Overcome Runtime error when exporting large data table from PostgreSQL

This problem is not with postgresql but with the IDE that you are using to manage it - PostgreSQL has nothing to do about that.

You can try to do this with COPY command  (different from \copy - because is a psql command)

COPY (
SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5'
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder )
TO '/tmp/yourfile.txt' delimiter ';';

If you are using Windows as a server, then you just need to use a Windows path instead of /tmp.

This is good if you have postgresql in your local machine or have some access in the server that postgresql is stored, then you can also use a path in the file name to put the file in some directory that is shared (via samba, Windows Share or NFS for example) with you or with your PHP/Java system. Guarantee postgresql user has access to the directory -- tmp is world writable because that i used it as a example.

Good lucky!


The original question had \n\r as the row terminator. On Windows, a row terminator is typically \r\n, with the carriage return first and the linefeed second (for obscure but interesting historical reasons).  Try that in your BULK INSERT command.

Next, I would next try loading the file in a hex editor to validate the exact content of the line breaks.  For example, if the data is Unicode you may need \0\r\0\n as a separator, and/or the DATAFILETYPE ='widechar' flag. Note 0x0A is decimal 10, 0x0D is decimal 13: the ASCII codes for line feed (\n) and carriage return (\r), respectively.


I would like the output to be the following:

 GroupKey    RecordCount    GroupScore
 -------------------------------------
   1              3             9.8
   192            4             7.1

Using correlated subqueries is one way to get the output you want.

  In the instance when a GroupKey contains the same exact RecordKeys as another GroupKey(s), then only one of those GroupKeys is grabbed (it doesn't matter which one).

I'm returning the Group with the lowest GroupKey when there is a match, but that is arbitrary as you say it doesn't matter.

test data:

INSERT INTO RecordTable(RecordKey,GroupKey)
VALUES ('A',1)
     , ('A',29)
     , ('A',95)
     , ('A',192)
     , ('B',1)
     , ('B',29)
     , ('B',95)
     , ('B',192)
     , ('C',1)
     , ('C',95)
     , ('D',192)
     , ('E',192);

INSERT INTO GroupTable(GroupKey,RecordCount,GroupScore)
VALUES (1,3,6.2)     -- ABC
     , (29,2,9.8)    -- AB
     , (95,3,6.2)    -- ABC
     , (192,4,7.1);  -- ABDE
GO

query:

SELECT GroupKey
     , RecordCount
     , GroupScore = ( SELECT max(GroupScore)
                      FROM GroupTable g2
                      WHERE ( SELECT count(*)
                              FROM ( SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g1.GroupKey
                                     UNION
                                     SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g2.GroupKey ) z
                            )=g1.RecordCount )
FROM GroupTable g1
WHERE NOT EXISTS ( SELECT *
                   FROM GroupTable g3
                   WHERE ( SELECT count(*)
                           FROM ( SELECT RecordKey
                                  FROM RecordTable
                                  WHERE GroupKey=g1.GroupKey
                                  UNION
                                  SELECT RecordKey
                                  FROM RecordTable
                                  WHERE GroupKey=g3.GroupKey ) z )=g3.RecordCount
                         AND ( g3.RecordCount>g1.RecordCount
                               OR ( g3.RecordCount=g1.RecordCount
                                    AND g3.GroupKey<g1.GroupKey ) ) );
GO

The subquery in the SELECT gets the highest GroupScore from only those groups that are subsets of this ('g1') group. It achieves this by counting the UNION of the RecordKey's for the 'g1' set and each 'g2' set. If the UNION is larger than the 'g1' set, there must be at least one RecordKey in the 'g2' set without a corresponding RecordKey for the 'g1' set, so the 'g2' set is not a subset and should not be considered for this row.

In the WHERE clause, there are two cases to consider for filtering. In either case, the 'g1' set is only filtered if all the 'g1' RecordKeys are also present in the 'g3' set; and this check is achieved by counting the union again (as per the SELECT clause).

The two cases are: ① the 'g1' set has fewer RecordKeys (g3.RecordCount>g1.RecordCount; in which case we filter), and ② the 'g1' set is identical to the 'g3' set (g3.RecordCount=g1.RecordCount; in which case we arbitrarily choose the set with the lower GroupKey)

output:

/*
|GroupKey|RecordCount|GroupScore|
|-------:|----------:|---------:|
|       1|          3|       9.8|
|     192|          4|       9.8|
*/

dbfiddle here


how mongo db does locking for updates/inserts?

According the documentation MongoDB locks the database only when create an index in the foreground ( this option does not exists any more in MongoDB 4.2. ).

On the other hand the collection are never exclusively locks.

See more what locks are taken on MongoDB on What locks are taken by some common client operations?


How do I grant execute permission to an Oracle database user?

The error message actually gives you the exact syntax

dbms_java.grant_permission( 'SCOTT',
                            'SYS:java.io.FilePermission',
                            '<<ALL FILES>>',
                            'execute' )

It's not obvious to me whether you modified the text of the error message to remove a directory from the error-- normally, the error will tell you a specific directory that you need to grant access to.

If your intention is to allow the Java stored procedure to execute arbitrary shell scripts (this would be very dangerous-- the commands would run as the Oracle operating system user so they would have the ability to bypass any security measures in the database), you should be able to do something like

begin
  dbms_java.grant_permission
      ('SCOTT',
       'java.io.FilePermission',
       '<<ALL FILES>>',
       'execute');
  dbms_java.grant_permission
      ('SCOTT',
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor' );
end;


Does a user who has permissions to select from a view also need select granted to the children entities of that view?

Generally speaking, sub-objects owned by the same owner of the object that one has permission to will pass along that permission. This is Ownership Chaining. By default this behavior is constrained to working within the current database, and with non-dynamic SQL. When you start involving other databases and/or dynamic SQL, then you need some additional configuration to allow that to work (that is, if you don't want to grant direct access to those sub-objects).

The simple / easy mechanisms are to enable Cross-Database Ownership Chaining and/or TRUSTWORTHY (database property), depending on what you are trying to do. However, being simple and easy also means that they both open up rather large security holes.

Fortunately, there is a way to accomplish this with just a little bit of extra work, but the end result will be a much more secure setup. You want to use Module Signing. The only down-side is that you might need to change 1 or more of the Views into Multi-statement Table-valued Functions so that the object can be signed (Views and Inline TVFs cannot be signed). However, the security itself is much cleaner since it is highly specific and self-contained. You will create Certificate-based Users that have the permissions that you want, in each DB that you want them to be extended to (this is done by re-creating the same Certificate in each DB). Then you just need to grant the actual Login / User the EXECUTE permission to the main object.

For a full example of how to do this, please see my answers here:

Stored procedure security with execute as, cross database queries, and module signing
Access view based on table in another database without account in that other database
Permissions in triggers when using cross database certificates

And for more info on why you should use Module Signing instead of Cross-DB Ownership Chaining and/or TRUSTWORTHY, please see my post here:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


How fast should I expect postgresql to be?

The trick to getting good performance out of PostgreSQL for inserts using SQLAlchemy is, as @LaurenzAlb hinted at, better management of transactions. SQLAlchemy can, but doesn't by default, use psycopg2's executemany helpers for PostgreSQL.
Turning these on transforms performance for multiple inserts into the same table. Here's the modified code for the python test.
    engine = create_engine(DB_NAME, echo=False, executemany_mode='values')

With executemany_mode=None:
SQLAlchemy Core: Total time for 50000 records 95.0814561843872 secs = 525.8648952855459 /s

with executemany_mode="values":
SQLAlchemy Core: Total time for 50000 records 1.761573314666748 secs = 28383.71788656377 /s

See https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-batch-mode for an explanation.


Yes.  This query will give you the current number of client connections using TCP/IP.  Of course there are other things that use sockets on the server.

select count(*) connections
from sys.dm_exec_connections
where net_transport = 'TCP'

You can see all the sockets usage on the server using the netstat command.


How to connect to a database in my vps from remote machine?

There could be several reasons why you cannot not connect:

The mysql server is not listening on the public interface. Make sure you are listening to the public ip by commenting the following lines (or setting bind-address to the corresponding public interface)

#skip-networking
#bind-address                   = 127.0.0.1

MySQL is not running in the 3306 port. Connect to the right one or reconfigure the port with:

port = 3306

You can check that mysql is listening in the fight port, right interface with:

$ netstat -tl

MySQL could not successfully start- easy to check, try to connect locally (using ssh) or check that it is running with:

$ pgrep mysqld
$ ps aux | grep mysqld

Your VPS or your provider may have a firewall activated on your server or on the datacenter, or running on a NAT, preventing incoming connections from outside of the server/network. You have to either disable the host network, connect to the public ip corresponding to the right private ip of the server or configuring the port in your control panel.

If for some reason you still cannot access mysql directly (or you do not want to expose the port publicly, which I recommend), you can tunnelize it using ssh, like this:

(localhost)$ ssh -N -L 3306:localhost:3306 user@vpsip
(localhost)$ mysql -h localhost -P 3306 -u root -ppasswd


It's not strictly necessary to have an association class to model a M:M relationship.  You can just have classes with references to the two parents that would map directly to a link entity.  Semantically, an association class would limit you to one relationship between two entities.

A short blurb on association classes can be found here.


Retrieve alert error description text via SQL Server Agent

Tokens may be what you're looking for:

  SQL Server Agent token replacement is simple and efficient: SQL Server
  Agent replaces an exact literal string value for the token. All tokens
  are case-sensitive. Your job steps must take this into account and
  correctly quote the tokens you use or convert the replacement string
  to the correct data type.

Using $(ESCAPE_SQUOTE(A-MSG)) should allow you to access the description from an error message when the agent job is triggered by an alert.


Creating a MySQL db from an Informix database

You first need to convert that output file nearest to MySQL syntax and convert the data type according to MySQL. You could user perl or sed scripts for first time and then use these techniques for future use. Once you have prepared the files of table creation then generate dynamic insert statements from informix and use them in MySQL for loading data.

Regards,
Nawaz


Trying to get password has for a login through PowerShell

For SQL 2005+

Invoke-Sqlcmd -Query "SELECT sys.fn_varbintohexstr(CONVERT(VARBINARY(MAX), LOGINPROPERTY('$LoginName', 'PASSWORDHASH'))) as PasswordHash"  -ServerInstance $SourceInstance

You can't use this for everything as it has length limitations, but for this purpose it will be fine.


What is the best option to easily fix fragmented heap indexes?

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following

ALTER TABLE tablename REBUILD

That being said you can read this article by Paul Randal as to why you shouldn't.  Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway you probably want to put a clustered index on your table.  At this point it won't be a heap and you have more options for dealing with fragmentation.

You generally want your CI to be small, unchanging and ever increasing (and unique is nice but not necessary).  The unchanging and ever increasing will minimize any fragmentation for the future.


I would advise to backup first ...  but that's a catch 22, since you can't.

Either way, since the error revolves around a fictional xid wraparound I thought this might solve your immediate problem:

VACUUM FREEZE tbl;

The chapter Preventing Transaction ID Wraparound Failures in the manual has details.

But turns out, this runs into the same problem.

Of course, you can delete and recreate the table if you have the data outside Postgres.

The only remaining option I see is to upgrade to the latest point release and hope your problem is covered in the various patches that have been added, like @a_horse commented.

However, after some more research I found that the core hackers have been working on a solution for just this problem in the past weeks.
Details in this thread on pgsql-hackers.
The commit message.

So I would hold back and upgrade straight to Postgres 9.4.9, which should be out soon:
The next point release is planned for August 11th, 2016 at the latest.


how to set Mongod config set multiple bindIp addresses

without a space after the comma, AND the ip address has to present


Hazards of Upgrading SQL Server Express 2008 to R2

You're unlikely to run into any issues with this but that said, it's rarely caution that gets a DBA fired.

I'd probably go with installing a named instance, side-by-side with the existing instance. As you've got a test bed server up already I'd also suggest testing the application with 2012 Express. Why upgrade to an already out-dated version?


Select column to be inserted based on input column value

By using CASE statements..

INSERT INTO table2 (id, seq1, seq2, seq3, seq4, seq5)
SELECT
  <SOME ID VALUE>,
  CASE WHEN sequence_number = 1 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 2 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 3 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 4 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 5 THEN value ELSE NULL END
FROM table1

If you consider that hard coding, then (as far as I'm aware) your only other option would be dynamic SQL within a plpgsql block.

DO $$
DECLARE
  row table1%ROWTYPE;
  colName VARCHAR(10);
BEGIN
  FOR row in SELECT * FROM TABLE1 LOOP
    colName := 'seq' || row.sequence_number;
    EXECUTE '<SQL HERE THAT USES colName FOR INSERT>';
  END LOOP
END;
$$ language plpgsql;


Bypassing version store for changing column datatype

Prefacing this by saying I think my idea is lame, but it is an idea. :)
You can try creating a new empty NVARCHAR column (of the appropriate size) and do batches of updates from OldVarCharColumn to NewNVarCharColumn at the rate your system can tolerate. You can even automate the entire update by iteratively looping and using a rolling range of the key (assuming it's numerical) for which rows, by setting a variable on each iteration (e.g. WHERE KeyField >= @CurrentIterationValue - 1000 AND KeyField < @CurrentIterationValue) and by using the WAITFOR DELAY command to pause between each iteration for any amount of time you specify (e.g. WAITFOR DELAY '00:05:00.000' will pause execution for 5 minutes).
Once your new column is fully updated you can then DROP the OldVarCharColumn.


How do tablespaces differ in DB2 from databases in MySQL or SQL Server?

In DB2, a tablespace is the object that serves as a layer of abstraction between tables (and indexes) and disk.  It is similar to a file group in MS SQL Server.

In DB2, a database is roughly equivalent to a SQL Server instance – a database contains multiple tablespaces, transaction logs, one or more bufferpools and other memory areas.

A "database" in SQL server is much closer to a schema in DB2.


Restroring MYSQL databse from .frm and .ibd files (database does not exist error)

Get all .ibd and .frm files into a zip or tar.gz and upload it on https://recovery.twindb.com/ it will recover tables from .ibd files.

tar zcf database.tar.gz /path/to/dir_with_ibd_and_frm


Compare all attributes in M:N relationship

Well, after some experiments and close reading of multiple SQL tutorials, I found this is a quite good solution:

SELECT DISTINCTROW book_id, title, year_of_publication FROM
((book
INNER JOIN (SELECT * FROM link_author_book
                 INNER JOIN author ON link_author_book.author = author.auth_id
                 WHERE author.firstname = "Jeff" AND author.lastname = "Carpenter") AS author
ON author.book = book.book_id)

INNER JOIN (SELECT * FROM link_place_book
                 INNER JOIN pub_place ON link_place_book.p_place = pub_place.place_id
                 WHERE pub_place.placename = "Sebastopol" OR pub_place.placename = "Tokyo") AS place
ON place.book = book.book_id)

WHERE title = "Cassandra: The Definitive Guide, 2nd Edition" AND
year_of_publication = 2016 AND pagecount = 370;

This should return exactly the required book and nothing else. Obviously, hard coded values can be replaced with values from frontend forms or something, to make this more flexible ... Any suggestions to optimize this solution are welcome.


The question is mainly about how to optimize the select statement:

SELECT [TABLE], [FIELD], [AFTER], [DATE]
FROM mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB' AND
[FIELD] = 'STATUS'

Removing the redundant projections and adding the presumed dbo schema:

SELECT [AFTER], [DATE]
FROM dbo.mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB'
AND FIELD = 'STATUS';

Without an index like ([TABLE],[FIELD]) INCLUDE ([AFTER],[DATE]) SQL Server has two main options:

Scan the heap entirely (3GB+); or
Locate rows matching [TABLE] = 'OTB' and [FIELD] = 'STATUS' (using IDX6), then perform a heap (RID) lookup per row to retrieve the [AFTER] and [DATE] columns.

Whether the optimizer chooses a heap scan or index seek with RID lookup depends on the estimated selectivity of the [TABLE] = 'OTB' and [FIELD] = 'STATUS'predicates. Check to see if the estimated number of rows from the seek matches reality. If not, update your statistics. Test the query with an table hint forcing the use of the index, if that condition is reasonably selective. If the optimizer is currently choosing the index seek, test performance with an INDEX(0) or FORCESCAN hint to scan the heap.

Beyond that, you could look to improve the scan of the heap a little by removing some of the unused space (370MB). In SQL Server 2008 this can be done by rebuilding the heap. Unused space in heaps often results from deletes performed without a table lock being taken (without a table lock, empty pages are not deallocated from a heap). Tables that experience frequent deletions are often better stored as a clustered table for this reason.

The performance of the heap scan depends on how much of the table is stored in memory, how much must be read from disk, how full the pages are, the speed of the persistent storage, whether the scan is I/O or CPU bound (parallelism can help).

If performance is still unacceptable after you have investigated all of the above, try to make the case for a new index. If available on your version of SQL Server, a possible filtered index for the given query would be:

CREATE INDEX index_name
ON dbo.mytbl ([DATE],[AFTER])
WHERE [TABLE] = 'OTB'
AND [FIELD] = 'STATUS';

Also consider index compression, if that is available and beneficial. Without a new index of some kind, there's relatively little you can do to improve the performance of the given query.


Is it possible to increase Oracle execution plan output indentation?

You can use PLAN_TABLE for such result as following:

Your statement for generating the execution plan

EXPLAIN PLAN
SET STATEMENT_ID = 'mystatement1'  -- give your statement a meaningful ID so that we can fetch it from PLAN_TABLE using it
    FOR
SELECT
    *
FROM
    ACTIVE_USERS;

Now, Fetching the execution plan from PLAN_TABLE

SELECT
    ID,
   -- LPAD(' ', LEVEL - 1) -- default format
    LPAD(' ', LEVEL * 2) -- Indentation is done using this
    || OPERATION
    || ' '
    || OPTIONS
    || ' '
    || OBJECT_NAME "Operation",
    OBJECT_NAME   AS "Name"
FROM
    PLAN_TABLE
CONNECT BY PRIOR ID = PARENT_ID
           AND PRIOR STATEMENT_ID = STATEMENT_ID START WITH ID = 0
                AND STATEMENT_ID = 'mystatement2'
ORDER BY
    ID;

Output:

You can add more columns into result according to your need and also add more spaces using LPAD in the above query.

Cheers!!


Cannot find reason why differential backups size reduced

The most likely cause by far is that a full backup was taken out of sequence (between that differential and the previous full backup) - that makes the differential smaller as there are less differences to include between it and this extra backup than between it and the expected previous backup.

If this is the case then to be able to restore the unexpectedly small differential you need to find a copy of the extra backup.

This is usually because someone has taken a copy for use elsewhere, perhaps for testing a support issue or new release. IT is important that everyone who has access to be able to take backups knows that they need to be careful of this because it breaks your DR plans and so forth.

There is a safe way to take an extra backup: use the COPY_ONLY option. This takes the backup as normal but does not interfere with your main full/diff/log backup sequence.


SELECT
    inbox.ItemId,
    item.NAME,
    inbox.Expiratory,
    t = SUM(inbox.TotalNumber)
FROM dbo.Item
INNER JOIN dbo.inbox
ON Item.ItemId = inbox.ItemId
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.Outbox
    WHERE Item = inbox.Item
    AND Expiratory = inbox.Expiratory
)
GROUP BY inbox.ItemId, item.NAME, inbox.Expiratory;


Using COPY in psql script does not write to file

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

No all of the commands don't have to be on the same line, but that's not the problem here. COPY is saving the file on the server. You want it on the client, that's what the psql command \COPY does. I assume you're connecting to the remote server which is where the file is. Likely you could just scp or rsync it back to the client or the like, but that's a bad idea. You're better off doing it with just a client.

There are numerous ways to put the \copy on a different line. For instance, take this test.psql

CREATE TEMP TABLE t AS VALUES (1,2), (2,3);
\COPY t TO /tmp/1
CREATE TEMP TABLE b AS VALUES (1,2), (2,3);
\COPY t TO /tmp/2

You can run it with psql -d test -f test.psql

COPY is a PostgreSQL server-command that is made to look like SQL.
\COPY is a PostgreSQL client (psql)-command that is made to look like COPY


With "quiet" configuration parameter (not recommended) you get rid of:

"Successfully authenticated as..."
"connection accepted from ..."

but "end connection ..." still stays there and there is no way to remove it, without changing source code. You can always ask developers to change code to

if (!quiet) {
  const auto word = (connectionCount == 1 ? " connection"_sd : "connections"_sd);
  log() << "end connection " << remote << " (" << connectionCount << word << " now open)";
}


Is there anyway to make this stored procedure thread safe?

I know raising the transaction isolation level and adding a transaction can seem counter-intuitive, but sometimes, you have to make things queue up and wait their turn.  Doing those two things worked for me in my simple test rig.  Try it and let us know how you get on.

EDIT: Test rig
OK, nice simple test rig using tempdb.  It's a bit rough and ready but provided an easy repro and provable fix for me.  You should enable SQLCMD mode in SSMS before you run.  I basically pit the proc against itself in multiple sessions using either ostress or GO 100.  Run the initial version of the proc, monitor for deadlocks; I get many.  Then, reset the rig, run in the revised version of the proc and try again.

USE tempdb
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[RawData_d_DeleteObsoleteRawData]') IS NOT NULL
DROP PROC [dbo].[RawData_d_DeleteObsoleteRawData]
GO

-- Example table type
IF EXISTS ( SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IntListType' )
    DROP TYPE IntListType
GO

CREATE TYPE IntListType AS TABLE ( Item INT PRIMARY KEY )
GO
-- Create a large table
-- DROP TABLE dbo.rawData
IF OBJECT_ID('dbo.rawData') IS NULL
CREATE TABLE dbo.rawData (
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT,
    MeasureId INT,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    DateEnergy DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

TRUNCATE TABLE dbo.rawData

-- Populate table
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.rawData ( AreaId, MeasureId, DateEnergy )
SELECT areaId.rn, MeasureId.rn, DATEADD( day, DateEnergy.rn % 300, '1 Jan 2014' )
FROM
    ( SELECT TOP 10 rn FROM cte  ) areaId,
    ( SELECT TOP 100 rn FROM cte  ) MeasureId,
    ( SELECT TOP 1000 rn FROM cte  ) DateEnergy,
    ( SELECT TOP 2 rn FROM cte  ) doubleUp
GO

-- Original proc
CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

         -- Delete non remote reading raw data
         DELETE TOP (10000) FROM [dbo].[RawData]
         WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList)
         AND DateEnergy < @MaxTime

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
         ;WITH cte AS
         (
                 SELECT AreaId, MeasureId, DateEnergy,
                          ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                 FROM [RawData] rd WITH (NOLOCK)
                 INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                 WHERE DateEnergy < @MaxTime
         )
         DELETE TOP (10000) rd
         FROM [RawData] rd
         INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
         WHERE rn <> 1

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         SELECT @TotalDeleted as DeletedRow

    END
GO
:exit

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Main proc call START
-- Run this section in two or more connections
-- eg using ostress
-- !!ostress.exe -E -S.\sql2008r2 -dtempdb -i"C:\temp\temp.sql" -n10 -r5 -mstress
------------------------------------------------------------------------------------------------

-- Proc call
SET NOCOUNT ON
DECLARE @tvp AS [IntListType]

INSERT INTO @tvp
VALUES ( 1 ), ( 2 ), ( 3 ), ( 5 ), ( 8 ), ( 13 )

EXEC [dbo].[RawData_d_DeleteObsoleteRawData] @tvp, 30
GO 100

-- Main proc call END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Revised proc START
------------------------------------------------------------------------------------------------

/*  Reset the rig and try with the revised proc

ALTER PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRY

        BEGIN TRAN

            -- Delete non remote reading raw data
            DELETE TOP (10000) FROM [dbo].[RawData]
            WHERE MeasureId NOT IN ( SELECT Item FROM @RemoteReadingMeasureIdList )
            AND DateEnergy < @MaxTime

            SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

            -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
            ;WITH cte AS
            (
                    SELECT AreaId, MeasureId, DateEnergy,
                            ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                    FROM [RawData] rd
                    INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                    WHERE DateEnergy < @MaxTime
            )
            DELETE TOP (10000) rd
            FROM [RawData] rd
            INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
             WHERE rn <> 1

             SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

             SELECT @TotalDeleted as DeletedRow

             COMMIT TRAN

         END TRY

         BEGIN CATCH

            DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

            IF @@TRANCOUNT > 0 BEGIN PRINT 'Error handler rolling back...' ROLLBACK TRAN END

            RAISERROR( @errorMessage, 16, 1 )

         END CATCH

    END
GO
*/

-- Revised proc END
------------------------------------------------------------------------------------------------


Scenarios in which you might need to restore large number of databases at once?

So you've got two zones. Let's say zone one floods or burns... it's completely gone. All the hardware is destroyed, and the old site isn't even usable. It could be weeks putting it back together. Thankfully, you're still online thanks to zone two.

Of course, you might try something like short-term leasing servers to get redundancy back while you put together your real new production facility, but really the time spent getting those online is a distraction from your main task, which should be getting a new primary facility up to snuff as quickly as possible. If you do attempt the temporary facility, having the restore scripts will greatly aid getting the temporary facility online quickly, allowing you to get back to your main task.

If you don't opt for the temporary facility, how capable is your zone two site really? I hear all the time about failover equipment that's really just retired equipment from from the primary site. This often means equipment that is older, which raises concerns about both performance and reliability. How long do you really want to leave that facility running as your only data center? Are you taking good backups while this site runs as primary?

In short, wouldn't you like the setup process for your new site to be as quick, reliable, and well-understood as possible? The longer it takes to get the main facility back up and running, the more likely it is for something to also go wrong at your second facility.

Of course, this is just one scenario. It's the big scary total destruction scenario the seems to unlikely to ever actually happen for you, and maybe even that's true. But this kind of things plays out on a smaller scale all of the time. The ability to do quick, reliable, scripted restores for your entire data center for the big emergencies implies the ability to also do quick, reliable, scripted restores for the small emergencies. And that's a good thing.


First time running a replication on SQL Server 2008. "The process could not connect to subscriber 'SERVER2'"

This is likely a permissions issue.

Verify that your replication agent accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.


Same table alias for two different tables (Oracle)

Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES

The bug is "still being worked on".

The workaround is obvious: use different aliases.

Non-ANSI join throws an error as expected.

Seems to be fixed in 18.1 and 18.2 as well.


Configure sp_send_dbmail to send emails ONLY to certain recipients

There is nothing built in that you could use already.

I would suggest creating a "wrapper" stored procedure for sp_send_dbmail, that accepts the exact same parameters and passes them through, with additional logic in place to check the @ recipients parameter that is passed against your exclusion list.


This type of requirement comes under the banner of "gaps and islands". A popular approach is

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (PARTITION BY ItemId ORDER BY DateOfChange) -
                DENSE_RANK() OVER (PARTITION BY ItemId, Status ORDER BY DateOfChange) AS Grp
         FROM   ItemTable)
SELECT ItemId,
       Status,
       MIN(DateOfChange) AS Start,
       MAX(DateOfChange) AS Finish
FROM   T
GROUP  BY ItemId,
          Status,
          Grp
ORDER  BY Start


I guessing that you need the examdate of the last exam that does not have a complete date?

Query

 SELECT fname,
        CASE
        WHEN exam1complete IS NULL THEN 'exam1date'
        WHEN exam2complete IS NULL THEN 'exam2date '
        ELSE 'exam3date' END AS columnname,
        CASE
        WHEN exam1complete IS NULL then exam1date
        WHEN exam2complete IS NULL then exam2date
        ELSE exam3date END AS examdate
FROM Information;

Result

fname   columnname  examdate
joe exam1date   2018-01-30
james   exam2date   2018-03-02
javier  exam3date   2018-04-01


Changing the SQL Server Compatibility setting for individual tables/columns, which have retained older settings

Compatibility levels are set at the database, not at the table or column.

You might be thinking of collations, which are set at the column level.  You can't simply change the collation on those objects - you have to build new objects with the right collations and push the data into them.

You might also be thinking of deprecated datatypes like text, ntext, image, or vardecimal.  You don't have to change those yet, but you'll have to fix them before a future SQL Server version.


Understanding SQL Server statistics better

Very easy. Look for the result of:

select datediff(day,'1/1/1950','12/31/2007');

It will give you your DISTINCT_RANGE_ROWS + 1
That means that SQL Server thinks that all your DOBs are approximately equally distributed across the range with average 236 items per day.
Query your data and see if you have any outlines like: missing day ranges or >1000 DOBs per day.
If you do, than that would be a question to the engine.


Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

This doesn't 100% answer the question as to WHY I was getting the answer, but a workaround I figured out was moving the entire job to a different server where it worked.


how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

This should work:

with w(id, line#, line, rest) as (
  select id, 1, SUBSTR(regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 1, 132),
         substr(to_char(bar), LEAST(132, regexp_instr(bar, '$', 1, 1, 1, 'm'))+1)
  from foo
  union all
  select id, line#+1, SUBSTR(regexp_substr(rest, '^.*?$', 1, 1, 'm'), 1, 132),
         substr(rest, LEAST(132, regexp_instr(rest, '$', 1, 1, 1, 'm')+1))
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, ROW_NUMBER() OVER (ORDER BY line#) AS line#, line
from w
where TRIM(line) <> ''
order by id, line#;

What it does is:

line: truncate (SUBSTR(..., 1, 132)) the first full line to the first 132 characters (which does nothing for lines shorter than 132)
rest: grab the rest of the CLOB of either the first full line, or whatever comes after 132 characters, whichever starts first (substr(rest, LEAST(132, ...)))

Response to the sequence question in comments:

To create a sequence over a partition, your best bet is self-join with a grouped-and-sorted:

SELECT t.MO_ID, g.DOC_NO, ...
FROM <your table> t
JOIN (
  SELECT DISTINCT ROWNUM AS DOC_NO, MO_ID
  FROM <your table>
  WHERE <same as in outer query>
  ORDER BY MO_ID
  ) g ON (g.MO_ID = t.MO_ID)
WHERE ...

or with a CTE + ROW_NUMBER() window function:

WITH
  t AS (
    SELECT *
    FROM <your table>
    WHERE ...
    ),
  g AS (
    SELECT MO_ID, ROW_NUMBER() OVER (ORDER BY MO_ID) AS DOC_NO
    FROM (SELECT DISTINCT MO_ID FROM t) x
    )
SELECT t.MO_ID, g.DOC_NO, ...
FROM t
JOIN g ON (g.MO_ID = t.MO_ID)


Why does MySQL UPDATE with <= lock insert intention?

I believe the issue is due to the UPDATE using "next-key locking" as evidenced by "asc supremum" in the engine status conflicting with "insert intention locking" of the INSERT as evidenced by "lock_mode X insert intention waiting" in the engine status. It seems the only way around this is to effectively disable gap locking entirely by using READ COMMITTED transaction isolation level for Transaction 1 or innodb_locks_unsafe_for_binlog.

Transaction 1

set transaction isolation level read committed;
start transaction;
update foo set value = NOW();

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

Since using READ COMMITTED disables all gap locking (including next-key locking) in favor of index record locking it is not necessary to use where id <= @max so this can now be removed. I do not believe it is possible to accomplish this update without blocking the insert using REPEATABLE READ.


The problem having different vms with the same sql server Licence key

That's not a "License Key" it's an "Activation Key", which simply controls the Edition of SQL Server that is running.  Its only relationship to licensing is that it may help you determine what licenses need to be assigned to the server.
Licenses are assigned to servers using external bookkeeping, like in a Software Asset Management Solution or an Excel Spreadsheet.
For instance, SQL Server on Docker Containers and the Visual Studio Subscription Downloads all come with the key already embedded in the installer.  Both of these can be used for production deployments by simply assigning the appropriate licenses to the servers without changing the key.


Extracting a field from RESTORE HEADERONLY

This works for me.

SELECT BackupStartDate
FROM OPENROWSET('SQLNCLI',
                'Server=MARTINPC\MSSQL2008;Trusted_Connection=yes;',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC(''
RESTORE HEADERONLY
FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')'
)

The ad hoc distributed queries Option needs to be enabled. Or if you don't want to do that you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername

SELECT BackupStartDate
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')


Create stored procedure which performs LOAD DATA INFILE.
Specify its DEFINER attribute as a user who have rights to import data (root@localhost, for example).
Specify SQL SECURITY DEFINER attribute.
Grant the user to execute this procedure.

Result: when user calls this SP it is executed with the rights of its definer - it is enough to import data. Even if the user called SP have no rights to this table at all (I'm not talking about the FILE right).

You can check any additional conditions in the procedure (current username, hostname, day of week, etc.) and perform import or not dependent by them.

Filename, tablename, additional data can be transferred into SP as parameters and/or via user-defined variables (which existence and value can be additional checkpoint).


T-SQL query must include and exclude child rows

Alright, round three!
So, it looks like you're trying to find any parents that match all given criteria, correct? I'm assuming you're looking to use this in some real world scenario (a report or application). If that's the case, you'll need to pass in the criteria using a parameter.
The code here is going on that assumption.For this to work correctly, you'll need a function to split the parameter string. STRING_SPLIT function is not supported until SQL 2012, so if you're working in 2008, you'll need this function.
SET QUOTED_IDENTIFIER ON;

SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.SplitString
(
    -- Add the parameters for the function here
    @myString     VARCHAR(500)
    ,@deliminator VARCHAR(10)
)
RETURNS @ReturnTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    id     INT         IDENTITY(1, 1) NOT NULL
    ,value VARCHAR(50) NULL
)
AS
BEGIN
    DECLARE @iSpaces INT;
    DECLARE @part VARCHAR(50);

    --initialize spaces
    SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);

    WHILE @iSpaces > 0
    BEGIN
        SELECT  @part = SUBSTRING(@myString, 0, CHARINDEX(@deliminator, @myString, 0));

        INSERT INTO @ReturnTable
        (
            value
        )
        SELECT @part ;

        SELECT  @myString = SUBSTRING(
                                         @myString, CHARINDEX(@deliminator, @myString, 0) + LEN(@deliminator)
                                         ,LEN(@myString) - CHARINDEX(' ', @myString, 0)
                                     );

        SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);
    END;

    IF LEN(@myString) > 0 INSERT INTO @ReturnTable   SELECT @myString;

    RETURN;
END;
GO

Then, this query should give you the results you're looking for.
If you're using SQL 2012 or higher, you can replace the function (dbo.SplitString) with the built in windowed function STRING_SPLIT and achieve the same results.
DECLARE @ParmList VARCHAR(MAX) = 'red hair,blue eyes';

SELECT  c.parent_id
FROM    dbo.children AS c
WHERE   EXISTS (
                   SELECT   1
                   FROM     dbo.SplitString(@ParmList, ',') AS at
                   WHERE    at.value = c.attribute
               )
GROUP BY
        c.parent_id;


Find values which occur in every row for every distinct value in other column of the same table

Count distinct A grouping by B, and return those where count(distinct A) match.

select B, count(distinct A)
from table_AB
group by B
having count(distinct A) = (select count(distinct A) from table_AB);

 B   A
=== ===
 x   4


DBCC show_statistics: density doesn't match what I calculate

The documentation for DBCC SHOW_STATISTICS says that Density (in the header) is:

  Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values.

It also says:

  This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

You are using SQL Server 2005, so this value may be relevant to you in some circumstances, but I don't know exactly when 2005 would use that Density value.


Update
As Aaron points out, there is the chance the copy-and-truncate can miss some entries. So the safer method is to move and FLUSH.

Original

This article has the basic principle to rotating the slow query log that I use. Basically you need to copy the slow log to a new file, then truncate the contents of the slow.log:

cp log/slow.log log/slow.log.`date +%M`; > log/slow.log

If you just move the slow log to a new file and creating a new  'slow.log', it won't work because the moved file still has the same inode, and mysql still has it open. I suppose moving the file and then issuing a FLUSH SLOW LOGS command would work, as that closes the file and reopens, but I find the copy-and-truncate to be just as effective and doesn't require logging into mysql.

His article mentions using logrotate in Linux, but I just made a cronjob to run once a day at midnight to do this for me.

Also, to address the issue of replication on FLUSH LOGS:

  FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK (with or without a table list) are not written to the binary log in any case because they would cause problems if replicated to a slave. [src]

So no, since those statements are not written to the binary log, it will not interfere with replication. For your purposes I would specify FLUSH SLOW LOGS to only close/open the slow query log.


MySQL 'copying to tmp table' while enlarging VARCHAR

This is the paradigm of ALTER TABLE. It is a simpler approach to making changes. Keep in mind that MySQL supports an open storage engine plugin architecture. The same calls to perform every operation fits InnoDB, MyISAM, and other storage engines.
Under the hood, doing
ALTER TABLE tblname MODIFY COLUMN mycolumn VARCHAR(255);

would mechanically work like this
CREATE TABLE tblname_new LIKE tblname;
ALTER TABLE tblname_new MODIFY COLUMN mycolumn VARCHAR(255);
INSERT INTO tblname_new SELECT * FROM tblname;
DROP TABLE tblname;
ALTER TABLE tblname_new RENAME tblname;

This approach is totally storage engine agnostic.
The alternative to this would be for the storage engine to actively manage a list of blocks that can hold new rows.
What happens when you deal with...
CHAR columns
Even if such management was in place, it would never get summoned because none of the fragments would be large enough to hold changed rows since the column had a smaller size before. This would result in appending all the rows to the physical file for the table. Now, the space occupied by the rows before the change would appear before the first of the table after the ALTER TABLE was done. This would be true for expanding CHAR variables.
VARCHAR columns
In the case of expanding a VARCHAR, altering lengths to be larger may require changing from 1 to 2 bytes for every row should you expand a VARCHAR(255) (255 is a 1-byte unsigned number) to VARCHAR(256) (256 is a 2-byte unsigned number). The introduction of that one extra byte would still require vigorous fragmentation block list management just for the sake of accommodating one extra byte. Therefore, actual space management via maintaining a fragmentation block list would be highly impractical for the open storage engine plugin architecture of MySQL. Such management would probably be in Oracle, SQL Server and PostgreSQL.


Excessive replication snapshot size causing failure

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and by even bigger factors than x4.

The native format alternative is the bcp utility native format (since snapshots are applied using bcp). This format is only slightly longer than the native SQL storage when comparing individual values, but it can result in actually smaller files than SQL Server required storage because the bcp file is dense: there is not page header overhead, nor partially filled pages (fragmentation, fill-factor). See Use Native Format to Import or Export Data.

Additionally you should also compress the snapshots. See Enhance General Replication Performance:

  Use a native mode snapshot unless a character mode snapshot is required.
  Use the default native mode snapshot for all Subscribers except non-SQL Server Subscribers and Subscribers running SQL Server Compact, which require a character mode snapshot.
  Use a single snapshot folder for a publication.
  When specifying the publication properties related to snapshot location, you can choose to generate snapshot files to the default snapshot folder, to an alternate snapshot folder, or to both. Generating snapshot files in both locations requires additional disk space and more processing when the Snapshot Agent runs.
  Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
  The Snapshot Agent performs a sequential write of data to the snapshot folder. Placing the snapshot folder on a separate drive from any database or log files reduces contention among the disks and helps the snapshot process complete faster.
  When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged. This allows minimal logging of the bulk inserts performed during the application of the snapshot at the Subscriber. After the snapshot has been applied to the subscription database, you can change to a different recovery model if necessary (replicated databases can use any of the recovery models). For more information about selecting a recovery model, see Restore and Recovery Overview (SQL Server).
  Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks.
  Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media.
  Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Alternate Snapshot Folder Locations and Compressed Snapshots.


Double entry bookkeeping database design

A Journal is a chronological listing of all transactions of a specified type for an accounting system. Here is a classical presentation on ledger paper of a simple Sales (on Account) Journal:

Note that every line is a single transaction, with Total Debits = Total Credits; and that every transaction hits the same three accounts. A Cash Sales Journal would look similar but replace the column Accounts Receivable Debit with one labelled Cash Debit. A Cash Disbursements Journal would have a first column labelled Cash Credit and additional columns such as Accounts Payable Debit and Employee Expenses Debit.

This presentation was standard for hundreds of years until personal computers became affordable a couple of decades ago. It has a significant advantage of easily verifying that every transaction is balanced by simply checking  each row. Likewise, prior to posting a page of such transaction to the Ledgers the page totals could be similarly verified. That is a batch-processing model used in many accounting systems.

It is easy to see that this paper model has significant disadvantages when literally transcribed to an automated system:

The data structure is a pivoted one, whereas experience has shown that it is much simpler (also more robust and more easily verified) to program against a folded data structure; and
Because every specialized Journal hits a different set of accounts,  each such Journal would have to be separately designed and programmed, a wasteful and error-prone activity.

For these reasons it is generally recommended to use the Specialized Journals design as the interface to the accounting system, but to design a data structure that can be the numerical repository for multiple journals. In a modern RDBMS this has the potential advantage that the General Ledger, and even the specialized subledgers, can become Indexed Views on the Journal, completely eliminating the requirement to code a Posting Process (the step where a Journal transaction is locked and has its account totals transcribed to the various ledgers).

Whatever data design you end up with, the key is to have a single Posting Entry for each transaction type (ie each specialized Journal in the equivalent paper system) where balance checks are made.

My point is that both approaches you propose are unsound mechanisms for double-entry bookkeeping. First point: Journals are write-once tables for very good reasons. Sometimes the two virtual tables Pending Entries and Posted Entries are collocated in a single data structure, but the IsPosted bit is always write-only and the system must ensure that the read-only nature of the Posted Entries records is maintained.

The way accountants have posted Journal Entries for the past 800 years is fully normalized. The only difference between a paper presentation and a sound electronic presentation is that a folded table structure is more convenient in the latter case while a pivoted table structure in the former, which historically was most significant when highly parallel processing was desired - i.e. many clerks each maintaining a single or small number of specialized journals. Only the Controller historically had permissions for the General Journal.

Note carefully that in the above I specified that Journal Entries are fully normalized; Journal Entries are a chronological record of all transactions, grouped in Journals specific to each transaction type. The posting of journal entries to the Ledgers is a separate endeavour and, while fully normalized on its own, is a redundant copy of the journal entries where all transactions are summarized (General Ledger) or detailed (Sub Ledger) by account. Both Journals and Ledgers employ double-entry bookkeeping independently.

@Codism Any accounting system, DEB or SEB, give you generalized reporting for all accounts recorded. Note that, internally, a sub-ledger is by definition a single-entry bookkeeping record; the other side is the corresponding control account(s) on the Balance Sheet. DEB ensures that for every asset dollar there is a precise record of the business claim on the asset, i.e. the equity in the asset whether it be a debt equity (aka liability) or an ownership equity, and of the priority of those claims if the organization becomes insolvent.


How do I migrate Postgres database using an intermediate file

When you are restoring a dump taken with -C, the database you specify on the command line is name of the database you connect to in order to execute the creation of the new database.  Therefore it can't be the name of the new database.  It seems like you already know this, you just haven't thought it through.  psql is trying to connect to {dbname} because that is what you explicitly told it to do.
PostgreSQL doesn't "treat" sudo commands differently.  Sudo is in charge, not PostgreSQL.  Different commands do different things, and PostgreSQL is just along for the ride.
You are trying to tell the "postgres" system user to read a file located in  your daily user's home directory.  It probably doesn't have permission to do that.  Again, this is not PostgreSQL's choice.  You could put the file somewhere else (like /tmp) and give it world read permissions, or you could change your pg_hba.conf so you can connect to the database as your daily user rather than needing to sudo all the time.


How to remove XFS filesystem warning in mongodb shell?

To permanently suppress this server warning you currently need to use the XFS filesystem. The warning was added due to observed stalls with EXT4 during WiredTiger checkpoints.

You may want to upvote & watch SERVER-19790: Provide mechanism to clear/acknowledge startup warnings in the MongoDB issue tracker.

If you want to suppress displaying this warning (and all other startup warnings), start your mongo shell session with --quiet.

The quiet option is typically only used for non-interactive scripts as otherwise you may miss an important warning. Startup warnings only appear once per mongo shell session and highlight potentially serious issues like having access control disabled (or in your case, a filesystem which can result in stalls during checkpoints).


Using a select max(blah) as an input parameter

As the comments state, you forgot to include the FROM clause in your SELECT query. Try this:
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', now(), 12.4, (select max(version) from MeterData where nmi = 'E1234' and interalend = now()) + 1)


Can this query be improved? view for few count totals on a table

Use CASE so that only one full scan is needed. An non-clustered index on iscomplete will cover this query.

SELECT
      COUNT(1) AS Total
    , SUM(CASE WHEN iscomplete is null or iscomplete = 0 THEN 1 ELSE 0 END) AS Active
    , SUM(CASE WHEN iscomplete = 1 THEN 1 ELSE 0 END) AS Complete
FROM dbo.alerts;


That's not the best practice to use SYS user to use in Enterprise Manager. And you can change password for the user like SYSTEM or SYSMAN using OEM but not for SYS user.

You can change the password of SYS user using SQL*PLUS command called password which send the password in encrypted form instead of plain text.

[oracle@ora12c ~]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 24 13:37:49 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
Password changed

First of all find out which profile is assigned to the user-

SQL> select profile from dba_users where username='SYS';

PROFILE
--------------------------------------------------------------------------------
DEFAULT

-then you can user ALTER PROFILE to change password life time.

Also password doesn't cause effect on SYS user(I couldn't find documentation link) as demonstrated below.

SQL> alter user sys password expire;

User altered.

But I still can connect to the database using old password.

C:\Users\user>sqlplus sys/oracle@192.168.30.133:1522/oracdb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 24 13:52:46 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

However, if your password for sys user has expired and unable to login then-
 use ORAPW utility
 run sqlplus / as sysdba from the terminal of the user who owns the Oracle software.


Importing LARGE quantity of data into a PostgreSQL database

Log files in /var/log/postgres are only for your information (in a standard installation) and do not serve the system itself, especially not "ACID compliancy". They need to be writable once configured, that's all the server needs.

There are a number of settings in your postgresql.conf that govern what is logged. Most of them can also be set at the command line to overrule the setting in the config file. We are not talking about WAL files, which are not usually placed in /var/log/postgres.

I would drastically reduce the verbosity in your case. Among others, I would set (and reload the server):
log_statement = none

Depending how you import the data, you probably need to do more than that. Look at:
log_min_messages

To skip writing log files altogether you can set:
log_destination = 'stderr' ...

More in the manual.


Why do SQL Server auto-created statistics sometimes include the primary key?

I have spent a decent amount of time searching for an explanation of the different naming conventions on some of my own databases, and haven't found anything definitive.
That being said, in both our cases we are dealing with very old databases that have undergone many upgrades over the years. My best guess is that at some point Microsoft switched the auto-created statistics to use an incremental alphanumeric pattern instead of using partial column names (somewhere around SQL Server 2000 or 2005?). This may have been done to reduce the potential for naming conflicts or to better handle the renaming of a column.
I haven't seen the Primary Key be added into auto-created statistics, but that might also be an artifact of a very old version.


How to model changing OrderLine rows in fact table

I should maybe create a list only with OrderNumber and Status column. Then I would create a mechanism that will delete all changed non-completed orders and load them again and another mechanism which will load newly completed Orders. I think the specific structure really depends on the structure of you Dw (how often do you perfrom loading, how many layer your solution has etc.)


Fill in NULL values with those from another another table

First you need to relate the first empty PIN in Employees with the first PIN in PINs, the second empty PIN with the second available PIN and so on. Then you will be able to use an update with a join and populate the rows thus related.

One way is to assign row numbers to both sets and use the row numbers as the joining criterion. As MySQL does not support the standard SQL ROW_NUMBER() analytical function, you have to do without it.

One alternative is to assign the row numbers with the help of a variable, like this:

SELECT
  s.EmployeeID,
  @r := @r + 1 AS RowNumber
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

The above produces a row set containing employee IDs and corresponding row numbers. Store the resulting set as a temporary mapping table and do the same for the PINs table, unless its PinID column already is a row number column. Based on your comment to Thronk's answer that the PINs table is imported from Excel, I am going to assume that the values in PinID have indeed been generated in Excel as row numbers essentially.

So, after you save the mapping set like this:

CREATE TEMPORARY TABLE Mapping
AS
SELECT
  s.EmployeeID,
  @r := @r + 1 AS PinID
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

you can use it in the UPDATE statement like this:

UPDATE
  Employees AS e
  INNER JOIN Mapping AS m ON e.EmployeeID = m.EmployeeID
  INNER JOIN PINs AS p ON m.PinID = p.PinID
SET
  e.PIN = p.PIN
;

You can play with this method at Rextester.


mysql trigger to insert/update date column from datetime column

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key,
     `dt1` datetime,
     `dt2` datetime,
     `d1`  date,
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1),
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1),
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo


Repreparation of prepared statements in MySQL/MariaDB, how to find a cause?

From the 5.1.25 Changelog:

  Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed.
  The server attempts repreparation up to three times. An error occurs if all attempts fail.
  Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES.
  The default value of the table_definition_cache system variable has been increased from 128 to 256. The purpose of this increase is to lessen the chance that prepared statements will need repreparation due to referred-to tables/views having been flushed from the cache to make room for new entries.

So...

Com_alter_table = 2496 = 3.5/hour -- This is rather high, and could cause the need for reprepare.
CREATE/DROP TABLE are also rather frequent.
I suggest increasing table_definition_cache.

Further analysis...

Observations:

Version: 10.1.26-MariaDB-0+deb9u1
64 GB of RAM
Uptime = 30d 00:53:13
You are not running on Windows.
Running 64-bit version
It appears that you are running both MyISAM and InnoDB.

The More Important Issues:

innodb_buffer_pool_size = 50G   -- from 1G
     -- Or the dataset size, if significantly less than that.
table_open_cache = 3000
innodb_open_files = 3000
table_definition_cache = 800
join_buffer_size = 1M
query_cache_size = 32M
max_connections = 200  -- Currently 151, and you ran out of connections some time
thread_cache_size = 30

Convert the rest from MyISAM to InnoDB

USE dbname and several SHOWs occur awfully often; what is going on?

A lot of slow queries, table scans, etc.  Look for the slowest few queries and work on improving them: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Did you deliberately turn off some of the optimizer_switches?  If so, why?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 1024M / 65536M = 1.6% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 1024M / 0.70) / 65536M = 2.4% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( Opened_tables ) = 6,996,394 / 2595193 = 2.7 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 5,643,679 / 2595193 = 2.2 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( innodb_buffer_pool_size ) = 1024M -- InnoDB Data + Index cache
-- woefully small.

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 65536M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( query_prealloc_size / _ram ) = 24,576 / 65536M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00% -- For parsing. Pct of RAM

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Qcache_lowmem_prunes ) = 54,439,645 / 2595193 = 21 /sec -- Running out of room in QC
-- increase query_cache_size

( Qcache_lowmem_prunes/Qcache_inserts ) = 54,439,645/193592210 = 28.1% -- Removal Ratio (pct of time need to prune)

( Qcache_hits / Qcache_inserts ) = 194,941,934 / 193592210 = 1.01 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 2696464) / 7979 / 16384 = 0.108 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Created_tmp_disk_tables ) = 13,897,242 / 2595193 = 5.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Select_scan ) = 168,942,798 / 2595193 = 65 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 168,942,798 / 402363586 = 42.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( binlog_format ) = binlog_format = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( Slow_queries ) = 169,941,950 / 2595193 = 65 /sec -- Frequency (Slow queries per sec)
-- Rework slow guys; improve indexes; watch disk space for slow log file

( Slow_queries / Questions ) = 169,941,950 / 712388796 = 23.9% -- Frequency (% of all queries)
-- Find slow queries; check indexes.

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 1,016,245 / ( 1016245 + 217503779 ) = 0.47% -- Subquery cache hit rate
-- Consider SET optimizer_switch='subquery_cache=off';

( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog.
-- This clutters the slowlog; turn it off so you can see the real slow queries.

( back_log / max_connections ) = 80 / 151 = 53.0%

( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections
-- increase max_connections and/or decrease wait_timeout

( Com_change_db / Connections ) = 245,625,094 / 14750877 = 16.7 -- Database switches per connection
-- (minor) Consider using "db.table" syntax

( Com_change_db ) = 245,625,094 / 2595193 = 95 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( Connections ) = 14,750,877 / 2595193 = 5.7 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Threads_created / Connections ) = 1,856,073 / 14750877 = 12.6% -- Rapidity of process creation
-- Increase thread_cache_size

Abnormally large:

Com_savepoint = 5.9 /HR
Com_show_create_table = 0.31 /sec
Com_show_events = 14 /HR
Com_show_function_status = 14 /HR
Com_show_procedure_status = 14 /HR
Com_show_tables = 2.4 /sec
Com_stmt_close = 139 /sec
Com_stmt_execute = 139 /sec
Com_stmt_prepare = 139 /sec
Com_stmt_reprepare = 0.24 /sec
Com_unlock_tables = 43 /HR
Feature_fulltext = 54 /HR
Feature_subquery = 24 /sec
Handler_discover = 0.072 /HR
Handler_read_rnd_deleted = 62 /sec
Handler_savepoint = 5.9 /HR
Sort_priority_queue_sorts = 137 /sec
Sort_range = 86 /sec
Sort_scan = 57 /sec
Subquery_cache_miss = 84 /sec
innodb_max_dirty_pages_pct_lwm = 0.10%

Abnormal strings:

innodb_cleaner_lsn_age_factor = HIGH_CHECKPOINT
innodb_empty_free_list_algorithm = BACKOFF
innodb_fast_shutdown = 1
innodb_foreground_preflush = EXPONENTIAL_BACKOFF
innodb_log_checksum_algorithm = INNODB
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
opt_s__mrr = off
opt_s__mrr_cost_based = off


Should my application utilize database credentials for user login?

Depends on who is allowed to access your application.

Probably not a good idea to give DB access to users since they'll be able to connect to the database directly which is probably not what you want.

If its a whitelist, maintain a list of users and if applicable which roles they hold in the database.

Run your web application using a Windows account, and grant access to that identity to the database.

Authenticate users using Windows Authentication, dont perform password management on your own.


PLSQL search procedure to return best and near matches based on multiple criteria

if I correctly understood, that criteria:

1) status = pending

2) company = PipedPiper

3) type=manual

4) date = Not selected

means (1 or 2 or 3 or 4) = true

if so, there I can see two way.
firstly you can use dynamic sql, secondly you can use case keyword. I think you know how to use dynamic sql, I will show example for using case keyword.

select * from your_table t

   where case
         when param_status != 'Not selected' and param_status = t.status then 1
         when param_status = 'Not selected' then 1 else 0 end = 1

         and case
         when param_company != 'Not selected' and param_company = t.company then 1
         when param_company = 'Not selected' then 1 else 0 end = 1

       ...

    -- Checking filter parameters, if non of selected then returning empty --
     and  case when param_status = 'Not selected' and param_company = 'Not selected' ...
         then 0 else 1 end = 1

I hope this will help you.


trying to execute SQL query on an oracle 10g database using java but getting ORA-00911 error

Nevermind. I jumped the gun on posting here! When executing queries in java, I am not suppose to put the semi-colon at the end of the statement. I had tried not putting the semi-colon at the end and it ran without errors but I thought I had checked my database and saw that no table was created. I have now confirmed that dropping the semi-colon has worked!


Database: Storing multiple Types in single table or multiple intermediate delta tables

For  the employee table you should have two audit tables.

  1 employee_audit (employee_audit_id , ref_employee_id, reason,update_date, update_by)

and

  2 employee_audit_detail (id,ref_employee_audit_id , field_name, old_value)

So you have every value saved only once and you have a complete record,what was changed and by whom grouped the date it was change.

Programming is every time you save data for an employee , you save first a new row in employee_audit  get the last id and then enter a new row for every changed value the name of the value and old  content


TSQL Re-Enabling Contraints take a long time

As expected.

For example, CHECK constraints: every row must be checked for every check constraint.

If you have 3 CHECK constraints and 370 million rows, that's 1.11 billion checks to be done in a single ALTER.

Each UNIQUE constraint must check all 370 million rows, in the same ALTER.

This is why I don't normally disable constraints in my DWs.
It takes too much time. Especially when you get to billions of rows.


Need to backup remote database using mysqldump

Unfortunately, you cant continue your failed backup. When mysqldump returns a failed status you have to start over again.

My first recommendation is to compress your script before transmitting it

mysqldump -h 192.168.10.209 -u user -ppassword testdb | gzip > dump.sql.gz

Another recommendation is to connect to the remote server via ssh, then make the backup in the remote server (never will fail for network issues because is a local backup) and, in the last step you could transmit the file (after compress of course).

Other than that, the other 2 options more used are PERCONA XTRA BACKUP and MYSQL ENTERPRISE BACKUP but there are also high prices involved.