PostgreSQL: Unable to run repmgr cloned database

I searched around and realized that the problem was due to not enabling hot_standby on the standby server. I wrote a corrected, updated and simplified version of the above article as an odt for future personal reference. Here is the text in its entirety (converted to html from odt using openoffice.org) for those who are interested in setting up a single read-only clone of a database using repmgr:

Single Slave Streaming Replication with
PostgreSQL

Introduction
This guide aims to quickly help you configure a PostgreSQL 9.1
server with a database, and have it replicated to a slave that can be
used for read-only queries. There is no concept of failover involved
here, and the slave will only have a read-only copy of the master's
data.

1. Tools Needed

    A stable GNU/Linux Distribution (Recommended OS: CentOS 6.2 x86-64)
    PostgreSQL 9.1 – you may install from here: http://yum.postgresql.org/ (also make sure that the following packages or their equivalnets are installed)

        postgresql
        postgresql-client
        postgresql-contrib
        postgresql-server
        postgresql-server-dev
    2 workstations – a master that runs the primary database
    and a slave that runs the replicated read-only database (for the
    purpose of this document, their respective IP addresses have been
    replaced with pgmaster and pgslave so
    make those changes in /etc/hosts of both machines if you want to
    follow the following instructions word for word)

2. Installation Check and Password Creation

    Run /etc/init.d/postgresql start on both systems to check if PostgreSQL is functional or not.
    Run /etc/init.d/postgresql stop on both systems to stop PostgreSQL. We will not be using PostgreSQL till we finish some configuration related tasks.
    Set a password for user postgres on both the systems. This user by default has no password, but we need a password to help create an SSH tunnel between pgmaster and pgslave.
    Run sudo passwd postgres on both systems and type a new Unix password for both.

3. SSH Tunnel Creation

    On the pgmaster do the following:
    su postgres
    ssh-keygen -t rsa (press enter at every prompt)
    ssh-copy-id -i ~/ssh/id_rsa.pub pgslave  (you need to enter pgslave's postgres password)
    ssh pgslave and see if you are able to login without password
    Repeat the above steps on pgslave
    su postgres
    sh-keygen -t rsa
    ssh-copy-id -i ~/ssh/id_rsa.pubaster
    ssh pgmaster and see if you are able to login without password
    Make sure you log out from the remote machine after you finish checking connectivity

4. Editing postgresql.conf on pgmaster
You need to make the following changes in the file
postgresql.conf that resides in the configuration
directory inside /etc/postgresql/
on your machine pgmaster:

    listen_addresses = '*'
    wal_level = hot_standby
    checkpoint_segments=30
    archive_mode=on
    archive_command='cd .'
    max_wal_senders=2
    wal_keep_segments=5000

5. Editing postgresql.conf on pgslave
You need to make the following change in the file postgresql.conf
that resides in the configuration directory inside /etc/postgresql/
on machine pgslave:

    hot_standby=on

6. Editing pg_hba.conf on pgmastger
You need to make the following changes in the file pg_hba.conf
that resides in the configuration directory inside /etc/postgresql/
on machine pgmaster:

    host all all 192.168.5.0/24 trust
    host replication all 192.168.5.0/24   trust

7. Adding PostgreSQL bin folder to PATH
There are a bunch of nifty postgresql utilities we will be using
here, so lets set the PATH variable so that it knows where to find
them

    Execute locate pgbench
    The output will be something like /usr/lib/postgresql/9.1/bin/pgbench
    Excluding final word pgbench, copy the above path and add it to your path variable.
    Execute nano ~/.bashrc
    add line export PATH+=:/usr/lib/postgresql/9.1/bin/ (or whatever location the locate command revealed)
    Close the file and save changes
    You may need to log out and login (or open a new shell) for changes to take effect.

8. Loading pgmaster's PostgreSQL server with
dummy data

    On pgmaster start the PostgreSQL server first: /etc/init.d/postgresql start
    We create a test database and load it with some dummy data with the following commands:

        su postgres
        createdb pgbench
        pgbench -i -s 10 pgbench

    Alternatively open the database pgbench yourself, create a sample table and insert sample data into it.

9. Erasing pgslave's data and checking pgmaster
connectivity

    We are going to erase data directory of pgslave, so execute following on that machine:
    First stop PostgreSQL server /etc/init.d/postgresql stop
    Move into the PostgreSQL default data directory folder: cd /var/lib/pgsql/data (or to the data directory that is default for your installation)
    Empty the directory completely with rm -rf *
    Now execute psql -h pgmaster -d pgbench and see if you are able to access the database on pgmaster through pgslave.

10. Installing repmgr
Though single slave replication is fairly straightforward, we are
using a tool called repmgr to help make the process even more simpler. Here is how you install it:

    Grab repmgr source code from http://projects.2ndquadrant.it/sites/default/files/repmgr-1.1.0.tar.gz
    and copy it to /tmp for installation.
    Have the following packages installed to ensure that compiling repmgr is possible:

        make
        gcc
        postgresql-devel
        libxslt-devel
        pam-devel
        libopenssl-devel
        krb5-devel

    Extract the downloaded archive and enter the source code folder.
    Compile with make USE_PGXS=1
    Install with make USE_PGXS=1 install
    Execute below two commands to check if repmgr is installed correctly:

        repmgr --version
        repmgrd --version

11. Cloning pgmaster onto pgslave using repmgr

    Execute su postgres and login as the postgres user on pgslave
    Run this to clone database: repmgr -D /var/lib/pgsql/data -d pgbench -p 5432 -R postgres --verbose standby clone pgmaster
    Note that the -D parameter /var/lib/pgsql/data should be replaced with the appropriate location of the data folder on pgslave
    Also note that 5432 is the default port PostgreSQL runs in.
    When the command finishes executing (it is going to take several seconds to finish if you had used pgbench to insert random data) you may start PostgreSQL on pgslave with /etc/init.d/postgresql start

12. Testing streaming replication
Congratulations! You have successfully configured pgslave
to copy pgmaster's database via
streaming replication. Note that due to being in continuous recovery
mode, pgslave can only be used to
execute read-only queries and no
insertions or modifications are possible.

    Insert some values into sample_table on pgmaster.
    execute select * from sample_table; on pgslave's psql prompt after connecting to pgbench database.
    If everything worked properly, you should be able to view newly added tupples on the output from pgslave.

Conclusion
We just learned how to quickly configure a PostgreSQL database to
act as a read-only mirror to another database. Note that in case the
slave goes offline, it will automatically recover data from the
master and would be soon up-to-date. Streaming replication is at
database level, so you need to execute the command in part 11for each
new database that needs to be replicated via streaming replication.


What are Measures and Dimensions in Cubes

Think about a process or event that you want to analyze.

Let's say you were building Lougle Analytics, and want to analyze visits (as opposed to single-page requests) to your site. Visiting a website is a process.

A fact table represents a process or event you want to analyze, in this case it is a list of site visits. You can have as many fact tables as you want, one per process or event.

What things might be useful to you in analyzing site visits?

Information about the web browser (brand, screen resolution, ...)
Information about the user (country, state, city, ISP, based on their IP address)
Information about when the visit started (year, quarter, month, week, day)
The duration of the visit
The referring page, landing page, and exit page (title, url path)
The number of pages visited during the visit

Technically, you could put this all into one table - as you would in Excel, but that'd get real big real fast, so we'll do ONE level of normalization. Dimensional design is often just "put everything you need for an analysis in one big table", and then normalize out one level.

So your fact table will look like this:

web_browser_key          bigint
ip_address_key           bigint
start_date_key           int
referring_page_key       bigint
landing_page_key         bigint
exit_page_key            bigint
duration_seconds         int
number_of_pages_visited  int

It has a bunch of key values that refer to values in other tables†, and two non-keys. The non-keys are numeric values and are called Measures. Can you take the average of the web browser brand name? No, so it's a dimension. Can you take the average of the visit duration? Yes, so it's a measure.

The other tables are called Dimension tables, and the ip_address dimension table might look like this:

ip_address_key  bigserial primary key, /* use meaningless surrogate keys */
ip_address      inet unique,
country         text,
division        text,
locality        text,
latitude        numeric(8,6),
longitude       numeric(9,6)

Notice that it is not normalized: country could be derived from city (locality). However in a data warehouse, we care about facilitating analysis first. By facilitating analysis, I mean avoiding deep joins.

Notice that some of the data in the dimension table is hierarchical: country > division > locality. Your OLAP server (hopefully) understands hierarchies to support drill-down operations.

A logical Cube is just the collection of Dimensions and Measures that you are working with. Unlike a Rubik's cube, it can have more than 3 dimensions. Just think of a dimension as a column in your data set.

OLAP is a set of operations you can do against a data set, such a pivoting, slicing, dicing, drilling. Think of Excel PivotTables. An OLAP Server facilitates OLAP operations.

† usually without foreign keys


Does SQL Server guarantee accurate data storage? If so, how?

All the major SQL databases provide this; it is called being ACID compliant. This is probably the key phrase you need to be looking up in the documentation (and/or Google).

As for how they do it, MS SQL Server is closed-source, as is Oracle, so no-one outside the company can really answer that question, but as you say, if they didn't manage it for large-scale systems, their products would be out of the market very rapidly.


Issue shrinking dev sql server DB's log file

Is the database in FULL or SIMPLE recovery mode?

One thing that you could try doing is putting the database into SINGLE_USER mode and then shrinking the log file.

I have had success with troublesome systems before by taking two log backups in quick succession and then shrinking the file.  Put all the commands in one script window and execute them to get the commands to execute in rapid succession.

If the database is in FULL mode, then make sure you are taking regular LOG backups; this will keep the file smallish.


Is it possible to concatenate query results to form a new query?

Unless you are explicitly trying to write queries that work in multiple engines, I recommend sticking to the catalog views in the sys schema rather than INFORMATION_SCHEMA (see this and this). You'll need dynamic SQL for this anyway, and in other engines the syntax for this will vary greatly:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
  + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
  + QUOTENAME(s.name + N'.' + t.name) + N''', '
  + N' value = CONVERT(nvarchar(4000),'
  + QUOTENAME(c.name) + N') FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM
...

SET @sql = STUFF(@sql, 1, 10, N'');
...


Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application?

ELT is fine for a critical system. Your problem is predicated on this faulty assumption:

"Because the data is not cleaned up like in ETL..."

You can do most any cleanup with ELT as you do with ETL. You just generally do it in a staging table on the target system instead of in-flight between the source and target.

Edit:
You asked why do they mention:

  The source database and the target database are the same

I think that means "ELT is easier to do when the source and target database engines are the same, because you frequently have them talk directly".

For example, ELT over Oracle DB links (Oracle to Oracle) or SQL Server Linked Servers (SQL Server to SQL Server) is much easier than setting up heterogeneous links with something like GoldenGate.

Tools like Presto can make this easier though, as you'll see people do:

insert into sqlserver.table
select * from oracledb.table


How to convert varchar into decimal and its sum

The issue in the above query is only due to these value (7,956.00 and 5,328.00 ) when casting in implemented on these values the value returned as 7.00 and 5.00 due to which the query gives the result as 2569.00.

Just remove these commas from these numbers and the query will execute properly

SELECT SUM(REPLACE(Amount, ',', '')) as result
FROM tblserviceinvoice


Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

This behaviour has changed in recent updates. See
FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider

  After you apply this fix, NUMBER values with unknown precision/scale
  are treated as double values with OLE DB provider. If the precision is
  important and the range of the values is not large enough, you can
  enable the new Trace Flag 7314 that would start treating such values
  as numeric (38, 10).

However I'm a bit surprised you are getting a float instead of a double \ numeric since that seems to contradict the article somewhat, so I'm not sure it explains your exact situation.


"abc" would be a default instance, where "abc" is the actual server name. Sounds like you created a named instance where /xyz is the name of the instance. This allows you to have multiple instances of SQL Server on the same server. The only way to remove the instance is to uninstall SQL Server and then reinstall with the name you want.


Is there a way to get the query results (rows affected/time taken) after every query

You send one command, one line (which consists from 3 queries). So PHPMyAdmin waits until this command finished, and then shows the result of this one command which consists from 3 separate results. I think he has this feature - it is PHPs input buffering.

Use CLI interface, for example - it shows the result for each query in a command immediately after this result is formed and sent to server's output / client's input. - Akina


Process attempted to unlock a resource it does not own

If this is just one time issue and CHECKDB run without any errors and you have good backups, do not bother to investigate it (unless you want to spend time and resources to what is not reproducible).

If you are able to reproduce the issue, then I would recommend you to

Replicate on a test environment and capture whats going using a
Profiler.
As a proactive measure, you can probably have an agent job
or powershell job to scan the error log (note that you can put a logic inside Powershell job to only scan from the last time it ended) to look for specific errors like Error: 1203 or Error: 3314 and then fire SQLDIAG to capture what was actually going on when the issue happened.

Let me know if you need any such script and I will be happy to share it.


Would a cron job to automatically optimize tables on a monthly basis be ideal?

The compete and correct answer depends on which Mysql version you have and on which engine is the table.

Fast answer: no.

MyISAM

Short answer
Only if you can have some downtime and want to take some risks (see below).

Long answer
During the optimize, the table becomes I inaccessible, and if you need to use that table (selects, inserts, etc), then it's not a good idea to perform an scheduled downtime.

InnoDB

Short answer
If you have mysql 5.6.17 or above, than you may do it without downtime, but I wouldn't recommend it.

Long answer
Since mysql 5.6.17 that InnoDB can perform an optimize table with Online DDL, which means that selects, and even inserts, deletes and updates can be performed on the table while the optimize is running, if that is your case.
With that you can schedule an optimize on the table knowing that your system continues to run.

But it may not be safe to do it like that, as things may go wrong, and you're not with your eyes on the database to fix it ASAP. For example, you may not have free disk space for the optimize to run.

For 5.6.16 or lower, applies what I've said about myISAM.

Conclusion

Optimize table may lock your database users from accessing the database, if your mysql version doesn't support online ddl. And even if it supports, performing an operation like that automatically needs to be done with extreme care.

Maybe create a cron that sends you an email to remember to do the optimize?


Strategy for setting up a 60gb MySQL database from a dump?

To speed up load time of InnoDB you can tune some variables.

innodb_buffer_pool_size. Kind of the most important one. Allocate as much as your system allows, but make sure MySQL doesn't use swap. 4GB RAM is desperately little, try buffer pool around 2G. If you can install more memory that will be more helpful than anything below.
innodb_log_file_size. The larger REDO log the less random IO InnoDB does. It doesn't make to make it larger than the buffer pool. So, 512M - 1G should be OK.
Reduce InnoDB durability with innodb_flush_log_at_trx_commit=0
A bit risky, but you can also disable innodb_doublewrite
Make sure you disable all unnecessary logs (binlog, slow log, general log).

DISCLAIMER:
All recommendations for the load process only. For normal operation configuration must be different.


Assuming that the [PhoneField] is 10 digits, such as '1234567890' then you could use STUFF to put the proper values in.  E.g.

select STUFF(STUFF('1234567890',7,0,'-'),4,0,'-');

But please note that this only answers formatting a phone number for the 10-digit answers.  If you are using internationally dialed numbers, you need to investigate the various forms of phone numbers you might need to handle.

See: https://en.wikipedia.org/wiki/National_conventions_for_writing_telephone_numbers


What is the permission necessary to drop a service in service-broker?

After I post this question, I just found my answer with some tricky way. I went to the left object explorer and delete the contract with it. but instead of submitting the form I click on the script button which produces the scripts. It was my mistake to drop a contract with drop service command. so the correct query to drop a contract is:

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
    DROP CONTRACT [//Audit/Contract]

I post this answer because maybe somebody encounter with this error and my guide can be useful to him/her.


MySQL query takes too long in codeigniter

Several things make the query slow:

Leading wildcard in LIKE.  (See below)
OR -- One workaround is with UNION.
IN ( SELECT ... ) -- This one is version-dependent; what version is each machine running?

The first two can be alleviated by using a FULLTEXT(name, iupac_name, inchi_key) and MATCH(name, iupac_name, inchi_key) AGAINST('quer*' IN BOOLEAN MODE).


SQL Server not using all CPU cores/threads

In the SQL Server error log, it should tell you why. I found this on a customer system (really just pasting the message for Google juice):

  SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I got the likely explanation from here:

  For customers with Software Assurance on existing SQL EE Server licenses (or access to them under their current Enterprise Agreements during term) a version of Enterprise Edition was created to enable them to upgrade to SQL Server 2012. This version has technical restrictions limiting an instance to using only 20 processor cores (40 CPU threads with Hyperthreading).

Summary: in the error log, the edition will be reported as either Enterprise Edition or Enterprise Edition: Core-based Licensing. If it says the former, as in the case of the aforementioned customer system, you will need to obtain a core-based licence to use all the available cores.

If that isn't the case and you're already licenced for all cores, check your affinity mask settings, particularly if they were set, and the underlying hardware was upgraded.


My sql select query during long updates on the same table. Junk or old data?

It is controlled by Transaction Isolation Levels in MySQL. Default Transaction Islation Level is Repeatable Read. With this you will see the old data until it is committed.


Very large Transaction Log File - When is it normal?

Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation. One of the most probable reason - you didn't make log backups. You need to backup your transaction log and after that you will be able to shrink it. If you don't need log backups you can switch database recovery model to simple and shrink the log.
Other possible reasons are a bit more complicated (for example, you may have some issue with database mirroring or long running transactions). You can find more details here:
https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#FactorsThatDelayTruncation


How to get INNER JOIN result from two tables

Please try:

SELECT tf.font_id, tf.font_name, tcf.font_id, tc.charset_name, tcf.font_id, tcf.charset_id
FROM table_fonts tf
INNER JOIN table_charsets_by_font tcf ON tcf.font_id=tf.font_id
INNER JOIN table_charsets tc ON tc.charset_id=tcf.charset_id
WHERE tc.charset_name='latin';


Throttle DDL statement to avoid impacting performance of other queries

I hope your tables are InnoDB, not MyISAM.  Some of the following will not work well with MyISAM.

Plan A:  Use Percona's pt-online-schema-change.  It is self-throttling.

Plan B:  (You don't like this.)  Many ALTERs can, especially in 5.6 or 5.7, run ALGORITHM=INPLACE, making it much less invasive than the way (ALGORITHM=COPY).

Plan C:  (You don't like this.)  Play replication games.  This leads to virtually zero impact on other queries.

Plan D:  Galera-based replication (PXC, MaraiDB+Galera, etc).  This is also a replication game, but the tools probably make it less effort for the DBA.

Plan E:  If you are adding a column, build a parallel table for the extra column(s).  This has zero impact to set up.

Plan F:  Have a layer of code to hide the original table from the modified table.  Write some extra code to handle, with zero downtime, the transition, regardless of how long it take.

Plan G:  Pay for MEB - it plays some nifty games to make backup faster.


What subquery do I need to calculate this report?

I've got this far (DDL and DML below)

SELECT ticket_id, c_max_date FROM
(
  SELECT ticket_id, MAX(FROM_UNIXTIME(comment.comments_timestamp)) AS c_max_date
  FROM comment
  WHERE comment.comments_timestamp < UNIX_TIMESTAMP(NOW() - INTERVAL 2 YEAR)
  GROUP BY ticket_id
) AS mytab;

+-----------+---------------------+
| ticket_id | c_max_date          |
+-----------+---------------------+
|         3 | 2004-07-09 15:15:30 |
|         4 | 2006-06-30 15:15:30 |
+-----------+---------------------+

These two comments are indeed the ticket number of tickets whose comments haven't been updated in 2 years (arbitrary choice). The dates are the dates of the last update to the ticket comments.

Can you (anyone) put the finishing touch to this and join mytab to tickets? My brain is fried!

CREATE TABLE IF NOT EXISTS ticket
(
  ticket_id INT NOT NULL,
  ticket_description TEXT NULL,
  PRIMARY KEY (ticket_id)
);

CREATE TABLE IF NOT EXISTS comment
(
  comment_id INT NOT NULL,
  ticket_id INT NOT NULL,
  comments_comment VARCHAR(45) NULL,
  comments_timestamp INT NULL,
  PRIMARY KEY (comment_id),
  INDEX ticket_index (ticket_id),
  FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_id)
);

INSERT INTO ticket VALUES(1, 'ticket 1');
INSERT INTO ticket VALUES(2, 'ticket 2');
INSERT INTO ticket VALUES(3, 'ticket 3');
INSERT INTO ticket VALUES(4, 'ticket 4');
INSERT INTO ticket VALUES(5, 'ticket 5');

INSERT INTO comment VALUES(1, 1, 'comment 1 on ticket 1', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', UNIX_TIMESTAMP('2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', UNIX_TIMESTAMP('2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', UNIX_TIMESTAMP('2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', UNIX_TIMESTAMP('2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', UNIX_TIMESTAMP('2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', UNIX_TIMESTAMP('2006-06-30 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2014-07-09 15:15:30'));


Group By With Rollup results table has totals at the top

You should use the GROUPING function

  GROUPING returns 1 for aggregated or 0 for not aggregated in the
  result set.

The advantage of this is that it will deal correctly with the case that B2B_Week is itself NULL and not incorrectly treat it as a total row.

WITH T
     AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
                bm.MISSION_ID,
                dl.DRIVER
         FROM   DD.B2B_MISSIONS bm
                INNER JOIN dd.IN_GATE ig
                  ON bm.MISSION_ID = ig.IN_MISSION_ID
                INNER JOIN dd.XNS_DRIVER_LOGIN dl
                  ON ig.IN_DRIVER_ID = dl.driver)
SELECT B2B_Week = CASE WHEN GROUPING(B2B_Week) = 0 THEN CAST(B2B_Week AS VARCHAR(5)) ELSE 'Total' END,
       B2B_Mission = COUNT (DISTINCT MISSION_ID),
       Distinct_B2B_Drivers = COUNT (DISTINCT DRIVER)
FROM   T
GROUP  BY B2B_Week WITH ROLLUP
ORDER  BY GROUPING(B2B_Week),
          T.B2B_Week


It appears MySQL is silently failing at the call to proc3(), and therefore never running the rollback statement.
This is how you should construct the code to actually perform the rollback, passing some details back to the caller.  You can test this code at dbfiddle.uk:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Creating the original three procs:
DROP PROCEDURE IF EXISTS proc1;
DROP PROCEDURE IF EXISTS proc2;
DROP PROCEDURE IF EXISTS proc3;

DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
//
DELIMITER ;

Create a new proc to call the three procs above:
DELIMITER //

DROP PROCEDURE IF EXISTS call_procs;

CREATE PROCEDURE call_procs()
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        ROLLBACK;
        SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
        SELECT @full_error;
    END;
    SET autocommit = 0;
    START TRANSACTION;
    CALL proc1();
    CALL proc2();
    CALL proc3();
END
//
DELIMITER ;

Call the new proc, and check the results:
CALL call_procs();

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The output now appears to be what you'd expect:
╔═══╗
║ x ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝
And a nice little bonus, you get the following output alerting you to an issue:
╔═══════════════════════════════════╗
║            @full_error            ║
╠═══════════════════════════════════╣
║ ERROR 1365 (22012): Division by 0 ║
╚═══════════════════════════════════╝


And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.


Can I get shell access through SQL Server 2017 on Linux?

No sadly it doesn't, when I try EXEC xp_cmdshell 'dir *.exe'; I get,

  Msg 15281, Level 16, State 1, Server x230, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

When I try to enable it with sp_configure 'xp_cmdshell', 1; I get

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

But, If I retry the EXEC xp_cmdshell command above I get,

Msg 16202, Level 14, State 200, Server x230, Procedure xp_cmdshell, Line 1
Keyword or statement option 'xp_cmdshell' is not supported on the 'Linux' platform.

They are such kill joys.


Which are the downsides of not deleting the CDC records in change tables?

What does 'grow unmanageable' means for me?

This all depends on your environment. If you had a DB that was subject to a lot of transactions (one with a lot of CRUD operations), then this table would grow much faster and larger than one with minimal changes making it harder to manage.

  Will it have very negative impact on the performance or storage in the
  DB? Maybe an impact on Replication performance?

The larger your change tables grow, the longer your ETL / change data query functions that are used to store the data on another instance would take to run (they would be slower). You haven't stated of you are storing these records elsewhere, or just using the change tables as your end point for auditing, but it seems like the latter.

  Am I just fine doing CDC with no cleanup job at all?

If your change table isn't expected to grow > 20K rows ever you probably wouldn't see a performance burden.

  Is it a better alternative to copy the data to a reporting DB using a
  SSIS package or a job running a stored procedure? If so, is there a
  simple way to handle the creation of new columns in the destination
  table based on new columns added in the change tables?

Based on this question, and the fact that you state you already have a script that is copying data to another server, it seems like you are trying to capture changes multiple ways. One way you may want to explore is System-Versioned Temporal Tables. This may be the easiest route for you and could eliminate CDC and your SSIS / custom script especially since your DML events seem to be low. These tables will also change when you conduct DDL changes to your source table.

  Does the answer change if instead of 20K rows in one change table
  there are 1 million?

Since that would be 50X the number of rows, sure, you can expect different results regarding performance of any queries against those change tables.


Pull multiple fields from table B where a specific value from Table A is not in Table B

Based on your comment

  The task is to find all contacts in the Contact table that are not in
  the Event table, and pull their email address AND their contact
  creation date.

Why doesn't this work?

SELECT c.emailAddress
    ,c.contactProperty_createdate
FROM Contact AS c
WHERE NOT EXISTS (
        SELECT *
        FROM Event AS e
        WHERE e.recipient = c.emailAddress
        );


Is there a simple way in PL/pgSQL to check if a query returned no result?

Exception blocks are meant for trapping errors, not checking conditions. In other words, if some condition can be handled at compile time, it should not be trapped as error but resolved by ordinary program logic.

In Trapping Errors section of PL/PgSQL documentation you can find such tip:

  Tip: A block containing an EXCEPTION clause is significantly more
  expensive to enter and exit than a block without one. Therefore, don't
  use EXCEPTION without need.

Instead using exceptions (bad), or IF/THEN/ELSIF (better), you can rewrite this to one query:

SELECT c.data into data
FROM  doc c
WHERE c.doc_id = id
  and (
    c.group_cur > group_cur
    or
    c.global_cur > global_cur
  )
ORDER BY
  -- this will make group always preferred over global
  case when c.group_cur > group_cur then 1 else 2 end ASC,
  -- and this is your normal ordering
  c.id DESC
limit 1;

If you really want two queries, you can use special FOUND variable to test if previous query gave any result:

select c.data into data
from doc c
where c.doc_id = id and c.group_cur > group_cur
order by c.id desc limit 1;
if not found then
    select c.data into data
    from doc c
    where c.doc_id = id and c.global_cur > global_cur
    order by c.id desc limit 1;
    if not found then return null; end if;
end if;

Obligatory RTFM links folllow :-)

See this for description of FOUND variable, and this for IF/THEN blocks.


Delayed transactional replication setup?

To initialize a Transactional subscription from a backup you actually end up creating the publication first, enabling it to initialize subscriptions from a backup, then take a backup, and then initialize subscriptions with the backup.

Changes that occur after taking the backup and before initializing the subscription(s) will be stored in the distribution database, which acts as a store-forward queue.  Those changes will then be replicated to subscribers after they have been initialized from the backup.

Note that by default the distribution cleanup jobs clears transactions older than 72 hours from the distribution database which is based on the publication retention period.  You may want to temporarily disable this job before the backup and re-enable it after you initialize the subscriptions from the backup.

Have a look at Initialize a Transactional Subscription Without a Snapshot and Initialize a Transactional Subscription from a Backup.


What is the fastest way to reset SQL Server database if you can not use RESTORE?

Run each of your tests inside of a single transaction... at the end of each test, ROLLBACK the transaction.


After a lot of research, I ended up using Barman and a custom post-backup script, it works very good!


It would be convenient to diagnose where is the performance bottleneck in this case. Can you monitor the network throughput / CPU usage / disk usage to see which one is higher while uploading?

If it's a network bandwidth problem, make sure you're using compression in server/client protocol, any client should have this feature, Mysql Workbench for sure does
Most probably it's not related to the fact that the data comes from Access
Having your own test Mysql server inside your network would be of use for two things: importing the data there to see if the network is the problem; and importing it there and exporting it from there, forcing a multi-row insert export format


Unique pair checking query improving in pivot table

The query can be made easier:

select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*)
from tbl_profile_follow
group by least(profile_id,followed_id), greatest(profile_id, followed_id)
having count(*) > 1;

Using this approach you can also create a unique index on the table

create unique index idx_unique_pair
   on tbl_profile_follow (least(profile_id,followed_id), greatest(profile_id, followed_id));

This above will however not prevent or detect multi-level loops:

profile_id | followed_id
         3 |           5
         5 |           6
         6 |           3


Retrieving cached plans fails due to cached objects of database Mirror

The sys.dm_exec_query_plan has a column returned for dbid, you could just filter on that for the database that is being mirrored.

Or, you could get fancy and nest a sub-query that filters for any dbid that is in a mirrored state by going out to the sys.database_mirroring DMV.


The setup for maximal resiliency from power failure is to have 3 Galera nodes, one in each of three geographically separate locations.

You seem to be using MariaDB, the newer versions of which include Galera Clustering.  There is, of course, some setup for clustering and some coding/admin differences than vanilla MariaDB.

When a node (or even the entire datacenter it is in) goes down, the other nodes are available to take reads and writes.  The only issue is pointing your clients to them.  There are several Proxy servers (etc) that provide this.

3 is a magic number.  It allows for a single-point-of-failure, with the other 2 nodes voting to say "we have a quorum" and will stay up.  The one that went down might actually be alive, but hidden behind a network outage.  To prevent "split brain", that node realizes that id does not have a quorum, so it refuses to take writes, since they might compromise the consistency of unique keys.

For a single 'home' system:

Use InnoDB, not MyISAM.  InnoDB will recover graceful from power failures.
Plug into a UPS.  This will give you 'some' time before the power really fails.
Use a laptop, not a desktop -- laptops have a battery that will last 'some' time.


SQL Server Migration Plan - Alias Options for Multiple Apps Connecting

you may eventually rename the old VM and the old instance (es: prdServer01_old) and assign the prodServer01 at the new one (both VM and instance)
prdServer01 --> prdServer01_old
prdServer02 --> prdServer01
here how to rename an instance:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15
As i'm supposing you are not using ip addresses in connection string, that's all.


Looping over column names in SQLite and running a command for each column name

You can't do it in pure sqlite; column names have to be hardcoded in the query (also why they can't be bound to parameters), even if there was a way to use UPDATE in a loop, which there isn't.


How do I detach all other users from a postgres database?

You could try connecting to the database as the postgres user and running:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )    -- 1. don't terminate your own session
    AND datname =                     -- 2. don't terminate connections to
    (SELECT datname                   --    other databases in the cluster
       FROM pg_stat_activity
      WHERE procpid = pg_backend_pid( )
    );

update
An even better query gets rid of the subselect:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
    AND datname = current_database( );


What's a good indexing scheme for a type-2 SCD table with a very broad natural key?

Look at the order of the column indexes and ensure the most selective are first

SELECT
 COUNT(DISTINCT A),
 COUNT(DISTINCT B),
 COUNT(DISTINCT C),
 COUNT(DISTINCT D)
FROM
 X

Then, read this around memory allocations for varchar columns in queries: SQL Server VARCHAR Column Width (follow Martim Smith's link)

Finally, you could use HASHBYTES to collapse the columns into a hashed computed column. You can add this to the index too as the first column so this is highly selective because it's almost unique
A surrogate key may be useful


You don't say how you are trying to process the file (command line, via a web based tool, ...) but I suspect that you are using something like phpMyAdmin's web based interface which is the most common way people interact with mySQL (aside from through their own code) in my experience.

Here you will run into issues with both PHP and the web server it is running within having limits on the size of any single upload. These limits are in place to prevent DoS issues (accidental or otherwise) due to /tmp or other areas becoming full so unless you run you own server you probably won't be able to get the limits pushed up high enough to deal with that large a file. If you do run your own server, see pages like this answer for details of how to make the required changes.

Other than this you have four options:

Split the file into smaller chunks and process them manually. This could be long winded for such a large file.
If you have shell access, upload the large file to the server and use the command-line mysql tools to import it (unfortunately I don't think phpMyAdmin has the feature to process a "local" file like this otherwise you could upload and then still use that).
Write your own simple PHP script (or other language depending on what is on your server) that reads the uploaded file and runs it. For smaller files loading the file into a string and calling multi_query() should do the trick, but you will run into memory limit issues with a 1.5Gb file so you'll need to be more clever than that (parsing the file and submitting individual statements or groups of statements instead of it all in one go).
Find an existing a tool that already does option 3b (reading a file from the web server's filesystem and processes it) for you.


Unable to connect to MySQL cluster database

It looks like your MySQLD is running at port 5000, from the working mysql command line.
  The server listening on port 1186 is probably the cluster management server (ndb_mgmd).  It has its own client (ndb_mgm).  You cannot connect to it using the mysql client.  Similarly, the MySQL JDBC driver cannot connect to it.
Frazer


"Best" is fairly subjective since it depends on your requirements.

Your listed approaches are:

Always On - If you could implement a readable secondary, this might provide a good way for your MIS team to be able to see up-to-the minute data.  Downside to this - you need to upgrade your SQL Server, which might be costly, and it will take a fair amount of technical knowledge to get right.
Replication could be setup to replicate the desired tables to a second SQL Server instance that could then be used by the MIS team in a read-only fashion.  Replication can be tricky to setup correctly, and can be difficult to maintain.
Mirroring won't allow your MIS team to read data from the mirror database.  The mirror database is kept in a perpetual state of "recovery" until (if) the primary becomes unavailable.
Adding a column to the database.  If you have code written using SELECT *, adding a column can be problematic since you'll need to revisit all that code so it can handle the new column - the takeaway from that exercise would be "never use SELECT *".  Use a rowversion datatype for the new column since it will automatically increment every time any insert/update/delete occurs in the entire database.  At the end of each "import" operation, the MIS team would record the current rowversion value, to limit the rows being imported during the next run to only those rows that have been modified.

Another possible solution would be to use triggers to insert modified rows into holding tables that become the source of the MIS import job.

A simple example of how that might work.  We'll create this test in tempdb:

USE tempdb;
IF OBJECT_ID(N'dbo.MainTable_Holding', N'U') IS NOT NULL
DROP TABLE dbo.MainTable_Holding;
IF OBJECT_ID(N'dbo.MainTable', N'U') IS NOT NULL
DROP TABLE dbo.MainTable;

The main table:

CREATE TABLE dbo.MainTable
(
    MainTableID int NOT NULL
        CONSTRAINT PK_MainTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someval varchar(100) NOT NULL
);

The "holding" table, where rows will exist until they are processed by the MIS job:

CREATE TABLE dbo.MainTable_Holding
(
    op_id int NOT NULL IDENTITY(1,1)
    , MainTableID int NOT NULL
    , someval varchar(100) NOT NULL
    , op tinyint NOT NULL
        CONSTRAINT CK_MainTable_Holding_op
        CHECK (op IN (1, 2, 3, 4))
);

CREATE CLUSTERED INDEX CX_MainTable_Holding
ON dbo.MainTable_Holding (op_id);
GO

The trigger that will capture modifications from the main table into the holding table:

CREATE TRIGGER MainTableHoldingTrigger
ON dbo.MainTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @op_ins tinyint;
    DECLARE @op_del tinyint;
    SET @op_ins = 1;
    SET @op_del = 2;
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @op_ins = 3; --updated to
        SET @op_del = 4; --updated from
    END

    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
    SELECT d.MainTableID
        , d.someval
        , @op_del --delete
    FROM deleted d;
    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
    SELECT i.MainTableID
        , i.someval
        , @op_ins --insert
    FROM inserted i;
END
GO

A couple of test statements.  First we insert 10 rows:

INSERT INTO dbo.MainTable (someval)
VALUES (REPLICATE(CHAR((CRYPT_GEN_RANDOM(1) % 26) + 65), 10));
GO 10 --insert 10 rows

Here, we delete the first 5 rows:

DELETE
FROM dbo.MainTable
WHERE dbo.MainTable.MainTableID <= 5;

This updates the 6th row:

UPDATE dbo.MainTable
SET dbo.MainTable.someval = 'XXXXX'
WHERE dbo.MainTable.MainTableID = 6;

Here we can see the contents of the holding table:

SELECT mth.MainTableID
    , mth.someval
    , Operation = CASE mth.op
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'UPDATE TO'
        WHEN 4 THEN 'UPDATE FROM'
      END
FROM dbo.MainTable_Holding mth;

The results:

╔═════════════╦════════════╦═════════════╗
║ MainTableID ║  someval   ║  Operation  ║
╠═════════════╬════════════╬═════════════╣
║           1 ║ XXXXXXXXXX ║ INSERT      ║
║           2 ║ VVVVVVVVVV ║ INSERT      ║
║           3 ║ NNNNNNNNNN ║ INSERT      ║
║           4 ║ CCCCCCCCCC ║ INSERT      ║
║           5 ║ RRRRRRRRRR ║ INSERT      ║
║           6 ║ GGGGGGGGGG ║ INSERT      ║
║           7 ║ CCCCCCCCCC ║ INSERT      ║
║           8 ║ HHHHHHHHHH ║ INSERT      ║
║           9 ║ VVVVVVVVVV ║ INSERT      ║
║          10 ║ KKKKKKKKKK ║ INSERT      ║
║           5 ║ RRRRRRRRRR ║ DELETE      ║
║           4 ║ CCCCCCCCCC ║ DELETE      ║
║           3 ║ NNNNNNNNNN ║ DELETE      ║
║           2 ║ VVVVVVVVVV ║ DELETE      ║
║           1 ║ XXXXXXXXXX ║ DELETE      ║
║           6 ║ GGGGGGGGGG ║ UPDATE FROM ║
║           6 ║ XXXXX      ║ UPDATE TO   ║
╚═════════════╩════════════╩═════════════╝

The MIS job would simply read all rows from dbo.MainTable_Holding, performing the appropriate insert, update, or delete.  Rows that have been inserted into the MIS database could be removed from the holding table, limiting the amount of space required by it.

Another possible solution would be to use SQL Server's queue mechanism, Service Broker.  Service Broker could be setup to send messages into the MIS Server; each message would be an insert/update/delete operation from one of the affected source tables.  Service Broker on the MIS Server would then pickup those messages, processing them asynchronously.


Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

1:many -- Just an id in one of the tables to link to the other.
many:many -- Need a table to link them.  It will have (probably) exactly 2 columns, namely the two ids.
key-value pairs -- read about EAV and its evils and benefits.


Nested Loop Output Estimate Low when expanding dataset

That's the optimal plan for that query.  So you should consider storing the result.  One easy way is to use an Indexed View.

create view TacticItemsByDay
with schemabinding
as
SELECT  [TacticId], [ItemKey_A], [ItemKey_B], [From_Day_Idx], [To_Day_idx], [ddate], [Day_Idx]
--INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

go

create unique clustered index ix_Results on TacticItemsByDay(TacticId, ItemKey_A, ItemKey_B, Day_Idx)


How can I display twice each stored row without using UNION ALL or a temporary table?

SELECT ta.id, ta.name
FROM emp ta
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;


Turn off ANSI_WARNINGS for heterogenous queries

This should remove your warning.

select name, count(employeeId) from employees group by name
select * from RemoteServer..departments


EAV (key-value) schema is clumsy and hard to optimize.

Please provide SHOW CREATE TABLE for each table.

What table(s) are newsletter_subscription and degree_discipline in?

These should help:

tags:   INDEX(fk_id, type, tag_key)
users:  INDEX(newsletter_subscription, user_type, signup_date)

The Query cache, if invoked, will take only milliseconds, not 30s.  It sounds like other I/O caching kicked in.

Also, try this...  Try 3 queries, each with one of the OR clauses.  While doing it, see if you can simplify each.  For example, when fetching tag6, there is no need to join to tag7.  If they add it up to less than 2 minutes (or 30 sec), we can discuss using UNION to finish out the query.

A few more hints...

( SELECT u.firstname, u.email
    FROM users AS u
    LEFT JOIN tags as tags ON tags.fk_id = u.id
                          AND tags.type = "user"
                          AND tags.tag_key = "option"
    LEFT JOIN tags as tags6 ON tags6.fk_id = u.id
             AND tags6.type = "user" AND tags6.tag_key = "role"
    LEFT JOIN tags as tags7 ON tags7.fk_id = u.id
              AND tags7.type = "user" AND tags7.tag_key = "sector"
    WHERE (user_type IN ("1"))
      AND (tags.tag_key = "option" AND tags.tag_value IN ("1", "2", "8"))
      AND ((degree_discipline IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
) UNION DISTINCT
( SELECT ... same ...
    FROM ... same ...
    WHERE ... second part of OR ...
) UNION DISTINCT
( SELECT ... 3rd ...
) GROUP BY u.email


Join tables up together to get different counts of account type per postcode are

I will try something like this:

select substring(PostOfficeBox,1,4) as 'Postcode'
    , COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
    , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
    , COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
    , COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
    , COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
    , COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
    , COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
--having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)


SSIS Excel source not mapping all columns

Try removing then adding the Excel Source component in the DataFlow Task, Also try to set the DataFlow Task Delay Validation property to True

If the Error persists, i think that the issue is not with the columns name, it can be generated by the column data type, try adding ;IMEX=1 to the excel connection string so the data type are taken from the first row


Product quantity bound to colors and sizes table structures

The model you described looks good to me.

You can add a trigger that will work on UPDATE and INSERT and will check if SUM(qty) from Used_sizes_colors table for updated or inserted PID is greater than Products.maxqty, and if it is: rollback transaction and display error.


Class not registered error in SSAS Data Source View

You have either installed an older version of SSDT/Visual Studio side by side with your 2015 visual studio or you are trying to open a data source view created in a newer version of SSDT/Visual Studio.

Some binary data stream is written in the .dsv file and includes the version information. The solution is to install a recent enough version of Visual Studio or SSDT and possibly re-register DDSShapes.dll.

See this MSDN post for details which explains:

  If you try to use SQL Server 2012 version of the SQL Server Data Tool
  to open the DSV modified by the SQL Server 2014 version of the SQL
  Server Data Tool, you gets this COM exception. The reason is, this
  leading 4 characters of binary data is the version of ATL used to
  compile the DDSShapes.dll. When this DLL reads the binary data, it
  check if the version of the ATL that was used to save the data, making
  sure it is below the version recognized by the DDSShapes.dll. "000a"
  is a higher value than "0008", so the SQL Server 2012 version of the
  SQL Server Data Tool throws an exception.

  The solution is to install the SQL Server 2014 version of the SQL
  Server Data Tool from https://msdn.microsoft.com/en-us/data/hh297027 .
  You should get the new version of the DDSShapes.dll in C:\Program
  Files (x86)\Microsoft SQL Server\120\Tools\Bin.


My bias is to use a single table with appropriate row-level security.

There are potentially huge maintenance advantages to a single set of tables.  If you end up with n copies of each table, that means that you have to run n copies of each script every time you want to make a change.  Frequently, that means that you end up with at least a few very slightly different versions of the application running at a time because someone forgot to apply script 7 of 23 in a monthly build to one set of tables and someone else created an index on one set of tables to address one customer's issues without adding it to every customer which makes debugging much harder.

A single set of tables has significant scalability advantages.  Adding new customers just requires adding a new row to the customer table not deploying a new schema/ database with the new customer's copies of the tables.  Adding new customers also doesn't directly add ongoing work for the DBAs.  If you have separate copies of the tables, you need someone to deploy tables to create a new customer and every new customer means additional work for the DBA at least to run the scripts one more time every time there is a change.

A single set of tables may also offer performance advantages.  If you're using a separate set of tables, each customer would realistically need a separate connection pool in the middle tier.  It would defeat the purpose of having separate tables, after all, if your middle tier is connecting as a user that can see every tenant's data because then you'd be implementing row-level security in the middle tier and dealing with all the complexity of multiple sets of tables in the back end.  That makes it tough to scale across servers-- do you create a connection pool for every client on every server?  Do you send certain clients to certain servers? Do you not preallocate connections and incur the cost of waiting for connections to be established more frequently?

That being said, there are cases where separate tables might be preferred.  If your customers are frequently large institutions, for example, separate tables will make it much easier to do things like move a customer to a dedicated box (or at least a dedicated VM) if the customer wants to upgrade to dedicated hardware so that they don't risk performance being affected by other customers.  Those large institutions may want greater control over outages and upgrades so it may make sense to have separate tables to allow different customers to be upgraded at different times in order to work with that customer's schedule.  Those institutions may find it easier to tell an auditor that their data is physically separate from all other customers rather than explaining that the data is physically intermingled but security controls are in place to guarantee row-level security.  If you're going to have relatively few relatively large customers, the amount of maintenance overhead you introduce by having separate tables may not be particularly significant given the general day-to-day maintenance tasks that each client likely requires.  In that sort of environment, different clients often have sufficiently different configurations that the problems they encounter are relatively unique even when they're running exactly the same version of the software.


SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query shouldn't take 30 seconds, but sometimes that's really the best and most optimal outcome for that context). In other cases, SQL Server is simply overwhelmed and can't (at least when not given infinite time) quickly come up with an optimal plan. So it will spend some time coming up with a "best plan I can find in this time" and then it will show "Reason for early termination: timeout" and you get what you get. You can force SQL Server to spend more time coming up with a plan using trace flag 2301 (which Paul White mentions and links to a more thorough piece in this answer), but more time may not prove to help that much (and more compile time may not be a good thing for end user performance on the whole). Another alternative is to break up the logic so SQL Server can focus on optimizing smaller, less complex queries.

Think of it like eating a pie - you can probably eat a pretty good-sized piece in one sitting, but if your goal is to eat the whole pie, you might want to spread it out across multiple settings.

For SQL Server, in some cases it can do better with chunks of that logic separated. This doesn't mean you should simplify all joins into #temp table waterfalls, but in some extreme cases it is a valid workaround.


The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!).

That should fix any access right issues. However it will cause two service restarts.


Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave?

Follow-up: we ended up doing the migration as I described in my question. We ran into no issues except minor ones related to change in behavior between version. The replication setup worked perfectly. This was done on RDS.


If I actively BEGIN a transaction, but fail to ROLLBACK at error, will PG become confused?

The transaction won't be "auto rolled back", it will be marked as failed, and the only option you have, is to do a rollback.
If you did not run rollback in the function but simply return from it in case of an error, then the UPDATE following the function call would result in "current transaction is aborted, commands ignored until end of transaction block" - so the UPDATE can't be run either (again: only if you omit the rollback in the function in case of an error).

Unrelated to your question, but: your UPDATE will (implicitly) run in a second (different) transaction if your insert succeeds because your function cleanly ends the transaction that was started in there. If you did mean to run insert and update in the same transaction, your code is not going to do that.


SELECT COALESCE with subselects is throwing error 'Operand should contain 1 column'

SELECT *
FROM JournalEntryTitleAndContent jetc
WHERE jetc.journalentry_id = 1
ORDER BY FIND_IN_SET(jetc.language_id, '1,2') DESC
LIMIT 1


You do not need to reindex, because CLUSTER effectively does it for you.

More specifically, CLUSTER locks the source table then creates a new copy of it ordered according to the target index. It creates indexes on the new copy then replaces the old table and indexes with the new ones.

Note that this is also true of VACUUM FULL in 9.0+.

If you've been seeing discussion suggesting that CLUSTER bloats indexes it could be people who're assuming that CLUSTER works like pre-9.0 VACUUM FULL. You might also be seeing and misreading discussions that mention index bloat caused by the old VACUUM FULL implementation and suggesting CLUSTER as an alternative.

This is implied in the documentation:

  a temporary copy of the table is created that contains the table data
  in the index order. Temporary copies of each index on the table are
  created as well. Therefore, you need free space on disk at least equal
  to the sum of the table size and the index sizes

What it doesn't say, but should, is that those temporary copies then replace the original table. (Bold mine).


You have to use convert DT_WSTR to DT_DBTIMESTAMP. You can find BOL reference here.

for e.g.instead of "Getdate()" use your Date_Column

(DB_TIMESTAMP)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + (DT_WSTR,10) Time_Column)

Also,  you can use the Data Conversion transformation, which is probably easier if you are only changing the data type.

EDIT: You can also look for an option of SSIS GoogleAnalyticsSource from codeplex.


Is it possible to only display the largest calculated table column?

Atleast for 8.0 there's the GREATEST function. I would also suggest that you do one scan against the table instead of six:

SELECT GREATEST(SUM(CASE WHEN vote_candidate_1 = 1
                         THEN vote_candidate_1 END)
              , SUM(CASE WHEN vote_candidate_2 = 1
                         THEN vote_candidate_2 END)
              , SUM(...)
               ) as ...
FROM votes;


Does Oracle server installation include client components?

Yes, it does, as @Phil said in the comment.


Literature about In-Memory Database Systems

The CMU advanced database course is about building an in-memory DBMS. It has been released on Youtube. Each lecture has some notes and references.


SQL Server database backup and saving at the same time

DBMS will take care of these race conditions.It will be done by some  schedule. What called scheduling? you can read about it here .


a memory leak doesn't corrupt the data, it only grabs more and more memory without releasing it. so you would not have a problem with integrity, because the old data, are not  released, the new data are the current ones.
cpu time at 100 pro cent, will make it difficult to save any data, because you will not get any cpu time to do so.
Fix your problems, the<y are all manageable and then you will not have any bothers or see if there is an update


Backup Database Task - What's the number at the end of the filename?

Since this logic is buried within the code for the SSIS package behind maintenance plans, I think you will only get guesses unless the author happens to swing by here. Here is my educated guess based on a few experiments:

For SQL Server 2008 and above, the 7 digits are the sub-second portion of the datetime2(7) output of SELECT SYSDATETIME(); at the moment the T-SQL for the backup command is actually generated. If you create a subplan for all user databases, and then go in to edit and choose View T-SQL, you will see this:

BACKUP DATABASE [SQLSentry] TO  DISK = N'<path>\SQLSentry_backup_2014_12_17_114845_6488663.bak'
WITH NOFORMAT, NOINIT,  NAME = N'SQLSentry_backup_2014_12_17_114845_6488663',
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Views] TO  DISK = N'<path>\Views_backup_2014_12_17_114845_6644778.bak'
WITH NOFORMAT, NOINIT,  NAME = N'Views_backup_2014_12_17_114845_6644778',
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

You'll notice that component of the filename, which happens to conveniently come after HHMMSS, is increasing with each subsequent command, but the numbers are still grouped together to some extent. If you repeat this a few times you will also see that they change every time, that they are not ever-increasing, and that they loop around to even show 7-digit numbers with leading 0s. Which indicates to me that it is not a sequence of any kind, and certainly not any component of the LSN or other meaningful properties of the database itself.

I couldn't think of a very easy way to have the SSIS package also include some output that would indicate SYSDATETIME() at the point of generation, and even if I could, I would have no idea if it would generate that value before or after, and how long it would take or what other delays it would introduce. So I'm not sure there is a way to confirm that's what it is.


Finding the right pattern for creating indexes,redesign query structure

Without knowing anything about the data, if we assume your JOIN's are on related data, a UNION isn't what you want. The related data will be presented disjointed on separate result rows.
There are no hard-and-fast rules on index design; the right design is the one that increases performance on your queries without consuming excessive resources. As a rule of thumb, however, you want an index that contains your JOIN, WHERE, and ORDER BY columns. INCLUDE the columns you are SELECTing. But before you create the index, be sure that another index can't do the trick with an added column or two.

Start there, and see how performant the query is. Use SET STATISTICS TIME ON and SET STATISTICS IO ON and run with "Include Actual Execution Plan" enabled while you tinker. I actually like to do this before I create any indexes so I have a baseline to compare my "improved" query with.

Also, be sure that 'optional' tables are LEFT JOIN.


How to get the settings associated with a stored procedure in relation to filtered indexes?

I think those setting are in your SSMS session (not in the proc itself unless you add the "Set" command in there).

Check in the SSMS [tools] -> [options] then under [Query Execution] -> [ANSI].

You can compare the setting and make sure they match with the SSMS (that is probably running locally on your non-prod server)


Deleted sql server windows authentication user

When you add a Windows Authentication login to SQL Server, the login gets created using the Windows Security ID, or SID, of the Windows user to create the login.

The name of the login is irrelevant, since authentication occurs via the SID.

If you delete and re-create a Windows user that has a login in SQL Server, the newly created Windows user will have a different SID than the old user, and hence will not be linked to the SQL Server login.  In order to have the new Windows user login to SQL Server, you need to create a new SQL Server login for the user via the CREATE LOGIN T-SQL statement, as in:

CREATE LOGIN <login_name>
FROM WINDOWS;


I dont realy try it or sure that is true but i read same info on webs

user_load+system_load+table as %    (how mush use)
,CPU                        as %    (how mush use)
,network_processor_load     as %    (how mush use)
memory                      as %    (how mush use)
combine will give you the % that you wont...

at lest im hope so XD...

sorry... if it not ture ^o.0^ but still my brain was work hard for this...

sooo easy on me...

and dont - star pliz


This really depends on what you want your graph to show (i.e. trending, inconsistencies, over time, over categories, etc.).  Some additional context would be helpful.

Looking at what you provided my first thought would be to show the data trending over time where one line represents "Actual" and the other "YTDPlan".  This would show an "actual vs. plan" comparison.  Using this assumption as an example, I would plot these 2 fields on the x-axis.  You can limit what you show over time in your query or a report parameter.  SQL Server Reporting Services (SSRS) will auto-create the values for you for the y-axis once the report is run (SSRS does this by analyzing the min and max bound values of your x-axis data).


Query SQL Server from Excel with parameters linked to cells

I have found a driver that I can install on a PC that does not have SQL Server installed on it and it makes Excel's data connections connect to SQL Server with cell linked parameters that are dates. The install can be found here: http://www.microsoft.com/en-us/download/details.aspx?id=36434

You then need to change the driver in your connection string to look like:

DRIVER=ODBC Driver 11 for SQL Server;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;


Extending Mark's answer...

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.

Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so

This detritus from the abort will block other processes.

The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON.

You can verify this be opening 2 query windows in SSMS:

Window 1:

In menu Query..Query Options set a timeout of 5 seconds then run this

BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout

Window 2, this will wait forever (or hit your timeout)

SELECT * FROM sometable

SET XACT_ABORT ON has interesting side effects too:

@@TRANCOUNT is set to zero on the implicit rollback but error 266 is suppressed (this happens if @@TRANCOUNT is different on entry and exit from a stored proc)
XACT_STATE will be -1 (it's "doomed")

The combination of this means that you can't use SAVEPOINTS (although, I can't recall exact behaviour) for partial commits/rollbacks. Which suits me

SO links on SET XACT_ABORT:

https://stackoverflow.com/a/1539686/27535 (me)
https://stackoverflow.com/a/919279/27535 (me)
https://stackoverflow.com/a/1150350/27535 (Remus Rusanu)

On nested stored procs:

https://stackoverflow.com/a/2074139/27535 (me)

On sp_reset_connection:

https://stackoverflow.com/a/2924456/27535


Is there a way to get the Actual Execution Plan for a cancelled query?

In SQL Server 2016 Management studio you can see the execution plan while executing, using the include live execution plan. This works for SQL Server 2014+

For SQL Server 2014 I usually use this query to get the execution plan of execution query's

SELECT
    r.session_id
,   r.start_time
,   TotalElapsedTime_ms = r.total_elapsed_time
,   r.[status]
,   r.command
,   DatabaseName = DB_Name(r.database_id)
,   r.wait_type
,   r.last_wait_type
,   r.wait_resource
,   r.cpu_time
,   r.reads
,   r.writes
,   r.logical_reads
,   t.[text] AS [executing batch]
,   SUBSTRING(
                t.[text], r.statement_start_offset / 2,
                (   CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
                         ELSE r.statement_end_offset
                    END - r.statement_start_offset ) / 2
             ) AS [executing statement]
,   p.query_plan
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
    sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
    r.total_elapsed_time DESC;

This gets the statement executing with their corresponding estimated query plan.


Give privilege to new created databases in MySQL

It is impossible to do this using permissions only .

The only way is to create a stored procedure as described here

If you want to avoid stored procedures, a workaround is:
GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.


Why is PostgreSQL blocking creation of a new table on update on foreign table?

If you are executing both statements in a single transaction, they won't lock each other.
If you execute the statements in different transactions, one will have to wait until the other is done. The reason is that foreign key relationships are implemented by system triggers in PostgreSQL and creating a trigger on tablea requires a SHARE ROW EXCLUSIVE lock on the table.
If you look at the lock compatibility table, you will see that that lock conflicts with the ROW EXCLUSIVE lock required by INSERT, UPDATE and DELETE.
Make sure that your UPDATE are running in short transactions, then that won't be a problem.


How to update views outside of update/insert transaction in SQL Server

No, there isn't. The C (Consistency) in ACID dictates that the dependent (indexed) views are updated.

If you want to workaround this, you'd need to build views on a separate table that is updated on demand in your own time. Say via Service Broker or scheduled


How to update array fields in a table in Postgresql?

You can simply do

UPDATE my_tbl
   SET (a1, a2) = (a1 || 123465, a2 || 132456789)
 WHERE id = 321;

To obtain a function, you simply have to replace the integer constants with the parameters.  You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


Using of OPTIMIZE FOR hints in a sql query

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.


This should work if you want to join two tables by day and do counts on different conditions of the columns.

SELECT *
FROM (
SELECT
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM recent_items
    GROUP BY date(time)
) as recitems
JOIN (
    SELECT
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM insider_trades
    GROUP BY [timestamp]
) as InInfo ON recitems.someDay = InInfo.someDay

This may be slow depending on the size of the data that is being queried. Limiting the number of days you are trying to get data for by adding where clauses to the inner queries will help.


Save output from multiple SQL SELECT commands to a file

Basic function to return rows from multiple SELECT statements:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS TABLE(a_id integer, txt text) AS
$func$
BEGIN

RETURN QUERY SELECT a.a_id, a.txt FROM tbl_a a;

RETURN QUERY SELECT b.a_id, b.txt FROM tbl_b b;

END
$func$ LANGUAGE plpgsql;

You could do the same with UNION ALL.

Simple & quick demo to output from tables with different structure:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS SETOF text AS
$func$
BEGIN

RETURN QUERY SELECT t::text FROM tbl_a t;

RETURN QUERY SELECT t::text FROM tbl_c t;

END
$func$ LANGUAGE plpgsql;

You can cast any type to text in PostgreSQL, even the composite type of a table row ...

Call:

SELECT * FROM f_get_rows()

To write this to a file (local to the server):

COPY (SELECT * FROM f_get_rows()) TO '/path/to/my/file.csv';

To get you started read these chapters in the manual:

CREATE FUNCTION
Returning From a Function
COPY

For a more specific answer you need to write a more specific question.


Storing mirror database failover details to the respective server or on repository

The concept is to create an extended event session listening to the DATABASE_MIRRORING_STATE_CHANGE event. This post covers the mechanics of querying extended event info using TSQL on live server, though haven't used this approach.

Otherwise, you can create the extended events session via SSMS, and configure that session to write the output to a file (xel) which can either be opened by SSMS or by using a system function that can convert the data to xml. The XEL ("event file") itself is binary. easiest way IMO is just to configure some kind of script to copy those files to a central instance, then open and process there.

Fields that DATABASE_MIRRORING_STATE_CHANGE event captures:  https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/database-mirroring-state-change-event-class.

See https://msdn.microsoft.com/en-us/library/hh213147.aspx  for step by step to set up the an extended events session that dumps results to an event file. (needs to be done per instance, though can be scripted). Fairly quick setup to see if it's giving you all the details you need.

You can include the DATABASE_MIRRORING_STATE_CHANGE event class as part of a trace as well.


PostgreSQL nondeterministic collations are not supported for LIKE

Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation when needed via the COLLATE clause. For example, in your case, you can use the tr-TR-x-icu collation as follows:

select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"

You can see a working demo of this, using your sample code posted in the question, on db<>fiddle.

Of course, that will be case-sensitive. For this you need to use the ILIKE operator which is case-insensitive. For example:

select name from testtable1 WHERE name ILIKE '%A%' COLLATE "tr-TR-x-icu"

But, the = operator is still case-sensitive because the collation as defined in the CREATE COLLATION statement did not alter the default sensitivities, and by default collations are everything-sensitive (which is not the same as binary collations, just FYI).

To create a collation that uses the "tr-TR" locale and is also case-insensitive, you need to specify the locale property in the CREATE COLLATION statement as follows:

locale = 'tr-TR-u-ks-level2'

To see that in action, please see: db<>fiddle example 2

Please see "Unicode Technical Standard #35: UNICODE LOCALE DATA MARKUP LANGUAGE (LDML), PART 5: COLLATION" for the full list of collation options.

And, please visit Collations Info for collation-related info. It's geared mostly towards SQL Server and .NET, but has some MySQL and PostgreSQL stuff, and the key concepts are generally the same across platforms and languages.


Riak - why can't I go to http://localhost:8091/stats in my browser? (Seven Databases in Seven Weeks)

It looks like that's not the port it runs on any more. You can have a look in the server's app.config (e.g. dev/dev1/etc/app.config), which is mentioned in the book in case you need to change the port.

On my instance it looks like it's running on port 10018, so I can see the stats on localhost:10018/stats (dev1), 10028 (dev2) and 10038 (dev3)


Page setup and print layout in SSRS Report

The page setup in the print options does not control your report parameters. You will need to use the Height/Width property of the report inside BIDS. Orientation in SSRS is controlled by setting these values. We have a report at our office that is similar to what you describe and there are two ways of (semi) achieving what you want to get done.

The first is to use a separate report for each page that you want. This gives you complete control over all elements of each page such as height/width etc but may be more work for you to do and possibly more maintenance in the long run. It does however mean that should you change elements of the report later you only have to retest the bits you have changed, not the entire report.

The second one is to you expressions to force page breaks. This will work for reports that need the same orientation (as they inherit the properties set on the report container) but will not work for a report that has a different orientation. A link to an example of page breaks is below:

http://msdn.microsoft.com/en-us/library/ms157328.aspx#AppearanceofReportData

I hope this makes sense and that it helps you in what you want to achieve.


This kind of situation is exactly what BIML (Business Intelligence Markup Language) is made for.

From Introduction to Business Intelligence Markup Language (BIML) for SSIS, this solves exactly the problem you are facing:

  With Microsoft SQL Server Integration Services (SSIS), you can build
  powerful and flexible packages to manage your enterprise-wide ETL
  solutions. However, every ETL project has some repetitive tasks: you
  need to import 20 different flat file exports from the ERP system, you
  need to load 15 dimensions into the data warehouse and so on. With
  SSIS out-of-the-box, you are forced to create multiple individual
  packages which is very time consuming due to the lack of easy code
  reuse. In a typical BI project, the ETL implementation can easily take
  up 60% of the project time, so it is beneficial if the development
  time of similar SSIS packages can be cut down.

Some tools you should definitely check out are:

BIDS Helper which has an add-in for BI Development studio or SQL Server Data Tools
SSIS Package generator

If you want to create a metadata mapping table and use that to generate SSIS packages you can have a look at Generate multiple SSIS packages using BIML and metadata


Does UDP Port 1434 need to be open for named instances with static TCP?

UDP 1434 is used for Named SQL Server instances and SQL Browser service listens on this port for any incoming requests to a named sql server instance. The browser service will respond to the client with TCP port no. for the requested named instance.

From BOL :

  The SQL Server Browser service lets users connect to instances of the Database Engine that are not listening on port 1433, without knowing the port number.

  To use SQL Server Browser, you must open UDP port 1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the port number.

-

  It needs to be open if the port is not explicit in the connection string?

Yes. If browser service is not running, you HAVE to specify servername,portNo e.g. YourSecureSQLServerName,50101


In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?

Am I right that it will guarantee the order in this case without an order by clause?

No. A Flow Distinct that preserves order (allowing ORDER BY without a sort) is not implemented in SQL Server today. It is possible to do in principle, but then many things are possible if we are allowed to change the SQL Server source code. If you can make a good case for this development work, you could suggest it to Microsoft.

  If not, is there another method to force a plan that is as fast as Solution A, preferably one that avoids sorts?

Yes. (Table & query hints only required when using the pre-2014 cardinality estimator):

-- Additional index
CREATE UNIQUE NONCLUSTERED INDEX i
ON #Orders (StoreID, CustID, Amount, OrderID);

-- Query
SELECT TOP (500)
    O.CustID,
    O.Amount
FROM #Orders AS O
    WITH (FORCESEEK(IX (StoreID)))
WHERE O.StoreID = 1
AND NOT EXISTS
(
    SELECT NULL
    FROM #Orders AS O2
        WITH (FORCESEEK(i (StoreID, CustID, Amount)))
    WHERE
        O2.StoreID = O.StoreID
        AND O2.CustID = O.CustID
        AND O2.Amount >= O.Amount
        AND
        (
            O2.Amount > O.Amount
            OR
            (
                O2.Amount = O.Amount
                AND O2.OrderID > O.OrderID
            )
        )
)
ORDER BY
    O.Amount DESC
OPTION (MAXDOP 1);

(500 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

SQL CLR solution

The following script shows using a SQL CLR table-valued function to meet the stated requirements. I am not a C# expert, so the code may bear improvement:

USE Sandpit;
GO
-- Ensure SQLCLR is enabled
EXECUTE sys.sp_configure
    @configname = 'clr enabled',
    @configvalue = 1;
RECONFIGURE;
GO
-- Lazy, but effective to allow EXTERNAL_ACCESS
ALTER DATABASE Sandpit
SET TRUSTWORTHY ON;
GO
-- The CLR assembly
CREATE ASSEMBLY FlowDistinctOrder
AUTHORIZATION dbo
FROM [encoded data, 12290 characters]
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
-- The CLR TVF with order guarantee
CREATE FUNCTION dbo.FlowDistinctOrder
(
    @ServerName nvarchar(128),
    @DatabaseName nvarchar(128),
    @MaxRows bigint
)
RETURNS TABLE
(
    CustID integer NULL,
    Amount float NULL
)
ORDER (Amount DESC)
AS EXTERNAL NAME FlowDistinctOrder.UserDefinedFunctions.FlowDistinctOrder;

Test table and sample data from the question:

-- Test table
CREATE TABLE dbo.Orders
(
    OrderID    integer  NOT NULL IDENTITY(1,1),
    CustID     integer  NOT NULL,
    StoreID    integer  NOT NULL,
    Amount     float    NOT NULL
);
GO
-- Sample data
WITH
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT dbo.Orders
    (CustID, StoreID, Amount)
SELECT
    CustID  = Number / 10,
    StoreID = Number % 4,
    Amount  = 1000 * RAND(Number)
FROM FinalCte
WHERE
    Number <= 1000000;
GO
-- Index
CREATE CLUSTERED INDEX IX
ON dbo.Orders
    (StoreID ASC, Amount DESC, CustID ASC);

Function test:

-- Test the function
-- Run several times to ensure connection is cached
-- and CLR code fully compiled
DECLARE @Start datetime2 = SYSUTCDATETIME();

SELECT TOP (500)
    FDO.CustID
FROM dbo.FlowDistinctOrder
(
    @@SERVERNAME,   -- For external connection
    DB_NAME(),      -- For external connection
    500             -- Number of rows to return
) AS FDO
ORDER BY
    FDO.Amount DESC;

SELECT DATEDIFF(MILLISECOND, @Start, SYSUTCDATETIME());

Execution plan (note the validation of the ORDER guarantee):

On my laptop, this typically executes in 80-100ms. This is nowhere near as fast as the T-SQL rewrite above, but it should show good performance stability in the face of different data distributions.

Source code:

using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

public partial class UserDefinedFunctions
{
    private sealed class ReverseComparer<T> : IComparer<T>
    {
        private readonly IComparer<T> original;

        public ReverseComparer(IComparer<T> original)
        {
            this.original = original;
        }

        public int Compare(T left, T right)
        {
            return original.Compare(right, left);
        }
    }

    [SqlFunction
        (
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.None,
        FillRowMethodName = "FillRow",
        TableDefinition = "CustID integer NULL, Amount float NULL"
        )
    ]
    public static IEnumerable FlowDistinctOrder
        (
        [SqlFacet (MaxSize=128)]string ServerName,
        [SqlFacet (MaxSize=128)]string DatabaseName,
        long MaxRows
        )
    {
        var list = new SortedDictionary<double, int>
            (new ReverseComparer<double>(Comparer<double>.Default));

        var csb = new SqlConnectionStringBuilder();
        csb.ConnectTimeout = 10;
        csb.DataSource = ServerName;
        csb.Enlist = false;
        csb.InitialCatalog = DatabaseName;
        csb.IntegratedSecurity = true;

        using (var conn = new SqlConnection(csb.ConnectionString))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText =
                    @"
                    SELECT
                        O.CustID,
                        O.Amount
                    FROM dbo.Orders AS O
                    WHERE
                        O.StoreID = 1
                    ORDER BY
                        O.Amount DESC";

                int custid;
                double amount;

                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        custid = rdr.GetInt32(0);
                        amount = rdr.GetDouble(1);

                        if (!list.ContainsKey(amount))
                        {
                            list.Add(amount, custid);
                            if (list.Count == MaxRows)
                            {
                                break;
                            }
                        }
                    }
                }
            }
        }
        return list;
    }

    public static void FillRow(object obj, out int CustID, out double Amount)
    {
        var v = (KeyValuePair<double, int>)obj;
        CustID = v.Value;
        Amount = v.Key;
    }
}


How to read queries of master that sends to slaves?

SEE QUERIES AS THEY HAPPEN ON THE SLAVE

On a running Slave, you should see to connections with the username system user

Login to the Slave and run this

SELECT * FROM information_schema.user WHERE user = 'system user'\G

SEE QUERIES BEFORE THE SLAVE GETS TO PROCESS THEM

On a running Master, the binary logs contains all completed SQL commands.

To the see the commands, you need to use the mysqlbinlog command utility agaunst the binary logs on the Master or you could run it against the relay logs on the Slave

Here are my earlier posts on how to apply mysqlbinlog in a variety of ways

Feb 18, 2014 : mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error
Sep 06, 2013 : MySQL : Extract SQL statements from binlog
Sep 03, 2013 : Get missed transactions from slave
Feb 04, 2013 : MySQL Point in Time Recovery For Just a Single Schema?
Aug 16, 2012 : How to see what was executed in the database via binlogs for specific date?
May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Oct 26, 2011 : Multi-threaded replay of master activity for load/performance testing


Create stored procedure that contains newlines with a oneliner sql statement

Yes, you can do something like this with dynamic SQL:

DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @NewLine NCHAR(1) = NCHAR(10);

SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;


How to query for address "predictions" while applying constraints

3 tables is what I call "over-normalizing" -- it leads to performance problems.

"Normalizing" is used for two purposes, neither of which really apply to your use case.

Isolate something, say "city" so that it can be easily changed.  In real life, that rarely happens for cities.  ("Bombay" -> "Mumbai" (India), "Hot Springs" -> "Truth or Consequences" (New Mexico))
Save space.  Even if you have all 3 million cities on earth, spelling them out the country each time is not much burden of space.  (For countries, I recommend the standard 2-letter country-codes and use CHARACTER SET ascii.  Optionally, have a table mapping them to spelled out names.)

Zip codes:  In the US, there are about 42K zip-codes.  To normalize you could use a 2-byte SMALLINT UNSIGNED.  But the zipcode, itself, can be stored in a 3-byte MEDIUMINT(5) UNSIGNED ZEROFILL.  Zipcodes do change.  But that usually involves forcing half the users of one zipcode to adopt a 'new' zipcode.  You would have to go through all users of that zipcode (which could be INDEXed) to figure out which ones to change.

Similarly for the split-up of Yugoslavia and Czechoslovakia.  On the other hand, changing "The Republic of Upper Volta" to "Burkina Faso" is easy if you have 'normalized' it.

Plan A (millions of locations):  Simply spell out the location of each item.

Plan B (billions of locations):  Split in two:  Street address and zip+city+country

Think about it another way:  What will you do with the address?

Only for sending mail?  Then why split up the pieces; simply have some text that can be readily printed as-is.
Also used for statistics?  Then you need, say, the "country" (or "country_code") so you can do SELECT SUM... GROUP BY country_code.  This implies that country_code is a separate column.


You can see you are creating temptable with same name.You cannot create temp table with same name in single query or batch this is documented as per BOL document. From Bol document

  If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

You should use below logic it will just check before creating and deleting

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

   DROP TABLE #Temptable1

CREATE TABLE #Temptable1(name nvarchar(30))

Which would make your query like

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

       DROP TABLE #Temptable1

    CREATE TABLE #Temptable1(name nvarchar(30))

-- Some queries involving #TempTable1

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

       DROP TABLE #Temptable1

-- Some queries

declare MyCursor cursor fast_forward for
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

          IF OBJECT_ID('tempdb..#Temptable2') IS NOT NULL--new temp table

       DROP TABLE #Temptable2

    CREATE TABLE #Temptable1(name nvarchar(30))

       -- Some queries involving #TempTable1

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

       DROP TABLE #Temptable2

       fetch next from MyCursor into @CursorValue

    end

close MyCursor
deallocate MyCursor


Store values in select as a temp variable and reuse them

Yes, you can do what you want.  It's not exactly a variable but it works the same.  It's one of several ways you can use CROSS APPLY.

SELECT
      bq.quoteID
    , bq.unsentNumber
    , bq.sendSubsequent
    , bq.unsentDelay
    , sr.contractorID
    , CASE
        WHEN (ComputedColumns.MyDateAdd  - FLOOR(ComputedColumns.MyDateAdd)) <= ((ComputedColumns.MyDate) - FLOOR(ComputedColumns.MyDate))
            THEN 1
            ELSE 0
        END AS 'Add Bid'
    , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
    , GETDATE() AS 'Time Now'
    , e.email
    , e.name
FROM tblCustomerBidQueue bq
INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
INNER JOIN tblContractors c ON c.ID = sr.contractorID
INNER JOIN tblEmails e ON e.custom = bq.QuoteID
CROSS APPLY (SELECT CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT) AS MyDateAdd,
                CAST(GETDATE() AS FLOAT) AS MyDate) ComputedColumns
WHERE bq.status = 'Queued'

Basically you CROSS APPLY your variables and then you can use them as if they are a the columns of a table.


What's the best way to store and search through many product specifications

One way is to design the specifications in a supertable/subtable form. Here is one such design: Fiddle. I'm sure it can be improved on, but the key point is that specification ID values are unique among all specifications, not just within each type of specification. So there are no collisions between Colour and Weight, for example. However, a spec defined as colour cannot be entered into any other spec subtable except Colors (or Colours -- try as I might, I'm afraid I mixed the spelling in at least one location). The super table (Specs) makes it easy to query individual specs.