Anyway to get the trigger to work? I want to insert last insert query to the another column.For that i wish to use TRIGGERS

--
-- Triggers `sync_log`
--
DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`;
DELIMITER //
CREATE TRIGGER `tg_sync_id_insertbgnn` BEFORE INSERT ON `student_master`
FOR EACH ROW BEGIN
INSERT INTO `sync_log`(`Sync_query`) SELECT * FROM student_master WHERE `SM_NO` = NEW.`SM_NO`;
END
DELIMITER ;

This doesn't seems to work.Any idea why ?

You need three changes

CHANGE #1

Add // after end

CHANGE #2

Just insert NEW.SM_NO directly into sync_log

CHANGE #3

Make it an AFTER INSERT trigger, to be sure the new SM_MO lands in student_master

--
-- Triggers `sync_log`
--
DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`;
DELIMITER //
CREATE TRIGGER `tg_sync_id_insertbgnn` AFTER INSERT ON `student_master`
FOR EACH ROW BEGIN
INSERT INTO `sync_log`(`Sync_query`) VALUES (NEW.`SM_NO`);
END //
DELIMITER ;

Give it a Try !!!


Express union which is dependent on other results I would like to make a union of two tables that are actually in a hierarchical constellation.

How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship.

First I do a selection on A and then I want to only make a union with these entries in B that would be in a join with selection on A.

I would like to write it the way that a database would evaluate it.

Is there such a thing as a conditional union?

If I understood the question well, your task can be solved by using recursive queries. Oracle (see  and SQLServer (2005 and higher) both support it. Surely, different RDMS vendors use a slightly different syntax.


How to immediately terminate a SPID with a very long rollback time estimated? I have a script to restore a database from a backup. Prior to the restore command, it has:

set single_user with rollback immediate

The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.

How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?

Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.

In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:

EXEC master.sys.sp_helpfile;

Then the restore command is like this:

RESTORE DATABASE [new_name_here]
  FROM DISK = 'C:\path\file.bak'
  WITH MOVE Logical_Data_File_Name TO 'C:\some other path\some other name.mdf',
       MOVE Logical_Log__File_Name TO 'C:\some other path\some other name.ldf';


How to persistently set MySQL (Linux mysql command) session time zone to UTC? I'm using MySQL on Linux (CentOS, recent release).  I want the MySQL system variable time_zone to reflect UTC.

When I start a MySQL session (using the command-line tool mysql) and check the time zone setting, I see this:

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

I know (see MySQL Set UTC time as default timestamp) that I can configure the server to have UTC as its default timezone.  What I want is to set up a client session to have UTC as the default.

The page above says I can set a MySQL system variable in my SQL syntax, like this:

mysql> set @session.time_zone="+0:00";

This leads to having the session time zone to be the local (SYSTEM) time zone:

mysql> show variables like '%zone%' ;
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

What I'd like to do is to set the time zone as a kind of preference for a user, like a setting in .my.cnf.

Problem is, the MySQL 'mysql' command options don't list a 'time zone' option for .my.cnf, and (of course I had to try it anyway) putting

 time_zone="+0:00"

in my $HOME/.my.cnf file leads to the message

 mysql: unknown variable 'time_zone=+0.00'

I tried setting the TZ environment variable, and that has no impact.

As the StackExchange reference above says, the only effective way I've found is through issuing the SQL command:

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> set @session.time_zone="+0:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| +0:00              |
+--------------------+
1 row in set (0.00 sec)

So...

My question then is, is there some other mechanism for setting MySQL session variables?  Or some other way to set the session time zone?

Use default_time_zone=UTC in my.cnf.

Ref: https://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file


Performance improvement on SQL query joining table on itself I'm running this query on a table with approximately 20MM records in it.  It's a very slow query, and I'm wondering if there's a better way to accomplish this that might run faster ?

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.[feed_id] < latest.[feed_id]
    and previous.[condition_1] in (
        '1'
        ,'2'
        )
    and latest.[condition_1] = '3'
    and latest.[feed_id] = 500

You are doing the entire delete in one transaction that will possibly bloat your transaction log and it will crawl like a sloth.

There is a much better way of doing a delete - break it into chunks or you can do deletes using Careful Batching.

Also, its always a good habit to refer objects with their schema.


Search efficiency of datetime vs date + indexed smallint (PostgreSQL) Context

We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 years of data. Besides analyses conducted for research purposes, for which speed isn't of primary importance, there will also be an online dashboard that will display the latest forecast(s) on a map.

The table could have the following columns:
datetime, metadata_id, raster_data

I foresee two main use cases: (1) either getting the most recent forecast, or (2) selecting a single past forecast. The queries could look something like this:

SELECT datetime, metadata_id, raster_data
FROM myTable
ORDER BY datetime DESC
LIMIT 1;
SELECT datetime, metadata_id, raster_data
FROM myTable
WHERE datetime= '2015-10-12 12:00:00'::timestamp;

Broader Context

The above situation actually applies to 6 different tables that only differ in that their data comes from different sources, and different physical variables are represented. I am therefore considering a single table with the following columns:
datetime, dataSource_physVar, metadata_id, raster_data

The different sources do have different raster extents, so I am consider using child tables.

Question 1

Given the limited use cases, is it better to use a single datetime field, or rather a date field with an indexed smallint field for the hour?

Question 2

Given the quantity of data (365*3*20 years = 22k lines), is it even worth worrying about efficiency?

I'm putting down my recommendations for your two questions and additional considerations for your index.

1

I'd keep the timestamp as one field, breaking it into two separate fields for date and a smallint for the hour will reduce the space by 2 bytes per row (aka 43 MB with your 22k row estimation) having to compare two fields for date /time doesn't seem worth the small space gain.

2

22k rows may not be many in comparison to some databases, if you're picking out individual occurrences though then you still want it as fast as possible. finding a specific value from a heap requires a full table scan which can take time, if it's indexed then you do an index seek which means you can find records a lot faster.

Additional

If the data is broken up to different rows by location (eg, '2015/10/13 00:00', 'London', 'sunny') then I'd advise a composite key between the timestamp and location

I don't believe that is the case, it sounds like the data will be stored in one long line, so (datetime timestamp, London (data), Paris(data)), for this just index the timestamp field, as an index would suffice

Hope that helps


Install Express/Standard side-by-side I've reviewed these two existing questions:

Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install?
Installing SQL Server Express in parallel of Standard

...and I believe mine to not be a duplicate, as the details of my scenario differ slightly (and in an important way). If I'm mistaken please let me know and I'll try to edit my question appropriately.

Question:

I already have two named instances of SQL 2014 Standard Edition installed, but no default instance. I wish my default instance to be a SQL 2014 Express Edition installation (MSSQLSERVER).

But I see that the Express installer wants to put its files in C:\Program Files\Microsoft SQL Server\, where files and folders already exist from the earlier Standard Edition named instance installations (e.g. .\110\*, .\120\*, .\Client SDK\*, etc.).

If I proceed with the Express installation and accept the default instance root directory (again, which already exists under Standard), will I cause any problems for the currently running Standard Edition named instances?

Or instead should I indicate a new instance root directory to be created (e.g. C:\Program Files\Microsoft SQL Server Express\) for the new Express installation?

I'd prefer the former if possible, in order to keep everything consolidated, but I also don't want to cause trouble for myself if I can avoid it.

Is there a really good reason you do want to use a shared root directory? From my experience on SQL 2000 and 2005 it sometimes wasn't the best option.

Having them separate would make it less likely that a change/patch in one might accidentally affect the other or edge cases where NTFS permissions/corruption or something aggravates another issue causing more troubleshooting or affecting both.  If there's not a strong reason to keep them shared, I typically separate due to a tiny amount of bad experiences.


Generic Trigger on MYSQL to replicate data on other table I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed.

CREATE TABLE `competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `credit_hour_equivalent` double NOT NULL,
  `description` text,
  `book_name` varchar(255) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  `statement_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `competencies_domain_id_index` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;

CREATE TABLE `history_competencies` (
  `id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `credit_hour_equivalent` double DEFAULT NULL,
  `description` text,
  `book_name` varchar(255) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  `statement_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `author_action` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For every INSERT on competencies I want to copy the row into history_competencies.

So I created the following trigger

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW.`id`,
               NEW.`title`,
               NEW.`type`,
               NEW.`credit_hour_equivalent`,
               NEW.`description`,
               NEW.`book_name`,
               NEW.`domain_id`,
               NEW.`created_at`,
               NEW.`updated_at`,
               NEW.`slug`,
               NEW.`video_id`,
               NEW.`statement_id`,
               NEW.`author_id`,
               'CREATE');

My question aims to find a better way to write the trigger so I don't have to DROP and CREATE the trigger every time that I change the competencies table or reuse for other tables just changing the table names.

I was looking for something like

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW.*, 'CREATE');

Or

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW, 'CREATE');

But it didn't work.

Do you know if this is even possible?

Thank you!

One idea would be to write:

CREATE TRIGGER `after_insert_log_competency`
AFTER INSERT ON `competencies`
FOR EACH ROW
   INSERT INTO history_competencies SELECT *, 'CREATE' FROM competencies WHERE id=NEW.id;

That doesn't work well for deletions, as the row wouldn't exist anymore. Also, you are doing an extra eq_ref for each write.

But be careful, because if you ALTER competencies without altering history_competencies, all your INSERTS, etc. on competencies will fail due to the trigger failing.

If you do very frequently ALTERs, and all you do is inserting and selecting those new fields, you could serialize them to a single BLOB. That is a very denormal form, with a lot of drawbacks (no constraints, no indexes, difficult to update a single field), but it may be interesting in some cases.


What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012? We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers.  These machines consist of:

Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for a total of 48 cores.
512GB DDR3 1033Mhz RAM
10GB Ethernet SAN connected LUNs for MDFs, Logs, TempDB, Backups, etc.

We are looking at upgrading to SQL Server 2008 R2 Enterprise or SQL Server 2012 Enterprise.

I understand all the features that Microsoft presents in their standard comparison guides, however it seems to me there is likely a lot of other smaller features that are not present in these official guides that might make a DBA's life easier in SQL 2012.

Can anyone suggest killer features that would sway towards SQL Server 2012 over 2008 R2?

As a result of Microsoft changing their licensing scheme from socket-based to core-based, we are looking at a huge expense to license all 48 cores.  To combat this unexpected rise in price, we are considering swapping these CPUs out for faster CPUs with a lower core count.  We are considering using 4 x Opteron 6204s, which are quad-core and run at 3.2GHz.  Our current systems are typically running at around 25 to 30% utilization; does anyone have experience dropping from 48 cores at 2.1Ghz to 16 cores at 3.2Ghz (or similar), and can we expect a perceivable decrease in speed?  I know that's a loaded question, and am expecting a fair amount of flack - however it just helps to think this through by getting the help of other more experienced DBAs.

Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition:

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

(I could list them here but I don't think duplication makes sense. And there are some other good answers on that page as well.)

The only difference would be you could add to the list the things that have been added from 2008 on: date/time data types, data compression (Enterprise), backup compression (Standard+), some syntax improvements (multi-row values, inline declare + assign, +=/-=, etc). These are things you would get, though, whether you went 2005 -> 2008 R2 or 2005 -> 2012. Guess I knee-jerked listing those because you won't get them if you stay where you are.

In addition, moving to 2012 now instead of 2008 R2 buys you a longer lease toward end-of-life / end-of-mainstream support. However if you are using CAL licensing you might consider 2008 R2, because this option isn't available anymore in 2012 Enterprise. And core licensing can be more expensive if your CPUs have more than 4 cores per socket. They recently updated the "How to Buy" part of the site; you should stay on top of that and keep in good touch with a licensing rep. They're not perfect (and we can have a different discussion about this) but they're the only ones who can give you plausible deniability should anything they sell you end up putting you out of compliance, as long as you haven't lied to them. :-)

If you're going to take advantage of CPU-intensive things like compression, then having faster, fewer cores may be tolerable overall especially since it will make your SQL licensing (quite likely the most expensive part of your solution) that much cheaper. More, slower cores will be more expensive in terms of licensing, and spreading the work over more cores won't necessarily do any better for an OLTP-type workload - since many tasks cannot be parallel anyway. Also, AMD cores are subject to a core factor, which means the pricing is adjusted. This means you pay less per AMD core than per Intel core, but this is probably reflected in the performance, as well (at least if my conversations with Glenn Berry are any indication). No discount like this is in effect for 2008 R2 (at least AFAIK) since you're paying by the socket. Personally, I would rather spend the money on the better cores, but at 48x + 1.33x for licensing I'm not sure I could sell the bean-counters on it. :-)

But there's no way anyone here can tell you which way to go. If you can get some spec hardware in both configurations that would be ideal - you really should test your full workload cycle to see if fewer, faster processors are better for your workload - or at least close enough that they are justified by the licensing savings. On modern hardware I'd expect that to be a better option but I am not going to sign anything that guarantees it. :-)


structure of query does not match function result type I want to create a basic function which takes an input and throws a table with multiple columns and rows as an output . I have written the following query for the same :

create or replace function proc_name_1 (store text)
returns table (question text , resp numeric , gendr text , resp_count integer)
as $$
begin
return query
select question_type , response, gender, sum(response_count)
from fashtagcompute.response_count
where store_id = store
group by question_type , response, gender ;
end ;
$$
language plpgsql ;

CREATE FUNCTION

The function is succesfully created. However if I try and execute  it , I get the following error

select * from proc_name_1('ab1f47ff-5c96-46fb-b975-81bf92c01b63')

ERROR: structure of query does not match function result type
DETAIL: Returned type character varying(80) does not match expected type text in column 1.
CONTEXT: PL/pgSQL function proc_name_1(text) line 3 at RETURN QUERY

Please help.
Thanks

It seems that question_type is defined as varchar(80) but you as defined question as text.

Simply cast it as text:

select question_type::text


User switching accounts, can't log into MS SQL Server anymore First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. (Not a real profile, just used as an example).

We have an application that uses the SQL server authentication to restrict what they can access in the application. We use AD groups to control what they can see to make user management easier. We found out in the past we can't rename a user as they can't log into this account. We had to delete the old account and completely create a new different account in order to have them log in.

Now we have a similar, but different problem. The old "CEO" left and we updated the CEO profile with the new CEO's information, now that account can't log into the application. We have deleted the groups they we have for the application and re-added them. They still can't log in. Based on this article, when you rename a user, the meta data doesn't match so you need to do a little ALTER USER script to fix the user. The suggestion to prevent this in the future would be to use groups much like we are doing now.

Is there a cache of users that might be causing problems, even if we don't authorize by users? How would I flush that cache if that is the case? Is this a problem with SQL Server 2008 as well? We are upgrading to that soon. What else can I look at?

Here is how the user is set up: CREATE LOGIN [DOMAIN\cgUSERS] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

Here is the exact error from the application:

  You don't have proper security rights to read the Cartegraph system tables. Access the the system tables are required to run the application. Please contact your System Administrator.

Another site mentions rebooting the service and/or server. I think I might try that once to see if that fixes our issue.

So after some more researching, we decided to reboot the server after-hours. This morning the person is able to log in. Looks like it is some sort of user caching that was causing the problems. We are going to do our other user moves and see if it works.


Oracle 10g: Optimizing an update query that uses a subquery I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.

  UPDATE TBL_REPORTING REP
  SET    (REP.ISSUER_ID, REP.ISSUER_NAME
         ) = ( SELECT DISTINCT TMP.ISSUER_ID
                       ,TMP.ISSUER_NAME
                FROM   TMP_REPORTING TMP
                WHERE  TMP.CMD_KEY = REP.CMD_KEY
                AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            )
  WHERE  REP.CMD_KEY = v_cmd_key;

TBL_REPORTING has 332124 rows in total.
TMP_REPORTING has 55589 rows in total.

I have tried rewriting the subquery using the WITH clause, but I can't get it working when there's 2 columns in the SET statement. I was reading that perhaps I could use the MERGE statement, but I haven't been able to get a working query yet. Ideas?

Update:

I managed to rewrite the query using the MERGE statement, but I still need to check if the results are the same:

MERGE INTO TBL_REPORTING REP
USING (SELECT DISTINCT TMP.CMD_KEY, TMP.REPORTING_KEY, TMP.ISSUER_ID, TMP.ISSUER_NAME
            FROM TMP_REPORTING TMP
            WHERE TMP.CMD_KEY = v_cmd_key) comp
            ON (REP.CMD_KEY = comp.CMD_KEY AND REP.REPORTING_KEY = comp.REPORTING_KEY)
            WHEN MATCHED THEN UPDATE SET ISSUER_ID = comp.ISSUER_ID, ISSUER_NAME = comp.ISSUER_NAME;

Update 2: Results are not the same. The UPDATE statement updates 40 more rows than the MERGE statement.

Execution plan for update:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 644,432  Bytes: 322,065  Cardinality: 7,157
    6 UPDATE TBL_REPORTING
        2 TABLE ACCESS BY INDEX ROWID TABLE TBL_REPORTING Cost: 302  Bytes: 322,065  Cardinality: 7,157
            1 INDEX RANGE SCAN INDEX IDX_TBL_REPORTING_R4 Cost: 8  Cardinality: 7,157
        5 SORT UNIQUE  Cost: 44  Bytes: 45  Cardinality: 1
            4 TABLE ACCESS BY INDEX ROWID TABLE TMP_REPORTING Cost: 43  Bytes: 45  Cardinality: 1
                3 INDEX RANGE SCAN INDEX TMP_REPORTING_13R2 Cost: 10  Cardinality: 6,949

Table TBL_REPORTING has indices:
  PK_TBL_REPORTING on REPORTING_KEY and
  IDX_TBL_REPORTING_R4 on CMD_KEY.

Table TMP_REPORTING has:
  an index on CMD_KEY,
  but not on REPORTING_KEY.

Try the below to update unique issuer id and issuer name instead of using Distinct by using Group by together with the index on Issue Id and Issuer Name in Temporary reporting table

Kindly refer the below link
Fast Query To Get TOP-N Distinct Rows From A Huge Data Table

UPDATE TBL_REPORTING REP
SET(REP.ISSUER_ID, REP.ISSUER_NAME
) = ( SELECT TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
            FROM   TMP_REPORTING TMP
            WHERE  TMP.CMD_KEY = REP.CMD_KEY
            AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            GROUP BY TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
        )
WHERE  REP.CMD_KEY = v_cmd_key;


what would be the right permission to allow everything else but overwriting or creating a database? I have a test environment where the AD group mycompany\developers is currently sysadmin.

Recently I have had some issues, specially regarding to people restoring databases. My concerns are:

sometimes the database is involved in replication
the permissions are overwritten - the correct way is to save the
current permissions before the restore and re-apply them after the restore.
Have you checked the current available disk space in all drivers,
specially those used in this restore?
there might be other people using or working on that specific
    database, and restoring over it without communication can cause
    someone's else work being lost
in the dev environment the databases can be in simple recovery mode.
Have you shrunk the log and changed the database to simple recovery
mode.
in this particular case, the full backup for every user database is
going to run automatically every night. but anyways, have you
checked for scheduled backups?
have you deleted from the server the backup that you used to do the
restore?
the folders and drives or data and logs are different in live and
test. are you sure you moved the files to the right places?
Have you had a look at orphaned users and logins?

I particularly have concerns when developers create new databases, even in the dev environment. why?

because they start by creating the database in dev and then just ask me to copy it to live.
have you done any capacity planning for this database? how big it should be and how much it would grow in a month?

have you isolated or considered different filegroups for demainding
objects\indexes?
Have you checked the current available disk space in all drivers,
    specially those used in this DB creation?
how big is the transaction log and why? Initially I like to set it
1.5 times the biggest clustered index.
what are the autogrowth settings?
what are the current permissions?

I have realised some people are not so easy to deal with, I rather reduce their power over that server.

I don't want to do their jobs, but I would like to restrict them from creating new databases, or overwriting existing ones, either by restoring or by attaching files, etc.

They still should be able to create and run jobs, create\alter any object withing user databases, create logins, grant permissions etc...

Basically the question is:

How to limit the power of a current sysadmin?
I thought about not granting sysadmin but a set of server permissions (excluding those to create/alter databases) that would allow them to do everything else.

what would that set of server permissions be?

TL;DR

In order to restrict the DEVs from doing anything "wrong" you have to at least remove the sysadmin SQL Server role.

Let's start

You could assign your DEVs the db_owner database role for each individual database. However that role will permit a user to perform a BACKUP DATABASE ... or BACKUP LOG ... which you mentioned you don't want them to be able to perform.

Solutions

Beginning with SQL Server 2014 you can then restrict a SQL Server Login with the db_owner role further removing individual privileges/permissions. One example is to remove the backup privilege on the database and transaction log level by issuing the following commands on the database:

USE <database_name>
GO
DENY BACKUP DATABASE TO <database_principal>
GO
DENY BACKUP LOG TO <database_principal>

Reference: DENY Database Permissions (Transact-SQL) (Microsoft Docs)

There are three levels of permissions.

GRANT  : You are granted/granting a specific permission
REVOKE : A previously granted/denied permission is revoked (removed) again
DENY   : You are denied/denying a specific permission

Alternative

Now because DENY has precedence over GRANT you could use the alternative solution of creating a new database role and adding the <database_principals> to that role:

USE <database_name>
GO
CREATE ROLE [deny_backup]
GO
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
EXEC sp_addrolemember N'deny_backup', N'<database_principal>'
GO

Reference: Restrict Backup permission to user (Social MSDN)

Step by Step Guide

Because some people are sceptical about statements I have made in this post, I am adding the individual steps to show you how you can indeed DENY permissions from a SQL Login even though that login has the db_owner role.

Creating and Mapping SQL Login

Let's create a SQL Login and assign it the db_owner database role:

Deny Backup Database permissions

Now we switch to a higher privileged account like sa or a Windows Login (which would be you, the DBA asking this question) that has the sysadmin SQL Server role and DENY the right to backup database from the database principal hot2use:

In a perfect world you would also need to submit a DENY BACKUP LOG TO hot2use too.
Log in to server with hot2use and back up database

Seeing as hot2use has the db_owner database role for the Test database, we log in with hot2use...

...and try to back up the database:

Well that doesn't work and is what we expected. This is because the SQL Server login hot2use no longer has the privilege to back up the database even though the login still has the database role db_owner:
SQL Login wants to hack permissions

Seeing as the SQL Server login has the db_owner role and according to some people he can assign himself the permissions back, let's try that out:

(The test was conducted in a fresh database SQL Server Query window)
Summary

If you DENY permissions with a higher privileged account, then these privileges can not be GRANTed back from the lesser privileged account, even thought the SQL Login (e.g. hot2use) should have these privileges from the database role db_owner. This is something that changed in SQL Server 2014 (yes, the tests were conducted on a SQL Server 2014 instance).

Additional ressources

You can further restrict permissions according to the official overview which can be found here:

Permissions (Database Engine)

Further recommended reading:

Getting Started with Database Engine Permissions

Wait, restores...

Oh yes, we only denied the BACKUP DATABASE privilege didn't we? That has nothing to do with RESTORE DATABASE, or does it? Let's find out. I'll create a database backup with a higher privileged account/login:

Seems to work. Let's switch back to the hot2use SQL Server Query window and restore the backup:

As you can see, it isn't going to work, because the BACKUP DATABASE privilege also restricts the restore capability of that SQL Server login.

No backup privilege = No restore privilege.

How about DENY BACKUP DATABASE FROM SYSADMIN?

Well, since you asked. Let's try it out. I'll leave out the screen shots that show how I revoked the database role.

I assigned the SQL Login the sysadmin SQL Server role with:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [hot2use]
GO

...and then removed the permission to backup the database with the above mentioned DENY BACKUP DATABASE.. commands and then performed a database backup with:

backup database Test to disk = 'C:\temp\Test_Full_Backup_20171121.bak'

... which resulted in:

So you can't DENY permissions from a SQL Login with the sysadmin Server role, but you can DENY permissions if the SQL Login does not have the 'sysadmin' database role.

Minimal permissions required to backup / restore a database

If I assign the SQL Server login absolutely no SQL Server roles and assign only the db_backupoperator database role on a given database, then I can backup and restore the database. This is something that others believe the SQL login is unable to perform. The higher privileged account created the backup, so the lower privileged account (non-sysadmin) shouldn't be able to do a backup/restore.

After resetting all permissions of the SQL Server login hot2use to public on the server and db_backupoperator on the database level, I am presented with the following enumerated permissions:

According to others I shouldn't be able to perform a backup because I am missing a SQL Server role.

So I can in fact backup a database if I have the db_backupoperator role.

And again, if I DENY BACKUP DATABASE to the SQL Server login (on a SQL Server 2014 instance or greater), then I am no longer capable of backing up my database, even though I still have the db_backupoperator role on the database level:

If the SQL Login hot2use tries to REVOKE the previously denied permission then that will fail, with a similar message that I previously showed above.

Summary

Determing which permissions/privileges/roles you want to assign to your DEVs is going to be a tiring fight/issue, because they won't want to give up the  sysadmin SQL Server role. You will have to find out which SQL Server Roles and which Database Roles they will exactly require to do their job. The worst-case is that everything will stay the same. The best case is that you will be able to restrict the DEVs in their permissions/privileges.

You will at least have to remove the sysadmin SQL Server role from your DEVs in order to further restrict them from being able to do everything they want. But once that SQL Server role has been removed, you can assign them for example the db_owner role and DENY BACKUP DATABASE ... to disallow them from backing up the database or restoring it.

The developers can still do anything they want inside the database (includig a DROP DATABASE), but they cannot perform a backup or a restore. See the following enumerated permissions for the SQL Server login hot2use with the database role db_owner but having both DENY BACKUP DATABASE ... and DENY BACKUP LOG ...:

SQL Server Login with db_owner role but without backup permissions


What permissions do I need if database a contains a stored procedure that inserts into a table in database b (same machine)? I have a stored procedure in database a that contains dynamic sql to perform an insert into a table in database b.  What permissions does the executing user need on database b?

if the procedure uses execute as owner then the owner of the proc needs rights to database b and the table in database b.
if the procedure does not use execute as owner then the calling user needs rights to database b and the table in database b.
if cross-database ownership is on, then "it depends"
here is a link that speaks to options there:
Cross-Database Ownership Chaining


Group By - Incorrect syntax near 'mycolumn' I'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, '')))  Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);

Error message:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'Address4'

I can't see what is wrong - this works:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);

What is the syntax error?

Figured it out shortly after I posted.  I need to remove the alias from the GROUP BY clause.

Solution:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))),
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);


Mysql : foreign key relative to multiple tables I'm trying to improve the database integrity adding some foreign key (it's the first time i use them).

I have some tables relative to different type of devices (different tables because the "devices" are completely different from each other), a table groups and a relation-table group_device (the same device can be in more groups).

devices_typeA

id is primary key

id | column1 | column2 |
------------------------
1  | aaa     | bbbb    |
2  | aaa     | bbbb    |

devices_typeB

id is primary key

id | column1 | column2 | column2 | columnN |
--------------------------------------------
1  | aaa     | bbbb    | cccc    | dddd    |
2  | aaa     | bbbb    | cccc    | dddd    |

devices_typeC

id is primary key

id | column1 |
--------------
1  | aaa     |
2  | aaa     |

groups

id is primary key

id | name    |
--------------
1  | group 1 |
2  | group 2 |
3  | group n |

group_device

id | type_device | id_device | id_group |
-----------------------------------------
1  | typeA       | 1         | 1        |
2  | typeA       | 1         | 2        |
3  | typeB       | 1         | 1        |
4  | typeC       | 2         | 3        |

Now i would like to add some foreign key to the tables group_device (in such a way that when a device is deleted, is deleted also in the table group_device [with the ON DELETE CASCADE action]). My problem is the id_device column, that is relative to multiple tables (devices_typeA.id, devices_typeB.id , devices_typeC.id).

FOREIGN KEYs have severe limitations.  I think you have exceeded them.

Complex data structures cannot depend on FKs for integrity, your code is required.

Suggest that you build Stored Routines and/or subroutines in your application language, and encapsulate the integrity constraints there.  And build a clean API into the routines to make it easy for the clients to achieve their goals while the routine achieves the integrity goals.


MySQL optimizing - use ram instead of CPU? I am on a dedicated server with Intel Xeon D D-1520 4/8t 2,2 / 2,6 GHz, 32 GB of RAM DDR4 ECC 2133 MHz, and 2 x480GB of SSD SOFT. The problem I am encountering is that MySQL eats up all of my CPU power and slows down everything else. My server pilot says my total CPU usage is 92% right now, and these are the active processes.

As you can see, I have a lot of RAM left to be used, but nearly any CPU. This is my MySQL config my.cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

my mysql_innodb.cnf only has this entry

[mysqld]
innodb_file_per_table = 1

Now, is there anything I can do to optimize my situation?

I am completely new to managing my own server, but I figured I have to start sometime somewhere somehow. So I would sincerely appreciate any recommendation or advice.

Thanks!

Verify that you tables are using the InnoDB engine and not the MyISAM or ARIA engine. Concurrent writes on non InnoDB tables can cause huge CPU spikes from attempts to get full table locks.


Page setup and print layout in SSRS Report I'm creating a SSRS report using BID 2008 and have a couple of questions:

Why is the page layout on the report different from print set up? For example I have set the page layout of the report in design mode to Landscape but when it is printed, it is portrait and the report is broken in many pages, the way that the table columns are broken makes the report not usable. How can we ensure what we see on the screen is the same as what will be printed (aligned with what was designed)? I've spent a considerable amount of time to do that, but have not had much success :(
My report length is relatively long, consisting of 3 pages (in design mode). I want to have multiple layouts in the report. For example page 1, 2 and 3 landscape and page 4 portrait. There is no sub report in the report. How can this be achieved?

The page setup in the print options does not control your report parameters. You will need to use the Height/Width property of the report inside BIDS. Orientation in SSRS is controlled by setting these values. We have a report at our office that is similar to what you describe and there are two ways of (semi) achieving what you want to get done.

The first is to use a separate report for each page that you want. This gives you complete control over all elements of each page such as height/width etc but may be more work for you to do and possibly more maintenance in the long run. It does however mean that should you change elements of the report later you only have to retest the bits you have changed, not the entire report.

The second one is to you expressions to force page breaks. This will work for reports that need the same orientation (as they inherit the properties set on the report container) but will not work for a report that has a different orientation. A link to an example of page breaks is below:

http://msdn.microsoft.com/en-us/library/ms157328.aspx#AppearanceofReportData

I hope this makes sense and that it helps you in what you want to achieve.


Restrictions on user-defined queries I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database.  I am concerned about users entering malicious queries *.  Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

* Ignoring denial of service. My concern is primarily loss of data

I am concerned about users entering malicious queries. Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

That is actually two separate, though related, questions: one about "security", and one about "malicious". While security violations are malicious, not all malicious queries are security violations.
You can lock down read and write access to various tables through proper use of permissions and Views. However, I don't think you can prevent them from doing things such as issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; and then using XLOCK or TABLOCK table hints. Or combine that with issuing BEGIN TRAN; and then select from a table using those query hints, and then issue a WAITFOR DELAY '10:00:00.000'; -- 10 hour delay, hee hee.
Read-only access to user databases doesn't prohibit temporary objects, right? So they can still create temporary tables and table variables? If so, someone could intentionally, or "accidentally" fill up tempdb **, or at least degrade performance by adding a lot of contention to tempdb and its log file.
Lots of CURSORs will cause either more locking and contention (i.e. blocking) if dynamic, or more tempdb usage if STATIC.
<sarcasm>And I'm sure all of these users have been trained in both T-SQL and your data-model, hence won't be executing horribly inefficient queries that will fill the Buffer Pool more than necessary, thereby reducing Page Life Expectancy and degrading performance of the entire application.</sarcasm>
This is what a Read-Only Replica is for, or a secondary system populated via log-shipping, etc. Don't give direct, ad hoc query access to your OLTP system to anyone who can't be fired for misuse (i.e. employee or subcontractor).
** @MartinSmith noted that one can just execute the following to create a 10 billion character string:
SELECT DATALENGTH(REPLICATE(REPLICATE(CAST('X' AS VARCHAR(MAX)),100000),100000));

That amounts to 9.31 GB of data space needed in tempdb (it shows up when using the sys.dm_db_task_space_usage and sys.dm_db_session_space_usage DMVs). OR, just add one little letter to make the VARCHAR(MAX) an NVARCHAR(MAX) instead, and you will have yourself an 18.63 GB string ;-). Woo hoo!

Since the question was clarified to focus mostly on data loss and not "denial of service", I can think of two scenarios to look out for:

Linked Servers: If you have any Linked Servers, make sure that the local, read-only Login will not connect to the remote server using a mapped Login/password for a read-write account.
Or you can at least try to hide Linked Servers from the read-only Login. Please see the "Permissions" section of the MSDN page for sys.servers for details.

Elevated permissions due to Ownership-Chaining, EXECUTE AS, and code signing: Do a thorough review of all Stored Procedures that this read-only Login might have EXECUTE rights to. It is really easy to bypass direct object permissions via Ownership-Chaining, EXECUTE AS, and code-signing. If you are using any "Ole Automation Procedures" or SQLCLR objects, those can potentially reach outside of the database and so while you might have CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, etc locked down, there can be Stored Procedures that write to the file system, registry, etc that won't be prohibited by those other restrictions. You need to make sure that the entire system is read-only, not just SQL Server.

P.S. While this is not a security issue, on a more practical level relating to giving "users" the ability to write ad hoc queries: how will these queries be used, and what is your level of obligation to notify the "users" of changes to the data model? Giving people ad hoc query ability is not the same thing as providing an API. The data model will inevitably change over time. These changes will possibly break queries that the "users" had been executing successfully (even if painfully) in the past. Will your client be upset with you when their users complain that there queries broke (and yes, they will complain because they will save queries in a file or wiki page and build business-processes to run them on a regular basis)?


Doubt in Log Shipping Currently, I have setup log shipping from production server to DR server. Now, the doubt is that whenever my production server goes down or lets say data center crashes, I will switch it to DR site. On DR, I will change the mode of database from "Restoring/No Recovery" to "Recovery" mode.
But, now the problem is that whenever my data center recovers lets say it recovers in 2 hours, then I want to switch again to my production server. So, when it gets switched to PROD server then how can I change database mode to "Restoring" state on DR site to able to start log shipping again?

This article covers the topic: https://msdn.microsoft.com/en-us/library/ms178117.aspx

Basically, you have to configure log shipping from the former secondary (now primary) server to the former primary (now secondary) server.


how do I connect to mysql database from visual studio without requiring SSL connection? I have established a link between Mysql database and visual studio on my windows 10 machine.Unfortunately my application which is a desktop app can't open a connection to the DB. I keep getting this error whenever I try connecting to the database:
MySql.Data.MySqlClient.MySqlException: 'The host localhost does not support SSL connections.'

This is my connection:

***MySqlConnection conn = new MySqlConnection("user id=root;server=localhost;persistsecurityinfo=True;database=edutechsys");
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM TblLogin WHERE UserName = '" + TxtUserName.Text.Trim() + "' and Password = '" + TxtPassword.Text.Trim() + "'");
            conn.Open();
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter();
            **

How do I correct the error.

You might want to explicitly add this to the connection string - "SslMode=none"
Your connection should look like this:

  MySqlConnection conn = new MySqlConnection("user id=root;server=localhost;persistsecurityinfo=True;database=edutechsys"); SslMode=none; MySqlCommand cmd = new MySqlCommand("SELECT * FROM
  TblLogin WHERE UserName = '" + TxtUserName.Text.Trim() + "' and
  Password = '" + TxtPassword.Text.Trim() + "'"); conn.Open();
  cmd.ExecuteNonQuery(); DataTable dt = new DataTable();
  MySqlDataAdapter da = new MySqlDataAdapter();


How to export all tables with user in Oracle 10g Enterprise edition? I want to take backup of all my tables and the user too in Oracle 10g Enterprise edition and then I want to import it to some other computer. The user is not system I have created another user and all my tables are in that user.

You can use Oracle Datapump to perform export-import in schema(user) level.

Here are some examples.

expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

The above command exports SCOTT schema in a dump file called SCOTT.dmp in a directory specified using DIRECTORY parameter, and writes it's log in expdpSCOTT.log file.

You can copy this dump file to another host and use Datapump Import to import it as shown below.

impdp scott/tigerschemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

References:
Data Pump Export
Data Pump Import
CREATE DIRECTORY


Doing a select based on a count(), a date range, and string match I'm going to do my best here.

I Have a list of citations that were written and a and some information about those citations. I need to select the citations that have been voided in a given date range and where the person who wrote it also wrote over 5 citation in that same date range.

I want a result that shows all the citations that person 'x' wrote
citation 1 ... info,
citation 2 ... info,
citation 3 ... info,
citation 4 ... info,
citation 5 ... info,... etc

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorUserId

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum AND (ds.StatusName LIKE 'void%')

WHERE DateModified BETWEEN @BeginDate AND @EndDate
--DateModified is when the document was voided--

GROUP BY d.CreatorUserId
HAVING COUNT(d.CreatorUserId) >= @MinVoids

This gives me the right guids but not all of them. I cannot figure out for the life of me why it's not grabbing all of them. (Should be 16 and I only get 11). I have a feeling that COUNT isn't working how I want it to.

And just because im paranoid about sounding ignorant around SQL gurus, I just started learning SQL about two weeks ago.

SOLUTION
There was some information that I left out that could have helped but the answer still helped a lot for getting it solved.

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorFirstName + ' ' + d.CreatorLastName as name
    ,ds.StatusName

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND (ds.StatusName LIKE 'void%')
    AND ((ds.StatusNum BETWEEN 1 AND 20) OR (ds.StatusNum BETWEEN 100 AND 120))
    AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
            JOIN [ARK].[dbo].[DocStatus] AS dst
            ON  dc.DocStatus = dst.StatusNum
        WHERE dc.CreatorUserId = d.CreatorUserId
            AND (dst.StatusName LIKE 'void%')
            AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
            AND ((dst.StatusNum BETWEEN 1 AND 20) OR (dst.StatusNum BETWEEN 100 AND 120))
      ) >= @MinVoids
ORDER BY name

It's not a trivial query. You need one more join or a subquery:

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum AND ds.StatusName LIKE 'void%'

WHERE ds.DateModified BETWEEN @BeginDate AND @EndDate
--DateModified is when the document was voided--

  AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
        WHERE dc.CreatorUserId = d.CreatorUserId
          AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
      ) >= @MinVoids ;

Not sure about the name of the column (used DateCreated) in table [tblDocument]. Modify accordingly


Find Source & Details of APL Heap Table Insert I have been running system monitor(sysmon) on Sybase ASE 15.7(SP139) on HP-UX(Itanium) with below command:

sp_sysmon "00:10:00"
go

Observed that number of inserts are very high in any time of a day, unable to find the source of it and the details e.g.- table name, database name, program name etc. below is sample output from sysmon in the early morning when there is no load:

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                  10.7           n/a        6390     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table              58665.7        5508.5    35199448     100.0 %
      APL Clustered Table             0.0           0.0           7       0.0 %
      Data Only Lock Table           13.2           1.2        7918       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted             58679.0        5509.8    35207373     100.0 %

It looks like inserts are happening in tempdb however these many inserts are a bit bothering, Appreciate to have expert advice on this.

I found an indirect way of addressing the issue posted in my question as I couldn't find any tool or script which could monitor all the inserts happening at database level, I analyzed the procedure(query) which runs 24 hours a day and is using maximum resource at Production database. To simulate, I executed that procedure manually in Test environment with all its parameters and kept sysmon running in the background for the same period as much it takes for procedure to execute. And I managed to gather close to same number of inserts in the test environment.

In order to prove that this is the only procedure which is doing this much high number of inserts every seconds, I also ran sysmon before and after the problematic procedure and captured that number of inserts too, which is in hundreds and not even in thousands.

Sysmon During procedure execution:

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                   0.1           n/a           9     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table              89575.5      895754.9     8061794     100.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted             89575.5      895754.9     8061794     100.0 %

Sysmon Before/After procedure execution(when its not running):

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                   0.1           n/a          13     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table                127.6         883.7       11488     100.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted               127.6         883.7       11488     100.0 %

This way, I managed to find the procedure which was doing 60k inserts every second. Also to add, these inserts were because of improper joins of table(containing huge records) and number of inserts were in the form of worktable as below:

Table: Worktable1 scan count 1, logical reads: (regular=88517 apf=0 total=88517), physical reads: (regular=52316 apf=0 total=52316), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=8092124 apf=0 total=8092124), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 1, logical reads: (regular=11 apf=0 total=11), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Table: Worktable2 scan count 0, logical reads: (regular=10 apf=0 total=10), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable33 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable9 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable7 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable4 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=8180641 apf=0 total=8180641), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable17 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable15 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable12 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable31 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable29 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable27 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable25 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable23 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable21 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable19 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 0, logical reads: (regular=21 apf=0 total=21), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

I hope above will help others in case they face similar issue which I faced.


Unable to turn on compression using innodb I am unable to create a new table using compression.

I'm using the following options which from what I understand should allow me to use compression.

default-storage-engine          = InnoDB
innodb_large_prefix             = true
innodb_file_format              = barracuda
innodb_file_per_table           = true

This is on a brand new install of MySQL, and these configuration options have been set before creating the new database, so file per table should be set.

When I try to create a new table I get the following response:

CREATE TABLE IF NOT EXISTS `db_example`.`table_example` (
   `ugid` INT NOT NULL,
   `uid` INT NOT NULL,
   PRIMARY KEY (`ugid`, `uid`))
   ENGINE=InnoDB ROW_FORMAT=COMPRESSED;;

Error message:

Error Code: 1031. Table storage engine for 'table_example' doesn't have this option.

I don't understand why this would be happening since the storage engine is set to innodb, which should have support for compression.

Additionally. If I force the creation of the schema to complete by disabling innodb strict mode, and then try to alter the tables enabling compression:

ALTER TABLE ${table_goes_here} ROW_FORMAT=COMPRESSED;

I get the following:

ERROR 1478 (HY000) at line 1: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'

Error Code: 1031. Table storage engine for 'table_example' doesn't
  have this option.

I have gone through your error it's based on ROW_FORMAT.

As per MySQL documentation here If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf (For Linux environment) or my.ini (for windows environment) configuration file.

As MySQL documented here The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression. The COMPRESSED row format supports index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix variable, which is enabled by default.

  Note: The compact family of row formats also optimizes CHAR column storage when using a variable-length character set such as utf8mb3
  or utf8mb4.

For further your ref here , here


Posgresql - deadlock on non related queries I am using Postgresql 11.
I have found an error similar to following in my logs:

org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 332091 waits for ShareLock on transaction 99896257; blocked by process 332093.
Process 332093 waits for ShareLock on transaction 99923910; blocked by process 332091.
  Hint: See server log for query details.
  Where: while locking tuple (884208,33) in relation "tab_1"
SQL statement "SELECT 1 FROM ONLY "sch"."tab_1" x WHERE "tab_1_key" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "INSERT INTO sch.tab_2 (col1, col2, ..., coln)
   SELECT
      col1,
      col2,
      ...
      coln
   FROM
      sch.tab_3
   LEFT JOIN sch.tab_4
      ON tab_4.id = tab_3.id
   LEFT JOIN sch.tab_5
      ON tab_5.id = tab_3.id

So basically the first query looks like some DB internal query, nothing from my code.
And it seems like it is making deadlock with my query. But they are not related by any table.
Any ideas about how to track the reason for the deadlock in such a case?

These queries are generated internally by the triggers that implement foreign key constraints.

For example, adding a row to a table with a foreign key constraint will add such a key share lock on the corresponding row in the referenced table, so that nobody can delete it or modify the primary key until the inserting transaction is done.

This can cause deadlocks between transactions that modify both tables.

When searching for the statements at fault, don't forget that it was not necessarily the last statement in the transaction (that is shown in the log message) that took the problematic lock.

One way to proceed is to set log_min_duration_statement to 0, and when the deadlock strikes again, examine all statements run by the affected transactions. The other way is to review the application code to find out which statements were executed in the transactions.


Is it possible to force a query to ignore the server option "remote proc transaction promotion" so it doesn't promote remote proc transaction? I have a specific query that executes a stored procedure on a remote linked server and inserts the results into a local temp table.

I don't want it to promote a remote procedure transaction but I can't re-configure the server.

Is there a query hint or some way to force it to not promote a remote procedure transactions?

Edit: The reason I ask is because I'm getting the following error when trying to execute my remote procedure call:

  OLE DB provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" returned
  message "The transaction manager has disabled its support for
  remote/network transactions.". Msg 7391, Level 16, State 2, Procedure
  spMyRemoteProcedure, Line 64 [Batch
  Start Line 15] The operation could not be performed because OLE DB
  provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" was unable to
  begin a distributed transaction.

I know I can set "Enable Promotion of Distributed Transactions" to false on the Linked Server options to fix this, but I'm not allowed to make server option changes in this context, so I'm looking for a workaround.

Is there a query hint or some way to force it to not promote a remote procedure transactions?

No there isn't, but if you're currently doing something like this:

INSERT #Temp
EXECUTE schema_name.procedure_name AT linked_server;

or

INSERT #Temp
EXECUTE linked_server.db_name.schema_name.procedure_name;

You could work around it using:

INSERT #Temp
SELECT column_list
FROM OPENQUERY(linked_server, 'EXECUTE db_name.schema_name.procedure_name');

Assuming you're using a simple procedure that returns a single result set. You may also need to add SET NOCOUNT ON to the procedure as I mentioned in answer to Using OPENQUERY to execute a script.


SQL Server Authentication I am able to access a database using the Windows Authentication method in DBeaver. However, when I switch to different account in Windows and I would like to access the datebase, I used SQL Server Authentication method, it keeps giving me error. How should be the username format?

Moreover, how can I add another Windows account to the database so I access the database without the need to switch the Windows account everytime.

There are two modes of authentication on SQL Server as you can see below:

Windows Authentication - If only this is enabled then, you can only create domain account(windows account) and login will have format like --> Domain_Name\Login_Name
SQL Server and Windows Authentication - Also called mixed more, if this is enabled then you can either have windows authenticated(domain account) or SQL login.

The difference between above two is - You need to explicitly provide password for the SQL authentication whereas in the Windows authentication if you have already logged in with same domain account then, no need to provide password and other policies are taken care at DC(Domain Controller or Active Directory) level.

In case, you have logged in to windows using account A(for example) and want to use account B, you can do so by doing CTRL+SHIFT+Right Click and choose "Run as different user" for the application and you may input your ID(with domain i.e. Domain_Name\Login_Name) and password for account B as below:

For creating a new windows authentication account and other relevant details, you may refer below link:

https://www.tutorialgateway.org/create-windows-login-in-sql-server/

I hope above helps.


How to prevent high memory, CPU and time consumption by MySQL restore? I have a local test machine with 2 GB RAM and a dual core processor.

I imported a dump on that using

mysql -uuser -psecret < script.sql

script.sql is a 700 MB file which contains a single database with more than 100 tables.

Restore took approximately 25 minutes.

Question: Why it took so long time? I have already optimized my server settings. The dump also contains extended inserts and disables keys). Is 700 MB is too large for this kind of machine? mysqld was using 100% of one of the CPU when I checked using htop and 22% of the memory.

Now what actually I want is to convert all my tables of that database to InnoDB. So for that I executed a simple shell command:

mysql -uuser -psecret -Bse "select Concat('Alter Table ' , TABLE_SCHEMA ,'.',TABLE_NAME , ' Engine= \'InnoDB\' ;') from information_schema.tables where ENGINE='myisam' and TABLE_SCHEMA='db_name';" > Innodb.sql

so Innodb.sql contains the script for converting tables to InnoDB.

Now when I run Innodb.sql one of my table takes more than 20 minutes for conversion. It contains only 1,378,397 records.

In total it takes more than 30 minutes. In the meantime memory usage by mysqld daemon was 73%. The CPU usage was OK at this time.

How can I minimize the time consumtion? Should I go for changing MySQL server settings or anything else?

If anybody wants my my.cnf setting I will share that.

I think the second point more or less shows you where your bottleneck is.  I would be willing to bet that if you look at top or equivalent on your platform you will see high I/O wait time.

In essence you are probably waiting for your hard disks as tables are rewritten.  Also innodb has some oddities regarding locks here so it isn't clear that these are all sequential writes.

I can't tell you about expected MySQL performance other than that you seem to be hitting write performance bottlenecks.  I can tell you that a 700MB dump won't restore super-fast on any db with ordinary hard drives, but also that 25 min does seem a bit high based on my experience on PostgreSQL.


Why is my database stuck in the Restoring state? I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the beginning of these tests.

The restoration code looks like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY
ALTER DATABASE {0} SET MULTI_USER

After this, the tests connect to the database and do whatever they need to do.

The problem is that, during the tests debug, if eventually I need to hit the stop button to cancel the tests, the database is being left in the Restoring state forever. It's strange because it only happens when I stop the debug session. If I have 20 tests and all of them restores the snapshot prior to the test, I'll get no error during these test executions.

Do you have any suggestions of what might be causing this?

EDIT

Complementing the @usr's response, to recover the database from the inconsistent state at the beginning of the tests, it's necessary to add the REPLACE option to the restore statement.

It will work if it's like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY, REPLACE
ALTER DATABASE {0} SET MULTI_USER

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new.

Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests.

To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot again.


Background images in SSRS report resizing on export to PowerPoint We are experiencing an issue with SSRS 2016, when we export a report with a background image to PowerPoint.  The image displays properly in the designer (VS 2015), and in preview as well; it also displays properly if exported to PDF or Word.  When exported to PowerPoint, the image is reduced to roughly 1/3 of the intended size.  We have attempted changing the DpiX and DpiY device information settings, to no avail.  We then tried enlarging the background image to 3x the screen size; this worked for the PowerPoint export, but now the image extends well beyond the view in the designer, preview, and other export formats.

We formerly used Aspose for this one critical report; however, the verion we currently have of Aspose does not work with SSRS 2016.

While this is not an answer but a work around that works for me.

You need to have both a small image , and a larger version of the image imported into your report.  Then use the following code to display the large image when exporting to PowerPoint.

The following code should be used in the rectangle's BackgroundImage.Value property

=iif(Globals!RenderFormat.Name="PPTX","LargeImage","SmallPage")


Master Slave Error user specified as a definer ('dbadmin'@'%') does not exist I setup mysql (version 5.5) master slave replication and when test it with root user from phpmyadmin data succesfully transferd. When start application slave have an error:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.1.1.31
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6963420
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529310
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1449
                   Last_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl
                                        set ....limit 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6300951
              Relay_Log_Space: 1191935
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1449
               Last_SQL_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl
                                        set ....limit 1'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

How to solve it ? maybe just add user 'dbadmin'@'%'?

Perhaps the GRANTs on the Slave are not consistent with those on the Master?

On Master, do SHOW GRANTS FOR 'dbadmin'@'%';.  Then apply the grant(s) it gives you to the Slave.

Then do START SLAVE;


Create a unique constraint on a column based on distinct value from another column in the same table I want to make a column unique, but it has to be unique based off a distinct value from another column in the table.

Consider the following table:

CREATE TABLE locations(
  locationId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  locationName VARCHAR(50) NOT NULL,
  companyId INT NOT NULL FOREIGN KEY REFERENCES companies (companyId)
);

My goal is to have location name be unique for each value in the companyId column.

For example, given the following table:

    [locationId]   [locationName]   [companyId]
=====================================================================
    1              'Example A.'     1
    2              'Example B.'     1
    3              'Example A.'     2
    4              'Example C.'     2

If I attempted to execute the following query:

INSERT INTO locations (locationName, companyId)
VALUES ('Example B.', 2)

This would not give me a unique constraint error because no row in the locations table has a locationName of 'Example B.'and a companyId of 2

However, the executing a query like this should produce a unique constraint error:

INSERT INTO locations (locationName, companyId)
VALUES ('Example C.', 2)

because a row with locationName 'Example C.' and companyId 2 already exists.

I've tried the query

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName])
WHERE ([companyId] IS DISTINCT);

however IS DISTINCT is not valid SQL syntax, but I can't think of the proper way to accomplish this.

Any help would be very appreciated!

Seems like this should do it:

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName],[companyId]);


Logical Replication: Schema does not exist while subscription PostgreSQL logical replication.
I am trying to replicate the tables from server 1 to server 2.
In Server 1
create publication locale for table code.locale_cd;
CREATE PUBLICATION

In Server 2
create subscription indsolv_dev10 connection
    'host=192.168.0.26 port=5432 password=****** user=replicator
    dbname=indsolv_dev' publication locale;

Throws an error schema "code" does not exist but it's present in the database.

Connect to the database where Subscription is required.

\connect indsolv_dev


How to maintain database ownership restoring across domains? I am working with a vendor who is developing a SQL Server database outside of our domain. Occasionally, they need to deploy a new copy of that database into our domain by backing up their database, and restoring over the top of the of the database within our domain.

The vendor uses a domain login on our side to access this database. This domain login is also the owner of this local copy. This vendor is trusted enough that we have granted the login the "dbcreator" server role. A combination of database ownership and the dbcreator role allows this domain login to restore a copy of the database over the top of the existing database. The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system). After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again. The domain login can then access the database again.

Is there any way I can configure this so the vendor is able to perform the restores when they need to without losing the existing ownership? This way I would not have to be involved every time they deploy a new version of the database. I cannot give them sysadmin because there are other databases on the server.

The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system).

Correct. While it's possible (I believe) to have the same SID (i.e. Security ID) between their system and yours (if so, it would require you creating their domain account in your domain with the SID that they provide that is the SID of the owner account on their side), most likely it's a different SID, and that is retained in the database itself.

  After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again.

Now this doesn't sound right. Usually, if you add an SID to a database as a User, and then later try to make a Login (having that same SID) the owner of the database, you will get an error stating that the account / SID is already present in the database:

     Msg 15110, Level 16, State 1, Line XXXXX
     The proposed new database owner is already a user or aliased in the database.

It's possible that something is misstated here, or that I am misunderstanding something, but as we will see in a moment, it doesn't really matter.

  This vendor is trusted enough that we have granted the login the "dbcreator" server role.

It's nice that you can trust them, but you don't need to do anything special for them. You can allow them to restore their database, and automatically set the owner to their domain account, and anything else that needs to happen in this process, all while removing them from the dbcreator fixed Server Role. All their login needs is the ability to connect to the instance (and they already have that), and permission to execute a single stored procedure. What stored procedure? The one I will now describe :-)

Using the wonderful awesomeness of Module Signing, you can set up a stored procedure that will a) execute the RESTORE DATABASE command, b) set their login as the owner of that database, and c) perform any other administrative steps needed for this operation, no matter what level of permissions are needed. You will never give the vendor's login any permissions except for the ability to execute this single stored procedure. The necessary permission(s) will instead be given to the stored procedure.

Assuming that you have a [Utility] (or whatever name) database for general administrative functionality, do the following:

In the [Utility] database:

Add the DOMAIN\VendorLogin to the DB using CREATE USER
Create a RestoreVendorDB stored procedure that does the following:

Executes RESTORE DATABASE [VendorDB] ...
Executes ALTER AUTHORIZATION ON DATABASE::[VendorDB] TO [DOMAIN\VendorLogin];

Allow the vendor to execute the stored proc: GRANT EXECUTE ON [dbo].[RestoreVendorDB] TO [DOMAIN\VendorLogin];
Create a certificate using CREATE CERTIFICATE...
Sign the stored procedure with that certificate using ADD SIGNATURE...
Copy certificate (only the public key) to [master] database using Dynamic SQL to execute CREATE CERTIFICATE with result of CERTENCODED(CERT_ID(...))

In the [master] DB, do the following:

Create a login from the certificate using CREATE LOGIN [Permission$CreateVendorDB] FROM CERTIFICATE [...];
Add the certificate-based login to the dbcreator role using ALTER SERVER ROLE [dbcreator] ADD MEMBER [Permission$CreateVendorDB];
If you need an additional role and/or permission in order for the stored procedure to change the database's owner, then repeat the previous step with the appropriate role and/or  GRANT the certificate-based login the appropriate permission(s).
Remove the vendor's login from the dbcreator role (they no longer need that permission) using ALTER SERVER ROLE [dbcreator] DROP MEMBER [DOMAIN\VendorLogin];

If you ever alter the code in the RestoreVendorDB stored procedure, just re-sign the stored procedure (after the ALTER) using ADD SIGNATURE....

Using this approach, you not only get exactly what you were wanting, but you also end up with a more secure system 😺 .

For more explanation and a working example (of this concept), please see:

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


What are the risks of upgrading DB major version in replica and then promoting it as master? Recently AWS decided to force update all MariaDB RDS under 10.3, my RDS is in MariaDB 10.1. We are trying to minimize the downtime and any risk of data out of sync.
Our plan is to create a replica and upgrade it from 10.1 to 10.5. After that we will let it to consume binary log from old non-upgraded RDS then promoting it as Master and point our website to the newly created and upgraded RDS.
Assuming we will have continuous stream of data coming into the RDS, what are the risk of steps above? Is the new RDS able to get all binary log genereated during the upgrade time? We estimated there will be ~ 1.5 hours to finish upgrading from 10.1 to 10.5
Thank you for helping

There are risks.  I doubt if anyone can enumerate all the risks.
Plan A:  Dump all the data via mysqldump (or other dump utility); upgrade the system; load the data.  This usually lets you safely do multi-major-version upgrades.
Plan B:  Upgrade one major version at a time.  (This has always been a requirement in MySQL, especially when going to 8.0.  MariaDB does a better job at relaxing the limitation to a single major.)
Plan C:  Get RDS to do the work.  I would expect that they have well-tested upgrade paths for when they force an upgrade.  (The Question is unclear as to whether your 10.1 is currently hosted by AWS or yourself.)
I would go with 'C' since they have all the tools to do everything with minimal downtime, etc.  They will probably use snapshots, etc, which you don't have easy access to.


Multiple table rows in one row Informix I have problem just like this one but in Informix.
I have table with 2 attributes ID, email like this one:

ID  email
1   my_mail1@mail.com
2   my_mail2@mail.com
2   my_mail3@mail.com
2   my_mail4@mail.com
3   my_mail5@mail.com
4   my_mail6@mail.com

I want to create a query that would return this:

ID   email
1    my_mail1@mail.com
2    my_mail2@mail.com my_mail3@mail.com my_mail4@mail.com
3    my_mail5@mail.com
4    my_mail6@mail.com

If you have version 12.1 the following method also works, and is especially useful if you don't have access rights to create procedures on the production database.

It uses rank()over() to group the email addresses by id and sequence them,
and then sys_connect_by_path() to do the concatenation.

select id,  sys_connect_by_path(trim(email),' ') as email
from
 (
  select id, email,
         rank() over (partition by id order by email) as seq,
         rank() over (partition by id order by email desc) as rseq
    from emails
 )
 where rseq = 1 start with seq = 1
 connect by prior id=id and prior seq=seq-1
 order by id


What is the persistent_version_store table? What is the purpose of the MS Shipped table: sys.persistent_version_store?

Why do I want to know

Primarily curiosity, partly because it may relate to an issue waiting to bite us...

We're running a SaaS instance of MS Dynamics 365 for Finance and Operations.  This has very limited tools for monitoring performance.  Whilst trying to find something of meaning I discovered that this table had an insanely high row count (~75,000,000).  That seems worrying. It's possible that this is OK, but MS have included it on their analysis tools, meaning that this eclipses all other information on the graph (since it has a linear scale) making the graph pointless.  I want to understand what this table is so I can determine if it's something to be investigated, or if it's just bad design to have included it on the monitoring tools.

Why not read the documentation

Since it's an MS table it should be documented somewhere.  However, searching persistent_version_store gives 1 result, and it's in Chinese.  Searching for Persistence Version Store does better; but it's unclear whether this is the same thing / there's not much documentation on what it is; only how to fix issues where it's reached its maximum size.

Since this question hasn't had any comments or answers since August 2018 I'm going to assume nothing came of the large number of rows, but documenting the structure of the table might be useful to someone.

This table can be found in SQL Server 2019 Preview (CTP 2.2 as of this writing).

CREATE TABLE sys.persistent_version_store (
    xdes_ts_push bigint,
    xdes_ts_tran bigint,
    subid_push int,
    subid_tran int,
    rowset_id bigint,
    sec_version_rid binary(8),
    min_len smallint,
    seq_num bigint
    prev_row_in_chain binary(8),
    row_version varbinary(8000)
);

Perhaps given the high churn of a Dynamics environment, the version store did have a lot of rows, but they were short-lived?


Convert column from string of numbers to a subtotal on select statment This probably very basic but I have struggled to figure out. I have looked around but couldn't find any similar problem. I am using SQL Server 2008 R2

I have a view:

ID  |           VALUE
1   | 151;181;179;185;160;187
2   | 185;160
3   | 151;181;179;185;150
4   | 185;160;187
5   | 187

I want a select statement to produce this results:

ID  |           VALUE
1   |           1043
2   |           345
3   |           846
4   |           532
5   |           187

This is definitely not a good data model.

If you are stuck with it you could use

SELECT ID,
       X.value('sum(/x/text())', 'FLOAT') as Value
FROM YourView
CROSS APPLY (VALUES(CAST('<x>' + REPLACE(VALUE, ';', '</x><x>') + '</x>' AS xml))) V(X)

Online Demo


Can migration from MS Access to SQL Server or ... make database faster? A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn't because of their database design rather than MS Access.

I think that their database is smaller than 1GB large and it will remain always smaller than 10GB. Let me be pessimist 100GB! I'm afraid that the migration doesn't accelerate their query execution time and they would remain unsatisfied. I'd like to know if their slow interaction could be because of Access or I should  contact consult them to continue working with access and just change the designs, optimize network and the programs?

In which case should I think migrations towards non relational databases like MongoDB?

We can't really answer whether you should consider other platforms such as mongodb - we know nothing about the application or its data and whether such a platform would be appropriate.

I can tell you that SQL Server has a lot more features geared toward performance and tuning than Access will ever have. Access is pretty simplistic and, while it can function as a multi-user database, it is not a true RDBMS and really is meant for the desktop space.

Whether those features in SQL Server will make your company's specific application any faster, again, shrug, we just don't know.


Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks, and I suspect it's because some of the tables are InnoDB. Post-import, several .ibd files are showing up on the new server in /var/lib/mysql/dbname that aren't present on the old server. For each table that has an .ibd file, we've noticed that the data import is incomplete to varying degrees. For example, one table should have more than 4,000 rows in it, but after import, it only has 307. Also, of the three views present in the database, two import correctly, but one appears as an empty table.

Here's some output from the latest import attempt:

mysqldump -u -p live < live_dump_from_old_setup.sql

User time 8.74, System time 0.22
Maximum resident set size 14896, Integral resident set size 0
Non-physical pagefaults 5767, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2780, Involuntary context switches 33

My application connects to the database successfully after the import, so no problem there, but a lot of the data is missing.

I'm fairly new to MariaDB, even though it's supposed to be a drop-in replacement for MySQL, so I'm sure I'm missing something. How can I ensure that my entire mysqldump is imported correctly?

5.0 -> 5.1 -> 5.5 -> 5.6/10.0 -- That's 3 major upgrades.  Various tables in the mysql database have been added/changed.  Lots of changes to information_schema / performance_schema.

You would be quite lucky to leap from 5.0 to 10.0 without some major hiccups.  You did run mysql_upgarde?  MariaDB tries to make that handle multiple hops, but this may be more than it can handle.

if enforce_storage_engine is turned on, this could explain the sudden appearance of unexpected .ibd files.  There can be hiccups in blindly changing the Engine.

How are you measuring number of rows?  When using SHOW TABLE STATUS, MyISAM has exact numbers; InnoDB has approximate numbers.  Even 307 versus 4000 is not unheard of.  Use SELECT COUNT(*) .. to be sure.

As for the empty table -- manually run the load for it; see if you get any extra clues.


Constraints based on other columns Is it possible to limit what values are allowed in a column based off of other values in the row?

For example, my table:

ID  Test_mode  Active
--  ---------  ------
1   1          Null
2   0          1
3   1          0

Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

OR

If Test_mode is 1 not allow insertion/update of Active

OR

Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.

I hope this makes sense, if not let me know and I'll update the question.

First of all, Welcome to dba.stackexchange.com and thanks for your post !!

Is it possible to limit what values are allowed in a column based off of other values in the row.

Yes using CHECK CONSTRAINTS as described here

Example :

create table myTable (ID int identity(1,1)
                        , Test_mode int
                        , Active int
                        )
go

-- Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.
ALTER TABLE myTable WITH CHECK ADD
   CONSTRAINT ck_active CHECK (active IS NULL OR active IN (1, 0))
   go

-- some test data
insert into myTable (test_mode, Active) values (1, null)
insert into myTable (test_mode, Active) values (0, null)
insert into myTable (test_mode, Active) values (1, 0)
insert into myTable (test_mode, Active) values (0, 1)
insert into myTable (test_mode, Active) values (1, 1)

select * from myTable

-- Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

update myTable
set Test_mode = case when Active = 1 then  0
        else Test_mode
        end
where Active = 1

If Test_mode is 1 not allow insertion/update of Active --OR--
Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Use TRY/CATCH as described here