Querying the Username in a Share Data source (SSRS) I am running a project to go over all logins of all our SSRS 2012 datasources and I need a complete list of all datasources, INCLUDING the loginname used to connect. I couldn't find it anywhere.

I am convinced the datasource loginnames must be stored somewhere inside the database, or the RDL's themselves.

Does anyone have a clue how I can list all datasource login names of these  different datasources?

See attached pic for details:

The Content column in Catalog table only contains the connection string and whether the data source is using Windows credentials (authentications) or not the username

SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5

I did found the Username of the data source with this query :

select DSID, ItemID, SubscriptionID, Name, Extension, Link,
CredentialRetrieval, Prompt, ConnectionString,
OriginalConnectionString, OriginalConnectStringExpressionBased,
UserName, Password, Flags, Version
FROM DataSource

But it's encrypted - not only the password is encrypted, but also the username itself.

I would appreciate a lead into the right direction.

Thanks in advance!

Roni.

After doing some digging, I doubt you'll be able to find an unencrypted list anywhere. It's storing it in the report server, so the SSRS engine is encrypting it on it's own; i.e it's generating a key and salt in the engine code and using it to encrypt\decrypt those columns. You won't be able to query it via T-SQL, but you might have better luck using PowerShell to parse through the HTML on the reporting front end.

I believe the tag in question is the value for input named "ui_txtStoredName". I attached a picture as an example.


How to configure an instance to listen on port 1433 to allow remote connection Trying to connect using SQL Server Management Studio 2012 on a Windows 8.1 Client computer from outside the cluster's domain.

Cluster is a two node Virtual Cluster made from Windows Server 2012 R2.

We can connect to the cluster from within the cluster's domain by using the Failover Cluster Virtual Network Name Account with SSMS running on another Virtual Computer from within the cluster's domain.

We have created a new Inbound Rule with port 1433 on each of the two nodes - but still don't understand how to connect to the SQL Instance from outside the domain.

If you are connecting from outside the domain you can either

1) Use SQL authentication to login.  If you have enabled mixed mode during the install you will have an sa account and you can create a sql login for yourself

2) There are other tricks, such as enabling named pipes and then authenticating against the IPC$ share with your domain account and then you can login using np:ipaddress in SSMS.  But beware it is a bit slow and most people don't want named pipes enabled on a production system.

But if you are connecting from outside the domain the easiest way is use SQL authentication.


How to determine whether a PostgreSQL Hot Standby is fully mirrored? I have set up a hot standby of a PostgreSQL server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have the following:

LOG:  creating missing WAL directory "pg_xlog/archive_status"
cp: cannot stat `/var/lib/pgsql/9.2/wal/00000002.history': No such file or directory
LOG:  entering standby mode
cp: cannot stat `/var/lib/pgsql/9.2/wal/0000000200000031000000B4': No such file or directory
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 31/B47BFAC0
LOG:  consistent recovery state reached at 31/B73624A0
LOG:  database system is ready to accept read only connections

I am concerned about the missing WAL files. Can anyone confirm that, as long as it reaches a consistent state, the hot standby contains all the data of the master?

Everything else I check indicates that it's ok; for example, running psql -x -c "select * from pg_stat_replication;" on the master looks good, and adding a new record on the master replicates. I just want to be sure that there won't be anything missing from the slave.

I think this is normal and expected if your restore_command is set to something like this example:

restore_command = 'cp /mnt/server/archivedir/%f "%p"'

The manual says that:

  At startup, the standby begins by restoring all WAL available in the
  archive location, calling restore_command. Once it reaches the end of
  WAL available there and restore_command fails, it tries to restore any
  WAL available in the pg_xlog directory. If that fails, and streaming
  replication has been configured, the standby tries to connect to the
  primary server and start streaming WAL from the last valid record
  found in archive or pg_xlog. If that fails or streaming replication is
  not configured, or if the connection is later disconnected, the
  standby goes back to step 1 and tries to restore the file from the
  archive again. This loop of retries from the archive, pg_xlog, and via
  streaming replication goes on until the server is stopped or failover
  is triggered by a trigger file.

So you can expect to see exactly one restore_command failure when you start your standby, because PostgreSQL will keep calling it (with incrementing log file names/numbers) until it fails once.

Then it will connect to the primary and start streaming as described above, and as you saw in your logs:

LOG:  streaming replication successfully connected to primary

The slave is not guaranteed to be exactly up-to-date with the master, because it could be disconnected from the master for example. In particular, this line:

LOG:  consistent recovery state reached at 31/B73624A0

does not mean that "the hot standby contains all the data of the master". However, if you see it followed by this line, as you did:

LOG:  database system is ready to accept read only connections

then the database is "ready enough" to start functioning as a read-only standby, as the manual says:

  It may take some time for Hot Standby connections to be allowed,
  because the server will not accept connections until it has completed
  sufficient recovery to provide a consistent state against which
  queries can run. During this period, clients that attempt to connect
  will be refused with an error message.

In my case, I saw consistent recovery state reached not followed by database system is ready to accept read only connections. This turned out to be a problem with an embedded scripting language plugin (plpython2) having a system-wide startup script (sitecustomize.py) which did bad things to the PostgreSQL process (enabling faulthandler and installing a signal handler for SIGUSR2) which caused it to never enter hot standby mode.


Delete/remove orphaned users and their schema from server/database I'm new to the role of being a SQL Server DBA for SQL Server 2008 R2.

I need help on how to find out orphaned users in SQL Server and how to delete/remove the orphaned users and their corresponding schema from the database.

Please provide a step by step solution.

To find out the orphan users of a database say "ABC", you need to query below on that database:i.e

USE ABC

sp_change_users_login @Action='Report'

This will list out all the orphan database users for that database. In order to resolve the orphan users, use sp_change_users_login, with query below

USE ABC

sp_change_users_login
    @Action='update_one',
    @UserNamePattern='database_user_name_comes_here',
    @LoginName='login_name_comes_here'

Also, suggested reads: How To Avoid Orphaned Database Users with SQL Server Authentication and Fixing Orphan users


Partitioning on a single filegroup I have some very large tables in my database, but a substantial chunk of this data is "old".

Due to circumstances beyond my control, I am not allowed to remove this "old" data.  The other limitation is that I cannot modify the database, meaning add filegroups to it.  The way things stand right now, everything resides on the PRIMARY file group.

I was thinking to partition these tables into a few partitions, such as "new", "old", "archived" and similar. I do have a "status" column I would like to use for this purpose.

Given the described scenario and limitations, I was wondering if partitioning makes any sense here.  In other words, if my table is partitioned in this way, but all of the partitions sit on the same filegroup, will SQL Server be smart enough to find that special area in the underlying file where my "new" data resides and not touch the area with "old" data?

To put it differently, if, let's say, 80% of my data is "old".  Does SQL Server have a mechanism to avoid accessing 100% of the underlying files and access only 20% which contains "new" data (assuming, of course, I specify my partitioning column in the WHERE clause of the queries).

I guess to answer this, one would need to understand how the partitioning is implemented internally. I appreciate any pointers.

There are two advantages to partitioning a table in the same filegroup:

Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance.  Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
Leveraging partition elimination and (possibly) partition level locking to improve query maintenance.  I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
To avoid performance issues, you should align your partitions.  This means all your indexes should include your partition key, whether as an include or as part of the index itself.
Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012).  If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it.  Kendra Little has an excellent list of resources where you can get started.


Joining 2 tables based on Dynamic criteria Hoping you can help me. I have 2 Tables (Table PS & Table A).

What i am trying is the following ;

Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following conditions ACCOUNT_CODE__C and  I or T or C.

For example :

In the scenario below for Table A for ACCOUNT_NUMBER = '1234' the Id_Type = 'I', therefore, i want it to to search the PS table for ACCOUNT_CODE__C '1234' and against column I

If in future scenario below the for table A for ACCOUNT_NUMBER = '1234' and has Id_Type of = 'T', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column T

If in future scenario below the for table A for ACCOUNT_NUMBER  = '1234' the Id_Type = 'C', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column C

There may be a scenario where Table A ACCOUNT_NUMBER = '1234' the Id_Type could have a mixture of "I", "T" or "C".

Where it matches then provide the join. Where it doesn't match enter a new line item and populate the value a

In the expected outcome below, you can see that the following Id ("LU0336376248 & LU0430493568") from the PS table are not present in Table A. We can also see that "LU0513029156" inst in present in Table PS.

Table PS & Data

create table #PS
(
ACCOUNT_CODE__C nvarchar(80),
ID nvarchar(80),
I nvarchar(80),
T nvarchar(80),
C nvarchar(80)
);

INSERT INTO #PS
VALUES
('1234', 'a1vf1000001icrBAAQ','LU0430492750', 'TESTCAEU LX', 'L5784E347'),
('1234', 'a1vf1000001icrCAAQ','LU1162086109', 'TESTGIHA LX', ''),
('1234', 'a1vf1000001icrDAAQ','LU0336376248', 'TESTGEUD LX','L57838408'),
('1234', 'a1vf1000001icrEAAQ','LU0430493568', 'TESTGBCH LX', 'L5784E420');

Table PS Result :

select * from #ps

ACCOUNT_CODE__C ID  I   T   C
1234    a1vf1000001icrBAAQ  LU0430492750    TESTCAEU LX L5784E347
1234    a1vf1000001icrCAAQ  LU1162086109    TESTGIHA LX
1234    a1vf1000001icrDAAQ  LU0336376248    TESTGEUD LX L57838408
1234    a1vf1000001icrEAAQ  LU0430493568    TESTGBCH LX L5784E420

Table A & Data

create table #a
(
ACCOUNT_NUMBER nvarchar(80),
ID nvarchar(80),
Id_Type nvarchar(80)
);

INSERT INTO #a
VALUES
('1234', 'LU0430492750', 'I' ),
('1234', 'LU1162086109', 'I' ),
('1234', 'LU0513029156', 'I' );

Table A Result :

select * from #a

ACCOUNT_NUMBER  ID  Id_Type
1234    LU0430492750    I
1234    LU1162086109    I
1234    LU0513029156    I

Expect Outcome When tables Joined

ACCOUNT_NUMBER  ID        Table PS   Table A
1234    LU0430492750       Y           Y
1234    LU1162086109       Y           Y
1234    LU0336376248       Y           N
1234    LU0430493568       Y           N
1234    LU0513029156       N           Y

Looking forward to your help & support

Joining (or the expression in SELECT) looks similar to:

FROM #A
FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C
                   AND (    (#A.ID = #PS.I AND INSTR(#A.Id_Type, 'I') > 0)
                         OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0)
                         OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > 0) )

The rest is trivial.


How do I totally remove SQL Server 2017 on Ubuntu? The docs has instructions about how to remove SQL Server.  However, this leaves certain packages behind.  How can I remove all traces and uninstall SQL Server 2017?

These are actually very poor instructions on how to remove the Microsoft SQL Server.

SQL Server installs these associated packages.

mssql-server
mssql-server-fts
mssql-tools
msodbcsql
unixodbc-dev

Assuming you wish to totally remove SQL Server, you should

# Stop the service
systemctl stop mssql-server.service

sudo add-apt-repository --remove "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)"

# Remove those packages from the system.
sudo apt-get --purge remove mssql-server mssql-server-fts mssql-tools msodbcsql unixodbc-dev

# Delete the SQL Server repositories
sudo rm /etc/apt/sources.list.d/mssql-* /etc/apt/sources.list.d/msprod*

# Remove the Microsoft keys.
apt-key del "BC52 8686 B50D 79E3 39D3  721C EB3E 94AD BE12 29CF"

# Delete the Microsoft created directories
sudo rm -rf /var/opt/mssql/ /opt/{microsoft,mssql-tools,mssql}/ /usr/share/doc/{msodbcsql,mssql-server,mssql-tools}


THROW not including name of calling procedure unless custom error-message is specified I am experiencing some behavior with THROW that I can't understand. Consider the following stored procedure:

CREATE PROCEDURE usp_division_err AS
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0')
END TRY
BEGIN CATCH
    THROW;
END CATCH

When the procedure is executed, the following error is raised:

  Msg 8134, Level 16, State 1, Line 1
  Divide by zero error encountered.

Note, no information about in which procedure the error was raised is included. That's because the erroneous, dynamic SQL is executed in another scope, and that is fine. However, change the CATCH-block to look like this

BEGIN CATCH
    THROW 50000, 'An error occurred.', 1;
END CATCH

and the execution of the procedure will raise this error instead:

  Msg 50000, Level 16, State 1, Procedure usp_division_err, Line 7 [Batch Start Line 0]
  An error occurred.

The error is still encountered while executing the dynamic SQL, but when I manually specify the error number and error message (the first and second parameter of THROW), the procedure-name of the executing procedure somehow appears.

Why does the procedure-name appear in the second error-message but not the first?

Code the CATCH like this:

BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH

That allows you to pass all the parameters into the THROW statement so it can send them back to the caller.

I tested that like this:

USE tempdb;
IF OBJECT_ID('dbo.usp_division_err', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_division_err;
GO
CREATE PROCEDURE dbo.usp_division_err AS
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0');
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH
GO
EXEC dbo.usp_division_err;

The results:

  Msg 58134, Level 16, State 1, Procedure dbo.usp_division_err, Line 10 [Batch Start Line 16]
  Divide by zero error encountered.

To obtain the native error number you just need to subtract 50000 from the reported error number.  Since the parameters for THROW are optional, there must be two code-paths inside THROW, one that reports the procedure name, and one that doesn't.  I'll leave it to the reader to decide which code-path does that.


connection String and Listener I am using SQL server 2017, I will like to know how Connection String can be use to talk via Listener to Databases rather talking directly to the DB

Availability Group Listener has a DNS name, for example AG-Listener-01.domain.com

In the connection string, you just specify

Server=AG-Listener-01,1433;Initial Catalog=YourDatabase;User ID=SQLLogin;Password=SQLPassword;
MultiSubnetFailover=True


Fan trap and redundant relationships issues Here're a few domain rule resulting the current design of the relations. A project application form can have only one company responsible supervisor, and one company where the project is conducted. A company can have multiple supervisors, and be applied within many project application form. However, a company record together with the company supervisor record are only first created when there's an application on them. The system shall be able to look up company name and find company information, associated supervisor information, and previous project information in the application form. It shall also show a project's company, supervisor when someone process the project application form.

From the design, I can see one of the relationship is redundant and there's fan trap. How can I resolve these without breaking the domain rules.


Oracle and TimesTen - Can you bind it? I have some questions, that I like to know to my MSc research.

Lets consider following situation:
I have two small tables A and B.
And two big tables C and D.
I want to put A and B in TimesTen and C and D (because they are to big) in standard Oracle database.
Can I configure system that way, that queries to A and B will be RAM-only queries and queries for C and D will be standard database queries?
I want this to work transparent to user: user makes query and don't know, if he is querying TimesTen or standard Oracle.
If above is possible: What about queries in which user joins A and C? Will A be read from memory or from disk?

I don't need to know how to do it. Only if its possible.

For this you use pass through. The client connects to the TT instance and TT passes the query through to the Oracle rdbms, when needed. Why not just add a few more nodes to the grid and also cache the bigger tables?


SQL Server Secure and Unsecure Traffic Configuration Help I have a SQL Server 2017 instance that I have configured to listen on multiple ports 1433 and 1234. I would like to know if its possible to have one port (1234) accept only secure connections while the default port accept both?
I have already set up the instance, verified both ports are working, and installed the certificate but it seems my only option is to force SSL for the entire instance in config manager. Am I missing something? All help is appreciated.

Am I missing something?

No. "Force Encryption" is an instance-wide setting.


Does UPDATE INDEXES work for local indexes? I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):

CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
    PARTITION p1 VALUES(1),
    PARTITION p2 VALUES(2)
);
CREATE INDEX table1_index on table1(col2) LOCAL;
INSERT INTO table1 VALUES(1, 1);
INSERT INTO table1 VALUES(2, 1);

Second table is similar but without partitions and index:

CREATE TABLE table2(col1 INT, col2 INT);
INSERT INTO table2 VALUES(1, 2);

When I exchange one of partition from table1 with table2 then index for this partition will be unusable. To avoid this I can use UPDATE INDEXES, so I do:

ALTER TABLE table1 EXCHANGE PARTITION p1
WITH TABLE table2 WITH VALIDATION UPDATE INDEXES;

But UPDATE INDEXES seem to doesn't work, because when I check status of index for this partition it's unusable.

SELECT partition_name, status FROM user_ind_partitions
WHERE index_name = 'TABLE1_INDEX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
P2                             USABLE

For GLOBAL index it works as expected.

The question is: does UPDATE INDEXES work for LOCAL indexes when using EXCHANGE PARTITION?

From the Maintaining Partitions documentation:

You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).
[...]
Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated.

I believe you'll need to create a matching index on table2 and use both INCLUDING INDEXES and UPDATE INDEXES (but don't have a way of testing it right now).


Getting Values of the two rows before the selected row POSTRESQL I want to update an row according to the values of two rows before the row how can i do that?

Let's assume my table looks like this

ID         amount

1           1
2           3
7           5

And i want to do this

UPDATE amount
FROM table
SET amount = (AmounToFRowOneBefore + AmounToFRowTwoBefore)/2
WHERE ID=7;

Expected output would be

 ID         amount

    1           1
    2           3
    7           2

And if I do this I don't know anything about the two rows before so no ID or anything is there a query to select the values before the selected row?

You can use either a window function:

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;

Or use a subquery that returns the 2 previous rows:

update t
set amount = (select sum(t3.amount) / 2
             from (select amount
                   from t t2
                   where t2.id < t.id
                   order by id desc
                   limit 2) t3)
where
    t.id = 3;

Check them for performance.

select * from t;

id |             amount
-: | -----------------:
 1 |                  1
 2 |                  3
 3 | 2.0000000000000000

db<>fiddle here


How is Activity Monitor Pulling processes specific memory information? How is SQL Server Management Studio's Activity Monitor pulling the following memory by processes information?

The screen I am talking about is...
Log into the database, right click on the server in the tree on the left and select activity monitor.
Expand out processes.
The Memory Use column - where is it coming? Is there an internal database table that it is pulling from?

I ask because I am trying to figure what all goes into this metric so I can determine if one of the processes is growing without bound or growing as expected due to design.

The Memory Use column - where is it coming?

It corresponds to memory_usage column in sys.dm_exec_sessions.

As a friendly note, I would suggest not to use Activity Monitor.

Use  Adam Machanic‘s free procedure, sp_WhoIsActive (uses DMVs). You can even log into a table for post analysis.

Kendra Little talks about Activity monitor limitations :

It groups wait types
Leaves out wait types
Dont leave it open ... else it will boil down your server :-)


Arithmetic overflow error for type varchar This is my query:

insert into [Consolidated_LoB].[dbo].[consold]
    ([shp_to_pstl_cd]
      ,[LoB]
      ,[catlg_nbr]
      ,[shpd_qty])
   select
      [POSTAL_CODE]
      ,[LoB]
      ,[PART]
      ,[SHIPPED_QUANTITY]
   from
      [ITB_Pank].[dbo].[ITB_consoldtd]

and the message I receive is:

  Arithmetic overflow error for type varchar, value = 436112652.000000.

Please advise

CREATE TABLE [dbo].[consold]
(
    [srce_loc] [char](3) NULL,
    [q2c_nbr] [char](8) NULL,
    [ln_itm] [char](3) NULL,
    [mfg_loc] [char](3) NULL,
    [shpd_qty] [int] NULL,
    [shpg_loc] [char](3) NULL,
    [inv_prof_loc] [char](3) NULL,
    [shp_to_pstl_cd] [char](10) NULL,
    [shpmnt_dt] [datetime] NULL,
    [shpmnt_typ_ind] [char](2) NULL,
    [tot_typ] [char](2) NULL,
    [catlg_nbr] [char](34) NULL,
    [LoB] [char](10) NULL
    [del_id] [varchar](12) NULL
)

CREATE TABLE [dbo].[ITB_consoldtd]
(
    [POSTAL_CODE] [float] NULL,
    [LoB] [char](10) NULL,
    [DELIVERY_ID] [float] NULL,
    [PART] [nvarchar](255) NULL,
    [SHIPPED_QUANTITY] [float] NULL
)

To simplify the mapping exercise, this is source to target

- [POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
- [LoB]              [char](10)      -> [LoB]            [char](10)
- [PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
- [SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]

An "Arithmetic overflow" occurs when you are converting data from one data type, ConvertFrom to a different type, ConvertTo and the datatype of ConvertTo cannot store the data coming from ConvertFrom

i.e. ConvertFrom INT has a value of 234,567 which you are assigning to ConverTo TINYINT.

As TINYINT only allows values 0 to 255 you get an "Arithmetic overflow" error.

In your example above you have multiple type conversions that may result in an "Arithmetic overflow" error i.e.

[POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
[PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
[SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]

In the case of POSTAL_CODE, this will only work if the size of the FLOAT field contains no more then 10 digits including the decimal point where as the FLOAT fields supports up to a 53 digit number.

In the case of PART this will only work if the there are 34 characters or less.

In the case of SHIPPED_QUANTITY its a bit more complicated for instance 123456 will convert as will 123456.789 but the data will convert as 123456 loosing the digits after the decimal point which could be critical.

You need to review your table and use common data types for storing common data.


How to add the aliases to the group by clause In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause.

I am struggling for the past few hours.

SELECT ieor.aper07_req_k                                  AS reqno,
       ford.apeh09_supervisor_cds_d                       AS ll6CdsID,
       'Ford Offboard Req'                                AS reqtype,
       par_val1.aper17_parameter_val_x                    AS requeststatus,
       (SELECT Min(iti2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req iti2
        WHERE  ieor.aper07_req_k = iti2.aper07_req_k
               AND iti2.aper06_req_status_k = 4)          AS assignedTOiti,
       (SELECT Min(hlp2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req hlp2
        WHERE  ieor.aper07_req_k = hlp2.aper07_req_k
               AND hlp2.aper06_req_status_k = 5)          AS assignedTOasstAdm,
       (SELECT Min(asst2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req asst2
        WHERE  ieor.aper07_req_k = asst2.aper07_req_k
               AND asst2.aper06_req_status_k = 12)        AS assignedTOhlp,
       (SELECT Min(clsd2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req clsd2
        WHERE  ieor.aper07_req_k = clsd2.aper07_req_k
               AND clsd2.aper06_req_status_k IN ( 3, 9 )) AS closedDate,
       ieor.aper07_assigned_to_cds_d,
       ieor.aper07_comment_x,
       ieor.aper07_last_updt_user_c,
       ieor.aper07_last_updt_s                            AS last,
       ieor.apeh09_employee_k,
       ieor.aper06_req_status_k,
       ipw.apeh04_first_n,
       ipw.apeh04_cds_d,
       ipw.apeh04_last_n,
       ipw.apeh04_person_type_c,
       irs.aper06_req_status_x,
       offr.aper15_offboard_reason_x                      AS offboardReason,
       Min(aper07_req_created_s)                          AS creationDate
FROM   iaper07_employee_offbrd_req ieor,
       iapeh05_person_vw ipw,
       iapeh09_ford_employee ford,
       iaper06_req_status irs,
       iaper15_offbrd_req_reason offr,
       iaper17_parameter_values par_val1
WHERE  ford.apeh09_cds_d = ipw.apeh04_cds_d
       AND ieor.apeh09_employee_k = ipw.apeh04_visitor_k
       AND irs.aper06_req_status_k = ieor.aper06_req_status_k
       AND offr.aper15_offboard_reason_k = ieor.aper15_offboard_reason_k
       AND ieor.aper06_req_status_k = par_val1.aper17_parameter_val_r
       AND ipw.apeh22_region_k = 1
       AND ieor.aper07_last_updt_s = (SELECT Max(aper07_last_updt_s)
                                      FROM   iaper07_employee_offbrd_req b
                                      WHERE  ieor.aper07_req_k = b.aper07_req_k)
       AND ieor.aper07_req_k = 3387
GROUP  BY ieor.aper07_req_k,
          ford.apeh09_supervisor_cds_d,
          ieor.aper07_assigned_to_cds_d,
          ieor.aper07_comment_x,
          ieor.aper07_last_updt_user_c,
          ieor.aper07_last_updt_s,
          ieor.apeh09_employee_k,
          ieor.aper06_req_status_k,
          ipw.apeh04_first_n,
          ipw.apeh04_cds_d,
          ipw.apeh04_last_n,
          ipw.apeh04_person_type_c,
          irs.aper06_req_status_x,
          par_val1.aper17_parameter_val_x,
          offr.aper15_offboard_reason_x
HAVING ipw.apeh04_person_type_c NOT IN( 'c', 'C' )

Using the with clause will help keep your queries readable:

with <some_meaningful_name> as (
  select
    <complex_expression1> as <alias1>,
    <complex_expression2> as <alias2>,
    <other_columns>
  from
    ...
)
select
  <some_columns>,
  <aggregate_expression>
from
  some_meaningful_name
group by
  <some_columns>;


Moving MySQL database from UniServer to Ubuntu Server? I have set up Ubuntu Server 11.10, and plan to host my website on it. Until this point I was using UniServer to host it in a portable fashion. Using the built in MySQL. This is fine, and through testing, I have gathered a decent amount of info in my database that I really don't want to have to re-enter.

The problem is; that I cant find any help online to install the existing databases into MySQL on the Ubuntu side of things. I can Export the existing databases from UniServer, no problem. But once I've copied them over to Ubuntu, how do I get MySQL to read them.

For anyone that doesn't know, Ubuntu Server is a Command Line Interface OS. While I do have a Debian Graphical User Interface installed (accidentally), it doesn't allow much to be done graphically, other then file system search, copy, cut, paste, etc. Terminal application, and a minimalist web browser.

Any help that can anyone can give here is greatly appreciated. I am a Linux newbie, so simplified jargon is also appreciated, but I am very eager to learn!

The simplest option is to just back up everything in the filesystem and carry it over.

To find out where your data is, look at the datadir variable with in mysql

test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /var/lib/mysql/   |
+---------------+-------------------+
1 row in set (0.00 sec)

Note Your actual value may differ.

Then from your shell shut down mysql, back it up and copy it over.  Something like:

> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *

Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown


How did my colleague get his own schema? I have a stored procedure that:

checks if a table exists and if so, drops it.
creates that table again
then fills that table with about 30 queries.

When I (DB Owner) run this proc, everything works as intended. When my co-worker does so, who has DROP/CREATE rights on this database through a role in the Active Directory, several things go wrong. What boggles my mind is this:

The creation of the table did not have the dbo schema explicitly specified before the name. This resulted in a table called domain\cowork_id.table_name_here table to be created. Besides that table being created in his personal schema, he now also has that schema on the database (it didn't exsist prior to running the proc).

What happened? Why does SQL Server create tables in user's schema's instead of in dbo when that isn't specified?

As a rule, you should explicitely specify dbo schema if you want to create the object in this schema.

As you are db_owner, your default schema is dbo, so there is no problem when you don't specify dbo schema while creating objects. But for other (Windows) users it's not the same.

Your users are members of Windows group that has no default schema. In this case the corresponding user and the schema are created when the user creates any object, it's documented here: CREATE SCHEMA (Transact-SQL)

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user
  account (a database principal in the database). This can happen in the
  following situations:

  A login has CONTROL SERVER privileges.

  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a
  member of a Windows group which has a database user account (a
  database principal for the Windows group).

  When a user without a database user account creates an object without
  specifying an existing schema, a database principal and default schema
  will be automatically created in the database for that user. The
  created database principal and schema will have the same name as the
  name that user used when connecting to SQL Server (the SQL Server
  authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows
  groups to create and own objects. However it can result in the
  unintentional creation of schemas and users. To avoid implicitly
  creating users and schemas, whenever possible explicitly create
  database principals and assign a default schema. Or explicitly state
  an existing schema when creating objects in a database, using two or
  three-part object names.

To resolve the issue just assign dbo schema as default schema to all yor users-Windows groups or write the schema explicitely when creating objects. Always.


Postgres plpgsql - Using a variable inside of a dynamic create statement Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:

 ...
 DECLARE
    tblVar varchar := "myTable";
 BEGIN
 EXECUTE 'CREATE TABLE $1 (
             foo integer NOT NULL,
             bar varchar NOT NULL)'
 USING _tblVar;
 ...

However, I continue to receive the error message

  ERROR:  syntax error at or near "$1"

If I don't use the $1 token and, instead, write the string myTable it works just fine.

Is there a limitation on using dynamic statements for CREATE calls?

In addition to what @filiprem wrote, here is how you do this properly:

...
DECLARE
   tbl_var text := 'myTable';   -- I would not use mixed case names ..
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tbl_var) || '(
   foo integer NOT NULL,
   bar text NOT NULL)';
...

Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.


Unable to CREATE DATABASE: error while processing file dmanage.bsq I want to create a DATABASE on my recently installed Oracle 12c so, after creating spfile and listener/tnsnames/sqlnet.ora for my instance, I call my CREATE DATABASE request :

CREATE DATABASE DBNAME
   USER SYS    identified by password
   USER SYSTEM identified by password
     MAXINSTANCES  1
     MAXLOGHISTORY 1
     MAXLOGFILES   20
     MAXLOGMEMBERS 4
     MAXDATAFILES  200
   CONTROLFILE REUSE
   DATAFILE
           '/[...]/system_01.dbf' SIZE 1000M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE
           '/[...]/sysaux_01.dbf' SIZE 2000M REUSE
   UNDO TABLESPACE "UNDO" DATAFILE
           '/[...]/undo_01.dbf'     SIZE 2000M REUSE
   DEFAULT TEMPORARY TABLESPACE "TMP" TEMPFILE
           '/[...]/tmp_01.dbf'       SIZE 2000M REUSE
            EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1M
   LOGFILE
       GROUP 1 ('/[...]/redo_1a.dbf',
                '/[...]/redo_1b.dbf') SIZE 100M REUSE,
       GROUP 2 ('/[...]/redo_2a.dbf',
                '/[...]/redo_2b.dbf') SIZE 100M REUSE,
   CHARACTER SET WE8ISO8859P15
   NATIONAL CHARACTER SET AL16UTF16 ;

And I get this error:

CREATE DATABASE DBNAME
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dmanage.bsq' near line 22
ORA-00604: error occurred at recursive SQL level 1
ORA-19502: write error on file "/[...]/sysaux_01.dbf", block
number 640 (block size=8192)
ORA-27085: Message 27085 not found;  product=RDBMS; facility=ORA
Additional information: 1044480
Additional information: 1048576
Process ID: 23040
Session ID: 244 Serial number: 9021

The request at the line 22 of the dmanage.bsq file is:

CREATE TABLESPACE sysaux DATAFILE "D_SXFN"
  "D_SDSG" ONLINE FORCE LOGGING
/

What is the problem with my request?

According to this thread, this error means I don't have enough space on my disk. I try the same request with lower size for my datafile and it works.

So, I have two solutions: adapt, in my request, the size of my datafiles or extend my logical volume.


Best way to store images in SQL Server 2014 I am working as database developer (intern), and I have to find out the best way to store image files in SQL Server 2014. Could anyone suggest the best solution?

I have found that if the file size is less than 256KB, it's better to store it in the database; if it exceeds 1MB then it's better to store in filestream, and just store the path in the database.

My research is still on to find the way which is better in terms of performance, security. If you have any idea please share.

I've dealt with this very issue for a number of years at the hospital where I work. For us, it really doesn't come down to size of the individual file so much, but overall size of ALL the files stored. What's disappointed me most about storing images in a database is that customer's poor forecasting for future growth. We have systems where SQL Server image databases are small but have terabytes of image data on a file share. And we have images stored in SQL where the overall size of the database is contained--like thumbnail images for a finite set of employees.

What you've mentioned with 256K versus1 MB has merit in a pure effectiveness and efficiency (in a technical vacuum apart from reality) but I've found that SQL works well enough even if you depart from this model. Even if the customer would tell you that they'd spare no expense for disk storage for SQL Server, it will cost you greatly if you're tasked with backing up databases that are terabytes in size.

For me it comes down to how much do you trust your customer or vendor of the software. Regardless of what you choose--only store the images within SQL if you:

A. Really trust your customer/software vendor

B. Can ensure a limited/finite scope of both images and size of the image data

C. If the trust level is low yet the customer/management insists on storing images in SQL, ensure they have their own SQL Server and pay continually for disk increases for databases and backups-- to isolate the explosion as much as possible

That said, as  DBA I really like the idea of being able to store images if I should need to. Technically, there is not really an overreaching "best" answer. And quite often the "trust" just isn't there for me to believe that the customer/vendor knows what they're talking about enough to store images on SQL--but occasionally they do.


How to guess or recover a timestamp conversion in sqllite? I am in the middle of tiny forensic effort =)
I am trying to transfer transactions from Money IQ (IOS) to My Wallet+ (IOS) as both apps use sqlite. Money IQ uses a standard unixepoch timestamp. However, I got troubles with My Wallet+. I have matched actual dates with timestamps of sample transactions:

actual date | app timestamp
2013-09-03 => 399924000
2013-10-26 => 404424000
2013-11-04 => 405201600
2013-11-04 => 405201600
2013-11-10 => 405720000

My objective is convert '%Y-%m-%d %H:%S' to this format. I think the app does not store the time part of a timestamp thinking that transactions occurred at midnight. My best guesses are H1 for ts to date conversion and H2 for date to ts conversion (inaccurate by 14400):

H1: strftime('%Y-%m-%d %H:%S', datetime(zdate, 'unixepoch'), '31 years', 'localtime')
H2: strftime('%s', '2013-11-13', '-31 years')

Is there anything I'm missing or the H2 is the most appropriate way to convert? Thank you!

UPD: I have expanded dataset:

timestamp | unix epoch diff     | manual note
---------------------------------------------------
321829200 | 2001-01-01 03:00:00 | 2011-03-15
347054400 | 2001-01-01 04:00:00 | 2012-01-01
378676800 | 2001-01-01 04:00:00 | 2013-01-01
395092800 | 2001-01-01 04:00:00 | 2013-07-10
399924000 | 2000-12-31 06:00:00 | 2013-09-03
400881600 | 2001-01-01 04:00:00 | 2013-09-15
404424000 | 2001-01-01 04:00:00 | 2013-10-26
405201600 | 2001-01-01 04:00:00 | 2013-11-04
405201600 | 2001-01-01 04:00:00 | 2013-11-04
405720000 | 2001-01-01 20:53:00 | 2013-11-10 16:53
406411200 | 2001-01-01 16:00:00 | 2013-11-18 12:00

The Unix epoch timestamp is defined as the number of seconds since the beginning of 1970 (in UTC).

These timestamps are measured from the beginning of 2001, and appear to use the respective local time zone:

> select datetime(strftime('%s', '2013-11-10') - 405720000, 'unixepoch');
2001-01-01 04:00:00
> select strftime('%s', '2013-11-10') - strftime('%s', '2001-01-01 04:00:00');
405720000

To correct for the random time zone differences, you should round to the nearest midnight.


Choosing the right field data type in SQL Server 2008 I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters that are retrieved from a terminal screen.

There will be about 50 million rows at first, and initially, we estimated the field will required <= 7,500 characters, so I opted to use the VARCHAR(8000) datatype. After more analysis, we determined that probably about 90% of the 30 million rows will be <= 8000, but the other 10% will be <= 15,000.

I obviously can't use the VARCHAR due to its 8,000 limit, so I assumed my only option is the text datatype. I'm concerned the overhead of 'text' and that it may be removed eventually. I'm now looking at VARCHAR(max), but I've never used it in a production environment.

Should I use varchar(max), text, or look for a way to compress that 10% of data and store it in a varchar(8000)?

You should use varchar(max). The text data type "will be removed in a future version of Microsoft SQL Server."

Related on SO Using varchar(MAX) vs TEXT on SQL Server

  The basic difference is that a TEXT type will always store the data in
  a blob whereas the VARCHAR(MAX) type will attempt to store the data
  directly in the row unless it exceeds the 8k limitation and at that
  point it stores it in a blob.


Convert hexadecimal to varchar I have a column with values saved as hexadecimal.

When I run this query:

select column from  table
where column like '%22639935-KCN%';

This is the output I get:

0x24000000008B010000000089FF32323633393933352D4B434E000000

I would like to turn the output into a varchar. This should give me a value resembling what is in the like statement.

My attempts so far have been:

SELECT CONVERT(VARCHAR(MAX),
       0x24000000008B010000000089FF32323633393933352D4B434E000000)

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),
       '0x24000000008B010000000089FF32323633393933352D4B434E000000', 2))

However, I get this outcome:

$

How can I convert this hexadecimal value into a varchar?

It looks like your varbinary string got injected with some nonsense. All of the sequences of two 00 values are null characters, so this is why your string is terminating upon conversion. The first character is 0x24 (dollar sign), which is why the output is just a dollar sign.

SELECT CONVERT(varchar(60), 0x2400....anything....);
-- is equivalent to:
SELECT CONVERT(varchar(60), 0x24);

Now, if I take your string and strip out all of the 00 sequences:

SELECT CONVERT(VARCHAR(60), 0x248B0189FF32323633393933352D4B434E);

I get something close; again, there is a bunch of garbage in there, but the string you're after is in there:

$‹‰ÿ22639935-KCN

You can ignore the leading garbage by just taking the original value and performing RIGHT() against it, but this assumes that the important part of the string is always the same length (we can't tell you if that's true).

SELECT CONVERT(VARCHAR(60),
       RIGHT(0x24000000008B010000000089FF32323633393933352D4B434E000000, 15));

Or using SUBSTRING, but this assumes that the garbage at the beginning of the string is always the same length:

SELECT CONVERT(VARCHAR(60),
       SUBSTRING(0x24000000008B010000000089FF32323633393933352D4B434E000000, 14, 46));

We also can't possibly tell you why that garbage is there and if it has any additional meaning. You'll need to find out how the values got encoded this way in the first place. The value you wanted encoded, 22639935-KCN, should have looked a little differently as varbinary:

SELECT CONVERT(VARBINARY(32), '22639935-KCN');

--------------------------
0x32323633393933352D4B434E

So, again, you will need to do some research to find out why this value wasn't encoded this way. We can't answer all of this because we didn't design your system or store those values.


Is it possible to setup MySQL replication through ProxySQL? We currently have replication working through haproxy so that if a node goes down replication continues with one of the other nodes. We're trying to completely replace haproxy with proxysql. Due to this I'm trying to replicate this functionality in proxysql.

Test:
3 on-prem nodes setup as a Percona Galera cluster.
1 on-prem ProxySQL node pointing to the Percona cluster
1 aws EC2 node with Percona installed.

I setup replication between the EC2 node, and one of the 3 on-prem nodes without issue. I can also connect to the DB through proxysql from the aws node.

When ever I stop the slave, and run the following:

CHANGE MASTER TO
         MASTER_HOST="XX.XXX.XXX.193",
         MASTER_USER="[username]",
         MASTER_PASSWORD="[password]",
         MASTER_AUTO_POSITION = 1,
         MASTER_PORT = 6033;

I get the following error when I run show slave status:

Slave_IO_State: Waiting to reconnect after a failed registration on master
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 1597
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Lost connection to MySQL server during query (Errno: 2013)

I can then swap back to directly connecting between DB nodes and replication works fine. Is the mysql backend of proxysql preventing this from working in the same way it does with haproxy?

This sounds really bad.  Replication and Galera clustering will (I think) get confused if a slave/node is suddenly talking to a different master/node.

Sure, use HAProxy or proxysql for connecting clients to slaves/nodes.  And have rerouting happen.  That's their purpose.


Primary key in resulting relation from union operation in relational algebra I have a question regarding the resulting table/relation from a union operation in relational algebra. Does the resulting relation not have a primary key? Or does it return a table with a hidden primary key?

Example: Suppose we take the union of the following two tables:

R1:
id | name
----------
1    a
2    b
3    c

R2:
id | name
----------
1    b
2    c
3    d

Where the attribute "id" is primary key in both relation R1 and R2, now the union of those two will be:

id | name
------------
1     a
1     b
2     b
2     c
3     c
3     d

Now none of the attributes in the resulting relation can be primary key since both id and name contain duplicates. How is this resolved by the DBMS?

By definition the union of two relation contains no duplicates. So it contains at least one candidate key, the composition of all attributes. In your example (id, name) is a candidate key.

DBMS do not "resolve" the issue by assigning a primary key because in almost all DBMS (at least those that use SQL), the union of two tables is a derived table (or a view or a CTE but not a base table) and primary keys(1) can be defined only for base tables (not for views, not for derived tables and not for common table expressions).

Still, the DBMS optimizer is often aware that the derived table that results from a union has no duplicates and that the composition of all attributes uniquely defines a row and may use this fact in subsequent operations on the table (e.g. in a more complex query where the union is joined to or unioned with other tables).

(1) : and constraints in general. There is an generalized SQL constraint construction (CREATE ASSERTION) that allows constraints across several tables that could be used for creating a unique constraint on a union but assertions have not been implemented by any major DBMS - probably because it's a very complicated issue. Firebird claims to have them but not sure how well or what the restrictions are.


Mysql Adding date/time stamp to file produced, or other method of automated file naming to keep resulting files unique SELECT
'"Part #"',
'"Manu"',
'"Qty"',
'"partdesc"',
'"Rohs status"'
UNION all
(
SELECT partno, manuf, qtyleft, partdesc, stfifo.ROHScompliance
    FROM stmaster, stfifo
    WHERE stmaster.internal = stfifo.internal AND stfifo.warehouse = 'MAIN'

AND manuf != 'aaaa'
AND manuf <>'bbbb'
AND manuf <>'cccc'

AND stmaster.partno <>'yyyyyyyyy'
AND stmaster.partno <>'xxxxxxxx'
AND QTY >0
GROUP BY partno, manuf
ORDER BY partno ASC
INTO OUTFILE
    'D:/Reporting/abc - 19.03.15.xls'
    FIELDS TERMINATED BY '  ' ENCLOSED BY '' LINES TERMINATED BY ''

How do I make it so that the resulting file includes today's date as determined by a NOW() function? CONCAT() functions seem useless as the file is within a UNION all() function. At the moment I cannot run this query more than once without deleting the .xls file first. Any other workarounds are greatly welcomed. Thanks in advance

You may need to use shell script for this.
First write a script to do so.. a sample is as below
!/bin/bash

dto=date +%d'_'%b'_'%Y'_'%H_%M_%S
mysql -uroot -p dbname -e " select * from table_name into outfile 'file1_$dto' "

This will always give you unique names against your files.
Hope it helps.


UUID array declared in stored procedure, on access throws explicit type casts needed I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception.

create or replace function test.test_function(max integer, interval interval) returns SETOF test.test_table
    language plpgsql
as
$$
DECLARE
    min TIMESTAMP;
    max TIMESTAMP;
    array_of_guids uuid[];
BEGIN
    array_of_guids = ARRAY(SELECT guid FROM test.test_table WHERE guid NOT IN (SELECT guid FROM test.ignore_test));
    ...

END
$$;

The guid is of type UUID, but at this line, I get an error which says:

[2019-09-27 12:12:48] Where: PL/pgSQL function test.test_function(integer,interval) line 10 at assignment```

Unable to figure out the syntax to fix the above problem.


Can database objects be made case insensitive while keeping strings case sensitive? In Microsoft SQL Server (2014) it is possible to choose between a case insensitive and case sensitive collation.

My reason for using a case-sensitive collation would be to make "test" = "TEST" return false.

Yet, what I would like to preserve is that when a table "TEST" exists, writing a query like select * from test; would still work. When the database has a case-sensitive collation it doesn't, because I would need to write it like select * from TEST;

Is there a way to set "object collation" and "string collation" separably?

Mostly "yes", depending on how "automagic" you want everything to work.

First: Database identifier Collation (i.e. object names, column names, index names, etc) is taken from the Database's default Collation. That will determine the Collation of columns such as sys.objects.name, etc. So to do this, you can create or alter the Database to have a case-Insensitive Collation, such as Latin1_General_100_CI_AS_SC. This will allow you to have a table TEST that will be valid in queries such as select * from test;.

Server-scoped identifier Collation (e.g. Database names) is handled by the Instance's default Collation.

Second: If a Database's default Collation is case-Insensitive, then expressions that are entirely string literals and/or variables and/or output-parameters and/or UDF return values will be treated as case-Insensitive. For these expressions you would need to override that default behavior, per expression, using the COLLATE clause.

For example:

/* take database default collation behavior */
IF ('test' = 'TEST')
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- ? result depends on the default collation of the "current" database ?

/* force case-sensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CS_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Sensitive

/* force case-insensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CI_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Insensitive

Expressions between string columns and either string literals or variables will use the Collation of the string column, and if the Collation for the column is case-sensitive, then the expression will be evaluated as case-sensitive without needing to use the COLLATE clause.

By default, newly created columns that do not specify the COLLATE clause will inherit the Database's default Collation. In this case, you will need to specify something like COLLATE Latin1_General_100_CS_AS_KS_WS_SC for all VARCHAR / NVARCHAR columns that you want to be sensitive across all 4 categories (plus support Supplementary Characters).

PLEASE NOTE: Be very cautious about using a binary Collation (i.e. Latin1_General_100_BIN2) to get an accent/case/kana/width sensitive Collation; binary Collations are not truly case-sensitive.


Single MySQL query with row averages based on conditions I have problem, where i know how to proceed with multiple queries, but dont really now

I Have a table with

  BP {
  name: varchar
  systolic : int
  diastolic : int
  timestamp: date
  }

I need to get the Names from BP table that satisfy the following ANY of the following conditions.

If  systolic > 180 OR diastolic > 110
If  (systolic  >= 140 AND systolic < 180), then take the next 2 readings of systolic, get the average and if that average is >= 140. Then Condition Satisfied.
Same diastolic. If  (diastolic  >= 90 AND systolic < 110), then take the next 2 readings of diastolic, get the average and if that average is >= 90. Then Condition Satisfied.

There will be more many rows with the same unique name.  (meaning many BP Recordings per person).

I can certainly get this working if i jus get all the values and parse it myself in PHP.

But Im wondering if theres a better way to do it directly in MySQL.

My goal here was to find the people that have Hypertension. And those conditions above the guidelines in categorising a person as Hypertensive.

In this example, I simplified the table. And I want to get the name(s) of those that meet these hypertensive guidelines.

Condition 1 is very simple to address. Condition 2 & 3 are whats troubling me.

What I do now

I just get all those values using SELECT and ORDER BY timestamp ASC, name. And parse everything in PHP
I simply get the list of people with systolic >=140 AND systolic < 180, Filter them out based on name, order it by timestamps and enumerate through each set, if I find a systolic >= 140 AND systolic < 180, i break enumeration and check the next 2 readings of that name and take the average.
I repeat that with diastolic. and Merge the results with all the three conditions, removing any duplicate entries.

I really want to learn if there was a good way to do this in sql itself. Im assuming using a Stored Procedure would be a must. But I'd be glad to know if there was a better way using some kind of subquerying.

Keep the raw data in MySQL.  But plan on coding the logic in PHP (or whatever).  You have only begun to touch on how complex the math can get.  What about the blood pressure readings, plus whether the patient is/was taking a BP lowering drug?


MariaDB event to automatically move data older than a specific time to another table without temporary table? I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance.
So far I've only come across answers, that are based on a WHERE clause that is used for both INSERT INTO SELECT and the DELETE statement. Since time passes between both statements, the WHERE clause could theoretically have different results and thus lead to data loss.
This is my event:
DELIMITER $$
CREATE EVENT IF NOT EXISTS archive_6_month_old_entries
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
begin
START TRANSACTION;

CREATE TEMPORARY TABLE data_to_move
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

INSERT INTO api_log_archive
SELECT * FROM data_to_move;

DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

COMMIT;
end $$
DELIMITER ;

Is there any way to do this without a temporary table?

You say:

but we have the issue that, if in theory, the insert query takes up to
5 minutes, there will be a difference in the result between the INSERT
INTO and the DELETE statement, leading to 5 minutes of lost data.

No, you don't have to risk losing data! That's what TRANSACTIONs are for!
InnoDB uses (Multi-Version Concurrency Control - MVCC). You should read up on this important topic in order to better exploit the capabilities of your server.
Something like this will work:
SET TRANSACTION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;

  INSERT INTO api_log_archive
    SELECT *
    FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT 5000, it's not necessary

  DELETE FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT

  COMMIT;

SET TRANSACTION LEVEL READ COMMITTED;  -- or whatever your default is

Put this into an EVENT and schedule it to run every day at (say) 03:30 every night.
Once you are happy that your event is working regularly at the time you require, then all you'll have to do is make monitoring it part of your daily monitoring routine.
Isolation levels
A brief explanation of the difference between the READ COMMITTED and the SERIALIZABLE transaction isolation levels follows:
The default TRANSACTION ISOLATION LEVEL on MariaDB is READ COMMITTED.
So, in terminal one, I do the following
Terminal_1_> SHOW TRANSACTION ISOLATION LEVEL;

Result:
 transaction_isolation
-----------------------
 read committed
(1 row)

That's what we want - so now we issue this command:
Terminal_1_> SELECT * FROM t;

Result:
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

That's as we want it - so, now we delve into transactions as follows:
Terminal_1_> BEGIN TRANSACTION;
BEGIN
Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Here, we "suspend" the transaction by switching to another terminal 2...
We run this query:
SELECT
       pid,
       age(clock_timestamp(), query_start),
       usename, query
FROM pg_stat_activity
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result (active queries which are not the one above):
 pid  |       age       | usename  |      query
------+-----------------+----------+------------------
 3500 | 00:05:46.113281 | postgres | SELECT * FROM t;
(1 row)

So, we can see that my SELECT * FROM t; query is still active and is over 5 minutes old.
So, now, again in terminal 2, we insert a row into the table:
INSERT INTO t VALUES (3, 'Three');

And (still terminal 2), I check:
Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

And I go back to terminal 1 and issue a SELECT * FROM t; and the result is:
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now, to see the behaviour with TRANSACTION ISOLATION LEVEL set to SERIALIZABLE.
We issue the command:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

I'm using a local PostgreSQL instance as that's the only thing I have running and running these sorts of commands isn't possible using a fiddle - the equivalents for MariaDB are here - I leave it up to you to construct the required tests with MariaDB.
So, from termina1, we check our settings:
SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE ('default%transaction%o%');

Result:
             name              |   setting    |                          short_desc
-------------------------------+--------------+---------------------------------------------------------------
 default_transaction_isolation | serializable | Sets the transaction isolation level of each new transaction.
 default_transaction_read_only | off          | Sets the default read-only status of new transactions.
(2 rows)

So, we're good to go. From terminal_1_> (SERIALIZABLE) we run;
BEGIN TRANSACTION;
SELECT * FROM t;

Result:
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Now, from terminal_2_> we run our check on active queries again:
SELECT
       pid,
       age(clock_timestamp(), query_start),
       usename, query
FROM pg_stat_activity
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result:
  pid  |       age       | usename  |      query
-------+-----------------+----------+------------------
 11064 | 00:03:37.694149 | postgres | SELECT * FROM t;
(1 row)

The query in terminal_1_> is sitting there and is now > 3mins old...
From terminal_2_> again, we update the table:
INSERT INTO t VALUES (3, 'Three');

and:
Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now we go back to terminal_1_> and run SELECT * FROM t; a second time within the same serializable transaction.
and we get:
Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

The SERIALIZABLE transaction has the same view of table t that it had at the beginning of transaction, despite terminal_2_> having INSERTed a new record...
I would urge you to experiment with differing setups to get to know your server's capabilities and how you can best exploit them...
In terms of your problem, what this means is that:
Scenario 1 (you keep the defaults on your server - i.e. REPEATABLE READ):
At time t1, you run this:
CREATE TEMPORARY TABLE data_to_move
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

Say, for example, you SELECT 10 records. It's a log table, so it's constantly being updated...
At time t2, you INSERT into your archive table.
INSERT INTO api_log_archive
SELECT * FROM data_to_move;

At time t3, you DELETE from your api_log
DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

Now, your process will miss any records that have aged to over 6 months between t1 and t3 - so, fine, you say, I'll pick them up the next time...
Scenario 2 (your run your archiving process with the TRANSACTION ISOLATION LEVEL set to SERIALIZABLE).
For starters, you can cut out the CREATE TEMPORARY TABLE phase as follows:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE        -- check out MariaDB syntax for this

BEGIN TRANSACTION;

INSERT INTO api_log_archive
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

DELETE FROM api_log
WHERE id IN
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

COMMIT;

-- clear up - set your transaction defaults back to your preferred default...

The advantage of this is you don't have the overhead of creating a potentially large TEMPORARY TABLE, you reduce the number of steps from 3 to 2 and your system will be more robust.

There is another possibility - you can lock your tables for the duration of your update - however, this means that no writes can occur to the api_log table during the time of your transaction - it's generally considered not to be good practice if it can be avoided. If you wish to explore this possibility, then you might like to ask another question...

This fiddle should help clarify things - PostgreSQL has the useful (and intelligently explicit) functions TRANSACTION_TIMESTAMP and CLOCK_TIMESTAMP - with READ COMMITTED any query sees the data as of CLOCK_TIMESTAMP and a SERIALIZABLE transaction sees the data as of TRANSACTION TIMESTAMP!


What MariaDB storage engine should i use? I have a database with size about 170GB with rows between 20.000 - 700.000.000 rows (depending on the table). Currently i'm using MyISAM storage engine.

In my case, querying (READ) speed is very important, but transactional is optional.

My CRUD Operation estimates is :

98% READ
2% INSERT
< 1% for both UPDATE & DELETE

Your only real choice here is between InnoDB and Percona's XtraDB engines - see this page.

Coincidentally, I wrote an answer to Change all table ENGINE at once from "MyISAM" to "InnoDB" in MySql with single query this morning on how to convert from MyISAM to InnoDB which might be of interest to you? There are very good reasons for not going with anything non-transactional!

  Any particular reason you recommend InnoDB/XtraDB since MariaDB blog recommend Aria instead?

Yes - for their transactional capabilities - you get DRI (Declarative Referential Integrity) thrown in. Enforcing DRI app-side is a disaster waiting to happen - whereas if you use the RDBMS, you have a system that's been tested by literally millions of people. If you "roll-your-own", you have to rewrite it every time you want to write a new app for that particular schema - and if your data is important, this will happen! I would urge you to use either InnoDB or XtraDB but if you do insist on an NTE, then go with Aria! -  Vérace


What is the best approach to run a DELETE query on 92K of 4 million rows? Options that have occurred to me:

One big delete statement. For example:

delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')

Same as above, but chunk in smaller pieces, 10k at a time?
Create a stored procedure so all I'm sending are the the two query values.

92K out of 4M is still only 2.3% of your records. I'd suggest your first option, just throw what you want deleted at the database and let the database sort it out (if you give it to the database all at once, chances are it'll do it more efficiently than if you parcel it out). The only time I'd recommend chunking it is if your application is time-critical and you're worried about slowing it down (in which case the other solution is to do it off-hours).

If you were taking 20-30% or more of the records out of the table, I'd recommend dropping indexes, doing the delete, and recreating indexes, followed by an OPTIMIZE TABLE, but for 2% of the records in the table the index rebuild overhead isn't worth it (it's faster just to keep the indexes up-to-date while deleting the records).

That said, after you've done the delete, running OPTIMIZE TABLE might not be a bad idea - don't do that during peak hours though.


sp_executesql: what can be parameterized Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.

For example, predicates can, table names cannot.  Number of rows in TOP can:

exec sp_executesql @stmt =
    N'SELECT TOP(@n) a, b
    FROM (VALUES(1,2)) v(a,b)',
    @params = N'@n int',
    @n = 10

MAXDOP cannot:

exec sp_executesql @stmt =
    N'SELECT TOP(10) a, b
    FROM (VALUES(1,2)) v(a,b)
    OPTION (MAXDOP @n)',
    @params = N'@n int',
    @n = 10

  Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '@n'.

I suppose there are many more examples of what works and what doesn't.  I'm looking for an authoritative list to eliminate trial and error.

Background

The reason why these sometimes don't work isn't specific to sp_executesql, but rather to how variables work in SQL Server. Your command wouldn't work if you used a local variable and no sp_executesql for MAXOP either.

Per the documentation:

  Variables can be used only in expressions, not in place of object names or keywords.

And expressions are defined as:

  ...a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

To someone very familiar with SQL Server the above will make sense and probably not require further explanation, but I can see how this definition may be lacking. Especially since the TOP usage doesn't really look like an expression (even though the value supplied for it is one).

Something easier

It might be a fool's errand to simplify the above, but I'll give it a shot.

A loose guide that is more descriptive and helpful might be:

  Parameterized values are valid when they affect the results of the
  query.

That is to say that the following would be valid, because they are affecting the results (which rows, how many rows, what value is returned, etc.) but the query is still doing the same thing essentially. That is because they are being used in expressions which modify the query results but do not change the query.

SELECT * FROM Table WHERE Col = @n;
SELECT TOP(@N) * FROM Table;
SET @var = @n;

Whereas these are not valid because they are changing the context of the query, engine settings, or resulting in a different query altogether, etc. Generally changing any of these areas is done through a custom procedure, keyword, function, or by changing objects used in the query and thus do not use an expression.

SELECT * FROM Table OPTION (MAXDOP @n);
USE @n; --Change db context
SELECT * FROM @n;


Stats for usage of columns in queries I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used in where clause and how many times ?

This specific information is not fully stored in SQL Server.

You do however have a few options to make a small analysis.

You can check the index_usage stats ( SYS.DM_DB_INDEX_OPERATIONAL_STATS) and cross reference this to the columns included in the index. This will, however, not give you exactly which columns are used or selected or used in a where clause. But you can derive a lot of information here to check for more optimal indexes.
You can use the following query from the exec_query_stats

 SELECT
     OBJECT_NAME(qt.objectid)
   , qs.execution_count AS [Execution Count]
   , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
   , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
   , qs.total_worker_time AS [TotalWorkerTime]
   , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
   , qs.max_logical_reads
   , qs.max_logical_writes
   , qs.total_physical_reads
   , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
   , qt.text
 FROM
     sys.dm_exec_query_stats AS qs
 CROSS APPLY
     sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

This will give you the execution count of queries on you SQL server (not all, only cached). Here you can go through the query text field and cross reference to your tables but this will be a lengthy process. You can also check the execution stats for stored procedures & then cross reference this to the columns in the stored procedures, but this will again be a tough process.

Use a third party tool which captures most of your queries and query through your queries (this will be slow and a lot of data).


Storing Passport Numbers in a database We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database.

Is there any security best practice for storing passport numbers in a database? Should it be encrypted?

Passport numbers should definitely be encrypted in the database.  You should investigate the legality of collecting and storing this information before proceeding.


Can Oracle Text query return the terms found? I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'people OR country ', 1) > 0;

Is there a way to see what terms in the "contains" statement that Oracle found in the documents?

You might want to look into the MARKUP or HIGHLIGHT functions of the CTX_DOC package. An example of how MARKUP can be used for your query (this assumes your index is called idx_news and you have an ID column):

declare
  MARKLOB   clob;
  MARK1     number;
  MARK2     number;

  cursor NEWS_SEARCH is
    select * from NEWS;
begin
  for REC in NEWS_SEARCH loop
    CTX_DOC.MARKUP('idx_news', to_char(REC.ID), 'people OR country', MARKLOB);
    MARK1 := instr(MARKLOB, '<<<');
    MARK2 := instr(MARKLOB, '>>>');

    if MARK1 + MARK2 <> 0 then
      dbms_output.
       PUT_LINE(
        REC.ID || ',' || substr(MARKLOB, MARK1 + 3, MARK2 - MARK1 - 3));
    end if;
  end loop;
end;
/

What it does is mark up your text (surround the found items with <<<>>>) and outputs that new text to a clob which is then searched for the markup. All text that is found in your query will be marked up but my PL/SQL will only display the first result; it can be adapted to find them all and display only the unique ones. This is a simple example and perhaps can become too complicated for your original query but it's here to point the way.

Alternatively, a simple SELECT may suffice if all you have is an OR in your text query:

select decode(sign(SCORE(1))
             ,1, decode(sign(SCORE(2)), 1, 'both', 'people')
             ,'country')
         as WHICH
      ,TITLE
  from NEWS A
 where CONTAINS(TITLE, 'people', 1) > 0 or CONTAINS(TITLE, 'country', 2) > 0;

This checks the score for individual searches on 'people' and 'country' and works out which ones were positive, returning 'both' if both search terms were found.


Can I surveillance the backup taken in SQL Server? I have a SQL Server and I want to put some surveillance on it, to know if someone is taking a backup. I know that I can make an audit to monitoring the backup. But I want to have some kind of trigger every time someone takes a backup, so I can send an email to myself about it. Is it possible? (To get an email every time a backup is taken.)

You can create a server level event notification for the Audit Backup/Restore Event Class event. In processing the event you can do whatever you wish, including sending yourself email. See Event Notifications and CREATE EVENT NOTIFICATION. Event notifications can be used to trigger activated procedures.


PostgreSQL function to get levels (hierarchy) I need a function which returns description of level from cascade (hierarchy) tables.

CREATE TABLE level_1(
 id_1 SERIAL,
 description_1 text
);

CREATE TABLE level_2(
 id_2 SERIAL,
 description_2 text
);

CREATE TABLE level_3(
 id_3 int4 SERIAL,
 description_3 text
);

CREATE TABLE levels(
 id_levels int4 SERIAL,
 id_1 int4,
 id_2 int4,
 id_3 int4
);

I need this function:

SELECT f_get_level('Level 1', 10);

'Level 1' ..  which level I want
10 .. value of id_levels

With this result:

description_1

The table levels is cross table between the others. Example:
Level 1 - Site
Level 2 - Sectors in Site
Level 3 - Lines in Sector

Levels: Level 1 - Level 2 - Level 3

id1  - Site (S1) - Sector (NULL) - Line (NULL)
id3  - Site (S1) - Sector (Sec1) - Line (NULL)
id4  - Site (S1) - Sector (Sec1) - Line (L1)
id5  - Site (S1) - Sector (Sec1) - Line (L2)
id6  - Site (S1) - Sector (Sec1) - Line (L3)
id7  - Site (S1) - Sector (Sec2) - Line (NULL)
id8  - Site (S1) - Sector (Sec2) - Line (L1)
id9  - Site (S1) - Sector (Sec2) - Line (L2)
id10 - Site (S2) - Sector (NULL) - Line (NULL)
id11 - Site (S2) - Sector (Sec1) - Line (NULL)

I need a function where parameters are id and Level.
Examples:

Select f_get_level('Sector', 5)  -- where 5 is id5

Result: 'Sec1'

Select f_get_level('Site', 11)   -- where 11 is id11

Result: 'S2'

Select f_get_level('Line', 6)    -- where 6 is id6

Result: 'L3'

Select f_get_level('Sector', 6)   -- where 6 is id6

Result: 'Sec1'

One simple way would be correlated subqueries in a CASE expression of a single query:

CREATE OR REPLACE FUNCTION f_get_level(_level text, _id_levels int)
  RETURNS text AS
$func$
SELECT CASE _level
          WHEN 'Level 1' THEN (SELECT description_1 FROM level_1 WHERE id_1 = l.id_1)
          WHEN 'Level 2' THEN (SELECT description_2 FROM level_2 WHERE id_2 = l.id_2)
          WHEN 'Level 3' THEN (SELECT description_3 FROM level_3 WHERE id_3 = l.id_3)
          ELSE 'Unexpected input for _level!'
       END
FROM   levels  l
WHERE  l.id_levels = _id_levels
$func$  LANGUAGE sql STABLE;

Call (exactly as requested):

SELECT f_get_level('Level 1', 10);

Another option would be a PL/pgSQL function with separate CASE (not SQL CASE!) branches and an OUT parameter ...

CREATE OR REPLACE FUNCTION f_get_level2(_level text, _id_levels int, OUT _descr text) AS
$func$
BEGIN
   CASE _level
   WHEN 'Level 1' THEN
      SELECT description_1
      FROM   levels  l
      JOIN   level_1 l1 USING (id_1)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 2' THEN
      SELECT description_2
      FROM   levels  l
      JOIN   level_2 l2 USING (id_2)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 3' THEN
      SELECT description_3
      FROM   levels  l
      JOIN   level_3 l3 USING (id_3)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   ELSE
      RAISE EXCEPTION 'Unexpected input for _level: %!', _level;
   END CASE;
END
$func$  LANGUAGE plpgsql STABLE;


Mysql get records more then 3 in interval of 1 minute, return group_concat ID Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.

CREATE TABLE test
(
  `id` bigint NOT NULL AUTO_INCREMENT,
  created_date TIMESTAMP(1) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UC_PRE_CHAT_TALKID_COL` (`id`)
);

✓

INSERT INTO test VALUES
(1,'2021-01-18 11:02:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(2,'2021-01-18 11:02:35.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(3,'2021-01-18 11:02:03.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(4,'2021-01-18 11:11:28.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'),
(5,'2021-01-18 11:11:36.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'),
(6,'2021-01-18 11:11:05.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii');

✓

db<>fiddle here
something like this:
ids     origin_url              client_session_id
1,2,3   https://testett.com/    znkjoc3gfth2c3m0t1klii
4,5,6   https://rarara.com/     znkjoc3gfth2c3m0t1klii

Edit some context:
I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 seconds in the same url and client_session_id.
follows the SQL I'm running:
select
    count(session_id),
    client_session_id,
    GROUP_CONCAT(id) as talkIds,
    origin_url
from
    bot_talk
where
    created_date > now() - interval 60 second
group by
    client_session_id, origin_url
having
    count(session_id) >= 3

This query works as I expect, but sometimes my cron service is sometimes down, and I lose those repeated records.
I thought about making an SQL(Cron) at the end of the day to analyze the last 24 hours, and look for the records that are repeated according to the rule I mentioned above?

Here is the answer - see the fiddle. Another answer I wrote to a similar question may provide some clearer background and is a bit simpler - see here.
All I will say is that it gives some idea of the power of window functions.
I noticed in the comments that there was some debate about what constituted a group - in this example, I have constructed the SQL such that it picks out as a group where all subsequent sessions started within 180 seconds (i.e. 3 mins) of the first - you can change the 180 to 60 (or whatever) yourself.
I've added in some records for the purpose of testing and also added CONSTRAINTs to the table definition. It's always best to put as much as possible into the DDL - your database is your final bastion of defence for your data!
CREATE TABLE test
(
  id bigint NOT NULL,
  created_date TIMESTAMP(2) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL,
  CONSTRAINT test_id_pk PRIMARY KEY (id),
  CONSTRAINT test_cd_url_sess_id_uq UNIQUE (created_date, origin_url, client_session_id)
);

Always use named constraints - these provide more meaningful messages than: ... failed... CONSTRAINT xyz_000_43abc has been violated....
I populated it as follows:
INSERT INTO test VALUES

--
-- 1 lone record...
--

(1,'2021-01-18 10:30:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'), -- XX

--
-- 4 records within 180 seconds of the first one
--

(2,'2021-01-18 11:02:24.0', 'https://zes.com/', 'znkjoc'),
(3,'2021-01-18 11:02:35.0', 'https://zes.com/', 'znkjoc'),
(4,'2021-01-18 11:03:03.0', 'https://zes.com/', 'znkjoc'),  -- **
(5,'2021-01-18 11:04:15.0', 'https://zes.com/', 'znkjoc'),  -- YY

--
-- 3 records within 180s of the first one
--

(6,'2021-01-18 11:49:28.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'),
(7,'2021-01-18 11:49:48.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'), -- **
(8,'2021-01-18 11:50:13.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'), -- **

-- 1 lone record

(9,'2021-01-18 12:57:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),  -- XX

(10,'2021-02-18 09:02:24.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(11,'2021-02-18 09:02:35.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(12,'2021-02-18 09:03:03.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(13,'2021-02-18 09:04:15.0', 'https://rar.com/', 'znkjoc3'); -- ZZ

-- -- XX - Added record > 1 minute from next or previous.
-- -- ** - Changed created_date to get groups within 180 seconds.
-- -- YY - Added record < 3 minutes from previous to give 4 records
-- -- ZZ - Added group of 4 records at the end.

It's always worth checking out for edge cases - single records at the beginning/end of your dataset + groups that you want to capture at the beginning and end also! I leave it to you to do more exhaustive testing!
I'll give the results first:
rn  st  sids                URL:                Session id: Session start time:            Session end time:
1   2   2,3,4,5             https://zes.com/    znkjoc      2021-01-18 11:02:24.00  2021-01-18 11:04:15.00
2   5   10,11,12,13         https://rar.com/    znkjoc3     2021-02-18 09:02:24.00  2021-02-18 09:04:15.00

There's a bonus - you've got the starts and ends of the multiple close-together sessions thrown in for free!
I do have one word of advice - you really shouldn't be using or gathering data as comma-separated lists - SQL wasn't designed for string manipulation and extracting meaningful information and knowledge from such lists is painful - better to have a single atomic datum in a single field - see 1st Normal Form!
I've left in the various "sub-fiddles" that I used to arrive at the final result - hopefully they'll help you to learn about window functions &c... My own preference for the results would be in this format (see the fiddle  - with one record/session - you can prune as you see fit):
sid created_date    st  min_cd  max_cd  f_ts    l_ts    c_ts_asc    c_ts_desc   o_url   c_sess_id
1   2021-01-18 10:30:24.00  1   2021-01-18 10:30:24.00  2021-01-18 10:30:24.00  1   1   1   1   https://zendes.com/ znkjoc3gfth2c3m0t1klii
2   2021-01-18 11:02:24.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  1   4   1   4   https://zes.com/    znkjoc
3   2021-01-18 11:02:35.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  2   3   2   3   https://zes.com/    znkjoc
4   2021-01-18 11:03:03.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  3   2   3   2   https://zes.com/    znkjoc
5   2021-01-18 11:04:15.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  4   1   4   1   https://zes.com/    znkjoc
6   2021-01-18 11:49:28.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  1   3   1   3   https://rararar.com/    znkjoc3gfth2c3m0t1klii
7   2021-01-18 11:49:48.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  2   2   2   2   https://rararar.com/    znkjoc3gfth2c3m0t1klii
8   2021-01-18 11:50:13.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  3   1   3   1   https://rararar.com/    znkjoc3gfth2c3m0t1klii
9   2021-01-18 12:57:24.00  4   2021-01-18 12:57:24.00  2021-01-18 12:57:24.00  1   1   1   1   https://zendes.com/ znkjoc3gfth2c3m0t1klii
10  2021-02-18 09:02:24.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  1   4   1   4   https://rar.com/    znkjoc3
11  2021-02-18 09:02:35.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  2   3   2   3   https://rar.com/    znkjoc3
12  2021-02-18 09:03:03.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  3   2   3   2   https://rar.com/    znkjoc3
13  2021-02-18 09:04:15.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  4   1   4   1   https://rar.com/    znkjoc3

Or another way might be (again, see the fiddle):
sid Session no. Start of sessions   End of sessions Session count   o_url   c_sess_id
2   2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  4   https://zes.com/    znkjoc
10  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  4   https://rar.com/    znkjoc3

You have the starts and ends and the number of sessions... anyway, that's up to you. Take a look at this bit (easily missed):
MIN(created_date) OVER (PARTITION BY st ORDER BY created_date ASC) AS min_cd,
MAX(created_date) OVER (PARTITION BY st ORDER BY created_date DESC) AS max_cd,

This allows you to have an ASCending and a DESCedning list.
This means that you can then do this:
WHERE (v.f_ts + v.l_ts) >= 5

And the records where this  corresponds must be the ones of interest for the bunched sessions.
So now, the SQL, here it is (drum roll, trumpets sound in the distance...)! Go get a cup of coffee to read it - it's a monster (needs pruning - but that's an exercise for the OP and anybody else who's got this far):
SELECT
  ROW_NUMBER() OVER () AS rn,
  v.st,
  GROUP_CONCAT(v.sid SEPARATOR ',') as sids,
  o_url AS "URL:",
  c_sess_id AS "Session id:",
  min_cd AS "Session start time:",
  max_cd AS "Session end time:"
FROM
(
  SELECT
    sid,
    created_date, st,
    MIN(created_date) OVER (PARTITION BY st ORDER BY created_date ASC) AS min_cd,
    MAX(created_date) OVER (PARTITION BY st ORDER BY created_date DESC) AS max_cd,
    ROW_NUMBER() OVER (PARTITION BY st ORDER BY created_date ASC)  AS f_ts,
    ROW_NUMBER() OVER (PARTITION BY st ORDER BY created_date DESC) AS l_ts,
    COUNT(st) OVER (PARTITION BY st ORDER BY created_date ASC)  AS c_ts_asc,
    COUNT(st) OVER (PARTITION BY st ORDER BY created_date DESC) AS c_ts_desc,
    o_url,
    c_sess_id
  FROM
  (
    SELECT
      sid,
      created_date, YYY,
      SUM(testy) OVER (PARTITION BY testy ORDER BY created_date ASC) AS s,
      ROW_NUMBER()
        OVER (PARTITION BY testy ORDER BY created_date ASC) AS rn,
      SUM(testy) OVER y AS st,
      FIRST_VALUE(created_date) OVER (PARTITION BY testy ORDER BY created_date) AS fv,
      LAST_VALUE(created_date) OVER y AS lv,
      o_url,
      c_sess_id
    FROM
    (
      SELECT
        id AS sid,
        LAG(created_date, 1) OVER x AS lag_1,
        created_date,
        LEAD(created_date, 1) OVER x AS lead_1,

        UNIX_TIMESTAMP(LEAD(created_date, 1) OVER x) - UNIX_TIMESTAMP(created_date) AS XXX,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) AS YYY,

        --
        -- The IMPORTANT one!
        --

        CASE
          WHEN (UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) > 180
          OR   UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) IS NULL)
          THEN 1
          ELSE 0
        END AS testy,

        FIRST_VALUE(created_date) OVER x AS f_val,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(FIRST_VALUE(created_date) OVER x) AS c_f_diff,

        ABS(TIMESTAMPDIFF(MINUTE, created_date, LEAD(created_date, 1) OVER x)) AS min_diff,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LEAD(created_date, 1) OVER x) AS ut_d,

        origin_url AS o_url,
        client_session_id AS c_sess_id

      FROM test
      WINDOW x AS (PARTITION BY origin_url, client_session_id
                     ORDER BY created_date ASC, origin_url, client_session_id)
      ORDER BY created_date ASC
    ) AS t
    WINDOW y AS (ORDER BY created_date ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                                    AND CURRENT ROW)
    ORDER BY created_date ASC
  ) AS u
  ORDER BY created_date ASC
) AS v
WHERE (v.f_ts + v.l_ts) >= 5
GROUP BY v.st, o_url, c_sess_id, min_cd, max_cd;


How to repair corruption for "Possible chain linkage problem." I have large table with 15 000 000 record and suddenly Select Top stopped working. I use MS SQL Management Studio.
This is not working, the query return 0 records:
SELECT TOP (10)
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]

To my surprise if I try to select one ID field it works percently.
Working fine:
SELECT TOP (10)
       [Id]
  FROM [ca-v2].[dbo].[Log]

Also if I add order by at the end the query starts working too:
SELECT TOP (10)
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]
  Order By [DateStamp]

I ran DBCC CHECKTABLE on the table, and received the following error:

Error: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1029578706, index ID 1, partition ID 72057594043367424, alloc unit ID 72057594045071360 (type In-row data). Page (1:5044) is missing a reference from previous page (1:5042). Possible chain linkage problem.

What can I do about this?
With the help of @RDFozz I was able to repair damaged index using sql
ALTER DATABASE "db" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

DBCC CheckTable ("log", REPAIR_REBUILD)

go

ALTER DATABASE "db" SET MULTI_USER WITH ROLLBACK IMMEDIATE

Two important thing here: 1) a database must be set to single user mode. 2) if database is in production and has other connections the SET user command must got with ROLLBACK IMMEDIATE to drop current connections.

When facing database corruption, you will need to determine the extent of the problem. Do you have one corrupt table/index, or many? You will probably want to run DBCC CHECKDB on the database.

Depending on what you find, you have the following options (in decreasing order of safety):

Restore from a backup

This is usually the simplest and safest solution. However, if you're not certain how long ago the corruption appeared, this may be impractical - it's possible that you have no recent backups without the corruption. And, depending on the usage of the database, throwing away a day's worth of data to fix the corruption might be severely costly, in terms of continuing to be able to run your business.

However, if the corruption is sever, this may be the only solution you have available. Even if you try some of the following options, you may need to come back to this one.

If you have backups, keep them handy, and don't cycle any out (if possible) until you've resolved the problem.
Use DBCC CHECKTABLE or DBCC CHECKDB with a repair option

Your run of DBCC CHECKTABLE and/or DBCC CHECKDB may have indicated a repair option you could use to try to restore things to normal.

The commands have the same repair options:

REPAIR_FAST - as it says, it tried some simple things, and will take less time than the other repair options. As you might guess, this also means it's the least likely to be successfully.
REPAIR_REBUILD - This does what REPAIR_FAST does, but is also willing to try rebuilding indexes from scratch - of the problem is in a non-clustered index, this should resolve it.
REPAIR_ALLOW_DATA_LOSS - As indicate, it will try to restore the affected objects to usability, but may have to eliminate partial rows and such to do so; you may lose some of your data. This is one of the reasons why restoring a backup is often the best solution.

Frankensteining your bad table (so to speak)

If only this table is affected, you can try to:

Locate a backup where the table is not corrupt (and stand up the backup, under a different name);
Drop the bad table;
Confirm that this hasn't moved your problem elsewhere by running DBCC CHECKDB again; and
Copy in the table and its data from the backup.

Or, to try to retain as much newer data as possible:

Get a backup ready, as above
Use REPAIR_ALLOW_DATA_LOSS on the table.
If the table is now working, locate rows in the backup that aren't in the repaired table, and copy them in.

As noted by CaM in the comments, you should use this as motivation to make sure you're performing regular maintenance tasks on your database.

First, if you aren't today, start backing it up regularly. Most of the databases I administer have a weekly full backup, and daily differential backups, so we should never lose more than a day's data.

In addition, you should run DBCC CHECKDB on a regular basis as well, to catch corruption before it becomes too serious to fix. This is also something that I try to run weekly.


Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday? In the "Effectively Troubleshooting Latency and Failover of Always On" by Sourabh Agarwal At PASS on Friday, November 3, 2017 3:30" about 30 minutes in he talks about how Synchronous mode commits are quicker, as the hardening is just about getting the t-logs to disk on the secondary.  The Redo is an async operation outside of the AlwaysOn commit process.

I may be missing something, but it seems like this "could" mean that the primary OLTP will not be impacted by long running (and blocking) queries on the secondary.  I envision that the logs just build in size on the secondary until the blocking report query completes, and then applies all the changes, that have occured on the primary and been hardened to disk on the secondary.

Related Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? In this question the answwer by Kin suggests the answer does not address 2016+

Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday?

For this question: Assume multiple disks, with data (MDF) and logs (LDF) on seperate drives.

I may be missing something, but it seems like this "could" mean that the primary OLTP will not be impacted by long running (and blocking) queries on the secondary.

There isn't a one sized fits all answer, so assuming that the reporting workload doesn't run the secondary replica out of CPU/Memory/Disk/Network resources and there isn't any contention for said resources then the primary should not be impacted but log growth will occur, which in the end could impact the performance of the AG/SQL.

  I envision that the logs just build in size on the secondary until the blocking report query completes, and then applies all the changes, that have occured on the primary and been hardened to disk on the secondary.

That's the gist of it, if you can't truncate and are growing log that may eat up CPU/Disk time that is wasted or cause an issue with the disk growing too much and eating all available space. Don't laugh (or do!), I have witnessed it multiple times - it really does happen.

The other item which isn't discussed is synchronization and redo. Assuming synchronization stays nominal (synchronized) the redo may fall behind. Yes, at some point it might "catch up" but the SLA was already missed. REDO queue build up is a thing in extremely high performant environments and even though SQL Server 2016+ has the potential for parallel redo it doesn't mean it'll always be faster or that the database is using parallel redo. I'm assuming we don't care about automatic failover or failover times (database startup times will be affected).

  Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday?

ASSUMING all of the previous, it acts no differently than 2012/2014 in terms of readable secondary replicas. Yes, there were enhancements but the real reason why there are performance implications on the primary is mostly due to a resource being run out on a secondary and causing long waits which show up in the primary as HADR_SYNC_COMMIT.