Why Oracle sessions are still alive after two weeks being Inactive DB: Oracle 10g

O/S: Windows Server 2003 64 bits

I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe)

select * from V$session
where UPPER(program) LIKE '%W3%'
order by logon_time

According to Logon_Time, why are still alive sessions from 31/07/2012 or 01/08/2012 or any session before today (21/08/2012)?

I have configured on sqlnet.ora:
SQLNET.EXPIRE_TIME= 20
So it means that every 20 min Oracle is checking if connections are still active.

All user-schemas have default profile. It would means that no session would never expired or die?

Added in response of Phil's comment:

Added in response of Phil's answer:

I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently.

INACTIVE in v$session merely means there isn't a SQL statement being executed at the exact moment you check v$session.

If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast query startup - there's a much larger overhead logging in to execute one query, then disconnecting every time.

To get the last activity time for each session:

select username, UPPER(program), logon_time,
       floor(last_call_et / 60) "Minutes since active", status
from v$session
where UPPER(program) LIKE '%W3%'
order by last_call_et;

I'd advise against killing sessions unless you know that doing so will not cause problems on the application side (trying to use a session that's been killed, for example).

It may be the case that you're looking at an incorrectly configured connection pool which creates hundreds of connections once the app starts up - the connection pool may be an order of magnitude bigger than it needs to be. I suggest reaching out to the developers/application support staff & taking a look at how the connection pool is configured.

Having done a bit of research, w3wp.exe is the IIS Application Pool Process - you almost certainly want to talk to your IIS webserver admins to help get to the bottom of the connection pooling configuration.


Is there any approach for encrypting mysql column by key? I have a scenario for encrypting user information in mysql db. Is there any approach to encrypting column by key. This key information will be there in application configuration file. This key we won't store any where in databases/triggers.

My approach is while connecting to database from application we'll pass key. With that key we'll get the information. If some user directly comes into db he will see the encrypted data in table columns.

Please suggest how can I proceed?

Have a look at the encryption/decryption functions. For example, using AES (see aes_encrypt):

SET @key_str = SHA2('My secret passphrase',512);
SET @crypt_str = AES_ENCRYPT('text',@key_str);
SELECT AES_DECRYPT(@crypt_str,@key_str);

Which gives, as expected:

+----------------------------------+
| AES_DECRYPT(@crypt_str,@key_str) |
+----------------------------------+
| text                             |
+----------------------------------+
1 row in set (0.00 sec)

So you can store the result from AES_ENCRYPT in the column, and then retrieve it using AES_DECRYPT.

The above code is also compatible with MariaDB (at least 10.2). MySQL additionally supports an init string to the AES_ENCRYPT and AES_DECRYPT functions, see the example at the link to AES_ENCRYPT above.

MySQL also offers InnoDB tablespace encryption, but that will encrypt the whole table, not just individual columns.


Which will be faster for Millions of records? Procedure or Insert statement with multiple values? I have written a perl script that reads row from a tab separated txt file, do some processing on it, and inserts into the MySQL table.Currently, I am making an insert query with 10,000 records (from the script) and finally execute the statement. insert into tablename values (1, 'kamal'), (2, 'nayan'), ......, (10000, 'kumar');  Another approach that came in my mind is stored procedure. Can anyone tell me how can I utilise stored procedure to make my queries faster? Right now, I have tested stored procedure to insert one row at a time, but that would need 10000 procedure calls, while insert statement is asking for just one call.Any help would be appreciated.Engine: InnoDBVersion: 5.5.45

Stored Procedure is better, why? Read this: http://www.sqlbook.com/SQL-Server/Why-use-Stored-Procedures-41.aspx

When a query runs, it calculates the most efficient method of processing the query and stores it in something called the execution plan. Depending on the query, a good deal of the time taken to execute a query is actually spent on calculating the execution plan.

When inline SQL statements are executed the execution plan must be generated each time the query runs. When a stored procedure is called its execution plan is stored in memory and the next time the stored procedure is called the execution plan is retrieved from memory without needing to be recalculated. This increases the speed of execution and improves database performance.


How can I get the latest five scores per user per subject? There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this:

id  |  user_id  |  score  |  dept      | cdatetime
---------------------------------------------------------
1   |  123      |  5.5    | math       | 2014-01-01 12:00
2   |  123      |  8.9    | chemistry  | 2014-02-01 03:20
3   |  123      |  0.2    | physics    | 2014-01-01 09:00
4   |  123      |  1.4    | math       | 2014-03-01 12:00
5   |  456      |  9      | biology    | 2014-01-18 05:20
...

A user can have more than one score per dept.

What I want is: If a person took more than 4 tests in a dept, the average of the last 5 scores per user per dept.

I feel I'm almost there with this query:

SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
FROM  (
   SELECT *
         ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
  FROM mg.scores
  WHERE score IS NOT NULL) as score
   ) AS x
WHERE  x.rn <= 5
GROUP  BY user_id,dept;

However, the depts with less than 5 scores show up :(. There must be something that is wrong in the query to do with window functions, I can't spot it though...

Is there a better way to write this query?

Do you want the ones with less than 5 scores in mix.  If you want only the 5 scores one, you can add a having to your query:

    SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;


When installing mysql in linux, why do we create a mysql group and user? When installing mysql in linux, why do we create a mysql group and user ?
The installation step tells to create a user and to change the ownership of mysql folder to mysql.

Every process under linux runs under specific user privileges. Services (like MySQL) usually need to open ports and access various system resources during startup, so they are required to be started as root user. However, it is not safe to have all the processes run under root as it is not required for continuous operation of services, thus it is recommended to create a special user, which will be used to run MySQL service. MySQL will only be able to access what special user can, and this is going to be limited to MySQL files on the system.

This is usual practice in linux. If you, however, use you distributions built-in package manager to install MySQL, this will be done for you automatically (in most distributions at least).


Can I downscale/upscale my MySQL server automatically? Is this doable? Since we know the peak times of our platform we are planning to downscale our DB after peak hours then upgrade the specs before the start of peak hours, how can we do this? Like how is RDS doing this?

the feature you want is currently in preview - it is called flexible server for mysql
https://azure.microsoft.com/en-us/pricing/details/mysql/flexible-server/


SQL Server 2017 MLS - cant add python package I'm new-ish to MLS and have been trying to add new python packages like  attrs  to the python but it hasn't been working.
Our set up is SQL Server 2017 with CU14 on Win 2016.
Python version is 3.5.2
The usual command I run is:
pip install attrs

and I run this from the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Scripts
but nothing happens.....
it says "collecting attrs" then nothing.
When I look in the So I updated a dev VM to SQL Server 2017 CU22, and it installed Python 3.7 and tried same thing - it worked.
But this doesn't help me as we already have existing SQL Server 2017 servers with this package in it and the dude who originally set this up is gone, so any help appreciated.
All the installs are default, nothing special.

I worked it out, it seems the VM it was running in was too high VMware hardware level.
When I dropped the hardware back to a lower version, it worked.
Thanks


Updating a column in one table based on matches with column in another table I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = table2.col2. I found out that SQLite3 does not support join in updates like postgresql (with which I am familiar). How to achieve this?

You can try something like this, this is setting table1's col3 value to table2's col3 value where they match on col1 and col2. You can replace the col3 with your 'some value'.

create table table1 (
col1 int,
col2 int,
col3 int
);

create table table2 (
col1 int,
col2 int,
col3 int
);

insert into table1 values (1,2,3);
insert into table1 values (4,5,6);

insert into table2 values (1,2,10);
insert into table2 values (4,5,20);

select * from table1;

update table1
set col3 = (
 select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
)
where exists (
select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
);

select * from table1;

DB Fiddle


Allow only one reader to a table I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again.

However due to multiple worker/threads they many times read simultaneously. I want that only one reader should be allowed at a time and then update the column value.

select port from socketport where available = 't'
update socketport set available = 'f' where port = port (from above)

Can above select statement be locked and then updated without releasing lock?

Edited: look at the last example is a better way

Yes, but you must send the two sentences within a transaction and add the "for update" clause in the select sentence.

Python

If you send then sentences one by one you can write something like this (in Python the connection handles transactions)

conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
cur = conn.cursor()
cur.execute("select port from socketport where available = 't' limit 1 for update")
row = cur.fetchone()
port = row[0]
cur.close();
cur = conn.cursor()
cur.execute("update socketport set available = 'f' where port = %s", (port))
cur.close();
conn.commit();

node.js

If your language does not support transaction you must include manually:

const { Client } = require('pg')
const client = new Client(connOpts)
try{
  await client.query("BEGIN TRANSACTION");
  var result = await client.query("select port from socketport where available = 't' limit 1 for update");
  var port = result.rows[0].port;
  await client.query("update socketport set available = 'f' where port = $1", [port]);
  await client.query("commit");
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

pure postgresql

You can use direct postgres. Any block is within transaction

do $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
end;
$SQL$;

looking for a better solution

using a function

You can do something like this. A postgres function that does the job and returns the port, then you can call the function from python (or whatever you want).

create or replace function get_avaialbe_port() returns integer
  language plpgsql
as $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
  return v_port;
end;
$SQL$;

then in your backend you can call:

select get_avaialbe_port()

All code inside a function runs inside a transaction. The "for update" is stil needed, because without it two functions can run simultaneously and can take the same value of v_port prior to the update statement.

only one sentence

Also you can update the selected value in one sentence

update socketport
  set available = 'f'
  where port in (select port from socketport where available = 't' limit 1)
  returning port;

You can call it directly from python or node.js. You can see a postgres running example at: http://rextester.com/OKMC68108

UPDATE sentence with RETURNING clause acts as SELECT you can query it and fetch the values. UPDATE ... RETURNING is like a SELECT that updates.

NOTES

Watch at LIMIT 1 in the examples. In the postgres version it is mandatory because of SELECT INTO. In python and node.js version it is recommended because in that way only one row is locked. Then the table can be updated with releasing ports or taking others using some kind of "order by random"


How do I convert the sum of minutes into Hours:Minutes format How do I add the value of two money fields that contain a number of minutes and return a string formatted as hours:Minutes?

The query I have so far:

SELECT (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type ON action.act_type_id = dbo.act_type.act_type_id
  WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
  AND (action.incident_id = inc.incident_id)
) AS [Travel Time], (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type AS act_type_6 ON action.act_type_id = act_type_6.act_type_id
  WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
  AND (action.incident_id = inc.incident_id)
) AS [Total Productive Time]
FROM dbo.incident AS inc
INNER JOIN dbo.assyst_usr ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
INNER JOIN dbo.serv_dept ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id

Expected Result:

Travel Time     Total Productive Time              Total Service Time
60.00           17.00                              1:17
2520.00         10240.00                           212:40

Description:

'Travel Time' and 'Total Productive Time' are retrieving perfectly, please help me to get the 'Total Service Time' in (HH:Min) format.

You'll simply have to build that format manually.

SELECT  t.TravelTime, t.TotalProductiveTime,
        TotalServiceTime = CONVERT( NVARCHAR( 16 ),
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) / 60.0 ) )
            + N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) % 60.0 ) ) )
FROM (  SELECT  TravelTime = CONVERT( MONEY, 2520.00 ),
                TotalProductiveTime = CONVERT( MONEY, 10240.00 ) ) t

The above calculation divides the total number of minutes by 60 to get the number of hours, then uses the modulo operator (%) to return the remaining number of seconds.  The results are appended together to form the hours:minutes string desired.

To put this into the context of your query, you could put your T-SQL into a CTE or subquery, then perform the calculation above on the two components:

;WITH src AS (
    SELECT (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type
          ON action.act_type_id = dbo.act_type.act_type_id
      WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Travel Time], (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type AS act_type_6
          ON action.act_type_id = act_type_6.act_type_id
      WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Total Productive Time]
    FROM dbo.incident AS inc
    INNER JOIN dbo.assyst_usr
        ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
    INNER JOIN dbo.serv_dept
        ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id
)
SELECT src.[Travel Time]
    , src.[Total Productive Time]
    , [Total Service Time] = CONVERT(VARCHAR(20)
        , FLOOR((src.[Travel Time] + src.[Total Productive Time]) / 60))
        + ':'
        + CONVERT(VARCHAR(20),
             (src.[Travel Time] + src.[Total Productive Time]) % 60
          )
FROM src ;


What is the best option for High availability on a data warehouse? I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with SQL 2017 standard on each and has now asked me to plan a HA/DR plan for the new DW database/instance.

I immediately thought of using AlwaysOn Availability Groups, although I have never used them before, and none of the articles I read talk about typical data warehouse workloads - it's all OLTP applications. With a large daily ETL process and a smaller intra-day ETL process running on their current DW, will that have an impact on how we approach this?

Thanks - any help to point me in the right direction here would be beneficial!

Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since there's no automatic failover.

If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a Failover Cluster.

It doesn't care what recovery model your databases are in, since it's more reliant on Windows than SQL Server. Your SQL Server just has the option to live on different nodes in the cluster if something goes amok with one. The one requirement of this technology is using shared storage, like a SAN.

You get automatic failover in most situations, but no readable replica (you don't get that with BAG, either). With Standard Edition, you're limited to a two node cluster, but that shouldn't be a big deal.

Failover Clusters are also a lot easier to manage if you're not too savvy with SQL Server. You really need basic Windows sysadmin skills. AGs can be a tough cookie when they go down, or when a patch goes bad (which sadly happens rather often).


Can we create a table in specific datafile - Oracle 11g R2? I am buying a new server than has two HDs:
300 GB with 15000 rpm.
600 GB with 10000 rpm.

I am thinking to allocate the datafiles in both hard disks. and put the less used tables in the data files that reside  in the slower HD that is 600 GB with 10000 rpm.

So, Can we create a table in specific data file - ?

I am using Oracle 11g R2 SOE .

Regards,

You can create two tablespaces, put all the data files for one tablespace on one drive and all the data files for the other tablespace on the other side, and then create the tables in whichever tablespace you would like.  It seems unlikely, however, that you would really want to do this.  It is very unusual to run a database on a server without any sort of RAID configuration.  And it is generally the case that any automatic I/O load balancing that you would implement would outperform your manual load balancing.


Should I backup a database with only temporary data? I have a database, in SQL Server 2008, with only temporary data, but it has tables with a certain structure which I need. Should I back it up?

And if I should, should I make one copy of it or should I include it in the rest of the backup routine?

If there is a database with data on it, someone, at some point, will consider it important. So best approach would be run a full backup to avoid any hassle. Maybe weekly with couple of daily differentials. If there are frequent changes then maybe a daily full backup and one differential in the middle of the day.

If the data is temporary and can be recreated somehow, then I would configure the recovery model to SIMPLE and take a FULL backup daily. And to avoid spaces issues, if the data is not so important and can be recreated afterwards, I would add a process to empty all tables before running the backup. Then configure another step after backup process in order to add fresh data once again so anyone using the database has data to continue working.

And last, but not least, you could just script the database and recreate it from those scripts. Do you have your database code under source control? That would help a lot on this case. We have couple of databases with similar characteristics and we only save the recreation scripts, data can be easily recreated with couple of scripts.


Adding a new database to an existing sql cluster I got a requirement in my company, where a third party application requires just one database. The Application owner told me that he is OK with the database being created in an existing SQL Server instance on a cluster.  I have two questions regarding this

1) Can I use the existing drives (Data,log) drives, create a new database and map the mdf and ldf to the existing mountpoints?

2) Do I need to create another MSDTC drive for this application?

Please let me know. Thanks in advance!

Can I use the existing drives (Data,log) drives, create a new database and map the mdf and ldf to the existing mountpoints?

Yes you can use the existing drives for new database. You would need a new set of shared drives if you are installing new SQL Server clustered instance.

  Do I need to create another MSDTC drive for this application?

Not necessarily, a single MSDTC can work for multiple instances. But if you want to know what all queries need MSDTC I would direct you to Cindy Gross Blog Do I Need MSDTC for SQL Server Cluster. Quoting

  If your application calls DTC directly or if you use a feature in SQL Server that calls DTC, then you need DTC to be available. Some examples of how SQL Server uses DTC: linked servers, OPENROWSET, OPENQUERY, OPENDATASOURCE, remote procedure calls, BEGIN DISTRIBUTED TRANSACTION, updatable subscriptions for transactional replication (immediate and queued updating are now deprecated). You can choose to enlist in a DTC transaction from CLR, SSIS, and DTS. SQL Server does not support using DTC with some features such as database mirroring.


Restore .bkp file of different version oracle I have 12.1 Oracle version .bkp file. Can I restore it in 11.2 Oracle XE?
I have used below command:

 RMAN> restore controlfile from
2> '/home/db_dump/201101.BKP';

And now getting below error:

ORA-19690: backup piece release 12.1.0.0.0 incompatible with Oracle release 11.2.0.2.0

Please help me to figure out this or give some conclusion.

No, you can't restore 12c backup in 11g environment.

If you need to copy data, then restore backup with 12c version and use expdp to export. After that import data to 11g.


Queries are performing bad after SQL Server 2008 SP4 upgrade We had SQL Server 2008 SP2 and Microsoft told us to upgrade it to SP4 if we wanted any support from them. Now that Microsoft has warned us, my path to go to SQL Server 2016 SP1 is clear. Going back to my question, I upgraded our production servers on Saturday to SQL Server 2008 SP4. But stored procedures which were performing fast (this is a relative term) are running way too slow on Monday. I don't see any blocking. Users are not Memory pressure seems normal. I see Available as well as Free Memory when I open task manager. CPU is a bit high. But not pegged at 100%. My guess is, plan cache was flushed after rebooting servers post upgrade and queries are taking hit today. All index maintenance jobs were successful prior to upgrade. Should I have run full index maintenance after the upgrade? I ran DBCC CHECKDB after upgrade and it was clear. Replications are in sync. Modified Stats are updated daily. Let me know if you need any more information. Any insight will be a great help. Thank you.

We opened a support ticket with Microsoft and they gave us a tool to run to gather metrics when issue was in progress. I couldn't run the tool during business hours since I didn't want it cause problems for working processes. Then in the evening, update statistics job ran for almost twice long than before. Probably that produced better query plans for queries running on next morning i.e. on Tuesday. It was way better than Monday. I observed the performance today. It's back to normal. I didn't have to use Microsoft tool at all. Go figure! Thank you.


Update one mysql column when another is edited I'm trying to make a datetime field that automatically gets updated with the current time only if there was a change to a certain field.

It seems I have a syntax error.
I try to have last_progress_date that gets the date when the progress_percentage is updated/edited:

CREATE OR ALTER TRIGGER last_progress_date
ON wp_task_mgr
AFTER UPDATE
AS BEGIN
   IF UPDATE (progress_percentage)
   SET last_progress_date = GETDATE()
END

The code doesn't look like valid Mysql code:

ALTER is not a valid option
No AS in Mysql
IF UPDATE(column)

UPDATE(column) tests whether column updated in SQLServer, not in Mysql
IF syntax is invalid (check https://dev.mysql.com/doc/refman/5.5/en/if.html)

Finally, you can change value of the record which is being modified in BEFORE trigger, not in AFTER.

You need something like

drop trigger if exists last_progress_date;
delimiter //
CREATE TRIGGER last_progress_date BEFORE UPDATE ON wp_task_mgr
FOR EACH ROW
BEGIN
   --assuming progress_percentage is not nullable; if it is, the condition
   -- needs to be modified to properly handle NULL values
   IF (NEW.progress_percentage != OLD.progress_percentage)
   THEN
       SET NEW.last_progress_date = GETDATE();
   END IF;
END;
//
DELIMITER ;


Same Query plan, no new query competition, same hardware, but stored procedure runs slowly We have an SSRS report that is based on about 8 stored procedures which was running fine up until a few days ago. The report used to run in about 2 hours. Last Sunday (September 15 2019) it suddenly started timing out based on the SSRS timeout setting. We changed this to more time and found that it finishes after 6 hours. We're also seeing CPU contention when this report runs that we didn't see previously. What's strange is that I can't seem to find any apparent changes that would account for this:

The SP is using the same query plan it's used for weeks. So, in QueryStore you can see it completing in its usual 2 hours for days and suddenly spike 9/15. Even after a restart, the same plan was picked and it isn't being forced.

The underlying server is virtual, but we didn't change any resources until a few days later when we ended up adding more cores (which helped). Our VM guy isn't seeing any underlying hardware contention and from what I've seen, he's right.

The SSRS report itself wasn't changed. Same subscription, same configuration (other than the timeout setting being changed after the first night of bad performance). Statistics are set to update right before this report kicks off.

I'm pretty stumped here. I hate that for now we've had to settle with just throwing more resources at it. I tried searching for this issue online, but no one else seems to have posted about this specific scenario. Any suggestions? Below is the part of the code that takes up to 6 hours total.

EDIT: Link to Query plan: https://www.brentozar.com/pastetheplan/?id=SJ0XtSWwH

SELECT DISTINCT Object1.Column1
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1) AS Column4
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7)
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7) * ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1) AS Column8
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7)
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7) * ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1)
            / NULLIF(Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1), ?)
            / ? AS Column9
INTO Object4
FROM Object5 Object1
JOIN Schema1.Object6 Object7 ON Object7.Column1 = Object1.Column1
LEFT JOIN Schema1.Object8 Object9 ON Object9.Column10 = Object1.Column10
    LEFT OUTER JOIN Object10 Object3 ON Object3.Column10 = Object9.Column10
                        AND Object3.Column11 = ?
    JOIN (SELECT Object11.Column10, Function3(Object11.Column12) AS Column13
        FROM Object12 Object11 (NOLOCK)
        WHERE Object11.Column14 = ?
        GROUP BY Object11.Column10) Object13 ON Object9.Column10 = Object13.Column10

    JOIN Object12 Object2 (NOLOCK) ON Object2.Column10 = Object13.Column10
                        AND Object2.Column12 = Object13.Column13

    LEFT JOIN Database1.Schema1.Object14 Object15 ON Object2.Column15 = Object15.Column16
                                  AND Object15.Column14 = ?
                              AND Object2.Column17 IN (?)

One possible cause for this sudden increase in duration is that there is another process blocking this query from making progress.

You mentioned that you have query store enabled.  I would run a query like this, and look for the point where avg_duration jumps up from 2 hours to 6 hours (after 9/15):

SELECT
    qsrs.first_execution_time,
    qsrs.avg_duration,
    qsrs.avg_cpu_time,
    qsrs.avg_logical_io_reads,
    qsrs.avg_logical_io_writes,
    qsrs.avg_dop,
    qsrs.avg_rowcount
FROM sys.query_store_runtime_stats qsrs
WHERE qsrs.plan_id = @your_plan_id
ORDER BY qsrs.first_execution_time DESC;

If the problem is blocking, you should see all of the other values (CPU, reads, writes, etc) stay mostly the same.  You should then be able to track this problem down by finding other queries running during this time period (either using Query Store, or running sp_WhoIsActive, or logging it to a table).

If other values in the runtime stats did change, then at least this will be a clue as far as where to look next.  For instance, I included avg_dop because maybe the query has suddenly stopped being able to use parallelism as much because of a settings change you didn't know about.

Since you've determined that CPU increased with duration, but the other metrics stayed the same, I'm tempted to think that the VM is fighting to get CPU time on the host, possibly because it's oversubscribed and there are other CPU heavy workloads running on other guests.

Jonathan Kehayias has an interesting post that shows SOS_SCHEDULER_YIELD waits could result from this situation (CPU Ready Impact on SOS_SCHEDULER_YIELD).  If you're logging wait stats, you might look to see if you can correlate the increase in duration with increases in this wait type.  Or better yet, work with your VM admin to see if there's evidence on the host side:

CPU Ready Time in VMware and How to Interpret its Real Meaning
Hyper-V Equivalent to VMware CPU Ready Time


SQL Server 2000 on Windows 10 I need to install SQL Server MSDE 2000 SP4 on Windows 10.

I have other machines that run Windows 7 and 8.1 and SQL Server MSDE 2000 works fine (with merge replication configured too).

Now I testing the same scenario with Windows 10, but setup is closing unexpectedly during installation.

I know that this version is very old, but is there a workaround to install it on Windows 10?

I know of no hacks to get SQL Server 2000 to install on an existing Windows 10 installation. There are probably forces against you from both within the setup program (which looks for certain things on the host system) and within the operating system (which prevents many flavors of older software from getting installed, mostly for security reasons but also compatibility, stability, etc). I have tried in the past and even with compatibility override options I came across the same failures every time.

A couple of ideas:

Use a virtual machine. This really is the simplest and most obvious solution. Most (if not all) editions of Windows 10 now support Hyper-V natively; if not, there is always VMWare Workstation (not free) and Oracle VirtualBox (free).
Install 2000 then upgrade. Not really practical, especially if you want to use the Windows 10 installation you already have, but if you were doing this from scratch, you could install XP/Vista/Win7, all of which should allow you to install 2000, and then upgrade. Take care, though, because some methods of upgrading to Windows 10 will relegate all of your programs and services to Windows.old, and they won't run any more.

In either case, firewall the crap out of that thing, because the most recent security patches for 2000 are hard to find, and the ones that I found wouldn't install anyway...


Return Only Rows where count >= 1 This is my data set-up, I am needing a way to return only itemsnames and itemonhand where the count of itemonhand is >= 1

I tried simply adding an OR statement to the query, but that is returning items that have a 0 count.

  Declare @TableTest Table (dealerID int, itemname varchar(100), itemonhand int, itemname2 varchar(100), itemonhand2 int, itemname3 varchar(100), itemonhand3 int)
  INSERT INTO @TableTest (dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3) VALUES
  (1, 'ball', 0, 'hat', 1, 'sock', 0)
  ,(2, 'ball', 0, 'hat', 0, 'sock', 1)

  Select * FROM @TableTest
  where itemonhand2 = '1'
  OR itemonhand3 = '1'

My desired result set is

1  hat  1
2  sock 1

I just want one row for each dealerID with all items returned on that one row.

Live by the cross apply, die by the cross apply

 CREATE TABLE #TableTest
 (
   dealerID INT,
   itemname VARCHAR(100),
   itemonhand INT,
   itemname2 VARCHAR(100),
   itemonhand2 INT,
   itemname3 VARCHAR(100),
   itemonhand3 INT
 );
 INSERT INTO #TableTest
        ( dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3 )
 VALUES ( 1, 'ball', 0, 'hat', 1, 'sock', 0 )
  ,     ( 2, 'ball', 0, 'hat', 0, 'sock', 1 );

 SELECT tt.dealerID,
        ca.*
 FROM   #TableTest AS tt
 CROSS APPLY ( SELECT   *
               FROM     ( VALUES ( itemname, itemonhand), ( itemname2, itemonhand2), ( itemname3, itemonhand3) ) x ( item_name, item_count ) ) ca
 WHERE  ca.item_count = 1;


Restoring MDF without LDF file I am trying to attach a database just from the MDF file, cause the server crashed and could never find the LDF. I am on a 2019 MS SQL Server. I am following the instructions from this page. I have created the database, took it offline and swapped my MDF and deleted the created LDF. Then put back online. When I run the following command I get the following:

UPDATE sysdatabases
SET status = 32768
WHERE name = 'MyDB'

Ad hoc updates to system catalogs are not allowed.

I ran the previous command without problems

USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

Furthermore, if I run the next command, I get the following:

DBCC TRACEON (3604)
DBCC REBUILD_LOG(Database_Name, 'g:\Program Files\Microsoft SQL Server\MSSQL15.AUS\MSSQL\Data\MyDB.ldf')
GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2526, Level 16, State 3, Line 2
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

That is not an authoritative source.  Please follow Paul Randal's process here, as he's the one who "decided to add a new feature to SQL Server 2005 called EMERGENCY-mode repair".


SET ARITHABORT, .NET, and explanations inconsistency? A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I tried to read as much as possible about it, but I still have some hard time building a definitive picture.

The evidence:

ARITHABORT is one of the parameter that is officially documented by MS to be used in the choice of the plan to use
Plans created with ARITHABORT set to ON and set to OFF are clearly different
All versions of SQL Server ships with ARITHABORT set to ON
Microsoft recommends to keep it always set to ON
.NET always set it to OFF, which can, and do, leads to some horrible performances

Aside from wondering why .NET always set it to OFF, the matter would look quite settled, and thus I'd proceed to explain it to my tech leader, and that we need to set it back to ON every time we open a connection to the server.

But then, it seems there are quite a few voices advocating the contrary, one especially is linked in the second answer to this question:

Why would SET ARITHABORT ON dramatically speed up a query?

I've tried to go through the entire document, but he lost me quite quickly, especially in the part where he says that ARITHABORT is un-influent when compatibility lever is set at 80 or more, which is not clearly the case. And he lost me even more when, like many others, seems quite firm on stating that Microsoft documentation is wrong about the importance of the ARITHABORT parameter. On top of that, he suggests that if the SP is a lot slower when run from code and it's extremely faster when run from SSMS, the fault is in the SSMS and we should instead correct this one so that it set ARITHABORT to off.

Quite honestly...I'm a little lost. Is there some definitive evidence about the entire stuff?

Should we always force .Net to use ARITHABORT set to on, accordingly to with what the company who produce SQL Server says, or what?

The sql engine is generating you a bad plan.  As a starting point make sure your regular maintenance is getting fresh statistics and you don't have fragmented indexes so the engine is trying to work with valid numbers.  If the basics are solid you are more than likely having parameter sniffing issues.  Put simply sometimes the engine is getting a good plan and sometimes it is making a very bad estimate based on a parameter which gives you a different execution plan done in a terrible way resulting in poor performance.

Arithabort setting does not create this performance issue as you would think looking at your fast execution.  .NET using the setting off and SSMS using the setting on just guarantees you won't use the same plan and it will come up with a new plan probably the correct one.  But if you just turn arithabort on for .NET you will probably run into the same bad estimate issue.  Rather what you want to do is leave arithabort off and then try a sample execution of the query with the setting OFF from SSMS to make it mimic running from code and setting ON to compare to help you discover when you are having a problem.  You may or may not be able to figure out based on the table data where the issue lies for instance we had a table with a pseudo-PK that was 0 half the time and had a value the rest of the time so queries had trouble using it as the key is should have been.

The best way to deal with this once you discover this is a problem is the add the I found a parameter sniffing issue specific hint optimize for unknown.  You should be able to search for that and find the syntax that fits your case.


Database design for Time Sheet adopting different weekend My Requirement
I need to design a database that handles multiple time sheet formats for their staff\contractor working with different clients. Each of them may have a different week end say

Client 1 ==> Saturday & Sunday
Client 2 ==> Friday & Saturday
Client 3 ==> Sunday & Monday
Client 4 ==> Saturday & Sunday
Client 5 ==> Saturday & Sunday
Client .. ......................................................

My Query

How should I design the DB to implement this?

I will be having a Timesheet table.

I will be also having a Holiday table to include the public holidays for different clients.

Where can I design the table to include the different week ends?
Please suggest.

Update

I need to implement the below - to restrict or alert weekend day - while allowing option to add the time sheet data. The weekend days are different (targeting different time sheet format) for each staff as per the client they are working with.

So as per your suggestion Joel, including the table WeekDay to my design, the below way is the option you recommend to implement different weekends?

Timesheet        ==> TimesheetID,EmpID,SubmittedDate, ClientID ...
Timesheet_Details ==> Timesheet_Details_ID,TimesheetID,Date,WeekDayID,IsHoliday
Client            ==> ClientID,TimesheetformatID,..
...
...
...
Holiday           ==> HolidayID,HolidayDescription,Date,ClientID ...
WeekDay           ==> WeekDay_ID,DayName,DayNumber,ClientID...

You don't say so explicitly, but I think maybe your concern is that you want to have a timesheet that has one column per day and you don't know what to do about inconsistent weekends.

If that's the case, then you're approaching it incorrectly.  Don't have columns for days.  Have rows in a details table for days.

Your timesheet should use individual entries at a date level, assuming you only want one entry per day.  If you want multiple entries per day (per worker) then have one entry per date and start time.

For weekends you keep a separate table for working days (of the week) per worker.

Consider something like this:

Note that the WEEKDAY and TIMESHEET_STYLE_DETAIL tables (in blue) are a bit contrived. You could these tables, but really in practice I think a lot of people would probably use something like a bit flag array in the TIMESHEET_STYLE table containing a flag for each day of the week instead.

Note however that some of your SQL queries would actually be simpler if you did have an explicit WEEKDAY table and an intersection table (TIMESHEET_STYLE_DETAIL) that contained a row for each day of the week.  This way SQL functions that convert a date into a day of week number could be used as part of joins against this table.


Replication with only update and insert I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to remain on the slave?

If you're just going to delete this extra data manually on the odd occasion, you can simply turn off the binary logging for the current session. On the MySQL prompt run the following:

set sql_log_bin=0;

Then any query you enter won't be sent to the binary log, and thus won't be sent across to the slaves.

Once you're finished, re-activate binary logging with

set sql_log_bin=1;

Reference: MySQL manual


Can I merge duplicate results into a single result? (SQL) This is my query:

SELECT
    CONCAT((SELECT LEFT (Event_Start_Time, LEN(Event_Start_Time) -3)),
            ' ','-',' ',
           (SELECT LEFT (Event_End_Time, LEN(Event_End_Time) -3))) AS 'Time',
    CASE WHEN Course_Code IS NULL
        THEN Course_Title
        ELSE Course_Code
    END AS 'Course',
    CASE WHEN User_Last_Name
           IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
        THEN NULL
        ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
    END AS 'Instructor',
    Room_Name AS 'Room'
FROM
    Events
    JOIN Courses ON Courses.Course_ID = Events.Course_ID
    JOIN Users   ON Users.User_ID = Events.User_ID
    JOIN Rooms   ON Rooms.Room_ID = Events.Room_ID
WHERE
    Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
ORDER BY
    Event_Start_Time, Event_End_Time, Users.User_Last_Name;

Current Output:

08:00 - 11:30     NURS3405 F.Lastname1     LabA
08:00 - 11:30     NURS3405 F.Lastname2     LabB
08:00 - 11:30     NURS3405 F.Lastname3     LabC
08:00 - 11:30     NURS3405 F.Lastname4     LabD

Expected output:

 08:00 - 11:30 NURS3405 F.Lastname1 LabA
                        F.Lastname2 LabB
                        F.Lastname3 LabC
                        F.Lastname4 LabD

And just be one row? I'm not sure if this is possible.

You can do it using the row_number() window function. What (I think that) you want is to PARTITION your data into subgroups, where each partition has a common (Time, Course). Then, for each partition, you want to output Time, Course only on the first row of each partition. In your example, all rows become one single partition; but this could not be the case.

The SQL query you would use would start with your original one, and add a few tricks to "hide" the unwanted output:

; WITH e AS
(
    /* We start with a (slightly simplified) version of the original query */
    SELECT
        CONCAT(CONVERT(VARCHAR(5),Event_Start_Time, 108), ' - ', CONVERT(VARCHAR(5),Event_End_Time, 108)
        ) AS Time,
        CASE WHEN Course_Code IS NULL
            THEN Course_Title
            ELSE Course_Code
        END AS Course,
        CASE WHEN User_Last_Name IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
            THEN NULL
            ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
        END AS Instructor,
        Room_Name AS Room,
        Event_Start_Time, Event_End_Time, User_Last_Name /* We will still need these columns at output to sort by */
    FROM
        Events
        JOIN Courses ON Courses.Course_ID = Events.Course_ID
        JOIN Users ON Users.User_ID = Events.User_ID
        JOIN Rooms ON Rooms.Room_ID = Events.Room_ID
    WHERE
        Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
)
/* We use window functions, and partition by (Event_Start_Time, Event_End_Time), order by User_Last_Name. We will output only Time and Course on First Row */
SELECT
    case when (row_number()
          over (PARTITION BY Event_Start_Time, Event_End_Time
               ORDER BY User_Last_Name) ) = 1 then Time   end AS Time,
    case when (row_number()
          over (PARTITION BY Event_Start_Time, Event_End_Time
                ORDER BY User_Last_Name) ) = 1 then Course end AS Course,
    Instructor, Room
FROM
    e
ORDER BY
    Event_Start_Time, Event_End_Time, User_Last_Name;

You will get:

 Time          | Course   | Instructor   | Room
 :------------ | :------- | :----------- | :---
 08:00 - 11:30 | NURS3405 | F. Lastname1 | LabA
 null          | null     | F. Lastname2 | LabB
 null          | null     | F. Lastname3 | LabC
 null          | null     | F. Lastname4 | LabD

You can check everything in this DBFiddle.

NOTES:

You can remove a few SELECT from your original query. They are not needed.
Your way of "shortening" times seems not to work on all cases. I have used a different approach taken from Stack Overflow's "SQL Server 2005 DateTime (return only hh:mm)".
I am not sure at all my assumptions about your tables are correct (you are recommended to post the structure of your tables (or the relevant part of it) when posting questions). Advise, otherwise.
We use the fact that CASE WHEN cond THEN a END will return NULL when cond is not met. If you need an empty string instead of NULL, you must change the expressions to CASE WHEN cond THEN a ELSE '' END.
Seriously consider the comment from @sp_BlitzErik: this should most probably be done at the presentation layer, not the database layer. It just happens, sometimes, that you can control your database layer better than the presentation one. If that's the case, this is (one possible) alternative. This solution is, in fact, a grouping set in disguise, with a little bit more of flexibility, and without the OLAP cubes background.

References:

Window Function Examples for SQL Server
OVER clause
row_number() function

Data model used for the example:

Tables, PK and FK

 CREATE TABLE Rooms
 (
    Room_ID integer PRIMARY KEY,
    Room_Name varchar(50)
 ) ;

 CREATE TABLE Courses
 (
    Course_ID integer PRIMARY KEY,
    Course_Code varchar(50),
    Course_Title varchar(50)
 ) ;

 CREATE TABLE Users
 (
    User_ID integer PRIMARY KEY,
    User_First_Name varchar(50),
    User_Last_Name varchar(50)
 ) ;

 CREATE TABLE Events
 (
    Event_ID integer PRIMARY KEY,
    Course_ID integer REFERENCES Courses(Course_ID),
    Room_ID integer REFERENCES Rooms(Room_ID),
    -- User_ID is most probably the "organizer"/"lecturer", not the "attendant"
    User_ID integer REFERENCES Users(User_ID),
    Event_Date date,
    Event_Start_Time time,
    Event_End_Time time
 ) ;

Data

 INSERT INTO Users
     (User_ID, User_First_Name, User_Last_name)
 VALUES
     (  1, 'FirstName1', 'Lastname1'),
     (  2, 'FirstName1', 'Lastname2'),
     (  3, 'FirstName1', 'Lastname3'),
     (  4, 'FirstName1', 'Lastname4') ;

 INSERT INTO Courses
     (Course_ID, Course_Code, Course_Title)
 VALUES
     (100, 'NURS3405', 'Title NURS3405') ;

 INSERT INTO Rooms
     (Room_ID, Room_Name)
 VALUES
     (201, 'LabA'),
     (202, 'LabB'),
     (203, 'LabC'),
     (204, 'LabD') ;

 INSERT INTO Events
     (Event_ID, Course_ID, Room_ID, User_ID, Event_Date, Event_Start_Time, Event_End_Time)
 VALUES
      (300, 100, 201, 1, GetDate(), '08:00', '11:30'),
      (301, 100, 202, 2, GetDate(), '08:00', '11:30'),
      (302, 100, 203, 3, GetDate(), '08:00', '11:30'),
      (303, 100, 204, 4, GetDate(), '08:00', '11:30') ;


SELECT A.*
FROM    jos_bet_1x3_best A
   INNER JOIN
      (SELECT bet_id,
              q1,
              AVG(`q1`) AS betavg,
              COUNT(bet_id) AS nr,
              MAX(q1) AS qmax,
              (MAX(q1) - AVG(`q1`)) AS diff
         FROM jos_bet_1x3_best
       GROUP BY `bet_id`
       HAVING (q1 >= betavg AND nr > 2 AND diff < .5 AND q1 < 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;

Please try this.


Temporary role/privileges Is it possible in PostgreSQL to temporary grant roles/privileges to a user? I'm thinking about this as a solution for the situation when there is a need to troubleshoot an issue and an user may need some specific role/privileges (even superuser) only for a short period of time.

GRANT does not have an expiration date, but roles have. So:
CREATE ROLE foo VALID UNTIL '2019-05-01 00:00+02';

GRANT my_privilege_bundle TO foo;

And my_privilege_bundle would be a group role bundling all required privileges.
The manual:

VALID UNTIL 'timestamp'
The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.

Note that this does not end existing sessions. The role just can't make new connections with password after the expiration date. The manual once more

The VALID UNTIL clause defines an expiration time for a password only, not for the role per se. In particular, the expiration time is not enforced when logging in using a non-password-based authentication method.


Basic transactional DDL script in PostgreSQL PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the whole thing back; otherwise commit. I'm having a hard time getting the syntax right, and I suspect my problems might stem from conflating SQL DDL with PL/pgSQL and/or not grokking the transaction semantics.

Long story short, what's the PL/pgSQL boilerplate for a script to do something like this?

My attempts have gone something like schema.sql here:

BEGIN;

CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

EXCEPTION WHEN OTHERS THEN ROLLBACK; -- this, I gather, is PL/pgSQL

COMMIT;

\i schema.sql at the psql prompt produces a syntax error at or near EXCEPTION. OK, so EXCEPTION must be PL/pgSQL, and all this needs to go in a PL/pgSQL declaration. Let's try again:

Per the grammar at http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html, this looks like:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

I don't need to name this, it's a one-off provisioning script, not declaring a function I'm going to use again. So skip the declarations and expand statements to what I had before:

BEGIN

    BEGIN;

    CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

    EXCEPTION WHEN OTHERS THEN ROLLBACK;

    COMMIT;

END;

This blows up in even worse fashion, with syntax errors on BEGIN, EXCEPTION, and then it keeps running the rest of the script anyways, complaining about not being in a transaction.

What's the misunderstanding I have here?

if any errors, roll the whole thing back;

It's simpler than you seem to think. Any exception in a transaction (that is not trapped somehow) triggers a ROLLBACK for the whole transaction automatically. You don't have to do anything extra.

BEGIN;
CREATE TABLE IF NOT EXISTS blah ( ... );
-- much more DDL
COMMIT;

You cannot start, commit or roll back transactions inside plpgsql at all, since a plpgsql block is always run in the context of an outer transaction automatically.

Don't confuse SQL commands for transaction management with elements of a plpgsql code block. BEGIN is used as keyword in both, that's the only thing in common. That's never ambiguous, because the SQL command is not available inside plpgsql code and there are no plpgsql commands outside plpgsql code blocks.

An EXCEPTION clause in a plpgsql block is only used to trap errors and do something before or instead of the ROLLBACK. Of course, it only makes sense to do something that ROLLBACK isn't going to undo - like raising a message which is never rolled back.
You don't need any of this for your demonstrated code.


How to rewrite this correlated query as a JOIN? I'm just getting into SQL and am encountering some difficulty wrapping my head around correlated queries. I'm still trying to master it but am wondering what would be the right syntax for rewriting this statement as a JOIN. Essentially I have an employee list by department and want to know the highest salary in each department. Here's the query as a correlated query:

SELECT dept, name, salary
FROM employees AS a
WHERE salary >= ALL
(
  SELECT salary
  FROM employees b
  WHERE a.dept = b.dept AND salary > 0
)

One variant:

SELECT
  a.dept,
  a.name,
  b.salary
FROM employees AS a
JOIN (
  SELECT
    MAX(c.salary) salary,
    c.dept
  FROM employees AS c
GROUP BY c.dept
) b
  ON a.dept = b.dept

If you are using TSQL you can replace JOIN with CROSS APPLY on move ON clause inside and replace it with WHERE


Installing Oracle 19c server on Windows 7 I was having a problem after migrating my Database from 11.2.0.4 to 19c using dbua.
The migration process using dbua is smooth and successful except for migrating the TNS listener. I finally migrate the database without the listener and aims to create another listener after the migration.
I followed the instructions in "Net Configuration Assistant" to create a listener with the default port (1521). The listener is created successfully.
When I tried to start the newly created listener service, the following error is shown in the log,
Error listening on: (ADDRESS=(PROTOCOL=ipc))
TNS-01191: Failed to initialize the local OS authentication subsystem
 TNS-12557: TNS:protocol adapter not loadable
  TNS-12560: TNS:protocol adapter error
   TNS-00527: Protocol Adapter not loadable

I did some research about the error TNS-01191 and I had already updated the environment variables "PATH" with the new Oracle home bin directory.
May I know if anyone had installed Oracle 19c on Windows 7 and configured the TNS listener successfully?
FYI, the tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

The listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENERFORSD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = <ORACLE_HOME_MASKING>)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:<ORACLE_HOME_MASKING>\bin\oraclr19.dll")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = <ORACLE_HOME_MASKING>)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:<ORACLE_HOME_MASKING>\bin\oraclr19.dll")
    )
  )

<LISTENER_NAME_MASKING> =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME_MASKING>)(PORT = 1521))
    )
  )

Windows 7 is not a supported operating system for Oracle 19c.
The documentation states that it is only supported for these versions of Windows:

Windows 8.1 x64 - Pro and Enterprise editions
Windows 10 x64 - Pro, Enterprise, and Education editions
Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and
Foundation editions
Windows Server 2016 x64 - Standard, Datacenter, and Essentials
editions
Windows Server 2019 x64 - Standard, Datacenter, and Essentials
editions

Even if you are able to get the listener functioning, you will likely recieve other errors with normal database usage that will just cause more headaches in the future which is why it is very strongly NOT RECOMMENDED to install Oracle on an unsupported operating system.


Rollback taking forever Issue : I was moving a non clustered index on a 3TB table from one
file-group to another (userfilegroup to index) and it took 11 hours and never completed and i had to kill the process as it was blocking other processes.

Rollback is taking forever. It has been 10 hours since i killed the process and no clue what is happening. Locks are still held and are impacting other processes.

I see the percent_complete as 0% and kill with statusonly also shows 0% after so many hours.

Can you please suggest me if there is a way that i can check if rollback is still doing anything or just got stuck . I am afraid to restart the server as it has to go through the rollback process anyways.

Only positive thing that see is under sp_who2  diskIO moving for this SPID.

****Any help is much appreciated....****

Bad news: rolling work forward in SQL Server is multi-threaded, but rollbacks are single-threaded. If you had lots of cores doing work for 11 hours, it's entirely possible that it could take dozens of hours (or several days) to do the rollback.

Try using sp_BlitzFirst @ExpertMode = 1 (disclaimer: I'm one of the authors of that open source tool), and look at the file stats section. It'll show you which data & log files have had physical reads & writes in that time span. That'll show you if your rollback is continuing to move along.

You mentioned "impacting other processes" - keep in mind that your rollback speed can be reduced by other processes running on the server. You may also want to keep an eye on your drive free space and transaction log sizes. Your transaction was a logged operation, and SQL Server will need that transaction log to stay online even after you've taken log backups. It's very conceivable that you'll run out of drive space during the rollback.

Given how big that rollback was, and depending on your RPO/RTO goals, I'd consider failing over to your disaster recovery servers or restoring from backups. I hate to lecture you now, but this is a good time to learn the lesson that you should try everything in a development environment first to measure how long it'll take. You would have been better prepared to identify that you can't move a multi-terabyte object from one filegroup to another without a lot of planning.


SQL Timeout to Principle Server Error I am seeing a timeout to the Pricincipel SQL server (2008 R2)

The exact error I am seeing is this:

  Timeout expired. The timeout period elapsed prior to completion of the
  operation or the server is not responding. This failure occured while
  attempting to connect to the Principle server.

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The
  timeout period elapsed prior to completion of the operation or the
  server is not responding. This failure occured while attempting to
  connect to the Principle server. --->
  System.ComponentModel.Win32Exception (0x80004005): The wait operation
  timed out at XXXX

What I am trying to understand is is this a standard timeout, ie. the DB was unable to return the required results within the allowed time? Or does the "Principle Server" wording signify another issue?

I have not been able to find much about this specific error and need to know if its a performance issue that tuning the indexes and procs will help resolve or is there another issue I need to look at instead.

I am currently investigating an occurrence of the same timeout exception with the same key wording:

  This failure occured while attempting to connect to the Principle server.

The environment is SQL Server 2012 with mirroring, so I suspect that the problem was not directly related to a long-running query, but rather to a temporary network/sync issue, either between the application and the SQL servers or between the SQL servers themselves. Stack trace:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occured while attempting to connect to the Principle server. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   ...

Update: evidently, this wording is included in the exception message whenever a command times out against a mirrored/failover partner environment. It does indeed reflect a command that exceeded its timeout, and has nothing to do with a failure to attempting to connect. You can verify this by running a simple WAITFOR DELAY '00:00:31' command, which exceeds the default 30-second SqlCommand timeout, and triggers the same exception message.

I have opened a Connect bug to ask Microsoft to correct the misleading wording in the message.


Programmatically Check Dynamic File Name For Invalid Characters I have a SQL Script that runs every night and will generate files showing each Sales Reps production for the previous day.  This works 99% of the time, the 1% it does not work is when one of the variables has invalid characters, which of course would generate an invalid file name.  Is there a t-sql way of checking if @filename is a valid string and if it is not replace @filename with a generic naming convention so the procedure does not error?

Sample syntax below:

`Declare @RepName NVARCHAR(500), @filename nvarchar(4000), @Detailedinfo varchar(50)

DECLARE cursor1 CURSOR FOR

  Select Distint repname, saleinfo from dailysalesjournal;

  OPEN cursor1

  FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

  WHILE @@FETCH_STATUS = 0
  BEGIN

    Set @filename = 'C:\Test\GeneratedFromSQLProc\'+@RepName+'\SaleInfo'+@Detailedinfo+'.csv'

    Print @filename

FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

END

CLOSE cursor1

DEALLOCATE cursor1  `

Just run it through a series of REPLACE statements to get rid of any invalid characters:

SET @filename = REPLACE(@filename, '\','');
SET @filename = REPLACE(@filename, '/','');
SET @filename = REPLACE(@filename, ':','');
SET @filename = REPLACE(@filename, '"','');
SET @filename = REPLACE(@filename, '*','');
SET @filename = REPLACE(@filename, '?','');
SET @filename = REPLACE(@filename, '<','');
SET @filename = REPLACE(@filename, '>','');
SET @filename = REPLACE(@filename, '|','');

If it doesn't contain the character, it'll leave it unchanged. If it finds it, it'll remove it.


High CPU Utilization for Mongodb ReplicaSet Secondary Nodes I am using MongoDB server version: 3.6.4 (WiredTiger storage engine) with 3 node replica set on AWS r4 large machines.

Sharing the CPU Utilization graph of all the nodes

Primary (with .33%)

Secondary 1 (with ~99%)

Secondary 2 (with ~99%)

I tried to run

  db.currentOp({"secs_running": {$gte: 3}})

I can see only

{
    "inprog" : [
        {
            "host" : "XXXXXXXXXXXXX",
            "desc" : "WT RecordStoreThread: local.oplog.rs",
            "active" : true,
            "currentOpTime" : "2018-05-23T12:41:42.272+0000",
            "opid" : 18,
            "secs_running" : NumberLong(16636),
            "microsecs_running" : NumberLong("16636047730"),
            "op" : "none",
            "ns" : "local.oplog.rs",
            "command" : {

            },
            "numYields" : 0,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : NumberLong(1),
                        "w" : NumberLong(1)
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "w" : NumberLong(1)
                    }
                },
                "oplog" : {
                    "acquireCount" : {
                        "w" : NumberLong(1)
                    }
                }
            }
        }
    ],
    "ok" : 1,
    "operationTime" : Timestamp(1527079298, 1),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1527079298, 1),
        "signature" : {
            "hash" : BinData(0,"XXXXXXXXXXXXX"),
            "keyId" : NumberLong(0)
        }
    }
}

I tried to restart the secondary machines, but after sometimes CPU utilisation reaches the peak.

I have set readPreference as secondaryPreferred

Thanks in advance

As per MongoDB blog documentation from Asya Kamsky here CPU load is almost
never the bottleneck/limiting resource for MongoDB (or databases in
general).

Unless you are running a large number of MapReduce jobs and/or aggregation framework queries, high CPU utilization tends to indicate that you have poorly tuned queries possibly with in-memory sorts (as opposed to indexes supporting sorting by reading documents in correct order).

Note: write heavy load means that rather than worrying about using available disk drive space you should be concerned about available disk IO bandwidth.

To View Metrics related to Alerts and Monitoring here. The serverStatus command returns a document that provides an overview of the database’s state. Monitoring applications can run this command at a regular interval to collect statistics about the instance.

db.runCommand( { serverStatus: 1 } )

For example, the following operation suppresses the repl, metrics and locks information in the output.

db.runCommand( { serverStatus: 1, repl: 0, metrics: 0, locks: 0 } )

Note: The output fields vary depending on the version of MongoDB, underlying operating system platform, the storage engine, and the kind of node, including mongos, mongod or replica set member.

For the serverStatus output specific to the version of your MongoDB, refer to the appropriate version of the MongoDB Manual.

And the dbStats command returns storage statistics for a given database. The command takes the following syntax:

db.runCommand({ dbStats: 1, scale: 1 })

where scale is optional and defaults to 1.

For example

> db.runCommand({ dbStats: 1, scale: 1 })
{
        "db" : "test",
        "collections" : 1,
        "views" : 0,
        "objects" : 4,
        "avgObjSize" : 86.75,
        "dataSize" : 347,
        "storageSize" : 36864,
        "numExtents" : 0,
        "indexes" : 1,
        "indexSize" : 36864,
        "fsUsedSize" : 275137204224,
        "fsTotalSize" : 499632828416,
        "ok" : 1

The scale argument allows you to specify how to scale byte values. For example, a scale value of 1024 will display the results in kilobytes rather than in bytes:

db.runCommand({ dbStats: 1, scale: 1024 })

For your further ref here and here


MongoDB not using wildcard nested array index I have the following collection:
{
   _id: 12345,
   quizzes: [
     {
        _id: 111111,
        done: true
     }
   ]
},
{
   _id: 78910,
   quizzes: [
     {
        _id: 22222,
        done: false
     }
   ]
}

I want to select the documents where a certain quiz from the quizzes was done and want to make sure that it uses the appropriate index. So I use the following query:
Answer.find({ 'quizzes.0.done': true }).explain('queryPlanner');

Which returns:
queryPlanner: {
  plannerVersion: 1,
  namespace: 'iquiz.answers',
  indexFilterSet: false,
  parsedQuery: { 'quizzes.0.done': [Object] },
  winningPlan: { stage: 'COLLSCAN', filter: [Object], direction: 'forward' },
  rejectedPlans: []
}

The query is not using any index as seen from the output. I have tried the following indexes and none get used:
{ quizzes.done: 1 }
{ quizzes.[$**].done: 1 }
{ quizzes: 1 }

The only 1 that actually gets used:
{ quizzes.0.done: 1 }

However this is not really practical as I may target any quiz from the quizzes array not just the first one. Is there a certain syntax for the index in my case or this is a current limitation of mongodb? Thanks!

When MongoDB indexes an array, or a field in an array of subdocuments, the index is essentially a list of value -> recordId pairs.
The value part is the actual field value, and does not include any information about the index of the value within the array.
The query executor's index scan matches the queried value with the indexed values and returns a list of recordIds that then need to be fetched.
If you have additional requirements, such as an elemMatch clause or require the value to be in a specific position in the array, the documents will have to be examined after the index scan.
This means that if you are using an index on {"quizzes.done":1} to match "quizzes.0.done": true, the executory would need to retrieve all documents that contain at least 1 quiz that is done, and examine each to determine if it is the first index.  In that case, a collection scan is quite likely more efficient.
You noted that the index on {"quizzes.0.done":1} works for that query, but that is because the query executor can guarantee the location of the match within the array with that index, and be certain that no other array element will match.
I'm not certain how the { quizzes.[$**].done: 1 } index might be handled, but I suspect it won't look much different than { quizzes.done: 1 }


Analysis Services Component set to 'false'? Our database server (Microsoft SQL Server 2008 R2) undergoes audit and they are requesting to set the value
of Security\BuiltinAdminAreServerAdmins to false from default value of true

Now, since it is a production server, my worry is the effect of taking this action, can anyone tell me the effect when I set it to false?

These are the steps to go on said item.

Open the SQL server management studio
Connect to the  "Microsoft Analysis Server"
Right click on the "Microsoft Analysis Server" then select properties
Tick the box  for  "Show Advanced (All) Properties"
Scroll down till you find  [Security \ BuiltinAdminsAreServerAdmins]  and check the current value.
If the value set to true then stand on the true and change it to false.

Thank you in advance. Please use the following image as a reference.

Ref:

Grant Server Administrator Permissions (Analysis Services)

You need see what accounts are member of local administrator group and determine if those accounts need the privilege mentioned in the document. If these privileges are required and you set to false you will have issues.

  By default, members of the local Administrators group are also granted
  administrative rights in Analysis Server. Although the local group is
  not explicitly granted membership in the Analysis Services server
  administrator role, local administrators can create databases, add
  users and permissions, and perform any other task allowed to system
  administrators. This behavior is configurable. It is determined by the
  BuiltinAdminsAreServerAdmins server property, which is set to true by
  default. You can change this property in SQL Server Management Studio.

To look at the members of local admin group see this picture:

If not you can set to false. If some of those accounts need certain privilege you can create custom roles and add those accounts as member of those roles. See this article for details:

Roles and Permissions (Analysis Services)


Materialized views Performance Postgres’ docs note:

  While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current;

https://www.postgresql.org/docs/9.6/rules-materializedviews.html

Why is it “often much faster?”

The data of a MV is stored in a regular table, there is no magic to that. But access is typically (much) faster for multiple possible reasons:

multiple tables joined already
smaller row size with only relevant columns for common queries
pre-computed values
pre-selected rows, possibly in expensive ways
possibly much less bloat (fewer dead tuples)
multiple of the above items result in potentially much smaller table size as compared to underlying tables
rows physically sorted favorably (clustered), so that queries only have to read few data pages
size of indexes can be much smaller accordingly
some kinds of indexes only become possibly this way, like a multicolumn index on columns from multiple underlying tables

In short: most expensive work of sophisticated queries on underlying tables is already done, which allows potentially much faster access.


Managing code and deployments to Amazon Redshift I am finding it difficult to manage and deploy schema changes to my Amazon Redshift database.  Ideally, I would like is to have the code for my database under source control and use a schema comparison tool (like Redgate's Schema Compare or Microsoft's Sql Server Data Tools) to generate a delta script between the repository and the target db that can be used for deployment.  However, I can't find anything out there that does this.

Would anyone out there like to share how they are managing and maintaining their redshift codebase?

In general there is very little tool support at present for Redshift. We do all of our automation and deployment via psql shell scripts and we manage the code base using git version control.

As far as schema changes we use a "migrations" approach (inspired by Rails) where we create ALTER TABLE …ADD COLUMN and ALTER TABLE …DROP COLUMN scripts to roll the database forward/back from one deployment to the next.

We also use 3 separate database on the same cluster for Dev/Test/Prod. We then specify the target database in the psql script using the -d option.

Hope that helps.


Stored Procedure result is not showing in Datagrid pane in Toad for Oracle I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong?

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type)
AS
p_recordset Types.cursor_type;
BEGIN
    OPEN p_recordset FOR

    SELECT FILLED_MCB.BARCODE,TILTED_MCB.TILTINGTIME,MAKERS.FEEDERNUM,MAKERS.MAKERNUM
    FROM FILLED_MCB INNER JOIN TILTED_MCB ON FILLED_MCB.FM_ID = TILTED_MCB.FM_ID
    INNER JOIN MAKERS ON MAKERS.M_ID = TILTED_MCB.M_ID
    INNER JOIN CTS_OPERATIONS ON CTS_OPERATIONS.CTS_ID = FILLED_MCB.CTS_ID

    --WHERE CTS_OPERATIONS.OPERATIONNUM=OpNo AND CTS_OPERATIONS.BLENDNAME=BName;
    WHERE CTS_OPERATIONS.OPERATIONNUM=3004 AND CTS_OPERATIONS.BLENDNAME='PG';
END;

---- Execute Procedure ---------------

exec FEEDERS_TO_MAKERS_MCB(3004,'PG');

I think you want an output parameter of the type sys_refcursor. Currently your cursor is assigned to a local variable, which gets destroyed when the procedure terminates.

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(
   OpNo CTS_OPERATIONS.OPERATIONNUM%Type
   ,BName CTS_OPERATIONS.BLENDNAME%Type

   ,p_recordset OUT SYS_REFCURSOR

)
AS
BEGIN
    OPEN p_recordset FOR

    SELECT ...
END;


How can I mimic functionality of SQL Server `TYPE` in Oracle? I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition.

For instance, I need "money" type in Oracle which is technically NUMBER(18,4) in my system, and each table will have "money" column instead of underlying NUMBER for consistency purpose. There are some other "types" I'd like to have aliases rather than using them directly.

Unfortunately, I didn't find acceptable solution yet except giving up and using standard types with constraints(or size specification) for each column. The only approach that seems to work is to create OBJECT type, but it will add unnecessary complexity to the system which I'm trying to avoid.

A very nice Oracle feature, SUBTYPE, doesn't work in this case . As far as I understand, SUBTYPE (and subtypes from STANDARD) creates PL/SQL datatype which cannot be used in SQL. I understand there are various reasons for that.

I wonder what is the best practice for such a problem.

Thank you.

Update
It seems so far that I've listed all possible approaches, and it I'd rather close the question. Not sure if it may be useful for anyone; in case it is, I don't remove the question for the time being.

This is a very good question and not just because it shows effort on your part to answer the question.  Thank you for asking it and please do not remove it.

The answer as you have already discovered is -- You can't do that.

You have correctly identified several similar concepts including...

Using standard types with constraints.
Object Types that behave differently than standard types.
Subtypes which are for PL/SQL not SQL.

Why might SQL Subtypes be useful?  You mentioned consistency.  Here are some ideas:

Errors are prevented when inserting a field from one table into another.
The definition of the field does not have to be rememberd or looked up.
There is less typing.
Changing the definition can be done in one place.
The datatype name provides documentation.

Here are some thoughts addressing these:

Table definitions are not changed frequently.  Yes, they do change, but the amount of change is considerably smaller than that of PL/SQL (where you can use subtypes and %types) or application side code.
Databases are usually normalized to eliminate duplication of data and limit the requirement for multiple tables to have fields with identical definition.  Of course something as generic as a Money type would get used in many places, but some types would get moved to a lookup table and only the key repeated in multiple places.
Often there are benefits to using distinct sizes for different tables.  A discount store might have a products table with a field defined as Number(4,2) since they never sell anything over ten dollars.  The definition is itself a constraint on the data a constraint that improves acuracy, documents the system, is fast, cannot be circumvented, and is all around better than any application side constraint.  If the business decides to remove this constraint by increasing the field size, it does not necessarily mean it needs to change the size of other fields such as Salary, SalesAmount, etc.
Denormalized data is often aggregated and may require different field sizes. A Salary column need not be Number(18,4) and even a sum of all salary columns company wide need not be Number(18,4) (just short of 100 trillion). Even if you need to increase the aggregated field containing Salary information due to growth of the company, you still wouldn't necessarily need to increase the Salary field at the same time, but if they are based on the same SQL subtype then you would have no choice.  Right sized fields have the benefit of using less storage and memory as well as having faster index and table lookups.
Remembering or looking up a custom type is similar in difficulty to remembering or looking up a previously used field definition.
Oracle converts the SQL Server Money type to Number(19,4) when using the 10.2 Transparent Gateway, so even standardizing on Number(18,4) would be wrong when working with the gateway.
In Oracle, frequently table columns are created that include definition that could be in the datatype name for other systems.  For example, Wholesale with a datatype of Money might be WholesaleCost in Oracle so that it is clear the field holds relates to money.  Sometimes this disabiguation would be necessary anyway such as if there were UnitCount, UnitVolume, and UnitCosts fields.  Other times the name itself gives the type away (such as Salary).   Finally, comments can be put on the table columns to provide further clarification if necessary.

I say all this not to say that the concept of an SQL subtype is bad, just that it is not as useful as it may seem.


Find the median of a calculated field in SSRS 2012 I have a start date and an end date, and am calculating the weekdays in between with the following:

I created a calcuated field called CountWeekDays, and it equals: Code.getBusinessDaysCount(Fields!date_created.Value,Fields!date_closed.Value)

I can get an Average of that like this: =Avg(Fields!CountWeekDays.Value)

However, I cannot get the Median the same way. How can I get the median number of something that is calculated?

The code I am using to get the weekday count is as follows:

Function getBusinessDaysCount(ByVal tFrom As Date, ByVal tTo As Date) As Integer
Dim tCount As Integer
Dim tProcessDate As Date = tFrom
For x as Integer= 1 To DateDiff(DateInterval.Day, tFrom, tTo) + 1
  If Not (tProcessDate.DayOfWeek = DayOfWeek.Saturday Or tProcessDate.DayOfWeek = DayOfWeek.Sunday) Then
    tCount = tCount + 1
  End If
  tProcessDate = DateAdd(DateInterval.Day, 1, tProcessDate)
Next
Return tCount

End Function

I found code with the answer:

'MEDIAN code
'code from http://stackoverflow.com/questions/1943437/mean-median-mode-in-sql-server-reporting-services

Dim values As New System.Collections.Generic.List(Of Integer)
Dim valueCounts As New System.Collections.Generic.Dictionary(Of Integer, Integer)

Function AddValue(newValue As Integer) As Integer
values.Add(newValue)
AddValue = newValue
If Not valueCounts.ContainsKey(newValue) Then
    valueCounts.item(newValue) = 1
Else
    valueCounts.item(newValue) += 1
End If
End Function

Function GetMedian() As Double
Dim count As Integer = values.Count
If count = 0 Then
    Return 0
Else
    values.Sort()
    If count Mod 2 = 1 Then
        Return values(CInt((count / 2) - 0.5))
    Else
        Dim index1 As Integer = count \ 2
        Dim index2 As Integer = index1 - 1

        Dim value1, value2 As Integer
        value1 = values(index1)
        value2 = values(index2)

        Return (value1 + value2) / 2
    End If
End If
End Function


Single foreign key for referencing one of multiple tables Currently I have a schema that looks like this:
create table person(
    id uuid primary key default gen_random_uuid() not null,
);

create table car(
    id uuid primary key default gen_random_uuid() not null,
);

create table extra(
    id uuid primary key default gen_random_uuid() not null,
);

create table car_extra_m2m(
    car_id uuid not null references car(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (car_id, extra_id)
);

create table person_extra_m2m(
    person_id uuid not null references person(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_id, extra_id)
);

Is it possible to express the two many to many tables in a single many to many table? Since we're using uuid the ids should never collide, so it might be possible to know the type just from the uuid?
Like this pseudocode: (Edit: not valid syntax in postgresql, does there exist a valid syntax for this?)
create table extra_m2m(
    person_or_car_id uuid not null references (person(id) or car(id)) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id)
);

Yea there's no reason not to if it fits your use case better, there's nothing wrong with that design. My only suggestion would to add a extra_type field to explicitly identify whether the record is of type person or car. You'll likely find a field like that will be helpful later on. But yes that's a valid schema design.

After further clarification in the comments, you'd have to make a couple changes to natively accomplish this to still be able to support foreign key constraints in the database (otherwise what you'd be looking for is a polymorphic foreign key which isn't natively supported).
The first change is you'd have to merge your person and car table into a single table, e.g. person_or_car with the person_or_car_id primary key field. The second change is you'd need to add an extra_type_id field to the person_or_car table to distinguish between the two. And finally you'll need to add the extra_type_id field to the extra_m2m table, and create the foreign key constraint on it.
Example query with the extra_type_id field, as discussed in the comments, for step 3:
create table extra_m2m
(
    person_or_car_id uuid not null,
    extra_type_id int,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id),
    foreign key (person_or_car_id, extra_type_id) references person_or_car (person_or_car_id, extra_type_id) on delete cascade
);


OLE DB Driver sql 2008r2 to Oracle failing I have a SQL Server R2 Installation running on Windows Server 2008 R2.
I have installed Oracle Instant Client, registered the OraOLEDB.Oracle driver(orasql12.dll), and set the PATH in environment variables to the BIN folder of the Oracle installation.

I can see the OraOLEDB.Oracle provider in SQL Management Studio, however, when I try to connect to the remote datababase using OpenRowSet (see code below), I get the following error:

The OLE DB provider "[OraOLEDB.Oracle]" has not been registered.

I know that I have successfully registered this driver with regsvr32, and I still cant connect? What am I doing wrong?

The code I am using is as follows:

SELECT *
FROM   OPENROWSET('[OraOLEDB.Oracle]','[TNS]';'[UserName]';'[Password]','SELECT DataField, TYPE'); etc...

Install the Oracle Instant client - both the 32 and 64 bit versions find orasql12.dll and register it with regsvr32 orasql12.dll and c:\windows\syswow64\regsvr32.exe orasql12.dll for the 32 bit version. and add the oracle bin directory the the path environment variable

Make sure that you can see the oracle driver in ODBC both 32bit and 64bit and under data providers in the management studio. Check allow "Allow Inprocess" in the driver properties or run EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1


serial numbers for products Let's say I'm selling 3 products. I have a products table with id and name (so that table would have 3 entries). I want each product to have a serial number, each independent from one another, but I don't want to create a table for each product as the product count might grow.

How can I achieve this so that the serial number auto increments for each product? I figured I would need some pivot table but that requires me to make 3 pivot tables for each product.

From here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.


MS Access ER diagram I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?

Visio Professional(prior to 2013) has an option to reverse engineer a database schema.  I have used this in the past for Access as well as other databases.  However, it does mean you have to purchase the professional version.

You could also migrate the database schema into SQL Server and leverage the database diagrams found there.  You should be able to use SQL Server Express for this.


Azure elastic pool: Can I create as many databases as I want? I am sitting here trying to figure out what the elastic pool in Azure gives me. I have a customer that wants a guess on pricing if they move to Azure. They have 3 databases, on a server. The server has 96 GBs of RAM and a 3.4 Ghz processor. That is all I know. The three databases are sharing these specs, and each database has it own area of expertise:

one database handles all the CMS data
the second database handles all the commerce data
the third database is a database that stores user info, etc - all data that doesn't fit the two others

I think the usage pattern is pretty diverse, so sometimes the commerce DB is using a lot of RAM and CPU, other times it is the CMS DB etc.

I don't think they fit well into a single database, because they are going to need three big databases then, and almost all of them are going to be under-utilized all the time. So I thought about elastic pools.

The description of elastic pools fits the above very well: all databases share eDTUs. Perfect! But one thing the docs don't describe is: do I pay for the elastic pool AND per database? Or do I just create databases on the fly, and all of them share, that is: the databases are free, but you pay for the eDTU?

I have read this https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool - but it doesn't describe my situation.

Hope someone can give me some more info.

EDIT
They are going to update the docs: https://github.com/MicrosoftDocs/azure-docs/issues/49201#issuecomment-593579476. I created an issue for this asking for clarification.

The server has 96 GBs of RAM and a 3.4 Ghz processor

The elastic pool is mainly for smaller workloads, and multi-tenant applications.  For lift-and-shift of existing SQL Servers, you're normally better-off with Azure SQL Database Managed Instance.

Only Managed Instance (or SQL Server on an Azure VM) supports cross-database queries, SQL Server Linked Servers, Transactional Replication publication and distribution, SQL Agent, and other features an on-prem SQL Server may be using.

Managed Instance starts at 4 vCores, where Elastic Pool is available in smaller sizes, down to less than 1 vCore.


MySQL Error Reading Communication Packets In MySQL error logs, I see these quite few a warnings like these:

120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading communication packets)

Haven't noticed any loss of data per se, so I am wondering what this warning means, or what causes it, and if how one might address the issue causing these. This is on RHEL 6.1 and MySQL Enterprise 5.5.

One of the silent killers of MySQL Connections is the MySQL Packet.

First, let's figure out what a MySQL Packet is.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a Developer/DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large. Definitely, a packet too small will cause problems for open connections in this respect.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=256M

to cover future restarts of mysqld. To install the value now on the server, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Give it a Try !!!


check numeric column for empty cells How can I check for a numeric column for empty values

I want something like this:

select * from table where column_numeric = ''

This does not works obviously, but what can be the equivalent of this for numeric column?

A numeric column without a value is null:

select *
from the_table
where column_numeric is null;

There is no equivalent concept of an "empty string" for numbers.