Can I bring a warm standby SQL Server database online and then return to standby mode? What I'm trying to do falls outside of the normal SQL Server work flow so might be impossible.

I'm implementing a system that serves out copies of a large production database to multiple test / dev / training systems.  We're using a differencing-based file system (the Actifio implementation of ZFS) that allows these copies to be created quickly with low disk space overhead.  That system would normally be connected to a production database but that's making people nervous organisationally so I've been asked to operate from a warm standby database.

My problem is that Actifio can't see the standby database (it seems to use vsswriter to iterate databases and the standby database doesn't appear in the list that vsswriter iterates). The database is in standby read-only mode.

I can bring the warm standby database online so that Actifio can take an image but I then need to put it back to standby mode to continue applying logs and I can't find a reference that describes how to do this (or confirms that it's impossible)

Deleting the database and creating a new warm standby copy of production isn't an option as that disrupts the differencing engine's view of storage changes forcing a new full copy to be taken.  To put it another way, this will work but destroys the value of the ZFS-based disk space change tracking that allows us to hand out copies of this database with very low disk overhead.

Can I bring a warm standby database online temporarily and then return to standby mode and continue applying logs?  The database version is SQL Server 2008 R2

From what you are describing about what Actifio needs you should be able to put the secondary database in readonly mode after you restore the latest log file. See Kendra Little's post on log shipping secondaries in read only mode, she goes over some things that you need to account for. One of the things she mentions you'll need to account for putting the database in standby readonly is in order to continue restoring the next transaction log file you need to kick all of the users out of the database. That means you need to setup your restore scripts and the Actifio tool to work around each other.


When is the _id index used? When is the _id index used by MongoDB?

I could not find anything specific about it in Index Introduction, Analyze Query Performance, and Query Plans.

When sorting by _id at the end of the query via .sort({_id: -1}), .explain() explicitly shows the index use. It might be that it is implicitly used in every COLLSCAN operation, though.

So is it just a normal index that is only accessed when sorting/querying by _id, or does it have hidden uses not shown by .explain()?

In MongoDB, documents stored in a collection require a unique _id
  field that acts as a primary key. MongoDB uses ObjectIds as the
  default value for the _id field if the _id field is not specified;
  i.e. if a document does not contain a top-level _id field, the MongoDB
  driver adds the _id field that holds an ObjectId. In addition, if the
  mongod receives a document to insert that does not contain an _id
  field, mongod will add the _id field that holds an ObjectId.

ObjectId is a 12-byte BSON type, constructed using:

a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
a 2-byte process id, and
a 3-byte counter, starting with a random value.

MongoDB clients should add an _id field with a unique ObjectId. Using ObjectIds for the _id field provides the following additional benefits:

In the mongo shell, you can access the creation time of the ObjectId,
using the getTimestamp() method.
Sorting on an _id field that stores
ObjectId values is roughly equivalent to sorting by creation time.

Alternatives-
The following are common options for storing values for _id:

Use an ObjectId.
Use a natural unique identifier, if available. This saves space and avoids an additional index.
Generate an auto-incrementing number. See Create an Auto-Incrementing Sequence Field.
Generate a UUID in your application code. For a more efficient storage of the UUID values in the collection and in the _id index, store the UUID as a value of the BSON BinData type.
Index keys that are of the BinData type are more efficiently stored in the index if:
the binary subtype value is in the range of 0-7 or 128-135, and
the length of the byte array is: 0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16, 20, 24, or 32.
Use your driver’s BSON UUID facility to generate UUIDs. Be aware that driver implementations may implement UUID serialization and deserialization logic differently, which may not be fully compatible with other drivers. See your driver documentation for information concerning UUID interoperability.

for more detail-
See MongoDB


Oracle sort varchar2 column with special characters last How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order.  Or are any existing options available that will put letters first, then numbers, then all special characters.

Our first approach was using a function that does some manually mapping of characters to numbers.

select id, sorted_column
from some_table
order FN_SPECIAL_SORT_KEY(sorted_column,'asc')

The special sort function maps each character to a 2 digit number, and the return value is used for sorting.  This seems to be just really expensive concatenation, and it feels wrong.

        for i in 1..length(sorted_text)
        loop
            v_result:=v_result ||  case substr(sorted_text,i,1)
                WHEN ' '   THEN 82 WHEN  '!'   THEN 81 WHEN '"'    THEN 80 WHEN  '#'   THEN 79 WHEN  '$'
                ..............
                WHEN 'u'   THEN 15 WHEN  'U'   THEN 15 WHEN  'v'   THEN 14 WHEN  'V'   THEN 14 WHEN  'w'   THEN 13 WHEN  'W'   THEN 13 WHEN  'x'
                ....
                else 90 end;
        end loop;

I'm having a hard time coming up with an alternative approach.  I want to know what problems exist with this approach.  Perhaps we have no alternatives.

Addendum 1:

Adding example of sorted data.  In general,  all alpha characters case insensitive, then numbers 0-9, then special characters in any order.

Here is a sample sorted ascending list.  Keep in mind special characters are interchangeable, they all should be after letters and numbers.  In binary sort, some special characters are before letters (i.e.  '  )

My desired order,

AB1$
aCC#
ac'
BZ

Oracle binary order

AB1$
BZ
ac'
acc#

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so:

ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate

You can find a list of supported sort orders here.


How to query and group based on matched multiple conditions? I have a tableName with records as sample as following, the mainID is unique at all for same "name", but subID has different values (one or more) for same mainID(s),

name  mainID  subID
___________________
A     1234    12
A     1234    19
B     2341    12
C     3412    19
A     1234    3
B     2341    3
D     4123    12
C     3412    12
E     5123    3
F     1235    19

I want to have a result as following and mapping them with a "String" as sample like below.

name  mainD  subID
__________________
A     1234   X
B     2341   YY
C     3412   Y
D     4123   Z
E     5123   V
F     1235   Q

the mapping I'm defining are like below,

if (subID for same mainID matched _all_ 12, 19 and 3) then map it to "X"
if (subID for same mainID matched _only_ 12, and 3) then map it to "YY"
if (subID for same mainID matched _only_ 12 and 19 ) then map it to "Y"
if (subID for same mainID matched _only_ 19 and 3 ) then map it to "YYY"
if (subID for same mainID matched _only_ 12) then map it to "Z"
if (subID for same mainID matched _only_ 3) then map it to "V"
if (subID for same mainID matched _only_ 19) then map it to "Q"

What I tried was/is make them inside a CASE WHEN ... THEN ... END, but this matched one name like "A" print in multiple rows, with mapped to "Z", "V", "Q".

Can anyone please help to achieve the expected query result, thanks in advance

select      name
           ,mainID

           ,decode
            (
                "3" || "12" || "19"

               ,'ABC' ,'X'
               ,'AB'  ,'YY'
               ,'BC'  ,'Y'
               ,'AC'  ,'YYY'
               ,'B'   ,'Z'
               ,'A'   ,'V'
               ,'C'   ,'Q'
            )               as subID

from        mytable pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))

order by    name
;

select      name
           ,mainID

           ,decode
            (
                "3" || "12" || "19"

               ,'ABC' ,'X'
               ,'AB'  ,'YY'
               ,'BC'  ,'Y'
               ,'AC'  ,'YYY'
               ,'B'   ,'Z'
               ,'A'   ,'V'
               ,'C'   ,'Q'
            )               as subID

from        (select name,mainID,subID from ...) t
                 pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))

order by    name


PostgreSQL values to unique dictionary I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long URLs (text data type), used application names and similar duplicate information.

Is there some PG tool, script for PostgreSQL 9.3+ which can easily for selected columns create dictionaries of distinct values to other tables and after that update original values with SmallInt identificator from that dictionary? Do I have to write SQL for that manually?

TableOriginal
1;VeryLongURLText
2;VeryLongURLText
3;LoooongURLText
4;LoooongURLText
5;LoooongURLText

TableDictionary
1;VeryLongURLText
2;LoooongURLText

TableUpdated
1;1
2;1
3;2
3;2
3;2

Thank you.

Do I have to write SQL for that manually?

Yes, but it's not that hard:

create table original (id integer, url text);
insert into original
values
(1,'VeryLongURLText'),
(2,'VeryLongURLText'),
(3,'LoooongURLText'),
(4,'LoooongURLText'),
(5,'LoooongURLText');

create the dictionary

create table dictionary (id serial, url text);
insert into dictionary (url)
select distinct url
from original;

This creates the table with the following content:

id | data
---+----------------
 1 | LoooongURLText
 2 | VeryLongURLText

Now create a new table based on the dictionary:

create table compressed
as
select o.id, o.some_column, o.other_column, d.id as dictionary_id
from original o
  join dictionary d on o.url = d.url;

As your goal is to reduce the space overhead it's better to create  new table with the dictionary id rather then altering the existing one. This will also be a lot faster then updating all rows from the existing table (with a billion rows this will however still take some time)


Any way to maintain a partial index on only the highest element in Postgres? I need to

SELECT MAX(created_at) FROM someTable

whenever my application starts up. The table is enormous (billions of rows). The index on created_at that I created to make this query fast is using up multiple gigabytes. Is there a way to create some kind of partial index only on the highest element? Or any way to make this take up less space? Or an alternate approach entirely? All I ever need to query on this column is the maximum element.

The best is to take an alternative approach and not to query the large table all the time, if that is the only purpose of the index.

Create a second table that only contains a single timestamp and define a trigger on sometable that updates that small table whenever a new row arrives or an old one is updated.

This approach will only work well if you never delete the row with the latest created_at or never update created_at to an earlier timestamp.


Best practices in securing Oracle Standard edition one I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application uses Oracle to store data.

Best Wishes
Peter

You may want to work through the Oracle Project Lockdown documentation.  It takes you though the security process with increasing levels of security over time.


How to restore a SQL Server 2008 R2 database Here's my issue.

I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the SQL Server instance.
If I detach then reattach the database everything works fine, the two windows users are able to connect no problem.
If I detach the database, re-image the system, then re-attach the database the windows users will still be listed as I can see them via Management Studio.
I'd imagine that the attaching and detaching of the database has nothing to do with it since the logins are listed under 'Security' for the SQL Server instance and not the db.
At this point users exist and have rights, but they can not connect unless you delete and re-add the windows users, I believe this is because the SID's are messed up.

Any idea how to 'update' the sid's to reflect the current windows users and not the previous ones?

If the SIDs change on re-imaging then SQL Server will regard them as new Windows logins. They'll have to be dropped and re-added: as you are doing.

You can test for changed SIDs by using SUSER_SID('domain\somelogin').
The results should b e different before and after.

What you can do is to use a Windows Group instead. So the Windows group contains the 2 users. SIDs will change but you only have to drop and re-add the single group.

Now, you may be able to use a start-up stored procedure to test for SID differences using sys.server_principals and SUSER_SID. If different for a given name you can automate the DROP LOGIN, CREATE LOGIN, ALTER USER

Note: "logins" at the server level are expected to be static. SQL Server provides tools to remap a database-level user to a login via ALTER USER


Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor DBMS: SQL Server

Upgrading from version 2008 to 2016.

I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change.

  Error:  uses a constant expression which are not allowed in the ORDER BY clause in database compatibility level 90 or later

What am I missing here. To my understanding, I should not use constants here but like variables. Any help would be appreciated.

    DECLARE ForeignColumn1 CURSOR LOCAL STATIC FOR
    SELECT
      a.COLUMN_NAME
    , ROW_NUMBER() OVER (ORDER BY a.COLUMN_NAME) AS RowNumber
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
    WHERE 1=1
    AND a.CONSTRAINT_NAME = @Constraint
    AND a.TABLE_NAME IS NOT NULL
    AND a.COLUMN_NAME NOT LIKE
    CASE
        WHEN @SkipSite > 0 THEN
             @CurrSite
        ELSE
             ''
        END
    ORDER BY RowNumber

Going from 2008 to 2016 is jumping too far.
I suggest you to go move to 2012 first and then to 2016.

First of all download Microsoft Migration Assistant and try to spot incompatibilities.
Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
Spot the incompatible queries using the Query Tuning Assistant

Now that you are on 2012 repeat the steps 1, 2 and 3 and move to 2016.


oracle database - sql for counting the columns with the same id with the one in current row I have an oracle database and a few tables and with this query:

select rap.DENUMIRE,a3.ID,op.FK_ID,
    (SELECT COUNT(*) FROM ANEXA3_TABEL1_ROWS) AS count
from ANEXA3_TABEL1_ROWS a3
inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID

the result is:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      741 741     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      742 742     7

and I'd want to select in COUNT the number of columns with FK_ID equals to the ID in the current row. In this example, I'd want the following result:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      741 741     1
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      742 742     1

Thanks in advance!

select rap.DENUMIRE,a3.ID, op.FK_ID, count(*) over (partition by op.FK_ID) AS count
  from ANEXA3_TABEL1_ROWS a3
 inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
 inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID


Finding rows with duplicate values I have a one to many relationship between the Account table and a ScheduledPayment table. One account can have many payments but there should be a maximum of one payment per day.
Although unlikely, there are two scenarios which I need to cater for...

If there is one or more Payment records for an AccountId on a
day, with different payment and or reference then all payments
should be marked as cancelled.
If there is more than one Payment record for an AccountId on a
day, with the same payment and reference then one record should be
the winner and the other be marked as cancelled.

In reality, I don't anticipate seeing this scenario all that much because it is down to a concurrency issue in code, if two users are working on the same record at the same time.

Any ideas would be greatly appreciated!

Account
---------
AccountId
---------
    1
    2
    3
    4

ScheduledPayment
----------------
ScheduledPaymentId | AccountId | Amount | Reference | State
------------------------------------------------------------
       1           |    1      |  100   | ABCDE     | Live
       2           |    1      |  100   | ABCDE     | Live  (For these two, 1 would the winner and two marked as cancelled)
       3           |    2      |  100   | ABCDE     | Live
       4           |    2      |  110   | ABCDE     | Live
       5           |    2      |  110   | ABCDE     | Live
       6           |    2      |  130   | ABCDE     | Live (For these, all would be marked as cancelled)
       7           |    3      |  100   | ABCDE     | Live
       8           |    4      |  100   | ABCDE     | Live

This should return tag the records that need attention.  I put the tagging in SELECT, but you could easily turn this into a second CTE and simply select out the payments to clean up.

--
-- find all accounts with more than one payment and mark payments to cancel
--
WITH cte_DuplicatePayments AS
(
SELECT COUNT(*) OVER(PARTITION BY accountID) AS numberOfPaymentsPerAccountID
, COUNT(*) OVER(partition BY accountID, amount) AS numberOfPaymentsPerAccountIDAndAmount
, ROW_NUMBER() OVER(partition BY accountID ORDER BY amount asc) AS PaymentsNumberPerAccountID
, *
FROM ScheduledPayment
)
SELECT CASE
    WHEN numberOfPaymentsPerAccountID != numberOfPaymentsPerAccountIDAndAmount THEN 'MARK AS CANCELLED: Duplicate Payments with amount mismatch'
    WHEN PaymentsNumberPerAccountID > 1 THEN 'MARK AS CANCELLED: Duplicate Payments with matching amount'
    ELSE ''
   END AS PaymentAuditAction
, ScheduledPaymentID, accountID, amount,
FROM cte_DuplicatePayments
WHERE numberOfPaymentsPerAccountID > 1


XOR binary values greater than 64bit I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes.

hash1: string(64) "439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2"
hash2: string(64) "c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43" >
script combo: string(64) "838559e2bd662dc116882c04d326a967189b4da9545e985114019879b6f457f1"
mysql combo:  string(3) "439"

PHP:

$combo = bin2hex($hash1 xor $hash2);

MYSQL:

SELECT `hash1` ^ `hash2` as combo

As far as I can tell, the issue is that bit-wise XOR is limited to 64bit values, but I can't find a straightforward way to bi-wise XOR larger values.

Perhaps you could the following:

split each value into little two character strings
convert each 2-character string into a decimal numebr
XOR each decimal number side by side
CONCAT all results
Lower Case the Result

Sounds simple, eh??

Wanna see the code in MySQL ??

Here we go ...

SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Wanna see it executed ??? Here we go ...

mysql> SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----------------------------------------------------------------+
| HashResult                                                     |
+----------------------------------------------------------------+
| 838559e2bd662dc116882c4d326a967189b4da9545e98511419879b6f457f1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Sorry, it's late and I was not in the mood to do a Stored Procedure !!!

If you are interested why this works, this query let's look at the details:

SELECT *,LCASE(HEX(v1^v2)) v3 FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Here are the details:

mysql> SELECT *,LCASE(HEX(v1^v2)) v3 FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----+------+------+------+
| x  | v1   | v2   | v3   |
+----+------+------+------+
|  1 | 67   | 192  | 83   |
|  3 | 156  | 25   | 85   |
|  5 | 54   | 111  | 59   |
|  7 | 103  | 133  | e2   |
|  9 | 73   | 244  | bd   |
| 11 | 177  | 215  | 66   |
| 13 | 73   | 100  | 2d   |
| 15 | 224  | 33   | c1   |
| 17 | 35   | 53   | 16   |
| 19 | 140  | 4    | 88   |
| 21 | 36   | 8    | 2c   |
| 23 | 150  | 146  | 4    |
| 25 | 130  | 81   | d3   |
| 27 | 224  | 198  | 26   |
| 29 | 21   | 188  | a9   |
| 31 | 96   | 7    | 67   |
| 33 | 62   | 38   | 18   |
| 35 | 250  | 97   | 9b   |
| 37 | 70   | 11   | 4d   |
| 39 | 102  | 207  | a9   |
| 41 | 10   | 94   | 54   |
| 43 | 60   | 98   | 5e   |
| 45 | 100  | 252  | 98   |
| 47 | 125  | 44   | 51   |
| 49 | 8    | 28   | 14   |
| 51 | 188  | 189  | 1    |
| 53 | 230  | 126  | 98   |
| 55 | 154  | 227  | 79   |
| 57 | 24   | 174  | b6   |
| 59 | 74   | 190  | f4   |
| 61 | 122  | 45   | 57   |
| 63 | 178  | 67   | f1   |
+----+------+------+------+
32 rows in set (0.00 sec)

mysql>


Find a specific table and column value across all databases This is a question about logic that could help me understand more how to iterate into my DBs.

On my server I have several AdventureWorks* databases:

I have a query that cold help me iterate through all my databases, AdventureWorks* and not:

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB

select name from  sys.databases
WHERE database_id > 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB

For each AdventureWorks* database I want to list AWBuildVersion next to each DB:

At this point I'm puzzled because there are 2 problems I don't know how to solve:

How to run a select [Database Version] from AWBuildVersion for each database?
What to do if a database has no AWBuildVersion table?

Here I found this query:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'

That brings me close to my goal but it shows me the table name AWBuildVersion while I need the column Database Version = 11.0.2100.60

I would not want to do that with T-SQL because it limits me in the amount of servers I could get the results from.

Instead I would create a PowerShell script like this:

$servers = @( 'server1', 'server2')

$query = "SELECT [Database Version] AS DatabaseVersion FROM [dbo].[AWBuildVersion];"

$results = @()
$comment = @()

# Loop through the servers
foreach ($server in $servers) {
    $databases = Get-DbaDatabase -SqlInstance $server | Where-Object {$_.Name -like 'AdventureWorks*'}
    Write-Host "Going through server $server"

    # Loop through the databases
    foreach ($database in $databases) {
        # Reset variables
        $result = @()
        $data = $null
        $comment = $null

        if ($database.Tables.Name -contains 'AWBuildVersion') {
            Write-Host "- Querying database $database"

            $data = Invoke-DbaSqlQuery -SqlInstance $server -Database $database.Name -Query $query
        }
        else {
            $comment += 'Could not find table AWBuildVersion'
        }

        $result = [PSCustomObject]@{
            Server          = $server
            Database        = $database.Name
            DatabaseVersion = $data.DatabaseVersion
            Comment         = $comment -join ', '
        }

        $results += $result
    }
}

$results

You do need the dbatools powershell module to run it.

What it does is:

Iterates through the servers you assign.
Retrieves the databases that have 'AdventureWorks' in the name.
Iterates through the databases and executes the query.
If it cannot find the table it adds a comment to the results that the table could not be found.


Only do log backup if necessary in SQL Server We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup?

Some of the databases don't have any changes during the night, for example. But we'll still get many tiny log backups for these databases every time the log backup script runs.

I already tried scripting something based on the previous backup's last_lsn recorded in msdb and using fn_dblog to see if there were new committed transactions. I was not successful so far.

A bit of background:

These log backups are taken every few minutes and saved to Azure Blob Storage. They are not restored to another database (aka log shipping). During a single day we'll get a few hundred log backups per database. In case we ever have to restore something from Azure, I want to avoid having 100s or even 1000s of log files without any user transactions in them lengthening the restore process.

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO.  Your logic would look something like this:

CREATE TABLE #vlfs(
   RecoveryUnitID int
  ,  FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);

DECLARE @MBthreshhold int

SELECT @MBthreshhold = 5 --Enter your threshhold in MB here

INSERT INTO #vlfs
EXEC ('DBCC LOGINFO')

IF (select sum(filesize/1024/1024)
    from #vlfs
    where [Status] = 2) > @MBthreshhold
BEGIN
    BACKUP LOG [foo] to <<backup location>>
END

Please note there are a lot of caveats here.  Your transaction log will never be completely empty and there is always something to backup to it.  You can use this to determine at what size point you want to actually run your backup, but this can be a HUGE risk to your Recovery Point Objective (RPO).  Your log backups will happen at variable times, meaning you can never guarantee the maximum amount of data loss you will incur in a disaster.  This sort of strategy should be pursued with extreme caution.


Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D.

Example 1

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

The result is that all rows are deleted.

Example 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that the only difference is the change of what foreign key is RESTRICT from d. This example however, fails with

  Error Code: 1451
  Cannot delete or update a parent row: a foreign key constraint fails (hello.d, CONSTRAINT d_ibfk_1 FOREIGN KEY (id) REFERENCES b (id))

While logically, it's the same as Example 1. Without having looked at the source code of MySQL, I strongly suspect that the foreign keys are "applied" in lexical order based on their name. What will be the standard behaviour (ANSI-SQL) in this scenario?

I modified example 1 so that the syntax is accepted by all vendors that I tried. It turns out that the only DBMS of the tested ones that reject the scenario is Db2 DB<>Fiddle:

MariaDB 10.2, 10.3 Yes
MySQL 5.6, 5.7, 8.0 Yes
Postgres 11 Yes

Oracle 11g release 2, 18 Yes
SQLServer 2017 Yes
Db2 V11 No

Note that the foreign keys have to be slightly modified for Oracle and SQLServer. See links provided by Dinesh Kumar

Oracle
SQLServer

Db2 throws an exception like:

SQL20255N  FOREIGN KEY .. is not valid because it would cause a descendent table ... to be delete-connected to its ancestor table ... through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints ... and ... on the descendent table. Reason code = "3".  SQLSTATE=42915 SQLCODE=-20255

I skimmed through 7IWD2-02-Foundation-2011-12.pdf which can be download from:

http://www.wiscorp.com/sql20nn.zip

but I did not find anything mentioned regarding this.

To me, it seems as if Db2 behave sanely in this regard, but that's just my opinion.


SQL Server : how to disable connections to a linked server? SQL Server Linked Server to default instance - service is disabled but connection is still possible

I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed.

The SQL Server 2005 is running as the default instance, the SQL Server 2008 R2 is running as a named instance sql2008.

I have linked servers to swlon1.

I don't want the linked servers to the SQL Server 2005 to work anymore, so I stopped and disabled the swlon1 SQL Server services related to the default instance as you can see on the picture below.

Still people can browse the catalogs and see the tables on the SQL Server 2005 instance, even though the services are disabled.

I installed a named instance SQL2008 and I want that to be the only one available to the users.

Is there a way to achieve this, or I will have to drop all the linked servers to the default instance (SQL Server 2005)?

I could also uninstall the SQL Server 2005 from that machine.

I found the solution that I wanted:

-- how to disable a linked server

--check the existent linked servers
SELECT * FROM SYS.SERVERS
WHERE NAME = 'SWLON1'

-- Disable data access
EXEC sp_serveroption @server = 'SWLON1'
      ,@optname =  'data access'
      ,@optvalue =  'FALSE'

-- ABOUT procedure sp_serveroption
--https://msdn.microsoft.com/en-us/library/ms178532.aspx

-- the idea as how to disable a linked server
--http://www.sqlservercentral.com/Forums/Topic1038823-146-1.aspx


Global Read Lock for Snapshot of MySQL Data I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: Perform a Veritas cluster snapshot of the files, move them to the new place, and start replication.

Before I can start the cluster snapshot, I need to read lock so I don't lose any transaction (i.e., no SQL commands against the MyISAM table during the snapshot). I tried doing the following code but whenever I come out of the shell, the database becomes writable.

Is it the case that the read_only allows my ID to write since I own the lock? I am trying to google, but every article talks about mysqldump. Please help.

mysql> SET GLOBAL READ_ONLY=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2(a integer);
ERROR 1050 (42S01): Table 'test2' already exists
mysql> create table test3(a integer);
Query OK, 0 rows affected (0.00 sec)

When you ran SET GLOBAL READ_ONLY=ON; you stop users that do not have the SUPER privilege.

If every user has the SUPER, then setting read_only does not help.

Run this query

SELECT user,host FROM mysql.user WHERE super_priv = 'Y';

Whatever rows appear are the users that can still make changes even with read_only on.

SUGGESTIONS

What you really need to do is run

FLUSH TABLES WITH READ LOCK;

and hold that DB Connection open until you run your snapshot to completion.

How can you do so ? In my post How can I optimize a mysqldump of a large database?, I mention doing this in conjunction with a SLEEP command. Here is an except from that post under the heading "Option 4 : USE YOUR IMAGINATION" Point #2:

  Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete. This is helpful if a database contains both InnoDB and MyISAM

Here is some code from my 3-year-old post Writing transactions to .MYD file to take Backup

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

In your particular case, just replace the mysqldump with your LVM snapshot code.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

*** Snapshot Code Goes Here ***

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

GIVE IT A TRY !!!


Cannot add a column to an InnoDB table in Mariadb I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64).

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| current_version | varchar(20)  | NO   |     | NULL              |                |
| last_updated    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| key             | varchar(256) | NO   |     | NULL              |                |
| language        | varchar(20)  | NO   |     | NULL              |                |
| text            | text         | NO   |     | NULL              |                |
+-----------------+--------------+------+-----+-------------------+----------------+

When I try to add a column, the client hangs like so:

MariaDB [pkppln]> alter table terms_of_use add column `weight` int(11) not null default 0;
Stage: 2 of 2 'Enabling keys'      0% of stage done

The server continues to respond to other queries, but the client does nothing. I can kill the query with ctrl-C. The alter table command works in my dev environment, but seems to be failing here.

How can I fix this, and add the column?

What I did

MariaDB [test]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.0.15-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

Are you using 5.5?

CREATE TABLE terms_of_use
(

  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  current_version  varchar(20),
  last_updated     timestamp,
  keyx              varchar(256),  <== note keyx - fails if I use 'key'
  language         varchar(20),
  text
);

It appears that the problem is partly to do with the use of 'key' as
a field name.

INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');

MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+
| id | current_version | last_updated        | keyx     | language | text   |
+----+-----------------+---------------------+----------+----------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |
+----+-----------------+---------------------+----------+----------+--------+
6 rows in set (0.00 sec)

Then I added the column

alter table terms_of_use add column `weight` int(11) not null default 0;

Then SELECT again

MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+--------+
| id | current_version | last_updated        | keyx     | language | text   | weight |
+----+-----------------+---------------------+----------+----------+--------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |      0 |
+----+-----------------+---------------------+----------+----------+--------+--------+
6 rows in set (0.00 sec)

MariaDB [test]>


Error while creating an admin user in replica set I have a relatively simple replica set test setup between two VMs and my host machine, with VM1 as primary, VM2 as secondary and the host as arbiter. I have mongodb 2.6.4 installed on all machines. So far it works without any issue, however things go weird when I try to setup authentication. For now it is disabled, and I am just trying to create an admin user so that I can set auth to true and set the keyFile in the configuration.
I run the following commands on the primary (VM1):

use admin
db.createUser(
  {
  user: "admin",
  pwd: "blop",
  roles:
    [
      {
        role: "userAdminAnyDatabase",
        db: "admin"
      }
    ]
  }
)

However when I do that (on the primary, not the secondary), the secondary (VM2) crashes after throwing this error:

ERROR: error: exception cloning object in admin.system.users system.users entry must have either a 'pwd' field or a 'userSource' field, but not both obj:{ _id: "admin.admin", user: "admin", db: "admin", credentials: { MONGODB-CR: "85bc114ccxf991158fd4x8eb134834d8" }, roles: [ { role: "userAdminAnyDatabase", db: "admin" } ] }

What am I doing wrong?

Thanks in advance

EDIT: I found the issue, actually for some reason the mongod instance on VM2 was still on 2.4, which has an incompatible way of creating users and would crash when receiving new-style user data from the primary.

I found the issue, actually for some reason the mongod instance on VM2 was still on 2.4, which has an incompatible way of creating users and would crash when receiving new-style user data from the primary. I just fixed it by upgrading MongoDB on VM2


MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed although the presence of identity columns was checked in the generation script for the merge statement and identity_insert explicitly was turned on before the merge. However it still fails.

I created a smaller sample for demo that fails as well complaining about the IDENTITY Column:

  Cannot update identity column 'aid'.

I expect since I turned Identity_Insert ON that I can INSERT or UPDATE the value for the IDENTITY column however I like. But it does not work.

Here is the sample code:

CREATE TABLE [dbo].[tm2]
(
    [id] [int] NOT NULL,
    [aid] [int] IDENTITY(1,1) NOT NULL,
    [txt] [nchar](10) NULL,

    CONSTRAINT [PK_tm2]
       PRIMARY KEY CLUSTERED ([id] ASC)
            WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].tm2 ON

MERGE INTO [dbo].tm2 AS Target
USING (VALUES
  (1,2,'qdqewqf'),
  (2,3,'#ED7F00')
) AS Source ([ID], [aid], [txt]) ON (Target.[ID] = Source.[ID])

WHEN MATCHED AND (Target.aid <> Source.aid OR Target.txt <> Source.txt ) THEN
    UPDATE
    SET
       aid = Source.aid,
       txt = Source.txt

WHEN NOT MATCHED BY TARGET THEN
    INSERT([ID], aid, txt)
    VALUES(Source.[ID], Source.aid, Source.txt)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].tm2 OFF

Technical details:

SQL Server 2008 R2
Collation SQL_Latin1_General_CP1_CI_AS

Identity values cannot be updated.  They can be inserted if IDENTITY_INSERT is turned on for that table.

This code shows how that works:

USE tempdb;

CREATE TABLE dbo.TestIdentity
(
    ID INT NOT NULL
        IDENTITY(1,1)
    , SomeData VARCHAR(255) NOT NULL
);

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

--This works

SET IDENTITY_INSERT dbo.TestIdentity ON;

INSERT INTO dbo.TestIdentity (ID, SomeData)
VALUES (1, 'This is a test');

SET IDENTITY_INSERT dbo.TestIdentity OFF;

/*
    This fails with:

    Msg 8102, Level 16, State 1, Line 15
    Cannot update identity column 'ID'.
*/
SET IDENTITY_INSERT dbo.TestIdentity ON;

UPDATE dbo.TestIdentity
SET ID = 2
WHERE ID = 1;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

You may want to replace the IDENTITY column with a column that uses a manually incremented value.  If you were using SQL Server 2012+ you could use a SEQUENCE to populate the values.  Since you are using SQL Server 2008 R2, you will need to roll your own solution to generate values to replace the identity.  One such way is detailed here.

You could potentially resolve your problem using the OUTPUT clause to simultaneously delete the row, then insert it into the table with a modified ID value.  However, this is predicated on not using the merge construct.

/*
    This works, but cannot be used with MERGE
*/
TRUNCATE TABLE dbo.TestIdentity;

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

SET IDENTITY_INSERT dbo.TestIdentity ON;

DELETE
FROM dbo.TestIdentity
OUTPUT 2 /* The new identity value */
    , deleted.SomeData
INTO dbo.TestIdentity (ID, SomeData)
WHERE ID = 1 /* the old identity value */;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

SELECT *
FROM dbo.TestIdentity


SQL Server 2016 - Configuration Manager WMI Problem Background:

Windows Server 2016 Core
SQL Server 2016 Enterprise (obviously 64-bit)
Windows Failover Cluster
SQL Failover Cluster

I am attempting to connect to SQL Server Configuration Manager via my local Computer Management connecting to the database server. I expand Services and Applications and click on SQL Server Configuration Manager. However, this is the error message I see:

  Connection failed.
  Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

I have tried this in the command prompt:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"

Which then says:

  MOF file has been successfully parsed
  Storing data in the repository...
  Done!

Then I restarted WMI service, closed Computer Management, and reopened it. And I got the same error.

Does anyone know how to resolve this?

Thank you!

P.S. - I have posted this on another forum a few days ago and no one could help me resolve this issue

Update 1: I have turned off the firewalls on both client and server and attempted to connect to Configuration Manager which gave me the same error message

Update 2: My user account has domain admin privileges

Update 3: I ran the Get-WMIObject command and got SystemDirectory, BuildNumber, RegisteredUser, SerialNumber, and Version information so I'm guessing that I am allowed to access WMI remotely?

Update 4: I have SQLServerManager13.msc (located in the server's C:\Windows\SysWOW64) and attempted to open it on my local machine which gave me the same WMI error.

Update 5: I ran SQL Server repair on both SQL nodes (still same message)

Update 6: I ran winmgmt /verifyrepository and winmgmt /salvagerepository and both stated that the WMI repository is consistent

My solution: You need to have SQL Server 2016 installed on your local machine. Then you can use Computer Management to connect to your remote SQL Server Configuration Manager stuff.

Symptoms
On a 64-bit computer, you install an instance of the 32-bit (x86-based) version of Microsoft SQL Server. On the same computer, you install an instance of the 64-bit version of SQL Server 2008. If you then uninstall the 64-bit instance, you receive the following error message when you open SQL Server Configuration Manager:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

This problem also occurs if you uninstall the 32-bit instance, and you then open SQL Server Configuration Manager.
Cause
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.
Workaround
To work around this problem, open a command prompt, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"
Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2012   110
Microsoft SQL Server 2008 R2    100
Microsoft SQL Server 2008   100
Microsoft SQL Server 2005   90
After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.


Building a where clause based on user selections My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user chooses the fields to be considered).

For example, the user might want to know the number of people stored in the database that are male, over 20 years old, lives in NYC, blood type: A, and are single.

So I was wondering if there is a way to write the query in MySQL(MySQL 5.7.10.0) that returns the number of matching tuples (matches the user's selection and conditions) in the database, or if the jsc.jar in JAVA (java statistical classes) or any other JAVA jar can perform the required operation. I know that I should use the "count(*)" in the query, but I'm not sure how to build the "where" part of the query (that relies on the user's selection of fields) at runtime.

I'm looking for a way that a query builds itself (to be specific the "where" part) according to what the user chooses during runtime. Is it possible?

Non-dynamic dynamic where clause:

create table foo (
  gender char(1),
  age smallint,
  blood_type varchar(50)
);

insert into foo values
('m', 40, 'o+'),
('m', 20, 'b-'),
('f', 30, 'b+');

set @gender = 'm';
set @age = 40;
set @blood_type = null;

select
*
from foo
where
  (@gender is null or gender = @gender)
  and (@age is null or age = @age)
  and (@blood_type is null or blood_type = @blood_type)

http://sqlfiddle.com/#!9/c9d888/3


FileWatcher Troubleshooting Options I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working.  I am trying to figure out what I can check to troubleshoot why this isn't working.  After scaling back the code to a bare minimum, here are somethings I have already done.

Verify that files are being created in the folder monitored - They are.
Verify that the procedure can be run by itself - It can.
Verify that the File Watcher shows up in DBA_Scheduler_File_Watchers and is enabled. - It does and is.
Verify that DBA_Scheduler_Job_Run_Details shows a successful execution - It does NOT show any entries for this job.
Check the alert log - Nothing interesting.
Check for other trace files - I am getting trace files mattching *_j000_*.trc, but they just have this: FILE_TRANSFER error is: with nothing after it.

Could someone explain what I am doing wrong or give me a way to troubleshoot this further?  Here is my test code:

--Create Credential.
BEGIN
    sys.dbms_scheduler.create_credential(
        username => 'oracle',
        password => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        database_role => NULL,
        windows_domain => NULL,
        comments => NULL,
        credential_name => 'TestCredential'
    );
END;
/

CREATE TABLE FilesFound(FoundDate Date);

CREATE OR REPLACE PROCEDURE TestProcedure(iFileInfo In sys.scheduler_filewatcher_result) AS
BEGIN
   INSERT INTO FilesFound VALUES (sysdate);
   COMMIT;
END;
/

BEGIN
   DBMS_SCHEDULER.create_program(
      program_name => 'TestProgram',
      program_type => 'stored_procedure',
      program_action => 'TestProcedure',
      number_of_arguments => 1,
      enabled => False);
   DBMS_SCHEDULER.define_metadata_argument(
      program_name => 'TestProgram',
      metadata_attribute => 'event_message',
      argument_position => 1);
END;
/

BEGIN
dbms_scheduler.create_file_watcher(
   file_watcher_name => 'TestFileWatcher',
   directory_path => '/u01/test',
   file_name => '*.*',
   credential_name => 'TestCredential',
   destination => NULL,
   enabled => False);
END;
/

BEGIN
   dbms_scheduler.create_job(
   job_name => 'TestJob',
   program_name => 'TestProgram',
   event_condition => NULL,
   queue_spec => 'TestFileWatcher',
   auto_drop => False,
   enabled => False);

   --Enable Everything.
   dbms_scheduler.enable('TestProgram, TestFileWatcher, TestJob');
end;
/

--Set a one minute check interval.
BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/

--Create a file.
DECLARE
  vFile utl_file.file_type;
BEGIN
  EXECUTE IMMEDIATE 'create or replace directory TESTDIRECTORY as ''/u01/test''';
  vFile := utl_file.fopen('TESTDIRECTORY', 'TestFileWatcher.txt', 'w', NULL);
  utl_file.put_line(vFile, 'File has arrived '||SYSTIMESTAMP, TRUE);
  utl_file.fclose(vFile);
END;
/

--Delay to give time for file to appear.
BEGIN
   DBMS_LOCK.SLEEP(120);
END;
/

--Check the table.
BEGIN
   FOR vx IN (select count(*) ct from sys.FilesFound) LOOP
      If (vx.ct = 0) Then
         DBMS_Output.Put_Line('Failed - No File Watcher Activity.');
      Else
         DBMS_Output.Put_Line('Success - File Watcher Activity Found.');
      End If;
   END Loop;
END;
/

--Cleanup.
EXECUTE dbms_scheduler.drop_job(job_name => 'TestJob');
EXECUTE dbms_scheduler.drop_program(program_name => 'TestProgram');
EXECUTE dbms_scheduler.drop_file_watcher(file_watcher_name => 'TestFileWatcher');
EXECUTE DBMS_SCHEDULER.drop_credential(credential_name => 'TestCredential');
drop table FilesFound;
drop procedure TestProcedure;
drop directory TestDirectory;

To use file watchers, the database Java virtual machine (JVM) component must be installed.

Check whether java is installed :

COMP_NAME                                          STATUS
-------------------------------------------------- -----------
Oracle Expression Filter                           VALID
Oracle Enterprise Manager                          VALID
Oracle Workspace Manager                           VALID
Oracle Database Catalog Views                      VALID
Oracle Database Packages and Types                 VALID
JServer JAVA Virtual Machine                       VALID
Oracle XDK                                         VALID
Oracle Database Java Packages                      VALID

8 rows selected.

SQL> l
  1* select comp_name, status from dba_registry


Transfering data from one MongoDB server to another while eliminating users/authentication I am transferring MongoDB databases from one server to another.  The servers cannot talk to each other, so I must use mongodump/mongorestore.  The 'from' server used authentication for the databases.  The 'to' server is in a secure environment, so I'd like to completely obliterate any security/authentication features from the database.  Is it as simple as just deleting the *users

Yes, just db.system.users.remove({}); for each database.

It seems a bit harder to drop the system.users collection, see: https://jira.mongodb.org/browse/SERVER-4302 but it shouldn't hurt anything if it's empty.

The authentication part is enabled when you run mongod with -auth, so don't put that in your startup script or config file (comment out auth=true, or put auth=false if you're running 2.0+).


How can I aggregate multiple results from another table using something like BOOL_AND? I'm using PostgreSQL as my database.

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE associations (
  user      INT           NOT NULL,
  group     INT           NOT NULL,
  admin     BOOLEAN       NOT NULL DEFAULT false,
  PRIMARY KEY (user, group),
  FOREIGN KEY (user)
    REFERENCES users(id),
  FOREIGN KEY (group)
    REFERENCES groups(id)
);

Similar to another question I posted here, how can I compare the result of multiple rows from another table and aggregate them in a view, perhaps using something like BOOL_AND?

CREATE VIEW user_info AS
  SELECT DISTINCT
    u.id              AS id,
    u.name            AS name,
    EXISTS (
      SELECT 1 FROM associations AS a
      WHERE (u.id = a.user AND a.admin = true)
    )                 AS is_admin,
    BOOL_AND(???)     AS is_complete_admin
  FROM users AS u
;

Here, I'm wanting the is_complete_admin to be true if every row in associations for a particular user has admin = true.

Again, I think BOOL_AND is an option here, but I'm just not sure how to put it all together.

select
    u.id,
    u.name,
    bool_or (a.admin) as is_admin,
    bool_and (a.admin) as is_complete_admin
from
    users u
    inner join
    associations a on u.id = a.user
group by u.id, u.name


How to encrypt/secure single columns ins SQL Server? I would like to secure some columns in my data warehouse. People who are generally allowed to query the database should get returned the columns but encoded. For example:

SELECT PERSON_ID FROM PERSON

PERSON_ID
-----------
yba986SDOAH

When some else who has the key queries in some way the table/columns then he/she should get the correct value.

Which options do i have in SQL Server > 2016?

Although I am not an expert, I will suggest reading the following article on the c-sharp corner:

https://www.c-sharpcorner.com/article/implement-column-level-encryption-decryption-in-sql-server-2016/

This details the process fairly well and has some working examples posted in a way that i feel might suit your application.


Make sure date of departure is different than date of arrival? I have the following tables:

Flights (id_flight)
Airports (id_airport)
Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following attributes:

gate
date
type, which can be either 0 or 1, where 0 stands for departure and 1 stands for arrival.

Is there any kind of constraint to make sure that the date of the arrival is greater than the date of the departure?

Make a BEFORE INSERT TRIGGER, which checks in case of an Inserting a row for that flight and Airport the condition is met.

This is an example, you have ti check the conditions and error message if the condition is not met.

DELIMITER $$

CREATE TRIGGER before_Departures_Arrivals_insert
BEFORE INSERT
ON Departures_Arrivals FOR EACH ROW
BEGIN
    DECLARE _date  DATETIME;
    DECLARE _tyoe  INTEGER;

    IF NEW.tyoe = 1 THEN
        SET _type = 0;
    ELSE
        SET _type = 1;
    END IF;

    SELECT `date`
    INTO _date
    FROM Departures_Arrivals
    WHERE id_flight = NEW.id_flight AND  id_airport = NEW.id_airport
            AND type = _type
    ORDER BY `date` DESC
    LIMIT 1;

    IF NEW.`date` > _date THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = "The new date is before the arrival";
    END IF;

END $$

DELIMITER ;


Self blocking session we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC.

The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one task with blocking_session_id same as session_id. It's waiting for LCK_M_SCH_M, the others tasks are waiting for TEMPOBJ.

Both LCK_M_SCH_M and TEMPOBJ are weird. There is no scripts for changing/creating/droping schema. We are not using any temporary tables, only table variables and table-UDT as input parameters for several functions and stored procedures.

The task with LCK_M_SCH_M is inline-table-function with table-UDT (TblGuid).
The UDT TblGuid is table with single column:

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] NULL
)

The table-valued-function is unchanged for months and is called a lot. This behaviour became "out of nowhere".

If we restart the sql server service, all became ok. This state occures +- 10x in day.

I suspect the same session_id and blocking_session_id is invalid state and could not appears. Am I right?

EDIT:
The problematic function is:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM @IDsBattleTargets AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

The function is called from another function or from C# (.NET 4.0) via SqlConnection,SqlCommand and SqlParameter.

SOLUTION:
After advices I decided for change TVP to something another. I choosed the varbinary(max) where one Guid/uniqueidentifier value has 16 bytes. I created sql-clr function with varbinary(max) input and table output Guid uniqueidentifier. I won't use spliting text because it's not much efficient for my purpose (this function is called very often). After the 30 hours in production (the problem occured only on one server in production, and we was not able reproduce them in testing environment) there was no problem. Usualy the problem occures several times a day. This will be propably solution.

In the final the function looks:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets varbinay(max),
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM [dbo].[GetGuidsFromBinary](@IDsBattleTargets) AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

If someone will have similar issue, please contact me. I would like to more investigate this behaviour.

It's not blocking itself, this is just an odd artifact of how the waits are being reported. (And no, this isn't an invalid state, it happens all the time and is not indicative of any bug or deeper problem.) This has been reported a few times with table variables, and I think TVPs is a relatively newer one where there might still be some unresolved issues.

Some minor thoughts on optimization. First of all, why is the Guid column in the table type nullable? Why isn't it a primary key? The optimizer will be able to do a lot more with this if it understands the cardinality better, and your C# code is certainly capable of ensuring that it doesn't populate the parameter with duplicates or null values.

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] PRIMARY KEY
);

Now, the function has a lot of joins that I think are unnecessary, and if you can perform the grouping outside the function that may be better. I don't have sample data to verify that this returns the same results as your existing function, but if it's off it shouldn't be off by much. When you get it returning the same results, please do compare the actual execution plans and I am confident you will find this version is better.

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer  UNIQUEIDENTIFIER
)
RETURNS TABLE
WITH SCHEMABINDING -- added this
AS
RETURN
(
    SELECT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM dbo.tblUnit AS U
    WHERE U.Amount > 0
    AND EXISTS
    (
      SELECT 1 FROM dbo.tblBattleTarget AS BT
        WHERE BT.BattleTargetID IN (SELECT Guid FROM @IDsBattleTargets
          WHERE Guid = U.IDBattleTarget))
    )
    AND EXISTS
    (
      SELECT 1 FROM cache.[tblHeroSharingWithPlayerClan]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
      UNION ALL
      SELECT 1 FROM cache.[tblHeroSharingWithAlliance]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
    )
    -- only if necessary:
    -- GROUP BY U.IDUnitCass, U.IDBattleTarget, U.Amount
);


Global command to destroy all disconnected data files in Oracle 11g Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database?

Here is the scenario:

Every user-defined user and user-defined tablespace was deleted from an Oracle 11g R2 database by successfully executing the following commands for each and every user-defined user and tablespace:

DROP USER username CASCADE;
DROP TABLESPACE tablespacename INCLUDING CONTENTS AND DATAFILES;

However, the folder containing the .dbf files corresponding with all the dropped tablespaces still contains all the same .dbf files, each with the same original file size.  It is thus as if the data has simply been detached from the database without destroying the underlying datafiles.

I hesitate to simply delete all of the corresponding .dbf files out of fear of side-effects.  So is there a global command in Oracle 11g R2 that will enable an administrator to safely delete all unused data files that were once connected with the Oracle 11g R2 instance?

I am using SQL Developer and SQLPlus, so any command or utility either of these tools could work.

Based on your description of this behaviour and your other comment, I guess your database runs on Windows.

The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation.

Before dropping tablespaces or datafiles, take them offline:

alter tablespace ... offline;

or

alter database datafile ... offline drop;

And drop the datafile/tablespace after the above:

drop tablespace ... including contents and datafiles;

or

alter tablespace ... drop datafile ...;

If they still remain there, just simply delete them at OS level. And no, there is no such command thats deletes datafiles from the database that the database does not know of.

Reference:

Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted (Doc ID 389467.1)


Identical Servers but different query execution plans We have two server with identical hardware configuration and SQL server configuration.
The only difference in the server's being:

Server SQL1 - 256 GB RAM

Server SQL2 - 128 GB RAM

The same database has been restored on both servers. The query execution plan for a query on both servers is different.The MAXDOP on both servers is 4. But the query runs in 10 mins on SQL1 but takes 1 min on SQL2.

What would cause the query execution plans to be different? SQL1 where the query is slower, does have more load on it when compared to SQL2. Does load matter when SQL server decides on a query execution plan?

We set MAXDOP = 1 on SQL1 and we saw the query complete in 1 minute.

What would explain this?

Thanks

To me it looks like that the statistics are way off on the server with the bad plan.
For instance, Audit_New.dbo.RevOrder shows an estimated rows of 40k vs. actual rows = 8k.
I'd recommend to rebuild/reorganize the indexes involved in the query on the poorly performing server.


What would I need to do to make sure my DB policies have to pass a security audit? I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system.  I'm really new to this process and any guidance would be appreciated.

I applaud your effort, however the question asked is probably too vague. Different rules apply to different industries and furthermore, sometimes you have the opportunity to set your own rules, you just need to follow them.

I would suggest checking with someone as to what audit you are expected to pass, then finding the specific requirements.


Migration from SQL Server 2008 R2 to SQL Server 2012 SP2 I have many backups on my SQL Server 2008 R2 system, and I must restore them on new SQL Server 2012 SP2.

My question is, after the restore on new SQL Server 2012, what can I do to verify that everything is ok?

I'm sure that it is necessary to change the compatibility level, but is it necessary to run a DBCC CHECKDB and an UPDATEUSAGE command?

Is there a need to perform other particular checks? What's the best way?

Cheers

As you noted, you'll be running the DBCC CHECKDB command (note that DBCC just means "Database Console Command", whereas CHECKDB is the command it is executing).

This will be sufficient to ensure that the databases have survived the restore process.

For complete peace of mind, you can issue this command on each database:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS;

EXTENDED_LOGICAL_CHECKS will do extended checks on "indexed views, XML indexes, and spatial indexes, where present" (per the documentation);
NO_INFOMSGS returns no results if no errors were detected.

Incidentally, updating the compatibility isn't a required step. The databases can stay at the 100 compatibility level until you're ready to take advantage of the newer features of 2012 that require a newer compatibility level.

If you perform your backups with the CHECKSUM option, you can do an additional verification check during the database restore for added peace of mind.


Is there are a way to restore System schema to it's original state? I've installed Oracle 11g Database Enterprise Edition and my task was to import schemas. I'm totally new so I ran command:

imp system/password file=path.dmp full=y

And import everything (10 dumps, 150+ tables) into System schema. Could I restore this Schema to original state without reinstalling database? Or maybe you could delete directory with all of this tables and reinstall database, without harming listeners and tnsnames.ora?

There is no way to restore SYSTEM schema to the original state of empty database if you haven't backups, so you should recreate database. You could do this with Database Configuration Assistant (I'm using Windows Server 2012): run it, select delete database option, select database you want to delete, enter password to dba user and execute it.

After this you could recreate database with options you need.


Backing up SQL Server Express Edition on a VPS I'm considering installing SQL Server Express Edition on a VPS.

My understanding is that, among the limitations of Express Edition, some of them involve features that facilitate backups.

Can someone explain some options for performing daily backups for this configuration. Is there a straight forward way, or do I need to buy additional software?

(Sorry if the question seems dumb. I'm a software developer and not at all a database or admin guy.)

Your best bet would be to use ola Hallengren's utilities

https://ola.hallengren.com/

from the faq

How do I get started with the SQL Server Maintenance Solution on SQL Server Express?

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

    Download MaintenanceSolution.sql.
    Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
    Create cmd files to execute the stored procedures; for example:
    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
    In Windows Scheduled Tasks, create tasks to call the cmd files.
    Schedule the tasks.
    Start the tasks and verify that they are completing successfully.

Another option would be to use the DBATools Powershell module and create and schedule powershell scripts.


Upgrading hardware for MySQL. If we get SSDs, are CPUs likely to become a bottleneck We are upgrading hardware for a MySQL database server (DB is around 200GB).
We're getting 4x Samsung 850 Pro SSDs and are planning to run them in a RAID10 configuration.  Also, 128GB of RAM.

The quote from our supplier includes dual 6 core Xeons (E5-2620V2 2.1G) with an option to upgrade to E5-2630V2 2.6G instead.

The price for the faster CPUs would be about $622 extra (about $311 each), which is around 6.5% of the price of the server.

Since we don't have the hardware, of course, I can't benchmark it and see whether the faster CPUs would make a difference for our database and the way we query it.  But is there some way to get an idea of whether the faster CPUs will help?  i.e. assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck?

Thanks.

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change buffer, innodb checksums, transaction logs, doublewrite area, etc.).

Check some of the vendor benchmarks (Oracle MySQL, MariaDB, Percona), as they usually boast its capabilities on those kind of devices.

However, a generalization cannot be reached- most of the problems are load-dependent. To give you an extreme example: if your queries all look like this:

SELECT AES_DECRYPT(*, key) FROM my_table;

Your CPU is probably going to be your bottleneck, while if you only insert new rows, then most of the delay will probably due to the disk latency. You should check if with the current hardware, the CPU load is high, that will probably increase with the new storage- but you may still hit other problems, like concurrency issues due to several threads trying to update the same row at the same time.

  assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck

It is possible, but just with a bad configuration (what I mentioned before), assuming recent versions of MySQL (5.6, 5.7). Remember that SSDs are still way slower than the 1st cache level. You should probably ask again when the hardware is installed and meanwhile read sources like:

http://www.percona.com/resources/technical-presentations/ssdflash-modern-open-source-databases-percona-mysql-webinar (generalities on ssds for databases)
http://dimitrik.free.fr/blog/archives/2013/10/mysql-performance-the-road-to-500k-qps-with-mysql-57.html (check the configuration used)
http://www.percona.com/blog/2013/10/03/inexpensive-ssds-database-workloads/ (optimizing MySQL for SSDs)
http://www.percona.com/live/mysql-conference-2013/sessions/you-know-ssd%E2%80%99s-make-mysql-fast-do-you-know-why
http://www.percona.com/live/mysql-conference-2012/sessions/transitioning-ssds-high-io-environment.


MySQL right join with tables with 100's of 1000's of rows The following join is taking far too long in MySQL, almost half a day and it’s still not finished:
INSERT INTO `aaaaa`
SELECT *
FROM `chosenLang`
RIGHT JOIN `chosenLOCC_P`
    ON SUBSTRING_INDEX(SUBSTRING_INDEX
      (SUBSTRING_INDEX(`chosenLang`.`call`,'.',1),' ',1),'*',1)
       LIKE concat(`chosenLOCC_P`.`code`,`chosenLOCC_P`.`reference`)

chosenLang has  413826 rows, 42 columns (38 of them longtext, 3 of them varchar, 1 of them int(11)). call is varchar.
chosenLOCC_P has 117077 rows, 23 columns (21 of them longtext, 2 of them int(10)).
What can I do to maximise the speed of this query?

Make new columns to handle those clumsy expressions.
Make them reasonably sized VARCHARs, not LONGTEXT.
Write a 1-time script to populate those column(s).
Change code so that future INSERTs (etc) maintain the columns.
Add a suitable index so that the JOIN works efficiently.
Use JOIN unless you really need RIGHT JOIN.  Are you currently hoping to get a lot of NULL columns.


Arithmetic overflow error for data type smallint, value = xxxxx Same query run on two identical servers of the same SQL version(including SP/KB level) with identical server and database level settings errors out on one server(Arithmetic overflow error for data type smallint, value = xxxxx.) but executes successfully on the other one.
Any ideas?

Thanks,
VJ

Query:

SELECT
       c.cost_owner_key1
       ,pl_qty =
        CASE
            WHEN pa.PnlOwnerCode='C' AND pa.PnlOwnerSubCode = 'PR' THEN
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN -1
                        WHEN 'Sell' THEN 1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
            ELSE
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN 1
                        WHEN 'Sell' THEN -1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
        END
    ,pl_qty_factor =
        CASE
            WHEN c.cost_qty_uom_code = i.contr_qty_uom_code THEN 1
            ELSE (a.secondary_actual_gross_qty / a.ai_est_actual_gross_qty)
        END
    ,contr_qty_uom = c.cost_qty_uom_code
    ,alloc_num = c.cost_owner_key1
    ,alloc_item_num = c.cost_owner_key2
       ,CAST(c.cost_owner_key2 AS smallint)
FROM
    pc_pnl_cp2_pnl_asof pa
    INNER JOIN cost c WITH(NOLOCK) ON c.cost_num = pa.PnlRecordKey
                     AND pa.PnlOwnerCode = 'C'
    INNER JOIN ai_est_actual a WITH(NOLOCK) ON a.alloc_num=c.cost_owner_key1
                              AND a.alloc_item_num=c.cost_owner_key2
                              AND a.ai_est_actual_num=c.cost_owner_key3
    INNER JOIN trade_item i WITH(NOLOCK) ON i.trade_num = pa.TradeId
                           AND i.order_num = pa.OrderNumber
                           AND i.item_num = pa.ItemNumber
WHERE
    pa.CostStatus != 'CLOSED'
    AND pa.CostOwnerCode = 'AA'
    AND pa.CostType = 'WPP'

You mentioned database and server-level settings are the same, but how sure about that are you?  And is it possible something is changing a setting at the session level?

This produces the error for me (of course):

SELECT CAST(40000 AS smallint);

But this does not - it just returns NULL as the value:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SELECT CAST(40000 AS smallint);

Check to make sure the session doesn't have these SET options disabled by running this on both machines and seeing if the value is different:

SELECT @@OPTIONS;

You could also check to see if this is set at the server level using sp_configure:

EXEC sp_configure 'user options';

The more likely situation is that, due to having a different execution plan on the different servers, the higher value is being filtered out before being cast.  Solutions to that are covered here (and in the linked duplicates):

CAST throwing error when run in stored procedure but not when run as raw query


Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans? In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index.

The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking the same values in the same order.

Both nested loops have <NestedLoops Optimized="false" WithOrderedPrefetch="true">

Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?

(The reason for the question is that the first query was suggested to me as an optimisation due to the apparent much lower plan cost. It actually looks to me as though it does more work but I'm just attempting to explain the discrepancy...)

Setup

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

Query 1 "Paste the plan" link

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Query 2 "Paste the plan" link

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Query 1

Query 2

The above was tested on SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

@Joe Obbish points out in the comments that a simpler repro would be

SELECT *
FROM staging AS S
  LEFT OUTER JOIN Target AS T
    ON T.KeyCol = S.KeyCol;

vs

SELECT *
FROM staging AS S
  LEFT OUTER JOIN (SELECT * FROM Target) AS T
    ON T.KeyCol = S.KeyCol;

For the 1,000 row staging table both of the above still have the same plan shape with nested loops and the plan without the derived table appearing cheaper, but for a 10,000 row staging table and same target table as above the difference in costs does change the plan shape (with a full scan and merge join seeming relatively more attractive than expensively costed seeks) showing this cost discrepancy can have implications other than just making it harder to compare plans.

Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?

Generally speaking, an inner side seek below a nested loops join is costed assuming a random I/O pattern. There is a simple replacement-based reduction for subsequent accesses, accounting for the chance that the required page has already been brought into memory by a previous iteration. This basic assessment produces the standard (higher) cost.

There is another costing input, Smart Seek Costing, about which little detail is known. My guess (and that is all it is at this stage) is that SSC attempts to assess inner side seek I/O cost in more detail, perhaps by considering local ordering and/or the range of values to fetch. Who knows.

For example, the first seeking operation brings in not just the requested row, but all rows on that page (in index order). Given the overall access pattern, fetching the 1000 rows in 1000 seeks requires only 2 physical reads, even with read-ahead and prefetching disabled. From that perspective, the default I/O costing represents a significant overestimate, and the SSC-adjusted cost is closer to reality.

It seems reasonable to expect that SSC would be most effective where the loop drives an index seek more or less directly, and the join outer reference is the basis of the seeking operation. From what I can tell, SSC is always attempted for suitable physical operations, but most often produces no downward adjustment when the seek is separated from the join by other operations. Simple filters are one exception to this, perhaps because SQL Server can often push these into the data access operator. In any case, the optimizer has pretty deep support for selections.

It is unfortunate that the Compute Scalar for the subquery outer projections seems to interfere with SSC here. Compute Scalars are usually relocated above the join, but these ones have to stay where they are. Even so, most normal Compute Scalars are pretty transparent to optimization, so this is a bit surprising.

Regardless, when the physical operation PhyOp_Range is produced from a simple selection on an index SelIdxToRng, SSC is effective. When the more complex SelToIdxStrategy (selection on a table to an index strategy) is employed, the resulting PhyOp_Range runs SSC but results in no reduction. Again, it seems that simpler, more direct operations work best with SSC.

I wish I could tell you exactly what SSC does, and show the exact calculations, but I don't know those details. If you want to explore the limited trace output available for yourself, you can employ undocumented trace flag 2398. An example output is:

Smart seek costing (7.1) :: 1.34078e+154 , 0.001

That example relates to memo group 7, alternative 1, showing a cost upper bound, and a factor of 0.001. To see cleaner factors, be sure to rebuild the tables without parallelism so the pages are as dense as possible. Without doing that, the factor is more like 0.000821 for your example Target table. There are some fairly obvious relationships there, of course.

SSC can also be disabled with undocumented trace flag 2399. With that flag active, both costs are the higher value.


Is there a way to de-orphan users without setting context to a restored database? I have an installation script that restores a database from a template.bak file and then makes a few alterations. This question flows on from another question: How do I create databases / logins / users with minimal privileges when installing an application?.

Prior to running the installation, a script is executed that creates two SQL Login accounts MyAdmin and MyUser with what I believe is the necessary privileges (MyAdmin is used by the installer and a server management application, MyUser is used by the client application):

create login MyAdmin with password = 'TemporaryPassword'
exec sp_addsrvrolemember 'MyAdmin', 'diskadmin'
grant create any database to MyAdmin
grant alter any database to MyAdmin
create login MyUser with password = 'TemporaryPassword'

In the installation script I connect with the MyAdmin account and restore the template.bak file. I can see that the database is created and MyAdmin is the owner of the database. However:
- the instruction use MyNewDatabase fails with The server principal "MyAdmin" is not able to access the database "MyNewDatabase" under the current security context.
- if I login as sa in my test environment, I can see that the dbo user in the database has a user type 'SQL user without login'.

This leaves me very surprised. How is that the MyAdmin user can create a database, but cannot use it? Why is the dbo user not assigned to the owner / creator of the database?

The advice I have read so far is to use alter user MyAdmin ..., however I need to switch context to the database use MyNewDatabase to accomplish this - which I cannot do while the login is not linked to the user - a very frustrating catch-22.

I have found that I can resolve the issue by performing all of these tasks with the SA account, however I will not have access to the SA account with the installer as this is a commercial application.

Is there a way to de-orphan the accounts without setting context to MyNewDatabase?

When creating the login principals, set the SID of the login to the original login.

Original server select name, sid from templatedb.sys.database_principals to get the SID

New server create login MyAdmin with password = 'DummyPassword951', sid = 0xBCB4D3AC87D6F449979D6810B8E9E250

Edit: FYI MyAdmin is the owner of the template database and is mapped to the dbo user. In the installed version of the database, I have mapped the MyAdmin login to the dbo user, using the dbo SID from the template DB.


Collate view with old record on couchdb I need to create a collate view with couch db but I dont want to use the current records, I want to use records of a specific revision. For example, lets imagine that I have an invoice with an array of products ids (and theirs revision), I want to return the products with the description and prices as was at the invoice creation.

Normally you should never do so because other revisions may be deleted after compacting the database.
You should include all necessary fields to recompose(reprint) the invoice in the invoice document, like: product description, product price, total, and so on.

This is the same if you would store invoices in a rdbms.


What type of files are C-9999-yyyymmdd-00 in Oracle At my {ORACLE_HOME}\database I found like 3GB of this type of files:

I think they are not backup of controlfile because size of files changes over time.

They're control file backups.

The reason for the file size discrepencies is due to the fact that they're for different databases. Order the list by filename and it'll be more obvious. Just for your information, the first number after the C- prefix is the DBID (database ID).


SQL queries failing, Memory not releasing I have a SQL Server 2008 R2 with 8GB of ram and SQL Server is limited to 6GB. It seems to not be releasing the memory its used, and at some point future queries begin to fail. This is a mirrored server and the problem is solved after being failed over. Is there a way to force the Memory to always be released, or a known issue with limiting the memory?

There was a problem with the same server long ago, before SQL Server had a memory limit where it would use and use the memory until it crashed the server.

This is a Windows Server 2008 OS.

SQL Server is kind of a memory hog. You could try clearing the cache, BUT I would highly recommend doing some reading on these commands first before you using them. From what I hear, their can be negative side effects when using these especially in a production environment.

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE

Here are some links I found useful:

Difference between FreeProcCache and FreeSystemCache

https://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/

http://jamessql.blogspot.com/2012/04/clean-sql-server-cache.html


MySQL - "CREATE_OPTIONS" in "information_schema.tables"? I have a MySQL 5.7.16 Enterprise running on a Centos 6 machine with InnoDB as the default storage engine. I have around 300 tables in which around 90 are "list" partitioned.

I have the option innodb_stats_on_metadata=0 in my server configuration.

When I query the metadata using the query,

SELECT * FROM information_schema.TABLES WHERE TABLE_sCHEMA = 'mydb';

I can see the column CREATE_OPTIONS as empty for some non-partitioned columns, row_format=DYNAMIC for some non-partitioned tables, partitioned for some partitioned tables and row_format=DYNAMIC partitioned for some partitioned tables.

Why does this happen when all my tables have the same row format "Dynamic"?

I have table collations as "utf8_general_ci" for some tables and "latin1_swedish_ci" for some tables. Does this have something to do with the above scenario?

I got answer for my question. "CREATE_OPTIONS" gets updated as row_format=DYNAMIC when you specify it in the create statement explicitly. For partitioned tables, it takes row_format=DYNAMIC partitioned.

Table collation has nothing to do with it.


User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server I have a database on SQL Server. Yesterday It went to Suspect Mode, To recover that from the suspect mode I have followed some steps (Run some commands). But while running those commands it is giving me the error like : "User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server". To resolve that issue I ran the SSMS with 'run as administrator' privilege, and I also tried to change the permissions for the database for the user, But it is not working. I am not able to delete that also and this is the second time I am facing this issue Now I have 2 databases in suspect mode

.

It seems like there was a problem during the restoration process.
This error has nothing to do with permission. A database in suspect mode cannot be used or altered.
As mentionned, you will have to fix the issue with the database first (before you can run a "USE" or an "Alter database" on it).
If you do have backup for those database, restore the database from your backup in order to fix the issue.
If you don't have backup, then it will be a bit harder and information about how your DBs went into suspect mode would be nice to have in order to see the best approch to bring them back online (potentially with data loss or corruption).
You may have to set the database into the Emergency state before running a checkdb with repair_allow_data_loss on the database (which does exactly as it said)


Does plan recompilation happens to plan guide? Does plan recompilation happens for plan guide due to the, for example, auto update stats?

P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide

As I understand it, a plan guide is not compiled like an execution plan. Therefore, it cannot be recompiled. That being said, a plan guide does result in an execution plan, either through its hints or by specifying a specific plan to use. The resulting or specified execution plan could be affected by auto update statistics.

If auto update statistics determines that a statistics update is necessary, query plans dependent on those statistics would be recompiled.