CONCURRENT SELECT's SLOW .... LOCK IN SHARE MODE My Table has 25 Million rows total and 6 million rows satisfying below where clause.

Select 1:

select max(stamp) from Table where type = "string" LOCK IN SHARE MODE ;  6000+ seconds

Select 2:

select * from Table where type = "string" LOCK IN SHARE MODE ;  3000+ seconds

Select 3:

select * from Table where type = "string" LOCK IN SHARE MODE ;  2000+ seconds

If I try the SELECTS 2 and 3 without LOCK IN SHARE MODE WITH LIMIT, I am getting the data almost immediately.

All three queries have locked d table in shared mode. I don't understand why these are taking too much time when we use LOCK IN SHARE MODE.

Engine is Innodb,

Primary key is a composite key on multiple fields all are varchars

Create Table: CREATE TABLE `T1` (
`TYPE` varchar(25) NOT NULL,
`FIELD2` varchar(50) NOT NULL,
`FIELD3` varchar(130) NOT NULL,
`FIELD4` varchar(100) NOT NULL,
`FIELD5` varchar(30) DEFAULT NULL,
`FIELD6` varchar(20) DEFAULT NULL,
`FIELD7` varchar(110) NOT NULL,
.....
.....
.....
`STAMP` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE
CURRENT_TIMESTAMP(3),

PRIMARY KEY (`TYPE`,`FEILD2`,`FEILD3`,`FEILD4`,`FEILD5`,`FEILD6`,`FEILD7`),
KEY `COM_KEY` (`FIELD9`,`FIELD9`,`STAMP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INDEX(type, stamp) should speed up "Select 1" significantly, even without LOCK IN SHARE MODE.

(Please provide SHOW CREATE TABLE for further discussion.)

Edit

Even though you have type and stamp in indexes, having these two fields in this order will be optimal for "Select 1":  INDEX(type, stamp).  This is because the optimizer can drill into the BTree to the end of the "type='string'" values and find the MAX.  Instantly.  Only one rows is fetched.  And it is only fetched in the index.

With PRIMARY KEY(type, other stuff) it needs to scan 6M rows to find the answer.  COM_KEY is of no help (for this SELECT).

After this query is sped up, the other queries may be less of a problem.


SQL Cluster, AlwaysOn AG, or Something Else? I really needs some help with Setting up SQL servers.  We are re-configuring our network and have no clue how to resolve our SQL issue.  My Network Admin. wants an "Active/Active" with shared storage (SAN) solution.  The reason for this is load balancing, redundancy, and auto-fail over.  I do not believe this can be done or should be done.  Please help me figure this out.  I have listed some info. below on our setup.  Thank you

We will most likely use three servers, possible using VMs
We have a SAN with 1GB networking
Solution needs to have auto-fail over and the secondary node's database needs to be synchronous
If possible with a solution to have mixed hardware (Intel, AMD)
All servers and equipment located in one location
We will be using either SQL Server 2012 R2 or 2014

My Network Admin. wants an "Active/Active" with shared storage

There is no such thing as an "active/active" instance.  Only one node of the cluster can be hosting the SQL instance for a failover cluster instance.  If by "active/active" you mean having two different SQL Server failover cluster instances, that is possible.

Why shared storage?  That's an odd "requirement", and usually comes as a need from a requirement, not typically a requirement itself.  The failover cluster instance would exercise shared storage, but different replicas in an availability group would have their own storage.

  load balancing

There is no load balancing with a failover cluster instance.  For an availability group, you can offload certain operations to secondary replicas (reporting/readonly workloads, backups, etc.), but there is no fully capable (data modifying) load that can be offloaded to a secondary.

  redundancy

This can be achieved with either a failover cluster instance and/or an availability group.

  auto-fail over

This can be achieved with either a failover cluster instance or an availability group.

One of the main questions you need to ask yourself is what level of high availability are you looking for?  A failover cluster instance will give you HA for the whole instance, whereas an availability group will give you high availability of a grouping of databases as a unit of failover.


ASMCMD doesn't return any result I'm trying to use ASMCMD but it doesn't return any result in the command line

ASMCMD> ls
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> lsdg
ASMCMD>
ASMCMD> pwd
ASMCMD>

The version of database is 19c, the only environment about oracle grid is the GRID_HOME, but I also have the ORACLE_HOME and ORACLE_BASE, currently my $ORACLE_SID is orcl and my $ORACLE_HOME is /u01/app/oracle/product/19.3.0/dbhome_1.

My output about srvctl config asm is:

ASM home: <CRS home>
Password file: +DATA/orapwasm
Backup of Password file:
ASM listener: LISTENER,LISTENER2
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.1011650339
ASM diskgroup discovery string: /dev/oracleasm/disks/*

Incorrect environment. ORACLE_HOME for grid should be pointing to grid directory and ORACLE_SID for ASM should be +ASM.

Try this:

export ORACLE_HOME=$GRID_HOME
export $ORACLE_SID=+ASM
export PATH=$GRID_HOME/bin:$PATH

– Kumar


Register the time a machine running in a timerange using sql machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  135            12:00

the above machine runs from 13:45 to 16:00. I want to  register the first 15 mins in 12:00 timerange and next 120 mins in 2:00.

Currently my case statement is based on startdatetime and registers the hours in one timerange even though it overlaps two timeranges or can run across multiple timeranges. Also, the 24 hour is supposed to be broken down into 2 hour intervals. So total 12 intervals. And a process can run across multiple intervals.Also fyi i dont have create access on this database. Any help is appreciated. Thanks!

I am looking to present the data as

machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  15             12:00
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  120            2:00

This is what i tried and it seems to be working.

--split duration on a 2 hr basis

select case when st < startdatetime then startdatetime else st end [startdatetime],    case when et > [enddatetime] then [enddatetime] else et end [enddatetime] from (select *,
            xstart = dateadd(mi, 120*(datediff(mi, 0, d.startdatetime)/120), 0),
            blocks = datediff(mi, d.[startdatetime], d.[enddatetime])/120+2
     from table d) d cross apply (    select
          st = dateadd(mi,v.number*120,xstart),
          et = dateadd(mi,v.number*120+120,xstart)    from master..spt_values v    where v.type='P' and v.number <= d.blocks
     and d.[enddatetime] > dateadd(mi,v.number*120,xstart)) v

The following code will spray your one row into 2 as desired.

Declare @starttime time
Declare @endtime time
Declare @machinename varchar(10)
Set @machinename = 'vxx:2019'
If OBJECT_ID ('tempdb..#A') is not null Drop Table #A
Create Table #A (machinename varchar(15), startdatetime datetime, enddatetime datetime, timerunning int, timerange varchar(10))
Insert into #A (machinename, startdatetime, enddatetime, timerunning, timerange)
Select 'vxx:2019',    '2020-02-07 13:45:18.790' ,   '2020-02-07 16:00:38.920' , 135, '12:00'

Select @starttime = cast(startdatetime as time)
from #A
where machinename = @machinename

Select @endtime = cast(enddatetime as time)
from #A
where machinename = @machinename

If (@starttime  between '12:00' and '14:00') and (@endtime > '14:00')

Begin
Select machinename, startdatetime, enddatetime, datediff(minute,@starttime,'14:00') as timerunning, '12:00' as timerange
from #A
UNION ALL
Select machinename, startdatetime, enddatetime, datediff(minute,'14:00',@endtime), '2:00' as timerange
From #A
End

The two time ranges specified in the question are 12-2 and 2 - end time. It uses variables to determine whether the job runs across the time range or not. If the job runs across the time range then it just uses the datediff function to determine the time running in those timeranges and sprays it out into two rows. If you need to do this on a whole table a while loop is probably your best bet.


Linked Server : How to set an exclusive lock on a remote table? As I found, you can set a lock on your desired table within a transaction with :

begin tran
    select 1 from myTable
        with (tablock /*or tablockx to block even reads from table*/, holdlock)
    -- do your work
commit tran

But when I run this code on a linked server table, the table still accessible from within linked server! Why? How I can set a lock on this remote table like my example?

When trying to execute a transaction across multiple servers, you need to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

If my understanding is correct, BEGIN TRANSACTION creates a local transaction by default. Under certain circumstances (for instances, if you're doing an UPDATE on a table on the remote server) this will be escalated into a distributed transaction. If you have remote proc trans turned on for the remote server, then things like running a remote stored procedure should also automatically escalate to distributed transactions.

Otherwise, the remote operation will follow the normal transaction rules as a standalone operation on the remote server - in this case, the SELECT would be in its own one-statement "local" transaction on the remote server, and the lock would be released when the SELECT completed.

You may also need to use SET XACT_ABORT ON.

And, (as you noted in comments), you'll need to be sure the MS Distributed Transaction Coordinator service is on.


Combining multiple left join for one single line We have a query as below.

SELECT tblSData.header,
tblEAlert.eMessage
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc

Structure for the tables are as below:

CREATE TABLE IF NOT EXISTS `tblSData` (
  `sDataID` int(11) NOT NULL AUTO_INCREMENT,
  `header` varchar(255) NOT NULL,
  `aID` int(5) NOT NULL,
  `gFInID` int(5) NOT NULL,
  `gFOutID` int(5) NOT NULL,
  `gFAInID` int(5) NOT NULL,
  `gFAOutID` int(5) NOT NULL,
  `eAlertID` int(5) NOT NULL,
  `dateTimer` datetime NOT NULL
   PRIMARY KEY (`sDataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `sDataID` int(5) NOT NULL,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample data for tblSData

1,"A1",1122,100,102,1,2,1,2013-07-13 15:30:19
2,"A3",1122,104,103,3,4,3,2013-07-13 15:45:19
3,"A4",1122,105,108,5,6,7,2013-07-13 15:55:19

Sample data for tblEAlert

1,1,1,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 1"
2,1,2,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 2"
3,2,2,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 3"
4,2,3,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 4"
5,2,4,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 5"
6,2,5,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 6"

Sample output is.

"A1","Alert 1"
"A1","Alert 2"
"A2","Alert 3"
"A2","Alert 4"
"A2","Alert 5"
"A2","Alert 6"

The problem now is how to show say for e.g. A1 all the Alert Message in one line and same goes for A2 etc. Now each alert message is on a different line.

Use the GROUP_CONCAT function

SELECT header,GROUP_CONCAT(eMessage) ErrorMessages FROM
(
    SELECT tblSData.header,IFNULL(tblEAlert.eMessage,'') eMessage
    FROM tblSData
    LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
    Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
) A
GROUP BY header;

or

SELECT tblSData.header,GROUP_CONCAT(IFNULL(tblEAlert.eMessage,'')) ErrorMessages
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
GROUP BY header;

A good index to create for this would be

ALTER TABLE tblSData ADD INDEX (aID,dateTimer);

What good will this index do ?

This will get everything for a specific aID
All aID rows are  ordered by dateTimer
The sDataID column will be available for use from the index rather than the table because the Clustered Index will include the PRIMARY KEY anyway.

You should also create this one

ALTER TABLE tblEAlert ADD INDEX (sDataID);

Give it a Try !!!


Does the Always Encrypted certificates get copied over in availability groups? We have a column set up with Always Encrypted, and we are adding it to an AG.  We'd like for the encryption to keep working if/when we have to fail over to a secondary server.  Will the Availability Group copy over the certificates, or do we need to install them by hand?

In AlwaysEncrypted, the keys are not stored on the server to begin with.


Store same sequence value in two columns I have a table in which one column is auto increment sequence number. I want the same value to be stored in another column as well (so it can be changed later).

My table structure is like this:

CREATE TABLE Test
(
    test_id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    ...
    ...
    uid integer not null DEFAULT currval('test_id_seq'::regclass),
)

I want uid = test_id but the problem is when multiple row are inserted in one sessions. Then currval doesn't take the correct id.

I don't want to use a trigger. How can I manage it?

In order to not rely at all on currval, you might transform the INSERT queries in systematic way following this pattern:

To insert from a select, instead of :

INSERT INTO Test(columns) SELECT columns FROM...;

consider this form:

INSERT INTO Test(test_id, uid, columns)
SELECT pk,pk,columns FROM
(SELECT nextval('test_id_seq') as pk, columns FROM ...) AS alias;

For a single row in a VALUES clause, instead of

INSERT INTO Test(columns) VALUES(...values here...)

consider:

INSERT INTO Test(test_id, uid, columns...)
SELECT pk,pk,columns FROM
 (SELECT nextval('test_id_seq') as pk, ...values here...) AS alias;

for VALUES clauses with multiple rows, instead of:

INSERT INTO Test(columns) VALUES(...values1...),(...values2...)

consider:

 WITH v(columns...) AS ( VALUES(...values1...),(...values2...) )
 INSERT INTO Test(test_id, uid, columns)
 SELECT pk,pk,columns... FROM
  (SELECT nextval('test_id_seq') as pk, v.columns... FROM v) AS alias;

If PostgreSQL had a standard-compliant NEXTVAL, you could do without the subquery and just use something like:

INSERT INTO ... SELECT NEXT VALUE FOR seqname,
                       NEXT VALUE FOR seqname,
                       other columns...

and the NEXT VALUE FOR seqname would evaluate to the same value for both columns of the same output row, as mandated by the standard. But PostgreSQL does not have this construct.


Oracle shrinking / reclaiming free tablespace space I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.

TB1 54 %    90880   41383   49497   46  98304   50 %    ONLINE  PERMANENT

The tablespace has 3 datafiles.

E:\ORACLE\ORADATA\xx\TB1.DBF    33554416    True    100
F:\ORACLE\ORADATA\xx\TB1_2.DBF  33554416    True    100
G:\ORACLE\ORADATA\xx\TB1_3.DBF  25344000    True    100

How can I shrink the tablespace to reclaim the free 40GB (datafiles on disk)? I can not have a downtime.

I agree with EdStevens' comment, but here are some other ideas:
The AskTom website  has a script that will list exactly how much you can shrink the existing data files.
If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but if budget doesn't allow more disk space now, try this query:
select segment_name, sum(bytes)/1024/1024 as mb
from dba_segments
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Then if you find an index named  ind_big_stuff is huge, then
alter index ind_big_stuff rebuild online;

Also, if your application is a commercial application, there may be several tables with zero rows.  Or if you have partitioned tables, you may have some partitions with zero rows.  You can drop space allocated to them with
exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

You can use the segment creation deferred clause on an alter table statement for things like partitioned tables, or tables you know will not have any rows.


MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by I'm having trouble using GROUP BY with only_full_group_by.

Here is my employee_job table:

+----+-------------+--------+------------+
| id | employee_id | job_id | created_at |
+----+-------------+--------+------------+
|  1 |           2 |     10 | 2019-01-01 |
|  2 |           3 |     20 | 2019-01-01 |
|  3 |           3 |     21 | 2019-02-01 |
|  4 |           3 |     22 | 2019-03-01 |
|  5 |           4 |     30 | 2019-01-01 |
|  6 |           4 |     35 | 2019-02-01 |
+----+-------------+--------+------------+

I would like to select only the latest lines per employee_id, which I think gives me:

SELECT *, MAX(created_at)
FROM `employee_job`
GROUP BY employee_id;

The thing is, with only_full_group_by, which I can't disable, I get an error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.employee_job.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Well... I tried to read about that, and it seems I don't get the error. Of course, if I add other fields to the GROUP BY, the result still contains multiple times the same id.

Can someone explain to me how to group my results, maybe if GROUP BY isn't the best way to do it, what is, please?

If you want ties, i.e. when an employee has two jobs on the same day, that happen to be the latest one display both, you can join a derived table with query doing the aggregation.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT ej2.employee_id,
                               max(ej2.created_at) created_at
                               FROM employee_job ej2
                               GROUP BY ej2.employee_id) ej2
                       ON ej2.employee_id = ej1.employee_id
                          AND ej2.created_at = ej1.created_at;

If you want to eliminate ties and say you want the one with the largest ID in such a case, add another level.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT max(ej2.id) id
                               FROM employee_job ej2
                                    INNER JOIN (SELECT ej3.employee_id,
                                                       max(ej3.created_at) created_at
                                                       FROM employee_job ej3
                                                       GROUP BY ej3.employee_id) ej3
                                               ON ej3.employee_id = ej2.employee_id
                                                  AND ej3.created_at = ej2.created_at
                               GROUP BY ej2.employee_id,
                                        ej2.created_at) ej2
                       ON ej2.id = ej1.id;


What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small Q 1:

Whenever an Oracle SP wants to return error code/message, it fails returning below exeption:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA-01403: no data found
ORA-06512: at line 1".

I am calling it from MS SQL via a linked server as follows:

declare
     @id1   int
    ,@id2   varchar(8)
    ,@code  varchar(10)
    ,@curr  varchar(4)
    ,@ed    datetime
    ,@return_status int
    ,@error_code    varchar(10)
    ,@error_msg     varchar
    ,@landed_cost   numeric(14,5)

begin try
    exec ('BEGIN db.get_cost(?,?,?,?,?,?,?,?,?); END;'
         ,@code
         ,@id1
         ,@id2
         ,@curr
         ,@ed
         ,@return_status    output
         ,@error_code       output
         ,@error_msg        output
         ,@cost             output
    ) AT ORASRV
end try
begin catch
    select
          @return_status    as return_status
         ,@error_code       as error_code
         ,@error_msg        as error_msg
end catch

It seems that the input parameters work fine, as their values are such that when the SP is returning a correct result, everything is working. Only the IN parameter values could cause the SP to return an error code, when the error above is thrown.

The declaration on the Oracle side is as follows:

PROCEDURE get_cost (
     code   IN  VARCHAR2(10 BYTE),
     id1    IN  NUMBER(7,0),
     id2    IN  VARCHAR2(8 BYTE),
     curr   IN  VARCHAR2(4 BYTE),
     ed     IN  DATE,
     return_status  OUT NUMBER
     error_code     OUT VARCHAR2(10 BYTE)
     error_msg      OUT VARCHAR2,
     cost   OUT NUMBER(14,5)
)

Q 2:

To add insult to injury, after throwing the above exception 3 times, and working fine once, the script then dies with the following MS SQL error message:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

It dies even if I exclude the 1st 3 calls that cause the original error and just call the SP successfully once.

As stated in the comments, both errors have been caused by declaring @error_msg varchar where it should have been ,@error_msg varchar(max)


Why storing users passwords at all? I occasionally see questions asking how to safely store user passwords for a web application (using an RDBMS, I'm not talking of Facebook or Twitter). The usual answer is "salt the password, then hash it with a strong algorithm such as TDES or SHA512".

My question is : As an RDBMS user, why should I bother at all with the password storing problematic at all since most engines have a built-in authentication mechanism.

For example, if some user X wants to create an account user password Y on my web application, how is issuing the following query wrong:

CREATE USER X WITH ENCRYPTED PASSWORD Y IN GROUP baseuser;

Then within my application, the user can open a connection to the database using his credentials and I don't have to bother at all of password management.

I see multiple advantages to this method:

If the RDBMS decides that the encryption algorithm needs to be changed, I don't need to touch anything, just to apply the security updates;
It is easy for me to manage the users authorizations. If a user is promoted to an administrator's role, I just have to add the user to the corresponding group;
SQL injections are now meaningless, for I manage permissions to allow exactly what I want to allow to each user in the database (for example, in a forum like SO, adding new posts, answering to posts, commenting and editing/deleting his own questions/answers/comments);
An user account "anonymous" can be used for unauthenticated connections to my application;
Each user is the owner of the data he provided.

But on virtually every question I see on this topic, there seems to be a general consensus that this is not the way things have to be done. My question is : why?

Note : The third point is allowed by policies in PostgreSQL, and security policies in Microsoft SQL Server. I realize that these concepts are newcomers, but anyway, now that they are here, why doesn't the technique I describe become the standard way to handle users accounts?

Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single dedicated service account is used to connect to the database back-end.

As a DBA, I don't want to have to manage, at the database level, the 10,000 active users of some medium-sized public-facing web application, or maybe the 2+ million users of some suddenly popular app.

In a very real sense, this is a difference in philosophy between application developers and database developers/DBAs.

Many/most application developers are not going to want to pass responsibility for major aspects of app functionality and/or business rules down to the database layer. Instead, they view the database as a tool to simply store and retrieve data.

In some cases, this might be short-sighted; many RDBMSes do have awesome features that could make app dev lives much easier (row-level security, columnar indexes, filestream storage, etc.).

But some of these cooler features are only available in newer versions, and organizations aren't always quick to upgrade existing environments (see this chart from 2014).

And in other cases, handling those things at the application layer is preferred (and not just for database platform portability, I frankly think that claim is overblown).


Does this regex reliably remove square brackets from T-SQL? I'm using SQL Server Management Studio 17.4, which supports search-and-replace using a Microsoft version of regular expressions (regex).

I need a regex expression that can reliably remove square brackets from source code, such as large CREATE TABLE statements that have been scripted from existing an database.

I'm currently using this regex in the "find" dialog:

\[((?!\d+)(?!PRIMARY))(([A-Z]|_|[0-9])*?)\]

The "replacement" is $2.

It seems to work very well, however I'm concerned this may eliminate required square brackets under some conditions.

In the regex above, the (?!PRIMARY) piece ensures ON [PRIMARY] does not have the square brackets removed.  Are there other exceptions I need to be aware of?

I'm willing to exclude the possibility of column names being reserved words, such as [GROUP].  The environment I work in has specific naming convention that typically eliminates that possibility.

When I first answered this question the rules for what characters would determine valid or invalid were a bit hazy (the documentation isn't entirely clear on it, and is in some ways misleading). However, since that time I have done some research into the specific rules for valid regular identifiers (i.e. those that do not need to be delimited by either "name" or [name]) related to this DBA.SE question:

How to create Unicode parameter and variable names

and have discovered the exact rules. The research into getting those exact rules lead to finding a list of Unicode Categories (not blocks) that can be used in combination to determine both the "first" / "start" character and any possible "subsequent" / "continue" characters. That research is towards the end of this post (Steps 7 and 8):

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2

Reworking your pattern I came up with the following:

(?!\[(?i:PRIMARY|GROUP)\])\[([\p{L}\p{Nl}\u0023\u005F\uFF3F][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*)\](?!\])

Some notes:

You don't need to "or" the separate items together with a | in a character class (i.e. the [stuff_here]) as it is already a list. In fact, including the pipe symbol actually means you were adding it to the list of allowed characters ;-).
You need to separate out the exclusion list (i.e. PRIMARY|GROUP...) such that they can be wrapped in [ and ]. Otherwise you allow for something like [PRIMARYa] to get skipped and keep its delimiters.
I added (?!\]) to the end to account for embedded escaped end-delimiters: ]].
This pattern is 99.5% effective. The only thing it does not catch is if you have both:

an embedded leading delimiter: [, AND
no embedded escaped end-delimiter: ]

Meaning, [Te[st] will erroneously un-delimit it to be [Test (oops), but both [Te[s]]t] and [Tes]]t] will remain delimited as expected. And really, how often are there embedded delimiters? And even then, how often is there just the beginning delimiter and not the ending delimiter? Very rare, I would think.

Of course, I said it was 99.5% effective but it only catches 2 reserved words. Easy enough to fix since we can get the list of reserved words. Adding in all 185 reserved words (well, 184 as I left out "WITHIN GROUP" since it has a space in it and is thus naturally excluded) gives us the following:

( I made it a comment instead of a code block so that it would be readable since it is a single line [that can be copied and pasted into the "Find" box] )

  (?!\[(?i:ADD|ALL|ALTER|AND|ANY|AS|ASC|AUTHORIZATION|BACKUP|BEGIN|BETWEEN|BREAK|BROWSE|BULK|BY|CASCADE|CASE|CHECK|CHECKPOINT|CLOSE|CLUSTERED|COALESCE|COLLATE|COLUMN|COMMIT|COMPUTE|CONSTRAINT|CONTAINS|CONTAINSTABLE|CONTINUE|CONVERT|CREATE|CROSS|CURRENT|CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|CURRENT_USER|CURSOR|DATABASE|DBCC|DEALLOCATE|DECLARE|DEFAULT|DELETE|DENY|DESC|DISK|DISTINCT|DISTRIBUTED|DOUBLE|DROP|DUMP|ELSE|END|ERRLVL|ESCAPE|EXCEPT|EXEC|EXECUTE|EXISTS|EXIT|EXTERNAL|FETCH|FILE|FILLFACTOR|FOR|FOREIGN|FREETEXT|FREETEXTTABLE|FROM|FULL|FUNCTION|GOTO|GRANT|GROUP|HAVING|HOLDLOCK|IDENTITY|IDENTITY_INSERT|IDENTITYCOL|IF|IN|INDEX|INNER|INSERT|INTERSECT|INTO|IS|JOIN|KEY|KILL|LEFT|LIKE|LINENO|LOAD|MERGE|NATIONAL|NOCHECK|NONCLUSTERED|NOT|NULL|NULLIF|OF|OFF|OFFSETS|ON|OPEN|OPENDATASOURCE|OPENQUERY|OPENROWSET|OPENXML|OPTION|OR|ORDER|OUTER|OVER|PERCENT|PIVOT|PLAN|PRECISION|PRIMARY|PRINT|PROC|PROCEDURE|PUBLIC|RAISERROR|READ|READTEXT|RECONFIGURE|REFERENCES|REPLICATION|RESTORE|RESTRICT|RETURN|REVERT|REVOKE|RIGHT|ROLLBACK|ROWCOUNT|ROWGUIDCOL|RULE|SAVE|SCHEMA|SECURITYAUDIT|SELECT|SEMANTICKEYPHRASETABLE|SEMANTICSIMILARITYDETAILSTABLE|SEMANTICSIMILARITYTABLE|SESSION_USER|SET|SETUSER|SHUTDOWN|SOME|STATISTICS|SYSTEM_USER|TABLE|TABLESAMPLE|TEXTSIZE|THEN|TO|TOP|TRAN|TRANSACTION|TRIGGER|TRUNCATE|TRY_CONVERT|TSEQUAL|UNION|UNIQUE|UNPIVOT|UPDATE|UPDATETEXT|USE|USER|VALUES|VARYING|VIEW|WAITFOR|WHEN|WHERE|WHILE|WITH|WRITETEXT)\])\[([\p{L}\p{Nl}\u0023\u005F\uFF3F][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*)\](?!\])

Also, with the new pattern you will need to change the replacement to be $1 instead of $2.

I tested with the following:

SELECT [f], [p$o],   -- match these
       [prImary], [12y], [a b], [g%g], [j^6], [f¾], [u²], [h⁊], [s|w]
FROM   dbo.[$a],
       dbo.[#a], dbo.[@d], dbo.[_a] -- match these
WHERE  f.[&col1] = N'it will also [match] <- that :-(';
/* and [this] */

--[PRIMARY]
--[PRIMARy]
--[PRIMARYg]
--[1PRIMARYg]
--[PRIMARY g]
--[GROUP]
--[GROUPBY]
--[@Test]
--[Te]]st]
--[Te]]st]]]
--SELECT 1 AS [Te]]st]]a[b];        --OOPS
--[Te]]s[t]]ab]
--[Te]]s[t]]ab]]
--SELECT 2 AS [Te]]st]]], 2.1 AS [p];
--SELECT 3 AS [Tes[tab];            --OOPS
--[Te[st]                           --OOPS
--[Te[s]]t]
--[Tes]]t]
--[T[e[s]]t]
--[dbo].[TableName]
--[dbo].[1TableName]
--[dbo.1TableName]
--[[t]                              --OOPS
--SELECT 4 AS []]t];
--[[t]]]
-- SELECT 1 AS [d], '---' AS [1f], '$' AS [g]
-- SELECT 1 AS d, '---' AS [1f], '$' AS g
--[OPTION]
--[OPTIONt]

That all being said, I will reiterate what I originally said in a comment on the question:

Is making the script "look nicer" worth any amount of risk of potential errors? I would say "no". I used to remove square brackets for pretty much the same reason, but have gotten to the place where I now always add them because "guaranteed working" beats aesthetics. For me. I realize not for everyone. Of course, if you are more comfortable with .NET RegEx, then SQL# (which I wrote) has RegEx_Replace for free .

Also, keep in mind that this is a global search and replace, so it will match strings of this pattern even if they are in comments or string literals. If you have such strings then this might not be desirable and you will either need to find a way to exclude those (especially string literals), or give up this fight ;).


How to convert mysql to postgresql? I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:

Free
Works under Linux
Simple to use and not buggy
You actually tried and confirm that works
Preferably, not written with Java or Ruby

I have tried different tools listed here, but none worked for me.

Thank you in advance.

Migrating between two very different DBMS requires a lot more than just migrating the data. But migration of the data is usually the easiest part.

The way that I have tried, which is free and I can confirm that it works:

create a mysql schema only dump
Adjust the SQL using a text editor and a lot of search and replace
run the converted SQL in Postgres
Create a plain text dump from MySQL (e.g. CSV, or some other delimited format)
import the data using PostgreSQL's COPY command

Importing the data might actually be difficult if you relied on MySQL's behaviour to accept illegal data (like the 31st of February)

My guess is, that this is going to be quicker than searching for a tool, evaluating a bunch of them and then trying to understand the one that you chose. But it depends on what kind of "big" you are referring to. If big is several hundreds of tables this might not be feasible. If big only referred to the number of rows, then this is probably the quickest way to do it.

There are some tools out there that can dump a database schema in a DBMS independent (XML) format, like Liquibase, SchemaSpy or WbSchemaReport. With Liquibase probably being the easiest to use. The others will require some manual work writing/extending the XSLT to transform the generated XML.

If you are using triggers and stored procedures in MySQL, I don't believe there will be any automated tool that can translate them without requiring a major manual fixing afterwards - and then the generated procedures would probably not use any advanced features of the target DBMS.


What "fromRouter" option means on MongoDB aggregations? Recently i notice that a parameter fromRouter" : true exists on mongoDB aggregations. I search the MongoDB documentation and google for it, but i didn't found anything about it. Can anyone explain what this parameter is, or share any useful resource for it?

"op" : "command",
"ns" : "database_name.$cmd",
"command" : {
    "aggregate" : "collection",
    "pipeline" : [...erase them to make the post smaller...],
    "fromRouter" : true,
    "$queryOptions" : {
        "$readPreference" : {
            "mode" : "primary"
        }
    }
},
"ntoreturn" : 1,
"keyUpdates" : 0,
"numYield" : 440,
"lockStats" : {
    "timeLockedMicros" : {
        "r" : NumberLong(3106252),
        "w" : NumberLong(0)
    },
    "timeAcquiringMicros" : {
        "r" : NumberLong(5302577),
        "w" : NumberLong(6145)
    }
},

It just means that this is a sharded cluster and the aggregation is happening via a mongos (also known as a query router).  This is important for aggregations internally because it means that the output from multiple shards may need to be merged but it's not really relevant for most end users (you usually already know you are running in a sharded environment).


Is it possible to see which SPID uses which scheduler (worker thread)? We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message:

  The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads

While we were able to "solve" the problem by moving one of the Availability Groups to another server, I was wondering if it is possible to see which queries run on which scheduler (or worker, or task).

With the following query I'm able to see how many workers are available, in use and waiting for resources:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

And with the following query, I'm able to see which workers are running on which CPU (core):

SELECT *
FROM  sys.dm_os_Schedulers s --> Prozessoren Kerne
JOIN sys.dm_os_workers w ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t ON t.task_address = w.task_address
WHERE s.status = 'VISIBLE ONLINE'
AND s.cpu_id = 2

Is there some way to find which SPID (and in the end which query) is running on which thread?

I was already searching for a while and found some interesting information about the connection between schedulers, workers and threads, but nothing that really showed me if it is possible:

Understanding SQL Server Schedulers, Workers and Tasks by Daniel Farina
Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean? by _Jo.Pi_

I would like to see which database uses so many worker threads. We have a few databases which (in my opinion) do not belong on the production server. When I check sys.dm_exec_requests there seems not much going on.

The environment has run in the same configuration for more than 1 year without problems. The server in question has 24 CPUs and 5 AGs on it, with a total of 325 databases. 3 AGs are primary. To work around the issue we failed an AG with 50 databases from that server over to the secondary.

Thanks to Solomon Rutzky:

  Have you tried correlating to the scheduler_id column in sys.dm_exec_requests?

I was able to get the information I was looking for.

With this query, I can see which session is using which CPU_ID (scheduler):

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type,
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle
FROM  sys.dm_os_Schedulers s
JOIN sys.dm_os_workers w
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r
    ON r.scheduler_id = s.scheduler_id
order by 1,3

To get the SQL statements that are running, I change the query to:

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type,
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle,
    te.text
FROM  sys.dm_os_Schedulers s
JOIN sys.dm_os_workers w
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r
    ON r.scheduler_id = s.scheduler_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) te
order by 1,3

But that only gives me (obviously) the tasks that have an SQL_handle.

It seems that most of the threads on that server are being used by the system itself to keep everything in sync. Most of the workers are being used as "system jobs". The last_wait_type of those tasks is mostly REDO_THREAD_PENDING_WORK or HADR_WORK_QUEUE.

Although I have the answer I was looking for, I still didn't find the source of the problem. I'm going to open another question for that ( Who is using my worker threads? SQL Server 2014 - HADR ).


Use index in ORDER BY query SQLite I have a table that store messages for my Chat app:
CREATE TABLE message (
    currentUserId,
    threadId,
    timestamp,
    message,
    msgType,
    msgState )

Then I used this query to load 20 message for each thread:
SELECT rowid, * FROM message WHERE currentUserId = '123' AND threadId = '456' ORDER BY timestamp LIMIT 50, 20

So which index should I create to optimize loading speed of this query?
I already try to index in each of these approaches but none of them seem to be working:

timestamp
threadId
(threadId, timestamp)

As ypercube mentioned, you'll want to create your index on all three fields, currentUserId, threadId and timestamp like so: CREATE  INDEX IX_YourIndexName ON message (currentUserId, threadId, timestamp). The reasoning being you should index on all fields in your predicates (JOIN, WHERE, and HAVING clauses) to create a fully covering index (see 1.7 Covering Indices) which would be the currentUserId and threadId in this case. And then also adding the fields from your ORDER BY clause will help sort the index on those fields as well which will reduce the work for sorting when your query runs. For more information on indexing in SQLite.
Note you might even see a slight performance benefit by switching around the order of currentUserId  and threadId in the index definition, assuming the threadId is more unique than the currentUserId in the table. By defining your indexes with the most unique fields first, it improves the selectivity of that index because less values need to be seeked on upfront. So if threadId is more unique of a field, you could try this index definition as well CREATE  INDEX IX_YourIndexName ON message (threadId, currentUserId, timestamp). But unless the difference in uniqueness between threadId and currentUserId is a lot, or you have a lot of data, you probably won't notice much of a difference, so either definition should be equally fine.


MySQL timestamp timezone handling Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be described with many different y-m-d-h-m-s+timezone values. Without timezone, it's ambiguous, the same moment can be 17:25 for me and 13:25 for someone else.

Here's what I think I get. The current_timestamp function returns the current time in the current timezone, so this is an unambiguous instant, the real now. Inside a table, it is stored as UTC, so with this assumed timezone, it should also be unambiguous.

Now here's what I don't get: it's the existence of the utc_timestamp function. I stored a current_timestamp and an utc_timestamp inside the same column, and they were different. This function shouldn't even exist, I don't understand why it represents a different instant in time.

TIMESTAMP stores the number of seconds from 1970-01-01 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data.

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured.
UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

If you're using a timezone that's not UTC, these functions always return different values, equal otherwise.

MySQL always stores the date and time in UTC so it can be easily converted to different timezones. NOW() or CURRENT_TIMESTAMP() return the date/time plus your timezone configured.

UTC_TIMESTAMP() just returns the date/time ignoring your timezone setting.


Oracle 11g Fast Add Column Taking 11 Hours To the best of my understanding, a new feature of Oracle 11g is the ability to add a not null column with a default value very quickly as it would no longer auto-update every row in the table with the default value in the new column. However, the following command takes ~11 hours on a table with ~1.2 billion rows on an 11.2.0.4 database that had just been upgraded from 10.2.0.5:

alter table table_name add column_name varchar2(6) default "DEFLT' not null;

Although I've seen the following in the documentation:

"However, the optimized behavior is subject to the following restrictions:
•The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
•The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column."

to the best of my knowledge my table does not meet any of these conditions. Describe shows no LOBs, ORGANIZATION INDEX isn't in the create statement, it isn't a temporary table, isn't a cluster, queue, object, or container table. And the column of course is a varchar column. Is there something I'm missing, or is the answer just that I'm mistaken about my table meeting one of these requirements?

ETA: Not sure if this is helpful, but I noticed in a paper that the tell-tell sign of fast column adds is the use of NVL in the filter predicate. From an explain plan on a test table, it does not appear that my database is executing fast adds:

SQL_ID  f78gwf6cz50uq, child number 0
-------------------------------------
select count(1) from t where z = 123456

Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   133 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   110K|  1406K|   133   (4)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Z"=123456)

Note
-----
   - dynamic sampling used for this statement (level=2)

Ok, I sacrificed one of my 10.2 sandbox databases for a good cause, and upgraded it to 11.2.

As I suspected, the above DDL optimization does not work with compatible parameter still set to '10.2.*'.

After increasing compatible parameter, it works as intended:

SQL> show parameter compa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      10.2.0.5.0
plsql_v2_compatibility               boolean     FALSE

SQL> alter table t1 add C_DDL number default 42 not null;

Table altered.

Elapsed: 00:00:52.85
SQL> alter system set compatible='11.2.0.0.0' scope=spfile;

System altered.

Elapsed: 00:00:00.00

SQL> shu immediate
startup
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             289407880 bytes
Database Buffers          771751936 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.

SQL> alter table t1 add D_DDL number default 42 not null;

Table altered.

Elapsed: 00:00:00.04


joining the where clause to select statement of two different tables How can I properly structure the below query to make it work? I would like to have the Ref_CD = MBR_ID_TYPE_ID to be my select statement on the beginning of it.

select MBR_ID_TYP_ID || '-' || (
    select ref_desc
    from ref
    where ref_nm='memberIDType'
        and REF_CD = MBR_ID_TYP_ID) as "Member Type", id.*
from mbr_id id
where mbr_id = 0989879;

I'm not too sure what you are doing there and that's not at all how I write queries. Either way, I would try it this way:

select
    a.MBR_ID_TYP_ID || '-' || b.ref_desc as "Member Type" ,
    a.*
from mbr_id as a
    left outer join ref as b
    on  b.ref_nm = 'memberIDType'
    and a.REF_CD = b.MBR_ID_TYP_ID    -- or: b.REF_CD = a.MBR_ID_TYP_ID
                                      -- not clear from the code
where
    a.mbr_id = 6484532;

I don't join in where clauses because that is really hard to read.
I generally put the columns in the select portion, then do the joins in the from section. The filtering goes on in the where clause. Finally if necessary the order by is last, if I need to sort.


How to convert postgresql dump file into mysql dump file I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance

You can do this using the MySQL Workbench Migration Wizard, which was introduced in MySQL Workbench 5.2.41.

There's a blog post on the MySQL Workbench site here that details how to perform a migration.

Note that it will not be able to migrate more complicated database features.


use of age() function postgres I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this.
I have run a query to get the earliest date in my table which
SELECT MIN(date_column) FROM table

I also know that
AGE(timestamp '2015-10-5')

will give me the time elapsed since 5th Oct 2015. Is there a way to pass the result of the MIN query to the age function to find the length of time since that event?

You can do a AGEfrom you MIN date

CREATE tABLE "table1" (date_column timestamp)

INSERT INTO "table1" VALUES ( NOW())

SELECT AGE(MIN(date_column)) FROM "table1"

| age              |
| :--------------- |
| -12:07:06.199423 |

db<>fiddle here


select rows where column contains same data in more than one record I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record.

Here's what I've got:

select a.*
from articles a
where a.article_title = (select article_title
                         from articles
                         where article_title = a.article_title
                         AND a.id <> articles.id)

HAVING is a great aggregate filter. (http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html) For example, select the article_titles with more than on occurrence:

SELECT count(*), article_title
FROM articles
GROUP BY article_title
HAVING COUNT(*) > 1;

Adding columns to the SELECT and GROUP BY clauses allow you to locate duplicates based on a composite key of multiple columns.


Track write requests on Read-only database I have changed one of the databases to read-only in SQL Server 2005. I would like to track if someone tries to write to the database.

Any suggestions on how to track such requests (end user write request failures)?

A readonly database is "READ_ONLY". No writes are allowed - the application will get an error stating that the database is readonly.

You can run a server side trace (since you are stuck with sql server 2005) and look for user error messages and filter your database by ID.


SQL Server stop/exit query on error I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying:

String or binary data would be truncated.

When I go to check the source table I find out that the table does not exist. It was deleted by the drop statement. I thought the second statement would not be executed because the first one (the insert) failed.

How can I write the below code so that it will fail the entire query and not drop the table unless the first part (the insert) succeeds?

Do I need to use Begin, End and Go?

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable

DROP table SomeOtherTable

Basically, you need to test to see if the insert succeeded.  It will either succeed and everything will be inserted, or it fails and nothing is inserted.  Check @@ROWCOUNT like this:

create table SomeTable (someVale nvarchar(10));
create table SomeOtherTable (someVal nvarchar(11));

insert SomeOtherTable values
(N'1234567890'),
(N'12345678910');

select * from SomeOtherTable;

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable;

if @@ROWCOUNT > 0
    DROP table SomeOtherTable;

select * from SomeOtherTable;

You could wrap it in a transaction if you want or use a GOTO with a label.  It all depends on how you want to terminate.

For a full list of options and considerations, see Erland Sommarskog's reference articles:

Error and Transaction Handling in SQL Server


What is the privilege my user requires to be able to grant CONNECT, RESOURCE and CREATE SESSION to other users in Oracle 11g? What is the privilege my user requires to be able to grant the following privileges

CONNECT, RESOURCE and CREATE SESSION

to OTHER users in Oracle 11g?

If a system privilege is granted with ADMIN OPTION to a user, this user can grant this system privileges to other users, e.g. after

GRANT CREATE SESSION TO SCOTT WITH ADMIN OPTION

Scott is able to grant CREATE TABLE to other users. He is also able to grant CREATE TABLE to other users WITH GRANT OPTION. WITH GRANT OPTION works for roles too.

An object privileges (like a select on a table) must be granted WITH GRANT OPTION to a user to grant it to another user.

GRANT SELECT ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION

This can be found in the SQL Language Reference


'CONCAT' is not a recognized built-in function name A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however:

  'CONCAT' is not a recognized built-in function name.

I understand that CONCAT() is a new built-in function introduced in SQL Server 2012, which is all fine and well, however I have been asked to roll back my change to make this 2008R2 compatible under the guise of "the user executing the query may not have Transact-SQL permissions to execute." So I am just proving my point that the client most likely has a different version of SQL Server installed in DEV than they do in PROD.

I can't find any information about specifically denying SELECT/EXECUTE permissions for built-in scalar valued functions, but is it possible, and if so does the user still receive the same error text?

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation:

There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with earlier versions, you'll need to use the standard string concatenation operator (+). I don't know how you would do this with a scalar function, unless you always used the exact same number of input strings and you change your code to use dbo.CONCAT() instead of CONCAT() (there will be scenarios where it matters, plus if your function does anything the native doesn't do, you want consistent behavior if/when you upgrade). So I wouldn't recommend that approach. You may also need to add NULL handling and other minor changes (impossible to tell you how to change your existing script exactly, if we can't see it).


Mapping of multiple server logins to one database user This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).

Is it possible to log multiple server logins (sql server authentication) to a single database user (which has permissions assigned by being member of a database role)?

I have dozens of sql logins (sql server authentication) which need to read one setting from a central database and I'd rather map all these logins to a single DB user in the target database than create an own DB user for each login.

If yes, what would be the correct T-SQL syntax?

First to make sure of terminology.  A Login is an instance level security principal (sys.server_principals) and a User is a database level security principal (sys.database_principals).  They are joined together by an SID (security identifier).  If you look in the system views above you can see how they are joined together in a 1:1 format by SID.  That's 1 Login to 1 User in a database.  A Login can have multiple Users but they must be in different databases.

So you will need to create a User in your database for each Login that you want to have access to it.  It's a simple enough command.

CREATE USER [UserName] FROM LOGIN [LoginName]

From there you can put all of your users into a single (or multiple) Role.  A role is a container that has permissions and shares those permissions with each User (for database roles) or Login (for instance roles).  There is a built in database role called db_datareader that gives read access to every table and view in the database.  You could add all of your users to that role.  However, a better idea would be creating a new role and adding it to the db_datareader role.  Then add all of your users to the new role.  The benefit here is that if you want to add additional permissions to the group you can by simply changing the permissions on the role.

Create the role by:

CREATE ROLE RoleName

Add a user to a role (or one role to another)

EXEC sp_addrolemember 'RoleName','UserName'

or if you are in 2012 or higher

ALTER ROLE [RoleName] ADD MEMBER [UserName]


Is capitalization of SQL commands/keywords required, expected or completely optional? Also, is it done automatically in some environments?

The SQL language is not case sensitive.  It is not important whether you type select or SELECT.

Often rules about capitalisation are included in a company's or project's coding standards. These often dictate where indentation, alignment and white space should be used, too.  This is important because it adds uniformity across all the code.  After a short while your eyes get used to the layout and can scan the code much more quickly, identifying important clauses and missing constructs.

Exactly where white space, alignment and capitalisation should be applied is a subject of active debate. Every programmer I've ever worked with had their own idea. There is no universally correct answer.  The value lies in uniformity, not the individual choices.

Some years ago colleagues were working on a project where the database was declared with a case sensitive collation.  Then they did have to be careful about capitalisation as key words or variables were considered different, much like Java.


Listener Log Oracle 11g R2 I'm using Oracle 11gR2 in RHEL5. Now a days my application, during run time, is showing exception as below.

  Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

and

 Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

It happens only during the peak time.

Where can I find the reason? Somewhere it might be logging all. Where is it? I checked the alert_db.log as well as log.xml inside the tnslsnr, but couldn't find anything relating this.

The documentation explains how to go about configuring listener tracing, but in summary:

Add:

TRACE_FILE_<LISTENERNAME>=listener.log
TRACE_DIRECTORY_<LISTERNAME>=/path_to_your_log_directory
TRACE_LEVEL_<LISTENERNAME>=user

You can up the TRACE_LEVEL to admin or support if you need more information, but be aware that they generate increasing amounts of output.

Your issue could be due to either a DB not being able to service a request due to the maximum number of processes being reached (check the alert log), a lack of resources on the box (are kernel parameters set correctly?) or an outright DB crash.


Restoring corrupted bak file Our server & Backup data got crashed and we had a recovered the data using the recovery Tool. Currently I am having .BAK Files.
When we are trying to rebuild the DB using the recovered .bak file getting the Errors

when i try restoring through query

RESTORE DATABASE dbName
   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       REPLACE,
       STATS = 10

i get the error as

  Msg 3241, Level 16, State 0, Line 1 The media family on device
  'D:\DB\NASH_20160718.bak' is incorrectly formed. SQL Server cannot
  process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE
  DATABASE is terminating abnormally.

please help thanks !!

you can get this message in different error/ issues on during the backup/restore process. To identify and fix this problem.

You might using different backup version than what your current database. This is the most probable cause of what I suspect from the error message displayed. You might have a backup file from higher version and now your current server is lower version. you can always restore from lower to higher but can not backward.
The second issue could be, you might have a corrupted backup file. So first make sure you have restore able backup file.
Your backup file might be encrypted. Make sure your backup is not encrypted and if so you might need to find the decryption key.

Hopefully this will resolve your issue.


stock management - need suggestion I am trying to make a stock management software according to my client's requirement, where I bought products by weight or qty depending on product type. Which has following database structure.
User { id, name, address, role (Customer, Vendor, Labor etc)}
Product {id, name, type}

Types of product:

Sheet
Circle (After cutting)
Rolled Circle (After rolling)
Dark Circle (After Buffing)
Ready Circle (After Polish)

The issue is the product I purchase may go through 4 process:

Cutting
Rolling
Moulding
Polish

The above process depends on what type of product I purchase. If I purchase raw product then I may give it to labour who will cut it and give it back to me (i.e. cutting). In this process some material goes to scrap.
As you can see if I buy sheet which is purchased on weight is gone for cutting and turned circle (product type 2). Product type 2 is calculated as per qty.
I can ask client to put approximate weight of qty they received but issue is how can I manage the stock how much is remain at labour place.

Consider I am distributor I purchase products from multiple vendors, the products are classified in several types which is mentioned above. Every vendor has his price for products.
After purchase the product may go for processing or it may be sold. If it goes to process there will be 4 stages, according to client he can decide to sell product during any stage.
For e.g.

Client bought/purchased a product which is calculated as per weight in kg.
The product is given for cutting (stage 1) to multiple labours in parts for e.g. If I have 100 kg raw material, I gave 50kg to labour1, 20kg to labour2 and remaining is given to labour3, during this stage the product turned into multiple pieces (number of pcs are not same every time due to scrap generated during this process is not fixed so I cannot calculate the number of pcs by formula as of now we can expect the approximate pcs which may vary +/- 10 to 100).
Now labour will send me the pcs in parts for e.g. Labour1 got 50kg raw material which turned 1000 pcs he sent me these pcs in parts i.e.  500 pcs were sent day before yesterday 200 pcs were sent on yesterday 100 pcs were sent today Rest pcs are remaining at labour1 side

Issues I am facing

I am unable to manage how much stock I have given to labours
How much remain at their end (i.e. labours end)
How to maintain stock details for selling purpose at it is not managed through one table.

What I have done till now
I created following schema to solve my issue but I am not sure how much it is correct according to rules of DBMS.
Purchase
{ id, vendor_id, prod_id, weight, qty, focusOn (weight / qty),
  rate, amount, remainingWeight, remainingQty }

Job
{ id, purchase_id, prod_id, labour_id, weight, qty, focusOn (weight / qty) }

Returns
{ id, job_id, prod_id, weight, qty }

focus on parameter check whether to check weight of returned product or qty to make sure accountability.

You could approach it as double-entry book keeping. Each user has an "account" for each product, to which you credit and debit transfers. You act as the "bank" in the middle of the network. Each account's balance is the stock, either on-hand for sale or out for work.

The tables would be something like:

Participant
  ParticipantId
  <other columns>

I specifically do not call this "user" since participants need not be people. You will have a participant record, too.

Product
  ProductId
  UnitOfMeasure    -- will be "kilogram" or "piece"/"quantity"

One row for each of the five you mention, sheet through ready circle.

Account
  AccountId
  ParticipantId
  ProductIt

Each participant needs an account per product since there is no direct translation from e.g. sheet to circle, according to your description. Even from circle to rolled circle there may be wastage during handling so sending out 100 units does not mean 100 units are always returned. It's a lot like owning some dollars and some Yen - you can convert each to the other, but the point-in-time exchange rate fluctuates. Each must be held in its own bank account.

You yourself will have an account for each of the five products you deal with. This represents the items sitting on your shelves at any point in time, waiting for further work or to be sold.

Transfer
  TransferId
  FromAccountId
  ToAccountId
  <probably date, time, recipient etc.>
  Quantity

A row here records the movement of a given quantity of a particular product from one recipient to another. You know the participant and the product from the corresponding account. From the product you can determine if the Quantity represents a kilogram or an item.

For ease of processing you'll probably want to duplicate this under each account. This will need another table:

AccountTransaction
  AccountTransactionId
  AccountId
  TransferId
  DebitAmount
  CreditAmount

For each Transfer there will be two rows in AccountTransaction - one each for the FromAccount and the ToAccount; one will be a debit and the other a credit.

Over the years this table may get large and queries sluggish. At intervals (annual accounts?) it may be worth rolling it forward to a new starting balance and archiving previous transactions to a different table.

To answer your questions:

  I am unable to manage how much stock I have given to labours

Sum that Participant's AccountTransactions.

  How much remain at their end (i.e. labours end)

Well, you know how much you've transferred (previous question) but since you've no strong conversion rate or wastage you cannot know how much is actually in their workshop and how much has been scrapped. For that the labourers would have to report scrap and wastage directly.

  How to maintain stock details for selling purpose at it is not managed through one table.

Sum the AccountTransaction tables for your participant ID - these accounts record the units assigned to you and, presumably, sitting in your warehouse.

Apology: it is a while since I last wrote accounting software. Many of the details are a bit fuzzy now. I don't have the capacity to revise all the details and type a fully polished answer. There are a great many online resources available which can explain it more fully. Some finessing may be require around the foreign keys in Transfer and AccountBalance. Hopefully this is sufficiently clear to point you in the right direction. Good luck.


Selecting only from multiple tables in a transaction I need to be able to extract rows from multiple tables in a way that I don't get half changed transactions from other users whilst I'm extracting data. The extraction will take some time (over 100 tables to extract data from).

The purpose is to be able to copy a single users data from one database to another (mysql - non linked, not federated). From a single userID I can collect all the data from the different tables - exporting to a TABBED delimited file.

It would seem that using START TRANSACTION WITH CONSISTENT SNAPSHOT; would be the way to go, but before I go off developing something, is this going to achieve what I want?

Using mysql and all tables are using the INNODB engine.

"consistent snapshot" and "repeatable reads" consistency seem to do exactly what you are asking, even mysqldump uses it with its --single-transaction option.

Because InnoDB implements MVCC, the DML running in parallel won't bother you when all the selects are non-locking (and from InnoDB tables).

The only way how to get "outside" is using SELECT .. FOR UPDATE which will be executed in the "read commited" isolation mode even when the transaction is set to "repeatable reads". But that should not be needed for extraction of data.


Access SQL Server Express LocalDB from local area network I am developing a software which requires sql server. But for 50 MB application it is not good idea to tell customers to install SQL Server. So I think it is better to use SQL Server Express LocalDB.

I want to give feature of accessing my app from Local area network. But I am not getting any related topics to do this.

Is it possible to access SQL Server Express LocalDB on Local Area Network?

What are the limitations of SQL Server Express LocalDB compared to SQL Server?

LocalDB cannot be accessed remotely, it's by design and documented.

Here is a description of LocalDB.

Here is a list of LocalDB features and limitations.

I don't see how installing SQL Server can be a problem. 120 MB is not that big nowadays even for small applications. You can embed the setup in your application and ship SQL Express with your setup kit. If you need a SQL Server Edition with a small footprint, consider using SQL Server Compact Edition.


Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why? I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.

I currently have the following result in the execution plan for a very simple SELECT query:

As you can see it is off by 5 rows.

I then run:

UPDATE STATISTICS  Person.Address WITH FULLSCAN
UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN
GO
EXEC sp_recompile 'Person.Address';
GO
SELECT * FROM Person.Address OPTION(RECOMPILE)

However, it is still off by 5 rows.
Why?

I know I shouldn't worry unless there is a performance problem. However, I am trying to understand the actual effect of a complete statistics update

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB
GO

DROP TABLE IF EXISTS dbo.stattest
GO
CREATE TABLE dbo.stattest (ID int primary key, junk char(1))

INSERT dbo.stattest
SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'
FROM master..spt_values a
CROSS JOIN master..spt_values b
CROSS JOIN master..spt_values c

SELECT COUNT(*)
FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN
GO
SELECT *
FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)
FROM dbo.stattest
OPTION(
QUERYTRACEON 3604,
QUERYTRACEON 2363
)

  CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)


It is unclear which of two problems you are asking about.  I will address both.
The question of missing days requires another table:
SELECT ...
    FROM table_of_days AS d         -- pre-construct a big table of lots of days
    LEFT JOIN your_table  USING(dy)
    WHERE d.dy BETWEEN ... AND ...   -- the range of days desired for this SELECT

(This is one place where MariaDB shines -- it has a seq_0_to_1000 pseudo-table from which you can easily build a list of dates.)
The LEFT JOIN will provide NULLs for days with missing info.  Then...
Here are two ways to turn NULL into 0 (there are others):
mysql> SELECT IFNULL(x, 0), COALESCE(x, 0)  FROM (SELECT null AS x UNION SELECT 123) z;
+--------------+----------------+
| IFNULL(x, 0) | COALESCE(x, 0) |
+--------------+----------------+
|            0 |              0 |
|          123 |            123 |
+--------------+----------------+

In your case, COUNT(*) provides 0; don't use COUNT(day) unless you want NULL.  In general COUNT(*) counts the number of rows, while COUNT(foo) counts the number of rows WHERE foo IS NOT NULL.  However, this is an exception; see AndriyM's Comment.


MariaDb mysqldump configurations We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process.
After some research, we found out that [mysqldump] section can be added in my.cnf file. also options like 'quick' , 'max_allowed_packet = 512M' can be added for it.
If we can add 'single-transaction' also along with above settings then DB will not be locked during the dump and it should speedup the application.
So are below entries are fine for my.cnf (mariaDB 19.3) ??
[mysqldump]
single-transaction
quick
max_allowed_packet = 512M

If your database structure is relatively static then dump it separately (once), and dump only tables data using SELECT .. INTO OUTFILE - this is more fast.
Of course you need:

create a script (the best way - an event procedure) which dumps all (or only changeable) tables data
create a script (maybe as stored procedure in structure dump) which restores the data from this dump taking into account that relational data must be restored in "one then many" order strictly


I can't figure out how to call a function with a trigger, keeps saying function doesn't exist CREATE OR REPLACE FUNCTION calculate(nr char) RETURNS char AS $$
    BEGIN

      RETURN (SELECT COUNT(*) FROM patient_notitie WHERE pn_patient_nr = nr);

    END;
$$ LANGUAGE plpgsql;

This worked:

SELECT * FROM calculate('100001');

This is where it went wrong, when we try to create the trigger to call the function:

CREATE TRIGGER trigger_calculate AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate(pn_patient_nr);

It says the function doesn't exist:

  ERROR: function calculate() does not exist SQL state: 42883

Before creating trigger, you need to create a trigger function, which has to be (quoting the manual):

  A user-supplied function that is declared as taking no arguments and returning type trigger

Like:

CREATE OR REPLACE FUNCTION calculate_t_f()
  RETURNS trigger AS
$$
BEGIN
...
-- do something
-- maybe with with calculate(char)...
...
END
$$ LANGUAGE plpgsql;

Then you can create your trigger with that trigger function (not your original function):

CREATE TRIGGER trigger_calculate
AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate_t_f();


psql: FATAL: password authentication failed for user "GuardDog_02" In postgresql 9.5.1, I created a user called GuardDog_02. I gave it a password. And I gave it permissions to create db and user:

CREATE USER GuardDog_02 WITH PASSWORD 'apple100' CREATEDB CREATEUSER;

Then under the GuardDog_02 system user (Mac OSX):

$ psql -d template1
Password:
psql: FATAL:  password authentication failed for user "GuardDog_02"

I updated password just to make sure I typed it right:

template1=# ALTER USER GuardDog_02 WITH PASSWORD 'apple100';

Still it says password authentication fails. I checked the pg_hba.conf file too:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

What am I doing wrong?

When issuing CREATE USER GuardDog_02... the exact user name created is
guardgog_02 because of SQL case folding. You may check that with \du in psql.

But according to that error message:

  psql: FATAL:  password authentication failed for user "GuardDog_02"

the username you're trying to log in with is GuardDog_02 (G,D in capital letters), which does not exist with this capitalization

To create that exact name, the identifier must be enclosed in double quotes, as in:

CREATE USER "GuardDog_02" WITH PASSWORD 'apple100' CREATEDB CREATEUSER;

Or, alternatively, if you don't want to recreate the user, log in with psql -U guarddog_02.


Case always returns null? I have a subquery that gets the DATEDIFF of two dates. However the first case always returns null yet the actual sub query returns dates?!

CASE
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1)
WHEN date1 < date2
THEN DATEDIFF(DD,getdate(),date2)
END[date diff]

FROM(

SELECT
licext.new_licenceId,
CASE
WHEN CONVERT(time,licext.new_EmergencyLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_EmergencyLicenceExpiryDate))
ELSE CONVERT(date,licext.new_EmergencyLicenceExpiryDate)
END [date1], --EM Code

CASE
WHEN CONVERT(time,licext.new_standardLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_standardLicenceExpiryDate))
ELSE CONVERT(date,licext.new_standardLicenceExpiryDate)
END [date2]  --FRL
from new_licenceExtensionBase licext
)QUERY

The reason the second cases are on there is because the database I am working with stores datetime in a really weird way, ie)If it has 23:00 at the end it actually means tomorrows date?! (This is a Microsoft CRM Database) -But that's a whole different matter.

EDIT:

It did have an ELSE clause which worked fine for date2 being bigger than date1, because that was being evaluated. For Example, the following code works fine for getting the difference between today and date2:

CASE
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1)
ELSE DATEDIFF(DD,getdate(),date2)
END[date diff]

EDIT 2:

I've just done a couple of tests:
Using a select to return the dates works fine. -They return as expected.

Test 2: I've added a third WHEN to the query to say that when a date is null, return 1990-01-01..However it still returns null, Is my whole case being skipped over?

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.


Need Help in extended events SQL Server I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now

CREATE EVENT SESSION [PerQuerymonitor] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlos.wait_info_external(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.disk_log_read(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text))
ADD TARGET package0.event_file
(SET filename=N'C:\SQLDatabase\PerQuerymonitor.xel',
max_file_size=(204800))
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Appreciate your kind help.

This isn't answering your question, but please be careful with extended events.  This one has no filters on it at all, so it's collecting a lot of data.  If this is run on a small test box then you won't know and won't care.  If you ran this on a highly transactional production system then there's a good chance your users would be able to tell you that something bad is happening.

If you want to know more about extended events read Jonathan Kehayias's work.

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/

If you have a PluralSight subscription he and his coworker Erin Stellato have some great videos teaching you a lot more on this subject.


How can I execute multiple DDL statements within a transaction in Oracle? Is it possible to execute multiple DDL statements under a single transaction with Oracle database (10g etc.?) What I'm trying to accomplish is some sort of "upgrade" functionality of the database schema. I have a working implementation of this in SQL Server and I'm planning to port it to Oracle.

p-code follows:

start transaction

 CREATE TABLE A .....
 CREATE TABLE B ......

 INSERT INTO A....
 INSERT INTO B...

 commit transaction

 or error rollback transaction

You can issue multiple DDL statements in a single transaction using the CREATE SCHEMA command though you are limited to just the CREATE TABLE, CREATE VIEW, and GRANT statements.

CREATE SCHEMA AUTHORIZATION <<Oracle username>>
  CREATE TABLE a( col1 number, col2 number ) -- Note no semicolon
  CREATE TABLE b( col3 number, col4 number );

If you are trying to upgrade an existing database, depending on the Oracle edition and configuration, I would tend to suspect that you'd be better served with something like Oracle Flashback Database.  If you create a guaranteed restore point before you start the upgrade, for example, you can simply flashback the database to that restore point if the upgrade fails regardless of how many transactions you've committed during the upgrade.


Trouble with a query subselect I am trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Can anyone help work around this?
The strange inner select with many UNION ALL generates a list of all dates in a range. (adapted from https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range).
The number of levels of nesting seems excessive, but it's the only way I could get the query to work at all.
select p.products_id,
       ( SELECT count(*)
         from ( select ( SELECT if(v1.quantity, v1.quantity, null)
                         FROM inventory_history v1
                         where v1.products_id = p.products_id
                           AND v1.inventory_date <= t1.date
                         ORDER BY v1.inventory_date desc limit 1
                        ) as quantity
                FROM ( SELECT a.date
                       FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
                              FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
                             ) a
                       WHERE a.date >= curdate() - INTERVAL 400 DAY
                      ) t1
                having quantity is not null
               ) x
        ) as count
from products p
where p.master_categories_id=264;

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'where clause'

What I'm trying to do: inventory_history contains snapshots of inventory levels by date. The data is stored sparsely -- only adds a record when inventory changes. Most of the query is converting that sparse data into results for all dates in range, then I want to count the number of days where the inventory quantity is > 0.
Server version: 10.1.35-MariaDB FreeBSD Ports
Fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e199d540f40e4362e6aa1d667e17fcac

There might be a way to rearrange parts of the correlated subquery so that it can reference the outer level's products_id directly, rather than in a subquery/derived table of its own, but in the end I had too little patience to find it.
Instead, I can offer you a different approach to calculating the same results, which uses variables:
SELECT
  p.products_id
, (
    SELECT
      SUM(
        DATEDIFF(to_date, GREATEST(from_date, CURDATE() - INTERVAL 400 DAY)) * factor
      )
    FROM
      (
        SELECT
          IF(h.products_id = @p, @d, CURDATE() + INTERVAL 1 DAY) AS to_date
        , @d := h.inventory_date                                 AS from_date
        , (quantity > 0)                                         AS factor
        , @p := h.products_id                                    AS products_id
        FROM
          inventory_history AS h
        ORDER BY
          h.products_id ASC
        , h.inventory_date DESC
      ) AS v1
    WHERE
      v1.products_id = p.products_id
      AND v1.to_date >= CURDATE() - INTERVAL 400 DAY
  ) AS count
FROM
  (SELECT @p := null, @d := null) AS init
, products AS p
WHERE
  p.master_categories_id=264
;

With the help of two variables – @p to keep track of the current products_id, and @d to carry over inventory_date to the preceding record of the same product – the v1 derived table turns inventory_history into a set of date ranges, with a flag (factor) to indicate whether the range qualifies for the count result or not.
The derived table's definition is two levels deep, so it cannot reference the main query's products_id.  It simply returns the entire set.  However, the SELECT that is using v1 is where p.products_id is valid, and that is where filtering on products_id happens.
The count itself is then simply a matter of adding up the date ranges' lengths, using the DATEDIFF(end_date, start_date) function.  The starting day expression (GREATEST(...)) simply makes sure that the earliest date range's starting point, for the purposes of the query, is no earlier than 400 days from now.
Here is a link to a live demo of this solution at dbfiddle.uk using your test setup:

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=dc5baefec8902a99bf807115aaa235ba


Granting Permission on Databases and Schemas I have a  Security permissions script i have inherited that i'd like to improve. The script grants permissions on the user databases and Schemas.
I'd like to improve the script by  iterating through all the schemas instead of hard coding the schemas like i did within the cursor.
Any thought?

**

-- Assigning Permission to 'Domain\Development' at a DAtabase level
DECLARE @command nvarchar(max), @usr sysname, @DB nvarchar(500)
DECLARE @loginName varchar(256)
SET @loginName ='Domain\Development'
DECLARE Devcur CURSOR FOR
SELECT NAME FROM sys.databases WHERE database_id>4 AND NAME <>LOOKUP'
                                                   AND NAME  NOT LIKE  '%ReportServer%'
                                                   AND NAME NOT LIKE '%DBAdmin%'
                                                   AND NAME NOT LIKE '%DBA%'
                                                   AND state_desc='ONLINE' AND is_read_only=0
OPEN Dev
FETCH NEXT FROM Devcur INTO  @DB
WHILE @@FETCH_STATUS = 0
BEGIN
--Granting Access to User databases
BEGIN
    SET @usr='['+@loginName+']'
    SELECT @command = 'Use ' + @DB + ';
    EXEC sp_addrolemember N''db_datareader'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_ddladmin'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_datawriter'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_owner'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    GRANT Showplan TO '+@usr+';
    GRANT EXECUTE TO '+@usr+';
    GRANT VIEW DEFINITION TO '+@usr+';
    GRANT CONNECT TO '+@usr+';
    GRANT CREATE SCHEMA TO '+@usr+';
    GRANT DELETE TO  '+@usr+';
    GRANT INSERT TO  '+@usr+';
    GRANT UPDATE TO  '+@usr+';'

    PRINT @command
    Print '-----------------BUILD-----------------'
    Print @DB
--EXEC SP_executeSQL @command
END
--Access SOMER schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''SOMER'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[SOMER] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Poser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Poser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Lser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Lser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
FETCH NEXT FROM Dev INTO  @DB
END
CLOSE Dev
DEALLOCATE Dev
GO

**

Would very much appreciate.

Your script is assigning the user to the db_owner role.

All other permissions being applied are therefore redundant as this implies ALL of those other permissions.


Possible Pitfalls of Setting Up Kerberos to Permit Cross-SQL Database Access Via AD Trusted Connection We are hoping to the implement Kerberos on our Active Directory (2003, Functional Level 2) so that our SQL Server 2005 databases can communicate with one another when a client is using Active Directory Trusted Connectivity.

The reason this wasn't implemented back in the day (circa 2001?) is that we were highly concerned about the holes using Kerberos left open. We haven't truly revisited this issue until now. After reading a couple articles today, namely How to Implement Kerberos Constrained Delegation with SQL Server 2008 and Understanding When Kerberos Delegation Is Needed for SQL Server we see that it's possible to limit this to SQL Server.

Questions

What are some possible pitfalls that we will run into?
What might break?
Is there a reason not to do this?

EDIT: And dang it...I can't add a Kerberos tag!

I did this as well to make linked server permissions sooo much easier to manage. You get to choose "Be made using the login's current security context" when setting up linked server security.

I rarely but occasionally see some inconsistent behavior with the registering / deregistering of SPN's (Kerberos authentication will fail if the SPN becomes deregistered). It very well could be something with our setup, I occasionally experience other network vagueries as well. I mention it because it's always the first thing I check when I get the dreaded "Login failed for NT AUTHORITY\ANONYMOUS LOGON". That is the error you'll get if you've configured security as mentioned above, but Kerberos fails and authentication drops down to NTLM.


can't update user password on Mongodb 4.0.5 (Use of SCRAM-SHA-256 requires undigested passwords) I'm trying to update a password from a user, using changeUserPassword(), and I just receive the following message:

  Error: Updating user failed: Use of SCRAM-SHA-256 requires undigested passwords

> db
mydb
> db.getUsers()
[
    {
        "_id"  : "mydb.mongo",
        "user" : "mongo",
        "db"   : "mydb",
        "roles" : [
            {
            "role" : "dbOwner",
            "db" : "mydb"
            }
        ],
        "mechanisms" : [
            "SCRAM-SHA-1",
            "SCRAM-SHA-256"
        ]
    }
]

 > db.changeUserPassword("mongo", "mongo_n3w_p4ss");
2019-05-22T20:58:10.128-0500 E QUERY    [thread1] Error: Updating user failed: Use of SCRAM-SHA-256 requires undigested passwords :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DB.prototype.updateUser@src/mongo/shell/db.js:1487:15
DB.prototype.changeUserPassword@src/mongo/shell/db.js:1491:9
@(shell):1:1
> db.version()
4.0.5

I also tried updateUser(), but no use.

What am I missing and what is the meaning of "requiring undigested password"?

Basically, I made it with db.updateUser.

On the doc, it says the following:

passwordDigestor : string

  Optional. Indicates whether the server or the client digests the password.

  Available values are:

  "server" (Default)
          The server receives undigested password from the client and digests the password.

  "client" (Not compatible with SCRAM-SHA-256)
          The client digests the password and passes the digested password to the server.

  Changed in version 4.0: The default value is "server". In earlier versions, the default value is "client".

It seems that, the default was "client", by unknown reasons. By the way, my Mongo Shell Client is v3.6.3.

Therefore, I just defined passwordDigestor as server, and ok. Logged out and logged in with the new password.

> db.updateUser("mongo", {pwd:"mongo_n3w_p4ss", passwordDigestor:"server"});


Need help on database design I have a huge database of library system but I have a problem on how to design tables on this database. This is because I have under one entity (for example AUTHOR) a lot of repeatable fields. So I don't know whether to make a table with a foreign key for each repeatable data or that this will result in too many tables and may affect performance.

Taking into consideration that AUTHOR is only a small entity with about 30 columns like author or somehow bigger.

So is it suitable to make about 10 tables for author only?

I know that I will make all the Non Repeatable in one table called Author but the big problem is for the rest of fields.

For example:

Author ( Repeatable )

NOTE

(R)    ---> Repeatable

(NR) ---> Non repeatable

indicator means a character or digit which means some specific data about an author

First   Indicator Type of personal name entry element
0 --> Forename
1 --> Surname
3 --> Family name

Second Indicator Type of added entry
0 --> No information provided
2 --> Analytical entry

Sub-field Codes

•   a - Personal name (NR)

•   b - Numeration (NR)

•   c - Titles and other words associated with a name (R)

•   d - Dates associated with a name (NR)

•   e - Relator term (R)

•   f - Date of a work (NR)

•   g - Miscellaneous information (R)

•   h - Medium (NR)

•   i - Relationship information (R)

•   j - Attribution qualifier (R)

•   k - Form subheading (R)

•   l - Language of a work (NR)

•   m - Medium of performance for music (R)

•   n - Number of part/section of a work (R)    •   o - Arranged statement for music (NR)

•   p - Name of part/section of a work (R)

•   q - Fuller form of name (NR)

•   r - Key for music (NR)

•   s - Version (NR)

•   t - Title of a work (NR)

•   u - Affiliation (NR)

•   x - International Standard Serial Number (NR)

•   0 - Authority record control number or standard number (R)

•   3 - Materials specified (NR)

•   4 - Relator code (R)

•   5 - Institution to which field applies (NR)

•   6 - Linkage (NR)

•   8 - Field link and sequence number (R)

I suggest you build seperate tables for all repeatable data. This is good database design and the DBMS you are using should be able to handle it.
It provides you with these benefits:

You arrange data into logical groupings such that each group describes a small part of the whole.
You minimize the amount of duplicate data stored in your database.
You organize the data so when you modify it, you make the change in only one place.
You build a database in which you can access and manipulate the data quickly and efficiently.
Your database is better maintainable in case your data model changes.


Store row count or use query to calculate on Primary Key I am curious to know if I should store the row count for an instance number of friends of a user into a separate table or if I should use a query like

SELECT COUNT(user_id) FROM user_friend WHERE user_id = 20;

where user_id is AUTO_INCREMENTED PRIMARY_KEY

Is querying the COUNT each time slower than storing the row account in a separate table?

In my opinion, I shouldn't store the row counts separately as this will lead to Denormalization and also some other factors like having to update the row count again and again and fetching the records by joining the tables unnecessarily.

Redundant information in a database is a no-no.

If you have an index (such as the PRIMARY KEY) on user_id and if there is not thousands of rows for a user, the computing it on the fly is the way to go.