Detaching a database permanently If a database is detached from an instance permanently, are there any cleanup tasks that should be done?

If you detach a database from an instance, you will need to perform an OS-level delete of the file.  The safer approach is to drop the database instead.

What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of a Drop Database command.

The full set of commands would look similar to the following:

-- Use master db to ensure you don't have an active connection to the db you wish to affect
USE [master]
GO

-- This will kill any active transactions, but will force the database into a Read-Only state
ALTER DATABASE [db_name] SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [db_name] -- Fill in more options here or use the UI to take a backup if you chooose
GO

-- This will kick out all connections from the database allowing you to drop it.
ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Drop the database (which automatically removes the files from the OS)
DROP DATABASE [db_name]
GO

After this, you'll want to look for any Jobs that ran scripts against the database.  I would suggest you just wait to see what fails (after which you can script out/delete the job) as there are numerous ways a job can reference a database (not all of which are easy to identify).

Finally, you'll want to remove any users from the instance that only had access to this database.  This script should identify whom those users are, though Max's version is much cleaner (I didn't realize he posted an approach until after I edited my answer to include this):

DECLARE @ExecString NVARCHAR (4000)

-- Create Empty Table in a very lazy manner
SELECT  name, principal_id, CAST('' AS NVARCHAR(128)) as database_name
INTO ##tmp_AllDBUsers
FROM sys.server_principals
WHERE 1 = 2

-- Declare Cursor to iterate through all DBs on the instance
DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases

DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ExecString =
    'USE [' + @name + '];
    INSERT INTO ##tmp_AllDBUsers
    SELECT sp.name, sp.principal_id, DB_NAME()
    FROM sys.server_principals sp INNER JOIN sys.database_principals dp
        ON sp.sid = dp.sid'

    EXEC(@ExecString)

    FETCH NEXT FROM dbCursor
    INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

-- Show all logins that do not belong to a server-level role nor have access to any databases
SELECT sp.*
FROM sys.server_principals sp LEFT JOIN ##tmp_AllDBUsers adu
    ON sp.principal_id = adu.principal_id
WHERE adu.principal_id IS NULL
    AND sp.principal_id NOT IN (SELECT member_principal_id
                            FROM sys.server_role_members)
    AND TYPE IN ('S', 'U', 'G')

-- cleanup
DROP TABLE ##tmp_AllDBUsers


What is the difference between 'two way transactional replication' and 'peer to peer replication'? Can someone explain in detail the exact differences between 2 way transactional replication and peer to peer replication?

generally transactional has a publisher and a subscriber, with some updating from the subscriber, peer to peer replication shares the same data in more of a node environment (often over long distances) and would be more suitable for load balancing.

I'd go straight to technet for the overview    http://technet.microsoft.com/en-us/library/ms151196.aspx


migrate users from mariadb10.1 to mysql5.7 My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7).

Dumping / restoring our application schemas looks straightforward. However I'm trying to determine how I can migrate my user accounts given the following:

mysql.user table structure differs between the two; I can't dump and import mysql.user
pt-show-grants, for some users, does not output statements to create the user. For instance, for some users, pt-show-grants only outputs something like GRANT INSERT, SELECT, UPDATE ON `schema`.`table` TO 'username'@'x', which fails because the user 'username'@'x' doesn't exist yet on the MySQL5.7 instance.
mariadb10.1 does not have a 'show create user' or equivalent statement.

Has anyone made this kind of move before and encountered this issue? I am hoping to avoid a wild-goose-chase for dozens of passwords in order to recreate the users manually; some of these users predate me and I have no record of the assigned passwords.

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier.

Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user and priv tables physically). However, in some cases (in compatible permissions, roles, plugins), you may want to review after sending the sql for errors or missed grants. Also check for configurations that may make the loading failing, like temporarily disabling NO_AUTO_USER_CREATE and the default authentication plugin (native, not sha...).

I recently ported MariaDB 10.1 grants to MySQL 8 and it required some iterations, such as different names for the unix socket authentication plugin, the default authentication plugin (vs native) and the usage of VIA vs WITH. Mostly due to the default configurations, something you will have to work in anyway for the migration.


How to qualify variable inside trigger body (PL/SQL )? I can explicitly qualify variable in the body of stored procedure or function :

create or replace
PROCEDURE ptest  AS
int_val INT;
BEGIN
  ptest.int_val:=0;
END;
/

How to do the same inside trigger ?

 create table temp1(id int not null);

 create or replace trigger trg_before_insert_temp1 before insert  on temp1 for each row
declare int_val int;
 begin

   trg_before_insert_temp1.int_val := 0; -- PLS-00201, identifier must be declared

 end trg_before_insert_temp1;
 /

Interesting question.  As far as I can tell, this is not possible, though it seems like it should be.  Unless someone else can show how it can be done, a workaround would be to nest the code in a block:

create or replace trigger trg_before_insert_temp1 before insert on temp1 for each row
declare
begin
   <<bob>> declare
      int_val int;
   BEGIN
      bob.int_val := 0;
   END;
end trg_before_insert_temp1;


SQL Server 2012 Powershell script to generate database with schema and data Using Sql Server 2012, could somebody be so kind and show me a simple powershell 3 script that generates my entire database (schema and data)?

I tried this one:

$Filepath='d:\b\t' # local directory to save build-scripts to
$DataSource='HH' # server name and instance
$Database='HMovies'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file
$transfer.Options.ScriptData = $true;
$transfer.Options.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer.ScriptTransfer()
"All done"

But I get an error

Exception calling "ScriptTransfer" with "0" argument(s): "This method does not support scripting data."

If I disable the ScriptData option it works but then I get the schema only of course.
I have googled for hours but I just can't find a simple script (no bells and whistles for stored procedures, etc) that performs this simple task.

My time is running out. Please help.

I wanted to use Powershell to script a small database so that I can track changes in git. I found the same script you did to use as a starting point:
Automated Script Generation with Powershell and SMO

You can find all of the options for the ScriptingOptions class here: ScriptingOptions Class

You're getting the error because of the now deprecated ScriptTransfer method.

Replace this line

$transfer.ScriptTransfer()

with this

$transfer.EnumScriptTransfer()

and your script should run.


Two MySQL requests at the same time - Performance issue I have a MySQL server with many innodb tables. I have a background script that does A LOT a delete/insert with one request : it deletes many millions of rows from table 2, then insert many millions of rows to table 2 using data from table 1 :

INSERT INTO table 2 (date)
SELECT date from table 1 GROUP BY date

(The request is actually more complex but it is to shown what kind of request I am doing).

At the same time, I am going to run a second background script, that does about a million INSERT or UPDATE requests, but separately (I mean, I execute the first update query, then I execute an insert query, etc...) in table 3.

My issue is that when a script is running, it is fast, like let's say it takes 30minutes each, so 1h total. But when the two scripts are running at the same time, it is VERY slow, like it will take 5h, instead of 1h.

So first, I would like to know what can cause this ? Is it because of IO performance ? (like mysql is writing in two different tables so it is slow to switch between the two ?)

And how could I fix this ? If I could say that the big INSERT query is paused while my second background script is running, it would be great, for example... But I can't find a way to do something like this.

I am not an expert at MySQL administration.. If you need more information, please let me know !

Thank you !!

I have already asked my question on Stackoverflow and they only proposed to use an event scheduler.
My issue is that, in production, my first script (the least important/urgent) can take 5h or even more, and the second one would only take 30min and is a little bit urgent. I can't wait for the first script to finish before running the second one.

It looks like 'page swapping' is the problem here.

When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram.

On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be written to the disk, and the second table's info will be loaded to RAM. This is one of the MOST EXPENSIVE operations.

Solutions!! Not straight forward though: You may distribute your DB, or run your scripts sequentially. Hardware upgrade may make the process faster.


How to join to the same table multiple times? I have two tables, "hierarchy_table" and "name_table".

The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id.

|  object_id  |  parent_id_1  |  parent_id_2  |  child_id_1  |  child_id_2  |
-----------------------------------------------------------------------------
|     1234    |      9999     |      9567     |     5555     |     5556     |
-----------------------------------------------------------------------------

Each object id in the hierarchy_table has an entry in the name_table:

|  name_id  |    name    |
--------------------------
|   1234    |   ABCD     |
--------------------------
|   9999    |   ZYXW     |
--------------------------
| ...

How do I join each id in the hierarchy_table to the name_table multiple times so that I can have a result where every name is populated?

Like this:

|   object    |   parent_1    |   parent_2    |   child_1    |   child_2    |
-----------------------------------------------------------------------------
|     ABCD    |      ZYXW     |      BBBB     |     CCCC     |     DDDD     |
-----------------------------------------------------------------------------

Note:  the table names in the example are just for clarity / simplicity, the real names have proper names.

The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins. It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned:

SELECT
    o.name  AS object,
    p1.name AS parent_1,
    p2.name AS parent_2,
    c1.name AS child_1,
    c2.name AS child_2
FROM
    hierarchy_table AS h
  LEFT JOIN name_table AS o   ON h.object_id   = o.name_id
  LEFT JOIN name_table AS p1  ON h.parent_id_1 = p1.name_id
  LEFT JOIN name_table AS p2  ON h.parent_id_2 = p2.name_id
  LEFT JOIN name_table AS c1  ON h.child_id_1  = c1.name_id
  LEFT JOIN name_table AS c2  ON h.child_id_2  = c2.name_id ;


How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow? In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier;

https://aws.amazon.com/ec2/instance-types/t2/

It has: 20 GB storage, 1 GB memory, 1 vCPUs, 6 CPU Credits

So now I have a bunch of metrics I can follow in AWS;

As a developer, I know the basics of using SQL databases, but I know very little as a DBA (Database Administrator) and hosting them, but I want to learn the basics.

So my question is not a AWS specific question (its relevant for Google Cloud Platform and Microsoft Azure as well), but its a more more broad question;

As a DBA, how do you know when its time to upgrade a RDBMS infrastructure?
What key metrics should you follow and why in order to know when should try and upgrade your infrastructure?
What are the basics any new DBA should look at?

Unfortunately this is not a simple matter. There is much black art and experience involved because you are dealing with a few different but strongly coupled matters. First you are trying to decide if your infrastructure needs updating to handle the application1, if the application needs changes to optimise it instead2, some mix of the two3, or if you need to re-architect both in-step4.

1: throwing hardware at the problem
2: better indexing, fixing anti-patterns to improve use of those indexes, and so on
3: when there are optimisation gains to make in the application, but hardware upgrades are still needed to achieve the desired performance
4: switching to a wide scaling model may require changes to both the infrastructure and the code as part of the same grand (re)design

If you have little or no control over the application, you beleive it has been optimised as far as is possible on its own, or you desire to leave it alone as much as possible, then infrastructure changes are all you can work with, in which case there are slightly more hard and fast rules: if you are hitting disk/network a lot for read activity then consider upgrading the amount of RAM, if you are blocking on disk/network IO for write activity then a better performing storage layer may be needed, if your CPU resource is always busy but other resources less so then that is a likely upgrade target though take care to analyse if the load is mostly concurrent or not (if you have small numbers of long-running CPU-busy tasks then faster cores are useful, if you have high concurrency them more cores is probably going to be more helpful though make sure you have enough memory that they won't be starved by IO bottlenecks reading the data they need to process).

Of course even with no control to change the rest of the application you might be able to help it out with index changes.

As a general rule you are not wanting to monitor specific metrics for absolute values, but instead watching key ones for changes over time. These metrics will be a mix of application throughput at the front end (response times generally or for specific actions), throughput server-side (transactions per period, average transaction length, ...) and infrastructure related (CPU utilisation, IO busy/wait times, memory use). Make sure you know your baseline metrics, so you can more immediately see where a bottleneck develops over time due to data growth, over time due to concurrency growth, or suddenly due to a new glut of users or a change in the application.


Oracle Exchange partition from non partitioned table We have table name Summary with 86 GB data. This table do not have partition/sub-partition .We want to move the data from the summary table to a composite partitioned table (range partition on entry_date column and hash partition on users_codes column ) .

  oracle doesn't allow to exchange data with non partitioned table

So we are doing the following approach.kindly let me know if this is correct approach.

Create a temporary table summary_temp with range partition on entry_date column ,then exchange the data from summary table to partitioned table(summary_temp) in part_hist.
Alter the summary_temp table to add sub partition (hash partition on users_codes) column
exchange the partition and sub_partition data to the another empty destination table which is having the same partition and sub partition.

We have done the same operation using the following approach:

Create the destination table with the correct structure nologging
Insert append parallel into it (verifiy direct path load in the explain plan)
Create the indexes nologging
in some case add the FK novalidate
compute the stats
Alter the objects LOGGING
Drop the initial table


What steps are necessary to safely restart a machine hosting Microsoft SQL Server? Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL Server instance?

For example, I recently encountered the recommendation of stopping the SQL service manually.  My understanding is that this is handled by the Windows shutdown process.

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but I'd like to avoid repeating obsolete or superstitious practices.  Are there any recommendations from Microsoft, or widespread industry standards?

This question relates to the short-term procedure of rebooting a machine.  There's another question regarding the long-term procedure of ensuring that a machine is unused, before taking it down permanently.

You don't have to be fancy/worried or scared when you are restarting sql server.

Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.

If you have any DR setup and you dont want to be down, then best is to failover and then restart the passive or secondary node.

Clean Shutdown SQL Server occurs in below scenarios :

Stop sql server using Services console.
Shutting down your server
running SHUTDOWN command in SSMS

In above all situations, sql server cleanly shutsdown all its databases and then terminates the service which involves commiting or rolling back all the transactions, writing all dirty pages to disk and then writing an entry into transaction log.

Improper shutdown of sql server :

shutdown with nowait
pulling power cable from your server (if you have access).
killing sqlserver.exe from task manager
Dirve failure on which sql server binaries, exe, system databases reside or windows system drive failure .. usually C:\ drive.
overheating of the server causing it to shutdown (should rarely happen !!)

SQL Server will always try to do a clean shutdown ...unless you do something improper as stated above.

Some really good reading links on what happens behind the scenes during recovery phase :

Understanding Logging and Recovery in SQL Server
Understanding How Restore and Recovery of Backups Work in SQL Server
Simple overview of the SQL Server Recovery Process


Execute dynamic query inside procedure I'm trying to build a procedure as follows:

CREATE PROCEDURE [test].[ZTEST1]
@Messages NVARCHAR(4000)

AS
SET NOCOUNT ON;

BEGIN
  Declare @SQL nvarchar(1000);
  SET @SQL = 'SELECT parsename(replace(substring(@ConcatenatedErrorMessages,charindex('+quotename(':','''')+',@ConcatenatedErrorMessages) + 1,len(@ConcatenatedErrorMessages)),'+quotename(':','''')+','+quotename('.','''')+'),4)';

EXECUTE SP_EXECUTESQL @SQL;

END
GO

I call the procedure like this:

test.ZTEST2 'test1:test2:test3:test4:test5';

but I get this error:

  Must declare the scalar variable "@Messages".

I'm not sure what the problem is.

You are making this needlessly complicated. You are trying to build a dynamic query where a static one would do just fine. Assuming the logic in the dynamic SQL is correct, the entire procedure could look like this:

CREATE PROCEDURE test.ZTEST1
  @Messages nvarchar(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    PARSENAME(REPLACE(SUBSTRING(@Messages, CHARINDEX(':', @Messages) + 1, LEN(@Messages)), ':', '.'), 4);
  /* or you could split the complex expression into multiple lines,
     for potentially easier understanding of the logic:
    PARSENAME(
      REPLACE(
        SUBSTRING(
          @Messages,
          CHARINDEX(':', @Messages) + 1,
          LEN(@Messages)
        ),
        ':',
        '.'
      ),
      4
    );
  */
END
GO

I have replaced @ConcatenatedErrorMessages with @Messages as that seemed an obvious mistake (and may have been the cause of an error message very similar to the one you have posted).

On a different note, the way you are calling the stored procedure:

test.ZTEST2 'test1:test2:test3:test4:test5';

is acceptable only if that statement is first in the batch. The more universal way of calling stored procedures that would work regardless of where the statement was located would be using the EXEC/EXECUTE keyword:

EXECUTE test.ZTEST2 'test1:test2:test3:test4:test5';

Finally, the name of the procedure you are trying to execute, test.ZTEST2, does not exactly match the name of the one being created above (test.ZTEST1) – so, you need to fix that too.


MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition? I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I understand, most queries on a column used in the partition will be faster because the query will (for most queries) only have to search within the partition applicable to the query. Thus, it would make sense that, in order to maximize efficiency, you should divide a large table into the maximum number of partitions, therefore, making each partition as small as possible. In the case of MySQL, this means 1024 partitions. But is there any performance drawback to having a large number of partitions? Is so, how does one find the optimum number of partitions?

Note: There is a somewhat similar question on stackoverflow already, but only one answer, which (from my perspective) misses the mark. So I will state the question in my own way...hopefully it is more clear

Let's compare them
PARTITION SIZE
If you have the following:

100 million rows in a table
BTREE indexing
Each Page in the BTREE holds 1024 keys

What would the metrics look like?
Since LOG(100000000)/LOG(2) = 26.575424759099, a BTREE index with 1024 keys per page treenode would have a tree height of only 3 (CEILING(LOG(100000000)/LOG(1024))). With only three pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 30 keys.
NUMBER OF PARTITIONS
If you have the following:

100 million rows in a table
BTREE indexing
Each Page in the BTREE holds 1024 keys
You create 1024 parititions

The numbers would be slightly different.
Each partition should have about 97656 rows. What would the metrics become now?
Since LOG(97656)/LOG(2) = 16.575421065795, a BTREE index with 1024 keys per page treenode would have a tree height of only 2 (CEILING(LOG(97656)/LOG(1024))). With only two pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 20 keys.
CONCLUSION
Spreading out the keys just removes one tree level but essentially creates 1024 indexes. The queries won't know the difference. The search time would probably be nominal at best in favor of partitions. However, make sure all the data is active. Otheriwse, you may be hitting just a few partitions, while other partitions with rarely-accessed data just takes up space and are never accessed frequently enough to justify the partitioning. You may have different performance metrics to worry about that are more blatent (such as internal defragmentation in XFS, ext3 vs ext4, etc.) You also need to worry about which storage engine you are using because:

InnoDB indexing would be a little messier in comparison to MyISAM because of having to manage a clustered index
InnoDB does double writing of data in ibdata1 as well as the current log file (ib_logfile0 or ib_logfile1)


How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL I need to retrieve data from several tables. For now I am getting maximum of them from

     SELECT disc.id,
        users.user_login as 'last_active_user',
        disc.discussion_sub,
        list.todo_list_title,
        todo.todo_title,
        count(comments.comment),
        MAX(CAST(comments.comment_posted AS CHAR))
    FROM abc_discussions as disc
    LEFT OUTER JOIN abc_todo_list as list
    ON disc.comment_table_type = 'todo_list' AND disc.comment_table_id = list.id
    LEFT OUTER JOIN abc_todo as todo
    ON disc.comment_table_type = 'todo_item' AND disc.comment_table_id = todo.id
    LEFT OUTER JOIN abc_users as users
    ON users.user_id = 2
    LEFT OUTER JOIN abc_comments as comments
    ON disc.id = comments.discussion_id
    GROUP BY disc.id

where $last_comment_by->user_id = $wpdb->get_row("SELECT user_id FROM abc_comments ORDER BY comment_posted DESC LIMIT 1" )->user_id;

Is there any way to make it a single query?

I tried

SELECT a.id ,
a.discussion_sub,
b.todo_list_title,
c.todo_title,
ANY_VALUE(q.user_login) as 'last_active_user',
count(comment),
MAX(comment_posted)
FROM abc_discussions a
LEFT JOIN abc_todo_list b
ON a.comment_table_type = 'todo_list' AND a.comment_table_id = b.id
LEFT JOIN abc_todo c
ON a.comment_table_type = 'todo_item' AND a.comment_table_id = c.id
LEFT JOIN (
 SELECT x.user_id as user_id, y.discussion_id as discussion_id
    FROM abc_users x
    LEFT OUTER JOIN abc_comments y
    ON x.user_id = y.user_id ) d
 ON d.discussion_id = a.id
LEFT OUTER JOIN abc_users q
ON q.user_id = d.user_id
 LEFT JOIN abc_comments e
ON a.id = e.discussion_id
GROUP BY a.id

But getting wrong result for user_login and comment count .

"last_active_user" sounds like

( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 )

count(comments.comment) sounds like

( SELECT COUNT(*) FROM abc_comments WHERE ... )

That is, get rid of the joins and replace the aggregate values by subqueries as above.

JOIN explodes the number of rows, then

You need a GROUP BY to get it back in check
COUNT counts too big a number
Items (such as user_login) that are not in GROUP BY end up with unpredictable values.
Etc.

(Yes, MAX(comment_posted), is the same as, but simpler than, MAX(CAST(comments.comment_posted AS CHAR)).)


how to update information_schema.tables in mysql i have a table A and i want to update the creation time of this table.
I understand i cant simple update ( using update query) as it is not any table.

Is there any way i can update it.

for example creation time is '2015-07-03 12:03:33' to '2015-06-03 12:03:33'

Information_Schema are views and read-only. Yo can not update meta data about tables, only by modifying actual table with ALTER TABLE. If you do an ALTER TABLE you will have current timestamp in meta data.

  INFORMATION_SCHEMA is a database within each MySQL instance, the place
  that stores information about all the other databases that the MySQL
  server maintains. The INFORMATION_SCHEMA database contains several
  read-only tables. They are actually views, not base tables, so there
  are no files associated with them, and you cannot set triggers on
  them. Also, there is no database directory with that name.

  Although you can select INFORMATION_SCHEMA as the default database
  with a USE statement, you can only read the contents of tables, not
  perform INSERT, UPDATE, or DELETE operations on them.

MySQL 5.7 Reference Manual  /  INFORMATION_SCHEMA Tables

What's the exact reason you need to change meta data?


Not all members in a Windows group have permissions I ran into a bit of an odd situation.  Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database.  One of the members in that group is not getting the permissions that have been assigned to the group.  What's odd to me about this situation is that when I do

exec xp_logininfo 'DMN\Group', 'members'

the user shows up.  However, when I do

exec xp_logininfo 'DMN\user', 'all'

the group doesn't show up as a permission path.  How can this happen?  And, perhaps more importantly, how can I fix it?

Some ideas:

The user has not logged off and back in after being added to the group. The group membership info for a user is stored in that user's login token so it may not show until they reauthenticate to the DC
The user name is different to what you expect. Eg a Woman who got married and the login name changed to match the new surname. I've seen xp_logininfo fail because of this
The user has multiple permission paths and is denied via one of them
What do you mean by "not getting the permissions" is that "no rights to select" or "error when connecting" etc. To test, have the user log in using the ODBC control panel to test basic connectivity before worrying about permission

Edit, more ideas:

The permission path for xp_logininfo only shows how they connected to the SQL Server instance. As per comment, the user in question can connect to the Instance but doesn't have permissions in the database. So, the other members of that group get database permissions from somewhere else.

I wouldn't get hung up on the output of xp_logininfo. I'd check permissions and mapping end to end and not bother with xp_logininfo personally

  If the user is a member of multiple Windows groups of equal privilege level, only the group that was first granted access to SQL Server is reported.


Parameter sniffing work arounds I have used two methods to get around parameter sniffing issues in the past:

1) Use WITH RECOMPILE
 2) Reassign parameter values to local variables and use those instead of the parameters

From what I understand, the end result of both of these is the same -  a new execution plan that is optimized for the current query/params is created and used.

If this is true, are there any differences between these two methods or are they essentially the same?  Is one preferable over the other?

WITH RECOMPILE

This is a bit of a sledgehammer. It recompiles every statement in the module. Using OPTION (RECOMPILE) on the statements that are parameter-sensitive is a more targeted solution.

  Reassign parameter values to local variables and use those instead of the parameters

This has the same effect as OPTIMIZE FOR UNKNOWN i.e. a plan is generated based on average values. The plan does not recompile on each execution.

For more information please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.


How to delete large amount of data in sql server without data loss? I've been dealing with million of data deletion in day to day process.
Basically I have 4 tables.

Table_A
Table_B
Table_C
Table_D

I'm attempting to delete data older than 10 days in all tables.

Possibly I will be deleting around a million in each table.
I have created a stored procedure to do these operations.

The steps I have followed to delete the data is

Step 1: Move the recent days (data which I have to preserve) to a temp table

select * into Table_A_Temp
from Table_A
where <<where clause last 10 days to till date>>

Step 2: Rename main table to old table (table with all days data)

exec sp_rename 'Table_A', 'Table_A_Old'

Step 3: Rename temp table to main table (table with data between last days to till date)

exec sp_rename 'Table_A_temp', 'Table_A'

Step 4: Query the temp table with time frame if any new data is inserted during the copy process

Insert into Table_A
select * from Table_A_old

Step 5: Drop old tables

DROP TABLE Table_A_old

Step 6: Create keys and constraints in main table (means renamed table)

code to create primary keys and constraints

Problem:
If I continuously inserting data into table while the store procedure is running I'm losing data for few seconds. (all 4 tables)

Case 1: While renaming table

when I rename the main to old and temp table to main

I'm getting invalid object error (that table is exist error)

Case 2: Two of my tables have foreign key relation
If I insert data before creating constraints and key I'm getting related errors.

How to handle and delete the data properly without losing data.
Please advice the best practices.

Use batched deletes.

DECLARE @keepgoing bit = 1;

WHILE (@keepgoing = 1)
BEGIN
  DELETE d
  FROM
  (
  SELECT TOP 100 *
  FROM Table_A
  WHERE Created < DATEADD(DAY, -10, GETDATE())
  AND NOT EXISTS (....FK check...)
  ORDER BY Created
) d;

IF (@@ROWCOUNT = 0) SET @keepgoing = 0;
END


Large No of MySQL running threads killing response times Yesterday, our MySQL's response time increased 20 times- select queries which earlier used to take 0.6-0.7 ms took 20ms, and updates even more- 60ms.

The MySQL running threads count which usually stays around 10-15 shot up to 300-400, for a good five hours (before coming down when traffic cooled).

Server version: 5.6.25-log MySQL Community Server (GPL), with 32 core machine, 128GB machine (90 GB buffer pool)

Storage engine is Innodb.

Some output from show engine innodb status captured by ptstalk-

  SEMAPHORES
  ---------- OS WAIT ARRAY INFO: reservation count 159291194 OS WAIT ARRAY INFO: signal count 1074930257 Mutex spin waits 1220535470,
  rounds 2921695607, OS waits 26779994 RW-shared spins 291539762, rounds
  1856674850, OS waits 31197621 RW-excl spins 300274870, rounds
  5700270052, OS waits 95339247 Spin rounds per wait: 2.39 mutex, 6.37
  RW-shared, 18.98 RW-excl
  ------------ TRANSACTIONS
  ------------ Trx id counter 20746118705 Purge done for trx's n:o < 20746118666 undo n:o < 5 state: running but idle History list length
  1154

Also,there are large number of os waits, specifically at

  InnoDB  dict0dict.cc:2606       os_waits=60628 InnoDB
  dict0dict.cc:2606       os_waits=131984 InnoDB  dict0dict.cc:2606
  os_waits=81342 InnoDB  dict0dict.cc:2606       os_waits=230958

Also at:

  InnoDB  trx0rseg.cc:196 os_waits=491 InnoDB  trx0rseg.cc:196
  os_waits=458 InnoDB  trx0rseg.cc:196 os_waits=489 InnoDB
  trx0rseg.cc:196 os_waits=483 InnoDB  trx0rseg.cc:196 os_waits=459

Some other information:
1. Query Cache:

| have_query_cache             | YES                               |
| long_query_time              | 0.500000                          |
| query_alloc_block_size       | 8192                              |
| query_cache_limit            | 10485                             |
| query_cache_min_res_unit     | 4096                              |
| query_cache_size             | 0                                 |
| query_cache_type             | OFF                               |
| query_cache_wlock_invalidate | OFF                               |
| query_prealloc_size          | 8192

Adaptive Hash Index is also ON.

What could the issue be? Please let me know if you need more details.

Vmstats snapshot from that time:

procs   -----------memory----------   ---swap--  -----io----  --system-------cpu-----
 r  b   swpd   free    buff   cache    si   so    bi    bo    in     cs us sy id wa st
17  0 685588 2254064 310852 23913820    0    0    96   404     0      0  3  1 97  0  0
 5  0 685588 2256376 310856 23915576    0    0  6816 14092 53915 142794 18  3 79  0  0
 7  0 685588 2256284 310860 23916752    0    0  6288 13136 51433 143163 15  2 82  0  0
 7  0 685588 2254044 310860 23917792    0    0  7904 18572 52465 142161 16  2 82  0  0
 8  0 685588 2254044 310860 23918800    0    0  6512 18244 51958 146916 15  2 82  0  0
 6  0 685584 2252828 310860 23919844    4    0  5444 16820 51750 147625 16  2 82  0  0
 5  0 685584 2251820 310860 23920884    0    0  6864 14084 53609 146373 15  2 82  0  0
 6  0 685584 2251952 310860 23921996    0    0  6016 13112 52862 147601 15  2 82  0  0
10  0 685584 2250164 310864 23923056    0    0  6096 14468 52406 147109 15  2 82  0  0
 7  0 685584 2248936 310864 23924112    0    0  6576 13664 51638 142051 15  2 83  0  0

Query cache off -- good

Large history list -- Innodb stumbling over itself.

Latency suddenly shot up -- ditto

Thread count suddenly shot up -- ditto.

Possible causes:

A long-running UPDATE or ALTER blocking lots of other threads.  Or even a SELECT that was poorly written.  If the slowlog was on, you may be able to discover what it was.  long_query_time = 0.5 implies that (if the slowlog was turned on) it will have caught the naughty query.  And there would be a lot of normally-fast queries in the log, too.
Too many threads.  What is "too many"?  When this happens.  How to control it?  Decrease the limit on number of connections, either in the clients, or with max_connections.  I know that sounds like a bad remedy, but when MySQL gets into the state you described, it may take a restart to uncork it.  Sharing resources among 300-400 threads means that no one will 'ever' get finished.  How many clients?  Web server(s)?  What is the limit on each?  Example: Apache's MaxClients.
Swapping.  Do you have any monitoring that would say that there was swapping? 90G/128G sounds fine, but perhaps something else was eating into RAM.  Swapping is terrible for MySQL.


How to measure or find cost of creating a query plan? I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN, and OPTION(RECOMPILE). However, I can also dive into the query and try to optimize it.

I'm trying to determine whether I should: given limited time to fix problems I would like to know the cost of not doing it. As I see it, if I just stick with OPTION(RECOMPILE), the net effect is that a query plan is recreated every time the query is run. So, I think I need to know:

How to find out what the cost of creating a query plan is?

To answer my own question, I've Googled (e.g. with this query), and I've gone through the documentation of columns for the  dm_exec_query_stats DMV. I've also inspected the output window in SSMS for "Actual Query Plan" to find this info. Finally, I've searched DBA.SE. None of those led to an answer.

Can anyone tell me? Is it possible ot find or measure time needed for plan creation?

How to find out what the cost of creating a query plan is?

You can look at the properties of the root node in the query plan, for example:

(screenshot from the free Sentry One Plan Explorer)

This information is also available by querying the plan cache, for example using a query based on the following relationships:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    CompileTime = c.value('(QueryPlan/@CompileTime)[1]', 'int'),
    CompileCPU = c.value('(QueryPlan/@CompileCPU)[1]', 'int'),
    CompileMemory = c.value('(QueryPlan/@CompileMemory)[1]', 'int'),
    ST.[text],
    QP.query_plan
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
CROSS APPLY QP.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS N(c);

For a full treatment of the options you have for handling these sorts of queries, see Erland Sommarskog's recently updated article.


How to increase the value of a column in a table by a constant by consulting another table? I have prepared the database of the college given as

create table depts(
deptcode char(3) primary key,
deptname char(70) not null);

create table students(
rollno number(2) primary key,
name char(50),
bdate date check(bdate < TO_DATE('2004-01-01','YYYY-MM-DD')),
deptcode char(3) references depts(deptcode)
on delete cascade,
hostel number check(hostel<20),
parent_inc number(8,1));

create table faculty(
fac_code char(2) primary key,
fac_name char(50) not null,
fac_dept char(3) references depts(deptcode)
on delete cascade);

create table crs_offrd(
crs_code char(5) primary key,
crs_name char(35) not null,
crs_credits number(2,1),
crs_fac_cd char(2) references faculty(fac_code)
on delete cascade);

create table crs_regd(
crs_rollno number(2) references students(rollno) on delete cascade,
crs_cd char(5) references crs_offrd(crs_code)
on delete cascade,
marks number(5,2),
primary key(crs_rollno,crs_cd));

Now I want to give Grace Marks 5 in subject ‘DBMS’to the students who have scored less than 50 in that subject.

Accordingly I wrote the query as:

update r
set r.marks=r.marks+5
from crs_regd r
inner join crs_offrd o
on r.crs_cd=o.crs_code
where o.crs_name='DBMS' and r.marks<50;

but it shows error as SQL command not properly ended in line 3. I am using ORACLE SQL. What is the problem in this query?

The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work:

update crs_regd r
set marks = marks + 5
where marks < 50
  and exists
      ( select 1
        from crs_offrd o
        where r. crs_cd = o. crs_code
          and o. crs_name = 'DBMS'
      ) ;


delete rows in 3 tables with on delete cascade For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:

delete from t1 where id1=$id1;

I want to delete all rows of the t3 which contain the fk id1 and all rows of the t2 which contain the fk id2.
Is it possible in one query ?
Edit: here are my tables:

create T1 (
id1 ...
...
CONSTRAINT pk_id1 PRIMARY KEY (id1) );

create T2 (
id2 ...
 ...
CONSTRAINT pk_id2 PRIMARY KEY (id2) );

create T3 (
id1 ...,
id2 ...,
CONSTRAINT pk_T3 PRIMARY KEY (id1, id2),
CONSTRAINT fk_T3_1 FOREIGN KEY (id2)
REFERENCES T2 (id2) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_T3_2 FOREIGN KEY (id1)
REFERENCES T1(id1) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE )

It seems you want these two statements inside a transaction:

DELETE FROM t2
WHERE EXISTS
      ( SELECT 1
        FROM t3
        WHERE t3.id2 = t2.id2
          AND t3.id1 = $id1
      ) ;

DELETE FROM t1
WHERE t1.id1 = $id1 ;

The cascading delete effects will take care of the rest. Not sure though what locking or isolation level will be best for this operation.

Note that if you specify $id1 = 1, the above will delete all rows from t3 that have id1 = 1 and some rows that do not. If for example, you have 3 rows in t3 with (1,5), (1,6), (1,7), the operation will delete them and also delete all rows that have id2=5 or id2=6 or id2=7. So rows with (4,5), (8,5), (28,5), (2,6), (6,6), (40,7) will also be deleted due to the cascading effects, when the three rows of t2 (with id2 5,6,7 are deleted).


Temporary replication of data between continental datacenters I need a way to temporarily replicate data between two data centers on two continents for a small number of large tables in Oracle. The data in the tables is around 30Gb but the transfer rate is only around 5Gb/hour.

I guess that the data does not change greatly on a day to day basis.

What options do we have for replicating the data that are simple and cost effective for this short term requirement?

Clarifications

Temporarily i.e. two months
A subset of the database, 2 or three large tables
One way, until the database is migrated to the new datacenter

Of free solutions, I can only think of rolling your own with a trigger over a database link, or setting up streams.

I'd set up streams.


Replicating to Older Versions of MS SQL Server According to an old MSDN article (from the SQL Server 2008 R2 era), Microsoft SQL Server transactional replication subscribers need to be within two versions of the publisher. Does this hold true for more recent versions (i.e. 2012 & 2014)?

I've been unable to find current recent documentation on this.

Yes, this still holds true.  It is covered in Upgrade Replicated Databases in the SQL Server 2014 documentation.


SAP Sybase IQ 16: Missing DATEROUND() I'm just wondering what might can be wrong

I was trying to round a date by its quarter. Beside some hacks I found the function DATEROUND, which I was thinking of based of documentation is able to do so. So I've build up a little test:

SELECT DATEROUND(qq, now());

which failed with

  Feature 'dateround' not implemented.

Is this a bug inside documentation or did I just miss to enable something? Or is there maybe another easy solution without a case construct for getting this function?

Use the T-SQL compatible DATEPART construct to obtain the quarter, and construct the date from that.

For instance using T-SQL on SQL Server:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) +
    STUFF(
        '00'
        , 3 - (LEN(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3))
        , LEN(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3)
        , 1+(DATEPART(QUARTER, GETDATE()) * 3) - 3
        ) +
    '01');

Returns 2015-01-01 00:00:00.000, the start of the current quarter of 2015.

According to the documentation for Sybase IQ 15; you'd need to modify the above to something like (I can't check the syntax since I don't have Sybase IQ on this machine):

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) +
    STUFF(
        '00'
        , 3 - (LENGTH(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3))
        , LENGTH(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3)
        , 1+(DATEPART(QUARTER, GETDATE()) * 3) - 3
        ) +
    '01');

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/X315942.htm for details.


Difference between Flashback Query and Serializable Transaction Mode? Is there any conceptual difference between a SERIALIZABLE Transaction and a Flashback Query?

They are completely unrelated concepts.

Serializable is a type of isolation level (read here for more info)
Flashback Query is an Oracle technology for getting querying data as it was at a certain point in time - for example, if certain conditions are met you can use Flashback Query to get those million rows back after committing that transaction that accidentally deleted them.


Is the following database design good in the following table for the situation explained below Someone has created this table with the following information:

There are more entries in the database table which I have omitted for brevity purpose.

My ultimate goal is to display the following in the User Interface:

As you can see, the DESC column has same value twice for Capital,Summary etc, same value 4 times for First Reports. I am planning to generate a JSON response using the webservice which would look like the following:

{
    "webservice_status": {
        "status": "SUCCESS",
        "message": ""
    },
    "dataInfo": [{
            "desc": "Capital,Summary etc",
            "datadesc": "Counts of capital and summary ",
            "dataid": "1"

        },
        {
            "desc": "Capital,Summary etc",
            "datadesc": "Testing of capital and summary ",
            "dataid": "2"

        },
        {
            "desc": "First Reports",
            "datadesc": "Completed Successfully ",
            "dataid": "3"
        },
        {
            "desc": "First Reports",
            "datadesc": " Still Pending ",
            "dataid": "4"
        },
        {
            "desc": "First Reports",
            "datadesc": "Failed twice ",
            "dataid": "5"
        },
        {
            "desc": "First Reports",
            "datadesc": "Re attempting ... ",
            "dataid": "5"
        }
    ]
}

I am wondering if I should ask the person who created this to change the database design? As far as I know, having duplicate values in the same column is a violation of some normal form?

Also, while programming, I am not sure how would I know that I need to stop at DATAID=2 to know that my first heading will be Capital,Summary etc and then again, I am not sure how would I know to stop at 6 to know that my second heading is First Reports.

I am wondering if I should ask the person who created this to change
  the database design? As far as I know, having duplicate values in the
  same column is a violation of some normal form?

If the table is on the "many" side of a relationship, it can be the case that duplicates are stored in a "foreign key" column (eg see the DEPARTMENT_ID column of Oracle's sample HR.EMPLOYEES table below).  Thus, without knowing all the other details of the design - and taking them into account -, it may not be advantageous to ask for any changes to be made.

-- using https://livesql.oracle.com ...
select employee_id, last_name, department_id from hr.employees ;

EMPLOYEE_ID  LAST_NAME     DEPARTMENT_ID
100          King          90
101          Kochhar       90
102          De Haan       90
103          Hunold        60
104          Ernst         60
105          Austin        60
106          Pataballa     60
107          Lorentz       60
108          Greenberg    100
109          Faviet       100
110          Chen         100
...

  Also, while programming, I am not sure how would I know that I need to
  stop at DATAID=2 to know that my first heading will be Capital,Summary
  etc and then again, I am not sure how would I know to stop at 6 to
  know that my second heading is First Reports.

In your test table, the headings are already mapped to DATADESC.  The only item changed in the example below is: the column called DESC is now: DESC_ (as the word DESC is a reserved word - this, by the way, is something that probably should be changed: the name of column DESC).

-- table and data
create table T ( desc_ , datadesc, dataid )
as
select 'Capital,Summary etc'
, 'Counts of capital and summary', 1 from dual union
select 'Capital,Summary etc'
, 'Testing of capital and summary ', 2 from dual union
select 'First Reports'
, 'Completed Successfully', 3 from dual union
select 'First Reports'
, 'Still Pending', 4 from dual union
select 'First Reports'
, 'Failed twice ', 5 from dual union
select 'First Reports'
, 'Re attempting ...', 6 from dual ;

-- data
select * from T ;

DESC_                   DATADESC                        DATAID
Capital,Summary etc     Counts of capital and summary   1
Capital,Summary etc     Testing of capital and summary  2
First Reports           Completed Successfully          3
First Reports           Failed twice                    5
First Reports           Re attempting ...               6
First Reports           Still Pending                   4

You can get this resultset in JSON format, too.  Eg if you are using Oracle version 18.0 (eg @ https://livesql.oracle.com):

Query

select
  json_object(
    'desc_' is DESC_
  , 'datadesc' is DATADESC
  , 'dataid' is DATAID
  )
from T
order by dataid ;

Result

{
"desc_" : "Capital,Summary etc",
"datadesc" : "Counts of capital and summary",
"dataid" : 1
}
{
"desc_" : "Capital,Summary etc",
"datadesc" : "Testing of capital and summary ",
"dataid" : 2
}
{
"desc_" : "First Reports",
"datadesc" : "Completed Successfully",
"dataid" : 3
}
{
"desc_" : "First Reports",
"datadesc" : "Still Pending",
"dataid" : 4
}
{
"desc_" : "First Reports",
"datadesc" : "Failed twice ",
"dataid" : 5
}
{
"desc_" : "First Reports",
"datadesc" : "Re attempting ...",
"dataid" : 6
}

Another example - using sqlcl and Oracle version 12c release 12.1.0.2.0:

set sqlformat json

select * from T order by dataid ;

-- line breaks added for clarity
{"results":[{"columns":[{"name":"DESC_","type":"NUMBER"},{"name":"DATADESC","type":"NUMBER"},{"name":"DATAID","type":"NUMBER"}],"items":

[
 {"desc_":"Capital,Summary etc","datadesc":"Counts of capital and summary","dataid":1}
,{"desc_":"Capital,Summary etc","datadesc":"Testing of capital and summary ","dataid":2}
,{"desc_":"First Reports","datadesc":"Completed Successfully","dataid":3}
,{"desc_":"First Reports","datadesc":"Still Pending","dataid":4}
,{"desc_":"First Reports","datadesc":"Failed twice ","dataid":5}
,{"desc_":"First Reports","datadesc":"Re attempting ...","dataid":6}
]}]}
-- 6 rows selected.


SQL Trigger to Insert a row in Table B is the record is inserted in Table A I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for single record scenario but failing when I insert multiple records at a time. Here is the trigger code. Please gjuide me to fix this issue.
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update
AS

DECLARE @SiteId varchar (10),
       @SiteName varchar(60),
       @Description VARCHAR(200),
       @DeleteIndicator bit,
       @ExternalKey VARCHAR(400);

SELECT @SiteId = ins.SITEID FROM INSERTED ins;
SELECT @SiteName = ins.SITENAME FROM INSERTED ins;
SELECT @ExternalKey = ins.MV2ExternalKey FROM INSERTED ins;
;
set @DeleteIndicator = 0;

MERGE Site AS [target]
USING (SELECT [i].MV2ExternalKey FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET
         Name = @SiteID,
         Description = @SiteName,
         DeleteIndicator = @DeleteIndicator

WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator])
VALUES (@SiteId,
    @SiteName,
    @ExternalKey,
    @DeleteIndicator );

Just use your "source"  as supplier for it.
Because you are using variables , which holds only one value , this is way is working fine with one statement.
MERGE and trigger statements can work with multiple rows at once, not just one , so just use the special INSERTED table inside the MERGE statement; just replace the your MERGE statement with next MERGE statement, inside your trigger code
Here are some samples : example1 ,
example2
MERGE Site AS [target]
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName,@DeleteIndicator as DeleteIndicator FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET
         Name = source.SITEID,
         Description = source.SITENAME,
         DeleteIndicator = source.DeleteIndicator

WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator])
VALUES( source.siteID,source.siteName,source.MV2ExternalKey,source.DeleteIndicator)

so , the trigger code will become:
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update
AS

MERGE Site AS [target]
    USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source
    ON ([target].Name = [source].MV2ExternalKey)
    WHEN MATCHED THEN
    UPDATE  SET
             Name = source.SITEID,
             Description = source.SITENAME,
             DeleteIndicator = source.DeleteIndicator

    WHEN NOT MATCHED THEN
    INSERT ([Name],[Description],[ExternalKey],[DeleteIndicator])
    VALUES( source.siteID, source.siteName, source.MV2ExternalKey, source.DeleteIndicator);

As @Charlieface mentioned in comment section, in case of an update with the same values, some people, will not perform update.
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source

will become
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]
EXCEPT
SELECT d.MV2ExternalKey,d.SiteID,d.siteName, 0 as DeleteIndicator FROM DELETED AS d) AS source

ps: there are some bugs on merge statement, so be careful
link1, link2


SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry? In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4.

I tried adjusting my SQL code in the OLE DB Source to CAST to decimal(14,4) to see if SSIS would pick that up in Suggest Types, but it likes single-byte signed integer.  I don't see any option to set default column DataTypes.  Is there another way - either by adjusting my SQL code, or within SSIS options - to get all of my columns to decimal, scale 4?

If your goal is simply to create a Flat File have you considered using BCP.EXE to Bulk Copy your data out. Bulk Import and Export of Data (SQL Server) gives a link to the bcp utility.

You do need to give directions on how to interpret the data.  Here at Create a Format File (SQL Server) you can examine the two Format File type used to define the data format.  (Naturally.)

The format file that I use is a Non-XML Format File.  This is just a text file and is fairly easy to type and make changes within.  For me, this is a quick way to export a flat file. (Of course, if you read further down the page you will see an XML Format File, which might be more your style.)

It is also possible to use BCP to copy data back into a SQL Server and it is pretty fast.  (However BULK INSERT tends to be a bit faster for importing data.)

So BCP is fast, fairly easy, and comes in two flavors.


Altering the existing partition which is partitioned by date range in SQL Server 2012 I have a huge partition table with almost 1 billion records which is partitioned by date range i.e. from 2015-01-01 to 2017-12-01 with 1-36 partitions. There is a requirement of adding data to this table prior 2015 i.e. adding data of the year 2014. So i tried doing the below

ALTER PARTITION FUNCTION partition_function_name()
{
    SPLIT RANGE ( boundary_value ) };

But this can be done for adding the partitions after 2017 and I want to add it before 2015.
Is there a way I can add the partitions prior 2015 efficiently without moving the existing data as the data is very huge.

Yes, but I wouldn't recommend it. Splitting partitions with lots of data takes an unpredictable amount of time and typically eats a ton of transaction log. I'd insert the data into a new table with the correct partitioning. It's more work, but you are in control of the move. Best practice (hindsight's 20/20) is to always have an empty left and right partition for these types of scenarios.

Best Practices

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

https://msdn.microsoft.com/en-us/library/ms186307.aspx


Unexpected Transaction Log Flush events and LOG_BACKUP errors I recently had an unexpected Transaction Log event which I need assistance unpacking some of the event details. I have the what, but not the why...

The scenario is: Unattended application writing to Always On AG backed database, flushing about 55MB of Transaction logs every few minutes.

The database Transaction log size is 8GB, but <100MB is used. Log backups are every hour (as this is DEV) and this has been sufficient (up to now).

Within a 15 minute window, almost 20GB (unexpectedly) is flushed to the log. The drive the log is on fills up and "Full due to LOG_BACKUP errors" start spewing into the log.

The next scheduled log backup occurs at top of the hour, creates 3 GB compressed backup. The LOG_BACKUP errors stop, but transaction log used space remains at 20GB.

30 minutes later, Transaction log in use drops suddenly from 20GB to 1.5GB. No backup occurring during that time.

The application logs so far haven't been a huge amount of help in determining where those transactions have come from and why they flushed all at once to the log.

AlwaysOn Log and Redo queues also stayed at 0 during this event.

If the backup wasn't enough to free any space and all those transactions were still open and only rolled back 30 minutes (assuming they were as there was no log backup), why didn't the LOG_BACKUP errors continue after the backup?

Did you have a look at log_reuse_wait_desc in sys.databases?

We had a similar issue and I have asked a similar question.

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA


Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible? If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it.

Can I then turn around and back up that database from the 2017 instance and use that to overwrite the original version on the 2016 instance?

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref

Also, regarding detatching and reattaching per the docs:

  After being attached to SQL Server 2017, the database is available
  immediately and is automatically upgraded. This prevents the database
  from being used with an older version of the Database Engine

Also, not sure you care about the compatibility level, but FYSA:

  If the compatibility level of a user database is 100 or higher before
  upgrade, it remains the same after upgrade. If the compatibility level
  is 90 before upgrade in the upgraded database, the compatibility level
  is set to 100, which is the lowest supported compatibility level in
  SQL Server 2017.

Additionally, I believe you question is about a user database, but if it isn't:

  Backups of master, model and msdb that were created by using an
  earlier version of SQL Server cannot be restored by SQL Server 2017


Help filtering out records in MYSQL I hope that someone can help me.

I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care.

Each Koala has their own name with a fate that has happened with it.

I need to extract the koalaName for the Koala that has been Relocated, Rescued, or In Care but eliminate the koalaName if it has been eventually Found Dead.

In the case of the output above Alec is eliminated in the results as he has eventually Died, Euthanased or Found Dead. In the case of Aleta and Alex I just need to get their name once.

This is the query I've been trying to work with.

SELECT `id`, `koalaName`, `fate`
FROM main  A
    WHERE EXISTS
(
    SELECT `id`, `koalaName`, `fate`
    FROM    main  B
    WHERE   A.koalaName = B.koalaName
    AND     B.`fate` REGEXP 'Released|Relocated|Care'
)
AND NOT EXISTS
(
    SELECT `id`, `koalaName`, `fate`
    FROM    main  B
    WHERE   A.koalaName = B.koalaName
    AND     B.`fate` REGEXP 'Died|Euth|Dead'
)

I just don't seem to be able to get the result to output the koalaName as a single value instead of returning multiple values

e.g. Alex should only appear once.

Many thanks in advance, I think its simple but I just don't seem to be able to do it.

SELECT koalaName
FROM main
GROUP BY koalaName
HAVING SUM( fate IN ('Released', 'Relocated', 'Care') ) > 0
   AND SUM( fate = 'Found Dead') = 0


Getting a scan though I expect a seek I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure:

CREATE PROCEDURE dbo.something
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS

    SELECT [IdNumber], [Code], [Status], [Sex],
           [FirstName], [LastName], [Profession],
           [BirthDate], [HireDate], [ActiveDirectoryUser]
    FROM Employee
    WHERE (@Status IS NULL OR [Status] = @Status)
    AND
    (
      @IsUserGotAnActiveDirectoryUser IS NULL
      OR
      (
        @IsUserGotAnActiveDirectoryUser IS NOT NULL AND
        (
          @IsUserGotAnActiveDirectoryUser = 1 AND ActiveDirectoryUser <> ''
        )
        OR
        (
          @IsUserGotAnActiveDirectoryUser = 0 AND ActiveDirectoryUser = ''
        )
      )
    )

And this is the index:

CREATE INDEX not_relevent ON dbo.Employee
(
    [Status] DESC,
    [ActiveDirectoryUser] ASC
)
INCLUDE (...all the other columns in the table...);

The plan:

Why did SQL Server choose a scan? How can I fix it?

Column definitions:

[Status] int NOT NULL
[ActiveDirectoryUser] VARCHAR(50) NOT NULL

Status parameters can be:

NULL: all status,
1: Status= 1 (Active employees)
2: Status = 2 (Inactive employees)

IsUserGotAnActiveDirectoryUser can be:

NULL: All employees
0: ActiveDirectoryUser is empty for that employee
1: ActiveDirectoryUser  got a valid value (not null and not empty)

I don't think the scan is caused by a search for an empty string (and while you could add a filtered index for that case, it will only help very specific variations of the query). You are more likely a victim of parameter sniffing and a single plan not optimized for all of the various combinations of parameters (and parameter values) that you will be providing to this query.

I call this the "kitchen sink" procedure, because you are expecting one query to provide all the things, including the kitchen sink.

I have videos about my solution to this here and here as well as a blog post about it, but essentially, the best experience I have for such queries is to:

Build the statement dynamically - this will allow you to leave out clauses mentioning columns for which no parameters were supplied, and ensures that you will have a plan that is optimized precisely for the actual parameters that were passed with values.
Use OPTION (RECOMPILE) - this prevents specific parameter values from forcing the wrong type of plan, especially helpful when you have data skew, bad statistics, or when the first execution of a statement uses an atypical value that will lead to a different plan than later and more frequent executions.
Use the server option optimize for ad hoc workloads - this prevents query variations that are only used once from polluting your plan cache.

Enable optimize for ad hoc workloads:

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Change your procedure:

ALTER PROCEDURE dbo.Whatever
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'SELECT [IdNumber], [Code], [Status],
     [Sex], [FirstName], [LastName], [Profession],
     [BirthDate], [HireDate], [ActiveDirectoryUser]
   FROM dbo.Employee -- please, ALWAYS schema prefix
   WHERE 1 = 1';

   IF @Status IS NOT NULL
     SET @sql += N' AND ([Status]=@Status)'

   IF @IsUserGotAnActiveDirectoryUser = 1
     SET @sql += N' AND ActiveDirectoryUser <> ''''';
   IF @IsUserGotAnActiveDirectoryUser = 0
     SET @sql += N' AND ActiveDirectoryUser = ''''';

   SET @sql += N' OPTION (RECOMPILE);';

   EXEC sys.sp_executesql @sql, N'@Status INT, @Status;
END
GO

Once you have a workload based on that set of queries that you can monitor, you can analyze the executions and see which ones would most benefit from additional or different indexes - you can do this from a variety of angles, from simple "which combination of parameters are provided most often?" to "which individual queries have the longest runtimes?" We can't answer those questions based just on your code, we can only suggest that any index will only be helpful for a subset of all of the possible parameter combinations you're attempting to support. For example, if @Status is NULL, then no seek against that non-clustered index is possible. So for those cases where users don't care about status, you're going to get a scan, unless you have an index that caters to the other clauses (but such an index won't be useful either, given your current query logic - either empty string or not empty string is not exactly selective).

In this case, depending on the set of possible Status values and how distributed those values are, the OPTION (RECOMPILE) might not be necessary. But if you do have some values that will yield 100 rows and some values that will yield hundreds of thousands, you might want it there (even at the CPU cost, which should be marginal given the complexity of this query), so that you can get seeks in as many cases as possible. If the range of values is finite enough, you could even do something tricky with the dynamic SQL, where you say "I have this very selective value for @Status, so when that specific value is passed, make this slight alteration to the query text so that this is considered a different query and optimized for that param value."


slow wal replay in backup postgres12 server We have the following PG12 setup with Master, replica and readonly replica with archive.

master and replica both has 2cpu, 4gb ram
readonly replica with archive has 2 cpu, 2gb ram

below you can see that the db-app-2 and db-app-backup are both writing and flushing the WAL at the same time. But when it comes to WAL replay db-app-backup is far behind and the replay lag increases as the time goes.

CPU 69.2% RAM 34.0% HDD 61%

 application_name | client_addr  |   state   |   sent_lsn   |  write_lsn   |    write_lag    |  flush_lsn   |    flush_lag    |  replay_lsn  |   replay_lag    | sent_lag | write_lag | flush_lag | replay_lag
------------------+--------------+-----------+--------------+--------------+-----------------+--------------+-----------------+--------------+-----------------+----------+-----------+-----------+------------
 db-app-2         |   x.y.z.a    | streaming | 277/B8400000 | 277/B8400000 | 00:00:00.011132 | 277/B8400000 | 00:00:00.013937 | 277/B82EB168 | 00:00:00.213115 | 0 bytes  | 0 bytes   | 0 bytes   | 1108 kB
 db-app-backup    |   x.y.z.b    | streaming | 277/B8400000 | 277/B8400000 | 00:00:00.01765  | 277/B8400000 | 00:00:00.025976 | 274/C8DBC6E0 | 00:15:42.462623 | 0 bytes  | 0 bytes   | 0 bytes   | 12 GB

Any help in identifying the bottleneck for wal replay is appreciated.

Note:
The below query is used in master to fetch the data above

SELECT application_name, client_addr, state, sent_lsn, write_lsn, write_lag, flush_lsn, flush_lag, replay_lsn, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           sent_lsn
       )) AS sent_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           write_lsn
       )) AS write_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           flush_lsn
       )) AS flush_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           replay_lsn
       )) AS replay_lag
  FROM pg_stat_replication;

There must be a replication conflict with a query that prevents applying the changes to the data files.

Look into pg_stat_database_conflicts on the standby for details.

You probably have max_standby_streaming_delay set to a value greater than 0 and long running queries on the standby. Now if some action on the primary server causes WAL entries whose application would conflict with the running query, replay of these data modifications gets delayed.

You can try to minimize conflicts by setting hot_standby_feedback to on (at the risk of bloating your primary server's tables), but you'll never totally get rid of them.

It is difficult to have a standby that is both never delayed and supports long running queries at the same time. Better use two standby servers with different max_standby_streaming_delay if you need both. Of course, if you want the standby for scaling reads, you want both at the same time, and you are in a difficult position. Streaming replication is not very good for that.

You could also experiment with setting vacuum_defer_cleanup_age to a value greater than 0 to reduce the number of replication conflicts caused by VACUUM.


Trying to copy a schema using impdp I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool.  I've setup a database link back to itself and the link works fine.  Then I'm running this:

impdp username/password@instancename schemas=TARGET_SCHEMA
      network_link=LINK_BACK_TO_TARGET
      directory=HOME_DIR logfile=IMPDP_COPY_BACKUP.log
      remap_schema=TARGET_SCHEMA:SOURCE_SCHEMA
      TRANSFORM=STORAGE:n:table;

Everything works fine in my two tests systems, but in Oracle 11gR2 production, it gets to the moving of tables and produces this:

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in
    KUPW$WORKER.UPDATE_TD_BASE_PO_INFO [UPDATE
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mtu SET
    mtu.base_process_order = NVL((SELECT mts1.process_order
    FROM "ADMIN"."SYS_IMPORT_SCHEMA_04" mts1 WHERE
    mts1.process_order > 0 AND mts1.duplicate = 0
    AND mts1.object_schema = mtu.base_object_schema
    AND mts1.object_name = mtu.base_object_name AND
    mts1.object_type = mtu.base_object_type AND
    mts1.processing_state != :1 ),
    (-1000 - (SELECT MIN(mts2.process_order) FROM
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mts2 WHERE
    mts2.process_order > 0 AND mts2.duplicate = 0
    AND mts2.object_schema = mtu.base_object_schema
    AND mts2.object_name = mtu.base_object_name
    AND mts2.object_type = mtu.object_type)))
    WHERE mtu.process_order > 0 AND mtu.duplicate = 0
    AND mtu.object_type = 'TABLE_DATA'
    AND mtu.processing_status = :2]
ORA-01427: single-row subquery returns more than one row

I've tried it a few times, but I'm clueless whats going on.

I'm also open to other ways to clone a schema.

If you just want to get the job done and not worry about why it works on test but not on production then the old export/import methods will work. You don't mention an operating system so I assume Windows because that's what I work on.  Something like:

REM from a DOS .bat script located on the server

SET oracle_sid=YourDatabaseName

REM  export the schema
C:\app\YourOraclePath\bin\exp SchemaOwner/SchemaPass@SchemaName \
   file=(C:\Dump\SchemaName01,C:\Dump\SchemaName02,C:\dump\SchemaName03) \
   filesize=1GB log=C:\dump\Logs\SchemaName.log

REM drop and recreate the new user if they have existing objects
REM otherwise comment out this line

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

imp NewSchemaOwner/NewSchemaOwnerPassword \
  LOG=C:\dump\logs\importNewSchemaOwner.log file=SchemaOwner01.dmp \
  fromuser=SchemaOwner touser=NewSchemaOwner

This does not address your error using the data pump which is likely to be connected to the difference between test and production.  Nor does it address the wisdom of importing data sets into production from production.  If this operation fails what are the consequences?


MYSQL Group Replication I have setup a Master-Master-Slave replication where say A B and C, I want to replicate data from A to B (which is working) and B to C (also working) and from A to C which is not working with two masters since one slave is configured to accept only one master.My query is how to configure slave to accept multiple masters.

I want to achieve the following structure :

Tables in Master 1
+--------------+
| Master1-DB   |
+--------------+
| Table1       |
| Table2       |
| Table5       |
+--------------+

Tables in Master 2 where table 2 is replicated form master 1
+---------------------------+
| Master2-DB                |
+---------------------------+
| Table1 From Master1       |
| Table3                    |
| Table4                    |
+---------------------------+

Tables in master 3 where table 1 is from Master 1 and table 2 is from Master 2 must be replicated.
+--------------------------+
| Slave-DB                 |
+--------------------------+
| Table1 From Master1      |
| Table3 From Master2      |
| Table4 From Master2      |
| Table5 From Master1      |
+--------------------------+

You can't configure a MySQL server to connect to multiple masters as a replication slave.

You can configure Master B with log_slave_updates enabled, Master A with binlog_format = ROW and declare table 2 using Engine=BLACKHOLE on Master B, and the replication events from Master A's binary log will cascade through Master B onto Slave C, which would be connected only to Master B (not directly to A).

Otherwise, your alternative is Multi-Source Replication in MariaDB, which (unlike Oracle MySQL) does allow a single server to connect to multiple upstream masters simultaneously, as long as there are no conflicting combinations of schema+table names being replicated.  This configuration should work whether the master servers are actually running MariaDB or if they're running MySQL.


Turn off telemetry in SQL Server install config file I'm creating a SQL Server 2017 Image Completion config file, and the automatically generated one has a bunch of stuff about "telemetry". I clicked no to the prompts, but it still put several user accounts in the config file, yet no true/false option for the telemetry itself. Here are the user accounts it included in the config file:

  ; TelemetryUserNameConfigDescription

  SQLTELSVCACCT="NT Service\SQLTELEMETRY"

  ; TelemetryStartupConfigDescription

  SQLTELSVCSTARTUPTYPE="Automatic"

  ; ASTelemetryStartupConfigDescription

  ASTELSVCSTARTUPTYPE="Automatic"

  ; ASTelemetryUserNameConfigDescription

  ASTELSVCACCT="NT Service\SSASTELEMETRY"

  ; TelemetryUserNameConfigDescription

  ISTELSVCACCT="NT Service\SSISTELEMETRY140"

  ; TelemetryStartupConfigDescription

  ISTELSVCSTARTUPTYPE="Automatic"

I suppose I could change the startup type to manual, but I would rather just remove all this garbage. Any idea how to turn this off via config file?

as far as i know, the only way to disable telemetry is to change the registry keys later. There is no actual harm to having those accounts, I don't think.


How to add Spatial Reference ID 28992 to MSSQL server? I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

My end goal is to get this dataset into MS SQL Server, with the Geography Markup Language (GML) elements are stored in MS SQL Server's geography data type. After this, I want to make a connection to the MS SQL Server from QGIS, so I can do some nice data analyses.

My idea was to parse the GML elements (that are contained in an XML) using the GEOMFROMGML parser from MS SQL Server. I tried:

SELECT geography::GeomFromGml (
'<Polygon xmlns="http://www.opengis.net/gml">
<exterior>
<LinearRing>
<posList>78988.952 384548.144 78992.462 384549.74 78998.712 384555.12</posList>
</LinearRing>
</exterior>
</Polygon>'
, 28992 )

However, the above code gives the following error:
"The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view."

So, I tried to add the SRID to sys.spatial_reference_systems catalog view, using:

INSERT INTO [DataAnalyse].[sys].[spatial_reference_systems] ([spatial_reference_id]
      ,[authority_name]
      ,[authorized_spatial_reference_id]
      ,[well_known_text]
      ,[unit_of_measure]
      ,[unit_conversion_factor])
VALUES(
    28992,
    'EPSG',
    28992,
    'GEOGCS["Amersfoort", DATUM["Amersfoort", SPHEROID["Bessel 1841", 6377397.155,299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]',
    'metre',
    1
)

This gave another error: "Msg 259, Level 16, State 1, Line 13
Ad hoc updates to system catalogs are not allowed."

So, I looked how I could update a system catalog. It was explained on DBA how to do this. But it was stated there that there is a severe risk of breaking your MS SQL Server installation, and that warranty would cease. So, it seems that updating the system catalog directly is not really an option.

I first asked this question on Microsoft Docs but I was told to ask my question here.

Moreover, people seem to have asked similar questions:

How to add new spatial reference system (on SQLservercentral) asked in 2011, no response.
Insert new spatial reference system SRID into SQL Server (on stackoverflow) asked 2019, no response.

If you have an answer to my question, or have a different idea to get me to my end goal (stated above) please let me know.

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

As you've mentioned, there are ways to modify system catalogs manually but in general this is not supported or recommended. As such, I consider this a non-option.

The documentation specifies:

  SQL Server supports SRIDs based on the EPSG standards. A SQL Server-supported SRID for geography instances must be used when performing calculations or using methods with geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view.

Emphasis mine.

So it seems to be clear that SQL Server does not support this particular SRID (as of SQL Server 2019 - db<>fiddle).

That being said, you should

Create a feedback item for the MSSQL team about adding this functionality.
Look into PostGIS which is widely considered the best option for handling GIS data in a relational database (and free, to boot).


Docker to Docker Oracle 12c Connection I'm trying to run a simple exercise with two docker containers with oracle database 12c (store/oracle/database-enterprise:12.2.0.1). It's pretty straigthforward to run both containers but I haven't been able to connect from one to the other, what am I missing? Both containers are running on the same machine (a MacBook) and both are using the same network, I can reach them with ping

Any help would be very useful

The commands I'm using:

docker container run -d -h localdomain --name odbc -p 1521:1521 -p 5500:5500 -e DB_SID=ORCLCDB -e DB_PDB=ORCLPDB1 -e DB_DOMAIN=localdomain store/oracle/database-enterprise:12.2.0.1-slim

docker container run -d -h localdomain --name odbc2 -p 1522:1521 -p 5501:5500 -e DB_SID=ORCLCDB -e DB_PDB=ORCLPDB1 -e DB_DOMAIN=localdomain store/oracle/database-enterprise:12.2.0.1-slim

Make sure DB is up and running with status healthy

docker container ps

Make sure Oracle instant client is installed on host if trying to connect from host to containers  via sqlplus or any 3rd party gui tools and set up connections in tnsnames.ora on host

3̶. S̶e̶t̶ ̶u̶p̶ ̶c̶o̶n̶n̶e̶c̶t̶i̶o̶n̶s̶ ̶i̶n̶ ̶t̶n̶s̶n̶a̶m̶e̶s̶.̶o̶r̶a̶ ̶o̶n̶ ̶b̶o̶t̶h̶ ̶c̶o̶n̶t̶a̶i̶n̶e̶r̶s̶.̶   (skip this step)

Make sure Oracle listener is up and running and you can find out services names on each containers by running the command

lsnrctl status

You need to find out mapped ports. Execute command

docker port orcl

From my container the output is:

$ docker port orcl
1521/tcp -> 0.0.0.0:32769
5500/tcp -> 0.0.0.0:32768

You can connect directly from sqldeveloper or setup tns entries in tnsnames.ora for sqlplus client.

Here is the screenshot connecting from sqldeveloper

For sqlplus client on host

#tnsname entry for container 1
orclcdb =
(DESCRIPTION =
  # host ipaddress and mapped port for 1521 is 32769
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 32769))
 (CONNECT_DATA =
(SERVER = DEDICATED)
#service name from lsnrctl status command
(SERVICE_NAME = ORCLCDB.localdomain)
)
)

Verify tnsname entries with the command from host:

tnsping  orclcdb

Note

My setup was on Windows 10(host) and docker running on VM. I executed the following command to find the ipaddress of docker VM

Input:

docker-machine ls

Output:

Correction:

I didn't notice parameter -p 1521:1521 for the container start up command. That parameter exposes port 1521 for public. Just use container ipaddress(from docker-machine ls) and same port 1521 for sqlplus client connection from the host.


How to analyze compression options for Azure SQL Database? What is the most efficient way to determine whether row or page or columnstore compression (or none) would be most effective for each index in an Azure SQL Database (standard or premium tier) used for OLTP, where sp_estimate_data_compression_savings is not supported?

I realize that whether doing so is good for performance would depend on the frequency and volume of writes for each index. What other considerations should be taken into account?

(I realize that whether to use columnstore for a partitioned index is a complex issue and I'll consider that out of scope for this request.)

Kalen Delaney has created a version of sp_estimate_data_compression_savings specifically for this use case.

Its available for download from the corresponding blog post here.


Statistics impact on db2 optimizer plan Has the creation of statistics any impact on the estimated costs and the optimizer plan given by db2 optimizer. Or will (in the best case) only the execution time of a query change?

I ran something like this. Is this right/useful?

RUNSTATS ON TABLE DB2INST1.ARTISTS WITH DISTRIBUTION DEFAULT NUM_FREQVALUES 50 NUM_QUANTILES 5 AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE DB2INST1.ARTISTS ON KEY COLUMNS;
RUNSTATS ON TABLE DB2INST1.ARTIST ON COLUMNS((COUNTRY,ARTIST_NAME),(ID,ARTIST_NAME),(CONTACT_MAIL,CONTACT_PHONE),(WEBSITE,TWITTER,SOUNDCLOUD,FACEBOOK));

Two queries I ran on this database were:

SELECT * FROM ARTISTS WHERE WEBSITE IS NOT NULL AND TWITTER IS NOT NULL AND SOUNDCLOUD IS NOT NULL AND FACEBOOK IS NOT NULL
SELECT YEAR, FORMAT, count(*) AS ANZAHL, sum(TRACK_LENGTH) AS GESAMTLAENGE_sec FROM RELEASES JOIN TRACKS ON RELEASES.ID = TRACKS.RELEASE_ID GROUP BY YEAR, FORMAT ORDER BY YEAR DESC

The execution time of both queries was better than before running runstats. But the estimated cost and the optimizer plan given by db2expln were exactly the same.

db2expln command:

db2expln -q [one of the queries above] -t -g -d $dbname

Clearly yes - statistics are very important inputs for the optimizer!
This is what statistics are for.
Besides that configuation, data model (objects) etc. have an effect as well.
YOu can influence the optimization class to determine which optimizer techniques will be considered by the SQL Query Compiler and the DB2 cost based optimizer which is part of that.


Query running slow on script and producing error 500 when run in phpmyadmin I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within fractions of a second. Could a greater mind please tell me where i am going wrong as wherever i have asked there is no error in it.

SELECT ShipAddress1, CustomLabel, Quantity, PhService, UniqueID, FullName, ShipPostCode
FROM internetorders WHERE ShipAddress1 IN (SELECT ShipAddress1 FROM internetorders
WHERE Complete = '0' AND LabelCreated = '0' GROUP BY ShipAddress1 HAVING Count(*) > 1)
AND Complete = '0' AND LabelCreated = '0' ORDER BY ShipAddress1

When running in my script the query returns the result but slowly, when running in phpmyadmin i get error 500.

Also this query is obviously returning all results with a duplicate record in ship address one. How could i have it return all results that are same in ship address 1 and post code only?

Thanks in advance.

You should refactor the query as follows

SELECT
    B.ShipAddress1, B.CustomLabel,
    B.Quantity, B.PhService, B.UniqueID,
    B.FullName, B.ShipPostCode FROM
FROM
(
    SELECT ShipAddress1 FROM internetorders
    WHERE Complete = '0' AND LabelCreated = '0'
    GROUP BY ShipAddress1 HAVING Count(*) > 1
) A LEFT JOIN internetorders B USING (ShipAddress1);

If ShipAddress1 is not indexed, then create an index for it

ALTER TABLE internetorders ADD INDEX (ShipAddress1);


Read XML file in SQL Server 2008 How can I read an XML file and store the data in XML to our table in SQL Server 2008?

This parse node based XML. It's different to read attributes but it isn't as common

I had this lying around as a demo with 3 slightly different XPath queries

DECLARE @foo XML

SELECT @foo = N'
<harrys>
    <harry>
        <fish>0.015000000000</fish>
        <bicycle>2008-10-31T00:00:00+01:00</bicycle>
        <foo>ü</foo>
    </harry>
    <harry>
        <fish>0.025000000000</fish>
        <bicycle>2008-08-31T00:00:00+01:00</bicycle>
        <foo>ä</foo>
    </harry>
</harrys>
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)


SQLCMD -L returns a number of servers that definitely do not have SQL installed I have been running this command in our test domain:

sqlcmd -L

And have found that amongst all the valid results, it also returns several servers that definitely do not have SQL server installed. It is possible they may have had SQL installed on then in the distant past, but definitely not currently.

How is this possible?  I would have thought SQL Browser would have to be installed and running on a server for it to respond to the sqlcmd -L broadcast.

In case it helps, the servers that are false positives are:
-a domain controller
-a server for Microsoft SCCM
-a server for PKI management

Thanks
Mike

The servers might be running SQLServer Express and/or LocalDB

SCCM has a SQL Server backend with a runtime license, Domain Controllers might have a localdb instance and so forth, check in the services app on the servers for those or run the following on the command prompt

tasklist /FI "imagename eq sqlservr.exe"


Trying to install SQL Server 2008 Management Studio Express On a Wndows Server 2008 R2 64 bits, I installed Visual Studio 2010 Express, which installed a SQL Server 2008 Express:

Trying to install a SQL Server Management Studio, first I'm required to install Service Pack 1. But when trying to do so, I got the following error:

  There are validation errors on this page. Click OK to close this dialog box. Review errors at the bottom Setup page, then provide valid parameters or click Help for more information.

As shown in this image:

Then I uninstalled all SQL Server 2008 components from my server and tried to install Full SQL Server from this Microsoft download page.

(when installing, I would select Express Edition)

No luck, when trying to run setup.exe I get the following errors:

  This program has known compatability issues.

Then I click n "Check for solutions online" button, but then I get other errors:

  Your version of SQL Server 2008 isn't compatible with this version of Windows.

You need to install SQL Server 2008 first, then before running or rebooting install SQL Server 2008 Service Pack 1.

You are attempting to install the service pack files first, without an existing base installation of SQL Server 2008.  Since the Service Pack 1 package does not contain a full installation of SQL Server, you must have already installed the non-service-pack version.  The other option would be to create a slip-streamed version of SQL Server 2008 setup by, essentially, combining the Service Pack 1 with the original version on DVD - then installing directly from that.  Check https://duckduckgo.com/?q=%22sql+server%22+slipstream+setup for more info.

Don't click "Check for solutions online", click "Run Program" instead.  After that completes, install Service Pack 1.


NULL out value in table 1, depending on a time-log in table 2 I have two tables in MS SQL Server 2012. The first is a minute by minute production record which looks like (can't guarantee every single minute gets its own record, but never two records for one minute):

[myDateTime] [Produced]
12:01           100
12:02           110
12:03           115
12:04           100

The second table is a journal-style log of whether to consider the [Produced] column from Table 1 or not.

[myStartDateTime] [myEndDateTime]
     12:02            12:03
     12:47            12:58

I want to be able to look through each row of Table 1 and either take the [Produced] column, or take a NULL value, depending on if that minute falls within a record of Table 2 (ie between myStartDateTime and myEndDateTime INCLUSIVE).

In the example above, that would look like:

[myDateTime] [Produced]
12:01           100
12:02           NULL
12:03           NULL
12:04           100

I can't seem to get this to work (in code, or in my head). In my head I think I should join on myStartDateTime and join on myEndDateTime and make a new column called [Include] and set it to 1 or 0 depending on whether I want to include it or not. Each table might have 300 - 400 thousand rows and everything I am trying is slow.

Any help would be appreciated.

This will get you the result you want, but it's a strange requirement.

If you want to make it fast, we'd need a lot more information, like table and index definitions, query plans, etc. "It's slow" isn't particularly helpful.

CREATE TABLE #crap1 (myDateTime TIME, Produced INT)

CREATE TABLE #crap2 (myStartDateTime TIME, myEndDateTime TIME )

INSERT #crap1 ( myDateTime, Produced )
SELECT *
FROM (
        VALUES('12:01', 100),
              ('12:02', 110),
              ('12:03', 115),
              ('12:04', 100)
     ) x (myDateTime, Produced)

INSERT #crap2 ( myStartDateTime, myEndDateTime )
SELECT *
FROM (
        VALUES('12:02', '12:03'),
              ('12:47', '12:58')
     ) x ( myStartDateTime, myEndDateTime )

SELECT c.myDateTime, CASE WHEN c2.myStartDateTime IS NULL THEN c.Produced
                          ELSE NULL
                     END AS Whatever
    FROM #crap1 AS c
    LEFT JOIN #crap2 AS c2
    ON  c.myDateTime >= c2.myStartDateTime
        AND c.myDateTime <= c2.myEndDateTime;


How many cluster must i created on MongoDB Atlas? I want to implement MongoDB transactional. But there is a term that must not single alone server. So, how many clusters must I create to an implement that?

MongoDB Atlas won't let you create a standalone server, so any Atlas cluster should be able to use transactions.


Unable to connect using Azure AD Service Principal on SQL Server I'm having trouble testing a connection to Azure SQL Server using SSMS with an active directory service principal.

I have an AD Admin account created, and have successfully added a colleague's AD user account, whom can connect via SSMS.

The service principal is a Web App / Api service principal with a key. And I am attempting to create a database contained user (understanding this has better future compatibly)

Thinking it could be the syntax for creating the user I have tried many variations, however only this syntax has worked:

CREATE USER [username] FROM EXTERNAL PROVIDER

(other variations say the service principle cannot be found or type is not supported).

I have the database name specified in SSMS.

I've tried "Active Directory - Universal with MFA support" and "Active Directory - Password".

The error I receive is "Could not discover user realm" which suggests it's not aware of the active directory tenant to use, however none of these user login formats seem to work (using the password version, I wouldn't expect them to in universal):

user
domain\user
user@domain.com (FQDN)
user_domain.com#EXT#@domain.onmicrosoft.com
object id
application id

However MSDN documentation suggests AD service principals are supported.

I am using azure active directory service principals for other things fine.

Any ideas?

EDIT

The plot thickens, after reading Connect to Azure SQL Database by Using Azure AD Authentication

I wondered if the service principal needed explicit permissions in AD, however modifying the code slightly so it wasn't doing impersonation, I was able to connect fine using c# (I've added the c# tag for stackexchange syntax highlighting)

var sqlConnectionString = "Data Source=tcp:[servername].database.windows.net,1433;Initial Catalog=[databasename];Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";

string clientId = "[service principal application id]";
string aadTenantId ="[azure active directory id]";

string AadInstance = "https://login.windows.net/{0}";
string ResourceId = "https://database.windows.net/";

var authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, LINQPad.Util.GetPassword("[the password to get]"));

var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

authenticationResult.Dump();

using (var conn = new SqlConnection(sqlConnectionString))
{
    conn.AccessToken = authenticationResult.AccessToken;
    conn.Open();

    using (var cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
    {
        var result = cmd.ExecuteScalar();
        result.Dump();
    }
}

Notably, the SUSER_SNAME() bring back the username in the format clientid@tenantid (both guids). However this format doesn't work in SSMS.

EDIT 2

Interestingly if in the C# code I switch to the username / password in the connection string, I receive the same user realm error.

Service principal is not supported in SSMS as well as in other SQL tools. Only application interface is supported for this type of AAD services.Your C# program works and this is the right way to proceed with testing.