You need to avoid catching and reraising errors with a user defined error number. That will only make it harder to see where the error is happening. You should also avoid using explicit cursors and try using PL/SQL collections. As long as you are using 9i or above PL/SQL collections are the better way to go. You should probably have an index on transdetail as follows:

CREATE UNIQUE INDEX transdetail_idx1
  ON transdetail ( transtype, transdate, sr_cd, quantity);

This will greatly reduce the number of rows that Oracle needs to look at. It will do a range scan of the transdate column for each of the transtype values that you are searching for, plus the index has all of the columns that you want. Hence Oracle won't need to look at the index, then look at the table.

-- Let unhandled exceptions remain unhandled
-- EXCEPTION
-- This is a terrible example of error handling, don't use it
-- WHEN OTHERS THEN
-- raise_application_error(-20001,'An error was encountered - '||SQLCODE||'
-- -ERROR- '||SQLERRM);
-- END;
-- /

CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
AS
   TYPE transdetail_rec IS RECORD (
      sr_cd     transdetail.sr_cd%TYPE,
      transdate transdetail.transdate%TYPE,
      quantity  transdetail.quantity%TYPE );

   TYPE transdetail_type IS TABLE OF transdetail_rec
      INDEX BY PLS_INTEGER;

   transdetail_tab transdetail_type;
BEGIN
   SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month, sum(quantity)Receipt
     BULK COLLECT INTO transdetail_tab
     FROM transdetail
    WHERE transtype IN('STKREC','RP','RECADJ','SRET')
      AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
    GROUP BY trunc(transdate,'mm'),sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

    SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month,  sum(quantity) Issue
      FROM transdetail
     WHERE transtype IN('SINV','IP','STA','ISSADJ','PURRET')
       AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
     GROUP BY trunc(transdate,'mm'), sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

   COMMIT;
END;
/


Think of the way telephone directories are (typically) ordered.

If they were generated by an SQL query it would be

ORDER BY LastName ASC,
         FirstName ASC

The names are ordered by LastName first.

The secondary column is only used for ordering within groups of people sharing the same last name.


Why is data integrity not possible when using denormalization?

Data integrity is possible in both normalized and denormalized databases.  What is different in the two cases is the number of different ways an application program can write bad data to the database.

Let's take a simple case of bad data.  In the database, it says that employee with id 123 has last name "Brown".  But in the real world, employee 123 has last name "Browne".  This is bad data no matter how you look at it.  But let's go a  little deeper.

In one database, a normalized one, the last name is stored in precisely one place,  in the Employees table.  It's either right or it's wrong.  And, if it's wrong, it's up to someone who knows the right answer to fix the data in the database. This is part of data management, although not a very exciting part to those of us who work in automation.

In another database, a denormalized one, the last name of employee 123 is stored in two tables,  the Employees table and the Contacts table.  The contacts table is about phone numbers and addresses.  In the normalized database,  there is no contacts table.  It is a view instead.  Now, there are more possibilities.  The last name could be right in both places, or it could be wrong in both places.  But there is another failure mode.  It could be right in one place, but wrong in the other place.  Now the database is not only wrong, but self contradictory.  This is simply not possible in the normalized database, because the last name is stored only once.

This is an example of what is called harmful redundancy.  Second Normal Form through Fifth Normal Form are about eliminating cases of harmful redundancy, by imposing rules that cause one fact to be stored in one place.  This doesn't guarantee data integrity, but at least it obviates data inconsistency.  You still have to manage data to get it all right,  but now the DBMS is helping you manage data correctly.

Performance is a whole other story.  It may be the case that one of the databases outperforms the other for certain kinds of traffic.  It's even possible that one of the databases is more performant for updates but the other is more performant for queries.  But great performance and wrong answers may be catastrophic in some cases, and acceptable in other cases.

In the example I've given, the detection of wrong data is fairly trivial, even if it's somewhat tedious.  In the real world, there are examples where wrong data detection is well nigh impossible unless you get all the help you can from the DBMS.  Normalization can help in this regard.

Well, why don't you just makes sure that all the application code always does the right thing?  In the first place, the amount of application code grows dramatically, as the scope of the mission increases.  In the second place,  data errors can creep in by the use of interactive SQL,  maybe by the DBA.  Writing defensive application code is a good thing,  but for one reason or another, it might not get done.

Sorry this is so long,  but I needed to give you an idea about what's really going on here.  Data management is a never ending battle.  Finding and cleaning up wrong data is just part of that battle.  It's a dirty job, but somebody has to do it.  A good DBMS, coupled with normalization can help.


Adding a 'last time occurred' flag column

This cannot be done with a computed column. One option is to use a trigger:

Sample data

CREATE TABLE dbo.Example
(
    GroupKey integer NOT NULL,
    [Status] integer NOT NULL,
    inserted_date datetime NOT NULL DEFAULT GETUTCDATE(),
    IsLastTimeOccurred bit NOT NULL DEFAULT 'false',

    CONSTRAINT PK_dbo_Example_GroupKey_Status_inserted_date
    PRIMARY KEY CLUSTERED (GroupKey, [Status], inserted_date)
);

INSERT dbo.Example
    (GroupKey, [Status], inserted_date)
VALUES
    (11136, 10, '2015-03-30T13:01:50.153'),
    (11136, 11, '2015-03-30T13:02:03.590'),
    (11136, 12, '2015-03-30T13:02:08.980'),
    (11136, 10, '2015-04-30T09:37:40.477'),
    (11136, 11, '2015-04-30T09:37:46.400'),
    (11136, 12, '2015-04-30T09:38:04.177');

Initial setting

This assumes the rows all have IsLastTimeOccurred = false to begin with:

UPDATE E
SET IsLastTimeOccurred = 'true'
FROM dbo.Example AS E
WHERE NOT EXISTS
(
    SELECT *
    FROM dbo.Example AS E2
    WHERE E2.GroupKey = E.GroupKey
    AND E2.[Status] = E.[Status]
    AND E2.inserted_date > E.inserted_date
);

Maintenance

The following trigger ensures the column stays correct when the table changes:

CREATE TRIGGER trg_dbo_Example_AIUD
ON dbo.Example
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- Holds group keys and the latest insert date
    CREATE TABLE #Changes
    (
        GroupKey integer NOT NULL,
        [Status] integer NOT NULL,
        inserted_date datetime NOT NULL,

        PRIMARY KEY (GroupKey, [Status])
    );

    -- Populate the temporary table
    INSERT #Changes
    (
        GroupKey,
        [Status],
        inserted_date
    )
    SELECT
        E.GroupKey,
        E.[Status],
        MAX(E.inserted_date)
    FROM dbo.Example AS E
    WHERE EXISTS
    (
        SELECT *
        FROM Inserted AS I
        WHERE I.GroupKey = E.GroupKey
        AND I.[Status] = E.[Status]

        UNION ALL

        SELECT *
        FROM Deleted AS D
        WHERE D.GroupKey = E.GroupKey
        AND D.[Status] = E.[Status]
    )
    GROUP BY
        E.GroupKey,
        E.[Status];

    -- Set the flag
    UPDATE E
    SET E.IsLastTimeOccurred =
        CASE WHEN E.inserted_date = C.inserted_date THEN 1 ELSE 0 END
    FROM #Changes AS C
    JOIN dbo.Example AS E
        ON E.GroupKey = C.GroupKey
        AND E.[Status] = C.[Status]
    WHERE
        E.inserted_date = C.inserted_date
        OR E.IsLastTimeOccurred = 'true';
END;

This is a general solution that handles inserts, updates, and deletes. It was written before the question was clarified to state that the table was insert-only. Even so, I would probably leave the full general logic in; it doesn't cost much, and provides a safety net.


Query takes longer as domain user than local admin

After a few hours and some pow-wow time with my developers, we discovered the issue:

I typically uncheck login\logoff auditing in SQL profiler, but it happened to be the window I needed to peek on the cause. When running 'as admin' the application connects using TCP\IP.

When running as just a user, the app connects over Named Pipes. I'll still need to work out why, but in the mean time I can force the app to run in TCP\IP using a different connection string.


How to apply a regex on a column fetched from DB

You can use Regex with UDFs (user-defined functions). What you need is to create a Function that will call a Regex library. After that, your SQL will call this Function to execute the Regex.

First, read the following article as an example.

It says that you can create this Function as:

CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M))
    RETURNS INTEGER
    SPECIFIC regexSimple
    EXTERNAL NAME 'regexUdf!regexpSimple'
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NOT FENCED
    RETURNS NULL ON NULL INPUT
    NO SQL
    NO EXTERNAL ACTION
    ALLOW PARALLEL;

The article also shows the C library code and indicates how you can compile it.

After that, you may use your function like that:

SELECT c1, str
FROM   strTable
WHERE  regex1('#\s*\w+', str) = 1;

With that in mind, I suggest that you Google a little more to find a more complete Regex library to resolve your problem.

Edited: as suggested by Chris Aldrich, you may consider on running this function as fenced for safety. Read more here.


Add user to sql server with the required permission

Use a database role.  Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once.  However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>.  Once it is created you can then grant the role permissions just like you would a user.  Then you add users to the role.  Again you can either use the GUI or the command EXEC sp_addrolemember '<rolename>','<username>'.  And last but not least there are some pre-defined roles at both the server and database level. (You can't create server roles in SQL 2008.)  You can not make changes to the pre-defined roles but among others there is db_datareader which grants SELECT permission to every table in the database.  Oh, and you can add your user defined role to the pre-defined role.  So you could create a role MyReadOnlyRole and add it to db_datareader and then grant it EXECUTE permissions to various stored procedures, functions etc.  Or EXECUTE permission to the database itself for that matter. (That gives permission to execute any SP, function etc in the database.)


SQL Agent job for SSIS execution fails with "An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed."

This error can occur if the Configuration tab on the job step is used to set package parameters and one of the parameters (such as password) contains a character that needs to be delimited, such as a single quote.  When the parameters are modified on the job step dialog, the parameters are included on the job step's command line, and a single or double quote will not be delimited.  This will result in an invalid (or worse, erroneous) command line.

Possible solutions are:

Delete the job step, create a new job step, and set the package parameters on the SSIS project directly (in SSMS, under Integration Services, SSISDB, SSIS Packages, Projects).  The command line for the job step will not try to pass the parameters, but the package execution engine will read the parameters directly and do a better job of handling special characters.
It may be possible (but I haven't tested it) to include the backslash delimiter before the special character.  So for a password of My'Pa$$word you would instead use My\'Pa$$word.
Remove the offending special character from the parameter (e.g., if it's a password, generate a new password without any quotation marks in it).

I don't know what all characters can cause this issue, but I do know that single quotes will cause it, and I think it's safe to assume double quotes will also.

You can see what command will be executed by SQL Agent by running the following query:

USE msdb
SELECT t1.command FROM sysjobsteps t1
JOIN sysjobs t2 ON t1.job_id = t2.job_id
WHERE t2.name = '<SQL AGENT JOB NAME>'

The command will include a couple of /Par by default for $ServerOption::LOGGING_LEVEL and $ServerOption::SYNCHRONIZE, but if any parameters on the job step were modified you'll also see them in the command line, such as:

/Par "\"SQLServer_Password\"";"\"PLAIN_TEXT_PASSWORD\""

Note that for password fields, the password gets stored in sysjobsteps in plain text, so that's definitely not something you want to change on the job step.

There may be other issues that could cause invalid command errors, and anyone is welcome to edit this answer to add any others.


Select multiple ranges in a WHERE clause

BETWEEN is a nice method to help define ranges.

SELECT boroid
FROM districts
WHERE boroid BETWEEN 101 AND 108
  OR boroid BETWEEN 301 AND 303
  OR boroid = 402;


Test-Automation: Diff Data in Oracle Database - what are best practices?

There are two methods for comparing tables.  Method 1 works well with Unit Testing software.

You will want to use some sort of Unit Testing Software (and run it from the command line).

For full automation, you'll need some sort of DevOps Infrastructure.

SQL Developer Unit Testing

SQL Developer ($0) has some Unit Testing Capabilities

Running it from Command Line was found in this Related StackOverflow question.

./sdcli.exe unittest -run -suite -name AWARD_BONUS_SUITE -repo UNIT_TEST_REPO_CONNECTION -db TARGET_SCHEMA_CONNECTION

Building the Test

You'll need to create a no-op function to "test".
The "Process Validation" will be of type "Query returning no row(s)"

Building the Query

I cheat.  I use a template based code generator to create the comparison SQL(s).

There are a few different ones for Oracle. (FTLDB, tePLSQL, jk64)

There is also a plug-in for SQL Developer that allows you to place a GUI around the parameters for your template (eg table_name and db_link).  The name for the plugin is oddgen.

Method 1

This is the Brute-Force Method of comparing tables.

I cheat.  I use a template based code generator to create this code.

with old_data as (
    select
        banner -- specify your columns here
    from v$version
), new_data as (
    select
        banner
    from v$version@production
)
select * from (
    select 'new - old' test, a.* from new_data a
    minus
    select 'new - old' test, b.* from old_data b
)
union all
select * from (
    select 'old - new' test, a.* from old_data a
    minus
    select 'old - new' test, b.* from new_data b
)

Method 2

This one is OK for small tables.

select /*+ parallel */ sum(
    ora_hash( e.empno
           || e.ename
           || to_char( e.hiredate, 'yyyy-mm-dd hh24:mi:ss')
    )
) hash_value
from scott.emp e


Notification when the status on sql server replication is "Not Running"

Could you please let me know if there is a way we can receive alerts when the status changes on a particular replication.

You can achieve that using Replication Alerts

e.g. Below will set up alert for Replication: agent retry. Note that you must have database mail enabled and configured as well as sql agent operator configured.

EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent retry',
        @message_id=14152,
        @severity=0,
        @enabled=0,
        @delay_between_responses=0,
        @include_event_description_in=5,
        @category_name=N'Replication',
        @job_id=N'00000000-0000-0000-0000-000000000000'

Refer to : All About Automatically Monitoring Replication Agent Failures


All users have a profile assigned. You can alter the user to have the default profile by:

ALTER USER username PROFILE DEFAULT;


How to Query Which Table Is Inserted Mostly In SQL Server?

To know mostly inserted table you can simply retrieve number of rows/records in the table.

You can use following query to find it. Mostly inserted tables will be displayed on top of the records.

SELECT
   o.Name TableName,SUM(ps.row_count) Total_Rows
FROM
            sys.dm_db_partition_stats ps
inner join  sys.objects o
on ps.object_id=o.object_id
WHERE o.type='U'
GROUP BY
    o.Name
ORDER BY 2 DESC

Edit:1

As you described you requirement in comments, "you need find mostly inserted tables between a date range. It can be done using several method.

Using AFTER TRIGGER on the tables.
Using IDENTITY type column along with a column which records insert date & time (like Amina commented).
Using CDC you can record date & time of every changes in the table.
enabling Temporal Table (SQL Server 2016 +) feature on a table to record every changes.
Track Data Changes (SQL Server 2017)

Thanks!


Update a column based on another's table column value

UPDATE users
SET isActive=0
FROM users
JOIN userinfo
    ON users.id_emp = info.id_emp
   AND frdate IS NOT NULL

?


cross-database and cross-Availability Group queries and updates: which node do they go to with ApplicationIntent?

Goodness, you have a lot of questions in here. Here goes:

  Now suppose that the app does a select ... join between database A and
  B.

To be clear, let's say you use:

SELECT Stuff
FROM DatabaseB.dbo.Customers
JOIN DatabaseA.dbo.Sales

  Is Node1 (readwrite for AG1) used for the database A data, or Node2?

It would use Node1, the node you're connected to. It's just a plain ol' cross-database query.

  Similarly, suppose the client executes some update SQL against
  database A on that "readonly" connection; will this succeed (since
  database A is readwrite on that node) or not?

If you connected with "ApplicationIntent=ReadOnly", you shouldn't be trying to execute updates.

  Is there any way in the sql itself to namespace the reference to a db to force it to be from another node?

Sure, use views that point to linked servers, and the linked server name would be the AG listener name. That isn't necessarily performant, but you asked how you could do it from the SQL itself, and that would be one way.

  Does it matter if what is actually done from the client app is to exec a stored procedure (which might even be in another database C) […]

One thing to keep in mind is that availability groups are independent of each other and do not necessarily have the same replicas in their group. This means your AG1 might not have the same primary and secondary replicas as AG2 or AG3. Keeping this in mind, if you absolutely must do cross database queries then all of those databases should reside in the same availability group.

I'll stop here - your best bet for additional questions would be to break this question up into components and ask them individually. They could each have several great answers. Hope that helps get you started!


I assume you have already tried the "shame people into being more careful" approach without success.

I think I would try for an in-database solution, like adding timestamp columns to the table and adding columns for "soft deletes" rather than doing hard deletes, in such way that you can run queries to restore a table to its previous state.  If forbidding user-level hard deletes is not an option, then you can keep some log tables around that allow you to craft such restorative queries.  I use this module extensively for that purpose, although if I were starting from scratch I might consider something more modern like this.  The triggers needed to execute this logging do impose a considerable overhead on any DML operations, so you will have assess whether you can tolerate this.

A benefit of this approach is that you can often come up with queries that will fix one person's mistake without all changes after that mistake being lost.  Of course if the bad data from the oopsie leads to cascading errors because later work depended on the bad data, this may not be possible.

If that won't work, perhaps a hybrid approach would be a good idea.  You could save hourly pg_dump snapshots every hour and keep them for 12 hours (or whatever amount of storage you are willing to dedicate to this purpose).  If you do rarely need to go back more than 12 hours, then fallback to the PITR recovery method instead.  If you go this route, you might want to use the -Fc or -Fd options for pg_dump.  That way you can take just one dump of the database per hour, but choose which tables to restore at restoration time, rather than dump time.

If you want to keep with your current practice, there are many ways to make it faster, but it depends on what the current bottleneck is.  If the bottleneck is restoring the base backup from archive before you can start recovery, then you can just keep some already-restored copies around so that you can initiate recovery quickly using a pre-staged backup and just editing the pg_restore.conf file and then starting.  Once used you would have to throw it away and recreate it in the background so it is ready to go for next time.  If the bottleneck is instead the time it takes to do the PITR itself, then you could take more frequent base backups and use the most recent one which is old enough to be usable for your desired restoration time.  That way there is less work to recover.  For example, take a base backup every day, but only keep one from the beginning of time, and one from today, and one from yesterday.  Everytime you successfully complete a base backup, delete the one from two days ago.

You can also keep a lagging hot standby with "recovery_min_apply_delay" that is always around 12 hours behind, but by the time you stop that server, change the config to fast forward it to the time you need, and once done throw it away and recreate it for next time, it will probably not be much faster than just keeping a recent base backup, but will add a bit of complexity.


Fetch record from 3 tables which are related to each other through ID

JOINing is done before COUNTing.  That is, it gets 5 (or whatever) rows after joining, but 5 is not the value you wanted.

Instead, use a subquery to get the COUNT(*).


The error message is unrelated to privileges. The table valeur simply cannot be found. There must be some kind of confusion. My educated guess:

You call the function without schema qualification and accidentally execute a different function of the same name, because pckg_50d_facture is not in the search_path (or is there but in a later position). See:

How does the search_path influence identifier resolution and the “current schema”

Or you connected to the wrong database / database cluster by mistake.


What's the difference between a CTE and a Temp Table?

This is pretty broad, but I'll give you as general an answer as I can.

CTEs...

Are unindexable (but can use existing indexes on referenced objects)
Cannot have constraints
Are essentially disposable VIEWs
Persist only until the next query is run
Can be recursive
Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

Are real materialized tables that exist in tempdb
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subprocedures
Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases.  If you will have a very large result set, or need to refer to it more than once, put it in a #temp table.  If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance.  You will almost never speed things up by using a CTE, because, again, it's just a disposable view.  You can do some neat things with them but speeding up a query isn't really one of them.


pg_catalog.pg_statio_all_tables is your friend. All you should need to do is periodically poll pg_statio_all_tables for the tables in question. Changing stats ~ active table, non-changing stats ~ potentially unused table. Just be careful that no one does a select pg_stat_reset () ; in the middle of your monitoring.

For example:

test_1=# create table test_stats (col1 integer);
CREATE TABLE

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |              0 |             0 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Inserts:

test_1=# insert into test_stats (col1) select generate_series( 1, 10000000);
INSERT 0 10000000

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          44260 |      10088481 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Selects:

test_1=# select count (*) from test_stats where col1 between 10000 and 50000;
 count
-------
 40001
(1 row)

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          85560 |      10091429 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Deletes:

test_1=# delete from test_stats where col1 between 10000 and 50000;
DELETE 40001

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |         155075 |      10136163 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

update-- 2011-09-01

Further testing indicates that vacuum appears to increase the values in pg_statio_all_tables somewhat, which is unfortunate for your desired use. While vacuum doesn't make use of pg_statio_all_tables useless, it does make interpreting the results a bit fuzzier.

Perhaps a better place to monitor is pg_catalog.pg_stat_all_tables (at least with newer versions of Pg). I'm looking at version 8.4 and that has counts for tuples inserted, read, updated, and deleted-- ISTR 8.2 doesn't have all that and I don't know about 8.3 so YMMV depending on the version of Pg that you are using.

A third option (for insert, update, and delete activity) is to watch the file timestamps in the $PGDATA/base/$datid directory. The filename should map to the oid of the table, so you could use this to identify tables that aren't getting inserts, updates, or deletes. Unfortunately, this doesn't address tables that are still getting selected from, and using tablespaces will cause additional complications (as those files will not be under $PGDATA/base/$datid). The timestamps won't update until any pending changes are flushed, but if the file hasn't changed in months then the odds of a currently pending change are probably small.


Are all replicas configured to become primary in case of failover?
In case YES, you need to create the same job in all instances, scheduled at the same time. Keep in mind that each instance has a different MSDB, which means different jobs/schedules,etc..
So you will need to create a extra "check" in your jobs, to make sure it's running in the primary replica.
Here you can find a complete post about it.


Dynamic SQL:

declare @ServIP varchar(50) = 'IP of server'
declare @dsql varchar(1024)
set @dsql = 'select * from ' + quotename(@ServIP) + '.DbName.dbo.TableName'
exec (@dsql)


Counting number of total results for each column name

By JOINing to order_items that query returns one line for each row in the order_items table. The COUNT is therefore counting rows from order_items, not from table orders. This is why you think you are over counting.

Solutions would be to remove the joins to tables which are not referenced directly in the resultset, or to use the SELECT COUNT(DISTINCT ..) syntax.


Execute Stored Procedure with Account that has only read access

When I have a situation where someone (AD group or user) needs to TRUNCATE a table or a set of tables, I write the code in a stored procedure using the WITH EXECUTE AS 'dbo' clause, and grant execute rights to this procedure to the AD group/user who is designated to do the job.

For example:

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS 'dbo'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Delete all existing rows from tables as we only need a daily snapshot
    TRUNCATE TABLE dbo.MyTable;
END
GO

GRANT EXECUTE ON [dbo].TruncateMyTable TO [User];

Stored procedures created with the WITH EXECUTE AS dbo clause allow the stored procedure to run under the security context of the server principal who owns the database where the stored procedure resides.  This inherently allows the stored procedure to do things like TRUNCATE TABLE that normally wouldn't be possible without db_ddladmin or sysadmin rights.  In the example above, the [User] database principal will be allowed to execute the dbo.TruncateMyTable procedure, and thereby truncate the dbo.MyTable table.


Technically there should be no issue with backups being written to a shared LUN.

As you are using 2012 you do have the option of sending backups to a network share as well which could reduce the complexity of the cluster if you have a fileserver that you can use just to reduce the overhead in a DR situation (as in not having to add the LUN to the cluster etc etc).

Looking at a couple of guides and articles around the place backup drives shared to the nodes is a common thing.

https://mssqltrek.com/2012/06/28/how-to-add-a-new-diskdrive-to-sql-server-failover-cluster/

Fairly verbose example of said process but confirms the validity of backups on shared storage in a cluster.


Postgres: trying to fix some unicode encoding issues

If what you see is donÃ¢\u0080\u0099t then those are literal backslashes in your table, not escape sequences.

Try to double the backslashes to get literal ones:

select col from tab where col ~~ E'%\\u0080%';

And use the proper E'' syntax for PostgreSQL 8.4 with that.


Mapping and Inserting Values into a Specialized table

It's not always carried on automatically. Depends on the ORM you are using (if any ) on the app side it may or may not be implemented . For instance, it works for Hiberanate (personally I use @Inheritance(strategy=InheritanceType.JOINED) in main entity).

If you don't use any, then you need to do carry PK of main entity yourself - again, depends on RDMS engine, you can use RETURNING (Oracle, Postgres) or OUTPUT (SQLServer ) or some form of LAST_INSERT_ID (mysql, and maybe others ) to get value of auto-generated primary key in master table.

Also, as a side note I would recommend to have discriminator column in all tables (for instance, computer_type_id ) to ensure computer can have exactly one sub-type . E.g.

computer : Computer(computer_id, computer_type_id (FK to computer_type), PK (computer_id), UNIQUE(computer_id,computer_type_id)....)

laptop :   Laptop (computer_id, computer_type_id DEFAULT LAPTOP_TYPE_ID,   FK (computer_id, computer_type_id), CHECK (computer_type_id = LAPTOP_TYPE_ID))

Note : Unique constraint on (computer_id,computer_type_id) may see redundant (and it is from pure theoretical point of view), but it's added for a reason - thus you can refer to it in child tables. In some RDMS , for example Oracle, both constraints (PK and UNIQUE) can share the same index , so no overhead from performance point of view...


How does TIMESTAMP WITH LOCAL TIME ZONE behave when exporting data?

I made a test and exported table MY_DATA as given in your example.

Then I had a look at the dmp-File using a HEX editor. The file contains

<?xml version="1.0"?>
<ROWSET>
    <ROW>
        <STRMTABLE_T>
            <VERS_MAJOR>1</VERS_MAJOR>
            <VERS_MINOR>1 </VERS_MINOR>
            <VERS_DPAPI>3</VERS_DPAPI>
            <ENDIANNESS>2</ENDIANNESS>
            <CHARSET>WE8ISO8859P1</CHARSET>
            <NCHARSET>AL16UTF16</NCHARSET>
            <DBTIMEZONE>+01:00</DBTIMEZONE>
            <FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B0470083001F07D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO>
            <OBJ_NUM>3286546</OBJ_NUM>
            <OWNER_NAME>PMDSYS</OWNER_NAME>
            <NAME>MY_DATA</NAME>
            <PROPERTY>536870912</PROPERTY>
            <COL_LIST>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>1</COL_NUM>
                    <INTCOL_NUM>1</INTCOL_NUM>
                    <SEGCOL_NUM>1</SEGCOL_NUM>
                    <COL_SORTKEY>1</COL_SORTKEY>
                    <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>MY_DATA_ID</NAME>
                    <TYPE_NUM>2</TYPE_NUM>
                    <LENGTH>22</LENGTH>
                    <PRECISION_NUM>38</PRECISION_NUM>
                    <SCALE>0</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>2</COL_NUM>
                    <INTCOL_NUM>2</INTCOL_NUM>
                    <SEGCOL_NUM>2</SEGCOL_NUM>
                    <COL_SORTKEY>2</COL_SORTKEY>
                    <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>THE_DATA</NAME>
                    <TYPE_NUM>1</TYPE_NUM>
                    <LENGTH>10</LENGTH>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>31</CHARSETID>
                    <CHARSETFORM>1</CHARSETFORM>
                    <CHARLENGTH>10</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>3</COL_NUM>
                    <INTCOL_NUM>3</INTCOL_NUM>
                    <SEGCOL_NUM>3</SEGCOL_NUM>
                    <COL_SORTKEY>3</COL_SORTKEY>
                    <BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>DATA_TIMESTAMP</NAME>
                    <TYPE_NUM>231</TYPE_NUM>
                    <LENGTH>11</LENGTH>
                    <SCALE>6</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
            </COL_LIST>
        </STRMTABLE_T>
    </ROW>
</ROWSET>

As you can see, it contains information <DBTIMEZONE>+01:00</DBTIMEZONE>, so we can assume when you import such file into anther database running on different DBTIMEZONE  the values will be properly converted.


Thought about this SQL Server backup plan?

While this is functional, there's some holes in here that will make it difficult for to recover in the event of a problem:

I'm not sure what the device is your backing up to for your logs, but based on what I'm seeing here, you're appending all your log backups to the same file/device for all your log backup actions.  This can be cumbersome to manage and can cause you storage issues.
The BACKUP LOG ... WITH TRUNCATE_ONLY is redundant and can possibly cause you data loss.  Your log truncation occurs after every log backup, so that should be sufficient to manage your log, though you should consider scheduling your log backups for every hour 24/7 instead of just 6 AM-11 PM.
Your full backup with it's current syntax will overwrite your previous backup every run, so unless your saving that backup off to tape between the full backup runs, you will not be able to restore previous to your last full backup.
Shrinking databases regularly is VERY bad practice, for a variety of reasons.  Paul Randal talks more about it here.

In general, it looks like your backup/maintenance plan is as follows:

Execute a full backup nightly (1 AM)
Execute transaction log backups every hour
Rebuild fragmented indexes nightly

This is a solid enough plan, it looks like you just need to fill in some of the gaps.  Unfortunately, backup/restore is far to large a topic to cover here.  I recommend you read about this at SQLServerPedia(now ToadWorld).  Since you're committed to SQL 2000, this limits you to making use of some of the currently existing tools out there.  However, plenty of code examples exist to fine tune and polish your currently existing code.

As for index fragmentation, without seeing your code, I can't comment to any possible issues with it.  If it's been working fine up until now, there's probably no issues there, but you can read more about managing this up on MSDN.


SQL Server 2012 (compability level 110) and SQL Server Native Client 10

The client library used to connect does not care at all about the compatibility level of the database.

The code issued by the client may have some issues (for example, you needed 90 to use APPLY many years ago). But the mechanism used to send the code and get results does not care


X5687 vs X5690 CPUs for a SQL 2008 R2 Server

For 2008R2, I'd take the extra cores every time.

If the server might be upgraded to Denali/2012 on release you may need to factor in the switch from per socket to per core licensing. @MrDenny summarised this well in SQL Server 2012 Licensing Changes.


Not exactly understand the need, but i guess below might help:

To get the information between Principal and mirror via witness query the dmv sys.database_mirroring_witnesses

You can get the info from columns

principal_server_name -- Name of partner server whose copy of the database is currently the principal database

mirror_server_name  --Name of the partner server whose copy of the database is currently the mirror database.

Also, check out the cool commands from dbatools Get-DbaDbMirror

PS C:\> Get-DbaDbMirror -SqlInstance  sql2016

Gets properties of database mirrors and mirror witnesses on sql2016 SQL Server instance


Lets first get the concept of reader / writer threads out of the way

  During a backup, SQL Server creates one reader thread for each volume
  that the database files reside on.  The reader thread simply reads the
  contents of the files.  Each time that it reads a portion of the file,
  it stores them in a buffer.  There are multiple buffers in use, so the
  reader thread will keep on reading as long as there are free buffers
  to write to.  SQL Server also creates one writer thread for each
  backup device to write the contents of the buffers out to disk or
  tape.  The writer thread writes the data from the buffer to the disk
  or tape.  Once the data has been written, the buffer can be reused by
  the reader thread.

Source

Answer

So yes, while the reader threads are reading the pages and the writer threads are writing the pages to the separate disk, the time to process the pages will be impacted by both.

But, this does not mean that the79.088 MB/sec will be divisible by 2.

It means that the read operation was running at ~79.088MB/sec and the write operation was also running at ~79.088MB/sec. The speed is determined by the slowest of these two operations since both operations are reading and writing from multiple buffers (as long as BUFFERCOUNT is set correctly / not added to the command).

As a result, these buffers filling up too slow / not being cleared fast enough is the bottlenecking in action.

Another way to validate this is by calculating the MB/sec * backup duration. This will match your database size minus the space available.

Testing how fast you can read

If you want to know the amount of data & how fast it can be read from disk, you can take a backup to 'NUL'

BACKUP DATABASE DBName TO DISK = 'NUL' WITH COPY_ONLY;

In my case I ended up with 122.406 MB/sec

Testing how fast you can write

To know how fast you can write, you could use Crystaldiskmark.

Use Seq Q32T1 to mimic the backup operation writes.

Source

This is what pops up on my slow target drive, where I want to back up to.

Putting two and two together

If I then run a backup command of my database that resides on the D  (data) &  L  (log) drive to the disk I previously checked with CrystalDiskMark (E).

BACKUP DATABASE DBName TO DISK = 'E:\Folder\DbName.BAK';

Processed 703088 pages for database 'DbName', file 'Database' on file 1.
Processed 2 pages for database 'DbName', file 'Database' on file 1.
BACKUP DATABASE successfully processed 703090 pages in 50.198 seconds (109.424 MB/sec).

We see that the read part of the backup operation is the bottleneck on my system.


Weird memory pressure for SQL Server 2016

There is a cool article by Jonathan Kehayias on SQL SQLSkills.com with the title Wow… An online calculator to misconfigure your SQL Server memory!.

In his article Jonathan writes:

  My general recommendation is to use the calculation from my book, Troubleshooting SQL Server: A Guide for the Accidental DBA, which is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.  This isn’t an overly technical calculation but it has worked well and is generally going to configure ‘max server memory’ low enough that the server will be stable and have reliable performance.

If you follow on with this example, then you might be better off with configuring your SQL Server to run with a max_memory setting of 81 GB.

You can create an Excel to produce a nice little graph of SQL Server Max Memory settings with the following formulas and data.

The Excel Sheet starts of with a HW Memory (A2) column:

4

The Excel formula for the second column OS Reserved (B2) is:

IF(A2<=16;1 + A2/4;1+4+(A2-16)/8)

The SQL Memory column (C2) is then:

A1-A2

This produces the following chart:

Possible Solution

As you can see, if you have 96 GB of RAM, then you should reserve 15 GB for the OS and set the SQL Memory (max_memory) to 81 GB.

Jonathan goes on to explain in his article that ...

  SQL Server has a built-in component of the SQLOS, the Resource Monitor, that monitors the QueryMemoryResourceNotification Windows Server API to get status about the OS level memory availability on the server.  If the Windows OS is under memory pressure, it will set a low memory notification that the Resource Monitor thread will detect and force the external clock hands on the caches internally to begin sweeps of the caches to cleanup and reduce memory usage allowing the process to free memory back to the OS.

Your OS might not have enough memory and is taking the memory away from the SQL Server OS.

Slightly reducing your max_memory setting to 81 GB will allow the OS and other SQL Server components that don't run inside the max_memory setting to have enough RAM.

Your mileage may vary


Conext menu behaviour change in SSMS 18.0/18.1

While I cannot reproduce this problem (i.e. I can right-click on any item in Object Explorer and press ⇧ once to get to the bottom of the menu in SSMS 18.0 RTM), if the goal is to reduce the number of times you press a key, then I will suggest abandoning the desired approach (2 keys pressed: ⇧ and then Enter ↲) in favor of simply doing a left-click and then pressing F5 once (i.e. "Refresh"), thereby saving one key-press every time 😺.


Is it possible to make a trigger that does not allow INSERT?

It will be something like
CREATE TRIGGER forbid_events_update
INSTEAD OF UPDATE
ON events
FOR EACH ROW
BEGIN
    RAISE(ROLLBACK, 'Events update not allowed'); /* maybe ABORT of FAIL */
END;

And the same for INSTEAD OF DELETE.


This happens (based on the discussion in comments) because the tables exist in the model system database. As per the documentation (my emphasis):

  If you modify the model database, all databases created afterward will
  inherit those changes. For example, you could set permissions or
  database options, or add objects such as tables, functions, or stored
  procedures. File properties of the model database are an exception,
  and are ignored except the initial size of the data file.

As a solution, dropping the tables from model will stop this phenomenon.


Autoclose property resets to true after attaching db even if model is set to false

Attaching a database does not involve model at all, so there is no automatic way to enforce that your model database settings are applied to any database that you later attach. This is only used for databases that you CREATE, and even more restrictive, databases that you create without FOR ATTACH or FOR ATTACH_REBUILD_LOG.

In Express, attached databases are automatically attached as AutoClose = true, so there is nothing you can do with model, or the attach process itself, to set it to false. You need to either manually set it to false after attach, or I suppose you could set up a periodic job that checks if this setting is enabled on any database that has been created since the last time the job has run (or without a filter at all), and set it to false for you.

Why does Express work that way? Because Express is largely designed for local development, and to conserve resources on a developer's workstation (which typically doesn't have a whole lot of resources to spare, compared to production servers), it only holds open databases that are actively being used. This topic in Books Online helps explain, though it doesn't explicitly state that any database attached to Express sets this property to true.

If you don't want to deal with this (and other Express limitations), you could always use Developer Edition.


Aggregates per half hour intervals within a time span

First, let's look at how to get all the time slices.

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
  DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
FROM master..spt_values;

This will give you your set of 96 time slots, in half hour increments, from the 28th at midnight through the 29th at 23:30. A Numbers table is a better option than master.dbo.spt_values, but you can also use sys.all_objects - or any table really that has enough rows to cover your desired date range.

Next, let's see how to find the rows in the Events table that you care about - basically anything that passes through your window.

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

SELECT EventID, EventName, StartDate,
  EndDate = DATEADD(MILLISECOND, Duration, StartDate)
FROM dbo.Events
WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay)
  AND StartDate <  DATEADD(DAY, 1, @LastDay);

Now, how to use that to get the results you're after (edited to accommodate durations that do not end on the half hour mark):

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

;WITH TimeSlots(HalfHour) AS
(
  SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
    DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
  FROM master..spt_values
),
EventRange AS
(
  SELECT EventID, EventName, StartDate,
    EndDate = DATEADD(MILLISECOND, Duration, StartDate)
  FROM dbo.Events
  WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay)
    AND StartDate <  DATEADD(DAY, 1, @LastDay)
),
Combo AS
(
  SELECT e.EventID, e.EventName,
    HalfHourSlot = CONVERT(CHAR(5), CONVERT(datetime, CONVERT(time, t.HalfHour)), 108),
    CASE
        WHEN e.StartDate > DATEADD(minute, 30, t.HalfHour) OR e.EndDate <= t.HalfHour
            THEN 0
        ELSE
            DATEDIFF(MINUTE, CASE WHEN e.StartDate >= t.HalfHour THEN e.StartDate ELSE t.HalfHour END, CASE WHEN e.EndDate <= DATEADD(minute, 30, t.HalfHour) THEN e.EndDate ELSE DATEADD(minute, 30, t.HalfHour) END)
    END AS Duration
  FROM TimeSlots AS t
  CROSS JOIN EventRange AS e
)
SELECT EventID, EventName, HalfHour = HalfHourSlot,
  SumDuration  = SUM(Duration),
  CountClients = SUM(CASE WHEN Duration>0 THEN 1 ELSE 0 END)
FROM Combo
GROUP BY EventID, EventName, HalfHourSlot
ORDER BY EventName DESC, HalfHour;


Login failed for user 'domain\Administrator'. (Microsoft SQL Server, Error: 18456)

I reinstalled the sql server three times.

During the installation you are asked what users to be added as instance administrators. You should add yourself. You probably added the built-in Administrators group and now you're running a LUA process and had lost the admin token and cannot access the instance. If yes, then is simply a mater of running the client process elevated ('Run as administrator').

If you have omitted to add the built-in Administrators group as SQL instance admins then you must follow the steps described at Connect to SQL Server When System Administrators Are Locked Out.


PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

Sounds to me like you have PostGIS installed in your database more than once, possibly once via CREATE EXTENSION and again via a load script, or from a dump.

The hint is that:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

mentions the schema qualifier postgis.geometry for one operand, and omits it for another. I'd say you have two copies of the the geometry type in different schemas, probably public and postgis.


Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client?

I don't believe that PostgreSQL dropped support for any Linux distribution. Perhaps you mean that there are no binary installation packages provided.

Using a 9.6 client is no problem, since 9.6 is a supported release. You will not be able to use new features like scram-sha-256 authentication, but I guess you can live without that.

Building PostgreSQL from source would be another option.


Possible to detach a database from SQL Server while retaining file permissions?

While not a concrete answer, I find no configuration option or setting to override this behavior.  It seems to be baked in.

Everywhere Detaching and Attaching Databases is mentioned in the documentation points back to the Securing Data and Log Files page you referred to above.  The scenario on this page makes it pretty clear what is going on, and why the files should be secured as such.

Perhaps you could use a different user or service account to perform the operation?  IF the file is owned by the proper account (such as the SQL Server service account) this whole thing may be a non-issue.


Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle

This is a very simple query:

SELECT
    u.username,
    e.address
FROM
    Users  u
  LEFT JOIN User_Emails  ue
    ON ue.user_id = u.user_id
  LEFT JOIN Emails  e
    ON ue.email_id = e.email_id
ORDER BY
    u.user_id,
    e.address ;

You need the LEFT joins so users without emails are shown too (with NULL in the address).
You can add more columns in the SELECT list if you like, e.g. the u.user_id or the e.email_id.
The ORDER BY is also optional and can be changed according to your requirements.


One to one relationship or another approach?

To remove extra fields the best approach (In my eye of course) would be to insert site_id to clan,team and game tables.

In the site table we have site_id which is primary key(unique) and we have a field in the name of url.

  url field should be unique.
  one-to-one relationship between your three tables with site table.

This is the way to go.


Creating databases - no access to folder - MS SQL

SQL Server Login

When you create a SQL Server login (be it either a native SQL Server login with password or a Windows Authenticated login without password), then this user is assigned certain default permissions/privileges. Let's have a step-by-step look at how we can achieve what you want.

1. Create SQL Server Login

USE [master]
GO
CREATE LOGIN [Bob]
    WITH PASSWORD=N'BobsPass4567#',
    DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Reference: CREATE LOGIN (Transact-SQL) (Microsoft | Docs | SQL)

The login has been create for [Bob] and he can already connect to the SQL Server instance.

You may ask yourself: How so?

When a login is created the login receives some default permissions/privileges. Let's see what they are.

2. Check [Bob]'s Permissions/Privileges

In a SQL Server query window execute the following statements/commands:

EXECUTE AS LOGIN = 'Bob'
GO

Reference: EXECUTE AS (Transact-SQL) (Microsoft | Docs | SQL)

With the above step we are posing as [Bob] and are then going to check the default permissions/privileges. Next step:

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'LOGIN') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL,'ROLE') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'USER') AS fmp

Reference: sys.fn_my_permissions (Transact-SQL) (Microsoft | Docs | SQL)

We are checking the default permissions of [Bob] for various so called securable classes (securable_class). We only receive return values for the first statement. These are:

entity_name | subentity_name | permission_name
------------+----------------+-------------------
server      |                | CONNECT SQL
server      |                | VIEW ANY DATABASE

These basic permissions seem reasonable. Allowing the login to connect to the SQL Server instance is a good thing and letting the login view any database seems pretty adequate.

Oh, but you don't want the login [Bob] to be able to view any database? Well let's DENY [Bob] that privilege.

3. DENY Privilege to [Bob]

The following command will deny [Bob] the ability to view any database on the server:

You will have to open a new Query Window as your current Query is running as [Bob] and [Bob] can't revoke his own permissions/privileges. Try it....

DENY VIEW ANY DATABASE TO [Bob]
GO

Reference: DENY Server Permissions (Transact-SQL) (Microsoft | Docs | SQL)

...and because you shouldn't trust anything you read on the internet, you should go ahead and verify that [Bob] can't view any databases:

EXECUTE AS LOGIN = 'Bob'
GO
SELECT * FROM sys.databases
GO

Which returns:

name   | database_id | source_database_id | owner_sid | ...
-------+-------------+--------------------+-----------+-------
master | 1           | NULL               | 0x01      | ...
tempdb | 2           | NULL               | 0x01      | ...

Even though you have denied the VIEW ANY DATABASE privilege to [Bob], he'll still have the ability to view the master and tempdb database. He can't view the msdb or model databases. Why? Well, this topic is worth a separate discussion...

4. GRANT Privileges to Create Databases

[Bob] is now able to connect to the SQL Server instance, but unable to view any database (other than master and tempdb) or perform any other task. But he needs to be able to create databases. Let's assign the right privilege:

GRANT CREATE ANY DATABASE TO [Bob]
GO

Hmmm. Let's see if [Bob] can create a database now:

EXECUTE AS LOGIN = 'Bob'
GO
CREATE DATABASE BobsDatabase
GO

And let's see if [Bob] can view his own database:

SELECT * FROM sys.databases
GO

This returns:

name         | database_id | source_database_id | owner_sid                          | ...
-------------+-------------+--------------------+------------------------------------+---------
master       |  1          | NULL               | 0x01                               | ...
tempdb       |  2          | NULL               | 0x01                               | ...
BobsDatabase | 19          | NULL               | 0x9C426DE3F33E5340A71D96D8635900F0 | ...

So we have proven that [Bob] can create his own databases, but not view all databases.

  All these settings and checks will work if you open up a separate query window with [Bob]'s credentials and perform [Bob]'s action in that window, except when modifying his own permissions/privileges.

Folder Access

The folder access issue is a bit more complex. Needless to say, [Bob] doesn't have access to my local C:\SQL\Data\... folder, because [Bob] is a native SQL Server login. However, he does have the CREATE ANY DATABASE privilege, so he doesn't actually require access to the folder itself; the SQL Server instance's service account will do that for him.

Your issue with the folder access might have to do with how you are executing the script in your application. Is it [Bob] that is executing the statement or some unknown application pool account? Does that account have privileges to create any database? Or could you pose as [Bob]?

  Hint: EXECUTE AS LOGIN = 'Bob'

This does require that the application pool account have adequate SQL Server privileges. You might have to rewrite that part in your code or seek further advice on how to pose as somebody else.


Is there a way to query SQL Server 2005/2008 for location of its log files?

SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '')

EDIT: Cleaned it up a bit with a little more googling to verify; Also to note that this particular argument "ErrorLogFileName" to the SERVERPROPERTY function is undocumented and therefore unsupported by Microsoft. Use at your own risk. etc. etc.


Should a SQL Server TempDB space be allowed to be reclaimed?

TempDB (as with any other files) should be sized to a stable amount appropriate to your instance so that it shouldn't have to grow.  The idea is to minimize autogrowth events, because any time your files have to grow, your queries will be forced to wait until the file growth has been completed.  My practice(and this is my practice, your miles may vary) for sizing TempDB is as follows:

Total TempDB data file size should be approximately 1/3rd my total data file size for all databases
Number of data files is half the number of logical cores up to 8 total data files.
Log file is sized to 1/3rd total size of TempDB data files
I place TempDB on a LUN where it will have some space to grow, so that TempDB will have some ability to grow if a process requires more TempDB than I allocated.
I set explicit values for TempDB file growth, typically 1GB (depends on file size).  I do not use percentages for growth on any files, as percentage growth is harder to predict and accommodate.

As for giving space back to the OS, I typically don't do this.  The only time I will give space back is if there was an obvious run away process that blew TempDB out to an abnormal size.  To give the space back, I restart SQL in order to recreate TempDB fresh (as TempDB is created fresh on every SQL Service start) since shrinking TempDB can cause corruption.

There is a lot of discussion and opinion on exactly how TempDB should be configured.  Again, this is my approach.  For more on TempDB best practices and approaches, I recommend this whitepaper from Robert Davis(it's a vendor site, so you'll have to register).


MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

When I see messages of that nature, I usually blame the redo logs. This is where is LSN is written.

The problem may stem from not preparing the incremental backup.

What seems to be missing is the use of the --prepare option.

What this essentially does is create the backup rolled forward to a specific point in time. This is particularly true if there were transactions occurring during the backup.

It's kind of like the opposite of a mysqldump --single-transaction, which makes a logical dump based on the start time of the backup. Doing a --prepare with xtrabackup will make the redo logs roll forward to match the end time of the backup.

Note what the opening paragraph of Preparing the backup from the XtraBackup Docs says:

  After you make a backup with --backup, the next step is to prepare it. The data files are not point-in-time consistent until they’ve been prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these data files, it will detect corruption and crash itself to prevent you from running on damaged data. The --prepare step makes the files perfectly consistent at a single instant in time, so you can run InnoDB on them.

This should coalesce the LSN of the incremental after making the incremental.

For more information, please read Preparing the backup from the XtraBackup Docs


Ola Hallengren - DatabaseBackup - LOG Unable to open Step output file. The step failed

It turns out that the person migrated the jobs from old server to new server edited the jobs manually.

One of the edit was to "Output File: (Job Step properties-->Advanced)"
this was set to default log location C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log on old server.

On the migrated server it was edited to "F:\SQLAgentLog\Log\" where the directory Log did not exist. (that person missed deleting the Log text in the path)

On the other jobs (FULL and DIFF) the text Log had been removed so it was set like this "F:\SQLAgentLog\" and these both were working fine!


Without regex, I think you need an OR for the last requirement (for the part that is either DataSheet or RoHS:

WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]DataSheet%')
   OR m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]RoHS%')


Performance tuning a Great Circle distance calculation

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column.

Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.


Why don't "statistics" options appear in the job's command?

Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.

EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y'

that these default parameters  @UpdateStatistics = 'ALL' &  @OnlyModifiedStatistics = 'Y' will be used.

It is by design that these job steps are not changed.

  Is it wrong to edit MaintenanceSolution.sql directly for this?

Depends on your requirements.

By default no statistics will be updated when calling the procedure without the parameters specified.

If you change these @OnlyModifiedStatistics & @UpdateStatistics parameters in the procedure, all modified statistics will be updated when calling the procedure without these parameters added.

  Is there a way to add theses parameters to the job using a query?

Sure, change the jobstep to this:

 EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y',
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y';

EDIT

  So even if i don't see "@UpdateStatistics = 'ALL'" &
  "@OnlyModifiedStatistics = 'Y'" in the jobstep and that i have updated
  it as the default parameters in the MaintenanceSolution.sql ...
  Parameters will still be used ? –

Sure, executing below procedure with the default NULL parameter for the @UpdateStatistics parameter runs instantly

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL
--,@UpdateStatistics = 'ALL'

Date and time start

Date and time: 2019-08-21 14:49:22

Date and time end:

Date and time: 2019-08-21 14:49:22

No stat updates statements found.

This statement will do no index optimization and no update stats if no default parameters are changed on the procedure itself.

Changing the @UpdateStatistics parameter in the procedure and rerunning it, on a small database

It takes too long on all user databases ;)

@UpdateStatistics nvarchar(max) = 'ALL',

Rerunning the procedure on one small database

Date and time start:

Date and time: 2019-08-21 14:50:16

Date and time end:

Date and time: 2019-08-21 14:50:23

One of the statements in the messages tab:

 UPDATE STATISTICS [Database].[dbo].[test] [UIX_dbo_test_RecordType]


How to import export.sql in sql developer?

Open the file(s) it has created, and run them with F5 in SQL Developer. They should also work in SQL*Plus.

They'll create the tables/objects (if you have the Export DDL option checked), and then they'll INSERT the rows (if you have the Export Data option checked.)

Note that there are more efficient ways for moving objects/data around, especially when dealing with very large amounts of data - namely, Data Pump.

This particular feature is very helpful when wanting to do ad hoc data and object exports - especially when you, the developer, don't have access to run Data Pump.


Select a table's current identity from another database

If the Database is on the same Instance, then you don't need to use OPENQUERY. But even if the Database is on another Instance, then you still shouldn't need OPENQUERY (as @AndriyM reminded me of in a comment on this Answer): since the query uses JOINs to system Views instead of using the built-in meta-data functions (e.g. OBJECT_ID), fully qualifying the object names with the 4-part name (beginning with the Linked Server name) should work just fine. And in fact, using the 4-part names is probably better anyway as it would allow you to parameterize the query, whereas OPENQUERY requires a string literal for the Query (which then requires Dynamic SQL to generate the query that calls OPENQUERY).

Hence, in either case, the following query should work:

SELECT ISNULL(sic.last_value, 0) AS [LastValueReserved],
       ISNULL(sic.last_value, 0) + sic.increment_value AS [NextValue]
FROM   [ [ {linked_server_name}. ] {database_name}. ][sys].[identity_columns] sic
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[objects] so
        ON so.[object_id] = sic.[object_id]
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[schemas] ss
        ON ss.[schema_id] = so.[schema_id]
WHERE  ss.[name] = N'{schema_name}'
AND    so.[name] = N'{table_name}';

Of course, if you are getting this info for the current database, then you don't need to fully qualify those system view names with {database_name}..

Please note:

I labeled the first field with the qualifier of "Reserved" because there are reasons that Identity values can be reserved but never used (i.e. not found in the table). Examples being: if an INSERT statement fails, or if the INSERT is done within a Transaction that gets rolled-back, or if the server restarts (in which case you sometimes see a jump of approximately 1000; this behavior has been asked and documented on here and other sites).
ISNULL(sic.last_value, 0) is used because prior to the first INSERT operation, the value in the last_value column is NULL.


How to update a table from a another table

An alternative approach to the co-related subquery (suggested by Kerri) would be to use the MERGE statement, which might be more efficient than the subselect (which can only be verified by looking at the execution plan of both statements).

MERGE INTO table_b
USING
(
  SELECT id,
         field_2
  FROM table_a
) ta ON (ta.id = table_b.id)
WHEN MATCHED THEN UPDATE
    SET table_b.field_2 = ta.field_2


Eliminate Key Lookup (Clustered) operator that slows down performance

Key lookups of various flavors occur when the query processor needs to obtain values from columns that are not stored in the index used to locate the rows required for the query to return results.

Take for example the following code, where we're creating a table with a single index:

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

We'll insert 1,000,000 rows into the table so we have some data to work with:

INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Now, we'll query the data with the option to display the "actual" execution plan:

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

The query plan shows:

The query looks at the IX_Table1 index to find the row with Table1ID = 5000000 since looking at that index is much faster than scanning the entire table looking for that value.  However, to satisfy the query results, the query processor must also find the value for the other columns in the table; this is where the "RID Lookup" comes in.  It looks in the table for the row ID (the RID in RID Lookup) associated with the row containing the Table1ID value of 500000, obtaining the values from the Table1Data column.  If you hover the mouse over the "RID Lookup" node in the plan, you see this:

The "Output List" contains the columns returned by the RID Lookup.

A table with a clustered index and a non-clustered index makes an interesting example.  The table below has three columns; ID which is the clustering key, Dat which is indexed by a non-clustered index IX_Table, and a third column, Oth.

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    ID int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , Dat nvarchar(30) NOT NULL
    , Oth nvarchar(3) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat);
GO

INSERT INTO dbo.Table1 (Dat, Oth)
SELECT TOP(1000000) CRYPT_GEN_RANDOM(30), CRYPT_GEN_RANDOM(3)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Take this example query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We're asking SQL Server to return every column from the table where the Dat column contains the word Test.  We have a couple of choices here; we can look at the table (i.e. the clustered index) - but that would entail scanning the entire thing since the table is ordered by the ID column, which tells us nothing about which row(s) contain Test in the Dat column.  The other option (and the one chosen by SQL Server) consists of seeking into the IX_Table1 non-clustered index to find the row where Dat = 'Test', however since we need the Oth column as well, SQL Server must perform a lookup into the clustered index using a "Key Lookup" operation.  This is the plan for that:

If we modify the non-clustered index so that it includes the Oth column:

DROP INDEX IX_Table1
ON dbo.Table1;
GO

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat)
INCLUDE (Oth);        <---- This is the only change
GO

Then re-run the query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We now see a single non-clustered index seek since SQL Server simply needs to locate the row where Dat = 'Test' in the IX_Table1 index, which includes the value for Oth, and the value for the ID column (the primary key), which is automatically present in every non-clustered index.  The plan:


Want to output post title with meta-tags at same time on MySQL of WordPress

SELECT post_title,name FROM wp_posts
JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE post_type = 'post'


Why is the candidate composite index not used?

You have to FORCE the index, the optimizer will only then  use your wanted index, else you give it only a hint
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
    FORCE INDEX (territory_id)
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e


Pull Data from one database and insert to another database ( same schema )

FDW and dblink have the advantage that they make a direct postgreql to postgresql connection which means that there will be no processing needed in your python code.  here's an example that should work much like your example code, except faster.

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
strt="dbname='t_mig1' user='postgres' password='postgres' host='localhost'"
cur_p = conn_p.cursor();

cur_p.execute("select dblink_connect('tmpdb',%s);",(strt,));
cur_p.execute("""
  insert into "a_recipient" (id, first_name, middle_name, last_name)
  SELECT * FROM dblink('tmpdb',
    $$
    SELECT CAST(REGEXP_REPLACE(studentnumber, '[^0-9]', '','g') as integer), firstname, middlename, lastname FROM sprofile
    $$ ) as x( a int,b text, c text,d text);
""");

cur_p.execute("SELECT dblink_disconnect('tmpdb');");

unfortunately I was not able to test this code. feel free to make any correction needed.
I also changed the regex to remove all non digits.


Data too long for column for longtext datatype - MySqlException

http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

  LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 − 1)
  characters. The effective maximum length is less if the value contains
  multibyte characters. The effective maximum length of LONGTEXT columns
  also depends on the configured maximum packet size in the
  client/server protocol and available memory. Each LONGTEXT value is
  stored using a 4-byte length prefix that indicates the number of bytes
  in the value.

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
http://dev.mysql.com/doc/refman/5.7/en/memory-use.html


Insufficient privileges as a definer of package

When you compile a procedure that executes with the current_user's authorisation, then the current user must be able to do everything that the Procedure wants to do.

When you compile a procedure that executes with the definer's authorisation, then the owner of that procedure must be able to do everything that the Procedure wants to do.

Any and all privileges needed by such a procedure must be directly granted to the owning account; they cannot be conferred through a Role, even if that Role is granted "by default".

You probably need to grant "create table" to the account that owns the procedure.

That said, having a procedure to spin up tables at random strikes me as a slightly odd thing to want to do.


Reflexivity axiom for inferring functional dependencies

"What does it really mean that some attribute is a subset of some other attribute?"

It means nothing.  Functional dependencies exist between SETS of attributes.

Whenever the treatment is supposed to be formal and precise (as in, e.g., when stating the very definition of the axioms), that's how X,Y and Z are supposed to be interpreted.

Unfortunately, people tend to always write down concrete examples of FD's without making the SET property explicit.  As in, e.g., AB->C, where A,B and C are attributes.  Formally, that should actually be {A B} -> {C}, but those braces don't add very much.

And your formulation of the augmentation axiom is incorrect precisely on that count.  If X,Y and Z are sets of attributes, then the only correct formulation of the augmentation axiom is

X -> Y   ===>   (X UNION Z) -> (Y UNION Z)

The reflexivity axiom provides a formal definition of what it means to be a trivial FD.  The various normal forms are defined in terms of nontrivial FDs, hence it must be pinned down what that means exactly.  The reflexivity axiom does that.


Why does REGEXP_REPLACE concatenate with a previous result?

It's a known bug, fixed in 8.0.12.

  REGEXP_RELACE() results from one result set row could carry forward to
  the next row, resulting in accumulation of previous results in the
  current row.


IMHO

Let it catch up.

The Seconds_Behind_Master reading is not what I rely on to see if catching up is possible or worth it. Don't be afraid at the number reported.

You have to look at the server's setting for expire_logs_days. If your slave is so far behind that the binary log file has been deleted, then you need to re-image.


show table name + number of records for each table in a mysql innodb database

I have a very aggressive approach using brute force Dynamic SQL

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
INTO @sql FROM (SELECT table_schema db,table_name tb
FROM information_schema.tables WHERE table_schema = DATABASE()) A;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

Example: In my test database, I get this

mysql> use test
Database changed
mysql> SET group_concat_max_len = 1024 * 1024 * 100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
    '> COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
    -> INTO @sql FROM (SELECT table_schema db,table_name tb
    -> FROM information_schema.tables WHERE table_schema = DATABASE()) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------+----------------+
| Tables_in_database | Number of Rows |
+--------------------+----------------+
| biblio             |              3 |
| biblio_old         |              7 |
| dep                |              5 |
| e                  |             14 |
| emp                |              4 |
| fruit              |             12 |
| fruit_outoforder   |             12 |
| nums_composite     |              0 |
| nuoji              |              4 |
| prod               |              3 |
| prodcat            |              6 |
| test2              |              9 |
| worktable          |              5 |
| yoshi_scores       |             24 |
+--------------------+----------------+
14 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT : If all the tables are MyISAM, this will happen very fast. If all tables are InnoDB, each table will be counted. This may be brutal and unrelenting for very large InnoDB tables.


You need to look into using certificates to sign the stored procedures.  This will allow users to access anothers tables.


How do I select the count of a specific value without grouping in postgresql?

You can use a window function:

create or replace view x
as
select id, foreignkey, count(*) over (partition by foreignkey) as fk_count
from (
  select id, foreignkey from a
  union
  select id, foreignkey from b
) x;

Unrelated, but:

UNION removes duplicates from the result, UNION ALL does not. If you know there are now duplicates, then you should be using UNION ALL because it's faster.


Oracle systimestamp result differs in db server and client

It is normal, because your NLS settings differ on the server and on the client.

The NLS conversion always happens on the client side no matter what your server settings are.  Compare the output of these:

select * from nls_database_parameters;
select * from nls_session_parameters;


Executing a script via Powershell possibility

I notice you tagged the question netezza but I'm not sure that SQLCMD (suggested in the accepted answer) works with Netezza or just with SQL Server. An alternative is to use Aginity Workbench for Netezza which provides an unattended commandline option:

"C:\Program Files (x86)\Aginity\Aginity Workbench for PureData System for Analytics(x64)\Aginity.NetezzaWorkbench.exe" --unattended --stdout MyOutput.txt --stderr MyErrors.txt --description "New Execute SQL Command" --action exec --connstr "Driver={NetezzaSQL};server=mynzbox;UserName=myuser;Password=mypwd;Database=system;LoginTimeout=120" --dbtype NetezzaODBC --sqlfile MyQuery.sql


What does that indicate?

It indicates that invalid page flags for protection are set, mostly likely caused by something in the hardware, Windows I/O stack, or 3rd party software. In supported SQL Server versions there are three options for page protections:

None
Torn Page
Checksum

Imagine reading a page from disk and doing basic sanity checks against it. One check might be, let's check to make sure the page protection options are set appropriately. If there are three options, having a 4th value set or having multiple options set would be invalid.

  I'm just curious about the "root cause" piece, and what could possibly cause this flavor of the logical consistency-based I/O error.

It indicates that something scribbled the data on disk or the data being read from the disk into memory. The root cause for on-disk corruption is extremely hard to get if it isn't reproducible, so at best it's a bad piece of hardware or software in the I/O stack. Worst is it's another application not playing nice and doing whatever it wants to files on that server.


Does Active Data Guard include Golden Gate?

It's the other way around. Active Data Guard is included if you license GoldenGate on Oracle Database Enterprise Edition.

The GoldenGate product page on Oracle Technology Network states:

  The license for Oracle GoldenGate includes a full use license for
  Oracle Active Data Guard, and a full use license for XStream in the
  Oracle Database.


I need to log activity of the sa account

Your immediate answer predominantly resides here, the official SQL Server Audit documentation. The larger discussions of performance, requirements, actual security issues addressed, threats mitigated, and what-the-hell-is-wrong-with-your-auditors has been alluded to in the comments and isn't a great candidate for Stack Exchange.

For your scenario you could do something like this:

1) Create the Server Audit

USE [master]

GO

CREATE SERVER AUDIT [Audit-GeneralServerAudit]
TO FILE
(   FILEPATH = N'\\NetworkStore\AuditLogs'
    ,MAXSIZE = 1024 MB
    ,MAX_FILES = 70
    ,RESERVE_DISK_SPACE = ON
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = SHUTDOWN
)
GO

ALTER SERVER AUDIT [Audit-GeneralServerAudit]
WITH (STATE = ON);

2) Define the Server Audit (modify events as needed)

CREATE SERVER AUDIT SPECIFICATION [ServerName - Audit]
FOR SERVER AUDIT [Audit-GeneralServerAudit]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD(DBCC_GROUP)
WITH (STATE = ON)
GO

3) Create Database Audit(s) as needed. Create additional Server Audits to separate log files for easy use/permissions restrictions and to implement different retention/storage strategies

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]
FOR SERVER AUDIT [Audit-DB Specific Audit]
ADD (DELETE ON DATABASE::[DBName] BY [public]),
ADD (SELECT ON DATABASE::[DBName] BY [public]),
ADD (UPDATE ON DATABASE::[DBName] BY [public])
WITH (STATE = ON)
GO

SQL Server automatically controls log rollover and space as per your file size and number of file definitions. Or the Windows Log settings control this if you elect for that design. Still a yes though - it absolutely can get out of control in terms of log size and over-writes (loss of audit data) if you don't setup the correct capacities.

If you're auditing a single user, why not audit other users and issues? You know, in-case someone else or another account is performing unwanted behavior.

Even basic log files are probably massive... as stated in comments, auditors need a clear definition of "unusual activity" or a clear methodology to automate finding it.

Intrusion Detection is unlikely to be caught by undefined anomaly detection. An actual audit strategy needs more than just the log files. I haven't seen much in the way of pre-built solutions for SQL Audit log files, but there are a lot of web, network, server log file solutions out there... maybe one is adaptable. Some example reports and setup guides here to get started on very basic reporting.


mysql: counting number of tickets which are open per day basis

You can use conditional sum for this calculation something as

select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select
    -> date_format(created,'%d-%m') as created_date ,
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE
The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select
 date_format(t.created,'%d-%m') as created_date ,
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created)
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+


How to modify authentication plugin in mariadb starting with 10.4.1 to get mysql access for root user without password when migrating mysql to Mariadb

see: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

ALTER USER root@localhost identified via  unix_socket;
flush privileges;

also

mysql

now works for the root user without password.

The statement *"The unix_socket authentication plugin is also installed by default ..."

does have no effect if you migrate from Oracle's mysql to mariadb


There is no Run As proxy for T-SQL Job steps in SQL Server Agent.  You could upvote this with the chance that one day it will be filled:

https://connect.microsoft.com/SQLServer/feedback/details/632955/enable-sql-agent-proxies-to-run-t-sql-job-steps

The current way to get a T-SQL command to run with a proxy is to run it as an Operating System Command which does support a proxy user (instead of T-SQL which does not) and run the task either using SQLCMD.EXE or PowerShell to execute the code.


how to use with clause results as a temporary table

You can write this query without using a CTE or temp table:

SELECT id,
       UPPER(file_name) [Name],
       CAST(file_value AS INT) [Value],
       SUM(CAST(file_value AS INT)) OVER (PARTITION BY file_type) AS type_total,
       AVG(CAST(file_value AS FLOAT)) OVER (PARTITION BY file_type) AS type_avg
FROM test_example
WHERE id % 2 = 0

Is that any better?


How to compute number of concurrent application sessions using SQL Server table storing session data?

I find SQL Server 2012 to be a much better fit for this kind of problem because it supports ORDER BY in the OVER clause for the SUM window aggregate. Throwing your data into a temp table:

CREATE TABLE #my_sessions (sessionId INT, sessionStart DATETIME);

INSERT INTO #my_sessions VALUES
(1, '20180413 12:00:00'),
(2, '20180413 12:15:00'),
(3, '20180413 12:30:00'),
(4, '20180413 12:45:00'),
(5, '20180413 13:00:00');

I'll break the query into three parts so it's easier to understand. The first trick uses a running total to get the number of concurrent sessions at each time it changes. Imagine taking your data, assigning a 1 for the rows that create a session, and making a second copy of it for the destroyed rows and assigning a -1 for those rows. If you calculate a running total ordered by time you end up with the number of active sessions at each time the value changes.

SELECT DISTINCT
  event_time
, SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
FROM #my_sessions
CROSS APPLY (
    VALUES
    (sessionStart, 1),
    (DATEADD(HOUR, 1, sessionStart), -1)
) ca (event_time, event_change);

Here are the results:

╔═════════════════════════╦═════════════════╗
║       event_time        ║ active_sessions ║
╠═════════════════════════╬═════════════════╣
║ 2018-04-13 12:00:00.000 ║               1 ║
║ 2018-04-13 12:15:00.000 ║               2 ║
║ 2018-04-13 12:30:00.000 ║               3 ║
║ 2018-04-13 12:45:00.000 ║               4 ║
║ 2018-04-13 13:00:00.000 ║               4 ║
║ 2018-04-13 13:15:00.000 ║               3 ║
║ 2018-04-13 13:30:00.000 ║               2 ║
║ 2018-04-13 13:45:00.000 ║               1 ║
║ 2018-04-13 14:00:00.000 ║               0 ║
╚═════════════════════════╩═════════════════╝

Now we need to take the average of that value. I assume that you want a weighted average by time, so what's missing is the number of minutes that each measurement should count for. SQL Server 2012 introduces the LEAD function which makes this pretty easy. Now the query is as follows:

SELECT
  active_sessions
, DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
FROM
(
    SELECT DISTINCT
      event_time
    , SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
    FROM #my_sessions
    CROSS APPLY (
        VALUES
        (sessionStart, 1),
        (DATEADD(HOUR, 1, sessionStart), -1)
    ) ca (event_time, event_change)
) active_sessions

The intermediate result set:

╔═════════════════╦══════════════════════╗
║ active_sessions ║ minutes_until_change ║
╠═════════════════╬══════════════════════╣
║               1 ║ 15                   ║
║               2 ║ 15                   ║
║               3 ║ 15                   ║
║               4 ║ 15                   ║
║               4 ║ 15                   ║
║               3 ║ 15                   ║
║               2 ║ 15                   ║
║               1 ║ 15                   ║
║               0 ║ NULL                 ║
╚═════════════════╩══════════════════════╝

We need to calculate the average which is the easiest part. Putting it all together:

SELECT 1.0 * SUM(active_sessions * minutes_until_change) / SUM(minutes_until_change)
FROM
(
    SELECT
      active_sessions
    , DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
    FROM
    (
        SELECT DISTINCT
          event_time
        , SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
        FROM #my_sessions
        CROSS APPLY (
            VALUES
            (sessionStart, 1),
            (DATEADD(HOUR, 1, sessionStart), -1)
        ) ca (event_time, event_change)
    ) active_sessions
) average_me
WHERE minutes_until_change IS NOT NULL;

The final result is 2.5.

Apparently, this can also be done in SQL Server 2008.


B+ Tree structure with buckets (Begginer question)

It is not mandatory for a b+tree leaf to have a pointer towards the next leaf, but it is a widely used optimization to allow efficient scanning of value ranges, such as, in the context of relational databases, supporting WHERE field BETWEEN x AND y or WHERE field > x predicates. Frequently you see a leaf pointing to both next and previous leaves, supporting bidirectional scanning as well (for WHERE field < x). Apart from the search predicate support, it may also be useful for record ordering and aggregation.


Unable to view partition table in a schema

If you created the table with the above statement as user MDR_139, then that table belongs to the MDR_139 schema, not TESTSCHEMA.

If you want to create tables in other schemas, add the schema specifier to the create statement, for example:

create table TESTSCHEMA.sales_test (year number(4),
                    product varchar2(10),
                   amt number(10,2))
     partition by range (year)
     (partition p1 values less than (1992),
     partition p2 values less than (1993),
     partition p3 values less than (1994),
     partition p4 values less than (1995),
   partition p5 values less than (MAXVALUE));


SQL agent job Error while migrating from 2008 R2 to SQL 2014 for openrowset query

Try using WITH RESULTSETS as indicated by this connect item

https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error


Alter table add column with no default blocks

The problem is not your ALTER TABLE, but other transactions in the database.

You must have some long running transactions that hold any lock on the table (the ACCESS SHARE lock from a SELECT is sufficient to bloch the ACCESS EXCLUSIVE lock of an ALTER TABLE).

Your ALTER TABLE has to wait until this long running transactions are gone, and any later transactions that are trying to access the table have to queue behind the ALTER TABLE.

Close the long running transactions, and the statement will complete in a jiffy.