Removing duplicate-ish paths generated from recursive CTE I have some multi-parented data, that I run a recursive CTE against to find common ancestors:

item_id |item_name |path_name    |id_path_array |
--------|----------|-------------|--------------|
1       |D         |B->Two->D    |{9,4,1}       |
2       |E         |B->Two->E    |{9,4,2}       |
13      |W         |B->Two->D->W |{9,4,1,13}    |
14      |Y         |B->Two->D->Y |{9,4,1,14}    |
15      |X         |B->Two->E->X |{9,4,2,15}    |
16      |Z         |B->Two->E->Z |{9,4,2,16}    |

However, what I really want here is just the first two rows; I want to remove the rows that represent paths that have already been covered by a shorter path.  e.g. obviously if 'D' is a common ancestor then all of D's ancestors are too, so I don't want to include the paths for them.

I did think I had something with an array operator:

select * from common_ancestors q1
where exists (
    select 1 from common_ancestors q2
    where q1.id_path_array <@ q2.id_path_array and q1.item_id <> q2.item_id)

but even though it gives the right result here, I'm not convinced it's correct.  I would be happy to hear opinions or alternative solutions .

Desired result:

item_id | item_name | path_name | id_path_array
------: | :-------- | :-------- | :------------
      1 | D         | B->Two->D | {9,4,1}
      2 | E         | B->Two->E | {9,4,2}

db-fiddle here

You need NOT EXISTS and the contains operator reversed:

select
    q1.*
from
    common_ancestors q1
where not exists
    ( select 1
      from   common_ancestors q2
      where  q1.id_path_array @> q2.id_path_array
        and  q1.item_id <> q2.item_id
    ) ;

Test in dbfiddle.uk


SQL Server 2014 FCI Add Port Scenario:
Windows Server 2012R2 & SQL Server 2014 SP2, no CUs FCI adding a non-default port and keeping the default port

I made the change on both nodes via SQL Configuration Manager, however, neither failover or complete resource restart allows a connection to be made on the new port.  We have tested connection to the port with the application, telnet and PoSH (Test-NetConnection) to no avail.  The SysInternals PortMon utility does not show SQL Server listening on the added port.  I did confirm the port shows in the registry on both nodes.

Edit:
A huge detail I should have listed but forgot to.  When the service restarts, there is not indication that it is listening on the new port, just the default.

What did I miss or what else do I need to check?

Obviously, I cannot test any changes without some notice.

Thank you in advance!

You will also need to configure an additional TCP endpoint inside of SQL Server itself. If you run a query of

SELECT * FROM sys.tcp_endpoints;

I would expect you to see the default endpoint, as well as the Dedicated Admin endpoint. You will need to add another endpoint:

USE master;
GO
CREATE ENDPOINT [MyNewTCPEndpoint]
STATE = STARTED
AS TCP
   (LISTENER_PORT = YourTCPPort, LISTENER_IP =ALL)
FOR TSQL() ;
GO

And then you may need to re-grant access to the default endpoint

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

See the Microsoft documentation on configuring SQL for multiple TCP ports for full details.


OPENROWSET can't import an excel spreadsheet into SQL Server 2005 Express I am trying to run the following query to select data from an excel spreadsheet:

SELECT     FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES','select * from [sheet1$]');

SSMS is sending back the following error:

  Msg 7399, Level 16, State 1, Line 1 The OLE DB provider
  "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an
  error. The provider did not give any information about the error. Msg
  7303, Level 16, State 1, Line 1 Cannot initialize the data source
  object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
  "(null)".

ProcessMonitor is telling me this :

Date & Time: 21/11/2011 18:41:55
Event Class: File System Operation:
CreateFile Result: NOT A DIRECTORY
Path: D:\SQLFTP\LISTEFF3CFTP.xls
TID: 3496
Duration: 0.0000070
Desired Access: Execute/Traverse,Synchronize
Disposition: Open
Options: Directory, Synchronous IO Non-Alert
Attributes: n/a
ShareMode: Read, Write
AllocationSize: n/a
Impersonating: VM-CHALEUR-FIOU\Administrator

I have also installed the Office 2007 components and tried to use Microsoft.ACE.OLEDB.12.0 and got the same results from both SSMS and process monitor.

I am however able to import a CSV file from the same location so it is not a permissions issue.
I have tried running the same import from other SQL Server 2005 express servers and got the same result.
The Ad Hoc distributed queries option is set to 1 in sp_configure.

Anyone know what I'm doing wrong?

Ok well I found the solution or at least its one or both of the two

Instead of running express with the Network Service account, I created a regular user.  Then I gave that user rights to the folder with the excel spreadsheets.
I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer.  This time I restarted it.

This is the OPENROWSET SELECT statement I used

  SELECT
  FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_CP,FT_VILLE,FT_TEL,FT_FAXFROM
  OPENROWSET  ('Microsoft.Jet.OLEDB.4.0','Excel
  8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES',  'select * from [Requêtechauffage fioul$]');

So maybe it was a permissions problem?  Weird since I could import csv files.
Maybe it was just having to restart the computer after having installed AccessDatabaseEngine.exe.


Merge Join SSIS Throwing Error Despite Sort and Matching Meta I'm attempting to Merge Join two data sources. I ensured the metadata matched and that the sort key was present in the metadata, but it is still giving me the error:

  Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata."

All of the support articles and matching forum questions all mention metadata and sorting. I even type cast the right data set to match the left... Not sure what to do here. Flow and metadata are pictured. First is the left join, second is the right join. The last entry is the column I wish to join on in both metadata tables.

There is a setting where you can tell the Merge Join that the incoming information is sorted, although in the documentation, the Sort task is supposed to properly set these properties.  I wonder if it's because you have other tasks after the sort, and the IsSorted property is getting set back to False.

  There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:

  The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True.

    Important

    Setting the value of the IsSorted property to True does not sort the data. This property only provides a hint to downstream components that the data has been previously sorted.

  The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data.

  If you use a Sort transformation to sort the data, the Sort transformation sets both of these properties as required by the Merge or Merge Join transformation. That is, the Sort transformation sets the IsSorted property of its output to True, and sets the SortKeyPosition properties of its output columns.

  However, if you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.

The documentation tells you how to set those settings manually.


Almost sequential GUIDs vs random GUIDs? I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server.

In my webb-app I have many clients who will create items client side in javascript and save them only now and then.

The client code will create sequential GUIDs but items won't allways be saved straight away.

Am I loosing much of the benifit of using sequential GUIDs when the rows aren't saved in a perfect order?

Also, are there in the same way problems with occasionally mixing in normal random GUIDs?

Any GUID for an identity column will be an issue due to the size of it. This leaves less space in each page for actual data which means your data density is lowered and you are likely to see more page splits and fragmentation.

The problem when you use non sequential GUIDs on an id column with a clustered index is fragmentation. If you had 100 rows in the table with batches of 20 with sequential GUID values as the id and then wanted to add a random id value the engine may need to find space in the middle of your table to insert that record instead of just adding it to the end.

Depending on your fill factor level this could cause page splits which cause index fragmentation. Lower the fill factor of the index can help with fragmentation but not as much as always sequential values. This way each new record can be added to the newest page or a new page can be added at the end without the need to reorganize existing data.

Check here for more info.


Finding the default isolation level in SQL Server 2017 and Azure SQL DB I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that:

In on-premises SQL Server instance the default isolation level is Read-Committed based on locking

And the next sentence is:

The default in SQL Database is Read - committed snapshot based on row-versioning

My question is: what is the difference between "On-premises SQL Server instance" and "SQL Database" in these two sentences?

What is the default isolation level and how can I find it? Is there any special query to find out the default isolation level?

It's referring to Azure SQL Database which uses RCSI by default.

Isolation Level SQL
Database default database wide setting is to
enable read committed snapshot isolation (RCSI) by having both the
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options
set to ON, learn more about isolation levels here. You cannot
change the database default isolation level. However, you can control
the isolation level explicitly on a connection.

In on-premises SQL Server, the default isolation level is Read Committed.
Finding the "default" isolation level is a bit of a goose chase. It will depend on the database where a query is initiated from, and if any hints exist in the query to override a database level setting.
That said, you could query sys.databases to find out if RCSI or SI is enabled.
SELECT d.name,
       d.is_read_committed_snapshot_on,
       d.snapshot_isolation_state
FROM sys.databases AS d;

Note that the READ_COMMITTED_SNAPSHOT is not technically an isolation level. Rather, it is a database option that controls the implementation so that row versioning instead of locking is used to provide read consistency for sessions using the READ_COMMITTED isolation level.


Oracle standby databases monitoring I have many databases for which I have configured data guard setup. When it comes to monitoring the standby's, I use OEM to check the lag and I have enabled mail alerts as well as and when gap is more than 50 archives.

But the thing what I recently observed is that in one of my standby databases my recovery got stopped for one or more reasons and unfortunately lag also did not go above 50 so there was no alerts. In such a situation how I can ensure when my standby's recovery MRP itself is not active and I should be alerted in such a situation..

How is it possible? Any soultions?

There's a few ways to determine if MRP is running.  One is to query open_mode from v$database.  If MRP is running, you will get:

READ ONLY WITH APPLY

If not, you will get:

READ ONLY

You can run the following query which will show what MRP processes are running:

select process,status,sequence#
from v$managed_standby;

Look for the process MRPn

You could look in V$DATAGUARD_STATUS.MESSAGE for various things, including:

MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery Canceled

Another way is on the primary, to check

select recovery_mode from v$archive_dest_status where dest_id = n;

If it's IDLE, it's not doing anything.  With real-time apply enabled, it returns MANAGED REAL TIME APPLY.


Stale Statistics and View performance on SQL Server 2016 I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -

What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

.
SELECT  dim_isc.<cols>,
        dim_cr.<cols>,
        vw_cs.<cols>,
        --- **problem is here, guess** ---
        isnull(vw_cs.col_1,'  '),
        isnull(vw_cs.col_2,'  '),
        isnull(vw_cs.col_3,'  '),
        isnull(vw_cs.col_4,'  '),
        isnull(vw_cs.col_5,'  ')
FROM    dim_isc
        left join dim_ab on (dim_isc = dim_ab)
        join fact_cc on (fact_cc.cola = dim_isc.cola)
        join dim_cr on (fact_cc.colz = dim_cr.colz)
        join (
            select <cols> from dim_m join fcc
        ) rp on (fcs.coly = rp.coly)
        join vw_cs on (vs_cs.colx = dim_isc.colx)
WHERE
        rp = yyy -- input 1
        and dim_isc >= xxxx -- input 2

Few things to note, might help you advising further -

Removing the ISNULL from the SELECT columns helps resolve the query slowness. (I understand this could be stale statistics in the underlying view table(s) or column(s), resulting in a bad plan.)

vw_cs is a nested view with 4 levels i.e. the base table lies in the 4th view.

Updating statistics (fullscan) of the table from which the columns (vw_cs.col_1, col_2, col_3, col_4 col_5) are sourced, resolved the query slowness.

This table gets populated by nightly ETL jobs, as truncate & full load.

Any thoughts please?
(Apologies for not providing the execution plan, reasons - confidentiality with object names and was too huge to be exported. Happy to share information like scans methods chosen, cardinality, etc. Let me know.)
Database config -

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

Thanks
Ranit

What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

Automatic statistic updates are not enough
You should be proactively updating statistics  (more often than Auto Update Stats does) with FULLSCAN, to get better cardinality estimations and better query execution plans.
For more info watch this (Brent talks statistics sometime later in the video):
https://www.brentozar.com/training/think-like-sql-server-engine/

Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

Yes, update statistics with FULLSCAN for tables affected by the ETL batch processing


msdb.dbo.sp_send_dbmail temp table does not exist I have created a temp table and would like to email the results I am getting the following error.

  Query execution failed: Msg 208, Level 16, State 1, Server
  PRODYCHDBX3\X3V7, Line 1 Invalid object name '#WODups'.

After the temp table is created I ran the following code.

 DECLARE             @recordcount INT
 SELECT @recordcount = ISNULL(COUNT(*),0)
 FROM #WODups

 IF (@recordcount > 0)

 BEGIN

 SELECT * FROM #WODups
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQL Mail',
 @recipients = 'jXXn.XXXn??@yXXXXXX.com;XXXXXXXX1@gmail.com',
 @query = 'SELECT FROM #WODups',

 @subject = 'Duplicate Work Order Production Tracking',
 --@attach_query_result_as_file = 1,
 @body =0;

 DROP TABLE #WODups

Why am I getting this error, and how may I resolve it?

Personally, when I email results (and it's not 100K rows or whatever) I like to mark it up with CSS and send it as HTML so it is readable. Shnugo has a great function for doing this over on Stackoverflow.

To use it, aside from his instructions, you can do something like so:

declare @body xml =
    (select dbo.ufn_CreateHTMLTable (
        (SELECT FROM #WODups
         for xml path ('row'),elements xsinil)
    ,null,null,null))

--Maybe add some borders, Shnugo shows how to do it with his funciton too

declare @body_html varchar(max)
set @body_html =
    '<style type="text/css" media="screen,print">
    .center
    {
        text-align: center;
    }
    table,th
    {
        border: 1px solid black;
    }
    table,tr
    {
        border: 1px solid black;
    }
    table,td
    {
        border: 1px solid black;
    }
    </style>'
    + cast(@body as varchar(max))

    exec msdb..sp_send_dbmail
    @profile_name = 'SQL Mail',
    ,@recipients = 'someone@domain.com'
    ,@subject = 'This is my Subject'
    ,@body = @body_html
    ,@body_format = 'HTML'


Using Oracle hierarchical queries for transposing field value into rows One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm afraid it's not very efficient :

select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)
from
(
select 1 as test_id, '1,2,3' as str_data from dual
union all
select 2 as test_id, '4,5,6' as str_data from dual
)test_data
connect
by
regexp_substr(str_data, '[^,]+', 1, level) is not null

I don't like that I have to use distinct . Is there a better approach ?

Thank you.

Your CONNECT BY joins every row to every other (including itself) N times. This is a multi-auto-cross join. I can see how it could behave poorly performance-wise when the number of records increases.

I suggest a query like this instead:

SQL> SELECT test_id, regexp_substr(str_data, '[^,]+', 1, lvl)
  2    FROM (SELECT 1 AS test_id, '1,2,3' AS str_data
  3            FROM dual
  4          UNION ALL
  5          SELECT 2 AS test_id, '4,5,6' AS str_data FROM dual) test_data
  6    JOIN (SELECT LEVEL lvl FROM dual CONNECT BY LEVEL <= 1000)
  7      ON lvl <= length(str_data) - length(REPLACE(str_data, ',')) + 1;

TEST_ID REGEXP_SUBSTR(STR_DATA,'[^,]+',1,LVL)
------- -------------------------------------
      1                                     1
      2                                     4
      1                                     2
      2                                     5
      1                                     3
      2                                     6

Replace 1000 by the maximum number of elements (or calculate it with an inner COUNT subquery). Each row will only be queried n times (where n is the number of commas).

Also if performance is critical, use INSTR and SUBSTR instead of REGEX.


Should I check for existence of temp tables in Stored Procedures? Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them?

Similarly, is there a case when explicitly dropping them at the end of the Stored Procedure is preferable to letting SQL Server clean them up?

On Checking for #tbl

It can't hurt to check for the table's existence (and drop it if it exists) at the beginning of the procedure, but it depends on how you want to handle that scenario, and in most cases it's not possible for it to exist already anyway (at least if we're talking about the same #temp table as defined within that stored procedure).

You check for a table's existence using:

IF OBJECT_ID('tempdb..#tablename') IS NOT NULL

You can't check tempdb.sys.tables because the actual name is #tablename__________some hex code, and you shouldn't use OBJECT_ID('...') > 0 because of this potential issue.

Of course, there are exceptions. Two that come to mind:

if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure. You could make a case that you should just drop it and create your new one in that case, but you could also make the case that this is an error condition you want to know about - so maybe it's okay that CREATE TABLE #x fails.
you actually may want to use a #temp table created in an outer scope, and only create one if it hasn't been defined in that outer scope. This is possible and I use this technique all the time, but usually only when I want to capture data from system procedures that I can't easily manipulate myself (e.g. sp_helptext). So I might do this:

CREATE TABLE #x([Text] NVARCHAR(MAX));
GO
CREATE PROCEDURE dbo.myhelp @p SYSNAME
AS
  INSERT #x EXEC sp_helptext @p;
GO
EXEC dbo.myhelp N'dbo.myhelp'; -- inception
GO
SELECT [Text] FROM #x;

This works even though #x is defined outside. That's a terrible example because I'd rather use sys.sql_modules, but I'm sure you get the point and can envision how you might do that with your own procedures.

On DROP TABLE #tbl;

I think whether you should explicitly drop #temp tables at the end of the procedure is very much up for debate, and hence would be closed as primarily opinion-based; see these excellent blog posts by Paul White, read them thoroughly, and come back and formulate a specific question if all yours aren't answered:

Temporary Tables in Stored Procedures
Temporary Table Caching Explained


SQL equivalent of the "relocate all files to folder" option? When restoring database using the GUI, there's an option "relocate all files to folder" which is very convenient.

I wonder if there's the same thing if I'm restoring db using T-sql?
To me, finding out logical file names then put into the move clause seems to be too tedious and unnecessary.

There is no shortcut, no. The backup contains the original path to each and every file - a restore will try to put each file in its original location, unless you override it explicitly, and you have to do that for each file using WITH MOVE.


Data storage in innodb I am new to mysql administration and I came to know about the data storage in myisam is through .frm, .myd and .myi. I could not find the data storage mechanism in InnoDB rather than its storage in tablespace.

Could anyone help me to sort out the storage mechanism in innodb storage machine ?

Table format files (.frm files) that contain a description of table structure. Every
table has its own .frm file, located in the appropriate database directory. This is
true no matter which storage engine manages the table.
The InnoDB storage engine has its own tablespace and log files. The tablespace
contains data and index information for all InnoDB tables, as well as the undo
logs that are needed if a transaction must be rolled back. The log files record
information about committed transactions and are used to ensure that no data loss
occurs. By default, the tablespace and log files are located in the data directory.
The default tablespace file is named ibdata1 and the default log files are named
ib_logfile0 and ib_logfile1. (It is also possible to configure InnoDB to use
one tablespace file per table. In this case, InnoDB creates the tablespace file for a
given table in the table's database directory.)
Each InnoDB table is represented on disk by an .frm format file in the database
directory, as well as data and index storage in the InnoDB tablespace. The InnoDB
tablespace is a logical single storage area that is made up of one or more files or
partitions on disk. By default, InnoDB uses a single tablespace that is shared by
all InnoDB tables. The tablespace is stored in machine-independent format. It is
implemented such that table sizes can exceed the maximum file size allowed by
the filesystem. It is also possible to configure InnoDB to create each table with its
own tablespace.
MySQL represents each MyISAM table using three files: a format file that
stores the definition of the table structure, a data file that stores the contents of
table rows, and an index file that stores any indexes on the table. These files are
distinguished from one another by their suffixes. For example, the format, data,
and index files for a table named mytable are called mytable.frm, mytable.MYD,
and mytable.MYI.


Get records which doesn't have duplicate records I have a table with polcies and class codes, I want to get all policies which doesn't have duplicate class codes. Do you have any idea? One policy may have one or more class codes. If any class code is present for more than one time then I want avoid that policy in the result set.

I need to avoid 23442242 from the result set.

Assuming you just want to avoid the records that're bold:

Setup:

create table simplequestion
(
 p number,
 c number
);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (53453453,3454);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (23442242,4567);

insert into simplequestion values (23442242,4667);

insert into simplequestion values (53453453,5656);

insert into simplequestion values (53535354,3543);

insert into simplequestion values (34543545,4354);

Query:

select p,c,count(*)
from simplequestion
group by p,c
having count(*)=1;


Oracle 11g - Spool file - enforcing not quoted columns I am trying to spool large datasets (200k+ row) to csv format and need the columns not to be quoted. This is possible via the export settings of the SQL Developer GUI, but I am trying to do this via a SQL script.  The resulting csv file is then read by another application.  To demonstrate this I run the below script.

set ECHO      off
set FEEDBACK  off
set LINESIZE  60
set RECSEP    off
set TRIMSPOOL on
set VERIFY    off
set PAGESIZE  0
set SQLFORMAT csv
spool y:\aa.csv
SELECT * FROM PRODUCT_COMPONENT_VERSION;
spool off

This gives the ouput

"PRODUCT","VERSION","STATUS"
"NLSRTL ","11.2.0.4.0","Production"
"Oracle Database 11g Enterprise Edition ","11.2.0.4.0","64bit Production"
"PL/SQL ","11.2.0.4.0","Production"
"TNS for Linux: ","11.2.0.4.0","Production"

I need the output as below (NO QUOTED STRINGS)

PRODUCT,VERSION,STATUS
NLSRTL ,11.2.0.4.0,Production
Oracle Database 11g Enterprise Edition ,11.2.0.4.0,64bit Production
PL/SQL ,11.2.0.4.0,Production
TNS for Linux: ,11.2.0.4.0,Production

I would rather not post-process the file (powershell, perl, etc) after generation and before loading as it just adds unnecessary steps to a process.

How can I do a spool file where I can specify the left and right enclosures even setting them to NULL.

Regards,
Tim

set SQLFORMAT is a SQLcl option.

I am not aware of any option in SQLcl that removes the quotes.

SQL*Plus starting with version 12.2 supports csv output with the SET MARKUP option:

SQL> set markup csv on
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

"PRODUCT","VERSION","VERSION_FULL","STATUS"
"Oracle Database 19c Enterprise Edition ","19.0.0.0.0","19.3.0.0.0","Production"

You can simply disable quotes if needed:

SQL> set markup csv on quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT,VERSION,VERSION_FULL,STATUS
Oracle Database 19c Enterprise Edition ,19.0.0.0.0,19.3.0.0.0,Production

Setting a custom delimiter:

SQL> set markup csv on delimiter | quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT|VERSION|VERSION_FULL|STATUS
Oracle Database 19c Enterprise Edition |19.0.0.0.0|19.3.0.0.0|Production


Convert all ntext columns to nvarchar(max) I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max).

This is the code

ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;

  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR

    SELECT so.name as table_name, sc.name as column_name
      FROM sys.objects so
      JOIN sys.columns sc ON so.object_id = sc.object_id
      JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                        AND stp.name = 'ntext'
     WHERE so.type = 'U' -- to show only user tables
  OPEN tables_cursor

  FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE Eventlogic.dbo.' + @table_name + ' ALTER COLUMN ' + @column_name + ' nvarchar(max);')
    EXEC ('UPDATE Eventlogic.dbo.' + @table_name + ' SET ' + @column_name + '=' + @column_name + ' ')
    SET @count = @count + 1;
    IF @count > 0
      PRINT ('Eventlogic.dbo.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  END
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))

END;

Whenever I try to run it, I get this error:

  Msg 16924, Level 16, State 1, Procedure usp_SL_ConvertNtextToNvarchar, Line 37
  Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

We have 338 columns across different tables as ntext, due to legacy.

We upgraded to SQL Server 2008 R2, so we would like to convert these columns to nvarchar(max).

We want to follow the advice here ntext vs nvarchar(max) where Conwell suggests to do the update after the alter.

  SQL server moves the text from the LOB structure to the table (if less than 8,000 bytes).  So when we run the select again with IO STATISTICS we get 0 LOB reads.

Any help with this error would be great.

UPDATE

Updated code as Martin mentioned.
It is actually altering only the first one on the list before giving the error.

2nd UPDATE

After making the changes on the code to fix the second fetch, I closed SQL Management Studio.
Opened SQL Management Studio again and run it and it worked. So thanks Martin again.

Thanks in advance.

Federico

While if you get this sorted out before you read my answer, making it unlikely to be useful for your particular case (unless you need to do this in multiple databases), I prefer string concatenation over cursors for this type of work. Primarily because you can print the string instead of executing it, verifying that at least the first 8K worth looks proper and correct.

DECLARE @sql NVARCHAR(MAX) = N'USE Eventlogic;';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ALTER COLUMN ' + QUOTENAME(c.name)
  + CASE WHEN c.system_type_id = 99 THEN ' N' ELSE ' ' END
  + 'VARCHAR(MAX)' + CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
  + ';
  UPDATE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' SET ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(c.name) + ';
  PRINT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(REPLACE(t.name,'''',''''''))
    + '.' + QUOTENAME(c.name) + ' ('
    + CONVERT(VARCHAR(12), COUNT(*) OVER(PARTITION BY c.[object_id]))
    + ' columns)'';'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE c.system_type_id IN (35,99);

SET @sql += N'
PRINT ''Total columns updated: ' + CONVERT(VARCHAR(12), @@ROWCOUNT) + ''';';

PRINT @sql;
--EXEC sp_executesql @sql;

When you're happy with the PRINT output, uncomment the EXEC and run it again.

Jon Seigel's script offers additional functionality (dealing with columns that participate in FULLTEXT indexes).


Execution plan results for sp_executesql suggests adding missing index for already existing index I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results window, a simple nonclustered index is suggested.

Based upon the suggestion, I have added the index. However, when I re-run the same query, it continues to suggest the same index. Why would it think that the index does not exist?

I've seen the same behavior; check the execution plan and make sure it's using the new index, then ignore the recommendation.


Commands like CREATE, GRANT, DROP always return "0 rows affected" I was considering why some special commands in mysql like create user, grant privileges, drop user when correct passed return "0 rows affected", which is not true! (rows in mysql db are affected indeed)

I know there are not standard queries, but if we do delete from user where ... on mysql database, we will see N rows affected.

so it's countable in rather easy way.
otherwise response "Query OK (0.00 sec)" will be much accurate.

or am I missing sth?
I verify above with a few mysql version 5.1-5.5.

The way mysql works is this ...

Performing INSERTs, UPDATEs, DELETEs explicitly names tables

SQL Commands that do not explicitly name tables have 0 row(s) affected

Such commands that echo 0 row(s) affected include

CREATE USER
GRANT/REVOKE
CHANGE MASTER TO (to Setup MySQL Replication)

I suppose this has always been the case.


many Servers of SQL server 2012 needs mirroring I have two SQL server 2012 instances and I want to configure mirroring between the 2 servers with database.

My questions are:

How can it automatically change host of one if the other one was dropped ?

Is the performance of mirroring is acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?

Automatic failover requires a witness. See Database Mirroring Witness:

  To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

Your application will also have to use a mirroring aware connection string, see Connect Clients to a Database Mirroring Session:

  "Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"


Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?  For instance, I'm splitting payments and charges to their own tables from a previously unified "transactions" table.  Of course, the new tables have their own autoincremented ids and won't be cueing off the previously existing "transaction" ids so I was going to create a unique key for each transaction (based on an MD5 of the previous transaction_id and transaction_title) and use it as the new title.  Basically the pseudocode of the PHP/SQL would go something like this:

$old_payments = getOldPayments();  //DON'T WORRY ABOUT THIS, IT WORKS

foreach($old_payments as $payment){
    $unique_md5_info = md5($payment['transaction_id']."_".$payment['transaction_title']);
    $query = sprintf("INSERT INTO payments (title, amount, due_date) VALUES('%s','%s','%s') WHERE title <> '%s'",$unique_md5_info,$payment['amount'],$payment['due_date'],$unique_md5_info);
}

Obviously, in a perfect world we'd only need to run this once and be done.  However there are groups on multiple servers that won't be moving over at the same time so, in order to accommodate the staggered move, we'll have to run the query on different dates while being sure not to import any duplicate transactions.

Anyway, I'm probably seriously overthinking this issue, but hopefully someone has a reasonable solution.

Best!

You could use INSERT IGNORE. This would reject any INSERTs where the primary key or unique key already exists.

If you have to update non-keyed columns in a table in the event that the primary key or unique already exists, you can use INSERT ... ON DUPLICATE KEY UPDATE. I demonstrated how to use this in another question in the DBA StackExchange.

For your particular query, please make sure the title column is either the primary key or has a unique key defined. Then, INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE will work for you.

Give it a Try !!!

UPDATE

If the payments table is not too big, try this crazy ideas

Create a UNIQUE INDEX on this one-off

ALTER TABLE payments ADD UNIQUE INDEX title_amount_due_date (title, amount, due_date);

Perform your INSERTs IGNORE from the multiple servers. When done...

ALTER TABLE payments DROP INDEX title_amount_due_date


Creating Auto Number field with preceding number of 0's? Scenario: I have a table with a (int4) field 'pg_recordid' that has newly inserted records auto-increment the new record id using this macro after running 'SERIAL' in the field (in Navicat)

nextval('raw_casper_pg_recordid_seq'::regclass)

The current output for the first record: 1

Is there a method to set the macro so the Auto numbers have the entire number length filled with 0's for unused digits? example: 000001

Without getting into a technical argument, once you start getting fussy about the display of a number, it is no longer just a number. (Mathematically, we refer to a number as its value, while its display is referred to a numeral).

Why is this important? What you are asking for is not actually a number, but some way of displaying it. The short answer is that you will need to convert it to a string.

If you want to zero-pad a number, you use a combination of string operations:

::char will convert a value to a string
lpad(string,length,padding) will left-pad a string with the given padding.

Combining the two you get:

select lpad(id::char,4,'0'), …
from …;

where id is presumed to be your auto-numbered column.


Performance Impact of Constant Login / Logout SQL On High Latency Connection I'm reviewing the performance of a .NET application that sends 1,000s of consecutive select statements to a Microsoft SQL Server 2014 Instance. Ping time to server from client is 70 ms or higher across a WAN.

The client application is using Ole DB to connect to the SQL database.

Almost every single query SQL Profiler shows this pattern

Audit:Login
SQL:BatchStarting (Single SELECT statement)
SQL:BatchCompleted
Audit:Logout
RPC:Completed - exec sp_reset_connection

This repeats thousands of times. Is it possible the application could be re-coded to avoid constant login / logout, saving expensive network calls

Or is it expected to see this information for every query.

If Audit:Login EventSubclass=2, then this is expected, and shouldn't be an issue.  The client is just reusing a connection from a connection pool and "resetting" the connection to clear any impersonations, temp tables, session settings, etc.


How to read data from multiple Excel files with SQL Server Integration Services(New Senario) Hope you're doing well.
I have a scenario here.
As you can see I have a table with two main columns :

What I need is to read each "FilePath" which is "Active" right now and then read the Excel files in that path. So I assume that I need at least 2
"For Each loop Container" inside each other. I've already done some part of the scenario :

Within the "Execute sql task" I'm retrieving "FilePath" which are active from the table and putting them in an "object" variable (My first variable in SSIS) .

Then I have my second variable in SSIS in which I keep the name of each "FilePath" each time the outer loop is being executed.

At the end I have my third variable which will change each time the inner loop in being executed and it carries the Excel files.

The outer loop must execute to the number of active rows in meta data table and the inner loop must execute to the number of file within the path.

The point is I receive no error after executing the package, but the data won't be inserted in my "OLEDB Destination"
I was wondering if you could help me with this issue.

When you initially pull the file paths from the ExcelFilePath table, trying returning just names of the folders holding the Excel files that will be used and make sure these end with a \ (i.e. C:\Excel_Exercise\Excel_Source\).  You may need to used a T-SQL function like RIGHT/SUBSTRING, but doing this will allow you to load the files using the method outlined below.  Also, are you filtering for only "Active" file paths in the SQL query from the Execute SQL Task?  As you're probably already doing, make sure to set the DelayValidation and ValidateExternalMetadata properties accordingly.

The First Foreach Loop will be a Foreach ADO Enumerator using the object variable from the Execute SQL Task as the source variable.  In the Variable Mappings pane, add a string variable that will hold the file directory at index 0.
Inside the first Foreach Loop, add another Foreach Loop as a Foreach File Enumerator.  On the Collection pane, add an expression for the Directory property that uses the string variable from the outer Foreach loop with the directory.  On this Foreach Loop, add a variable in the Variable Mappings pane the will be used to hold the full file path (including file name/extension).
Within this Foreach Loop add a Data Flow Task that loads from an Excel Source to the desired destination.  On the Connection Manager for the Excel Source, create an expression for the ExcelFilePath and add the variable holding the complete file path and name/extension.


Restoring SQL_ASCII dumps to a UTF8-encoded database I've got a Postgres 8.4 environment where the encoding on all our databases is set to SQL_ASCII - We're finally migrating to Postgres 9.2, and I would like to migrate everything over to UTF8 encoding.

Unfortunately the text data in this DB is not clean -- Trying to restore the pg_dump to a utf8-encoded database throws errors about invalid byte sequences, even if I specify --encoding=UTF8 when I run pg_dump (presumably because Postgres doesn't know what to make of them and just dumps them unchanged?).

We have a LOT of data (upwards of a million rows with text/string elements), and auditing all of it by hand would be very time consuming (and error prone) so I'd like to automate this if possible.

Is there an easy way to find the non-utf8-conforming strings/text fields in the database so we can fix them?  Or am I stuck with a manual audit to straighten this mess out?

I suspect your database content may be in iso8859 or cp1252.  If it were ascii, you would not run into problems importing it.  You may be able to determine the coding by opening your dump with python.  The following python3 tries encodings until it succeeds.  It can be used to determine the file encoding.

for enc in ('cp1252', 'utf8'):
    print('Encoding', enc)
    try:
        file = open(fileName, 'r', encoding=enc)
        return file.read()
    except Exception as e:
        print(e)
        return None

Python also can also be used to access the database and audit the data.  It could also be used to transcode the data to UTF-8 while copying it if necessary.


Separate stored procedures for inserts and updates? I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures:

InsertNewPerson
UpdatePertsonById

But I was thinking to write 1 stored procedure instead (SetPerson) which would do both (if there is an ID, it is an update operation , else insert).

Should I create one stored procedure (only one to maintain) or should I create two different stored procedures?

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure.

CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT,
                                    @Foo INT,
                                    @Bar VARCHAR(10)
AS
    IF @Id IS NULL
      BEGIN
          INSERT INTO YourTable
                      (Foo,
                       Bar)
          VALUES      (@Foo,
                       @Bar)

          SET @Id = SCOPE_IDENTITY()
      END
    ELSE
      BEGIN
          UPDATE YourTable
          SET    Foo = @Foo,
                 Bar = @Bar
          WHERE  Id = @Id
      END

The benefit I see to this is that you don't have to maintain two separate parameter lists if the table structure changes. The disadvantage is that the single stored procedure now has two responsibilities and is somewhat less easy to understand.

I would generally opt for separating them into two stored procedures.

RE: "Can you elaborate how an upsert will look"

CREATE PROC UpsertYourTable
@Id int,
@Foo int,
@Bar varchar(10)
AS
MERGE YourTable WITH (HOLDLOCK)  AS T
        USING ( VALUES ( @Id, @Foo, @Bar ) )
              AS source ( Id, Foo, Bar)
        ON ( T.Id = source.Id )
        WHEN MATCHED
            THEN
        UPDATE SET
                Foo = source.Foo ,
                Bar = source.Bar
        WHEN NOT MATCHED
            THEN
        INSERT  (Id, Foo , Bar)
               VALUES
               (@Id, @Foo , @Bar);

This assumes that Id is no longer an IDENTITY column. The reason for using HOLDLOCK is explained here.


Postgres declare table as a parameter for a function? I would like to create a function that operates on a table, for example

create or replace function test(t table)
    returns void language plpgsql as
$func$
begin
    select * from t limit 10;
end;
$func$

Then, I can call the function with any tablename, e.g.

select test(myTable);

How would I do something like this?

You cannot declare a table as function parameter per se, since there are no table variables in Postgres. But there are various ways to achieve what you might want to achieve - which isn't exactly clear yet.

You can "pass" a table as ...

table name (type text or name) - especially when tables may not actually exist (yet):

Define table and column names as arguments in a plpgsql function?

object identifier (type regclass):

PostgreSQL: Pass table as argument in function
Table name as a PostgreSQL function parameter

actual table type (type anyelement) - using the concept of polymorphism:

Refactor a PL/pgSQL function to return the output of various SELECT queries

In most cases you will end up using dynamic SQL inside the function. Be wary of SQL injections vectors. Related:

SQL injection in Postgres functions vs prepared queries
PL/pgSQL regclass quoting of table named like keyword

The return type depends on your input and what you want to achieve ...


How to get average according to last update time? There is table - Item below, how can I get the average Value according to its 15 minute value from last record time? I am using SQL Server 2008.

Table -- Item

Table 15min_AVG

Assuming your table and sample data look like this:

CREATE TABLE dbo.Item
(
    Item        char(1) NOT NULL,
    RecordTime  datetime2(0) NOT NULL,
    Value       decimal(5,3) NOT NULL,

    CONSTRAINT PK_dbo_Item_Item_RecordTime
        PRIMARY KEY (Item, RecordTime)
);

INSERT dbo.Item
    (Item, RecordTime, Value)
VALUES
    ('A', '2014-12-29T10:13:00', 0.139),
    ('A', '2014-12-29T10:12:00', 0.150),
    ('A', '2014-12-29T10:11:00', 0.113),
    ('B', '2014-12-29T10:10:00', 0.139),
    ('B', '2014-12-29T10:09:00', 0.120),
    ('B', '2014-12-29T10:08:00', 0.084),
    ('C', '2014-12-29T09:55:00', 0.101),
    ('C', '2014-12-29T09:54:00', 0.095),
    ('C', '2014-12-29T09:53:00', 0.129);

One way to write the query is:

SELECT
    MaxTime.Item,
    Average.Value
FROM
(
    -- Highest record time per item
    SELECT
        I.Item,
        RecordTime = MAX(I.RecordTime)
    FROM dbo.Item AS I
    GROUP BY
        I.Item
) AS MaxTime
CROSS APPLY
(
    -- Average value within 15 minutes
    SELECT
        Value = CONVERT(decimal(5,3), AVG(I2.Value))
    FROM dbo.Item AS I2
    WHERE
        I2.Item = MaxTime.Item
        AND I2.RecordTime >= DATEADD(MINUTE, -15, MaxTime.RecordTime)
) AS Average
ORDER BY
    MaxTime.Item;

SQLFiddle Demo


Read a partially updated row? Let say I have two queries, running in two separate sessions in SSMS:

First session:

UPDATE Person
SET Name='Jonny', Surname='Cage'
WHERE Id=42

Second session:

SELECT Name, Surname
FROM Person WITH(NOLOCK)
WHERE Id > 30

Is it possible that the SELECT statement could read a half-updated row, for instance one with Name = 'Jonny' and Surname = 'Goody'?

Queries are executed almost simultaneously in separate sessions.

Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row.

Setup:

CREATE TABLE Person
  (
     Id      INT PRIMARY KEY,
     Name    VARCHAR(100),
     Surname VARCHAR(100)
  );

CREATE INDEX ix_Name
  ON Person(Name);

CREATE INDEX ix_Surname
  ON Person(Surname);

INSERT INTO Person
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID),
                   'Jonny1',
                   'Jonny1'
FROM   master..spt_values v1,
       master..spt_values v2

In the first connection, run this:

WHILE ( 1 = 1 )
  BEGIN
      UPDATE Person
      SET    Name = 'Jonny2',
             Surname = 'Jonny2'

      UPDATE Person
      SET    Name = 'Jonny1',
             Surname = 'Jonny1'
  END

In the second connection, run this:

DECLARE @Person TABLE (
  Id      INT PRIMARY KEY,
  Name    VARCHAR(100),
  Surname VARCHAR(100));

SELECT 'Setting intial Rowcount'
WHERE  1 = 0

WHILE @@ROWCOUNT = 0
  INSERT INTO @Person
  SELECT Id,
         Name,
         Surname
  FROM   Person WITH(NOLOCK, INDEX = ix_Name, INDEX = ix_Surname)
  WHERE  Id > 30
         AND Name <> Surname

SELECT *
FROM   @Person

After running for about 30 seconds I get :

The SELECT query is retrieving the columns from the non clustered indexes rather than the clustered index (albeit due to the hints).

The update statement gets a wide update plan...

... and updates the indexes in sequence so it is possible to read "before" values from one index and "after" from the other.

It is also possible to retrieve two different versions of the same column value.

In the first connection, run this:

DECLARE @A VARCHAR(MAX) = 'A';
DECLARE @B VARCHAR(MAX) = 'B';

SELECT @A = REPLICATE(@A, 200000),
       @B = REPLICATE(@B, 200000);

CREATE TABLE T
  (
     V VARCHAR(MAX) NULL
  );

INSERT INTO T
VALUES     (@B);

WHILE 1 = 1
  BEGIN
      UPDATE T
      SET    V = @A;

      UPDATE T
      SET    V = @B;
  END

And then in the second, run this:

SELECT 'Setting intial Rowcount'
WHERE  1 = 0;

WHILE @@ROWCOUNT = 0
  SELECT LEFT(V, 10)  AS Left10,
         RIGHT(V, 10) AS Right10
  FROM   T WITH (NOLOCK)
  WHERE  LEFT(V, 10) <> RIGHT(V, 10);

DROP TABLE T;

Straight away, this returned the following result for me

+------------+------------+
|   Left10   |  Right10   |
+------------+------------+
| BBBBBBBBBB | AAAAAAAAAA |
+------------+------------+


How can I control how Pentaho DI formats integers coming from a text file? I have a simple pipe-delimited text file with integers recorded as a sequence of numbers:

012345|00678 |various|text|fields

There are some leading zeros, and some trailing white space, but no decimals, commas, internal spaces, or other formatting.  Should be simple, right?

Pentaho's field analysis concludes that the fields are of type "Number", format "#.#", and precision "0".  That format string makes me wonder if Pentaho thinks there might be decimals in the columns, but when I run Preview Rows, I get nice clean integers, without decimals, scientific notation, or leading zeros, exactly as I want it.

When I try to consume the data with a PostgreSQL Bulk Loader step, I get error messages like these:

Import Table.0 - ERROR {0} ERROR:  invalid input syntax for integer: "5.017645082E9"
Import Table.0 - ERROR {0} ERROR:  invalid input syntax for integer: "1.0"

It seems that Pentaho has taken it on itself to format the integers with scientific notation and/or fixed decimals, and naturally Postgres balks at importing this.

How can I stop Pentaho from formatting these integers?

I'm using Pentaho Data Integration v6.1, and Postgres 9.4.9.  The file uses Windows line breaks and ANSI text.  Changing the field format to "#" or "0" had no effect.  Using a Table Output instead of a PostgreSQL Bulk Load works, but it is much slower.

Try to convert values in format you need before output, it can be done in "Select values" step, bookmark "Meta-data".


Can PostgreSQL slave server act as master for a new database? My question is somewhat related to:
https://stackoverflow.com/questions/19331577/does-postgres-replication-native-support-per-database-level-replication

We have master-slave replication set up on our Postgres server (1 master and 1 slave). We have all our app tables in database DB1.

Our reporting group reads from DB1 and creates tables in a separate database DB2. The queries look like this:

INSERT INTO DB2.<some_table>
(SELECT ... FROM ... DB1 tables ...)

The READ queries that run against DB1 take a long time to run, so ideally we want to run these queries on the slave. But the WRITEs have to go to the master, so we end up running our entire SQL on master now.

Is there a way to make DB2 available only on the slave server and let the slave server be the master for DB2?

The alternative would be to write a script that reads from slave and writes to master, but I would like to avoid that since most people know only SQL.

You have a few options. You could use DBLINK for your queries to run them on slave and get the data over to DB2 on master.  You could also do that with FDW's set up in DB2 on master.

The other option which sounds more like what you want in your title would require you running 2 separate Postgres clusters on the machines.  So you'd have two separate installs, 2 different data directories, running on different ports. DB2 would live on a different cluster than DB1.  You could set up your streaming replication differently for each of those databases at that point (where as if they're on the same cluster, you can't pick and choose).

Hope that helps.


How to model complex database role relationships The purpose of my application is the creation, viewing and editing of articles.

The problem I have is that users will have different roles for an article or group of articles. In addition, the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question.

I am struggling with how to model these relationships.

Here is another option.

Users

user_id (PK)
user_first_name
user_last_name
etc.

Articles

article_id (PK)
article_title
article_body
etc.

Groups

group_id (PK)
group_name

Article_Groups // this ties together the optional M:N articles and groups

group_id (PK)
article_id (PK)

Roles

role_id (PK)
role_description

User_Group_Permissions // users can have ability to view entire groups...

user_id (PK) (FK to Users.user_id)
group_id (PK) (FK to Groups.group_id)
role_id (FK to Roles.role_id)

User_Article_Permissions // or users can be given ability to view single articles

user_id (PK) (FK to Users.user_id)
article_id (PK) (FK to Articles.article_id)
role_id (FK to Roles.role_id)

Minimum cardinalities
Are all 0 on the child side, and 1 on the parent side.

Regarding the INSERTS into User_Group_Permissions and User_Article_Permissions:
I would take care of your permissions logic ("the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question") in your code, since that's a business rule.

But, you could use the roles table to keep track of the levels. For example, you could also do something like this, in order to set a minimum role requirement for that article or group and then handle the ASSURANCE of that in your business rules:

Articles

article_id (PK)
article_title
article_body
**minimum_role_to_add (FK to Roles.role_id)**
etc.

Groups

group_id (PK)
group_name
**minimum_role_to_add (FK to Roles.role_id)**


Running out of Transaction Log space during Alter Table I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

I'm guessing that it's not possible to break this down into modifying fewer rows at a time, so I'm wondering what my options are for making this change to the database. SQL Azure will not let me change the size of the transaction log (limited to 1gb).

I'm guessing that my best bet is going to be creating a new table with the new layout, migrating the data across into that one, deleting the original table and then renaming the new table to match the name of the old table. If that's the case, how is it best to structure those commands?

Scheduled downtime for our system is not an issue currently so this operation can take as long as it needs to.

You will want to load your data into a new table, doing this in small batches, then drop the existing table. I put together a quick example using the Sales.Customer table in AdventureWorks, something similar should work for you also.

First, create your new table, complete with the new datatype you want to use:

CREATE TABLE [Sales].[Currency_New](
    [CurrencyCode] [nchar](4) NOT NULL,
    [Name] [varchar](128) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Currency_New_CurrencyCode] PRIMARY KEY CLUSTERED
(
    [CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

Then, insert your records and define your batch. I am using 10 here, but you will likely want to use something larger, say 10,000 rows at a time. For 30MM rows I'd even suggest you go to 100k row batch size at a time, that's the limit I typically used with larger tables:

DECLARE @RowsInserted INT, @InsertVolume INT
SET @RowsInserted = 1
SET @InsertVolume = 10 --Set to # of rows

WHILE @RowsInserted > 0
BEGIN

INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
           ,[Name]
           ,[ModifiedDate])
SELECT TOP (@InsertVolume)
       SC.[CurrencyCode]
           ,SC.[Name]
           ,SC.[ModifiedDate]
FROM [Sales].[Currency] AS SC
LEFT JOIN [Sales].[Currency_New] AS SCN
    ON SC.[CurrencyCode] = SCN.[CurrencyCode]
WHERE SCN.[CurrencyCode] IS NULL

SET @RowsInserted = @@ROWCOUNT
END

I usually do a sanity check and verify the rowcounts are the same before cleaning up:

SELECT COUNT(*) FROM [Sales].[Currency]
SELECT COUNT(*) FROM [Sales].[Currency_New]

Once you are confident you have migrated your data, you can drop the original table:

DROP TABLE [Sales].[Currency]

Last step, rename the new table, so that users don't have to change any code:

EXEC sp_rename '[Sales].[Currency_New]', '[Sales].[Currency]';
GO

I don't know how long this will take. I'd suggest you try doing this when you have a clear maintenance window and users aren't connected.

HTH


Oracle user password with space character in it I connected to an Oracle 19c database and executed the following SQL:

alter user sys identified by "a b" container=all;

It executed successfully. However, after that I can't connect to Oracle using SQLPlus. Below is my trying:

oracle:[~]$ sqlplus
Enter user-name: sys as sysdba
Enter password:
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
Enter user-name: oracle:[~]$
oracle:[~]$
oracle:[~]$
oracle:[~]$ sqlplus sys/"a b"@localhost:1521/ORCLCDB as sysdba
... (not work)
oracle:[~]$ sqlplus sys/\"a b\"@localhost:1521/ORCLCDB as sysdba
... (not work)
oracle:[~]$ sqlplus sys@localhost:1521/ORCLCDB as sysdba
... (type "a b" without quotes and not work)

How can I connect to the database?

Not able to login to oracle using sqlplus utility because username and password contain space

[oracle@o71 ~]$ sqlplus sys/Oracle123@\'localhost/MIN19_O71\' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 30 23:12:19 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> alter user sys identified by "Oracle 123";

User altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

[oracle@o71 ~]$ sqlplus sys/\""Oracle 123\""@\'localhost/MIN19_O71\' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 30 23:19:25 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL>


Why database has char data type when varchar is more efficient? I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored.
But if VARCHAR is so efficient in handling the space management dynamically based on the content being stored, why databases has the support for CHAR datatype, all CHAR(n) fields can be VARCHAR(n) fields, right ?

You should read this article SQL varchar data type deep dive, and specifically the Storage and performance considerations using SQL varchar section:

Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.

Note this specifically speaks to the differences from a Microsoft SQL Server perspective, YMMV for other database systems, and not all support both CHAR and VARCHAR data types (e.g. SQLite has neither data type and only supports what it calls the TEXT data type).
And here's some good information, in similar vein, from MySQL's perspective, courtesy of RolandoMySQLDBA - DBA.StackExchange answer to What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?:

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields.

Surprisingly, in the domain of PostgreSQL, it sounds like the same performance differences don't exist, and therefore the practical differences between the data types are less abundant. Please see this StackOverflow answer to Difference between text and varchar (character varying) and this DBA.StackExchange answer to Index performance for CHAR vs VARCHAR (Postgres):

...the performance of inserts and selects for all 4 data types are similar.

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.


Should every table in my database include store id? I am designing single database for multiple stores. Every store may have different products and stock. should i have to include store_id in every table in database. my database looks like this

This problem is called multi-tenancy. You want to offer the database service to multiple 'tenants' (in your case, stores). Microsoft published some time ago a paper on the topic, Multi-Tenant Data Architecture which highlights some approaches (separate database, shared database separate schemas, shared tables) with pros and cons of each. Your approach is shared tables, and yes, you do need a store_id in table that contain per-store data (there are usually some shared tables that do no require a store_id). Not only that, but all indexes will require store_id as a leftmost key. All your JOIN must also add AND left.store_id = right.store_id as a join condition. And all your queries must have a WHERE store_id=@store_id filter.


Returning columns based on various conditions in Group By I would like to do a 'group by' on the following example table

Input:

Output:

[Low] = Min of [Low] column
[High] = Max of [High] column
[Symbol]
[Epoch]
[CuVol]
[Dt10Min])
[Close]
[Open]

Columns 3-7 have the values corresponding to highest Epoch, which is 129 in this case and columns 8 will be the value of [Open] at lowest Epoch of the group [Dt10Min],127 in this case.

I am very new to SQL can you please suggest.

Let's start by putting your data into a temp table. For future questions you'll want to do this in the question itself so people that want to help you don't have to copy it by hand from a screenshot. I made some guesses about data types:

CREATE TABLE #input (
    Symbol VARCHAR(1) NOT NULL,
    Epoch INT NOT NULL,
    [Open] NUMERIC(9, 1) NOT NULL,
    [Close] NUMERIC(9, 1) NOT NULL,
    [High] NUMERIC(9, 1) NOT NULL,
    [Low] NUMERIC(9, 1) NOT NULL,
    CuVol INT NOT NULL,
    Dt10Min DATETIME NOT NULL
);

INSERT INTO #input
VALUES
('A', 127, 23.6, 24, 23.9, 22.8, 1600, '20100822')
, ('A', 128, 24.6, 24.1, 24.8, 23.6, 3200, '20100822')
, ('A', 129, 23.7, 24.6, 23.9, 23.5, 4800, '20100822');

Now let's write separate queries for all of the information that you want and combine them at the end. Getting the minimum value from a table is very straightforward:

SELECT MIN([Low])
FROM #input;

As is getting the maximum value from a table:

SELECT MAX([High])
FROM #input;

Carrying along column values based on the minimum or maximum of another column is more complex. Around here we call that a "greatest n per group" problem. If you just need a single row then you can use the TOP operator. SQL Server will do a TOP 1 sort which will require any extremely low memory grant. This can be helpful if your table is very large. The query below returns the row with the lowest Epoch:

SELECT TOP 1 [Open]
FROM #input
ORDER BY Epoch ASC;

We can write a very similar query to return data associated with the largest Epoch. It's also easy to select as many columns as needed:

SELECT TOP 1 Symbol, [Epoch], [CuVol], [Dt10Min], [Close]
FROM #input
ORDER BY Epoch DESC;

Now we need to combine all of the queries together to get a single result set. Each query is guaranteed to return a single row, so we might as well use CROSS JOIN. Here's the complete query:

SELECT
  highest_epoch.symbol
, highest_epoch.Epoch
, lowest_epoch.[Open]
, highest_epoch.[Close]
, min_max.High
, min_max.Low
, highest_epoch.CuVol
, highest_epoch.Dt10Min
FROM
(
    SELECT
      MIN(i.[Low]) [Low]
    , MAX(i.[High]) [High]
    FROM #input i
) min_max
CROSS JOIN
(
    SELECT TOP 1 Symbol, [Epoch], [CuVol], [Dt10Min], [Close]
    FROM #input
    ORDER BY Epoch DESC
) highest_epoch
CROSS JOIN
(
    SELECT TOP 1 [Open]
    FROM #input
    ORDER BY Epoch ASC
) lowest_epoch;

This query returns the results that you're looking for:

╔════════╦═══════╦══════╦═══════╦══════╦══════╦═══════╦═════════════════════════╗
║ symbol ║ Epoch ║ Open ║ Close ║ High ║ Low  ║ CuVol ║         Dt10Min         ║
╠════════╬═══════╬══════╬═══════╬══════╬══════╬═══════╬═════════════════════════╣
║ A      ║   129 ║ 23.6 ║ 24.6  ║ 24.8 ║ 22.8 ║  4800 ║ 2010-08-22 00:00:00.000 ║
╚════════╩═══════╩══════╩═══════╩══════╩══════╩═══════╩═════════════════════════╝

Without any indexes, it'll do three scans of the table but it shouldn't require sorting. That could be okay depending on the size of the table and your response time requirements. However, if you need to speed the query up you could consider adding covering indexes for each derived table or you could use a more advanced technique to do the "greatest n per group" calculations. For example, it's possible to use GROUP BY to carry along extra columns as I showed in this answer.


There are probably many ways to do this. The first that comes to mind is to use window functions:

SELECT
    id, postcode
FROM
  ( SELECT id, postcode,
           ROW_NUMBER() OVER (PARTITION BY id
                              ORDER BY MAX(date_created) DESC
                             ) AS rn
    FROM tablename
    GROUP BY id, postcode
  ) AS t
WHERE
    rn <= 5
ORDER BY
    id, rn ;

Test at SQLfiddle.

If there are ties, say the 5th, 6th and 7th postcode for an id have the same date_created, only one of them (choice will be arbitrary) will be in the results. If you want all the tied postcodes in those cases, use RANK() instead of ROW_NUMBER().

Another option is to use the LATERAL syntax. I'm not sure which will be more efficient, it wil probably depend on the values distribution of the two columns (id and postcode), i.e. how many distinct ids in the whole table, how many distinct postcodes per id and how many rows per (id, postcode) combinations.

SELECT
    t.id, ti.postcode
FROM
    ( SELECT DISTINCT id
      FROM tablename
    ) AS t
  CROSS JOIN LATERAL
    ( SELECT tt.postcode,
             MAX(tt.date_created) AS date_created
      FROM tablename AS tt
      WHERE tt.id = t.id
      GROUP BY tt.postcode
      ORDER BY date_created DESC
      LIMIT 5
    ) AS ti
ORDER BY
    t.id, ti.date_created DESC;

Adding an index on (id, postcode, date_created) would be a good idea, too - or on (id, postcode, date_created DESC).


MySQL SELECT query hangs - Opening Tables INNODB On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even doing a:

mysql> use INFORMATION_SCHEMA;
mysql> SELECT * FROM TABLES;

hangs on me. The working databases seem fine (at least the websites are online). When I tried to access the bad database websites, it will just keep spinning.

If I do a:

mysql> use rbb;
mysql> SELECT * FROM wp_posts;

It will just hang.

My server is running CentOS 6.2 and up to now i've had no issues.

Any pointers would be much appreciated. I have no idea what to do next.

Even opening a Sequel Pro to the server hangs the application.

If I try a query and then check the process list in another connection, it shows:

+----+------+-----------+-----+---------+------+----------------+------------------------+
| Id | User | Host      | db  | Command | Time | State          | Info                   |
+----+------+-----------+-----+---------+------+----------------+------------------------+
| 10 | root | localhost | rbb | Query   | 45   | Opening tables | SELECT * FROM wp_users |

MySQL Version is 5.5.28-log:

mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.5.28-log |
+------------+
1 row in set (0.00 sec)

I was experimenting about a year ago with ClouSE, but haven't used this since. My error log does report this, however.

131207 20:49:38 [ERROR] ClouSE: S3 listObjects for 'hondurasproject' failed. SSL certificate problem, verify that the CA cert is OK. Details:
error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed.

I have tried a few times to restart the server, but it hasn't helped.

Server disk space is looking good. 66G is available. Output below:

[root@server log]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1             91G   21G   66G  24% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm

Yes, WordPress uses the default storage engine (which is InnoDB since MySQL 5.5).

I'm not sure I understand the problem. Can you execute the SELECT using the mysql command client? If you can, the problem is in the GUIs you use.

If you cannot (which means that you will receive an error, I think) you should investigate the error log. The only possible problem I can think now is a damaged table (REPAIR TABLE should solve the problem), but the error log should give you the exact answer.


ssis convert ddmonyyyy dd/mm/yyyy Hi guys trying to convert

16JAN2014 --> 16/01/2014.  Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc.

SSMS will quite happily do SELECT MONTH('16JAN2014') to get me the number of month however MONTH( "16JAN2014" ) will not work in SSIS.

hmmm... tried code but keep getting error below...

Use a Script Component to perform the conversion!  Be sure to set your read / write variables of course!  It's quite easy for use specific locale settings with the .NET framework, so a Script Component should be superior than trusting the database to correctly do the conversions for you.

Using a Script Component to Parse a DateTime Value from a String

    using System.Globalization;

    ...

    public void Main()
    {
        String str = ( String )Dts.Variables[ "StringVariable" ].Value;

             // Good candidate for another variable, this is just an example.
        String format = "ddMMMyyyy";
        CultureInfo provider = CultureInfo.InvariantCulture;

        DateTime dt = DateTime.Parse( str, format, provider );
        Dts.Variables[ "DateTimeVariable" ].Value = dt;

        Dts.TaskResult = ( int )ScriptResults.Success;
    }

And in VB, if that's How You Roll™

    Imports System.Globalization

    ...

    Public Sub Main()
            Dim str As String = Dts.Variables("StringVariable").Value
            Dim format As String = "ddMMMyyyy"
            Dim provider As CultureInfo = CultureInfo.InvariantCulture
            Dim dt As DateTime = Date.ParseExact(str, format, provider)
            Dts.Variables("DateTimeVariable").Value = dt
            Dts.TaskResult = ScriptResults.Success
    End Sub


PostgreSQL 11: How to restrict privileges to stored functions? How can I restrict the privileges of a PostgreSQL role to access only functions
already present in a schema, so no individual SELECT or INSERT queries are allowed?

I've created a new user:

$ createuser --no-superuser --createdb --no-createrole --pwprompt myuser
$ createdb --encoding UTF8 --owner myuser mydatabase

Then, I added my schema with all tables and functions for table access:

$ psql -h localhost -U myuser -d mydatabase -a -f myschema.sql

Is it sufficient do drop all privileges for role myuser and them just:

myuser=> GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO myuser;

Or should I create a distinct role? Can this role access a schema whom it is not the owner of?

If you want to use a function to give a user access to otherwise inaccessible data, you need to define it as SECURITY DEFINER and make the owner a user with the required privileges on the data.

Don't forget to

REVOKE EXECUTE ON FUNCTION myfunc FROM PUBLIC;

because by default, everybody can execute a function
ALTER FUNCTION myfunc SET search_path = ..., pg_temp;

so that the function is safe from privilege escalation attacks.


Using outer alias in a subquery |    payments    |  | transactions |  | transaction_items |
|:--------------:|  |:------------:|  |:-----------------:|
|       id       |  |      id      |  |         id        |
|      date      |  |    number    |  |   transaction_id  |
|     amount     |  |     date     |  |    description    |
| transaction_id |  |      val     |  |       price       |
                                      |      discount     |
                                      |      quantity     |

I'm trying to display a list of payments made on transactions and show the current balance after each payment. Below is an example of expected result

| number | DATE(p.date) | total   | paid    | balance |
| 1355   | 2016-10-31   | 899.00  | 450.00  | 449.00  |
| 1355   | 2016-12-06   | 899.00  | 449.00  | 0.00    |
| 1359   | 2016-09-28   | 4045.00 | 1515.00 | 2530    |
| 1359   | 2016-10-24   | 4045.00 | 35.00   | 2495.00 |
| 1361   | 2016-09-28   | 1548.00 | 1548.00 | 0.00    |

and here is my query so far, but have an error in the where clause

select
    t.number,
    DATE(p.date),
    ti.total 'total',
    SUM(p.amount) 'paid',
    ti.total - paid.total 'balance'
from payments p
left join transactions t
on p.transaction_id = t.id
left join (
    select inner_ti.transaction_id, sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity)  'total'
    from transaction_items inner_ti
    group by inner_ti.transaction_id
) ti on t.id = ti.transaction_id
left join (
    select inner_p.transaction_id, sum(inner_p.amount) 'total'
    from payments inner_p
    where inner_p.date <= p.date -- error unknown column p.date
    group by inner_p.transaction_id
) paid on t.id = paid.transaction_id
group by t.number, DATE(p.date), ti.total, paid.total
order by DATE(p.date) ASC

Please note that I'm grouping by p.date since our concern is total payments made within the day.

Can someone please enlighten me why I'm getting that error? And is there any workaround to achieve the expected result?

The two nested selects in your query are called derived tables. A derived table is not meant to be correlated with other datasets participating in the query, hence outer references to them in the nested query are not allowed.

One way to resolve the issue is to rewrite your query so as to move the offending select to the context where correlation is allowed. In your case you can move the offending subquery to the SELECT clause:

select    t.number,
          DATE(p.date),
          ti.total 'total',
          SUM(p.amount) 'paid',
          ti.total - (select sum(inner_p.amount)
                      from     payments inner_p
                      where    inner_p.transaction_id = p.transaction_id
                      and      inner_p.date <= p.date
                     ) 'balance'
from      payments p
left join transactions t
on        p.transaction_id = t.id
left join (
          select   inner_ti.transaction_id,
                   sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity)  'total'
          from     transaction_items inner_ti
          group by inner_ti.transaction_id
          ) ti
on        t.id = ti.transaction_id
group by  t.number, DATE(p.date), ti.total, 'balance'
order by  DATE(p.date) ASC;

rextester here

For the sake of completeness, the SQL standard actually has syntax that allows correlation for derived tables. It is called lateral join. From the syntactical point of view, it looks almost exactly like a normal join, you just need to add the LATERAL keyword after JOIN:

…
left join lateral (
    select inner_p.transaction_id, sum(inner_p.amount) 'total'
    from payments inner_p
    where inner_p.date <= p.date -- this outer reference would be valid
    group by inner_p.transaction_id
) paid on t.id = paid.transaction_id
…

The added keyword makes all the difference, as only with that keyword a nested query is permitted to reference other datasets in the same FROM clause (to the left of the most recent JOIN keyword).

Lateral joins are currently supported by PostgreSQL and Oracle. A similar concept with a slightly different (and less flexible) syntax is also supported by SQL Server. As you may have guessed, MySQL does not currently support anything of the sort.


MySQL error #1064 while creating a trigger DELIMITER $$
DROP TRIGGER IF EXISTS brandRep $$
CREATE TRIGGER brandRep AFTER UPDATE ON sales_flat_order
 FOR EACH ROW
BEGIN
IF (NEW.status <> OLD.status AND OLD.applied_rule_ids <=> 45182) THEN
    UPDATE reps_transaction_history_ids
    SET order_status = NEW.status
    WHERE order_id = OLD.increment_id;
ENDIF $$

END $$
DELIMITER;

There are two problems

PROBLEM #1 : ENDIF is wrong. It's END IF

PROBLEM #2 : END IF $$ is wrong. It's END IF;

This is how it should appear

DELIMITER $$
DROP TRIGGER IF EXISTS brandRep $$
CREATE TRIGGER brandRep AFTER UPDATE ON sales_flat_order
 FOR EACH ROW
BEGIN
IF (NEW.status <> OLD.status AND OLD.applied_rule_ids <=> 45182) THEN
    UPDATE reps_transaction_history_ids
    SET order_status = NEW.status
    WHERE order_id = OLD.increment_id;
END IF;

END $$
DELIMITER ;

Give it a Try !!!


How to call a function in regex_replace with matches? I'd like to use some processing function instead of replacement text in regex_replace function. Is there possible to use a function with matches as arguments, like:
SELECT REGEXP_REPLACE( description, '\[([is]?:?)(\d+)\]', CONCAT_WS(' ', \1, \2), 'gi' )
  FROM some_table
  WHERE id = 123;

Of course, backreferences \1 and \2 do not work here, but is there some way to use variables instead of them?
Background: description may contain more than one string with a pattern like [12345] or [i:12345] or [s:12345] and I'd like to process such matches into advanced strings, so I thought the best way is to write a custom function and call it with matches of regex_replace. I just did not found in the docs, how could I use matches as variable/arguments. Is this possible or what is a better way to accomplish my goal?
EDIT I show description field before and after:
Lorem ipsum dolor sit amet [123], consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et [234] magna aliqua.

should be after replacements like:
Lorem ipsum dolor sit amet "one-two-three", consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et "two-three-four" magna aliqua.

Use regexp_matches.  The output of capturing parentheses will be stuffed into an array slot and can be accessed from there. Each match of the entire pattern will generate one row, which you could re-aggregate if you wanted to.
SELECT concat_ws(' ',regexp_matches[1],regexp_matches[2])
   from regexp_matches( 'foobar[i:89]asdfasdf[s:12]llll[i:9]',
                        '\[([is]?:?)(\d+)\]',
                        'gi'
                      );


How to do ALTER TABLE TYPE efficiently Unlike tables, a table type cannot be altered by a simple command. I need to do the following:

Rename existing table type
Create the new table type
Open each affected objects (i.e. ALTER SP) and execute it again so that the reference to table type will be updated.

But this is a tedious task as there may be many SP to be updated. Is there any easy way/pre-generated script that can automate this process?

Instead of

  Open each affected objects (i.e. ALTER SP) and execute it again so
  that the reference to table type will be updated

you can just find all the affected objects and refresh them using sp_refreshsqlmodule.

Here is the code to find the modules:

select object_name(referencing_id)
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

To do refresh of all this objects you can simply generate the commands like this:

select 'sp_refreshsqlmodule ' + quotename( object_name(referencing_id), '''')
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

Make the output to text instead of grid and copy + paste generated rows to SSMS and execute them.


The placement of database files in a shared folder Is it a normal practice with MS SQL Server to place databases in remote shared folders on a fast local network rather than on the physical HDD where the server software is installed? What problems shall one fear that local placement avoids? For example, can a network glitch cause .mdf and .ldf to become inconsistent through successful modificaion of the one and failure to modify the other?

EDIT:
Whereas Microsoft recommends employment of Storage-Area Network (SAN) or SCSI instead of simple shared folders, could you please tell me the disadvantages of the latter?

The key differentiator between the recommended locations (e.g. local storage, a SAN, or an iSCSI-based network) and a network share is quite simply, redundancy.  All of the recommended approaches provide an option for redundant paths for I/O to take to persistent storage.

For instance, you can RAID local storage, providing redundancy if any disk fails.  With either the SAN or iSCSI-based network storage approaches, these technologies use Multipath Input/Output (MPIO) drivers, providing redundancy to the storage.

A network drive, in contrast, does not employ or allow for any redundant I/O paths.  If a Network Interface Controller (NIC) fails on either end, the share likely disappears.  Even if you have multiple NICs, there will still be a brief outage as a different IP address will now host that share, so any data sent to the old/failed IP will timeout and disappear.  Basically a network share wasn't designed with this level of redundancy, and a loss of data mid stream may corrupt your database (or worse just get lost without a trace).  The whole point of a database is to reliably store data and a network share brings that whole reliable aspect into question.


Alternative to MS Acess for in-house user database I am a working student at a bank and my current task is to create an in-house database for customer-specific lookups which often rely on complex underlying data aggregation from different sources. I was expecting to get access to something like a SQL Server or similar in order to implement this but was left with the challenge to do it without a dedicated DB or server to work with. My goal is to implement the most sustainable solution and not do something like a quick'n dirty fix.

I am always up for a challenge but certain things like data security/integrity and multi-user access popped up when I thought of implementing it all with MS Access.
On top of that I worry that it might become messy and also cause some drama should I eventually leave the company.

There are of course dedicated things like an Oracle Data Warehouse but using this will be problematic as other departments are denied access to it and therefore will not be able to use it.

Next thing that came to mind was using something like a portable DB. I downloaded PostgreSQL Portable and it works great, but since I am pretty much the only one fluent enough in SQL to use it, it would require some kind of frontend like Access yet again.

To outline my situation: I have access to my own computer and Network Storage with daily backups. I, as for now, do not have the ability to leave my laptop on all the time and have no access to a dedicated server.

On top of being a DB, I would like it to update and load data from other sources at regular intervals, but I could also automate that via PowerShell scripts if needed.

My questions are as follows:

Is using something like MS Access a smart or rather stupid idea? Are there any points I was missing in my assessment?
Are there any free alternatives I could use for my needs?
Is the plan in its current state even doable or should I rather sit down and negotiate about access to more resources?

I know that the questions are more subjective than I would like but I don't want to upset our DBA's and I think most of you guys have faced similar situations and know what would have been a far better solution.
I appreciate any input.

You can use the free SQL Server Express Edition, and use Access or many other tools as a front-end.  You can take nightly backups to network storage, or even store the database files directly on the network storage device.

Later the database can be migrated to a enterprise-managed SQL Server with no code changes.


Oracle: How to gather stats in a logical standby database? I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old ;)

SELECT guard_status FROM v$database;

GUARD_STATUS
------------
STANDBY

You cannot run any DML in a logical standby database unless you deactivate, temporary, the guard feature. To overcome you isse you can do:

SQL> ALTER DATABASE GUARD NONE;
SQL> SELECT GUARD_STATUS FROM V$DATABASE;
GUARD_STATUS
--------------------
NONE

And then run your dbms_stats.


Upgrading Postgres 12 to 13: 'pg_upgrade.exe' fails I'm attempting to upgrade a Postgres instance from version 12 to version 13, following the steps outlined at https://www.postgresql.org/docs/13/pgupgrade.html.
From an elevated command prompt, I'm running (under Windows 10):

SET PATH=%PATH%;C:\Program Files\PostgreSQL\13\bin;
pg_upgrade.exe --old-datadir "E:\Programs\PostgreSQL\12\data"
--new-datadir "E:\Programs\PostgreSQL\13\data" --old-bindir "C:\Program Files\PostgreSQL\12\bin" --new-bindir "C:\Program
Files\PostgreSQL\13\bin" --old-port=5431 --new-port=5432 --jobs=5

The directories appear to be all correct, however the command returns:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5431?
could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5431?

could not connect to source postmaster started with the command:
"C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start
Failure, exiting

pg_upgrade_server_start.log says:

command: "C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start >> "pg_upgrade_server_start.log" 2>&1
The program "postgres" was found by "C:/Program Files/PostgreSQL/12/bin/pg_ctl"
but was not the same version as pg_ctl.
Check your installation.

I'm at a loss; what might be wrong, and how can I successfully perform this procedure?

The answer had to do with the option "Beta: Use Unicode UTF-8 for worldwide language support" being checked in my locale settings in Windows.
I disabled that option and restarted the computer, the upgrade went without issues after that.


strategy for enabling a general query log on MySQL 5.0.95 with no restart? I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability.

Obviously I can set the "log" option in the my.cnf file and restart the server, but this is going to kick off a bunch of monitoring emails and cause some tomcat errors which I would like to avoid.

According to the docs, the server "log" option is actually read-only until it becomes the general_log in version 5.1.x;
http://dev.mysql.com/doc/refman/5.0/en/query-log.html

[mysqld]
log
log-output=/var/log/mysqld.log

the /etc/init.d/mysqld script that I have doesn't support a "refresh" and its looks like a reload is going do a fairly brutal stop and start, which is definitely going to show up in monitoring.

on a test server I tried the various mysqladmin refresh and reload options, but couldn't get it to reread the config.

Do I have any other options?

Actually, while researching for this question I found a couple of possibilities for logging the ongoing SQL in real time without restarting the database.

If you are not using SSL for the network connection to port 3306, then its possible to tcpdump the traffic, and then use mk-query-digest or wireshark to re-assemble the TCP stream and decode to the mysql SQL text.

This obviously is only practical when you can collect the data you need in a short time, because you are going to fill up the tcp dump file quickly for anything non-trivial.

I do not think for v5.0.95 is it possible to enable general log without server restart, but
as of MySQL 5.1.12, you can by executing this query,
You can try this on v5.0.95 as well and see whether it works or not,

  SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

Baron has written a script to enable and disable log without restart of server for v5.0 as mentioned below:

http://www.mysqlperformanceblog.com/2007/09/13/enablingdisabling-query-log-without-server-restart-in-mysql-50/


select distinct on csv data A table has CSV data and I want to do a SELECT DISTINCT ID on that table, how should I proceed?

+-----------+
| id        |
+-----------+
| 111,1487  |
+-----------+
| 462       |
+-----------+
| 2492      |
+-----------+
| 3184      |
+-----------+
| 3181,3184 |
+-----------+
| 3181      |
+-----------+
| 440       |
+-----------+
| 1436      |
+-----------+

I've got a function which I usually use that can split varchar csv string of INTs into a one-column table which I use as follow:

SELECT number from INTLIST_TO_TBL('123,234,345,4566')

It works well but I can't use it in this case as there are many rows and it will return multiple rows in certain cases.

Should I try using a cursor that inserts into a temporary table then proceed to the next step by querying this temporary table ?

I've been told cursors are synonym of poor performance which is why I always resort to asking here first before going down that road.

You should be able to use CROSS APPLY.

In an effort to provide a Minimum Complete Verifiable Example, I've included a 'simple' SPLIT TVF which is intended to emulate your INTLIST_TO_TBL function. This is for demonstration purposes only and there are more efficient ways to split a string that are beyond my answer.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Split]')
                    AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
    DROP FUNCTION [dbo].[Split] ;
GO
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (element int,items varchar(8000))
as
begin
    declare @element int=1
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            begin
                insert into @temptable(Element,Items) values(@element,@slice)
                set @element=@element+1
            end

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end

GO

Now that we have the Split TVF, let's see an example of how to incorporate it.

DECLARE @tbl TABLE (id VARCHAR(100))

INSERT @tbl
VALUES ('111,1487')
    ,('462')
    ,('2492')
    ,('3184')
    ,('3181,3184')
    ,('3181')
    ,('440')
    ,('1436')

SELECT DISTINCT b.items
FROM @tbl a
CROSS APPLY [dbo].[Split](a.id, ',') b


Move large mysql table data from one server to another I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which are based on above table. What i am planning to do it is move the old, say 1 week, data to another server and keep the current table size always small. I am running a php application to fetch api and generate reports on the server.

My concerns are:

Is it the right approach or are there other solutions to my problem?
What is the best way to do it?

You could use a cron job to connect to the existing database, make a backup of the table, select the rows you need to move (over one week old) disconnect from the sql server,  connect to the new server,  insert the rows you have previously selected using a loop,  disconnect from the second server,  reconnect to first server,  and delete the rows you don't need anymore.

That's one way of doing it,  I don't know if it's optimal,  but it would work,  do not forget to plan the cron job to run Only when the first server is at its lowest usage to make sure you don't impact the performance of the live server.