SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users Related Question: SQL Server script to delete accounts no longer in Active Directory

Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this procedure to then drop the logins, search all databases & drop any related users.

Id also like to drop all SQL logins that have not been logged in for over X months - and again, drop any related users across all databases.

Before I go writing all this from scratch - I cant help but feel I'd be re-inventing the wheel - someone must surly have written a script to do this already but i'm not having much luck finding one!

If no one has a script up their sleeve to do all this, scripts to do parts of them would be just as good - I've no problem merging them into a single script.

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db       VARCHAR(70),
                        SID      VARBINARY(85),
                        stat     VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
     'INSERT INTO #all_users
         SELECT ''?'', CONVERT(varbinary(85), sid) ,
          CASE WHEN  r.role_principal_id IS NULL AND p.major_id IS NULL
          THEN ''no_db_permissions''  ELSE ''db_user'' END
         FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p
           ON u.principal_id = p.grantee_principal_id
           AND p.permission_name <> ''CONNECT''
          LEFT JOIN [?].sys.database_role_members r
           ON u.principal_id = r.member_principal_id
          WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''

IF EXISTS
   (SELECT l.name
       FROM   sys.server_principals l
              LEFT JOIN sys.server_permissions p
                   ON  l.principal_id = p.grantee_principal_id
                   AND p.permission_name <> 'CONNECT SQL'
              LEFT JOIN sys.server_role_members r
                   ON  l.principal_id = r.member_principal_id
              LEFT JOIN #all_users u
                   ON  l.sid = u.sid
       WHERE  r.role_principal_id IS NULL
              AND l.type_desc <> 'SERVER_ROLE'
              AND p.major_id IS NULL
   )
BEGIN
    SELECT DISTINCT l.name     LoginName,
           l.type_desc,
           l.is_disabled,
           ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users')
           db_perms,
           CASE
                WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN
                     'no_srv_permissions'
                ELSE 'na'
           END                 srv_perms
    FROM   sys.server_principals l
           LEFT JOIN sys.server_permissions p
                ON  l.principal_id = p.grantee_principal_id
                AND p.permission_name <> 'CONNECT SQL'
           LEFT JOIN sys.server_role_members r
                ON  l.principal_id = r.member_principal_id
           LEFT JOIN #all_users u
                ON  l.sid = u.sid
    WHERE  l.type_desc <> 'SERVER_ROLE'
           AND ((u.db IS NULL
                       AND p.major_id IS NULL
                       AND r.role_principal_id IS NULL
                 )
                   OR (u.stat = 'no_db_permissions'
                          AND p.major_id IS NULL
                          AND r.role_principal_id IS NULL
                      ))
    ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName            type_desc  is_disabled         db_perms                                        srv_perms
app_agentjobreader   SQL_LOGIN      0       no_db_permissions, but is user in mydb DB           no_srv_permissions
app_web_general      SQL_LOGIN      0       no_db_permissions, but is user in mydb_second DB    no_srv_permissions
app_web_maker        SQL_LOGIN      0       no_db_permissions, but is user in mydb_third DB     no_srv_permissions


Amazon EBS Type for MySQL Replication Server I have the following topology

City 1                                                City 2
Master <----> Distribution Master (EC2 Instance) <---> Slave

Master : Write heavy
Distribution Master: Read only
Slave: Read only

There won't be any application in front of Distribution Master.

Distribution Master is slave of Master and Master of SlaveServer
. Master and Slave are in different cities and the replication needs to be Asynchronous.

The EC2 instance just gets the data from Master and replicates to the Slave when a connection is established.

Do you think I need SSD volumes for the Replication Master?
Or will a cold HDD (sc1) will be enough?

With a cold storage solution I can get more space for a much lower price.

Your "distribution master" -- assuming it is running MySQL Server, not a specialized program specifically for directly replicating  binlogs -- cannot "just get data from Master and replicate to the Slave when gets connection" and relay the incoming events to downstream replicas.  It will also be keeping a copy of the entire data set and processing the replication events, keeping its local copy in sync before sending the events downstream.

It will, therefore, need to have as much I/O capacity as needed in order to keep up with the master.

The question of whether sc1 Cold Storage (mechanical) HDD EBS volumes offer sufficient capacity in both write throughput and IOPS is not a question that can be answered in general.

The answer depends entirely on your workload.

There is nothing in sc1 that is intrinsically incompatible with MySQL, but a 500 GiB sc1 volume has a baseline throughput of only 6 MiB/s and a burst throughput of 40 MiB/s.  There is a reason it is marketed as "cold storage," and a reason it is inexpensive.

An st1 Optimized Throughput (also mechanical) HDD volume has > 3x the performance for < 2x the price of sc1 so is a somewhat safer bet.  I have recently begun testing st1 volumes for binary log and relay log storage on some non-critical replicas -- with the datadir still on gp2 SSD volumes -- and have not been seeing any replication lag nor taxing of these volumes according to the CloudWatch metrics nor significant iowait... but binary logs and relay logs are, of course, largely sequential write and read, which puts them within the design parameters of both st1 and sc1.

The bottom line, though, is that your experience will be highly correlated to your workload.

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html

Remember also that if you create a volume of any storage class from an EBS snapshot (such as when you are testing different storage classes -- a snapshot made from one type of volume can be used to create a volume of a different type) that you will see degraded performance until the volume is warmed up.

Assuming the volume is /dev/xvdf, this might look like dd if=/dev/xvdf obs=1M | pv -pterab > /dev/null ... followed by a few hours of waiting.  Don't try to optimize a warmup with fio or a large block sizes on smaller instances -- you'll DoS your own instance if you hit a stretch of already warmed-up blocks and the volume's read throughput spikes significantly above the available network bandwidth on the instance's Ethernet interface.  If this happens, the instance will fail its health checks.


How to Audit Successful Logins for certain logins only? I need to setup auditing (logging) of successful logins on SQL Server (date, time, who), not for all logins on a server, but only  for a small group of logins (developers)

In short, me only interested in logging/auditing developer logins, not application logins

Tried using SQL Server audit feature, using SUCCESSFUL_LOGIN_GROUP, but it does not allow me to select "principal name" and specify logins that are interesting to me

Is there a workaround ? I need only certain logins to be audited, otherwise it will be millions of records in the audit file if I audit every successful login of every application/user

You can do this with audit objects with SQL Server 2012 and later by using a filter to specify the principals you want to audit.  The only downside is that you can't specify a group or a role, you have to include each login.

USE [master]
GO

/****** Object:  Audit [sysadmin_successful_logon] ******/
CREATE SERVER AUDIT [developer_successful_logon]
TO APPLICATION_LOG
WHERE ([server_principal_name]='domain\dev01' OR
    [server_principal_name]='domain\dev02' OR
    [server_principal_name]='domain\dev03')
ALTER SERVER AUDIT [developer_successful_logon] WITH (STATE = ON)
GO

CREATE SERVER AUDIT SPECIFICATION [developer_successful_logon_spec]
FOR SERVER AUDIT [developer_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO


MySQL cluster Replication and Failover via docker-compose I am using docker-compose to create MySQL cluster (NDB). After cluster gets created. This is my cluster configuration:
ndb_mgm> show
Connected to Management Server at: mysql-manager-1:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @172.xx.0.5  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0, *)
id=12   @172.xx.0.8  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @172.xx.0.2  (mysql-5.7.25 ndb-7.6.9)
id=2    @172.xx.0.3  (mysql-5.7.25 ndb-7.6.9)

[mysqld(API)]   2 node(s)
id=21   @172.xx.0.4  (mysql-5.7.25 ndb-7.6.9)
id=22   @172.xx.0.7  (mysql-5.7.25 ndb-7.6.9)

When I bring down one of the SQL nodes, the cluster becomes unreachable.
E.g when node with id=21 is shut down, data should still be accessible from node id=22 because all other data nodes and management nodes are up and running.
Is this behavior supported out of the box or I have to make some configuration change for it?
I have gone through the MySQL documentation.
Am I referring to the correct documentation?
They advise executing the steps on the host machine but in my case each node is deployed as a separate docker container.  How do I achieve this failover in my case?

You may have set up replication, but failover doesn't just magically happen by itself. You need also

Some kind of cluster manager to notice the failure and initiate the failover procedure.
Complete the failover procedure and ensure that the new master is operational. Part of the process is described here.
A floating IP address or some sort of connection proxy to direct incoming connections to the new master.


Advisability of using STATISTICS_NORECOMPUTE I've recently become involved in maintaining a set of databases with some interesting index problems.  One of the ones that aggravates me the most is the differences in the indexes between development, test, model and production machines.  Since the differences make tuning queries rather difficult syncing them up is one of my first projects.

As I've compared the test and model environments I've noticed that most of the indexes in the model environment have STATISTICS_NORECOMPUTE set to ON while those in test do not.  In all of the environments there is a nightly job that updates the statistics of all of the databases.

I've never dealt with STATISTICS_NORECOMPUTE before so here are my questions.  Are there any best practices when dealing with this setting?  If I'm doing statistics updates at the end of the day is it best to turn STATISTICS_NORECOMPUTE in all of the environments on all of the indexes?  Or is there a good reason not to?

EDIT:
I've found one of Kimberly Tripp's blogs on the subject here that seems to suggest that STATISTICS_NORECOMPUTE should be used sparingly at best.  But I'm still concerned about turning it off globally.  Has anyone tried this and what did they experience?

It's really a situational thing that you want to look at per-table or per-index, and you really need to find out what's in production before taking any action. When in doubt, use what's in production in the other environments, too, even if it means using a bunch of crazy settings. You just can't get a good feel for how production will behave if things are different in test or dev.

Anyway, the general recommendation to leave auto-update stats turned on (STATISTICS_NORECOMPUTE = OFF, which is the default) is for safety reasons, because if this is turned off and nothing is manually updating the stats, the result could be really horrendous execution plans that never change after they're first created (and don't get invalidated for other reasons later on).

You said auto-update stats is turned off for most indexes (I think I originally misread that as all, not most). For the indexes with auto-update stats still enabled, does this setting make sense given the activity on those tables? I would expect that these are higher-activity tables. It's possible a lot of work went into figuring that out, and it may be worth keeping (or strongly considering) those settings. At the very least, make a note of which stats these are, because that information could come in handy down the road.

Thinking about it more, I will say that the current strategy does make sense. Is it better than leaving auto-update stats on for everything? It seems someone thought so, to the point that it was worth the ease-of-management tradeoff of having an associated SQL Agent job.

If the idea was to have fresh stats available without blocking queries (like this), you could consider turning auto-update back on for everything, and then also turn on AUTO_UPDATE_STATISTICS_ASYNC as well. Then probably change the job schedule to run once/week instead of daily, as you still do want stats updated WITH FULLSCAN periodically.

I might just leave it, though, as you probably have bigger fish to fry if the indexes themselves are different between environments, and the stats rebuilds aren't too painful. What's there now does make sense; you just need to make things consistent across environments. It's probably marginally better than the simpler settings I suggested, at the expense of more work being done. But find out what's in production, tend towards using that, and move on to more important things; revisit this when you're at the point of needing to more finely tune performance -- the best stats in the world won't save a query that's missing a critical index.


Stored Procedure Execute as Owner Close Database Connections Not Working I created a stored procedure to allow users to Close All Db connections in the QA environment.  I am SA and created the procedure.

When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.

Trying to understand why.

create procedure [dbo].[DatabaseConnectionClose]
    @DatabaseName varchar(255)
with execute as owner
as

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@DatabaseName)

select @kill as CloseConnectionScript

EXEC(@kill);

Using the EXECUTE AS clause of the CREATE {module_type} statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.

To do this properly:

Get rid of EXECUTE AS
Implement this using Module Signing:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)

The only permission you should need to grant to the Certificate-based Login is: ALTER ANY CONNECTION (as per the documentation for KILL)

For more info on module signing, please visit: Module Signing Info

Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).

However, if ALTER DATABASE [{db_name}] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level


How to lock all tables except some specific table? I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some tables like cache or session which needs to be rewritten.

So I am trying to use replicate_do_table and then locking those tables manually using below snippets and let other tables like cache and session rewritable.

In short I want read_only lock for all the tables in a database excluding some specific tables to read-write access.

I found below snippet on mysql website. But how can I select only specific tables without writing all the table names manually inside LOCK TABLES

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

Note: I am using innodb storage engine and mariadb 10.3

Do not use LOCK TABLES with InnoDB.  For this situation consider the following...

Leave read_only = OFF
Provide a readonly login for your application when it goes to the Slave in addition to a readwrite login for when it talks to the Master.

The app user would have grants something like:

GRANT SELECT         ON app_db.*   TO app_user@slave
GRANT ALL PRIVILEGES ON sessions.* TO app_user@slave   -- and cache.*
GRANT ALL PRIVILEGES ON app_db.*   TO app_user@master


Can we export data from PostgreSQL to xlsx file? I want to export the result of a query to xlsx file format.
I know we can export it to csv file, but I want to export it to xlsx file.

Like the following:

copy(select name_related from hr_employee limit 3) to '/tmp/ABC.xlsx'
delimiter ',' xlsx header;

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script.

csv2xlsx:

For this script to work, install Excel::Writer::XLSX and Text::CSV perl modules, either through apt-get/yum, or through sudo perl -MCPAN -e 'install Excel::Writer::XLSX' and sudo perl -MCPAN -e 'install Text::CSV'.

#!/usr/bin/perl -w
use strict;
use warnings;
use Excel::Writer::XLSX;
use Text::CSV;

my ($filename) = @ARGV;
open my $io, "-" or die "$!";

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( $filename ) or die "$!";

# Add a worksheet
my $worksheet = $workbook->add_worksheet();

# Data
my $fdata = $workbook->add_format();
$fdata->set_border(1);
$fdata->set_border_color('gray');
$fdata->set_font( 'Segoe UI' );
$fdata->set_size( '8' );

# Titles
my $ftitle = $workbook->add_format();
$ftitle->copy ($fdata);
$ftitle->set_bg_color( '#B0CBF0' );

# Dates
my $fdate = $workbook->add_format();
$fdate->copy($fdata);
$fdate->set_num_format('dd/mm/yyyy' );
my $fdatetime = $workbook->add_format();
$fdatetime->copy($fdata);
$fdatetime->set_num_format('dd/mm/yyyy hh:mm:ss' );

$worksheet->set_column('A:ZZ', 15);

my $csv = Text::CSV->new({ binary => 1, eol => $/ });

my $line = 0;
while (my $row = $csv->getline($io)) {
  my @fields = @$row;
  my $col = 0;
  if($line == 0) {
    foreach my $fld (@fields) {
      $worksheet->write_string( 0, $col++, $fld, $ftitle );
    }
  } else {
    my $col = 0;
    foreach my $fld (@fields) {
      if ($fld =~ /^[0-9]/) {
        if($fld =~ /^-?(0(\.[0-9]{1,14})?|[1-9][0-9]{0,13}(\.[0-9]{1,14})?)$/) {
          $worksheet->write_number( $line, $col, $fld, $fdata );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/) {
          $worksheet->write_date_time( $line, $col, "${fld}T", $fdate );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}[ T][0-9]{2}:[0-9]{2}:[0-9]{2}(\.[0-9]+)?$/) {
          $fld =~ s/ /T/;
          $worksheet->write_date_time( $line, $col, $fld, $fdatetime );
        } else {
          $worksheet->write_string( $line, $col, $fld, $fdata );
        }
      } else {
        $worksheet->write_string( $line, $col, $fld, $fdata );
      }

      $col++;
    }
  }
  $line++;
}

Usage example:

psql -c "COPY (SELECT 1 as id, 'foo' as bar, now()::date as date) TO STDOUT CSV HEADER" template1 |\
  ./cvs2xlsx /tmp/ABC


sql query - table@string meaning I am trying to understand a query . When I run the below query , no rows are fetched.

select * from Table

But when I run the query , a result is returned having multiple rows.

select * from Table@somestring

I am not sure what @ followed by somestring means and why that is used in PL/SQL.

It's part of a distributed query. The string after the @ is a database link.

You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents depending on the owner and your privileges. It might refer to a TNS alias, in which case you could get further details from the tnsnames.ora file.

When you query table you're (usually) looking at a table in your own schema. When you query table@dblink you're looking at a table in a remote database. They are different objects so you'd expect different results. Your local table is empty, but the remote one has some data.

I say 'usually' because table could be a synonym pointing at another schema, or even to a schema on a remote database itself. And dblink could be pointing back to your own database - it would be unusual but I've seen it done as a way of accessing another schema (not something I'd recommend). Without seeing if there is a synonym or where the dblink is going it's hard to be definitive, but these are less common scenarios. In this case though you're clearly hitting two different tables, probably on different databases. (Well, unless there's a VPD and connecting to the same table as a different user gives a different view... but starting to go down the rabbit hole now...)


A MySQL EXPLAIN number of rows discrepancy MySQL 5.5.49-log

More questions on the query in Why does it use temporary? (MySQL) (the query is the same but the question is different):

I have the following table (filled with many rows):

CREATE TABLE `SectorGraphs2` (
  `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',
  `Criterion` tinyint(3) UNSIGNED NOT NULL,
  `Period` tinyint(3) UNSIGNED NOT NULL,
  `PeriodStart` date NOT NULL,
  `SectorID` int(10) UNSIGNED NOT NULL,
  `Value` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `SectorGraphs2`
  ADD UNIQUE KEY `Producer2` (`Kind`,`Criterion`,`Period`,`PeriodStart`,`SectorID`) USING BTREE,
  ADD KEY `SectorID` (`SectorID`);

then I run:

EXPLAIN
    SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK
    GROUP BY SectorID

and it produces:

+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows | extra                                        |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | SectorGraphs2 | range | Producer2     | Producer2 | 6       | NULL | 1    | Using where; Using temporary; Using filesort |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+

See a nicely formatted explanation here.

My question: Why it is used a temporary table and filesort but it reports only 1 row examined? It seems that because of using a temporary table, it should process more than one row. How can I determine the real number of rows processed? How to solve this discrepancy about number of processed rows?

Note that the task I was assigned to do now is to eliminate heavy (involving too many rows) queries. And now I do not know how to do this.

Try to replace AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK clause with precalculated version:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;
SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart BETWEEN ? AND @PeriodEnd
    GROUP BY SectorID;

Explicitly point the index you want to use:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;
SELECT SectorID, SUM(Value)
    FROM SectorGraphs2 USE INDEX (`Producer2`)
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart BETWEEN ? AND @PeriodEnd
    GROUP BY SectorID;

Reorder fields in the index definition placing more selective fields first. To determine selectivity run the next query:

SELECT COUNT( DISTINCT Criterion )
  FROM SectorGraphs2;

Then replace Criterion with Kind, PeriodStart, SectorID etc.

The higher count means better selectivity and you have to arrange fields in the index from the best selectivity to worst.


Converting old SQL Server outer join syntax I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.

I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.

For example:

select  *
from    A
right outer join
        B
on      A.bid = B.id

...is written in the old style like:

select  *
from    A,
        B
where   A.bid =* B.id

Here is a part of my procedure that uses *=:

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
 FROM  a_stmt,
   s_dataccs,
   a_carr,
   a_icc
 WHERE ( a_stmt.ic_cntct_id *= a_icc.ic_cntct_id ) and  -- HERE
   ( a_icc.usg_ind = 'S' ) and
   ( a_carr.acna = a_stmt.acna ) and
   ( s_dataccs.acna = a_stmt.acna ) and ( s_dataccs.user_id = @user_id ) and
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
   ( a_stmt.amt_du_ic < 0 )

I have converted that to:

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
from
 a_stmt left outer join a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id
 join  a_carr on a_stmt.acna = a_carr.acna join s_dataccs on a_stmt.acna = s_dataccs.acna and
   ( a_icc.usg_ind = 'S' ) and
   ( s_dataccs.user_id = @user_id ) and
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
   ( a_stmt.amt_du_ic < 0 )

Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check. It would be really kind, if someone could help me out with this!

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it.

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
 FROM  a_stmt
    LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id
    INNER JOIN a_carr on a_carr.acna = a_stmt.acna
    INNER JOIN s_dataccs on s_dataccs.acna = a_stmt.acna
WHERE (a_icc.usg_ind = 'S') and
(s_dataccs.user_id = @user_id)  and
( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
( a_stmt.amt_du_ic < 0 ) ;

It seems like you have the same thing as me (or at least basically), so I would say you are good.  Obviously if you could test that would be best but I feel comfortable that it is the same thing logically.


Next Steps to Diagnose ODBC SQLAllocHandle on SQL_HANDLE_DBC failed An application intermittently fails with generic ODBC error. The client application is using ODBC to connect to SQL cluster. Enabling ODBC tracing shows this error:

StorageOnlineOp 46ec-44a8       ENTER SQLDriverConnectW
                              HDBC                0x012E2FD8
                              HWND                0x00000000
                              WCHAR *             0x6C228B34 [      -3] "******\ 0"
                              SWORD                       -3
                              WCHAR *             0x6C228B34
                              SWORD                       -3
                              SWORD *             0x00000000
                              UWORD                        0 <SQL_DRIVER_NOPROMPT>

StorageOnlineOp 46ec-44a8       EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
                              HDBC                0x012E2FD8
                              HWND                0x00000000
                              WCHAR *             0x6C228B34 [      -3] "******\ 0"
                              SWORD                       -3
                              WCHAR *             0x6C228B34
                              SWORD                       -3
                              SWORD *             0x00000000
                              UWORD                        0 <SQL_DRIVER_NOPROMPT>

                              DIAG [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed (0)

According to Microsoft the error is explained:

  (DM) During SQLConnect, the Driver Manager called the driver's
  SQLAllocHandle function with a HandleType of SQL_HANDLE_DBC and the
  driver returned an error.

Any recommended steps for further narrowing down the problem?

According to ODBC logs version information is:

03.80.0000 = SQLGetInfoW (SQL_ODBC_VER)
03.52 = SQLGetInfoW (SQL_DRIVER_ODBC_VER)

In this case I linked StorageOnlineOp in ODBC log to process name StorageOnlineOpns.exe. When issue occurred I used Task Manager to right click process and "Create Dump File"

An analysis shows the process was a 32-bit process, nearing it's 2GB address space limit, and was unable to allocate memory for the database connection.

Opening the dmp file in WinDbg, used the following commands to identify problem:

    0:011> lmvm StorageOnlineOpns
Browse full module list
start    end        module name
00170000 002b9000   StorageOnlineOpns   (export symbols)       StorageOnlineOpns.exe
    Loaded symbol image file: StorageOnlineOpns.exe
    Image path: D:\Program Files (x86)\Enterprise Vault\StorageOnlineOpns.exe
    Image name: StorageOnlineOpns.exe
    Browse all global symbols  functions  data
    Timestamp:        Wed May  1 11:23:18 2013 (51806E86)
    CheckSum:         0014CE8C
    ImageSize:        00149000
    File version:     10.0.3.1188
    Product version:  10.0.3.0
    File flags:       0 (Mask 3F)
    File OS:          4 Unknown Win32
    File type:        2.0 Dll
    File date:        00000000.00000000
    Translations:     0409.04b0
    CompanyName:      Symantec Corporation
    ProductName:      Enterprise Vault
    InternalName:     STORAGEONLINEOPNS
    OriginalFilename: STORAGEONLINEOPNS.DLL
    ProductVersion:   10, 0, 3, 0
    FileVersion:      10.0.3.1188
    FileDescription:  StorageOnlineOpns Module
    LegalCopyright:   Copyright (c) 2013 Symantec Corporation. All rights reserved. Use of this product is subject to license terms.

This process is 32-bit so is limited to 2GB address space, this is close to being exhausted:

0:011> !heap -s
Mapping file section regions...
Mapping module regions...
Mapping PEB regions...
Mapping TEB and stack regions...
Mapping heap regions...
Mapping page heap regions...
Mapping other regions...
Mapping stack trace database regions...
Mapping activation context regions...

--- Usage Summary ---------------- RgnCount ----------- Total Size -------- %ofBusy %ofTotal
Heap                                    669          6a04c000 (   1.657 GB)  86.08%   82.83%
<unknown>                               397           85b5000 ( 133.707 MB)   6.79%    6.53%
Image                                   712           58ea000 (  88.914 MB)   4.51%    4.34%
Free                                    406           4d72000 (  77.445 MB)            3.78%
Stack                                   153           3300000 (  51.000 MB)   2.59%    2.49%
Other                                    46             5f000 ( 380.000 kB)   0.02%    0.02%
TEB                                      51             33000 ( 204.000 kB)   0.01%    0.01%
PEB                                       1              1000 (   4.000 kB)   0.00%    0.00%

--- Type Summary (for busy) ------ RgnCount ----------- Total Size -------- %ofBusy %ofTotal
MEM_PRIVATE                            1201          73d79000 (   1.810 GB)  94.06%   90.50%
MEM_IMAGE                               741           5b38000 (  91.219 MB)   4.63%    4.45%
MEM_MAPPED                               87           19cd000 (  25.801 MB)   1.31%    1.26%

--- State Summary ---------------- RgnCount ----------- Total Size -------- %ofBusy %ofTotal
MEM_COMMIT                             1605          6f65a000 (   1.741 GB)  90.45%   87.03%
MEM_RESERVE                             424           bc24000 ( 188.141 MB)   9.55%    9.19%
MEM_FREE                                406           4d72000 (  77.445 MB)            3.78%

--- Protect Summary (for commit) - RgnCount ----------- Total Size -------- %ofBusy %ofTotal
PAGE_READWRITE                          902          68c29000 (   1.637 GB)  85.06%   81.85%
PAGE_EXECUTE_READ                       135           3e04000 (  62.016 MB)   3.15%    3.03%
PAGE_READONLY                           382           2960000 (  41.375 MB)   2.10%    2.02%
PAGE_WRITECOPY                           83            1fc000 (   1.984 MB)   0.10%    0.10%
PAGE_READWRITE|PAGE_GUARD               103             d1000 ( 836.000 kB)   0.04%    0.04%

--- Largest Region by Usage ----------- Base Address -------- Region Size ----------
Heap                                         dd10000            fd0000 (  15.813 MB)
<unknown>                                   7f0e0000            f00000 (  15.000 MB)
Image                                       6a611000            e70000 (  14.438 MB)
Free                                        72f4a000            1c6000 (   1.773 MB)
Stack                                        1980000             fd000 (1012.000 kB)
Other                                       7efb0000             23000 ( 140.000 kB)
TEB                                         7ef19000              1000 (   4.000 kB)
PEB                                         7efde000              1000 (   4.000 kB)

The database connection code is failing due to inability to allocate more memory:

0:011> k
 # ChildEBP RetAddr
00 031ac570 73b217cd ntdll!NtWaitForSingleObject+0x15
01 031ac5b0 73b27696 mswsock!SockWaitForSingleObject+0x3a
02 031ac62c 764b689f mswsock!WSPRecv+0x2af
03 031ac674 739616c7 ws2_32!recv+0x87
04 031ac6c4 5510b4a2 dbnetlib!ConnectionRead+0x429
05 031ac700 5514ceb2 sqloledb!CDataSource::ConnectionRead+0x82
06 031ac754 550f9056 sqloledb!CDBConnection::GetBytesFromWire+0x228
07 031ac7a4 551888e0 sqloledb!CDBConnection::ProcessTDSStream+0x1d6
08 031ac858 55121a4f sqloledb!CStmt::SQLExecRPC+0x61b
09 031ac894 550f88cb sqloledb!CCommand::ExecuteHelper+0x232
0a 031ac920 550ff5c6 sqloledb!CCommand::Execute+0x127c
0b 031ac960 57e5a335 sqloledb!CImpICommandText::Execute+0x16f
0c 031ac9a0 57e52c8b msado15!CConnection::Execute+0x9c
0d 031acbd8 57e5573b msado15!_ExecuteAsync+0x1e1
0e 031acbf4 57e556f7 msado15!ExecuteAsync+0x6f
0f 031acd24 57e54ecf msado15!CQuery::Execute+0x10f5
10 031acd88 57eabd39 msado15!CCommand::_Execute+0x192
11 031acfe0 57ead171 msado15!CCommand::ExecuteWithModeFlag+0x8da
12 031ad00c 5acb4406 msado15!CCommand::Execute+0x5e
WARNING: Stack unwind information not available. Following frames may be wrong.
13 031ad28c 5aca7a1f AdoInterface!CADODataAccess::ExecuteSQLCommand+0x266
14 031ad2b4 5853ff29 AdoInterface!CADODataAccess::ExecuteStoredProcedure+0x1f
15 00000000 00000000 EVFingerprintCatalogueAccessor!CRegistryHelper::IsOpen+0x15bb9
0:011> !gle
LastErrorValue: (Win32) 0 (0) - The operation completed successfully.
LastStatusValue: (NTSTATUS) 0xc0000017 - {Not Enough Quota}  Not enough virtual memory or paging file quota is available to complete the specified operation.

In addition there is was a lock blocking 4 threads. The blocking function was a heap function. This is commonly found in heap corruption issues.

0:004> k
 # ChildEBP RetAddr
00 026eed5c 77a1ebae ntdll!NtWaitForSingleObject+0x15
01 026eedc0 77a1ea92 ntdll!RtlpWaitOnCriticalSection+0x13e
02 026eede8 77a1ece9 ntdll!RtlEnterCriticalSection+0x150
03 026eeed4 77a02bd5 ntdll!RtlpFreeHeap+0x96
04 026eeef4 75b06e8a ntdll!RtlFreeHeap+0x142
05 026eef08 7671d9f6 ole32!CRetailMalloc_Free+0x1c [d:\w7rtm\com\ole32\com\class\memapi.cxx @ 687]
06 026eef1c 7671daad oleaut32!_SafeArrayFreeData+0x1f
07 026eef2c 766dfdf0 oleaut32!_SafeArrayReleaseData+0x37
08 026eef4c 766dfd5c oleaut32!_SafeArrayDestroyData+0x81
09 026eef68 766f0fc3 oleaut32!_SafeArrayDestroy+0xa0
0a 026eef8c 766f133c oleaut32!LPSAFEARRAY_UserFree+0x10e
0b 026eefa0 759f7c1e oleaut32!VARIANT_UserFree+0xae
0c 026eefd8 759f4256 rpcrt4!NdrUserMarshalFree+0x37
0d 026eeff8 75a7083e rpcrt4!NdrPointerFree+0xa8
0e 026ef01c 75a7072a rpcrt4!NdrpFreeParams+0x145
0f 026ef030 75a706e0 rpcrt4!NdrStubCall2+0x4eb
10 026ef428 75bee7e6 rpcrt4!NdrStubCall2+0x42a
11 026ef470 75bee876 ole32!CStdStubBuffer_Invoke+0xb6 [d:\w7rtm\com\rpc\ndrole\stub.cxx @ 1590]
12 026ef4b8 75beedd0 ole32!SyncStubInvoke+0x3c [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1187]
13 026ef504 75b08a6b ole32!StubInvoke+0xb9 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1396]
14 026ef5e0 75b08960 ole32!CCtxComChnl::ContextInvoke+0xfa [d:\w7rtm\com\ole32\com\dcomrem\ctxchnl.cxx @ 1262]
15 026ef5fc 75b09532 ole32!MTAInvoke+0x1a [d:\w7rtm\com\ole32\com\dcomrem\callctrl.cxx @ 2105]
16 026ef628 75beeccd ole32!STAInvoke+0x46 [d:\w7rtm\com\ole32\com\dcomrem\callctrl.cxx @ 1924]
17 026ef65c 75beeb41 ole32!AppInvoke+0xab [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1086]
18 026ef740 75bef1fd ole32!ComInvokeWithLockAndIPID+0x372 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1724]
19 026ef768 75b0938f ole32!ComInvoke+0xc5 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1469]
1a 026ef77c 75b0934e ole32!ThreadDispatch+0x23 [d:\w7rtm\com\ole32\com\dcomrem\chancont.cxx @ 298]
1b 026ef7c0 760f62fa ole32!ThreadWndProc+0x161 [d:\w7rtm\com\ole32\com\dcomrem\chancont.cxx @ 654]
1c 026ef7ec 760f6d3a user32!InternalCallWinProc+0x23
1d 026ef864 760f77c4 user32!UserCallWinProcCheckWow+0x109
1e 026ef8c4 760f788a user32!DispatchMessageWorker+0x3b5
1f 026ef8d4 001cc7d4 user32!DispatchMessageW+0xf
WARNING: Stack unwind information not available. Following frames may be wrong.
20 026ef938 001cc8a9 StorageOnlineOpns+0x5c7d4
21 026ef974 752634c7 StorageOnlineOpns+0x5c8a9
22 026ef980 7650336a msvcr90!_endthreadex+0xd8
23 026ef98c 77a09902 kernel32!BaseThreadInitThunk+0xe
24 026ef9cc 77a098d5 ntdll!__RtlUserThreadStart+0x70
25 026ef9e4 00000000 ntdll!_RtlUserThreadStart+0x1b


Archiving MSSQL Database and Retrieving the archived data for reporting I am still an amateur when it comes to MS SQL Server.

I have been tasked with Archiving our Production Database which is growing daily.  Mainly I am looking at the transactions tables, simply because they are the ones that generate the most data (average of a million records per day).

I understand that the tables from which they pull data from might need to be archived as well for referential integrity purposes.

I also need a way to be able to query this archived data whenever needed as quickly as possible if there's a report needed. Any advice?

It sounds like whatever application is feeding this database is dumping tons of records in there if as you say there are 1 million transactions a day.  See if whatever is feeding this database has a retention feature.  If you have 1 million + records a day you are going to run out of space quickly.

There are a lot of factors involved in this type of process.  Size of the database, number of transactions, network speeds, storage space, etc...

If I were in your shoes at this moment I would restore a copy of your backup either to another instance or to the same instance if you have room.  I would truncate out all data that is not needed.  I would then create a SQL agent job that will insert the data into the new database tables with the GetDate() function and whatever offset time is needed and do it in a batch function of maybe 1000 per batch.

Here is a sample of one that I use.  It can be an insert or delete batch:

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
    BEGIN
        BEGIN TRANSACTION;
        --This could be an INSERT or DELETE etc.
        DELETE TOP (1000)
        FROM dbo.application_log
        WHERE(DATEADD(MM, -6, GETDATE()) > date_created);
        SET @r = @@ROWCOUNT;
        COMMIT TRANSACTION;
    END;

After that is completed, then run a delete batch to remove the data from the primary database, which will purge the old data.

Keep in mind this is a very simplistic model of a data transfer and purge.  There are many ways to accomplish this, but require a greater knowledge of SQL and would take much longer to explain than we have time for here.

Hope that helps.


How to audit table permissions as a non-DBA I'm not a DBA, but I do administer a schema called SCHEMA_1.

I'm in charge of assigning privileges to the tables that I create in my schema. Since there aren't any sensitive or permission-unique tables in the schema, the DBA has instructed me to assign the same privileges to all tables.

Every table has these privileges:

ROLE_A has SELECT
ROLE_B has SELECT, INSERT, UPDATE, DELETE
ROLE_C has SELECT, INSERT, UPDATE, DELETE
SCHEMA_2 has SELECT

I would like to audit the tables in my schema to ensure that I haven't accidentally assigned the wrong privileges to any tables. I'm not sure how this is normally done by DBA's, but one idea I had was to tabulate the tables and privileges like this:

+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_NAME | ROLE_OR_SCHEMA | SELECT | INSERT | UPDATE | DELETE | OTHER (ERRORS) |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_1    | ROLE_A         | y      |        |        |        |                |
| TABLE_1    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_1    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_1    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_2    | ROLE_A         | y      |        |        |        |                |
| TABLE_2    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_2    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_2    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_3    | ROLE_A         | y      |        |        |        |                |
| TABLE_3    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_3    | ROLE_C         | y      | y      | y      | y      | OTHERS! ERROR! |
| TABLE_3    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+

The reason that I've suggested this sort of layout is because this is how I normally see the privileges when I set them using my GIS software:

Assuming that this is a logical approach (it might not be), how can I do this?

The only tools I have at my disposal are my GIS software and SQL Developer.

You need to connect to schema_1 to run this query.
There is a helpful view user_tab_privs. If you are on 11g or higher, then
listagg function can show the list of other privileges. Otherwise remove row with listagg form query

select table_name, grantee ,
        max(sel) sel, max(ins) ins , max(upd) upd, max(del) del,
        case when max(other_privs) is not null then 'other' end other_privs,
        listagg(other_privs,',') within group (order by other_privs) as list_of_other_privs
 from
 (select table_name, grantee,
        case when privilege='SELECT' then 'y' end sel,
        case when privilege='INSERT' then 'y' end ins,
        case when privilege='UPDATE' then 'y' end upd,
        case when privilege='DELETE' then 'y' end del,
        case when privilege not in ('SELECT', 'INSERT','UPDATE','DELETE') then privilege end other_privs
   from user_tab_privs
 )
  group by table_name, grantee
  order by table_name, grantee


Create SQL Server user with SQL Server Authentification I am trying to login to a SQL Server 2014 from another service (AWS Schema Conversion Tool). In the AWS tool I need to input 'User name' and 'Password'. Currently I am using windows authentication for connection to a MS SQL DB. My user name as per SSMS is 'Server\user'. I have tried logging into the DB from the AWS tool using user as 'Server\user' and 'user' with my windows password but keep getting a 'Incorrect username or password alert'.

To login from the AWS tool I would like to create a dummy user who has admin privileges and SQL Server Authentication. In SSMS have tried

Server->Databases->DB->Security->New user.

I created a new dummy windows admin user the tried to create 'SQL user with login' and got the error 'Create failed for user 'dummy''. ''dummy' is not a valid login or you do not have permission'. I am logged in with admin rights and 'dummy' definitely exists as a windows user.

Any ideas on how I can create a new user with SQL Server Authentication?

Hope these links help to fix your problem ---

https://stackoverflow.com/questions/6643720/add-login-and-connect-to-sql-with-sql-server-authentication

https://movabletype.org/documentation/mt5/advanced/creating-a-sql-server-database-2.html

The message you are receiving basically means "I can't connect to SQL Server". It can be due to a number of issues:

The SQL Server service is not running
The SQL Server instance is not
configured to use TCP/IP to communicate

The database is using windows authentication and the user is not authorized orThe database is using mixed-mode authentication and the user/pass is incorrect  As for the usernames you are given, I would assume that the "administrator" username is local windows administrator of the machine SQL server is running on, and that the "sa" user is the default database administrator, that is installed when setting SQL Server to use mixed-mode authentication.  So, I would recommend to try connecting to the database using Mixed-mode authentication, with the  "sa" username the "anotherpassword" password.


How to configure DBPROP_COMMANDTIMEOUT? How to configure the property of DBPROP_COMMANDTIMEOUT ?

Can this property be configured in Database ?

Thanks.

The Connection timeout is always set by client. The timeout is normally configured when the connection is created. In order to prevent recompilation of client code the timeout interval is often stored in a config file, registry entry etc

You cannot configure the timeout on the database itself.


Testing Database Replication and timestamps are different This may be a stupid question.

I've just setup database replication across two servers on different networks.

The Master is on MySQL v5.5.52 and Slave is on MySQL v5.7.21.

The replication all looks good, but when I manually checked the databases this morning to make sure it's pulling through accurately, I noticed the timestamps were different.

One of my tables has a column for the current timestamp, which on update is updated with NOW().

The master timestamp reads as 2018-02-01 16:43:23 the slave as 2018-02-01 22:43:23

Why would these be different? How can I make these match?

Do this on both machines:

show variables like '%zone%';

Also check the OS dates.

I think you will find a configuration difference.


Can SQL Server database be changed from NoRecovery to Standby? I'm receiving a log shipped database (and daily transaction logs) from an outside vendor -- to our FTP.  No internal access allowed.  No connection between the two databases.

I've restored the database (and transaction logs) "with NoRecovery" to allow further transaction logs to be applied, but it needs to be in Standby mode to allow for Users to read the data.

Is there a way to convert a database from NoRecovery to Standby?  [So that future transaction logs can be applied]

Yes, you can restore the logs by changing the settings for the Restore Job to be set to STANDBY, instead of NORECOVERY.  This would provide a degree of Read Only access until the next restore is run.

If you are using the log shipping functions that come with SQL Server you use the settings shown by Ali Razeghi for using sp_change_log_shipping_secondary_database.

If you have rolled your own log shipping process (which many people have done) then you are already controlling the restore process yourself.

EDIT: Since you restore the secondary one time a day, the READ ONLY window will be most of a day.  So that eases the scheduling issue.

Since the procedure sp_change_log_shipping_secondary_database is not working, either (1) you ran this on the primary instead of the secondary server, or (2) your organization made their own custom log shipping process.

EDIT: If you are in the (2) state, then please include the details of the secondary server's Log Shipping Restore Job.

However, a database in NORECOVERY can be updated to STANDBY like this:

RESTORE DATABASE [MyDatabase]
WITH STANDBY = N'D:\MSSQL\Backup\MyDatabase_RollbackUndoFile.bak'

Any time the Restore Job recovers a series of LOG files, it will disconnect any STANDBY users that are still connected.

Also see Kendra Little's post: Reporting From a Log Shipping Secondary in STANDBY mode  Contained in here post is a mention of the problem that you have run into with restoring a 2008 database to a 2008 R2 Secondary.

  You can’t use “STANDBY” Mode if your Secondary is a Higher Version

  I was a little sad when I learned this one years ago. I had a SQL
  Server 2005 instance that was the log shipping primary. The log
  shipping secondary was made readable so that developers could check
  out production data if needed without having access to production.

This is because the database pages need to be upgraded to the 2008 R2 level in order to be readable by STANDBY, but that is a violation of keeping the Secondary exactly like the Primary.

Really the only route to getting STANDBY mode on your secondary is to upgrade your SQL Server 2008 to SQL Server 2008 R2 so as to match the secondary. (Or
(if you want to modernize) to upgrade to a higher version for both primary and secondary.)


Can't add a foreign key constraint in a table on Postgresql I'm trying to add a foreign key of "prefectures" table in "centresdevote" table.
But I'm having this error while trying to add a foreign key to "centresdevote" table.
I had no problem adding foreign keys to other tables in my database by the way, and I can't seem to figure out why this error pops up only for this one.
I'm working on Postgresql 9.2 and I've added the foreign keys for the other tables manually. I tried also using SQL code as follows :

ALTER TABLE centresdevote
ADD CONSTRAINT objectid
FOREIGN KEY (objectid) REFERENCES prefectures (objectid);

Here's the error

ERROR:  insert or update on table "centresdevote " violates foreign key constraint "objectid"
DETAIL:  Key (objectid)=(10) is not present in table "prefectures".

Here's the structure of the two tables :

"prefectures" table :

objectid integer NOT NULL,
nomprefecture character varying(70),
shape st_geometry,
respcommissionrecensement character varying(70),
CONSTRAINT prefectures_pkey PRIMARY KEY (objectid)

"centresdevote" table :

objectid integer NOT NULL,
arabicname character varying(70),
frenchname character varying(70),
shape st_point,
CONSTRAINT centresdevote_pkey PRIMARY KEY (objectid)

Thanks in advance!

In the table centresdevote you have a row where the value of objectid is 10. As you are trying to create a FOREIGN KEY CONSTRAINT there is a check that there is a matching value in the column objectid in the table prefectures for all the rows in centresdevote. In prefectures there is no row with objectid = 10.

I suggest you read The PostgreSQL Documentation - 5.3.5. Foreign Keys and the W3Schools page about foreign keys. The whole point of the foreign keys is that they limit what you can insert into the field. From PostgreSQL Documentation:

  A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

Adding foreign key is not mandatory and you can do every kind of query without it.


How do I go about creating this SQL query? I'm attending the free DB course at Stanford Online, and frankly one of the  exercises gave me some trouble. I have a feeling that this should be horribly simple, so for a DBA, I'm obviously not very good with SQL.

We're working with a simplified scenario for rating movies.

  For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.

Here's the schema:

Movie ( mID, title, year, director )
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate )

How should I go about this?

You can self join to tables for this sort of criteria(at least, I can do this in SQL Server):

SELECT
     r.name,
     m.title
FROM
    rating rt
    JOIN movie m ON (rt.mid = m.mid)
    JOIN reviewer r on (rt.rid = r.rid)
    JOIN rating rt2 on (rt.mid = rt2.mid and rt.rid = rt2.rid)
WHERE
    rt.ratingDate > rt2.ratingDate
    AND rt.stars >  rt2.stars;

Note that we're joing rating back to itself.  This way we can use the WHERE clause to find rows in rating that are greater in both date and stars to other rows in rating.

You can review the full example I built in SQL Fiddle here.

If you want to add the condition that the reviewer made exactly these 2 reviews of the movie and no more, you can add:

    AND NOT EXISTS                                  --- not exists
        ( SELECT *
          FROM rating rt3                           --- another rating
          WHERE rt3.mid = m.mid                     --- for same movie
            AND rt3.rid = r.rid                     --- by same reviewer
            AND rt3.ratingDate <> rt2.ratingDate    --- on different date
            AND rt3.ratingDate <> rt1.ratingDate    --- than the first 2 reviews
        )

This assumes that the Rating table has  (rID, mID, ratingDate) as primary key.


Allow user to drop only tables / views created by that user in MySQL Is it possible to allow a user to only drop tables and views that he/she created in MySQL? How is this achieved?

I'd create a dedicated MySQL instance per user (with docker for example) and let them do whatever they want. Your situation maybe different, of course, I'm just sharing an idea.


Should I include another column here to keep track of cost + tax or not? I am making a two database tables tho add a sales order line item functionality to my application.  On the second table, which lets  call salesorderlineitems, I have the following schema

CREATE TABLE salesorderlineitems (
    other columns here,
    units INT(11),
    costPerUnit DECIMAL(11,2),
    totalCost DECIMAL(11,2),
    tax DECIMAL(11,2)
);

Now I realized after some developing that besides totalCost being sort of a misnomer since it's only costPerUnit * units, and not including the totalCost.  That made me think, should I change the name of totalCost to say totalUnitsCost and have totalCost be costPerUnit*units + tax.

That made me think well I can always just calculate that manually by doing totalUnitcost + tax, so why bother saving it.  But then I started thinking of well that's also true of totalUnitsCost since it's definition is just multiplication of two other columns.

I know my use case here is pretty simple but I'm sure this concept has popped up in many peoples databases.  Is it worth it to have a column that is just a calculation between two other columns, and under what conditions is it so?  Also, how would I document this if say I choose to go the other route and and only keep track of units costPerUnit and tax and leave the rest up to doing some intra-query calculations, and under what conditions would this be preferable?

This is a legal question.  The database schema should mimic what the laws mandate.

Since the tax_rate can change all too often, you really should include the rounded value of tax in the table.

On the other hand, total_cost is necessarily equal to subtotal of cost and tax, you should not include it.  A general rule in schema design is not to include any redundant data; that includes readily computable values.

Another thing to think about is "when" the tax is computed.  In some types of stores, the tax must be calculated from the subtotal.  In other types of stores, the laws may require computing the tax on each item (or multiple of items).

Perhaps you need a boolean flag called taxable.

Some laws say that some items are taxed and some are not.  For example, in California, food generally does not incur sales tax.  But a grocery store often sells non-food items, so there are two subtotals: "taxable" and "non-taxable".  To further confuse things there was, briefly, a "junk food tax" on various 'foods'.

Then comes "VAT" -- which seems to be reverse-computed from the total that the customer pays.


Lossy Decomposition meaning Suppose I have a relation R=(A,B,C,D) and I decompose it into:

R1=(A,B,C)
R2=(A,D)

then if it is said that the decomposition is lossy,then what is the meaning of that statement?I have an understanding that the decomposition is lossy iff:

R1 ∪ R2=R

But can't see the meaning of this here because schemas are not same so we cannot take union.

As this is a loss less join thus in lossless join we know that on joining we can get original relation along with some extra tuples thus answere is r⊂S where r is the relation in which original reltions were decomposed.


How to properly use moveChunk when chunks of certain range needs to be moved? A MongoDB 3.6.3 database has two shards and a mongodump file with partial data of one collection needs to be restored. The first shard is named "fast" while second "slow". The idea is to restore the dump to the "slow" shard. According to sharding rules the data should go to the "slow" shard, but it actually goes to the wrong one when restore is tried.

Before restoring the data, I want to manually move a range of chunks from fast to slow shard, but are unable to properly issue the command. All examples found are showing of moving only one exact chunk. _id is used as a sharding key.

Try 1:

use admin
db.runCommand({ moveChunk: "db.use", bounds : [ {_id : ObjectId("58b60e73e5d4e7019aa2be17")}, {_id : ObjectId("58bca60f5067031c77b03807")} ], to: "rs1" })

This is the response:

{
        "ok" : 0,
        "errmsg" : "no chunk found with the shard key bounds [{ _id: ObjectId('58b60e73e5d4e7019aa2be17') }, { _id: ObjectId('58bca60f5067031c77b03807') })",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1523459407, 14673),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1523459407, 14673)
}

Try 2:

sh.moveChunk("db.use", {_id:{$gt: ObjectId("58b60e73e5d4e7019aa2be17"), $lt: ObjectId("58bca60f5067031c77b03807") }},"rs1")

Response:

{
        "ok" : 0,
        "errmsg" : "no shard key found in chunk query { _id: { $gt: ObjectId('58b60e73e5d4e7019aa2be17'), $lt: ObjectId('58bca60f5067031c77b03807') } }",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1523460271, 11742),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1523460271, 11742)
}

Any idea how to move chunks that belong to certain shard key range?

Until a more decent way is found, I used this script solution:

use config
sh.stopBalancer()
var lower=ObjectId("58B60F00e4b03547ad945a8a");
var upper=ObjectId("58BCA680e4b03547ad945a8a");
var query={shard: "rs0", ns: "db.use", "max._id":{$gte: lower}, "min._id": {$lte: upper}};
var cursor=db.chunks.find(query);

cursor.forEach(function(d) {
 print( "chunk: " + d.min._id );
 sh.moveChunk("feedback.usage", { "_id" : d.min._id }, "rs1");
});
sh.setBalancerState(true)


~2 Hours after a large insert SQL Server gets "I/O requests taking longer than 15 seconds to complete" This problem has been haunting me for a while but I actually fixed the queries that caused the problems (after I finally found them) but i'm curious if it's an SQL Process that's the underlying problem our that our raidcontrollers are shit or what the problem truely is. So here's the scenario that's been bothering us.

@ 11:10~ and 01:10~ the server would give these IO errors, even through Remote desktop the servers would fail, management studio would give timeouts, everything would point towards imminent failure of the cluster.

(The servers are identical, we run Windows Server 2012 standard, clustered in an AlwaysOn running SQL Server 2012 Enterprise edition).

Around 5-15 minutes later, the servers would come back to live (without anything in the eventlogs except for the Event ID 833 which is the "well known": SQL Server has encountered 16870 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\XXXXXXXX.mdf] in database [XXXXX] (10).  The OS file handle is 0x0000000000001284.  The offset of the latest long I/O is: 0x0000041fb3c000).

Now I was running profilers to find out which queries were running, doing anything I could to figure out what would cause the servers to just freeze up and refuse to service properly. The event log aims towards a specific Database to I searched in that direction and yesterday I found a table that looked odd to me.

The specific table was 11 gigs large, had indexes worth 23 gigs (haven't looked into those yet but that seemed wrong to me) and had 80 million records. Considering the table should hold around 2 million and be growing relatively slowly this sparked my interested and I found three stored procedures which fill this table.

All three storedprocedures had flaws which made them insert not a few hundred records but over 500.000 each time they ran. (These are now fixed so the issue doesn't present itself but I feel there is an underlying problem here).

These stored procedures would run @ 09:00 and 23:00 (excatly 2 hours before the problems occurred). The procedures themselves would take around 5 minutes to run to completion.

Now I'm wondering why it would take the servers two hours to start dying on me after the actual storedprocedures in question would already have been done. I don't run any index maintenance at that point, or anything else for that matter (it's the first thing I checked for). For all I know nothing runs @ the specific times. Could it be SQL Server does some 'self maintenance' and notices that some indexes need to be defragmentated or something even if I haven't asked for any such thing?

Could it be our raidcontrollers (or even the SSD's that are connected to them) that are flawed and after A LOT of activity decide to show signs of degration after 2 hours?

I simply can not explain this behavior and I'm worried our production servers might be unreliable (which would suck since they're around 4 months old). Now i've fixed this issue last evening, and for 24 hours we haven't had this problem (I wouldn't call it 'resolved' unless it's been stable for multiple days) but I'm still very curious what the hell my servers are doing.

BOL refers to 833 event ID as :

  This problem can be caused system performance issues, hardware errors, firmware errors, device driver problems, or filter driver intervention in the IO process.

What it means is that

SQL server is doing more I/O's that what the disk subsystem can handle.
There could be some rogue process running on the system that is saturating the disks with all the I/O requests.

Below is the approach I would follow:

Troubleshoot using Wait Statistics - DMV's

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

Look at the pending I/O's

SELECT database_id
    ,file_id
    ,io_stall
    ,io_pending_ms_ticks
    ,scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) t1
    ,sys.dm_io_pending_io_requests AS t2
WHERE t1.file_handle = t2.io_handle

Below are PERFMON counters that will help you:

Avg. Disk sec/Transfer => Time taken to perform the I/O operation
Disk Bytes /sec  => Total reads and writes to disk per second in bytes.
Process:IO Data Bytes/Sec => Total reads and writes to disk per second in bytes by each process.
Buffer Manager: Page Read/sec + Page Writes/sec =>Total reads and writes to disk per second in bytes by SQL Server process.

Also, sometimes such errors can also occur when a different Power Management scheme is selected for the server. So better check your Windows Power Plan setting, and make sure you are using the High Performance Power Plan.

  All three stored procedures had flaws which made them insert not a few hundred records but over 500.000 each time they ran. (These are now fixed so the issue doesn't present itself but I feel there is an underlying problem here).

If you are inserting a huge amount of data, then you need to refer to The Data Loading Performance Guide to cater your server for such operations e.g. enabling trace flag 610.

Note: Others have mentioned that Auto Update Stats might be causing it, which is also worth looking at, but I would primarily concentrate on finding out rouge process that is causing lots of Disk I/Os and checking the storage subsystem to see if all the divers, etc are up-to-date and is configured correctly.

Good reading links :

I/O requests taking longer than 15 seconds to complete on file
Troubleshooting SQL Server I/O requests taking longer than 15 seconds - I/O stalls & Disk latency
Event ID 833: I/O requests taking longer than 15 seconds


SSMS 2012 is not showing the tables of the system databases I have SQL Server Management Studio 2012 on my laptop connected to a SQL Server 2012. If I go in System Databases and then any of the 4 (master, model, msdb, tempdb) I don't have the Tables folder.

I'm quite sure it was there in SSMS 2008 R2. Why were the tables hidden? How can I make them visible?

As per this Connect item it will be fixed in the next major release of SQL Server.


Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR? Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration.

Unfortunately, I couldn't get the example to work for event JOB_OVER_MAX_DUR in Oracle 10gR2 on one particular box.

What I've tried is:

-- create log table
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
  (message IN sys.scheduler$_event_info) as
begin
--
  insert into job_output values (systimestamp,'sniper job started for '
   ||message.event_type||' from '
   ||'"'||message.object_owner||'"."'||message.object_name ||'"');
  commit;
end;
/

-- create a sniper program
begin
  dbms_scheduler.create_program (
      program_name => 'sniper_prog',
      program_action=> 'sniper_proc',
      program_type => 'stored_procedure',
      number_of_arguments => 1,
      enabled => FALSE) ;
--
  dbms_scheduler.define_metadata_argument ('sniper_prog','event_message',1);
  dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to log any job that has
-- raised an event
begin
  dbms_scheduler.create_job('sniper_job',
    program_name=>'sniper_prog',
    event_condition =>
      'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'' OR tab.user_data.event_type = ''JOB_SUCCEEDED''',
    queue_spec =>'sys.scheduler$_event_queue,myagent',
    enabled=>true);
end;
/

-- create job to test the sniper job
begin
  dbms_scheduler.create_job
    ( 'first_job', job_action =>
        'insert into job_output values(systimestamp, ''first job begins'');
         commit; dbms_lock.sleep(120);
         insert into job_output values(systimestamp, ''first job ends'');',
      job_type => 'plsql_block',
      enabled => false ) ;
  -- set max runtime
  dbms_scheduler.set_attribute
    ( 'first_job' , 'max_run_duration' , interval '60' second);
  -- set all events to be raised (for debugging)
  dbms_scheduler.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => dbms_scheduler.job_all_events);
  -- start the job
  dbms_scheduler.enable('first_job');
end;
/

For some reason the event JOB_OVER_MAX_DUR is either not raised or the sniper_job doesn't receive it. Only JOB_SUCCEEDED is received.

My log table looks like:

SELECT *
  FROM job_output
ORDER BY 1 DESC;

25.07.2012 10:39:11,475879 +02:00   sniper job started for JOB_SUCCEEDED from "SCOTT"."FIRST_JOB"
25.07.2012 10:39:10,155557 +02:00   first job ends
25.07.2012 10:37:10,142660 +02:00   first job begins

So no event JOB_OVER_MAX_DUR was received.

Any ideas what I am doing wrong or which init parameters I have to check/set or how to debug these events?

Edit: I'm interested why this isn't working in 10g. This example is working fine in 11g but not in 10g and I don't see any reason why.

Edit 2: The example is working fine at a different 10g box, but on the intended one it's not.  I've checked all the %scheduler% dictionary views, but none of them gives details on which events have been raised. Any ideas/hints how to debug scheduler events? In this case I have DBA rights and OS server level access to debug.

When adding multiple attributes, try:

DBMS_SCHEDULER.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.some_attribute);

The valid values for event types (11gr2) is here.  And according to docs, job_over_max_dur is enabled already for 11gr2.

Now, for 10g or 11gr1, I don't see job_over_max_dur in the list of event types raised by the scheduler.  But you mentioned in earlier post that you will very soon migrate to 11gr2, so I assume you have a test box setup already that you can test this with 11gr2.  If you really want to go down the rabbit hole with AQ, you can do more custom events, but it would probably be much easier to wait a bit until you're on 11gr2.

Edit

To add more info, first some basic scheduler stuff you can view to see whats running (or scheduled, or stalled, or whatever state):

select * from dba_scheduler_jobs
where owner = 'SOME_OWNER'
and job_name = 'SOME_JOB';

You can see if max_run_duration column is set and also the "raise_events" column will show a list of event types that are to be raised by each job.  You should see "JOB_OVER_MAX_DUR" type here.

If you want to test if your db environment is handling the JOB_OVER_MAX_DUR properly, then you can setup a quick test:

BEGIN
    -- setup simple scheduler job
   --DBMS_SCHEDULER.DROP_JOB('EVENT_RAISING_JOB',false);
   DBMS_SCHEDULER.create_job (
      job_name        => 'EVENT_RAISING_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN
                            dbms_lock.sleep(70);
                          END;',
      start_date      => SYSTIMESTAMP,
      end_date        => SYSTIMESTAMP + 2, -- 2 day
      repeat_interval => 'freq=daily; byhour=1',
      enabled         => TRUE);

    DBMS_SCHEDULER.set_attribute(name=>'event_raising_job', attribute=>'max_run_duration', value=>interval '60' second);

   DBMS_SCHEDULER.set_attribute(name => 'event_raising_job', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events );

END;

-- setup email notification
exec dbms_scheduler.add_job_email_notification( job_name=>'EVENT_RAISING_JOB',recipients=>'someuser@somedomain.com', events=>'job_all_events');

-- launch job
exec dbms_scheduler.run_job('EVENT_RAISING_JOB',false);

You should get 3 emails that this job: STARTED, SUCCEEDED, and JOB_OVER_MAX_DUR


multi-table mysql query I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project.

A User is associated with Message/Thread/Project as either the Creator or as it being 'shared' with them.

A Message is contained within a Thread (associated by message.thread_id and thread.id), and a Thread is contained within a Project (associated by thread.project_id and project_id).

I would like to create a query where given a User.id value, it will return all messages that the user has access to, as well as the Thread and Project name that that message is under, both as Creator or 'Shared'. I use a table to handle the 'shares'. The rough diagram is: http://min.us/mvpqbAU

There are more columns in each, but I left them out for simplicity.

looks like you need to use "join" syntax. Something like:

SELECT
u.name,
u.id,
m.id,
t.id,
p.id,
FROM User u
LEFT JOIN Message m ON m.owner_user_id = u.id
LEFT JOIN Thread  t ON t.owner_user_id = u.id
LEFT JOIN Project p ON p.owner_user_id = u.id
WHERE u.id = 12345

Of course, this will have to be modified to include the fields you actually want returned and whatever sorting and ordering you may want... but this should get you started. Good luck!


Multiple conditional statement checking SQL I have a table with different columns. One of them is Message. I want to check multiple patterns using like in this case.

For a start position here is a simple patterns:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|%';

For matching this pattern, I use this query:

SELECT Logid 'LogId', 'Message' Message
FROM Messages
WHERE LogId >= 1000 AND
    (m.Message like @pattern);

And it is an example of Message content:

|8=FIX.4.4|9=70|35=A|34=1|10=008|

Now I want to check a complex pattern, which is:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|% %|34=[1]|%';

It means that we need to have a message with %|35=[A,D]|% AND %|34=[1]|%. I need to match with a complex pattern with n parts which there is a space between conditions. The previous example is a condition with to statement.

So, first of all, I need to create a list of conditions by splitting pattern and check all of the conditions and make a simple AND between results. I would be glad if I can do that. The last point is that the pattern is an input of a stored procedure.

Assuming no pattern can contain a space, the following may work. First, you need a splitting function that can handle a space (also a problem for some splitting techniques), and that returns the number of rows after the split.

CREATE OR ALTER FUNCTION dbo.SplitStrings
(
   @List       varchar(max),
   @Delimiter  varchar(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
       FROM n WHERE n <= LEN(@List))
     SELECT [Value] = SUBSTRING(@List, n,
       CHARINDEX(@Delimiter, @List + @Delimiter, n) - n),
       Fragments = COUNT(*) OVER() FROM n
     WHERE n <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, n, 1) = @Delimiter
   );
GO

Now, you can pass your pattern into that function, and you get returned the set of patterns. We need to check that all patterns are found in a given string, which means the number of rows that match in the join must equal the number of patterns. This translates to something like this:

DECLARE @BaseTable TABLE(SearchColumn varchar(255));

INSERT @BaseTable(SearchColumn) VALUES
  ('|8=FIX.4.4|9=70|35=A|34=1|10=008|'),  -- will match
  ('|8=FIX.4.4|9=70|35=QA|34=1|10=008|'), -- will not match
  ('|8=FIX.4.4|9=70|35=A|34=71|10=008|'); -- will not match

DECLARE @pattern varchar(50) = '%|35=[A,D]|% %|34=[1]|%';

SELECT bt.SearchColumn
  FROM @BaseTable AS bt
  INNER JOIN dbo.SplitStrings(@pattern, ' ') AS ss
  ON bt.SearchColumn LIKE ss.[Value]
  GROUP BY bt.SearchColumn
  HAVING COUNT(bt.SearchColumn) = MAX(ss.Fragments);

If a pattern may contain spaces, you should simply use a different delimiter between your patterns, e.g. three tildes (~~~):

DECLARE @pattern varchar(50) = '%|35= [A, D]|%~~~%|34 = [1]|%';

...
  INNER JOIN dbo.SplitStrings(@pattern, '~~~') AS ss
...


Having two entities that are the same except for 1 distinct attribute In an example scenario for a database design assignment, a company provides hotel rooms (split into two entities - Hotel and Room) and apartments. So the database will contain the following entities:

Hotel (hotelNo(PK), name, address, telephone)

Room (roomNo(PK), hotelNo(PK), dailyRentalRate)

Apartment (apartmentNo(PK), address, numberOfRooms, dailyRentalRate)

In the specifications the company wants the database to contain an inspection entity which stores the following data:

Date of inspection,
The condition of the room/apartment,
The staff member who did the    inspection.
The hotel and room number    and apartment numbers depending    on
whether the inspection was on a    hotel room or apartment.

My question is would it be better to split this up into two    entities    as    shown below or to have one    entity (also shown    below) which    would have    a null value for either    the hotel    and room number    (when the    inspection is for an    apartment),    or the apartment    number (when the    inspection is for a    hotel    room).

Two separate entities:

HotelInspection (hotelInspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK))

ApartmentInspection (apartmentInspectionNo, date, Condition, staffNo(FK), apartmentNo(FK))

One entity:

Inspection (inspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK), apartmentNo(FK))

If I were you, I would do something like the following:

(tl;dr) - you should have one inspection entity - they are a hair's breadth apart and in this context, essentially one and the same thing! Modifying your schema slightly makes them one and the same.

CREATE TABLE location  -- "master" room/apartment (could be called "accomomation" or similar)
(
  location_id INTEGER PRIMARY_KEY,
  location_type VARCHAR (10) NOT NULL,
  -- CHECK accommodation_type IN ('Hotel', 'Appartment'), could also have 'Hostel', 'AirBNB', 'Tent'...
  -- MySQL doesn't support `CHECK` constraints - use a lookup table!
  location_name VARCHAR (25), -- colloquial name
  location_phone VARCHAR (20) -- either the switch or owner's mobile/cell.
);

Note:

all lower case for table names - easy to read: SQL in caps, identifers in lower with underscores.
always (depending on your RDBMS - though all the major ones comply these days), give your PRIMARY KEYs, FOREIGN KEYs and UNIQUE INDEXes (i.e. all database objects) meaningful names - makes debugging much easier.
you will notice that I put in long-ish names for my fields - this is because

a) easier to debug and,
b) code/programmes spend 99% of their time in maintenance - typing a few extra characters is a small price to pay for an easier life!

Never use keywords (e.g. DATE) as table or column names! Makes life hell with quoted identifiers &c.
always try to reduce (within reason) the number of points in your schema where NULLs can be entered. It makes logic easier and can help the optimiser! Most of my column definitions can be followed with NOT NULL below!
you might also want to check out this site for other ideas. My thoughts are only a "first pass".

CREATE TABLE hotel
(
  hotel_id INTEGER PRIMARY KEY,
  hotel_name VARCHAR (50) NOT NULL,  -- full business name
  hotel_address INTEGER, -- FK reference to address table?
);

CREATE TABLE room
(
  room_id INTEGER PRIMARY KEY, -- as opposed to room_no - room numbers in hotels can change, renovations, buidling works!
  location_id INTEGER, -- FK to location. Also `UNIQUE INDEX` to prevent dups.
  hotel_id INTEGER, -- FK to hotel
  room_no INTEGER,
  room_rate INTEGER -- FLOAT/DECIMAL/whatever
);

CREATE TABLE apartment
(
  apartment_id INTEGER PRIMARY KEY
  location_id INTEGER, -- FK to location! Also, `UNIQUE INDEX` to prevent dups!
  apartment_address INTEGER, -- FK to address table
  apartment_room_count INTEGER,
  apartment_rate INTEGER -- FLOAT, DECIMAL...
);

CREATE TABLE location_rating
(
  location_rating_id INTEGER NOT NULL PRIMARY KEY,  -- if possible give your PK a meaningful name.
  accomodation_rating_text VARCHAR (20) NOT NULL
);

INSERT INTO accomodation_rating
VALUES
(1, 'Call the police!'),
(2, 'Totally unaccptable'),
(3, 'Very poor'),
(4, 'Poor'),
(5, 'Acceptable'),
(6, 'Reasonable'),
(7, 'Good'),
(8, 'Very good'),
(9, 'Excellent'),
(10, 'Kiss the cleaners!');

CREATE TABLE inspection
(
  inspection_id INTEGER PRIMARY KEY,
  accommodation_id  INTEGER, -- FK to accomodation
  inspection_date DATE, -- more meaningful than just date - anyway, NEVER call a table or field by a KEYWORD!
  inspection_rating INTEGER, -- FK to your accomodation_rating table - NEVER allow people to write free text if possible!

  -- NOW, no need for hotelNo or apartmentNo - plus no NULLs!
);


MYSQL after insert trigger is not running Im having problems with this MYSQL trigger cant seem to figure it out what the problem is. i have tables like the following.

create table assets(

id int,
model_id int,
asset_type varchar(6)
);

create table models(
id int,
category_id int,
name varchar(20)
);

create table categories(
id int,
cat_prefix varchar(4)
);

With that i want to have the cat_prefix value to be inserted in the asset_type column for every insert on the assets table based on model_id. Of course this are simplified versions of the tables.

So i wrote an insert trigger below.

DELIMITER //
CREATE TRIGGER asset_prefix_after_insert AFTER INSERT  ON assets
FOR EACH ROW
  BEGIN
DECLARE model_id INT DEFAULT NULL ;
DECLARE category_id INT DEFAULT  NULL ;
DECLARE category_prefix VARCHAR(6);
DECLARE msg varchar(128);

SELECT NEW.model_id INTO model_id;

IF (model_id IS NOT NULL) THEN

  SELECT category_id from models
  WHERE id = model_id
  INTO category_id;

END IF;
 IF (category_id IS NOT NULL) THEN
  SELECT cat_prefix from categories
  WHERE id = category_id
  INTO category_prefix;
END IF;

IF (category_prefix IS NOT NULL) THEN
  INSERT INTO assets
  (asset_type)
  VALUES (category_prefix);
END IF;

END //

DELIMITER ;

But it does nothing on insert. What im i missing here?. Please help. Thanks.

Mostly just a few little mistakes, the biggest of which is the ambiguous declaration of category_id.

SELECT category_id from models
WHERE id = model_id
INTO category_id;

This statement is selecting value of the DECLARE'd category_id ( which is the DEFAULT, null ), into the DECLARE'd category_id.  This renders the following branches unreachable, as they will always evaluate as false.  To eliminate this ambiguity, you can alias your table.

SELECT  m.category_id
FROM    models m
WHERE   m.id = model_id
INTO    category_id;

The lesson here is to always alias your tables.  As per natural law, for every opinion there is an equal and opposite opinion, but in this case, I'm right.  Alias your tables, all the time, every time.  It drastically improves readability and serves to avoid little bugs like this one.

The next issue with the trigger is a discrepancy between what you have said ( insert a value into the asset_type column for each row ) and what you've coded ( insert a row into the assets table for each row ).

IF (category_prefix IS NOT NULL) THEN
  INSERT INTO assets
  (asset_type)
  VALUES (category_prefix);
END IF;

This statement says to insert the [ (null), (null), category_prefix ] tuple into the asset table.  In addition to likely not being the actual intent, as your trigger is an after-insert trigger, if the trigger were to actually perform this operation it would infinitely loop.  What you need to do is to set the value of the NEW column:

IF ( category_prefix IS NOT NULL )
THEN
  SET NEW.asset_type = category_prefix;
END IF;

However, MySQL will also complain about this because you can't update a NEW column in an after-insert trigger, as the write has already completed: It's already done.  To make this work, it's necessary to change the trigger from an after-insert to a before-insert.

CREATE TRIGGER asset_prefix_before_insert
  BEFORE INSERT ON assets

With those changes, you may end up with something that looks a little like the following:

DELIMITER //
CREATE TRIGGER asset_prefix_before_insert
  BEFORE INSERT ON assets
  FOR EACH ROW
BEGIN

  DECLARE model_id INT DEFAULT NULL;
  DECLARE category_id INT DEFAULT NULL;
  DECLARE category_prefix VARCHAR( 6 );

  SELECT  NEW.model_id INTO model_id;

  IF ( model_id IS NOT NULL )
  THEN
    SELECT  m.category_id
    FROM    models m
    WHERE   m.id = model_id
    INTO    category_id;
  END IF;

  IF ( category_id IS NOT NULL )
  THEN
    SELECT  c.cat_prefix
    FROM    categories c
    WHERE   c.id = category_id
    INTO    category_prefix;
  END IF;

  IF ( category_prefix IS NOT NULL )
  THEN
    SET NEW.asset_type = category_prefix;
  END IF;

END; //

INSERT  INTO categories ( id, cat_prefix )
VALUES  ( 1, 'x' ),
        ( 2, 'y' ),
        ( 3, 'z' );

INSERT  INTO models ( id, category_id )
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 );

INSERT  INTO assets ( id, model_id )
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 );

SELECT  *
FROM    assets;


Using window function to carry forward first non-null value in a partition Consider a table that records visits

create table visits (
  person varchar(10),
  ts timestamp,
  somevalue varchar(10)
)

Consider this example data (timestamp simplified as counter)

ts| person    |  somevalue
-------------------------
1 |  bob      |null
2 |  bob      |null
3 |  jim      |null
4 |  bob      |  A
5 |  bob      | null
6 |  bob      |  B
7 |  jim      |  X
8 |  jim      |  Y
9 |  jim      |  null

I'm trying to carry forward the last non-null somevalue of the person to all his future visits until that value changes (ie becomes the next non-null) value.

Expected result set looks like this:

ts|  person   | somevalue | carry-forward
-----------------------------------------------
1 |  bob      |null       |   null
2 |  bob      |null       |   null
3 |  jim      |null       |   null
4 |  bob      |  A        |    A
5 |  bob      | null      |    A
6 |  bob      |  B        |    B
7 |  jim      |  X        |    X
8 |  jim      |  Y        |    Y
9 |  jim      |  null     |    Y

My attempt looks like this:

 select *,
  first_value(somevalue) over (partition by person order by (somevalue is null), ts rows between UNBOUNDED PRECEDING AND current row  ) as carry_forward

 from visits
 order by ts

Note: the (somevalue is null) evaluates to 1 or 0 for the purposes of sorting so I can get the first non-null value in the partition.

The above doesn't give me the result I'm after.

The following query achieves the desired result:

select *, first_value(somevalue) over w as carryforward_somevalue
from (
  select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition
  from test1

) as q
window w as (partition by person, value_partition order by id);

Note the null case statement - if IGNORE_NULL was supported by postgres window functions this wouldnt be needed (as mentioned by @ypercubeᵀᴹ)


Load specific element from XML I have the following xml:

<root>
 <123 type="line">
  <aa type="item">
   <value>1</value>
  </aa>
 </123>
 <234 type="line">
  <bb type="item">
   <value>2</value>
  </bb>
 </234>
</root>

How can I from bulk loading the xml into a database table (field xmldata)
query the xml so I get all values under type="line" and then all values under type="item" so I can then get to the value attribute. I can't hard code the attribute (eg 123) because it changes all the time, but the type value will remain constant.

You should use the nodes() method of the XML data type to shred your XML.

If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you are using but that the node names are valid XML names.

declare @XML xml = '
<root>
  <A123 type="line">
    <aa type="item">
      <value>1</value>
    </aa>
  </A123>
  <B234 type="line">
    <bb type="item">
      <value>2</value>
    </bb>
  </B234>
</root>';

select T.X.value('text()[1]', 'int') as Value
from @XML.nodes('/root/*[@type = "line"]/*[@type = "item"]/value') as T(X)

A breakdown of the xPath in the nodes function:

From /root give me all nodes /root/* where the attribute type is line /root/*[@type = "line"] and from there give me all nodes /root/*[@type = "line"]* where the attribute type is item /root/*[@type = "line"]/*[@type = "item"].

Finally get the value node /root/*[@type = "line"]/*[@type = "item"]/value and extract the text node using the value() function.


Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)? I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...).

Ideally I'd like to make them searchable since right now my best bet is spiting them (a little help from cygwin!) and reading them one by one as a text file ~500mb from notepad++ (yes...i know...) - so this is very inconvenient and caveman-like approach.

I'd like to use MySQL but maybe others, have budget of up to $500 for Amazon instances when needed - maybe 32gb ram some xeon gold and 200gb hard disk on Amazon can do it? No problem to use up to 10 instances each of which doing concurrent insert/loading.

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram - if I make it to even 50,000 rows/second and then be able to query it with say phpmyadmin in normal time -  I'd be happy. Thanks!

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram

That sounds like taken from my blog posts (or at least those were my numbers and specs): https://jynus.com/dbahire/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ and https://jynus.com/dbahire/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/

As you can see my experience is based on actual tests; but not only laboratory tests like the above, I do those because they help me be ready to make sure my database backups (and recoveries) are generated correctly, as well as they can be performed reliably and fastly, handling daily both logical dumps and snapshots for the half a petabyte of data we store on our MariaDB databases: https://www.slideshare.net/jynus/backing-up-wikipedia-databases

A 50 GB database on a 32GB memory server is a very generous ratio, where 60% of the data could fit in the buffer pool. In that case, thoughput can be optimized greatly, as long as you setup your vm, os and mysql configuration for it (disabling the binary log, increasing the buffer pool and transaction log files, loosening consistency parameters during the import, etc). You will also want the original format to be optimized for easy loading, so you dont waste cpu cycles on parsing or converting the format or other changes, as well as doing it in large transactions as well as on several threads in parallel if possible.

As an example, my production has 1-2TB databases with billions of rows, which I can recover logically in 6-12 hours on a 512GB memory machine, including the many indexes.

Under the above right circumstances, with a mostly in memory database, I would be able to load remotelly in parallel a 50 GB database in around 30 minutes. Less than 1h if the storage is slow. Be careful because the tests asume dedicated resources; a cpu, memory or io limitation can create a bottleneck, leading to higher load times.