Validate a column to accept only certain age limit from birthdate given using check constraint

Most likely the issue is that the ELSE is returning BirthDate instead of 0.

If there is already data in the table that conflicts, then add the WITH NOCHECK clause if you want to keep that data, else first delete any rows that conflict with the new constraint.

Also, best to use MONTH instead of M for the date part, for easier readability.


SQL Server, Allow remote connections to this server option. What does this do?

This checkbox is the GUI way of setting the remote access configuration option,

EXEC sp_configure 'remote access', 0; -- UI checkbox unchecked
EXEC sp_configure 'remote access', 1; -- UI checkbox checked

The feature is labelled incorrectly in the SSMS UI:

Incorrect: Allow remote connections to this server
Correct: Allow remote connections to from this server

and the documentation describes it wrong:

  Allow remote connections to this server

  Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

It should be:

  Allow remote connections to from this server

  Controls the execution of stored procedures from to remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from to a remote server.

Documentation from BOL 2000

SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:

  Configuring Remote Servers

  A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

  If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

   Note  Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

Disabling the option prevents outgoing connections

If you try to execute a stored procedure on a remote linked server (i.e. sp_addlinkedserver):

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

it will run fine.

If you then disable remote access on the local server:

EXEC sp_configure 'remote access', 0;, or
uncheck "Allow remote connections to from this server"

the same linked stored procedure will fail:

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

  Msg 7201, Level 17, State 4, Procedure GetUnreadDocuments, Line 1
  Could not execute procedure on remote server 'Hyperion' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

Short version

Remote access controls outgoing access to remote stored procedures.

And the documentation incorrectly notes that using sp_addlinkedserver avoids the problem:

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead.

That is false.

See also

How to execute a stored procedure against linked server?


Oracle - how to reference to a field%TYPE using database links?

You can reference a remote table in a procedure or a package. Here's an example with a loopback link:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l dual.dummy@loopback%TYPE;
  3  BEGIN
  4     SELECT * INTO l FROM dual@loopback;
  5     dbms_output.put_line(l);
  6  END;
  7  /

X

PL/SQL procedure successfully completed

I think there are restrictions and you should probably use a workaround such as a local view that references the remote table or a local synonym.

In any case this should remain an exception, obviously the best place to store a procedure is in the database where the data is stored. If you find that a program frequently needs access to several databases, it might be a good idea to merge those databases.


Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7

Short answer:  The numbers are close; don't worry.

Long answer:

There are a lot of explanations.

Well, I guess Barracuda is 15% smaller than Antelope.  (This may be the main reason for the difference.  Still, many other things can cause differences.)
Are they a different ROW_FORMAT.
SELECTs can cause locking that leads to extra fragmentation of inserts/updates/deletes.
Is innodb_file_per_table the same?
How was the Slave data cloned from the Master?
Have you done OPTIMIZE TABLE on either machine.  (There is virtually no reason to every do so.)
5.7 has a lot of code changes over 5.5, but I can't think of any other issues that might directly show up in those numbers.
It are not likely that there are any data differences.  Percona has a tool that will check.
Is there a "better way to monitor?" -- Monitor for what?

Also,

When an InnoDB block shrinks due to updating/deleting, there is some attempt to merge the block with an adjacent block.  It may be that 5.7 does a better job than 5.5, hence leading to slower growth of the table over time.  (This seemed to be more of an issue in index BTrees.)
The "Data_free" includes only a fraction of the reusable space.  It should not be trusted for much.


There are different ways to do this ...
Here is one
CREATE TABLE Table1
    ([RowNum] int, [Interface] varchar(5), [TIME] int)
;

INSERT INTO Table1
    ([RowNum], [Interface], [TIME])
VALUES
    (1, 'Intf1', 0),
    (2, 'Intf2', 3),
    (3, 'intf5', 1),
    (4, 'Intf2', 4),
    (5, 'intf3', 3),
    (6, 'Intf2', 2),
    (7, 'intf3', 5),
    (8, 'intf3', 2),
    (9, 'inft4', 2),
    (10, 'Intf2', 0),
    (11, 'intf5', 1)

SELECT t.*
FROM table1 as t
  INNER JOIN
  (
     SELECT Interface, Time ,
         ROW_NUMBER() OVER(ORDER BY a.Time dESC) as rn
     FROM Table1 as a
  ) AS P
  ON t.Interface = p.Interface
WHERE
  p.rn <=2
ORDER BY p.Time DESC,p.Interface,t.Time DESC

And output:
RowNum  Interface   TIME
7   intf3   5
5   intf3   3
8   intf3   2
4   Intf2   4
2   Intf2   3
6   Intf2   2
10  Intf2   0

dbfiddle


SQL - Best way to limit access to small number of tables

Just place a DENY ALL  on the table for that user. DENYs are always evaluated first, and will override the GRANT off SELECT to the overall schema:

DENY ALL ON [schema].[object] TO {user_name}

There is no reason to move the login table into a separate schema.


Does SHOWPLAN permission toggle exist in SSMS

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

Before answering this question lets first see what privileges you need for using SHOWPLAN.

You can grant it by using:

GRANT SHOWPLAN
TO <database_principal> [ , ...n ]

Sysadmin, dbcreator and db_owners role members automatically get privilege for using SHOWPLAN. Details here.

  For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC
  user_defined_function statements, the following permissions are
  required to produce a Showplan: Appropriate permissions to execute the
  Transact-SQL statements. SHOWPLAN permission on all databases
  containing objects referenced by the Transact-SQL statements, such as
  tables, views, and so on.

Now to answer your question about "a query to see if a user, other than myself, has SHOWPLAN permissions?"

You will need to find if the user is a member of any of these 3 roles or SHOWPLAN privilege is granted explicitly.

exec sp_helpuser 'test' will give the list of roles a user is a member of. For explicit privilege you can use this and check if SHOWPLAN privilege was grated explicitly. This script is modified from here.

SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE USER_NAME(dppriper.grantee_principal_id) ='TEST'

Here is my test:

USE [master] GO CREATE LOGIN test WITH PASSWORD  ='tes545###45454545454t'
GO
USE [WideWorldImporters]
GO

CREATE USER test FOR LOGIN test
GO
GRANT SHOWPLAN TO test

Now run the query above and you will see the explicit SHOWPLAN permission for user test.

Once you run above tsql you will be able to see the permission for the securable (database name is this case) in the bottom window 'permission for .....'  (you have to add the database as a securable first) .


I had to set the following line of the phpPgAdmin config file (which was duplicate in my config) to

$conf['owned_only'] = false;


Run the setup.exe for express again and you can find the option to repair the installation on the from the installation screen instead of doing it through add and remove programs. You can also install 7-zip and use that to extract the setup file to get the msi files for the repair


How do I include zeros in a count(column)?

There's not much information you provide, so this is only a guess.

Your WHERE clause Tab2.Col1 = Tab1.Col1(+) may be backward, with regard to the (+) ... I would recommend switching it to a LEFT JOIN regardless, just to make it more readable..

select
  count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

If you are still not getting the 0 counts, then you should try a coalesce statement.

select
  COALESCE(count(b.Col1), 0) AS "Number of Records", --- COALESCE HERE .. CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

You are not referencing table b anywhere in your sql, though - please explain why you need to join to it if you're not going to be using its data?


Namespacing database names on my server in order to test multiple different SSIS packages

When l was testing a large SSIS package l switched the data connections using the first technique here.  The second technique in the second answer l have not tried but looks simpler to implement.  The main advantage was that l could easily switch between test and production.  Just wondering if that is easier than changing the namespace?


Here is one thing that caught my eye when you replied to my comment: The target table is InnoDB and you are using LOAD DATA INFILE. I see two issues
ISSUE #1 : LOAD DATA INFILE
While LOAD DATA INFILE can load InnoDB tables, that command can be tuned for loading MyYSAM tables. There is only one option to do this: bulk_insert_buffer_size. either setting to very large or setting it to zero to disable it.
There is no synonymous provision for InnoDB.
ISSUE #2 : InnoDB Storage Engine
Let's take a look at the InnoDB Architecture

Now, picture yourself pushing 50 millions rows into one InnoDB table as a single transaction giving all the plumbing depicted in this elaborate illustration.
To ensure data consistency in the event of a crash, your data has to be written in three places:

There are 128 rollback segments in the System Tablespace (Physical File ibdata1). Your incoming table data must pile up on one Rollback Segment like defensive tackles on a quarterback.
You have an active Double Write Buffer in the System Tablespace. As the name implies, data is being written twice. InnoDB will write to the Double Write Buffer first before writing back to the .ibd files. Those data are used as source data for crash recovery.
The data are also being written in the Transaction Logs (Redo Logs in the Bottom Right Corner) via the Log Buffer. The physical files are ib_logfile0 and ib_logfile1.

My Perspective
InnoDB can handle 1024 current transactions but there are only 128 rollback segments. If there are other transactions going on, you got a New York City traffic jam on your hands. With all the InnoDB Internals to manage through your bulk insert, seeing NULL in the processlist should not be a surprise. You should look at four(4) things to make sure they are up-to-date:

filesize of ibdata1
timestamp on ibdata1
timestamp on ib_logfile0
timestamp on ib_logfile1

SUGGESTIONS
You could set one or more of the following

Disable the Double Write Buffer (set innodb_doublewrite to 0). please set it back to 1 afterwards.
Increase the InnoDB Buffer Pool (increase innodb_buffer_pool_size)
Increase the InnoDB Log File Size (set inndo_log_file_size to 2G)
Increase the InnoDB Log Buffer Size (set inndo_log_buffer_size to 512M)
Change Transaction Flush Behavior (set innodb_flush_log_at_trx_commit to 0). This will disables ACID Compliance (could lost 1 second of transactions upon a crash) but will increase InnoDB Write Performance.
Increase Write IO Thread (set innodb_write_io_threads to 64. You may as well set innodb_write_io_threads to 64)

GIVE IT A TRY !!!


Do NT Service accounts need to be SysAdmin when using Domain Accounts

If I am running SQL and SQL Agent as a Domain user, do I still need these NT Service accounts, and/or do they need to be SysAdmin?

Even if you are running SQL server as domain account, keep those NT SERVICE\* as is.

From BOL :

  Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>

These logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. Keep them in SYSADMIN role even if you are using Domain account. See SQL Server Per-service SID Login and Privileges section.

A really good answer detailing above stuff - Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they for ?

  These accounts can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.


How do you connect to SQL Server 2008 Express when SA is disabled?

you could start SQL Server in single-user mode.

When you do that, any windows administator can connect to SQL Server as a SQL Server administrator. Then you can re-create the sa user


Isn't contradictory that MongoDB may lose updates with journaling in WiredTiger?

It doesn't matter if it's a wiredTiger or MMAPv1, they are just storage engines. Whether you lose updates or not depends on write concerns.

Your app communicates with MongoDB server that writes mostly to memory that has a cache of pages which is periodically written and read from the persistent disk. There is also a journal that logs everything.

When your write to the database it writes both to pages and journal simultaneously. Pages will write to the disk depending on memory pressure. By default you don't wait for acknowledgement from the journal because the journal may not write to the disk for a while, which is represented by j=false. And by default w=1 which means it only acknowledges write to the cache pages.

Default: w = 1, j = false - this is fast, but if something happens before the journal has a chance to write to the disk then the data is lost. What's worse is that it will show as if the data has been written/persisted if you rely on w = 1.

w = 1, j = true - in this case you wait for the acknowledgement from the journal that it has written to the disk. This is a lot slower, but ensures the data is persisted.


I addressed this issue 1.5 years ago : innodb_file_format Barracuda

Just from reading the documentation, I was able to figure out mathematically that the smaller you make the key_block_size, the less extra space you need to add to the InnoDB Buffer Pool.

Here is the chart I made up in that earlier post

You have a DB Server with a 8G Buffer Pool
You ran compression with key_block_size=8

8 is 50.00% of 16
50.00% of 8G is 4G
raise innodb_buffer_pool_size to 12G (8G + 4G)

You ran compression with key_block_size=4

4 is 25.00% of 16
25.00% of 8G is 2G
raise innodb_buffer_pool_size to 10G (8G + 2G)

You ran compression with key_block_size=2

2 is 12.50% of 16
12.50% of 8G is 1G
raise innodb_buffer_pool_size to 9G (8G + 1G)

You ran compression with key_block_size=1

1 is 06.25% of 16
06.25% of 8G is 0.5G (512M)
raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

Given the following

BPS = Buffer Pool Size
KBS = key_block_size
BPSFC = Buffer Pool Size For Compression

The ideal size for the Buffer Pool in a perfect world would be

BPSFC = BPS * (16 + KBS) / 16

From this formula, less is more. The smaller KBS is, the smaller the increase in BPS needs to be. Additoinally, it would appear that the CPU would worker a little harder in response to tighter compression. How ?

For KBS=8, evicting one uncompressed page leaves room for 02 compressed pages
For KBS=4, evicting one uncompressed page leaves room for 04 compressed pages
For KBS=2, evicting one uncompressed page leaves room for 08 compressed pages
For KBS=1, evicting one uncompressed page leaves room for 16 compressed pages
For KBS=X, evicting one uncompressed page leaves room for 16/X compressed pages

I can see the CPU working harder to introduce more compressed pages with tighter compression. This could result in evicting LRU compressed pages more often.

If this is the case, it would appear that making the BPS even bigger than the suggested values from these formulas would give CPU some relief as well as reduce the rate of the eviction of compressed pages from the Buffer Pool.

EXAMPLE: From my first chart, an 8GB Buffer Pool would need to be 12GB for key_block_size=8. Making the Buffer Pool larger than 12GB would relieve the CPU of a lot of this heavy lifting (in the form of evicting uncompressed pages). Perhaps doubling the Buffer Pool Size may be need to be explored.

As for your question

  will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?

I would say it would hurt, no such much because of the hit rate against the compressed pages, but because of the additional house cleaning in terms of the management of uncompressed pages and the CPU usage required for it. This would lower InnoDB performance no matter how much tuning the rest of the Storage Engine gets.


Grouping on any one of multiple columns in Postgres

Another recursive solution that:

first creates the adjacency list of the connected graph of ids,
then finds its transitive closure (this is the recursive part)
and then groups by (once) to find the connected components that each node belongs to
and joins to the table again and group by (again) to gather the values from all nodes of each connected component.

Initial data (copied from Jack Douglas' solution):

begin;
create schema stack;
set search_path=stack;

create table foo as
select *
from (values (1,'X','D','G','P')
           , (2,'F','D','L','M')
           , (3,'X','N','R','S')
           , (4,'Y','I','W',null)
           , (5,'U','Z','E',null) ) AS f(id,a,b,c,d);

The query:

with recursive
  al (tail, head) as                     -- adjacency list
  ( select f.id, g.id
    from foo as f join foo as g
      on (f.a = g.a or f.b = g.b or f.c = g.c or f.d = g.d)
  ),
  tc (tail, head) as                     -- transitive closure
  ( select * from al
    union distinct
    select f.tail, g.head
    from al as f join tc as g on f.head = g.tail
  ) ,
  cc (head, ids) as                      -- group once
  ( select head, array_agg(distinct tail order by tail) as ids
    from tc
    group by head
  )
select                                   -- group twice
    ids,
    array_agg(distinct a order by a) as a,
    array_agg(distinct b order by b) as b,
    array_agg(distinct c order by c) as c,
    array_agg(distinct d order by d) as d
from
  cc join foo on cc.head = foo.id
group by ids ;

┌─────────┬───────┬───────┬─────────┬─────────┐
│   ids   │   a   │   b   │    c    │    d    │
├─────────┼───────┼───────┼─────────┼─────────┤
│ {1,2,3} │ {F,X} │ {D,N} │ {G,L,R} │ {M,P,S} │
│ {4}     │ {Y}   │ {I}   │ {W}     │ {NULL}  │
│ {5}     │ {U}   │ {Z}   │ {E}     │ {NULL}  │
└─────────┴───────┴───────┴─────────┴─────────┘

Cleanup:

rollback;


DBO execute returns true but doesn't execute

SOLUTION

The problem was fixed by adding this line after the execute.
$STMT3->closeCursor();

Thanks everyone that tried to help :)


How to extract multiple key value pairs into distinct columns

This can be achieved using a few lines of Power Query:

Remove outer brackets ("[" & "]") using Table.ReplaceValue
Merge [Primary] & [Secondary] columns using Table.AddColumns & Text.Combine
Split the new merged column into rows using the key value pair delimiter (",") using Table.ExpandListColumn, Table.TransformColumns, & Splitter.SplitTextByDelimiter
Split the new [KeyValuePairs] column into separate columns using the key value delimiter (":") using Table.SplitColumn
Create groups by [PageName], [CreatedBy], & [Key], while concatenating the [Value] column using Table.Group & Text.Combine
Pivot the table by [Key] for the [KeyValues] using Table.Pivot

Here is a the Power Query I constructed using this method:

let
    Source = #table({"PageName", "CreatedBy", "Primary", "Secondary"}, {{"page1", "Joe", "[owner:frank,topic:meals]", "[topic:drinks]"}, {"page2", "Dale", "[owner:joe,topic:drinks,topic:meals]", "[topic:appetizers]"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PageName", type text}, {"CreatedBy", type text}, {"Primary", type text}, {"Secondary", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value1", "KeyValuePairs", each Text.Combine({[Primary], [Secondary]}, ","), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Primary", "Secondary"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"KeyValuePairs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "KeyValuePairs"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"KeyValuePairs", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "KeyValuePairs", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Key", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Key", type text}, {"Value", type text}}),
    //#"Grouped Rows" = Table.Group(#"Changed Type2", {"Index", "KeyValuePairs.1"}, {{"Values", each _, type table}})
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"PageName", "CreatedBy", "Key"}, {{"KeyValues", each Text.Combine(_[Value], ", "), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Key]), "Key", "KeyValues")
in
    #"Pivoted Column"


Granting permissions only on a set list of objects

I've done this a couple of weeks ago -
Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS
(
  SELECT
    "name"
  FROM
    "master"."dbo"."syslogins"
  WHERE
    "name" = 'your_login'
)
BEGIN
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS
(
  SELECT
    1
  FROM
    "sys"."database_principals"
  WHERE
    "name" = N'your_username'
)
BEGIN
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS
(
  SELECT
    1
  FROM
    "sys"."database_principals"
  WHERE
    "name" = N'your_role_name'
)
BEGIN
  CREATE ROLE "your_role_name";
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
-- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?


Speed problem with a simple select statement

Tough to say just from looking at it, but here's a good way to figure out where to start:

Run your query from SSMS with the execution plan turned on (right click anywhere and choose "Include Actual Execution Plan"). Read from right to left, top to bottom. Each icon will display a percentage - figure out where the query is spending the most time then start there.

General advice: try to turn scans into seeks by adding indexes; look for big discrepancies between estimated and actual number of rows - this indicates statistics are out of date; look at "Number of Executions" - I suspect those functions are executing many many times.


Can be anywhere from 5 seconds to 15 minutes depending on your server, workloads, databases, etc. If you have thousands of databases, for example, you can check out the documentation and use switches like @CheckUserDatabaseObjects = 0 to go faster by skipping stuff.


How export a database based in a primary key of a table?

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ...
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.


SQL query to count with subtraction from count of other sql table

This works for your sample data, but may need further testing (and possible adjustments) against a larger set of data.

--Demo setup
declare @Table1 table (Id_tag int, BoxNo varchar(5))
declare @Table2 table (DeleteID_tag int, Col1 varchar(5))

insert into @Table1(Id_tag,BoxNo) values
(5699,'A4'),
(5538,'A4'),
(4567,'A5'),
(2222,'A3'),
(1234,'A4');
Insert into @Table2(DeleteID_tag,Col1) values
(1234,'abc'),
(2222,'jkl')
;

--The actual query
SELECT t1.BoxNo
    ,count(*)
FROM @Table1 t1
WHERE NOT EXISTS (
        SELECT *
        FROM @Table2
        WHERE DeleteID_tag = t1.Id_tag
        )
GROUP BY t1.BoxNo

| BoxNo | (No column name) |
|-------|------------------|
| A4    | 2                |
| A5    | 1                |


The best way to achieve what you want is to do full export/import using data pump technology.

After successful import, you will probably have to recompile invalid objects.


SSISDB on AlwaysOn Group running package on Secondary Replica

... but when failing over to second replica, the jobs fails.
Asking to create master Key or certificate.

This is due to how security works with keys in SQL Server. There are various methods around this.

is there a way to automate this and whenever FaillOver happen the jobs should be running as they are on Primary replica?

For the first part, yes, see the second link above. For the second part, IMHO you shouldn't be executing any SSIS jobs on a SQL Server because they don't play well together in terms of sharing resources. Period. You're asking for performance issues when doing this and I'd stay far, far away from it.


Slow queries related to subqueries using aggregation

Your approach ("aggregate first, join later") generally performs best when you need to retrieve the whole table or large parts of it, or if you can restrict to relevant rows cheaply in the subquery - or while the aggregated table is small. See:

Query with LEFT JOIN not returning rows for count of 0

For only a few rows in the result, this gets disproportionately expensive for big tables, since the whole table (event_dates in your case) has to be processed. In these cases, it's better to revert the paradigm to "join first, aggregate later". The equivalent query would then be:
SELECT e.id
     , array_agg(ed.date_start) AS starts
     , array_agg(ed.date_end)   AS ends
     , ...
FROM   editions       e
JOIN   edition_events ee ON ee.edition_id = e.id
JOIN   events         ev ON ev.id = ee.event_id
JOIN   event_dates    ed ON ed.event_id = e.id
WHERE  e.id = ?
GROUP  BY e.id;

This should help with any version of Postgres.
Also using table aliases to simplify the syntax.
I don't see how LATERAL (Postgres 9.3+) could help this particular query. You would use it to reference a column from the left side of the join tree that you couldn't access on the same query level otherwise, often to produce multiple rows to join to. Neither is the case here. What you are thinking of can be implemented with plain old correlated subqueries, but those typically perform worse.
Don't take my word for it. Run a quick test with EXPLAIN ANALYZE for your case (with your real life data), then you know for sure.


SQL Server: Additional cpus slow down batch

I can only guess about the query because I can't see it or the data. Perhaps it simply doesn't lend itself to parallel execution. In that case, you can use a query hint OPTION ( MAXDOP 1 ) (after the ORDER BY clause) to force the query to execute on only one core.

http://technet.microsoft.com/en-us/library/ms181714.aspx


Sql Server 2016 maintenance plan with weekly full backup and 4h differential backups

Sounds like you are using maintenance plans for your Full backup.  If so, make a new plan with a Backup task in it and change the backup type in the GUI to Differential, then set the schedule.   As RDFozz says, consider also using t-log backups every 15-60 minutes throughout the day.   If not, and your databases are in FULL recovery model, you will likely get crazy file growth in your .ldf files :)

Note that the task in this image says full, because that is the default.  When I click OK, it will change.


Speed difference between Drop table and Truncate table in Postgres

DROP & CREATE is slightly more expensive, as it actually deletes rows from some system tables (pg_class, pg_attribute, ...) in addition to removing the physical table files - and later has to parse etc. the new CREATE TABLE command, while TRUNCATE only removes the physical files for the table and starts new ones, keeping the catalog entries. But the difference is negligible for simple tables, especially for temp tables. And it gets smaller, yet if you factor in an additional ANALYZE that might be needed after TRUNCATE. Then again, you might need that in any case. See:

Are regular VACUUM ANALYZE still recommended under 9.1?

By "filling the tables" you mean COPY, I suppose? A much more costly difference would be to CREATE or TRUNCATE a plain table in a separate transaction before writing to it, as in this case you accrue the additional (substantial) cost of writing WAL (Write Ahead Log) entries. The manual:

  COPY is fastest when used within the same transaction as an earlier
  CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be
  written, because in case of an error, the files containing the newly
  loaded data will be removed anyway. However, this consideration only
  applies when wal_level is minimal for non-partitioned tables as all
  commands must write WAL otherwise.

Bold emphasis mine. (minimal is the default for wal_level.) Does not affect temp tables, which do not write WAL at all.

You might be interested in CREATE TEMP TABLE ...ON COMMIT DELETE ROWS. The manual:

  All rows in the temporary table will be deleted at the end of each
  transaction block. Essentially, an automatic TRUNCATE is done at each
  commit. When used on a partitioned table, this is not cascaded to its partitions.

Should be fastest. But the difference still typically small.


This is a very open ended question where lots of factors you havent revealed could change peoples opinions.

That said; I've had to deal with similar scenarios in my career.

To get the current balance you are going to need to sum up all the transactions.  This leads to the view that an indexed view is the right solution when using SQL Server.  It is indeed a good one, however, it is not without its limitations.  You need to consider these factors:

How much impact is a schema change going to cost me if I have an indexed view?
Will I need to drop the index and rebuild it?
How long will that take?
Can I afford the blocking on the base table(s) during that rebuild?
Does my indexed view prevent refactoring the transaction table(s) later in anyway?

These could become considerable blockers to your application development in the future.

Because of these (and I am sure other) factors you might start leaning towards calculating the balance "on the fly".  That is, you essentially have your view but without the index on it.  Or just query the table and sum the transactions.  If you start looking at this approach you have to consider:

Will we have lots of transactions per account?
And if we do, do the balance queries perform?
Will calculating the balance impact other queries (notably adding new transactions) in any way (think blocking here)

You have given the third option of calculating and storing the balance at the time of data updates.  This seems attractive as it avoids all the downsides of the previous two scenarios I've covered.  You have fast look ups and my schema is not hampered or tied to the indexed view.  There are some gotchas here too:

Do I calculate the new balance manually each time I update the table?
Do I use a trigger to maintain the balance (and avoid the previous point)
Do I have to cater for rows also being removed or updated (protip: you will...even if you think you wont)
Can I afford the performance impact of any trigger I create? Do its benefits outweigh this cost?

We havent even thought about transaction isolation levels yet!  Are you going to be using READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION at all? Because these have an impact you need to consider.

What I would probably do from your information given

You mention you only expect 1000 transactions per month. I'm going to read that as 1000 per customer per month as a worse case scenario.  Thats not really a lot of data, especially early on.  Granted, if you have 1,000,000 customers all doing 1000 transactions you're going to have a lot of data.  I'm assuming a small number of customers too.

I wouldn't bother with an indexed view or a calculated table/column for the balance.  I'd simply query it on the fly to start with.  This way you can enhance and expand your schema and not worry about the impacts the other choices might have had.

Longer term, as the schema settled down and the application matured I would look at those other two options to see which suits the use case best.  I love what indexed views can give you, however, they are not without their downsides and can be quite a pain later on when you want to make changes.

This is approach follows the "premature optimisation" issue one can face in application programming.  You cannot always do this with database design, however, I think here you can as the optimisation you could later is pretty easy.  Especially if you abstract the balance retrival through a view in the beginning.  No matter what approach you take later, you simply update that view to retrive the data from the new object(s).

Slightly Off Topic:

Couple of tips for your schema (Im also aware you may have simplified it for the question)

Your schema shows a list of transactions per customer. This says each customer has one and only one account.  That may be the case today, however, I would thoroughly recommend designing your schema to accommodate many accounts per customer.
I wouldnt recommend using the money data type.  It doesn't have enough precision or scale.  Often when dealing with financial data you might need to go to 6 decimal places depending on the currency.  Or beyond the max size of the money datatype.


Database backups are copies of your database contents to prevent data loss in case of a disaster or data corruption.

There are two general types of backup Physical & Logical.

A Logical Backup
exports the data stored in a table regardless of the location of the table.
Logical backups recreate the tables,indexes and insert all the data to the tables. Therefore, are relatively slower than physical backups. Depending on the RDBMS and the software you use you can change the way the logical backup is performed for the example you mentioned, in MySQL using mysqldump dbname --no-data  you can get a backup of table definitions without data in the tables for a database. A logical backup creates a file that when executed restores and recreates all the data in the database.

A Physical Backup
is an actual copy of the data files that store a database's Structure,Index and Data which are on the hard disk.

One of the challenges of taking backups is making consistent backups.When a database is active it may change while the backup is being performed.
During a hot backup( a backup when the database server is running) there are several methods to ensure consistency.

1) Using Table locks: Tables are temporarily locked and they won't execute any UPDATE, INSERT or DELETE.

2) Another method is using Multi-Version Concurrency Control (MVCC) where the database takes a point of time in the database as the backup reference point and backs everything up from there, while accepting UPDATE, INSERT or DELETE while backup is being performed. The advantage of MVCC is that the tables will completely operational while backup is taking place.

NOTE: Locking mechanisms depend on the RDBMS

A Cold backup
is another way to ensure consistency. In a cold backup the database server is turned off and not accepting any new clients.


Cannot execute job that runs a shell script

It looks like external scheduler jobs are not available in the XE edition.


Best MySQL storage engine for read heavy table with two indexes

The biggest downside for ARCHIVE in your environment would be that it won't work.

The ARCHIVE storage engine only supports INSERT and SELECT -- not UPDATE or DELETE -- and only supports one index, on the primary key.

I would to go with InnoDB, which can, incidentally, also store tables compressed on disk.  I can't think of a good reason to go any other way.


SQL Server RAM usage is increasing and query executions is slowing down with time

Based on the waits statistics in your system you have incorrect settings for parallelism.

Maxdop in your system should not be more than 16, you can find the correct value based on this MS article: Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Here are some T-SQL scripts to do it (I don't know your exact NUMA configuration but the scripts alilyze it):
MAXDOP setting algorithm for SQL Server

Here is the explanation given by Max Vernon on which I agree. He explains why incorrect setting of maxdop slows down the execution.

  If you set MAXDOP higher than the number of cores/numa node, you end
  up with calls into far memory which are many many times slower than
  calling near memory. This is because each numa node has its own
  memory; having a query use more threads than are present in a single
  numa mode will spread the CPU load over multiple cores, and therefore
  multiple memory nodes.


Strange query plan when using OR in JOIN clause - Constant scan for every row in table

The plan is similar to the one I go into in more detail here.

The Posts table is scanned.

For each row it extracts the OwnerUserId and LastEditorUserId. This is in a similar manner to the way UNPIVOT works. You see a single constant scan operator in the plan for the below creating the two output rows for each input row.

SELECT *
FROM dbo.Posts
UNPIVOT (X FOR U IN (OwnerUserId,LastEditorUserId)) Unpvt

In this case the plan is a bit more complex as the semantics for or are that if both column values are the same only one row should be emitted from the join on Users (not two)

These are then put through the merge interval so that in the event the values are the same the range is collapsed down and only one seek is executed against Users - otherwise two seeks are executed against it.

The value 62 is a flag meaning that the seek should be an equality seek.

Regarding

  I don't understand how it has access to these when it hasn't done the
  nested loop join from IX_NC_REPUTATION to the subtree containing
  Expr1010 and Expr1011

These are defined in the yellow highlighted concatenation operator. This is on the outer side of the yellow highlighted nested loops. So this runs before the yellow highlighted seek on the inside of that nested loops.

A rewrite that gives a similar plan (though with the merge interval replaced by a merge union) is below in case this helps.

SELECT DISTINCT D2.UserId
FROM   dbo.Posts p
       CROSS APPLY (SELECT Users.Id AS UserId
                    FROM   (SELECT p.OwnerUserId
                            UNION /*collapse duplicate to single row*/
                            SELECT p.LastEditorUserId) D1(UserId)
                           JOIN Users
                             ON Users.Id = D1.UserId) D2
OPTION (FORCE ORDER)

Dependant on what indexes are available on the Posts table a variant of this query may be more efficient than your proposed UNION ALL solution. (the copy of the database I have has no useful index for this and the proposed solution does two full scans of Posts. The below does it in one scan)

WITH Unpivoted AS
(
SELECT UserId
FROM dbo.Posts
UNPIVOT (UserId FOR U IN (OwnerUserId,LastEditorUserId)) Unpivoted
)
SELECT DISTINCT Users.Id
FROM dbo.Users INNER HASH JOIN Unpivoted
       ON  Users.Id = Unpivoted.UserId
WHERE Users.Reputation = 5


The most relational way is adding a day table. You will be able to join room, availability and day to find out which rooms are available for a certain period.


Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

Scalar UDFS must be qualified with schema. You don't need the database part of the qualified name unless it's in a different database of course.

SELECT [dbo].[fnIsReportingTo] (50,1132)

In the FROM clause, you can only use table valued functions

See "Types of Functions" in MSDN

Edit: As an observation, I'd tend to avoid:

nesting UDFs this
using table lookups in scalar UDFs

if you use this function for each row of another table you have a cursor that iterates over the table in the UDF for every outer row

Even this is better. It's now table valued and expands like a view.

create function fnIsReportingTo (
    @BossID int,
    @EmployeeID int)
RETURNs table
AS
return (
      select CAST(COUNT(*) AS bit) AS FooBar
      from dbo.fnGetEmployeeHierarchy(@BossID)
     where employeeID=@employeeID)
GO


how to retrieve mongodb admin password installed on AWS EC2

You can't retrieve existing passwords, but you can reset them assuming you have ssh access to the host and appropriate root or sudo permissions to edit the MongoDB configuration and restart the MongoDB service.

The exact steps may vary depending on your O/S and how you manage the MongoDB server, but the general process for resetting a password on a standalone MongoDB server would be:

Take a backup of the configuration file used by your used by your MongoDB service (typically /etc/mongod.conf for Linux installations).
Edit the current configuration file to set:

security.authorization to disabled
net.bindIp to localhost (you don't want to allow remote connections while access control is disabled)

Restart your MongoDB service using the relevant command for your O/S (typically sudo service mongod restart in Linux).
Login to mongod locally without auth using the mongo shell: mongo localhost/admin.
Reset the password for your admin user: db.changeUserPassword('myAdminUser','New&secur3').
Replace your MongoDB configuration file with the original that you backed up in the first step.
Restart the service: sudo service mongod restart.


syntax error at or near TO (ROLLBACK TO SAVEPOINT)

You cannot use transaction management statements in PostgreSQL functions.

Limited support for that was added in procedures, introduced in v11.

But you can (implicitly) use savepoints by using an EXCEPTION clause in a block.

So instead of

LOOP
   SAVEPOINT a;

   /* something */

   IF /* something else */ THEN
      ROLLBACK TO SAVEPOINT a;
      /* still more */
   END IF;
END LOOP;

you should use

LOOP
   BEGIN
      /* something */

      IF /* something else */ THEN
         RAISE EXCEPTION 'catchme';
      END IF;
   EXCEPTION
      WHEN raise_exception THEN
         /* still more */
   END;
END LOOP;


First of all, DBA_SYNONYMS.SYNONYM_NAME is the name of the synonym.
DBA_SYNONYMS.TABLE_NAME is the name of the object the synonym points to.
In your query, it is not the synonym that is INVALID, but the object that it points to.
It is not the synonym that needs to be fixed, but the object.

  If I try to recompile or drop these:

  ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

  It says:

  SQL Error: ORA-01031: insufficient privileges

  01031. 00000 -  "insufficient privileges"

  How could this be so? I'm logged in as SYSTEM.

It is a bug on your version.

  If I try to drop it:

  DROP PUBLIC SYNONYM GV_$LOADPSTAT; It says:

  SQL Error: ORA-01432: public synonym to be dropped does not exist

  01432. 00000 -  "public synonym to be dropped does not exist"

  How can it not exist when the query result shows it?

Then name of the synonym is GV$LOADPSTAT. You are trying to drop the object (view) here, called: GV_$LOADPSTAT. And that view is owned by SYS, not SYSTEM.

Where you see MISSING, the synonyms points to a non-existent object. Replace the object or drop the synonym.


Try unloading and then reloading the database - if that runs OK then the re-built database will be OK. Look at the docs for details of the dbunload.exe utility.

If you need more help then you can try posting on the dedicated SQL Anywhere forum http://sqlanywhere-forum.sap.com/, but don't forget that v5.5 hasn't been officially supported since 2002 - current version is v16!


Instead of trigger Sqlserver and Inserted table

Correct, that is because the Trigger is just part of the entire transaction.  The INSERTED table will hold the records that would have been inserted.

The difference between AFTER triggers, which may be causing this confusion, is that INSTEAD OF doesn't verify if the row insert would have caused failures due to any constraints present on the table. If an insert causes a PK violation for instance, the INSTEAD OF trigger would still fire, the AFTER trigger would not.

Typically, Instead Of triggers are used to perform additional validation or calculations on the data (from the inserted table) before doing the final insert or rollback or raiserror depending on the business logic you are trying to implement.


What security vulnerabilities are there with IDENTITY_INSERT and DBCC CHECKIDENT

DBCC CHECKIDENT

  Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

MSDN DBCC CHECKIDENT reference

IDENTITY_INSERT

  User must own the table or have ALTER permission on the table.

MSDN Set IDENTITY_INSERT reference

When a user can delete, update, insert, and select records, you are granting the user the ability to manipulate the data within the table.

When a user has alter, they can adjust the columns, data types, seeding information, and much more. Best practice dictates lowest amount of permission necessary to achieve a function so that there is less opportunity that can be exploited.

Now if you can already modify the table, I'd be interested to see how he locked the permissions down so you could not run at least the IDENTITY_INSERT commands.

The least amount of permission you need to achieve these goals is DB_DDLADMIN which can open some security implications across the database server. You would also need ALTER TABLE privileges, this is less powerful than DB_DDLADMIN since it can be isolated to a single table and not server wide, but not without potential vulnerabilities.


How can I parse mysql bin log file for statements (Row/Mixed Format)

When you have binlog_format=MIXED some statements will appear as actual SQL. Other statements may get obfuscated like this:

### INSERT INTO `sanma`.`response`
### SET
###   @1=743096223
###   @2=0
###   @3='IR'
###   @4='UnderProcess'
###   @5=2015-05-29 20:51:34
###   @6=743053329
###   @7=NULL
###   @8=NULL
###   @9='758484829'
###   @10=1432912893
###   @11=758484829
###   @12='UnderProcess'
###   @13=0
###   @14=NULL
###   @15=NULL
###   @16=NULL
###   @17=NULL
# at 20535714
#150529 20:51:33 server id 71  end_log_pos 20535741     Xid = 84151212
COMMIT/*!*/;

That statement was possible to see because the INSERT ... SET syntax was used.

Other statements may get obfuscated beyond recognition. This is the risk of using MIXED or ROW.

I have dealt with this recently

Jun 02, 2015 : Why binlog capture only postions
May 15, 2015 : Show sql statements from mysql binlog (GTID)

The only thing you can do is

# Look for INSERT, UPDATE, DELETE statements
mysqlbinlog ... > statements.sql
grep -i "^[IUD][NPE][SDL][EA][RT][TE]"     statements.sql  > dml.sql
grep -i "^### [IUD][NPE][SDL][EA][RT][TE]" statements.sql >> dml.sql

There are no guarantees you will catch every change in statement form when mysqld switches to binlog_format ROW intermittently.


Check for records that break a foreign constraint (created with foreign_key_checks = 0)?


What is the impact of large varchar data types on indexing?

The very first thing to ensure is what the requirements for the size of the data is.  If it will ever be above 900 then you can't create this index since an insert of more than 900 characters will fail.

Here's an example.

As far as size of the index goes, no, the size of the varchar data type won't affect how big the index is.  Because the data type is varchar, it will only take up the size of the actual data.  If the data changes a lot, though, you will get fragmentations depending on your index fill factor because the allocation for the data will have to grow if more characters are added.


Is there a way to tell optimizer not to use index for reads?

Other than using query hints to force different indexes, or using Query Store to force plans that don't use the index, no.  There's no way to "hide" an enabled index from the query optimizer.


Reducing table scans using group by and window functions

SELECT    DISTINCT a.col_a
         ,b.col_c
FROM      tab_a a
outer apply (select top 1 b.col_c
             from tab_b b
             where ((a.col_a = b.col_a
                     AND b.col_b = 'blabla1' )
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla2')
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla3'))
                  and b.col_c is not null
            order by b.col_b)b;

This solution has 1 tab_b scan but adds sort because you want to choose b.col_c as in your COALESCE. In the example above this order corresponds to the order given by your constants in join condition that correspond to values of c column. In case when the order should be different the thing will be more complicated as you should write customized order byclause.


ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

Thanks to Wernfried Domscheit suggestion, this solved the issue :

ALTER SESSION SET nls_length_semantics = CHAR which was decisive
CHARACTERSET WE8ISO8859P1 which exactly fits the source file characterset

Note that our Oracle 11g instance is configured with following defaults:

NLS_LENGTH_SEMANTICS = CHAR
NLS_CHARACTERSET = UTF8


Create database link on Oracle Database with 2 Databases on different machines.

You can define the connection string via tnsnames.ora then reference the alias

remotedb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

Then create a dblink referencing that alias:

CREATE DATABASE LINK remotedb
    CONNECT TO SYSTEM IDENTIFIED BY <password>
    USING 'remotedb';

Or facilitate the same inline with:

CREATE DATABASE LINK remotedb
CONNECT TO SYSTEM IDENTIFIED BY <password>
USING'(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL)))';

For details on syntax, check http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm

If you want to avoid DNS resolution, just substitute the IP address for remotedb.fqdn.com instead.  Of course, you'll want to check that the source can initiate TCP connections to the remote port 1521:

nc -zv remotedb.fqdn.com 1521


SQL Server Standard License over written because of Report Builder Evaluation version

The person is wrong; that is clearly an Evaluation Edition install, not Standard Edition. The SKUs are all on the same ISO typically, so even if you download an ISO that says Standard Edition, you can still choose Evaluation or Express during install.

Report builder should not cause this situation; however, it could also happen if you install a Standard Edition engine, and then install Management Studio from an evaluation download (for the same or different version than the engine).


Yes, it's safe to delete them.
If you're running on Linux, set up logrotate to compress & then delete after a set period of time. Examples of Oracle logrotate configuration can be found here.


How can I get identity-like values in a MERGE INSERT statement

Let me set up next example:

CREATE TABLE foo(messagenr int, language varchar(10), message varchar(100));
CREATE TABLE bar(language varchar(10), message varchar(100));
GO

INSERT INTO foo VALUES
(1, 'EN', 'M1'),
(2, 'EN', 'M2'),
(3, 'ES', 'M3');

INSERT INTO bar VALUES
('FR', 'M1FR'),
('CA', 'M1CA'),
('DE', 'M1DE');
GO

Since you can't increase messagenr value inside the transaction, and your MERGE statement only insert values, maybe you could use another approach.

You can use a CTE or a subquery to enumerate the returned rows in this way:

SELECT (SELECT MAX(messagenr) FROM foo) +
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
       bar.language,
       bar.message
FROM   foo
JOIN   bar
ON     bar.message LIKE '%' + foo.message + '%'

RN | language | message
:- | :------- | :------
4  | FR       | M1FR
5  | CA       | M1CA
6  | DE       | M1DE

Now you can insert new values using the previous query:

;WITH x AS
(
    SELECT (SELECT MAX(messagenr) FROM foo) +
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
           bar.language,
           bar.message
    FROM   foo
    JOIN   bar
    ON     bar.message LIKE '%' + foo.message + '%'
)
INSERT INTO foo (messagenr, language, message)
SELECT RN, 'EN', message
FROM   x;

This is the final result:

SELECT * FROM foo;

messagenr | language | message
--------: | :------- | :------
        1 | EN       | M1
        2 | EN       | M2
        3 | ES       | M3
        4 | EN       | M1FR
        5 | EN       | M1CA
        6 | EN       | M1DE

dbfiddle here


Assign different RAM usage (buffer pool size) per db in mysql

I have written a few posts on loading index pages into the InnoDB Buffer Pool

Feb 04, 2012 : MySQL warm procedure
Nov 21, 2011 : Cache MySQL database in memory

Here is an example of a SELECT that will generate all possible SELECTs you would want to run to read InnoDB Index Pages:
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

The main idea with these posts is this: Run SELECT queries whose columns are indexed. This will force index pages into the InnoDB Buffer Pool. Unfortunately, when doing SELECTs against non-indexed columns will push out old index pages out of the InnoDB Buffer Pool. There is no perfect solution for this but to get as many InnoDB index pages as possible.
You may want to limit the tables in the WHERE clause to a specific list of tables.
UPDATE 2021-02-02 10:13 EST
I have some additional advice: If the InnoDB tables don't change all that often, you may want to take a snapshot of the InnoDB Buffer Pool. This is on by default in MySQL 8.0.
For those using MySQL 5.7 and back, please do the following:
STEP 01 : Configure MySQL to Dump the InnoDB Buffer Pool on Shutdown and Load the InnoDB Buffer Pool on Startup
Add these two lines to my.cnf under the [mysqld] group header
[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

STEP 02 : Configure Running Instance to Dump the Buffer on Shutdown
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

STEP 03 : Dump the Buffer Pool Now
SET GLOBAL innodb_buffer_pool_dump_now = 1;

That's it. The dump of the Buffer Pool is just a binary file with a list of tablespace IDs. The size of such a file is usually in the 100K-120K range and dumps in seconds.


Error when running SQL Server 2014 64-bit Import/Export Wizard

I was able to resolve the issue by uninstalling and reinstalling the entire Visual Studio and SQL Server software stack on my laptop.

The cause of the problem appears to be with the anti-virus that corporate security has setup on my laptop. Apparently, the group policy is too restrictive and blocking the update from successfully performing its job. There are no failures or warnings to this fact. I was able to isolate this as the cause because I have an identical setup on remote desktop servers that I can log in to. I was able to successfully update VS2017 to 15.9.9 and edit script tasks/components without error on the remote desktops.

Upon inquiring with our corporate security team as to whether my group policy matches the servers, it was confirmed that mine did not match, which is not necessarily a problem - user and server policies are different. However, it was confirmed that I was in the wrong user group policy. I was switched into a different policy and the installation was successful. This further backs my suspicion of virus scanner interference because I had to previously ask that the virus scanner be disabled so that I could perform re-installations (this was the third time that I had to reinstall the stack).


Really what you're asking for is how can I deploy code/schema/data to a Oracle DB in a remote server, from a Git repo.  This can also be known as orchestration if done as part of a bigger process, or automated code deployment which leads to Continuous Integration/Deployment.

There are there are tons of solutions from your own homebrew options such as scripting it out.  The more commonly used tools for this are Jenkins, and you use .Net, Octopus is great.  Easiest solution is to write a Python/Powershell/whatever script to pull down the latest build, validate it, and execute it as statements then evaluate the return.

These tools work similarly together.  They often provide a web UI for you to communicate with multiple machines across domains and trusts with agents and users executing remotely or directly on the machine as the user.

For example, Octopus which works for .Net, deploys an agent known as a tentacle on the machine that all actions are executed under and under it's user on the remote machines (Oracle DB Server).  You can then have actions such as 'git pull' directly to your prod ops folder, then the next step verifies it and passes in variables directly from Octopus and continues executing statements.

Moving fwd, you could use these  as a central part of moving to the SDLC best practices involving C.I.   Jenkins could be used in similar methods for other software.  It is used extensively in management software such as Cloudera Manager to manage big Hadoop clusters.


You have to rewrite the second View as follows:

create view `Months` as
    select N+1 MonthNum from `Numbers`
    union all select 11
    union all select 12
;

Here is proof

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create view `Numbers` as
    ->     select 0 N union all select 1 union all select 2 union all
    ->     select 3 union all select 4 union all select 5 union all
    ->     select 6 union all select 7 union all select 8 union all select 9;
Query OK, 0 rows affected (0.05 sec)

mysql> create view `Months` as
    ->     select N+1 MonthNum from `Numbers`
    ->     union all select 11
    ->     union all select 12
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>

Behold the output of the Query

mysql> select * from Months;
+----------+
| MonthNum |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
|       11 |
|       12 |
+----------+
12 rows in set (0.00 sec)

mysql>

Give it a Try !!!


MySQL row length is almost the triple of what I expect

I don't think I've ever come across any description of how ndbcluster affects row layout. (I haven't looked hard, though. Also haven't read the source code.)  But, for example, innodb tables have a well-documented row layout. (Innodb row structure.)  You can find quite a few things that affect the overall length of a row.


How to create a SQL Server 2008 backup without logs with mirroring

Backing up a database (BACKUP DATABASE ...) backs up the pages from all of the database files (data and log) and upon restore those files will be recreated as a result.

A full database backup does just that:  It backs up the database in its entirety.  There is no way to get around not backing up the transaction log file.  If you are in full recovery mode, then you should ensure you are doing the proper transaction log backups so the log doesn't grow out of control.  Outside of that, you are constrained with a full backup.  A database consists of data file(s) and log file(s).

A workaround to this could be just to generate scripts from you database.  When you right-click your database to generate scripts, you can script out the entire database.  There is an option to script out both Schema and Data.  Then on your other machine/instance you can run this script.  It's not a traditional "backup" by any means, but it should get you what you want and you'll just have a script file as a result.  Mind you, when the CREATE DATABASE portion of the script runs, a log file will be created.  You can't have an RDBMS database without a log file (or at least not on SQL Server that I know of).


Automatic insertion of calculated values to another

I've reduced your table schema in this way:

CREATE TABLE AddItems
(
Item_ID int IDENTITY PRIMARY KEY,
Quantity int NOT NULL,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL
);

CREATE TABLE Products
(
Product_Id int IDENTITY PRIMARY KEY,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL,
Available_Quantity int NOT NULL DEFAULT(0)
);

You can add a trigger like next one:

CREATE TRIGGER trg_AddItems_Insert
ON AddItems
FOR INSERT
AS
BEGIN

    /* update existing products */
    UPDATE   p
    SET      Available_Quantity = Available_Quantity + Qty
    FROM     Products p
    JOIN     (SELECT   Item_code, SUM(Quantity) Qty
              FROM     inserted
              GROUP BY Item_Code) s
    ON       p.Item_Code = s.Item_Code;

    /* insert new products */
    INSERT INTO Products
    (Item_Code, Item_Description, Available_Quantity)
    SELECT   i.Item_Code, i.Item_Description, Sum(Quantity)
    FROM     inserted i
    WHERE NOT EXISTS(SELECT 1
                     FROM Products
                     WHERE Item_Code = i.Item_Code)
    GROUP BY i.Item_Code, i.Item_Description;

END
GO

This solution assumes AddItems table won't be modified or deleted, it is only used to add new rows. If some user can UPDATE or DELETE rows, you should consider either:

Add one trigger for DELETE and another for UPDATE.
Use stored procedures to INSERT/UPDATE/DELETE rows.

Now, let me insert some values:

INSERT INTO AddItems VALUES
(10, 'AAA', 'PRODUCT A'),
(5,  'AAA', 'PRODUCT A'),
(7,  'CCC', 'PRODUCT C'),
(1,  'BBB', 'PRODUCT B');

INSERT INTO AddItems VALUES
(25, 'AAA', 'PRODUCT A'),
(10, 'BBB', 'PRODUCT B'),
(3,  'CCC', 'PRODUCT C'),
(5,  'CCC', 'PRODUCT C');

And this is the final result:

SELECT * FROM PRODUCTS;
GO

Product_Id | Item_Code | Item_Description | Available_Quantity
---------: | :-------- | :--------------- | -----------------:
         1 | AAA       | PRODUCT A        |                 40
         2 | BBB       | PRODUCT B        |                 11
         3 | CCC       | PRODUCT C        |                 15

db<>fiddle here


How to remove datafile from oracle database which is already remove locally?

You can try:

shutdown immediate
startup mount
alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop;
alter database open


How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I am not sure if this is really more efficient, but you could try to join to a derived table that applies the limit.

select emp.employee_id, emp.salary, adr.address_arr
from (
  SELECT employee_id, salary, address_arr
  FROM employee
    LEFT JOIN payroll on payroll.employee_id = employee.employee_id
  WHERE employee.employee_id < 1000000
  LIMIT 100
  OFFSET 0
) as emp
  JOIN (
     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr
     FROM addresses a
     GROUP BY employee_id
  ) as adr ON adr.employee_id = emp.employee_id;

The first derived table only selects 100 rows, and the join/group by should then only be done for those 100 employees.

If the optimizer doesn't push that down, you could try a lateral join instead to "force" a push down:

select emp.employee_id, emp.salary, adr.address_arr
from (
  SELECT employee_id, salary, address_arr
  FROM employee
    LEFT JOIN payroll on payroll.employee_id = employee.employee_id
  WHERE employee.employee_id < 1000000
  LIMIT 100
  OFFSET 0
) as emp
  LATERAL JOIN (
     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr
     FROM addresses a
     WHERE a.employee_id = emp.employee_id
     GROUP BY employee_id
  ) as adr ON adr.employee_id = emp.employee_id;

The join condition isn't really needed, but it dosn't hurt either


SQL Server (2014) Failover Cluster Performance with many databases

1.Performance during a failover: Will there be an outage during the failover operation?

Yes there would be some outage during cluster failover.. During failover SQL Server is stopped on current node and failedover/moved to other node. After this SQL Server is brought online on the node to which it is failed over. Sometime this is so quick that application users might not notice. But the fact that SQL Server is restarted means you should always keep in mind that there might be some outage

  How long can I expect the failover to take?

There are lot of factors that influence how long it would take for failover cluster to come online on other node. The factors are (but not limited to)

Edition of SQL Server. Fast recovery which is enterprise edition feature could bring database online after second phase(redo) of recovery
Amount of transaction log SQL Server has to rollforward and rollback to bring database online in consistent state. Because database would go through crash recovery this would always be the one influencing the time most. If database takes lot of time to recover due to large amount of uncommitted transactions gettign rolled back it would definitely increase your time.
The hardware strength and network capabilities supporting the cluster. Some of this is documented here.


How to use trigger to perform addition from one table columns into another table column where id matches Sqlite

You can add a trigger to your python script

CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT
    )

CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT
    )

INSERT INTO sum(id, name, total)
    VALUES('001', 'name1', '');

INSERT INTO sum(id, name, total)
    VALUES('002', 'name2', '');

CREATE TRIGGER sum_after_insert
   AFTER INSERT ON newtable
BEGIN
  UPDATE sum SET total = total + NEW.num WHERE id = NEW.id;
END;

INSERT INTO newtable(id, name, num)
    VALUES('001', 'name1', '1000');

INSERT INTO newtable(id, name, num)
    VALUES('002', 'name2', '2000');

INSERT INTO newtable(id, name, num)
    VALUES('001', 'name1', '4000');

SELECT * FROM sum;

id  | name  | total
:-- | :---- | :----
001 | name1 | 5000
002 | name2 | 2000

db<>fiddle here


How to stop the execution of a long running insert query?

Do you mean by "kill / stop the MySQL instance" the linux command kill -9 ...?
Yes, this might lead to corrupt data.

Which instead should not leave corrupt data is the build-in kill command of MySQL. See the lower part of the page, which states that it might take the thread some time to actually notice the presence of the kill flag. The part which states it does not roll back updates when transactions are not used (which implies that it rolls back updates if transactions are used), etc. I therefore assume this does not corrupt any data (besides killing repair and optimize as noted there).

You can find out the thread id you need for this command with the command show processlist.


Trying to support unicode in MySQL database

You need utf8mb4 instead of utf8 to get  and a number of other emoji.

You need SET NAMES utf8mb4 after connecting.

You also need to make sure SHOW CREATE TABLE says utf8mb4.

Your "this link tutorial" seems to be broken.  I would like to see what is says.  (And perhaps find an error in it.)

More discussion of utf8mb4.


Determine a set of id's that exist in a set of tables: options needed to speed up or parallelize the algorithm

This belongs on dba.stackexchange.com  but I don't have the rep to comment
(and the reason I don't have the points is I gave out a 100 point bounty)

You don't give the sql an algorithm.  You give it indexes and a good query (some times with query hints).  SQL will parallelize on its own.

PartyID would be better as an int and then have another table for the text
Int or VarChar put and index on it

select table1.partyID
  from table1
  join table2
    on table2.partyID = table1.partyID
  join table3
    on table3.partyID = table1.partyID

for an or just do do a union

select table1.partyID
  from table1
union
select table2.partyID
  from table2
...


This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

Related answer with more explanation:

Find "n" consecutive free numbers from table


Try to add %h to log_line_prefix parameter (see https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX): it should display the host name or IP address.


How to extract xml attribute from a column?

The XQuery expression in the value() function must return at most one value.

Your expression EventFileTarget/file/@name[1] is saying that from all EventFileTargetnodes you want all file nodes and from those nodes you want the first name attribute.

Given this sample XML:

<EventFileTarget>
  <file name = "FileName 1"/>
  <file name = "FileName 2"/>
</EventFileTarget>
<EventFileTarget>
  <file name = "FileName 3"/>
  <file name = "FileName 4"/>
</EventFileTarget>

Your expression returns all four filenames.

Change the XQuery to (EventFileTarget/file/@name)[1] to get the first name attribute with the path EventFileTarget/file.

With XML that looks like this

<EventFileTarget>
  <file/>
  <file/>
</EventFileTarget>
<EventFileTarget>
  <file/>
  <file name = "FileName 4"/>
</EventFileTarget>

The result (EventFileTarget/file/@name)[1] is FileName 4.

In your case there can probably only be one EventFileTarget node and only one file node but SQL Server does not know that so you have to build the XQuery to guarantee only one value returned. (EventFileTarget/file/@name)[1].


Table structure and query for messaging between users

For your main question:

You should be able to use a case statement to get just the other person's name:

SELECT m.*, CASE WHEN u2.id = 1
              THEN u1.name
              ELSE u2.name
            END as participant
FROM messages m
JOIN users u1 ON m.sender_id=u1.id
JOIN users u2 ON m.recipient_id=u2.id
WHERE m.id IN (
SELECT MAX(id)
FROM messages
WHERE sender_id = 1 OR recipient_id =1
GROUP BY conversation_id) ORDER BY m.id DESC
;

If the user id from u2 is the ID of the user whose information you're retrieving, then you want the name from u1. Otherwise, you want the name from u2. This should work even if users can send messages to themselves.


The resident memory size represents the number of pages in memory actually touched by the mongod process.  If that is significantly lower than the available memory and data exceeds the available memory (yours does), then it could be a case of simply not having actively touched enough pages yet.

To determine if this is the case, you should run free -m, the output should look something like this:

free -m
             total       used       free     shared    buffers     cached
Mem:          3709       3484        224          0         84       2412
-/+ buffers/cache:        987       2721
Swap:         3836        156       3680

In my example, cached is not close to the total, which means that not only has mongod not touched enough pages, the filesystem cache has not yet even been filled by pages being read from the disk in general.

A quick remedy for this would be the touch command (added in 2.2) - it should be used with caution on large data sets as it will attempt to load everything into RAM even if the data is far too large to fit (causing a lot of disk IO and page faults).  It will certainly fill up the memory effectively though :)

If your cached value is close to the total available, then your issue is that a large number of pages being read into memory from disk are not relevant to (and hence not touched by) the mongod process.  The usual candidate for this kind of discrepency is readahead.  I've already covered that particular topic elsewhere in detail, so I'll just link those two answers for future reading if necessary.


Constraint violation in a trusted constraint

From comments:
This might happen due to the the value physically written on the page being incorrect (data corruption). Or a bug in the product (probably less likely but possible). Run DBCC CHECKDB to see if that finds any issues. – Martin Smith Jun 18 '16 at 0:07
3
Along Martin's comment - did the data get into the table via MERGE? Here is one bug (closed as "Won't Fix") that can do exactly this, and another bug (also "Won't Fix"). If you need more reasons to avoid MERGE, see this article. – Aaron Bertrand♦ Jun 18 '16 at 3:21


I've ran into this same problem! As Mr. Vernon stated, the account needs admin access to the server.  However, that is not the recommended way of providing access to the service account, especially if you're to follow the "least privilege" security mantra.

Instead, perform the following:

Create an appropriately named security group
Add the SQL Server Agent account to the group
Create a shared folder on the server you're shipping the logs to
Assign the newly created security group with Modify permissions to the folder


Rather unfortunately ... https://www.sqlservercentral.com/articles/20-tips-on-converting-mysql-views-to-ms-sql

  Although there are many free tools and solutions to migrate MySQL data to MS SQL server, none of them is able to convert views. That's why everybody who wants to migrate complete database have to convert views manually ...

After that they give 20 tips on how to convert the view-declaration so that mssql understands it. Unfortunately this means you have to work view by view .. by hand


Most efficient way to join two tables with MAX value

You need to think about how you would like to solve this if you had a paper-based system.

Like - do you want to use each ProductID one by one and find the newest one? Then your Nested Loop that calls a Seek using TOP 1 on an Index on (ProductID, Transaction Date DESC) will be best.

Do you want to search through all your transactions and group them by ProductID and grab the max? Then that approach could be best.

But I'd suggest to you that what you'd do in real life is to do the second approach on a subset of your data (such as the most recent month), and then do a product-by-product search on the ones that didn't work. Try an outer join onto a subquery that groups by ProductID and filters by TransactionDate to "fairly recent". Then do an Outer Apply to do a Top 1, but include a Predicate in there like where m.ProductID IS NULL, so that it only does those Seeks for products that were missed in the first round. You should see a Filter with a Startup Expression Predicate, and notice that the Index Seek is executed a lot fewer times.

I show this kind of thing at http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/ - look for the plan I show, on the example with two Outer Apply bits.


use function arguments or variable in postgres function

I have found the solution by using function triggers. I didn't dynamically change the default value but I used trigger function to update the column value before insert action. It can achieve the same result.

Create or replace function update_value()
Returns Trigger AS $$
BEGIN
IF NEW.floor ISNULL THEN
    NEW.floor :=(select floor from buildings where my_current_location='yes');
END IF;

IF NEW.building ISNULL THEN
    NEW.building :=(select name from buildings my_current_location='yes');
END IF;
RETURN NEW;
END $$ Language 'plpgsql';

Also you need to add a trigger to the table:

Create triggers update_value_trigger Before Insert On points For each row execute procedure update_value();


Better parallelization than a sequence container in SSIS 2008?

Assuming I understand the situation, you have lots of source data to process but it all needs to go through a slow destination (stored procedure) then there's a native component, Balanced Data Distributor, that might be of interest.

As a row flows into a BDD, it can detect whether the downstream consumers of data are busy (whether there is backpressure). It's somewhat similar to a Multicast in that you can have multiple output paths but you won't have duplicate data flowing into them. Instead, it'll be using backpressure detection to know whether a worker is available. That should address the issue you're encountering with your manual segmentation of the problem.

It doesn't come preinstalled with SSIS so you do have to work through that but it is a MS supported component.


How to set an Oracle user's default schema?

There is nothing like PostgreSQL's set search_path in Oracle.

The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ...

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
EXCEPTION
  when others
    then null; -- prevent a login failure due to an exception
END;
/

If the list of users isn't too long, you can create a database logon trigger so you don't have to create that trigger for each user:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('TOM', 'DICK', 'HARRY')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
  when others
    then null; -- prevent a login failure due to an exception
END logon_trg;
/

Of course the list of users where you want to change the default schema, can also be taken from a table. In that case you only need to insert or delete rows from there in order to "activate" this feature (rather than re-creating the trigger each time).

Another option would be to create synonyms each time you create user that point to the real tables. You could automate that using a stored procedure that loops through all tables in one schema and creates the synonyms for them in the other schema.

Unless all your Oracle users work on the same tables I would strongly advise against using public synonyms which you would have to create only once - they can cause a lot of trouble if different application users exist in your installation.

Edit:

Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
declare
  has_role boolean;
BEGIN

    has_role := dbms_session.is_role_enabled('FOOBAR_ROLE');

    if (has_role) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
   when others
      then null; -- prevent a login failure due to an exception
END logon_trg;
/


How to prevent informational messages from getting written when query results are output to a file

Apparently "someone" create a psqlrc.conf file that contains those messages you are seeing.

You can prevent psql from loading psqlrc.conf by passing the parameter --no-psqlrc (or -X). To hide the any other messages use the --quiet (or -q) parameter.

So your batch file should contain:

"C:\Program Files\PostgreSQL\10\bin\psql.exe" --no-psqlrc --quiet --host localhost ...


Instant synchronization of multiple SQL servers

I may be understanding this slightly wrong so I apologise if I am.

If you have two databases that you need to be identical and are on 2014 then use the AlwaysOn High Availability Group.

Since you're data centres are at separate locations use the Async mode

This will mean the database is kept completely up to date (all be it possibly with a few second delay) and you can have the secondary node as a read-only replica, this means that your alarm system can read into that database run all the checks etc you would normally.

the Always on system keeps everything up to date, so if the connection drops, when it comes back online it will merge over all changes

It also means that if your main centre goes down you can set it to automatically failover to the secondary, when the main datacentre comes back online it will re-sync with the (now) primary node, at which point you can fail it back over to your main centre.

You can run this on multiple databases, so we have our main DB and our Admin DB synced across our nodes, however what runs all the jobs and direct actions on each side is not replicated so can stay independent of each other


MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID

Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem.

FYI. It applied to MySQL 5.6 and 5.7.

See 8.2.1.16 LIMIT Query Optimization

If you want to order by primary key, you have to say so explicitly.


Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

I solved this (for single inheritance) like so:

each extension of a class inherits, as its primary key the base classes primary key and always targets with its fk the immediate base class:

class A
class B extends A
class C extends B

A(id,p1)
B(id,p1,p2)
B(id,p1,p2,p3)

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p2: sth

Table C:
id: primary key + foreign key -> B.id
p3: sth

this would reqiure a join to completely retrieve B or C.

alternatively you can have each extension mirror the properties of the base classes:

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p1: sth
p2: sth

Table C:
id: primary key + foreign key -> B.id
p1: sth
p2: sth
p3: sth

here you can retrieve any class without joins but data is duplicated and has to be kept consistent.

which solution you can use strongly depends on how you want to retrieve the data and if you can live with the overhead of the join in version 1 or overhead of writing data multiple times in version 2.

I also added a meta-column to the table to know if a row does have extensions or not which is useful for a some queries:

Table A:
id: primary key
p1: sth
class: A,B,C

Table B:
id: primary key
p2: sth
class: B,C

Table C:
id: primary key
p2: sth
class: C


Patterns for Deleting large amounts of data

but you are missing RequestID
hopefully you have index on URLID  and RequestID

select 1
while (@@rowcount > 0)
BEGIN
  DELETE TOP (50000) b
  FROM TableB b
  JOIN TableA a
    ON b.URLID = a.URLID
   AND a.RequestID = @RequestID
END

Disable FK can help but be sure you are not breaking any FK


SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

I am going to assume you are looking for the definition and the root cause of these events.

From How It Works: SQL Server 2005 SP2 Security Ring Buffer – RING_BUFFER_SECURITY_ERROR (archive):

  SQL Server 2005 SP2 added new ring buffer entries (sys.dm_os_ring_buffers) for various security errors. The reason the ring buffer entries were added was to provide the DBA with more details as to why a client is receiving a failed login or other such error.

You state that you have no failed login entries in the event log nor in the error log. Instead you can query this ring buffer directly:

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
dateadd (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time],
a.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC

The notification time might shed some light on the root cause.

I think you will find that the date/time of the entries will line up with login failure entries in the errorlog similar to:

  "Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ] Error: 18456 Severity: 14 State: 11."

From Troubleshooting specific Login Failed error messages (archive) :

  State 11 corresponds to “Valid login but server access failure” which indicates that the login is valid but is missing certain security privileges which would grant it access to the instance.

  Check if that login is directly mapped to one of the SQL Server logins by looking in the output of sys.server_principals.
  If the login is directly mapped to one of the available logins in the SQL instance, then check if the SID of the login matches the SID of the Windows Login.

If someone dropped the login at the Windows/AD level, and added it back, it will get a new SID which won't match the SID SQL has stored in its system catalog and it will fail.


Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2

Seems that changing the AG to not have WITH DTC_SUPPORT = PER_DB  works. Microsoft have been informed. Looking forward to a fix in a future SP2 CU.


No advantage of using Cross Apply or CTE over inline sub-query

But the execution plan for both is same as shown below:

The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact.

There are usually many ways to express the same requirement (or similar requirement, as in your example) in SQL. Which you use is initially a question of preference and style. In some cases, using one or the other will produce important performance differences because the declarative SQL takes a different code path through the optimizer. In this particular case, the outer join may play less well with the optimizer's exploration abilities (it has fewer tools to use with outer joins than inner joins).

Rewriting a query to define the same results using different syntax can be a valid tuning method, but it requires careful attention to detail and retesting whenever SQL Server is patched or upgraded. There is generally no reason to prefer one way of expressing a requirement in SQL over another.

In addition, as Andriy mentioned in a comment on the other copy of your question, "in the more general case, your inline queries will give you only one result per row, while joining to a CTE (which doesn't have to be a CTE, it could be a normal subselect) or CROSS APPLYing a row set can give you access to more than one column."


SELECT COUNT(*) returning incorrect results

This certainly looks like a corrupt index.  First things first, take a DB snapshot and store it someplace which no one has write access to.

This being RDS Aurora and not the community PostgreSQL, your first (and likely last) recourse would be to talk to AWS support.

You can try to reindex the entire database if you want, but if there is corruption in the indexes there might be corruption in the tables as well.  And of course if you executed any data modifications which failed to update all the rows they should have, or if you modified any data based on business decisions which relied on queries with the wrong results, this type of corruption will be invisible.  Re-indexing (or any continued use of the database) could destroy evidence that could be useful in forensic analysis of the corruption, hence the "first things first" above.

If you set up your data retention so that you can do point-in-time recovery into the far past, you could do tests to see how far back in time any specific instance of corruption appears.


Does dbms_lob.getlength return the compressed or uncompressed length?

yes , it return them for example at the below:

SET SERVEROUTPUT ON
DECLARE
l_in_blob            BLOB;
l_compressed_blob    BLOB;
l_uncompressed_blob  BLOB;
BEGIN
-- Set some values
l_in_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW
('This is a long string of words used for this example'));
l_compressed_blob   := TO_BLOB('0');
l_uncompressed_blob := TO_BLOB('0');

-- Compress the string
UTL_COMPRESS.lz_compress
(src => l_in_blob, dst => l_compressed_blob);

-- Uncompress the string
UTL_COMPRESS.lz_uncompress
(src => l_compressed_blob, dst => l_uncompressed_blob);

-- Compare the results with the input
DBMS_OUTPUT.put_line('Input length is    : ' || LENGTH(l_in_blob));
DBMS_OUTPUT.put_line('Compressed length  : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed length: ' || LENGTH(l_uncompressed_blob));

-- Caller responsibility to free up temporary LOBs
-- See Operational Notes in the documentation
DBMS_LOB.FREETEMPORARY(l_in_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

Input length is    : 52
Compressed length  : 67
Uncompressed length: 52

For more information you can use this site.


How to get back the PostgreSQL database to a specific point in time?

If you know which rows were accidentally updated, you just don't know what the previous value of those rows were, then what you can do is:

Restore to a new non-production server from your base backup.
Recover that server to the point just before the error.
Write a query to select the values for the rows you know got mistakenly updated.
Turn that into a series of updates which can be run against the original production server.
Spin up yet another server which has been recovered up to current date to test those updates against to make sure they do the correct thing
Apply to production.

Of course if people or software have been relying on the bad values between when the error was made and now, then reversing the error will not reverse the decisions they made based on bad data.


Strategies for rotating backend production MySQL data into a test environment

Plan A:  Have the Production system be a Master; have another machine (or two) be a Slave.  At any moment, you could use the Slave (in a couple of different ways) to provide a snapshot of Production at some point in time.  The impact on the Master (Production) is zero (after a somewhat painful setup).  Master-Slave is the beginnings of any HA (High Availability) solution.

Plan B:  Use LVM (Logical Volume Management, an OS tool) as a way of taking a snapshot of the Production machine.  This involves taking down the server briefly (about a minute, regardless of amount of data).  Them the disk can be copied to elsewhere for whatever use -- such as building a dev box.

Neither of those do what I think of as a "rotate", but perhaps they solve your request?

I suggest you search for either or both of those strategies, then come back for further questions, if needed.