How to configure Oracle11g to send out Password Expiry Notifications vide SMS or e-mail? Please guide me in configuring Oracle11g to send out Password Expiry Notifications vide SMS or e-mail before password expiry of one week or upon user's profile entering into grace period or any other given condition.

With all the possibilities of not expired, in grace period and expired this can be quite tricky.  As well many databases have service accounts that there is no point in sending emails to as they do not have an email address. In order to get this right you need:

a database profile for the people whose passwords expire and you wish to advise
a table to hold their database username, email, phone
a procedure or external process so that when a new user is added you add them to your email table.  Do not put a trigger on dba_users!
a logging table so when someone's password expires and they say they were not notified you can show when the email was sent
a table/package to hold constants like the name of the email domain

I had this code created by someone on our team to answer the same problem. It works but could be improved.  I leave the profile, table and some package names as an exercise

PROCEDURE EmailExpiringPassword IS

      pEmailBody       VARCHAR2(32767);
      pEmailTitle      VARCHAR2(32767);
      pCount           INTEGER;
      pDBName          VARCHAR2(50);
      pDomain          VARCHAR2(50);
      --email of group issuing this warning
      --so they can be contacted IF the user is locked out
      pDBNameSupportEmail        VARCHAR2(255);
begin
      pDomain := <call a package to get the constant value for the domain name>
      pDBNameSupportEmail := <call a package to get the constant value for the email address of the group/team responsible for account management>

      SELECT d.name
      into pDBName
      FROM v$database d;

       FOR missed IN (  SELECT
                        du.username,
                        du.expiry_date,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        du.account_status,
                        du.profile
                        FROM dba_users du
                        left outer join <Your custom table to hold user email,phone> ud
                           on ud.db_username = du.username
                        where   ud.db_username is null
                        and du.profile in ('<profiles of interest>')
                      )
         LOOP
          pCount := pCount + 1;
          pEmailTitle := pEmailTitle || missed.username || ' missing record, ';

          pEmailBody := pEmailBody ||
                        '
' ||
                        'Username: ' || missed.username || '
' ||
                        'Profile: ' || missed.profile || '
' ||
                        'Expiry Date: ' || missed.expiry_date || '
' ||
                        'Account Status: ' || missed.account_status ||  '
' ||
                        'Database: ' || pDBName  || '
' ||
                        'Table: <Your custom table to hold user email,phone>' || '
' ||
                        'Missing record. Please add the user to the table <Your custom table to hold user email,phone>.'   || '
' ||
                        '' || '
'
                        ;

          IF (length(pEmailBody) > 30000)THEN
            EXIT;
          END IF;
         END LOOP;

         IF (pCount > 0)
           THEN
           <Your custom package to send email>(
           strFROM    => pDBName || '_missing_record@' ||  pDomain ,
           strsubject => pEmailTitle,
           strbody    => pEmailBody,
           strto      => pDBNameSupportEmail
           );

           INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
           (user_data_pwd_email_log_id ,sentdate, username, EmailAddress, EmailBody)
           SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1
           FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
           sysdate,
           pEmailTitle,
           pDBNameSupportEmail,
           pEmailBody
           FROM dual;
           commit;

         END IF;

         pCount := 0;

        FOR email IN (  SELECT
                        du.username,
                        du.expiry_date,
                        ud.firstdotlastname || '@' || pDomain as email,
                        cast(du.expiry_date - sysdate as integer) as days_number,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        account_status, CASE WHEN p.limit = 'DEFAULT' THEN 10 ELSE 10 END AS Limit
                        FROM <Your custom table to hold user email,phone> ud
                        inner join dba_users du
                             on ud.db_username = du.username
                        inner join dba_profiles p
                             on du.profile = p.profile
                             and resource_name = 'PASSWORD_GRACE_TIME'
                        where
                        du.profile in ('<profiles of interest>')
                        and (cast(du.expiry_date - sysdate as integer) between -10 and 10
                            or account_status = 'EXPIRED (GRACE)')
                        and ud.active_ind = 1
                      )
         LOOP

          pCount := pCount + 1;

          pEmailTitle := email.username || ' password expiring';

          pEmailBody := 'Username: ' || email.username || '
' ||
                        'Database: ' || pDBName  || '
' || '
' ;

          IF NOT (email.account_status = 'EXPIRED (GRACE)' and email.expiry_date is null) and email.days_number>0
              THEN
                pEmailBody :=
                'Your password will expire in ' ||  email.days || ' days. ' ||
                'Please reset your password. It will expire after ' ||
                email.expiry_date ||
                ' and locked after ' ||
                to_char(email.expiry_date + email.limit) || '.' ||
                'Contact ' || pDBNameSupportEmail || ' for details.' ||'
' ||'
';

          ELSE IF email.days_number <= 0 and email.expiry_date >= sysdate
               THEN
                pEmailBody :=
                'Your password expired ' ||  email.days || ' days ago. ' ||
                'Please reset your password. It will be locked after ' ||
                to_char(email.expiry_date + email.limit) || ' days.' ||
                ' Contact ' || pDBNameSupportEmail || ' for details.' ||'
' ||'
' ;

           ELSE
                pEmailBody := pEmailBody ||
                'Please login into ' || pDBName || ' to reset your password. ' || '
' ||'
';
           END IF;
        END IF;

          pEmailBody := pEmailBody ||
          '' || 'Please do not reply to this email, this address is not monitored.'   ||
          '
';

         INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
         (user_data_pwd_email_log_id ,sentdate, username, EmailAddress ,  EmailBody)
         SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1 FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
         sysdate,
         email.username,
         email.email,
         pEmailBody
         FROM dual;
         commit;

         <Your custom package to send email>(
         strFROM    => pDBName || '_password_expiring@' ||  pDomain ,
         strsubject => pEmailTitle,
         strbody    => pEmailBody,
         strto      => email.email
         );

         END LOOP;

-- delete logs 60+ days old
delete FROM PASSWORD_EMAIL_LOG
where cast(sysdate - sentdate  as integer) >=60;

end EmailExpiringPassword;


Log Shipping copy job doesn't delete old files on a secondary node that is part of AlwaysOn configuration I have the following setup, all servers are SQL 2014 Enterprise:

2 node AlwaysOn availability group;
third server for off-site DR.

Log backups are being taken on the primary node, copied to the secondary node (restore job is disabled of course) and copied and restored to the DR server.

Everything is working great, except the fact that the log backup files are not being deleted on the secondary node. I noticed it because the folder kept growing, when I opened it I saw all the logs are there since day 1 when log shipping was set up. All jobs are executed without errors and the interval to keep the log backups is set. How can I troubleshoot this?

UPDATE: Apparently, the process of deleting the old files is actually part of the RESTORE job and hot the COPY job.

Now the question is, can I safely run the RESTORE job on a secondary AlwaysOn node as well?

Wouldn't you want to ensure the secondary logs are restored to the secondary server at certain intervals? With log shipping, I thought you ship the logs over to your secondary DB and then restore from those to keep the secondary up-to-date as close as possible to your primary.

Do you see any reason why you would not want to automate the log shipping restore on your secondary DB in the log shipping configuration?

Once those log files are applied to secondary, then the job will purge the logs that are not needed any longer since those are reflected in the secondary DB at that time.

Test it out and see how it goes.

EDIT: @nulldotzero

Okay, since you cannot run restore operations on primary or secondary in an AlwaysOn configuration, and because you are also copying the TRN log files to the seconary as well as the DR for redundancy, then you could just setup a SQL Agent job on the secondary instance to do the below for whatever hours you feel need to be purged.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1

Since the log backup files are purged from the locations which the LSRestore for DR runs and grabs those, then that job will take care of those once the restore job completes successfully.


How do I access a result set from a stored procedure in another stored procedure? I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like this:

CREATE PROCEDURE consumerProcedureA()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE consumerProcedureB()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE supplierProcedure()
BEGIN
    SELECT field1, field2, field3, MESSYEXPR AS field4
    FROM (complicated sub-SQL query) -- blah blah blah
    ;
END;

Grant you, I could create a view to capture the common query, but is there a way to have a calling stored procedure access and process the result set returned by a called stored procedure?

I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:

Calling a Stored Procedure in a Stored Procedure in MySQL

The accepted answer by AhamedMustafaM demonstrates a technique using OUT parameters with the following code:

    CREATE PROCEDURE innerproc(OUT param1 INT)
    BEGIN
     insert into sometable;
     SELECT LAST_INSERT_ID() into param1 ;
    END
    -----------------------------------
    CREATE PROCEDURE outerproc()
    BEGIN
    CALL innerproc(@a);
    // @a gives you the result of innerproc
    SELECT @a INTO variableinouterproc FROM dual;
    END

I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple OUT parameters to get what you need.


Best Way to Grant Read All Permission in SQL Server 2008 R2? What is the best method to grant read access to all current and future databases to a user (AD Group in this case)?

Currently I have a flaky scheduled job that does this using  (going to start debugging the following, but maybe I am doing it wrong anyways):

sp_msforeachdb '
USE [?]
CREATE USER [MY_DOMAIN\SQL-READ-ALL] FOR LOGIN [MY_DOMAIN\SQL-READ-ALL]
USE [?]
EXEC sp_addrolemember N''db_datareader'', N''MY_DOMAIN\SQL-READ-ALL''
'

Perhaps there is a better way?

For future databases, do this because all DBs are created from model

USE model
EXEC sp_addrolemember N'db_datareader', N'MY_DOMAIN\SQL-READ-ALL'

For existing databases, just run what you have above, once


How to specify the domain of an attribute in an entity-relationship diagram (ERD)? In an ERD (as in, rectangles for entity types, diamonds for relationship types, ovals for attributes, etc.) how would one specify that an attribute is, say, a 5-letter word?

That sounds like Chen notation. I don't think Chen has a standard representation for attribute domains.

If you're drawing the diagrams free-hand (or using a generic diagramming program) then just write it in there. If your using specific ERD software go with what it supports.


Finding out the hosts blocked by mysql server Can someone tell me how to list the hosts which are blocked by the mysql server due to the reason that they crossed the limit of max_connect_errors. Is there any table in which MySQL server keeps this data. I am using mysql-server-5.1.63

Noted in 5.6.5 changelog.

MySQL now provides improved access to the host cache, which contains
client host name and IP address information and is used to avoid DNS
lookups. These improvements have been implemented:

A host_cache Performance Schema table exposes the contents of the
host cache so that it can be examined using SELECT statements. The
Performance Schema must be enabled or this table is empty.

If you upgrade to this release of MySQL from an earlier version, you
must run mysql_upgrade (and restart the server) to incorporate this
change into the performance_schema database.
The cache size is configurable using the host_cache_size system
variable. Setting the size to 0 disables the host cache.This is
similar to starting the server with --skip-host-cache, but
host_cache_size is more flexible because it can also be used to
resize, enable, or disable the host cache at runtime, not just at
server startup. If you start the server with --skip-host-cache to
disable the host cache, it cannot be re-enabled at runtime.
There are Connection_errors_xxx status variables that provide
information about the nature of connection errors and that can help
diagnose connection problems.

Improved access to host cache contents makes it possible to answer
questions such as how many hosts are cached, or how close hosts are
to being blocked (by checking whether the
host_cache.SUM_CONNECT_ERRORS column is approaching the value of the
max_connect_errors system variable).


Multiply elements of equally sized postgresql array In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays?

For example

a = [1,2,9,4,5]
b = [5,8,4,0,12]

function(a, b) would return [5, 16, 36, 0, 60].

I'd prefer it as a function like this, since it'd be used in multiple places.

Parallel Unnest

What you want is something like this with parallel-unnest. Here we use the ARRAY() constructor rather than array_agg()

SELECT ARRAY(
  SELECT a*b
  FROM unnest(
    ARRAY[1,2], -- ex1
    ARRAY[2,3]  -- ex2
  ) AS t(a,b)
);

Creating our own function

In functional programming, we call this zip, and you'd use that in a language with primitive functional support like Python, or the like. In languages with more advanced functional support, like Haskell, you'd use zipWith or Ramda.js R.zipWith()

This will return an array sized to the greater of ex1, or ex2. You can put this in a function like this,

CREATE FUNCTION array_zip_with_multiply( arr_q anyarray, arr_e anyarray )
RETURNS anyarray
AS $$
  SELECT ARRAY(
    SELECT a*b
    FROM unnest(
      arr_q, -- ex1
      arr_e  -- ex2
    ) AS t(a,b)
  );
$$ LANGUAGE sql
IMMUTABLE;

SELECT array_zip_with_multiply( ARRAY[1,2,9,4,5], ARRAY[5,8,4,0,12] );
 array_zip_with_multiply
-------------------------
 {5,16,36,0,60}
(1 row)

Note: this won't work if there isn't an operator * defined for the type.


Repeating the same function in a query In the below query, there are repeated calculations such as the three calls to SUM(p.amount). Does MySQL re-calculate for each function call or is there some kind of memoization optimization under the hood? If not, how can this kind of query be optimized for maximum performance?

It seems like it would be faster after the first calculation to get the next one by the alias name, total_payemnts, but that just throws an error.

SELECT LEFT(c.last_name, 1) AS 'last_names',
    SUM(p.amount) AS 'total_payments',
    COUNT(p.rental_id) AS 'num_rentals',
    SUM(p.amount) / COUNT(p.rental_id) AS 'avg_pay'
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY LEFT(c.last_name, 1)
ORDER BY SUM(p.amount) DESC;

This query runs on the MySQL Sakila sample database.

A definite answer is really difficult. However, some trials might give us some hints.

We start with a few data:

CREATE TABLE customers
(
    customer_id integer PRIMARY KEY,
    last_name varchar(50) NOT NULL
) ;

CREATE TABLE payments
(
    rental_id integer not null,
    customer_id integer not null, --  references customer(customer_id),
    amount numeric(10,2) not null
) ;

INSERT INTO customers
VALUES
  (1, 'A - Customer 1'),
  (2, 'B - Customer 2'),
  (3, 'C - Customer 3'),
  (4, 'D - Customer 4') ;

INSERT INTO payments
VALUES
  (1, 1, 900.0),
  (2, 1, 800.0),
  (3, 1, 500.0),
  (4, 1, 900.0),
  (5, 2, 500.0),
  (6, 2, 500.0) ;

We first define a DETERMINISTIC (although it has side effects) function to log calls:

CREATE FUNCTION log_numeric(_n numeric(10,2), _counter character(2))
    RETURNS numeric(10,2)
    LANGUAGE SQL
    DETERMINISTIC
    BEGIN
      UPDATE log SET n = n + 1 WHERE counter = _counter ;
      RETURN _n ;
    END ;

We have previously defined a log table, to just store counts:

CREATE TABLE log
(
    counter character(2) primary key,
    n integer default 0
) ENGINE=MyISAM;
INSERT INTO log
VALUES
    ('x', 0),
    ('L', 0),
    ('T', 0),
    ('N', 0),
    ('L2', 0),
    ('T2', 0),
    ('N2', 0) ;

At this point, if MySQL were able to just perform the common subexpression elimination optimization (common for compilers), it would just make ONE call when we issue:

SELECT
    log_numeric(30, 'x') AS c1,
    log_numeric(30, 'x') AS c2 ;

Because log_numeric(30, 'x') would return the same value in both cases (because the function is deterministic).

In fact, it makes two calls to this function, as proven by watching counter 'x';

SELECT
    *
FROM
    log
WHERE
    counter = 'x' ;

[NOTE: Tested with MySQL 5.7.12]

It might be the case that MySQL has decided that the log_numeric function has some side effects, or that User Defined Functions do not work the same way as built-in functions. Built-in functions could be optimized, whereas UDF would not. In any case, if MySQL would have optimized the two expressions with just one call, it would be clear that this optimization is standard. As it did not, we can make an educated guess (which is not the same as a proof) and assume that the functions are called as many times as they're present.

The same can be done with the case posted:

SELECT
    log_char(LEFT(c.last_name, 1), 'L') AS last_names,
    log_numeric(SUM(p.amount), 'T') AS total_payments,
    log_numeric(COUNT(p.rental_id), 'N') AS num_rentals,
    log_numeric(SUM(p.amount), 'T') / log_numeric(COUNT(p.rental_id), 'N') AS avg_pay
FROM
    customers c
    JOIN payments p ON p.customer_id = c.customer_id
GROUP BY
    log_char(LEFT(c.last_name, 1), 'L')
ORDER BY
    log_numeric(SUM(p.amount), 'T') DESC;

And the results point to all calls being evaluated as many times as they appear.

You can check all theses tests at http://rextester.com/ZKS91815.

NOTE: For curiosity, postgreSQL (9.6.1) does a little better: it recognizes the ORDER BY and GROUP BY as "already computed" on the SELECT, but it doesn't optimize either for Common subexpression elimination. Room for improvement.

Alternative query avoiding repeating functions

If you're worried things are computed more than necessary, use:

SELECT
    last_names, total_payments, num_rentals,
    total_payments / num_rentals AS avg_pay
FROM
(
    SELECT
        log_char(LEFT(c.last_name, 1), 'L') AS last_names,
        log_numeric(SUM(p.amount), 'T') AS total_payments,
        log_numeric(COUNT(p.rental_id), 'N') AS num_rentals
    FROM
        customers c
        JOIN payments p ON p.customer_id = c.customer_id
    GROUP BY
        log_char(LEFT(c.last_name, 1), 'L')
) AS q0
ORDER BY
    total_payments DESC ;

I honestly don't think the difference will be much. The weight of the functions in the time spent for the whole query is probably (ver) small. It might be signficant if the functions were complex. Depending on how MySQL deals with Subqueries (that might be temporary tables), this could actually be worse.


Using DNS per application in ConnectionStrings I have come across an idea recently when I had to migrate a ton of in-house applications to a new SQL server. The more I look at it, the more it sounds perfect, but I wanted to ask the community for feedback about it. It is using SQL0216 with AlwaysOn.

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Example: DNS:App1_SQL points to Servr1,  DNS:App2_SQL points to Servr1.
Server=App1_SQL;Database=App1;Trusted_Connection=True;
Server=App2_SQL;Database=App2;Trusted_Connection=True;

This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

If a particular application is using external database references, I trained my developers to always use Synonyms. This way, I create a Linked Server to the old Database, and update the Synonyms accordingly upon migration.

Other than having to manage a ton of DNS entry one-by-one, I still consider it a way to relieve some stress related to database migration, being able to do it by small chunks instead of a whole server in one shot.

Now, what are the caveats that I don't see? Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever. Would Kerberos be an issue?

Thanks in advance

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Technically, now, one of the drawbacks is that they aren't going to change their connections strings... when, say, they should be adding in connection specific keywords such as MultiSubnetFailover.

You'll also have a ton of DNS items to regulate, which sounds easy - and in general, it is. I do, though, tend to see it lead to network name duplicates though.

  This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

This is actually pretty common, especially in environments that use tons of 3rd party applications where the connection string information isn't known or licensing code prevents the renaming of a connection (ugh).

  Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever.

See above. Many of the companies I know have an alias policy for that reason. It's also how AGs work for the listener as it is a network layer indirection.

  Would Kerberos be an issue?

If you're using a CNAME, it'll just point to the actual host so you won't have any issues there. If you put in a new A record then it will cause an issue and you'd need to setup the SPN for the new A record. Otherwise, with a CNAME, the SPN should be the name of the host.


On MariaDB event scheduler doesn't run I'm using MariaDB 10.3.22 on debian buster; I've set up a query to cleanup some expired user session from a table; this query should run every 15 minutes; the query is correct (tested and working on another identical DB but based on MySQL) but the start event is not triggered.

What I checked so far:

Event scheduler is ON

MariaDB [(none)]> show processlist;
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
| Id  | User            | Host      | db   | Command | Time  | State                       | Info             | Progress |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
|   1 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge coordinator    | NULL             |    0.000 |
|   2 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   3 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   4 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   5 | system user     |           | NULL | Daemon  |  NULL | InnoDB shutdown handler     | NULL             |    0.000 |
|  37 | event_scheduler | localhost | NULL | Daemon  | 13949 | Waiting for next activation | NULL             |    0.000 |
| 411 | root            | localhost | NULL | Query   |     0 | Init                        | show processlist |    0.000 |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
7 rows in set (0.000 sec)

Event inserted and enabled

MariaDB [integrity]> show events\G;
*************************** 1. row ***************************
                  Db: integrity
                Name: DELETE_EXPIRED_SESSIONS
             Definer: phpmyadmin@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 15
      Interval field: MINUTE
              Starts: 2019-04-10 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.001 sec)

Event query seems to be ok

MariaDB [integrity]> show create event delete_expired_sessions\G;
*************************** 1. row ***************************
               Event: DELETE_EXPIRED_SESSIONS
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`phpmyadmin`@`localhost` EVENT `DELETE_EXPIRED_SESSIONS` ON SCHEDULE EVERY 15 MINUTE STARTS '2019-04-10 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `core_sessions` WHERE `core_sessions`.`expiry` <= NOW()
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci

Other things to check?

Thank you for any hint...

First rule of  *nix admin is: Logs are your friends

Looking at logfile:

2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] execution failed, user no longer has EVENT privilege.
2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost][integrity.DELETE_EXPIRED_SESSIONS] Access denied for user 'phpmyadmin@localhost' to database 'integrity'
2020-05-15 14:41:00 1108 [Note] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] event execution failed.

The user defined at Definer: phpmyadmin@localhost MUST be an existing user and with all privileges to access your database...

Fixed that, everything worked fine.


SQL to add zeros in front of existing data to meet field length How to add zeros in front of existing data of NationalityCode field to make the length 5 ?

SELECT LPAD(nationalitycode, 5, '0')
FROM...

See https://firebirdsql.org/refdocs/langrefupd21-intfunc-lpad.html
If you want to update nationalitycode:
UPDATE ...
    SET nationalitycode = LPAD(nationalitycode, 5, '0')
WHERE ...


Restoring RMAN backup in Oracle 9i to another server I'm trying to do a restore of a cold backup made with rman in oracle 9i, the question is that I managed to successfully restore the pfile and the controlfile but when I try to give:
RMAN> restore database;

Starting restore at [DATE]

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 11 devtype = DISK
RMAN-00571: ======================================================================= =============
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ======================================================================= =============
RMAN-03002: failure of restore command at [date]
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Physically the backup pieces are there but when I do:
list backup of database;
it does not show the missing ones.

I must clarify that the restore I'm doing on a different server, but I keep the same directory structure and the same SID as in the original.

Also I copy the backup rman in the same directory.

When this happens in Oracle 11g I simply launch:
catalog start with

But for 9i there is no such option.

Could you recommend me some solution, please?

The rman script that I use is the following:

shutdown immediate;
startup force dba;
shutdown immediate;
startup mount
run {
set command id to 'ORA9';
allocate channel t1 device type disk;
allocate channel t2 device type disk;
allocate channel t3 device type disk;
allocate channel t4 device type disk;
backup filesperset 1
format '/ Backups / FULL_% d_% u'
(
database
include current controlfile
);
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
alter database open;

If possible, it would help me a lot more, if you could clarify the similar restore procedure in case the backup is done with tape.

Thank you very much.

As I remember, in 9i when rman is doing backup "include current controlfile" then this controlfile does not contain info about currently processing backup. You can check it by restore this controlfile and issue command:

list backup summary;

You need to backup your controlfile after you do backup of datafiles.

Add backup current controlfile;  command before release channel t1;
Or configure autobackup of controlfile - I like this option more.

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/Backups/%F';


Amount of time FULL VACUUM/VACUUM I have a table "lng_grid_test", which, at the beginning, had around 600 000 000 around. The table is defined as :

-- Table: public.lng_grid_test

-- DROP TABLE public.lng_grid_test;

CREATE TABLE public.lng_grid_test
(
  gid integer NOT NULL DEFAULT nextval('lng_grid_test_gid_seq'::regclass),
  geom geometry(LineString,4326),
  CONSTRAINT lng_grid_test_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.lng_grid_test
  OWNER TO postgres;

-- Index: public.lng_grid_test_idx

-- DROP INDEX public.lng_grid_test_idx;

CREATE INDEX lng_grid_test_idx
  ON public.lng_grid_test
  USING gist
  (geom);

I applied to delete request to this table, reducing the rows to around 490 000 000.
Before processing any further, I thought it would be accurate to execute a vaccum.
So I am currently running a vacuum,

INFO:  exécution du VACUUM sur « public.lng_grid_test »
INFO:  « lng_grid_test » : 3588177 versions de ligne supprimables, 485535819 non supprimables
parmi 5042516 pages
DETAIL:  0 versions de lignes ne peuvent pas encore être supprimées.
CPU 106.20s/230.72u sec elapsed 402.26 sec.

Do you think I should stop the full vacuum process and just chose the vacuum with analyze and freeze options or should I keep running the vacuum? And, if you think I should keep on running the process, what may be the order of time it will take to finish the vacuum?
Because I can't work on the table and I don't have a clue about how many time it will take to finish the vacuum.
Thank you!

You should never stop a vacuum process, ever.

A vacuum full will completely recreate the table, so it should take roughly as long to run as it would to create and populate a brand new table and index it with all 490_000_000 records.


how to execute sql inside a column in postgres I am trying to execute SQL statements that exist within a column of a database table.

The records of this table has 'tests' in each row.

sid  name     sql
1    test1    SELECT source_system_name, Count(*), CASE WHEN Count(*) BETWEEN 500 AND 2000 THEN true ELSE false END AS valid FROM prod.listing WHERE source_record_status = 'active' AND modified_on BETWEEN %L AND(%L - interval '24 hours') GROUP BY source_system_name;
2    test2    SELECT source_system_name, ....;

the output from the SQL inside sql column generates 3 columns - source_system_name, count, and valid.
Whenever function validation() is run I want to output the result - when valid = false

I wrote code for the innermost logic i.e. whenever there is false in value column.

CREATE OR REPLACE FUNCTION validation()
   RETURNS text AS $$
DECLARE
 show_log TEXT DEFAULT '';
 rec_val RECORD;
 cur_val CURSOR
  FOR SELECT   source_system_name,
         Count(*),
         CASE
                  WHEN Count(*) BETWEEN 500 AND 2000 THEN CAST(true AS BOOLEAN)
                  ELSE CAST(false AS BOOLEAN)
         END AS valid
      FROM     prod.listing
      WHERE    source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND     (
                        '2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name;
BEGIN
   -- Open the cursor
   OPEN cur_val;

   LOOP
    -- fetch row into the rec_val
      FETCH cur_val INTO rec_val;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;

    -- build the output
      IF rec_val.valid =  false THEN
         show_log := rec_val.source_system_name;
      END IF;
   END LOOP;

   -- Close the cursor
   CLOSE cur_val;

   RETURN show_log;
END; $$

LANGUAGE plpgsql;

I am a bit confused as in how to use this logic for all the statements in sql column.

You don't put the condition inside the procedure, you call a procedure only when the condition is true. The calling context should look something like

SELECT id, name, rs.*
FROM tests AS t
CROSS JOIN LATERAL myDynamic3ColExec(sql) AS rs
WHERE EXISTS (
      SELECT 1
      FROM     prod.listing
      WHERE    t.name = listing.source_system_name
      AND      source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND                        ('2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name
      HAVING Count(*) BETWEEN 500 AND 2000
);

Now you have to create a type, and create a function that returns the type given a sql statement as text.

CREATE TYPE mytype AS ( sid int, name text, sql text );

CREATE FUNCTION myDynamic3ColExec(sql text)
RETURNS setof mytype
AS $$
  RETURN QUERY EXECUTE sql;
$$ LANGUAGE plpgsql
VOLATILE;


Table old vs new value on merge? Say I have a table with two columns, new, old.
Whenever a row is inserte for the first time, new will be set and old should default to null.
Whenever a row is updated, the new should become the new value, and old should become the replaced value of new.
How to do this using Oracle?

Like this, I presume.
SQL> create table test (id number, old number, new number);

Table created.

SQL> insert into test (id, new) values (1, 100);

1 row created.

SQL> select * from test;

        ID        OLD        NEW
---------- ---------- ----------
         1                   100

SQL> update test set old = new,
  2                  new = 200
  3    where id = 1;

1 row updated.

SQL> select * from test;

        ID        OLD        NEW
---------- ---------- ----------
         1        100        200

SQL>


Why can't I read my transaction log backup file using fn_dump_dblog? I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.

After backing up the transaction log, I attempt to read it with:

SELECT COUNT(*) FROM fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'D:\TEMP\test_tlog2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

I get the following error:

  Msg 615, Level 21, State 1, Line 1
  Could not find database ID 0, name '0'. The database may be offline. Wait a few minutes and try again.

The database id isn't 0, it isn't offline, I don't see any params I'm missing or changes to the function. I can query the database and I can view the tlog backup file from within SSMS backup using the contents option, shows correct media set 1.

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)   Aug 23 2012 15:56:56   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Am I missing something fundamental?

@Alan Please check once by disabling the audit.

  That server has an AUDIT on a database. If the audit is enabled, you
  will get that error.  If you disable the audit,query will run fine.


Why configuring automatic failover for SQL availibility group feature on sql failover cluster instance not allowed I have set up an availability group where a primary is standalone server and replica is SQL failover cluster. This setup allows me only manual failover and when ever I try to set it automatic in FCI instance it says, this is not allowed on FCI instance.

Can anybody tell me the exact reason why it is not allowed or if its allowed please tell me the steps.

Automatic failover is not allowed if availability replica is hosted on SQL Server FCI. Quoting MS Arvind Shyamsundar

This is simply because the SQL Server team did not wanted to add more complexity to failovers scenarios. You already have automatic failover provided in SQL Server FCI and introducing automatic failover with availability group in FCI would make it too complex and may result in unwanted situations.

If you go ahead and try to configure automatic failover you would get below error message. Copied from this blog

  Msg 35215, Level 16, State 17, Line 1
  The Alter operation is not allowed on availability replica 'INST1',
  because automatic failover mode is an invalid configuration on a SQL
  Server Failover Cluster Instance.  Retry the operation by specifying
  manual failover mode.

In the same blog you can see Arvind Shyamsundar writing

  This is expected and by-design, the logic being that the FCI already has automatic failover configured between the nodes. The current implementation of AG doe not allow a failover outside of the FCI nodes.


About audits in Oracle 11g I need to do the auditing in database, but different the normal process that administrator use, (active audit in first installation or alter ) I don't have time window for this.

My scenario:
tree users with select grant just. But, for others users in same place, using the global user whit delete, update grant.

And I don't simply change the grant these users, because others applications use in font the same user and password.

Exist other form that I take a report with , what each one user does in database?

You're right, excuse any errors or agreement error.

My question is a concept so much, but explain.

I have users who have their logins, but there is a login that has some privileges that also and the same User used in important processes in the environment and is in source code.

At one point in the past knew the password for this login, and the more I say to access the environment with their respective login and password in some cases, they use this login with privileges.

As I have not, by default, a native audit Oracle configured in my environment, I wonder if through tables and views I can map out what such User made using a PL SQL DEVELOPER.
The worst and I have no time available to make this change, let offline the database, adjust and available.


Close all connections to postgres without having a connection I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague  log in to run some manual queries and forget to log out, my script will fail

FATAL: too many connections for role

I need to run some command within either crontab or the script itself to close any connections for my user before attempting to run my script.

Something like

select pg_cancel_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();

isn't helpful because to do so, my script would have to be able to make the connection.

Any option that doesn't require being logged in already or being an administrator of the db should work as the script should take priority over anything and the only users of this role are users that should defer to the script running on time.

Thanks.

PostgreSQL doesn't offer anything like that. You need to keep a second connection slot spare, or have a way to request termination via a side channel provided by the server host; say a web page that runs pg_terminate_backend as superuser to kill your connection.

Alternately, you can find and kill the client side of the connection if you have local access to the client host.

A final, albeit somewhat cumbersome, option is to run a pgbouncer instance. Always connect to the pgbouncer, and have the pgbouncer instance connect to the main DB. You can then restart the pgbouncer if you have issues.


Postgres: set client encoding default When querying from certain tables, I'll often receive the error:

character with byte sequence 0xef 0xbf 0xbd in encoding "UTF8" has no equivalent in encoding "WIN1252"

Varying bytes, but you get the idea. I can  solve this by doing:

set client_encoding to 'UTF8';

But the (very) annoying thing is, I have to do this client_encoding set every. Single. Time. I open postgres. How do I set this to default?

Also, I am not asking how to change the encoding of a table's data, or of databases I create from now on--that's a different issue.

You can also set it as a default for any user or role (takes effect after a new connection is established):

ALTER ROLE <rolename> SET client_encoding = 'UTF8';


Memory Settings for SQL Server, SSAS, and SSRS I started a new job about 4 months back and was surprised to learn that there has NEVER been a SQL Server DBA here, just developers who don't really know what they're doing (like me). I am not a DBA by any means but I've convinced my employer to send me to a DBA course in a few months after noticing really poor performance of our SQL Server instance. Until then, I was hoping someone could help me configure the memory settings on our server. Our server has 56.0 GB of RAM and it is running SQL Server, Analysis Services, and Reporting Services.

Currently the server is sitting at 98% memory usage with SQL Server, SSAS and SSRS using roughly 50GB, 1.5 GB, and 0.5GB respectively.

SSRS is hardly used, only running about 15 or so legacy reports once a day, every day. SSAS is about 1/3 as busy as SQL Server and is definitely not doing work all day like the SQL server instance.

Additionally, the SQL Server has the following memory settings:

Minimum server memory: 0
Maximum server memory: 2147483647

the SSAS Server has:

Memory \ HardMemoryLimit:0
Memory \ LowMemoryLimit: 65
Memory \ TotalMemoryLimit: 80
Memory \ VertiPaqMemoryLimit: 60 (we are using the mulitdimensional model, not tabular)

and the reportserver.config file has:

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>

I have adjusted the Maximum server memory setting based on the following recommendation I got from somewhere:

  To prevent Microsoft SQL Server from consuming too much memory, you
  can use the following formula to determine the recommended maximum
  server memory: (1) Reserve 4GB from the first 16GB of RAM and then 1GB
  from each additional 8GB of RAM for the operating system and other
  applications. (2) Configure the remaining memory as the maximum server
  memory allocated for the Microsoft SQL Server buffer pool.

but do i need to possibly lower this with SSAS and SSRS on the server too?
Can you recommend values for SSAS and SSRS too?
Oh, all instances are the 2012 version.

Thank-you.

we are not using the mulitdimensional model, not tabular

Do you mean NOT multi-dimensional ONLY TABULAR?

If so:
It's not best practice to install SSAS and SQL Server together on one server as the SSAS (Tabular) VertiPaq Engine works totally different than SQL Engine and it's (VertiPaq) is more memory intensive unless the models configured as DirectQuery mode. You could may have different VM for each of them if the license covered physical server cores

If not so:
I don't think that is practical as you must select either option TABULAR/MULTI-DIMENSIONAL during SSAS Installation. if you mean, there is no use of either SSAS options, you could simply uninstall it from the server

Yes, it's recommended to configure maximum server memory setting when there are multiple instances on the same server (other applications on database server not recommended), depending on the instance workload you could set max server memory setting, leaving minimum ~4GB to OS. I have seen ~16GB memory (for SSRS dedicated server) sufficient to handle around 150 users requests

Consider following settings are same as maximum server memory in SQL Server

In SSRS: WorkingSetMaximum
In SSAS: TotalMemoryLimit


This is just a guess on my behalf, it is not clear to me how you define highest row and next operation:

select ProdId, max(OprNum)
from (
    select ProdId, max(OprNum) as OprNum
    from ProdRoute pr1
    where not exists (
        select 1
        from ProdRoute pr2
        where pr1.ProdId = pr2.ProdId
          and pr1.OprNum = pr2.OprNum
          and SetupTime + ProcessTime > 0
    )
    group by ProdId
    union all
    select ProdId, min(OprNum) as OprNum
    from ProdRoute pr1
    where exists (
        select 1
        from ProdRoute pr2
        where pr1.ProdId = pr2.ProdId
          and pr1.OprNum = pr2.OprNum
          and SetupTime + ProcessTime > 0
    )
    and not exists (
        select 1
        from ProdRouteTran prt
        where pr1.ProdId = prt.ProdId
          and pr1.OprNum <= prt.MaxOpCompleted
    )
    group by ProdId
) as t
group by ProdId;


Update Statistics with full scan for a single table My application search becomes slow some times in a day.And whenever i update statistics for a particular table,it gets fixed.
So i am thinking of creating a job to  run update statistics every morning by 11 am for that table alone.Currently the statistics is set to update by itself with SQL server default sampling. Is it a bad idea to schedule a job to run Update statistics with full scan for that one particular table.This issue happens in production environment.

While you could schedule a job to run UPDATE STATISTICS on that particular table, you should also look into Parameter Sniffing. What's likely happening here is that a bad plan was wiped from the plan cache when you updated statistics on that table. Instead of doing a costly UPDATE STATISTICS, remove the bad plan from cache. Once wiped from the plan cache, the query will compile and get a new plan. Whichever parameter values were used at the time of compilation is what the execution plan will be optimized, that is as long as you are not overriding that behavior with the OPTION OPTIMIZE FOR query hint.

If the search function is a stored procedure, you can use the good ol' sp_recompile. Otherwise, use DBCC FREEPROCACHE and pass that command the SQL handle or the plan handle. You can use sp_BlitzCache with ExpertMode = 1 to get the command (it's all the way to the right in the result set).

To help diagnose which plan is causing the issue, I like to use sp_WhoIsActive or sp_BlitzCache. For sp_WhoIsActive, I like to log current activity to a table and then I can go back in time to compare execution plans and see who was the culprit.

For more information on Parameter Sniffing, see Erland's Slow in the Application, Fast in SSMS? post.


Cannot connect to Oracle database server through SQL Developer but Test works OK I am new to Oracle (from a SQL server and MySQL background) and have just installed Oracle 11g2 Express Edition.

I have downloaded SQL developer which appears to be the Oracle world's equivalent to SSMS.
I have gone to the new connection window, given my connection a name, used SYSTEM as the user and my password that I set up at installation.
I have left hostname/port/sid as default (localhost/1521/xe respectively)

If I click test I get "status: success" However, if I click the connect button, nothing happens.

What am I missing here?

When you connect from the connection properties dialog, two things will happen in the UI:

the connection icon gets a 'connected' overlay
you get a SQL Worksheet***

If your connection is not successful, you'll get an error message, with the text shown at the bottom of the connection properties window.

***For #2 above, this is conditional on the following preference being enabled, which the default is TRUE.

'Open a Worksheet on connect'


Is there a way to allow only DELETES from CASCADES only? I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?

For that you can use Foreign keys (referential integrity constraints):

REFERENCES orders ON DELETE CASCADE,

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row.

See the manual
I explain it a bit better.
You have a parent table A with an id, and you have a second table B, which has a reference to the id in Table A with CASCADE DELETE.
As the check constraints don't support referencing other tables, you can use a  trigger with a function:
CREATE FUNCTION delete_tabentry () RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_child
  BEFORE DELETE ON child
  FOR EACH ROW EXECUTE PROCEDURE delete_tabentry();

This would prevent deletion from child until its parent record is deleted.


ORA-44001: invalid schema I have an Oracle database dump which contains the dump of three schemas for e.g. alpha bravo charlie in one file, exported through EXPDP.

Now when I am trying to import these with the following command:

IMPDP alpha/alpha FULL=Y DUMPFILE=alpha06022014.dmp LOGFILE=alpha.log;

...after a couple of lines I get this error which is probably because I am supplying the credentials of only one schema in the command?

...
...
...

ORA-31684: Object type VIEW:"alpha"."table1" already exists
ORA-31684: Object type VIEW:"alpha"."object" already e
xists
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDL [VIEW:"bravo"."
VW_NDC_CONFIG_REPLY"]
ORA-44001: invalid schema

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6273

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
25BA9BC0     14916  package body SYS.KUPW$WORKER
25BA9BC0      6300  package body SYS.KUPW$WORKER
25BA9BC0     12689  package body SYS.KUPW$WORKER
25BA9BC0     11968  package body SYS.KUPW$WORKER
25BA9BC0      3279  package body SYS.KUPW$WORKER
25BA9BC0      6889  package body SYS.KUPW$WORKER
25BA9BC0      1262  package body SYS.KUPW$WORKER
25A03A14         2  anonymous block

Job "alpha"."SYS_IMPORT_FULL_02" stopped due to fatal error at 11:26:41

D:\oracle\product\10.2.0\client_1\BIN>

If yes, then how can I import all three schemas?.

To import data into a different schema you need to grant IMP_FULL_DATABASE role to the user. Oracle MOS Doc ID 351598.1.

Or you can use SYS user:

impdp \'/ as sysdba\' FULL=Y DUMPFILE=alpha06022014.dmp LOGFILE=alpha.log


Query to get the users who have a application approved I have two tables Users and Submissions . Need to find the query to get the users who have submitted atleast one grant and have never won .

I can write a subquery and achieve it .But I would like to know if there is an efficient way ?

Users
ID | name | email | registered | etc

Submissions

ID | userID | status | eventID | etc

Now Submissions can have multiple entries of users corresponding to different event. A user can have a status as approved ,rejected .

How do I write the MySQL query to fetch the users who have atleast one submission but never approved .

You can join the same table twice, once to check that there exists some submission (preferably a rejected one) and second time you left join the same to get any approved submission - then you can check if the left join found something (and remove those - where approved_table.id is null) - to get only one row per user you can do a group by Users.ID.

select users.* from users
inner join Submissions exist_grant
    on users.ID = exist_grant.userID
left join Submissions approved_grant
    on (approved_grant.status="Approved" and users.ID = approved_grant.userID)
where approved_grant.userID is null
group by users.ID


Microsoft SQL Server error 14545 I was trying to use copy Database wizard from one server to another to make a certain database identical and i got the error

  (The @flags parameter is not valid for a job step of type 'SSIS'. (Microsoft  SQL Server, Error: 14545)).

Some info:

The Source Server is SQL Server 2008 R2 and the destination is SQL Server 2016.
On Transfer Method I selected the second option (use the sql management object method) to keep the source database online.

Any advice on how to solve this or where to check will be appreciated.

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase]
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out
https://dbatools.io/functions/backup-dbadatabase/
&
https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server where you already have the databases on, I would suggest restoring the db's to a different name and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.


I want to get first,second and n th occurent of value in sql database? I would like to count Place  occurrences of values for attribute in each row:

id  | attribute      |Occurence

1  | ICT              1   |the first time of ICT
2  | ENGINEERING      1   |the first time of ENGINEERING
3  | ICT              2   |the second time of ICT
4  | ENGINEERING      2   |the second time of  ENGINEERING
5  | ENGINEERING      3   |the Third time of  ENGINEERING

You can use ROW_NUMBER to count it. Here is more about this

CREATE TABLE Table1
    ([id] int, [attribute] varchar(11))
;

INSERT INTO Table1
    ([id], [attribute])
VALUES
    (1, 'ICT'),
    (2, 'ENGINEERING'),
    (3, 'ICT'),
    (4, 'ENGINEERING'),
    (5, 'ENGINEERING')
;

SELECT
  id,attribute
  ,ROW_NUMBER()OVER(PARTITION BY Attribute ORDER BY id) as Occurence
FROM
  Table1
ORDER BY id;

output:

id  attribute   Occurence
1   ICT         1
2   ENGINEERING 1
3   ICT         2
4   ENGINEERING 2
5   ENGINEERING 3

dbfiddle here


multiple indexing issue I used Database Engine tuning advisor for  indexing a table and when I am checking indexing  , it showing multiple indexing on same table . as shown in
fig

I am wondering for these question  as follows :-

what will be the effect if i delete the blue highlighted indexing  because it and indexing below it has 2 columns common .
Any effect on the performance ??
why multiple indexing ??
4 . any other suggestion , other then delete ..

thanks in advance......

DTA is a good thing but you should always make the final decision yourself.
You should not blindly believe what DTA or SQL Server Missing indexes suggests you regarding indexes as they do not take into consideration many performance aspects such as update/insert intensity on the the table, they only consider performance enhancement of the certain query/table regardless of the overall workload.

So the answer to your question will be:

This has happened because DTA considers these indexes will improve performance of queries hitting this table.
Over-indexing any table will result in degraded performance on update/insert/delete operations, in more storage used, in longer backup, checkdb, index maintenance operations. It will potentially lead to more intensive use of RAM. As you see - excessive indexes have a lot of drawbacks.

The answer to 3 and 4 - read through the articles recommended below and make sure you understand your workload (read/write activity) and how indexes operate.
It is also a good practice to test such things first and apply in production if necessary.

SQL101: Indexing Basics by Kimberly Tripp
Are your indexing strategies working? (aka Indexing DMVs) also by Kimberly Tripp
SQL Server Index Design Guide in the TechNet Library


Best method of creating constant value I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query).

What would be considered the best way to make that value discoverable outside of the single query it is being used in?

A UDF immediately jumps to mind, but prior to 2019 it kills performance (don’t have 2019 so I can’t verify it, but it seems inlined UDFs should fix the performance problem).

A view or TFV might be a good alternative, but both seem overly complex and a misleading.

I don’t have a table/view of constants/lookup values.

Is there a good solution for this situation or just stick with comments in the relevant code?

Scalar UDFs don't have to kill performance when they return a constant value. You can save off the function value to a local variable or parameter and use that variable in your query

CREATE FUNCTION dbo.RETURN_SPECIAL_VALUE() RETURNS INT
AS
BEGIN
    RETURN 1;
END;

GO

DECLARE @special_value INT = dbo.RETURN_SPECIAL_VALUE();

-- example query that uses special value
SELECT TOP (5) @special_value, number
FROM master..spt_values;

On the other hand, if the constant is used in the WHERE clause the estimates may be better using the literal. Unless an OPTION (RECOMPILE) is used, which may be excessive.


Downgrade SQL Server Enterprise Edition to SQL Server Standard Edition There is a requirement in our project where they want to evaluate if it is possible to downgrade SQL Server from Enterprise to Standard edition and if a server can be decommissioned.

How can I identify which servers are suitable for downgrade?

Kendra Little did a great article on this: http://www.brentozar.com/archive/2014/08/sql-server-edition-change-standard-edition-enterprise-evaluation/

Microsoft article on supported upgrade paths:
http://msdn.microsoft.com/en-us/library/ms143393.aspx

First, note that not all downgrade scenarios are supported (You didn't provide the exact version you are running).

Second, run this query to check if there are any Enterprise features in use (those features will not work when you downgrade).

SELECT * FROM sys.dm_db_persisted_sku_features

Third, if your downgrade isn't supported (it doesn't seem like many are), then you are essentially going to have to do a fresh install with standard edition, then migrate to the new server.


SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS I have found this line of code online

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv"

This is used in a SQL job, to export to a CSV file.

There is no SSIS installed, I cannot install SSIS on the machine.

This is what is exported

name,database_id,source_database_id,owner_sid,create_date,compatibility_level,collation_name,user_access,user_access_desc,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,is_in_standby,is_cleanly_shutdown,is_supplemental_logging_enabled,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_update_stats_on,is_auto_update_stats_async_on,is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,is_ansi_warnings_on,is_arithabort_on,is_concat_null_yields_null_on,is_numeric_roundabort_on,is_quoted_identifier_on,is_recursive_triggers_on,is_cursor_close_on_commit_on,is_local_cursor_default,is_fulltext_enabled,is_trustworthy_on,is_db_chaining_on,is_parameterization_forced,is_master_key_encrypted_by_server,is_published,is_subscribed,is_merge_published,is_distributor,is_sync_with_backup,service_broker_guid,is_broker_enabled,log_reuse_wait,
log_reuse_wait_desc,is_date_correlation_on,is_cdc_enabled,is_encrypted,is_honor_broker_priority_on
----,-----------,------------------,---------,-----------,-------------------,--------------,-----------,----------------,------------,----------------,-----------------,-----,----------,-------------,-------------------,-------------------------------,------------------------,-----------------------------,-----------------------------,--------------,-------------------,------------------,-----------------------,-----------------------,-----------------------,-----------------------------,-----------------------,----------------,------------------,-------------------,----------------,-----------------------------,------------------------,-----------------------,------------------------,----------------------------,-----------------------,-------------------,-----------------,-----------------,--------------------------,---------------------------------,------------,-------------,------------------,--------------,-------------------,-------------------,-----------------,--------------,
-------------------,----------------------,--------------,------------,---------------------------
master,1,NULL,0x01,2010-04-08 09:13:36.390,100,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,1,ON,0,3,SIMPLE,2,CHECKSUM,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0xxxx0-0000-0000-0000-000000000000,0,0,NOTHING,0,0,0,0
tempdb,2,NULL,0x01,2014-09-15 08:39:08.587,100,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,0,3,SIMPLE,2,CHECKSUM,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,Exxxxx7-BE30-4829-8AB1-DAA8730978AE,1,0,NOTHING,0,0,0,0

Is there anyway to amend the command in order to exclude the line below the heading please, I need the heading, but they can't accept the line below the heading (the line starting with ----,--)

Unfortunately, there is no way directly from BCP to get the header as well as the rows out in a pretty format, even with the out parameter

HOWEVER, there is a workaround. You can do this:

SELECT 'HeaderCol1Name', 'HeadCol2Name'
UNION ALL
SELECT CAST(C.Col1 AS VARCHAR(255)), CAST(C.Col2 AS VARCHAR(255)) ...
FROM (SELECT <Your original query>) AS C

You can now:

BCP <above query in quotes> out Foo.txt

Yes, it is ugly. And yes, it works. You can even control things like date formatting this way.


Notifications database design So I am in the process of updating an existing notifications database design AS it wouldn't be optimal for storing group chat notifications.

Notifications:

id
user_id
type (enum)
thread_id (used for chat notifs)
contact_request_id (used for linking contact requests to a notif)
created_at
read (whether they read it or not)

At the moment, for each contact request, we would need to store 2 rows, for every single message, we would need to UPDATE 2 existing rows (each user has there own notif)...this is the main reason I want to find an optimal solution! Because when we add group chats, storing and updating 2..* rows will get ugly, and I can see data getting out of sync quickly.

I have thought of 3 solutions:

We create 1 notification AND link the users to that notification in another table (notification_users)
We create a seperate table for message notifications. So that we can facilitate for multiple users per notif?!

If you have to know read status for each notification, use separate table similar to this:

CREATE TABLE notification_read_status (
  notif_id bigint not null references notifications,
  user_id bigint not null references users,
  read_when timestamptz not null default now(),
  primary key (notif_id, user_id)
);

The boolean is not needed here. Existing row means true, missing row means false.

Possibly, if this is a very large scale app, and if you can simplify the model, then you can save some resources:

notification ID will be a monotonic bigint never going down and
store only one the last ID read (which means all previous notifications for given user are also read) - this will be just one field in users table.


Move database without replication taking new snapshot We are upgrading a physical server we have SQL installed on at work, and I need to move SQL to a new machine, but I need to do it with minimum downtime.

I have the new virtual server already available to me, so I was going to setup log shipping from the Old -> new server to handle the actual move of the database(s) -- The server also has replication coming from a different server (the database I am moving is the subscriber).  How could I get the replication moved from the old to new without having to re-initialize replication?

Have you considered setting up a subscription on the upgraded / new server and then repointing whichever application at the new subscriber database overnight?


Badly performing query plan after adding ORDER BY to Full Text CONTAINS search The following query is proving beyond my abilities to fix - hope someone can help.

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY InsertDate desc

Without the ORDER BY clause, the query performs fine (< 2 seconds). But when I add the ORDER BY, performance is awful (> 2 minutes).

The problem seems to be an incorrect query plan.

The query plan that performs well is this:

The query plan that performs badly is this:

IX_Foo_InsertDate is defined as: CREATE CLUSTERED INDEX [IX_Foo_InsertDate] ON [Foo] ( [InsertDate] DESC )

What is causing the change in query plans? How can I fix it?

The optimizer is choosing the different plan because it thinks the benefit of having the pre-sorted InsertDate (using IX_Foo_InsertDate) outweighs the cost once you add the order by clause.

The optimizer cannot be perfect, it might choose a slower path even if statistics are accurate - but it will certainly make poor choices if the statistics are not accurate - are AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on?

If necessary, you might be able to 'trick' the optimizer like this:

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY some_function_of(InsertDate) desc

where some_function_of(InsertDate) has the same order as InsertDate


MySQL Workbench Synchronization False Positives I have synchronized a table between a model and a live server database in Workbench, but every time I rerun the "Synchronize With Any Source..." wizard, Workbench detects (falsely) that there is a difference between the model and the server for that table and wants to execute the exact same SQL query.

The SQL in this case is the following:

ALTER TABLE `mydb`.`user_info`
CHANGE COLUMN `last_update` `last_update` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This statement has already been executed in my first run of the Wizard, and I can see that the table user_info is up to date on the live server. Why does it keep detecting it as a difference? Is it a known bug with MySQL Workbench or something...?

Probably a bug. Some of the change detection is not catching this specific case (or interpreting it wrongly). Best is to file a bug report (http://bugs.mysql.com).


Grouping rows by looking at two columns without considering the order and summing each separately I have a table looks like this one below:

+---------+---------+-------+
| SOURCE  | DEST    | VALUE |
+---------+---------+-------+
| A       | B       | 1     |
| B       | A       | 2     |
| A       | B       | 3     |
| C       | D       | 5     |
| D       | C       | 6     |
+---------+---------+-------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+---------+---------+-------------+-----------+
|         |         | SUM(SOURCE) | SUM(DEST) |
+---------+---------+-------------+-----------+
| A       | B       | 4           | 2         |
| C       | D       | 5           | 6         |
+---------+---------+-------------+-----------+

Does anyone has any idea about how can I get this?

(I am trying to do this on both SQL Server, and MySQL)

Related: Grouping rows by looking at two columns without considering the order

It's not pretty but it works.

SELECT * INTO YourTable
FROM
(
    SELECT 'A' AS [SOURCE],'B' AS DEST, 1 AS VALUE
    UNION ALL
    SELECT 'B','A',2
    UNION ALL
    SELECT 'A','B',3
    UNION ALL
    SELECT 'C','D',5
    UNION ALL
    SELECT 'D','C',6
) A;

WITH CTE
AS
(
    SELECT  CASE
                WHEN [Source] < Dest THEN [Source]
                ELSE Dest
            END col1,
            CASE
                WHEN [Source] < Dest THEN [Dest]
                ELSE [Source]
            END col2,
            SUM(Value) AS [SUM(Total)]
    FROM YourTable
    GROUP BY
        CASE WHEN [Source] < Dest THEN [Source] ELSE Dest END,
        CASE WHEN [Source] < Dest THEN [Dest] ELSE [Source] END
)

SELECT  A.col1,
        A.col2,
        SUM(B.VALUE) AS [SUM(SOURCE)],
        [SUM(Total)]-SUM(B.VALUE) AS [SUM(DEST)]
FROM CTE A
INNER JOIN YourTable B
    ON A.[col1] = B.[SOURCE]
    AND A.col2 = B.DEST
GROUP BY A.col1,A.col2,[SUM(Total)]

DROP TABLE YourTable

Results:

col1 col2 SUM(SOURCE) SUM(DEST)
---- ---- ----------- -----------
A    B    4           2
C    D    5           6


How do I reduce the logging of my procedure? I have a process that I run every night in our database to take some snapshots of various tables (not full table snapshots, just select record types).

Lately, it has been filling up the redo log and failing.
The statement is below.
Is there anything I can do to reduce the amount of logging this does?

BEGIN

  DELETE FROM DWH_STOCK_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SORD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SORDD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SOZD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_PORD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_PORDD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_POZD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SPRICE_STYLE_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SPRICE_CLRSKU_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SKU_COSTS_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;

  DWH_SNAPSHOT.TAKE_STOCK_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_SALES_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PURCHASE_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PRICES_SNAPSHOT();
  PDWH_SNAPSHOT.TAKE_COSTS_SNAPSHOT();
END;

As you are completely dropping all the data and rebuilding it, if there is any overlap in the new data and old (especially if there is a lot of overlap - i.e. many of the rows in each table will be the same after the process has completed) then it would be far more efficient to only delete rows that are no longer needed, insert rows that are new to this revised snapshot, and update rows that already exist and need to change.

This could be a lot more code, but it will potentially be much more efficient.

To do this in a DB agnostic way you will need up to three statements to delete, insert and update, wrapped in a transaction to avoid partial results. In many cases updates aren't needed and you only need the delete and insert phases - you will know if this is the case as you know your data better than I.

If portability is not an issue then Oracle supports a MERGE statement that will likely be far more efficient still and result in smaller, easier to maintain, code. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm for reference documentation, if that isn't clear you will likely find many examples/tutorials that flesh out the explanation further.


Multi-Subnet AlwaysOn: Is Routing and Remote Access always required? Context

I'm setting up a 2-node AlwaysOn Availability Group (SQL Server 2017 Enterprise) with the following setup:

Existing DC+DNS (One NIC: 10.20.11.1/255.255.254.0)
New AG-Node1 (One NIC: 10.20.11.2/255.255.254.0)
New AG-Node2 (One NIC: 10.22.11.1/255.255.254.0)

I've read that this would be considered as an "AlwaysOn Multi-subnet" scenario (since nodes belong to different subnets).

Question:

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

Yes. SQL Server isn't doing any "routing" at the network level, it's not a layer-3 device... However, if you don't treat it as a "multi-subnet cluster" then you can get yourself into some time-out causing situations. While the situations are easily rectifiable, why not save the trouble and set everything up the first time, then not have to go back and change things?

  I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.

I don't know why someone is telling you to setup RRAS... unless it's for some local repro environment, such as the one on my laptop that doesn't have any routing devices as it's all internal virtual machines. This doesn't make any sense unless you're using RRAS as the routing device (software router).

Either way, this is all network design and has nothing specifically to do with SQL Server.


SQL over partition -avoid repeated statement in for every column I have the follwoing SQL statement. Is there a way to avoid writing the OVER(PARTIION... statement multiple times for each line as it is identical in every line?

            SELECT
            DISTINCT
            LAST_VALUE (DSET_COB_DT)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_DSET_COB_DT,
            LAST_VALUE (SRC_HUB)              OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB,
            LAST_VALUE (TRD_SRC_SYS)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_SRC_SYS,
            LAST_VALUE (TRD_ID)               OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_ID,
            LAST_VALUE (NVL (TRD_VER,0))      OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_VER,
            LAST_VALUE (SRC_HUB_INGESTION_DT) OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB_INGESTION_DT,

            FROM DF_RISK_SIT2_OWNER.recon_ts_rs

Any suggestions would be appreciated to make the statement easier to read.

In standard SQL, you have a clause  where you can name the Partitioning option,but it is currently not supported by any RDBMS .You can see the connect item raised by Itzik Ben-Gan and vote for it

If implemented the Clause would look like this..

     SELECT empid, qty,
     SUM(qty) OVER W1 AS run_sum_qty,
     AVG(qty) OVER W1 AS run_avg_qty,
     FROM Sales.EmpOrders
    WINDOW W1 AS ( PARTITION BY empid
     ORDER BY ordermonth
     ROWS BETWEEN UNBOUNDED PRECEDING
     AND CURRENT ROW );


SQL Server Agent Job Step "Run As" dropdown When creating a job step in SQL Server 2008 R2 Agent, the third dropdown in the General page is "Run as:" - but this is empty, and seems to always be empty! What is it for?

Thanks

The "Run As" is so that you can specify a proxy account to run the particular step as.  The proxy account defines the security context that a job step can run as.


Update all tables containing column name In SQL Server, I can find all tables containing a COLUMN_NAME:

SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'COLUMN_NAME'

How can I update all of these columns like:

UPDATE <table> SET <column_name> = 123

The solution needs to work in SQL Server 2012 and newer. In my case the column will always be the same datatype: bigint

Do I need to use a CURSOR or similar?

This query will give you a list of your table names and the field name into a temp table.

IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max));

DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME';

INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, SQLScript)
SELECT
s.name
,o.name
,c.name
,'ALTER TABLE ' + s.name + '.' + o.name + ' SET ' + c.name + ' = ''123'''
FROM sys.columns c
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
JOIN sys.objects o
    ON c.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE c.name LIKE @FieldName
    AND o.type_desc = 'USER_TABLE';
    SELECT * FROM #BaseData

Results will look like this. The first three columns are not entirely necessary but I've included them to show how the data is put together;

Schema_Name Table_Name  Field_Name    SQLScript
dbo         Table1      Column_Name   ALTER TABLE dbo.Table1 SET Column_Name = '123'
dbo         Table2      Column_Name   ALTER TABLE dbo.Table2 SET Column_Name = '123'
dbo         Table3      Column_Name   ALTER TABLE dbo.Table3 SET Column_Name = '123'
dbo         Table4      Column_Name   ALTER TABLE dbo.Table4 SET Column_Name = '123'

You can then use a cursor to execute each line of your data;

DECLARE @sql VARCHAR(MAX)
DECLARE @SqlScript VARCHAR(max)

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT SQLScript
    FROM #BaseData;
OPEN c;
FETCH NEXT FROM c INTO @SQLScript;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @SqlScript

    EXEC(@sql);

    FETCH NEXT FROM c INTO @SQLScript;
END

CLOSE c;
DEALLOCATE c;

This will execute each line of code that you've previously created. The example code above will update all tables so that the value in Column_Name = '123'


Aggregate Query in SQL Developer Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks!

SELECT
    S.PRODUCT_ID,
    SUM(DISTINCT S.AWARD_AMT),
    SUM(DISTINCT S.EST_AMT),
    SUM(PMT.PMT_AMT),
    S.CREATE_DT,
    S.APPROVE_BY,
    S.EVALUATOR
FROM SALES S
    INNER JOIN INVOICE INV
        ON  S.PRODUCT_ID = INVS.PRODUCT_ID
    INNER JOIN PAYMENTS PMT
        ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID
            AND INV.SEQ_NO = PMT.SEQ_NO
WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014'
    AND INV.STATUS_CD = 'CREATE'
GROUP BY S.PRODUCT_ID
order by S.PRODUCT_ID

This was my final query. It worked. I had to remove the dups in excel

SELECT
    S.PRODUCT_ID,
    S.CREATE_DT,
    S.APPROVE_BY,
    S.EVALUATOR ,
    SUM(DISTINCT S.AWARD_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS S.AWARD_AMT,
    SUM(DISTINCT S.EST_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS EST_COST_TOT,
     SUM(PMT.LINE_AM PMT.PMT_AMT T) OVER (PARTITION BY OM.O_OMO_NO) AS TOTAL_PMT
FROM SALES S
    INNER JOIN INVOICE INV
        ON  S.PRODUCT_ID = INVS.PRODUCT_ID
    INNER JOIN PAYMENTS PMT
        ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID
            AND INV.SEQ_NO = PMT.SEQ_NO
WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014'
    AND INV.STATUS_CD = 'CREATE' and SUBSTR(APPROVE_BY,1,3) IN ('PEP','SMI', 'MOP','3/4')
GROUP BY S.PRODUCT_ID
order by S.PRODUCT_ID

ERROR msg:ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 91

Assume you have the following table TAB(name,value)

name value
a        2
a        3
a        2
b        4
b        5
b        5
c        6
c        6
c        6

what are the result sets of the following queries

query 1:

select name, sum(value)
from TAB
group by name
order by name

query 2:

select name, sum(distinct value)
from TAB
group by name
order by name

query 3:

select name, value
from TAB
group by name
order by name


Get a fixed value on a select I need to do a SELECT query where I get the value of the field "money".  The field doesn't actually exist in the database.  I just need the query to return this field with a fixed value; in this case a value in USD.

How do I return a constant value in a SELECT statement?

If the value is always going to be the same you can do something like this:

SELECT 'USD' AS `MONEY`, a.*
  FROM INFORMATION_SCHEMA.SCHEMATA AS a

Just replace a.* with the columns you want to return and INFORMATION_SCHEMA.SCHEMATA with the schema and table(s) you want to query.

I hope this is helpful to you.


how to optimise this group by on sys.dm_exec_query_stats? I would like to know the last 1008 queries run in my systems and with this intent I have put this query together:

-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [Date Time],
Database_Name=DB_NAME (execsql.dbid),
execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

and this is currently giving me the following result in one of my servers:

Looking at the picture, it seems that the same piece of code appear several times, within the same millisecond.

I modified the script and added a group by, however, the cost to run this query became too expensive.

-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [Date Time],
Database_Name=DB_NAME (execsql.dbid),
execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
GROUP BY execquery.last_execution_time, execsql.dbid,execsql.text
ORDER BY execquery.last_execution_time DESC

What could be a way to prevent the dupplicates to show up and at the same time not to kill my server when I try to run it?

You're getting multiple queries from within the same stored procedure but because you're just looking at execsql.text you're getting the whole body of the procedure. If you want to know the individual queries you need to extract those. (The solution is definitely not to try to group by the entire procedure body.) Try this query:

SELECT TOP (1008)
  execquery.last_execution_time AS [Date Time],
  Database_Name = DB_NAME (execsql.dbid),
  [Script] = SUBSTRING(execsql.text, (execquery.statement_start_offset/2) + 1,
    (( CASE execquery.statement_end_offset
        WHEN -1 THEN DATALENGTH(execsql.text)
        ELSE execquery.statement_end_offset END
            - execquery.statement_start_offset)/2) + 2)
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC;


how to extract difference between timestamps SQL greenhorn alarm:
I have a table with timestamp information (44 rows) and I now would like to calculate and extract the delta between any two consecutive rows. How can I do this? I select my data with a query like:

SELECT * from "table" where "RecognitionTimestamp" >= '2018-10-03 00:00:00' and "RecognitionTimestamp" <= '2018-10-04 9:00:00'

I'm using pgAdmin4 to submit the query and look at the data.

You can use Windows Functions which provides calculation across a set of table rows that are somehow related to the current row. The list of functions are in General-Purpose Window Functions section of the documentation and The lead() and lag() functions are answer of your question. Your query will should like this;

SELECT *,
  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
  lead("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") - "RecognitionTimestamp" AS delta_next
FROM "table"
WHERE
  "RecognitionTimestamp" >= '2018-10-03 00:00:00' AND
  "RecognitionTimestamp" <= '2018-10-04 9:00:00'


Does AWS ever `vacuumlo` PostgreSQL RDS instances? Link to the long story happened before (and reason to have) this question.

Does AWS ever vacuumlo PostgreSQL RDS instances?

Probably not, because blindly executing vacuumlo on random databases would be foolish.

Unlike vacuum that is pretty much unavoidable on a normal live database, vacuumlo fixes an oversight, a situation that should not arise in the first place, since applications should unlink the large objects that they no longer use, at about the same time that they delete the references to these large objects.

In a database with a large number of large objects that are properly handled, vacuumlo would burn CPU cycles and I/O at each invocation, just to compute every single time that there is nothing to remove.

Moreover, there are two assumptions that vacuumlo must do that are debatable:

the fact that any column of type oid should be taken as a candidate to a large object reference. oid are used for other purposes, especially if you want to refer to objects in the catalog.
the fact that, if a large object doesn't have it oid refered in some table in the same database , it should be removed. If the reference is in another database, or in a file, or in the same database but embedded in a text column, that logic doesn't work and is in fact dangerous.


Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task Here's my predicament:

The version on the server is SQL Server Express. And no, we cannot use any other version. (Long story, but: vendor will not allow without $ome...)
From 1, you can deduct that there is no SQL Server Agent jobs.
Hence the need to automate SQL Server backups "creatively".
We did so years ago, and it was working perfectly for years (more than 2).
What did we do? We created the command string sqlcmd -U SQLBackupOper -P <password> -S GSUDM -Q "EXEC sp_BackupDatabases".
Yes, sp_BackupDatabases is a stored procedure in the Master database that backs up all DBs.
Yes, if I login to the server and open an elevate (as administrator) command prompt and run the command in number 4, it works perfectly. So, the password is correct. I copied and pasted several times to make sure, and no, the problem is not the password.
Yes, the login SQLBackupOper is mapped to all DBs and has the role membership of db_backupoperator.
We also created a task in the Windows Task Scheduler that is meant to run the command string in number 4, and it worked for years. Now, for some indeterminate time (I am not sure how long) the task is failing. When I look at the SQL Server error logs I am greeted with the error that reads Login failed for user 'SQLBackupOper'. Reason: Password did not match that for the login provided.
Yes, the password in the command string is correct. I tested it from the command prompt via copy and paste. Again, it works from the command prompt. It only fails if run from the task scheduler, the same exact command string.
Yes, I did try varying the user that runs the job. Still failing.
Yes, I do run the task with the highest privileges.

With that said, has anyone faced this problem before? Again: It works from an elevated command prompt, but it complains when using the same command string citing wrong password when run as a task in task scheduler. Thoughts? Suggestions?

Thanks guys I resolved the problem. Although I'm not sure what I did that resolved it. What I did:

Created a brand new SQL Server login with sysadmin rights (I know it shouldn't need that much, but hey, I needed to fix this.)
Added a new local user login in users and computers MMC in the SQL Server hosting the machine, and made it a local admin to the machine.
Updated everything to use those users, instead of what we had before, and suddenly it started working.
So, it's something related to permissions, most likely for the SQL Server login. But still, as far as I can tell the initial user had everything it needed. So much so that I could confirm that to be the case both using a command line as well as a batch file.

Thanks for trying to help. Closing this here. Best, Raphael.


SQL Server 2005 and Standalone VB application I'm new to this forum and just want to ask some questions/suggestions on how to manage my database..

I'm having an application with sql server 2005 database as a backend.
I want make the application standalone, the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers.
I have my app working fine, but the database is not secure if ever the app is installed on other computer.

Now here's the scenario. (if the user is a techie guy and wants to dig things up)
The user installs sql server 2005.
The user installs the app.
The user can see the database via windows explorer (database is .mdf file)
Opens up SQL Server, uses windows authentication.
Attaches the database, and tada! Instant access to the database.
Now he can freely modify the contents of the database without the use of the app.

So how can I solve this? Is there any possible ways to avoid this? Any suggestions?
Take note that I can't control the application because it is installed on a different place
P.S., I'm quite new to this, actually this is my first project on a company :)

So there a couple thoughts here from my end. And I think they probably follow an order:

The first principle is - you will never secure your MDF file from being looked into by someone with SQL savvy. There are ways around most of what you can do. If you are deploying this application to a customer, and that customer has enough SQL knowledge and you've given them a data file - they can do what they like with it ultimately.. This principle rules all of the others.
Support contracts and license agreements protect you legally here - that is outside the scope of this format. But you have to trust the folks buying your app to honor their support agreements and EULAs. Every vendor out there has to do that same thing.
You can, however, do things like encrypt data in tables and encrypt/decrypt it in and out from your application. This can help protect data, but it isn't foolproof. Someone can try and attach a debugger, dig into the traffic and try and get a sense for what is going on.
You can encrypt your stored procedures to protect the stored procedure code - again this isn't full proof (remember Principle #1 rules them all here)

So there are things you can do, but the biggest question that I would ask if I were you is:

Why?

What I mean is - what is your goal? Customers see databases all the time. In fact as a DBA and Consultant with 15 years of experience, I can honestly say that 90% or more of the databases I work with are wide open to the folks who purchased the application. If they want to, they can certainly play with the data in their database directly. Most of them don't because they like support contracts to remain valid. Some of them do because their vendors are pains to work with and don't give common sense support and updates.

So you should check your motives. If you've made software that someones wants to buy, and they've paid for it and deploy it locally following your instructions and have entered into an agreement with you - why would they want to build their own app? And if they do? What is the harm? Charge enough money and make your agreement strong enough that if they do it is a non-issue.

Also - Is this a new application? SQL Server 2005 is 3 versions back from the currently released version (SQL Server 2012) and a new release (SQL Server 2014) is right around the corner.

You also said "the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers"

With the right installers and install procedures you should have control over how the application is installed. There is actually a lot in your question sort of behind the question that is hard to answer in this forum without a lot of back and forth. Some of the questions that come to mind are around areas like "How does your app install? Is there an installer app?", "What are the instructions for the installation and use of SQL Server?" "Are there configuration files in place to help determine where things get placed and how to connect to the database?", "Do you provide guidance to the customer on best practices for deployment?"

Don't answer those questions here - they are out of scope, but those are some of the questions you should be asking yourself as you go on this quest. I would recommend reading up on SQL Server application development and spending more time learning about deploying applications in general. I can't think of a good reference right now - but perhaps someone can add an answer with some. There must be something on msdn.com that can help here.